下面列出了java.sql.PreparedStatement#executeQuery ( ) 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
protected int getSidByList(Connection conn, int tid) {
String sql = "select sec_id from trade.securities where tid = ?";
try {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, tid);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
return rs.getInt(1);
}
rs.close();
} catch (SQLException se) {
if (!SQLHelper.checkGFXDException(conn, se)) return getSid(); //return random sid
SQLHelper.handleSQLException(se);
}
return getSid(); //random
}
MgmtEntry.Requestor createRequestor(String name) throws CaMgmtException {
final String sql = sqlSelectRequestor;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
stmt = prepareStatement(sql);
stmt.setString(1, name);
rs = stmt.executeQuery();
if (!rs.next()) {
throw new CaMgmtException("unknown Requestor " + name);
}
return new MgmtEntry.Requestor(new NameId(rs.getInt("ID"), name),
rs.getString("TYPE"), rs.getString("CONF"));
} catch (SQLException ex) {
throw new CaMgmtException(datasource.translate(sql, ex));
} finally {
datasource.releaseResources(stmt, rs);
}
}
@Test
public void testDivideByIntegerZero() throws Exception {
long ts = nextTimestamp();
String query = "SELECT a_integer/0 FROM atable";
String url = getUrl() + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + (ts + 5); // Run query at timestamp 5
Connection conn = DriverManager.getConnection(url);
try {
PreparedStatement statement = conn.prepareStatement(query);
statement.executeQuery();
fail();
} catch (SQLException e) { // expected
assertTrue(e.getMessage().contains("Divide by zero"));
} finally {
conn.close();
}
}
/**
*@author Warley Rodrigues
*Criação e inserção de Pais, Estado, Cidade, Usuario e Postagem no banco de dados
*/
@Override
public Cidade selecionar(int id) throws Exception {
Connection conexao = getConexao();
PreparedStatement pstmt;
pstmt = conexao.prepareStatement("select * from cidades where id = ?");
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
if(rs.next()){
Cidade p = new Cidade();
EstadoDAO estadoDAO = new EstadoDAO();
p.setId(rs.getInt("id"));
p.setEstado(estadoDAO.selecionar(rs.getInt("estado")));
p.setNome(rs.getString("nome"));
return p;
} else {
return null;
}
}
public Set<String> getActiveTokensForConsumerKey(String consumerKey) throws IdentityOAuth2Exception {
Connection connection = IdentityDatabaseUtil.getDBConnection();
PreparedStatement ps = null;
ResultSet rs = null;
Set<String> accessTokens = new HashSet<>();
try {
String sqlQuery = SQLQueries.GET_ACCESS_TOKENS_FOR_CONSUMER_KEY;
ps = connection.prepareStatement(sqlQuery);
ps.setString(1, consumerKey);
ps.setString(2, OAuthConstants.TokenStates.TOKEN_STATE_ACTIVE);
rs = ps.executeQuery();
while (rs.next()) {
accessTokens.add(rs.getString(1));
}
connection.commit();
} catch (SQLException e) {
IdentityDatabaseUtil.rollBack(connection);
throw new IdentityOAuth2Exception("Error occurred while getting access tokens from acces token table for " +
"the application with consumer key : " + consumerKey, e);
} finally {
IdentityDatabaseUtil.closeAllConnections(connection, null, ps);
}
return accessTokens;
}
@Test
public void testMixedTypeInListStatement() throws Exception {
String query = "SELECT entity_id FROM " + tableName + " WHERE organization_id=? AND x_long IN (5, ?)";
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
try {
PreparedStatement statement = conn.prepareStatement(query);
statement.setString(1, tenantId);
long l = Integer.MAX_VALUE + 1L;
statement.setLong(2, l);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals(ROW7, rs.getString(1));
assertTrue(rs.next());
assertEquals(ROW9, rs.getString(1));
assertFalse(rs.next());
} finally {
conn.close();
}
}
private Object executePreparedStatement(PreparedStatement pstmt)
throws SQLException {
try {
logger.info("Executing query " + pstmt.toString());
ResultSet result = pstmt.executeQuery();
// even if this result set is empty (i.e. no row found), just return
// the empty result set
logger.info("Query succeeded");
recyclePooledStatement(pstmt);
return result;
} catch (SQLException e) {
// throw away the pooled statement, just in case it was the problem
releasePooledStatement(pstmt);
logGetRowError(e);
throw new SQLException("Error executing query from archive database", e
.getSQLState(), VENDOR_CODE_ARCHIVE_ERROR, e);
}
}
public String serverTestMultipleSetDatabase()
throws SQLException
{
Connection con = this.getServerSideConnection();
String sql = "select DESCRIPTION from APP.ORDERS where ORDER_ID = ?";
PreparedStatement ps = con.prepareStatement(sql);
ps.setInt(1, 1);
ResultSet rs = ps.executeQuery();
rs.next();
String result = rs.getString(1);
rs.close();
ps.close();
con.close();
return result;
}
@Override
public void loadSubscription(Node node, String subId)
{
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
Map<Node.UniqueIdentifier, Node> nodes = new HashMap<>();
nodes.put(node.getUniqueIdentifier(), node);
try
{
con = DbConnectionManager.getConnection();
// Get subscriptions to all nodes
pstmt = con.prepareStatement(LOAD_NODE_SUBSCRIPTION);
pstmt.setString(1, node.getUniqueIdentifier().getServiceIdentifier().getServiceId());
pstmt.setString(2, node.getNodeID());
pstmt.setString(3, subId);
rs = pstmt.executeQuery();
// Add to each node the corresponding subscription
if (rs.next())
{
loadSubscriptions(nodes, rs);
}
}
catch (SQLException sqle)
{
log.error("An exception occurred while loading a subscription ({}) for a node ({}) from the database.", subId, node.getUniqueIdentifier(), sqle);
}
finally
{
DbConnectionManager.closeConnection(rs, pstmt, con);
}
}
/**
* Test that when a nested routine drops a role/schema, the
* current value is correctly reset. For roles, the current role
* is unchanged, since it is lazily checked (and potentially reset
* to NONE if it no longer exists or it is no longer granted to
* session user) only when it is attempted used for anything. For
* schema, the current schema should revert back to the session's
* default schema. This holds for all frames on the session
* context stack (see also caller's check).
*/
public static void dropper() throws SQLException
{
Connection conn1 = null;
try {
conn1 = DriverManager.getConnection("jdbc:default:connection");
// Drop current contexts
Statement stm = conn1.createStatement();
stm.executeUpdate("drop role outermost");
stm.executeUpdate("drop schema outermost restrict");
stm.close();
String[] expected = new String[]{null, "TEST_DBO"};
// check that we revert correctly
for (int i= 0; i < variableKeywords.length; i++) {
String curr = currentPrefix[i] + variableKeywords[i];
PreparedStatement ps =
conn1.prepareStatement("values " + curr);
ResultSet rs = ps.executeQuery();
assertCurrent(variableKeywords[i], rs, expected[i]);
rs.close();
ps.close();
}
} finally {
if (conn1 != null) {
try {
conn1.close();
} catch (Exception e) {
}
}
}
}
public ArrayList<Announcement> getAnnouncements() {
ArrayList<Announcement> announcements = new ArrayList<>();
try (final Connection connection = slaveInfo.getSource().getConnection()) {
String announcementTableName = String.format("%sannouncements", slaveInfo.getSettings().getPrefix());
PreparedStatement stmt = connection.prepareStatement("SELECT * FROM " + announcementTableName);
ResultSet res = stmt.executeQuery();
while (res.next()) {
if (res.getString("ANNOUNCEMENT_ID") != null) {
Announcement announcement = new Announcement(UUID.fromString(res.getString("ANNOUNCEMENT_ID")), Snowflake.of(res.getString("GUILD_ID")));
announcement.setSubscriberRoleIdsFromString(res.getString("SUBSCRIBERS_ROLE"));
announcement.setSubscriberUserIdsFromString(res.getString("SUBSCRIBERS_USER"));
announcement.setAnnouncementChannelId(res.getString("CHANNEL_ID"));
announcement.setAnnouncementType(AnnouncementType.valueOf(res.getString("ANNOUNCEMENT_TYPE")));
announcement.setEventId(res.getString("EVENT_ID"));
announcement.setEventColor(EventColor.fromNameOrHexOrID(res.getString("EVENT_COLOR")));
announcement.setHoursBefore(res.getInt("HOURS_BEFORE"));
announcement.setMinutesBefore(res.getInt("MINUTES_BEFORE"));
announcement.setInfo(res.getString("INFO"));
announcement.setEnabled(res.getBoolean("ENABLED"));
announcement.setInfoOnly(res.getBoolean("INFO_ONLY"));
announcements.add(announcement);
}
}
stmt.close();
} catch (SQLException e) {
Logger.getLogger().exception(null, "Failed to get all announcements.", e, true, this.getClass());
}
return announcements;
}
/**
* Retrieve all the Workflows for a tenant
*
* @param tenantId Tenant ID
* @return List<Workflow>
* @throws InternalWorkflowException
*/
public List<Workflow> listWorkflows(int tenantId) throws InternalWorkflowException {
Connection connection = IdentityDatabaseUtil.getDBConnection();
PreparedStatement prepStmt = null;
ResultSet rs = null;
List<Workflow> workflowList = new ArrayList<>();
String query = SQLConstants.LIST_WORKFLOWS_QUERY;
try {
prepStmt = connection.prepareStatement(query);
prepStmt.setInt(1, tenantId);
rs = prepStmt.executeQuery();
while (rs.next()) {
String id = rs.getString(SQLConstants.ID_COLUMN);
String name = rs.getString(SQLConstants.WF_NAME_COLUMN);
String description = rs.getString(SQLConstants.DESCRIPTION_COLUMN);
String templateId = rs.getString(SQLConstants.TEMPLATE_ID_COLUMN);
String templateImplId = rs.getString(SQLConstants.TEMPLATE_IMPL_ID_COLUMN);
Workflow workflowDTO = new Workflow();
workflowDTO.setWorkflowId(id);
workflowDTO.setWorkflowName(name);
workflowDTO.setWorkflowDescription(description);
workflowDTO.setTemplateId(templateId);
workflowDTO.setWorkflowImplId(templateImplId);
workflowList.add(workflowDTO);
}
} catch (SQLException e) {
throw new InternalWorkflowException(errorMessage, e);
} finally {
IdentityDatabaseUtil.closeAllConnections(connection, null, prepStmt);
}
return workflowList;
}
@Override
public Principal authenticateLocal(Session session, String name, String password,
byte[] salt) {
User user = null;
Connection conn = null;
PreparedStatement stmt = null;
try {
conn = openConnection();
stmt = conn.prepareStatement(GET_USER_SQL);
stmt.setString(1, name);
ResultSet rs = stmt.executeQuery();
if (rs.next()) {
String md5 = rs.getString(5);
if ((salt == null) ?
md5Password(name, password).equals(md5) :
password.equals(salted(md5, salt))) {
user = getUser(rs);
}
}
rs.close();
conn.commit();
}
catch (SQLException ex) {
throw new SecurityException("Error adding role", ex);
}
finally {
cleanup(conn, stmt);
}
if (user == null) {
throw new AuthenticationFailedException("invalid username or password");
}
if (session != null) {
session.put(SESSION_PRINCIPAL_KEY, user);
session.put(SESSION_ROLES_KEY, user.getRoles());
}
if (monitor.getUserMonitor(user.getName()) == null) {
monitor.registerUserMonitor(new UserMonitorImpl(user.getName()));
}
return user;
}
public static void main(String[] args) throws Exception {
TDataSource ds = new TDataSource();
ds.setAppName("ICBU_DA_PBSVR_DEV_APP");
ds.setDynamicRule(true);
Map cp = new HashMap();
cp.put(ConnectionProperties.ALLOW_TEMPORARY_TABLE, "true");
cp.put(ConnectionProperties.CHOOSE_TEMPORARY_TABLE, "true");
ds.setConnectionProperties(cp);
ds.init();
System.out.println("init done");
Connection conn = ds.getConnection();
// insert a record
// conn.prepareStatement("replace into sample_table (id,name,address) values (1,'sun','hz')").executeUpdate();
System.out.println("insert done");
// select all records
PreparedStatement ps = conn.prepareStatement("replace into tddl_category (id,name,gmt_modified,gmt_create) values (1,'aa',now(),now())");
// ps.executeUpdate();
ps = conn.prepareStatement("select ADL_DM_MDM_MEM_PROD_EFFECT_SDT0.STAT_DATE,ADL_DM_MDM_MEM_PROD_EFFECT_SDT0.SUM_PROD_SHOW_NUM,ADL_DM_MDM_MEM_PROD_EFFECT_SDT0.SUM_PROD_CLICK_NUM,ADL_DM_MDM_MEM_PROD_EFFECT_SDT0.SUM_PROD_FB_NUM,ADL_DM_MDM_MEM_PROD_EFFECT_SDT0.SUM_PROD_VISITOR_CNT from adl_dm_mdm_mem_prod_effect_sdt0 ADL_DM_MDM_MEM_PROD_EFFECT_SDT0 order by ADL_DM_MDM_MEM_PROD_EFFECT_SDT0.STAT_DATE asc limit 0,100");
// ps.setDate(1, new Date(System.currentTimeMillis()));
ResultSet rs = ps.executeQuery();
while (rs.next()) {
StringBuilder sb = new StringBuilder();
int count = rs.getMetaData().getColumnCount();
for (int i = 1; i <= count; i++) {
String key = rs.getMetaData().getColumnLabel(i);
Object val = rs.getObject(i);
sb.append("[" + rs.getMetaData().getTableName(i) + "." + key + "->" + val + "]");
}
System.out.println(sb.toString());
}
rs.close();
ps.close();
conn.close();
System.out.println("query done");
}
public static ResultSet getNonUniqQuery(Connection conn, int whichQuery, int cid,
BigDecimal price1, BigDecimal price2, int tid, boolean[] success) {
PreparedStatement stmt;
ResultSet rs = null;
success[0] = true;
try {
Boolean hasHdfs = TestConfig.tab().booleanAt(SQLPrms.hasHDFS, false);
String database = SQLHelper.isDerbyConn(conn)?"Derby - " :"gemfirexd - ";
String query = (! SQLHelper.isDerbyConn(conn) && hasHdfs ) ? " QUERY: " + nonUniqSelectHdfs[whichQuery] : " QUERY: " + nonUniqSelect[whichQuery];
stmt = (! SQLHelper.isDerbyConn(conn) && hasHdfs ) ? conn.prepareStatement(nonUniqSelectHdfs[whichQuery]) : conn.prepareStatement(nonUniqSelect[whichQuery]) ;
switch (whichQuery){
case 0:
//"select * from trade.securities s, trade.portfolio f where sec_id = f.sid and f.tid = ?",
Log.getLogWriter().info(database +"Querying SecuritiesPortfolio with no data" + query);
break;
case 1:
//"select cid, sid, symbol, price, qty from trade.securities s, trade.portfolio f where sec_id = f.sid and cid >? and f.tid = ? ",
Log.getLogWriter().info(database +"Querying SecuritiesPortfolio with CID:" + cid + query);
stmt.setInt(1, cid);
break;
case 2:
//"select cid, sid, symbol, exchange, qty, price from trade.securities s, trade.portfolio f where sec_id = f.sid and cid<? and(qty=availQty or price > ?) and f.tid = ?",
Log.getLogWriter().info(database +"Querying SecuritiesPortfolio with CID:" + cid + ",PRICE:"+ price1 + query );
stmt.setInt(1, cid); //set cid<=?
stmt.setBigDecimal(2, price1);
break;
case 3:
//"select cid, sid, symbol, exchange, price, subtotal from trade.securities s, trade.portfolio f where sec_id = f.sid and cid >? and (subtotal >10000 or (price >= ? and price <= ?) and f.tid = ?"
Log.getLogWriter().info(database +"Querying SecuritiesPortfolio with CID:" + cid + ",1_PRICE:"+ price1 + ",2_PRICE:" + price2 + query);
stmt.setInt(1, cid); //set cid>?
stmt.setBigDecimal(2, price1);
stmt.setBigDecimal(3, price2);
break;
case 4:
//Left outer join
Log.getLogWriter().info(database +"Querying SecuritiesPortfolio with LEFT OUTER JOIN " + query);
break;
default:
throw new TestException("incorrect select statement, should not happen");
}
rs = stmt.executeQuery();
} catch (SQLException se) {
if (!SQLHelper.checkDerbyException(conn, se)) success[0] = false; //handle lock could not acquire or deadlock
else if (se.getSQLState().equals("0A000") && se.getMessage().matches(".*disk.*")) {
SQLHelper.printSQLException(se);
Log.getLogWriter().info("got the unsupported exception, need to remove this once bug#40348 is fixed, continuing test");
useDisk[0] = true;
return null;
}
else SQLHelper.handleSQLException(se);
}
return rs;
}
@Test
public void testStarJoin() throws Exception {
String[] query = new String[5];
query[0] = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\", c.name, i.name iname, quantity, o.date FROM " + JOIN_ORDER_TABLE_FULL_NAME + " o JOIN "
+ JOIN_CUSTOMER_TABLE_FULL_NAME + " c ON o.\"customer_id\" = c.\"customer_id\" JOIN "
+ JOIN_ITEM_TABLE_FULL_NAME + " i ON o.\"item_id\" = i.\"item_id\" ORDER BY \"order_id\"";
query[1] = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\", c.name, i.name iname, quantity, o.date FROM " + JOIN_ORDER_TABLE_FULL_NAME + " o, "
+ JOIN_CUSTOMER_TABLE_FULL_NAME + " c, "
+ JOIN_ITEM_TABLE_FULL_NAME + " i WHERE o.\"item_id\" = i.\"item_id\" AND o.\"customer_id\" = c.\"customer_id\" ORDER BY \"order_id\"";
query[2] = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\", c.name, i.name iname, quantity, o.date FROM " + JOIN_ORDER_TABLE_FULL_NAME + " o JOIN "
+ JOIN_CUSTOMER_TABLE_FULL_NAME + " c ON o.\"customer_id\" = c.\"customer_id\" JOIN "
+ JOIN_ITEM_TABLE_FULL_NAME + " i ON o.\"item_id\" = i.\"item_id\" ORDER BY \"order_id\"";
query[3] = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\", c.name, i.name iname, quantity, o.date FROM (" + JOIN_ORDER_TABLE_FULL_NAME + " o, "
+ JOIN_CUSTOMER_TABLE_FULL_NAME + " c), "
+ JOIN_ITEM_TABLE_FULL_NAME + " i WHERE o.\"item_id\" = i.\"item_id\" AND o.\"customer_id\" = c.\"customer_id\" ORDER BY \"order_id\"";
query[4] = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\", c.name, i.name iname, quantity, o.date FROM " + JOIN_ORDER_TABLE_FULL_NAME + " o, ("
+ JOIN_CUSTOMER_TABLE_FULL_NAME + " c, "
+ JOIN_ITEM_TABLE_FULL_NAME + " i) WHERE o.\"item_id\" = i.\"item_id\" AND o.\"customer_id\" = c.\"customer_id\" ORDER BY \"order_id\"";
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(getUrl(), props);
try {
for (int i = 0; i < query.length; i++) {
PreparedStatement statement = conn.prepareStatement(query[i]);
ResultSet rs = statement.executeQuery();
assertTrue (rs.next());
assertEquals(rs.getString(1), "000000000000001");
assertEquals(rs.getString("\"order_id\""), "000000000000001");
assertEquals(rs.getString(2), "C4");
assertEquals(rs.getString("C.name"), "C4");
assertEquals(rs.getString(3), "T1");
assertEquals(rs.getString("iName"), "T1");
assertEquals(rs.getInt(4), 1000);
assertEquals(rs.getInt("Quantity"), 1000);
assertNotNull(rs.getDate(5));
assertTrue (rs.next());
assertEquals(rs.getString(1), "000000000000002");
assertEquals(rs.getString(2), "C3");
assertEquals(rs.getString(3), "T6");
assertEquals(rs.getInt(4), 2000);
assertNotNull(rs.getDate(5));
assertTrue (rs.next());
assertEquals(rs.getString(1), "000000000000003");
assertEquals(rs.getString(2), "C2");
assertEquals(rs.getString(3), "T2");
assertEquals(rs.getInt(4), 3000);
assertNotNull(rs.getDate(5));
assertTrue (rs.next());
assertEquals(rs.getString(1), "000000000000004");
assertEquals(rs.getString(2), "C4");
assertEquals(rs.getString(3), "T6");
assertEquals(rs.getInt(4), 4000);
assertNotNull(rs.getDate(5));
assertTrue (rs.next());
assertEquals(rs.getString(1), "000000000000005");
assertEquals(rs.getString(2), "C5");
assertEquals(rs.getString(3), "T3");
assertEquals(rs.getInt(4), 5000);
assertNotNull(rs.getDate(5));
assertFalse(rs.next());
}
} finally {
conn.close();
}
}
public String getServiceProviderNameByClientId(String clientId, String clientType,
String tenantDomain) throws IdentityApplicationManagementException {
int tenantID = -123;
if (tenantDomain != null) {
try {
tenantID = ApplicationManagementServiceComponentHolder.getInstance().getRealmService()
.getTenantManager().getTenantId(tenantDomain);
} catch (UserStoreException e1) {
throw new IdentityApplicationManagementException("Error while reading application");
}
}
String applicationName = null;
// Reading application name from the database
Connection connection = IdentityDatabaseUtil.getDBConnection();
PreparedStatement storeAppPrepStmt = null;
ResultSet appNameResult = null;
try {
storeAppPrepStmt = connection
.prepareStatement(ApplicationMgtDBQueries.LOAD_APPLICATION_NAME_BY_CLIENT_ID_AND_TYPE);
storeAppPrepStmt.setString(1, clientId);
storeAppPrepStmt.setString(2, clientType);
storeAppPrepStmt.setInt(3, tenantID);
storeAppPrepStmt.setInt(4, tenantID);
appNameResult = storeAppPrepStmt.executeQuery();
if (appNameResult.next()) {
applicationName = appNameResult.getString(1);
}
connection.commit();
} catch (SQLException e) {
throw new IdentityApplicationManagementException("Error while reading application", e);
} finally {
IdentityApplicationManagementUtil.closeResultSet(appNameResult);
IdentityApplicationManagementUtil.closeStatement(storeAppPrepStmt);
IdentityApplicationManagementUtil.closeConnection(connection);
}
return applicationName;
}
@Test
public void testSelfJoin() throws Exception {
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(getUrl(), props);
String tableName1 = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME);
String query1 = "SELECT /*+ USE_SORT_MERGE_JOIN*/ i2.\"item_id\", i1.name FROM " + tableName1 + " i1 JOIN "
+ tableName1 + " i2 ON i1.\"item_id\" = i2.\"item_id\" ORDER BY i1.\"item_id\"";
String query2 = "SELECT /*+ USE_SORT_MERGE_JOIN*/ i1.name, i2.name FROM " + tableName1 + " i1 JOIN "
+ tableName1 + " i2 ON i1.\"item_id\" = i2.\"supplier_id\" ORDER BY i1.name, i2.name";
try {
PreparedStatement statement = conn.prepareStatement(query1);
ResultSet rs = statement.executeQuery();
assertTrue (rs.next());
assertEquals(rs.getString(1), "0000000001");
assertEquals(rs.getString(2), "T1");
assertTrue (rs.next());
assertEquals(rs.getString(1), "0000000002");
assertEquals(rs.getString(2), "T2");
assertTrue (rs.next());
assertEquals(rs.getString(1), "0000000003");
assertEquals(rs.getString(2), "T3");
assertTrue (rs.next());
assertEquals(rs.getString(1), "0000000004");
assertEquals(rs.getString(2), "T4");
assertTrue (rs.next());
assertEquals(rs.getString(1), "0000000005");
assertEquals(rs.getString(2), "T5");
assertTrue (rs.next());
assertEquals(rs.getString(1), "0000000006");
assertEquals(rs.getString(2), "T6");
assertTrue (rs.next());
assertEquals(rs.getString(1), "invalid001");
assertEquals(rs.getString(2), "INVALID-1");
assertFalse(rs.next());
rs = conn.createStatement().executeQuery("EXPLAIN " + query1);
assertPlansEqual(plans[2], QueryUtil.getExplainPlan(rs));
statement = conn.prepareStatement(query2);
rs = statement.executeQuery();
assertTrue (rs.next());
assertEquals(rs.getString(1), "T1");
assertEquals(rs.getString(2), "T1");
assertTrue (rs.next());
assertEquals(rs.getString(1), "T1");
assertEquals(rs.getString(2), "T2");
assertTrue (rs.next());
assertEquals(rs.getString(1), "T2");
assertEquals(rs.getString(2), "T3");
assertTrue (rs.next());
assertEquals(rs.getString(1), "T2");
assertEquals(rs.getString(2), "T4");
assertTrue (rs.next());
assertEquals(rs.getString(1), "T5");
assertEquals(rs.getString(2), "T5");
assertTrue (rs.next());
assertEquals(rs.getString(1), "T6");
assertEquals(rs.getString(2), "T6");
assertFalse(rs.next());
} finally {
conn.close();
}
}
private void fixBasicZoneNicCount(Connection conn) {
try {
PreparedStatement pstmt = conn.prepareStatement("SELECT id from data_center where networktype='Basic'");
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
Long zoneId = rs.getLong(1);
Long networkId = null;
Long vmCount = 0L;
s_logger.debug("Updating basic zone id=" + zoneId + " with correct nic count");
pstmt = conn.prepareStatement("SELECT id from networks where data_center_id=? AND guest_type='Direct'");
pstmt.setLong(1, zoneId);
rs = pstmt.executeQuery();
if (rs.next()) {
networkId = rs.getLong(1);
} else {
continue;
}
pstmt = conn.prepareStatement("SELECT count(*) from vm_instance where name like 'i-%' and (state='Running' or state='Starting' or state='Stopping')");
rs = pstmt.executeQuery();
if (rs.next()) {
vmCount = rs.getLong(1);
}
pstmt = conn.prepareStatement("UPDATE op_networks set nics_count=? where id=?");
pstmt.setLong(1, vmCount);
pstmt.setLong(2, networkId);
pstmt.executeUpdate();
}
s_logger.debug("Basic zones are updated with correct nic counts successfully");
rs.close();
pstmt.close();
} catch (SQLException e) {
throw new CloudRuntimeException("Unable to drop 'path' index for 'domain' table due to:", e);
}
}
public String getItemSQL(String id ) {
Connection c = null;
// Define a list in which all work items are stored
String query = "";
String status="" ;
String description="";
try {
// Create a Connection object
c = ConnectionHelper.getConnection();
ResultSet rs = null;
Statement s = c.createStatement();
Statement scount = c.createStatement();
// Use prepared statements
PreparedStatement pstmt = null;
PreparedStatement ps = null;
//Specify the SQL Statement to query data
query = "Select description, status FROM work where idwork ='" +id + "' ";
pstmt = c.prepareStatement(query);
rs = pstmt.executeQuery();
while (rs.next()) {
description = rs.getString(1);
status = rs.getString(2);
}
return convertToString(toXmlItem(id,description,status));
} catch (SQLException e) {
e.printStackTrace();
} finally {
ConnectionHelper.close(c);
}
return null;
}