Java Code Examples for java.sql.PreparedStatement#executeQuery()

The following examples show how to use java.sql.PreparedStatement#executeQuery() . These examples are extracted from open source projects. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
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
}
 
Example 2
Source Project: xipki   File: CaManagerQueryExecutor.java    License: Apache License 2.0 6 votes vote down vote up
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);
  }
}
 
Example 3
@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();
    }
}
 
Example 4
Source Project: redesocial   File: CidadeDAO.java    License: MIT License 6 votes vote down vote up
/**
*@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;
    }
}
 
Example 5
Source Project: carbon-identity   File: TokenMgtDAO.java    License: Apache License 2.0 6 votes vote down vote up
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;
}
 
Example 6
Source Project: phoenix   File: InQueryIT.java    License: Apache License 2.0 6 votes vote down vote up
@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();
    }
}
 
Example 7
Source Project: gemfirexd-oss   File: GfxdJDBCRowLoader.java    License: Apache License 2.0 6 votes vote down vote up
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);
  }
}
 
Example 8
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;
}
 
Example 9
@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);
	}
}
 
Example 10
/**
 * 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) {
            }
        }
    }
}
 
Example 11
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;
}
 
Example 12
Source Project: carbon-identity   File: WorkflowDAO.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * 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;
}
 
Example 13
@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;
}
 
Example 14
Source Project: tddl5   File: OrderBySample.java    License: Apache License 2.0 4 votes vote down vote up
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");
}
 
Example 15
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;
}
 
Example 16
Source Project: phoenix   File: SortMergeJoinIT.java    License: Apache License 2.0 4 votes vote down vote up
@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();
    }
}
 
Example 17
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;
}
 
Example 18
Source Project: phoenix   File: SortMergeJoinIT.java    License: Apache License 2.0 4 votes vote down vote up
@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();
    }
}
 
Example 19
Source Project: cloudstack   File: Upgrade222to224.java    License: Apache License 2.0 4 votes vote down vote up
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);
    }
}
 
Example 20
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;
    }