Java Code Examples for java.sql.Connection

@Test
public void testFailOriginalUrl() throws Exception {
  TracingDriver.ensureRegisteredAsTheFirstDriver();
  TracingDriver.setInterceptorMode(true);
  try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:jdbc")) {
    Statement statement = connection.createStatement();
    try {
      statement.executeUpdate("CREATE TABLE employer (id INTEGER2)");
    } catch (Exception ignore) {
    }
    assertGetDriver(connection);
  }

  List<MockSpan> spans = mockTracer.finishedSpans();
  assertEquals(2, spans.size());
  MockSpan span = spans.get(1);
  assertTrue(span.tags().containsKey(Tags.ERROR.getKey()));
  checkNoEmptyTags(spans);
}
 

@Test
public void createDuplicateAliasTimeseriesTest2() throws ClassNotFoundException {
  String sql1 = "create timeseries root.turbine.d4.s1(temperature) with datatype=FLOAT, encoding=RLE, compression=SNAPPY " +
          "tags(tag1=t1, tag2=t2) attributes(attr1=a1, attr2=a2)";
  String sql2 = "create timeseries root.turbine.d4.temperature with datatype=INT32, encoding=RLE " +
          "tags(tag2=t2, tag3=t3) attributes(attr3=a3, attr4=a4)";
  Class.forName(Config.JDBC_DRIVER_NAME);
  try (Connection connection = DriverManager
          .getConnection(Config.IOTDB_URL_PREFIX + "127.0.0.1:6667/", "root", "root");
       Statement statement = connection.createStatement()) {
    statement.execute(sql1);
    statement.execute(sql2);
    fail();
  } catch (Exception e) {
    assertTrue(e.getMessage().contains("Path [root.turbine.d4.temperature] already exist"));
  }
}
 

@Test
public void testCastOperatorInWhere() throws Exception {
    String query = "SELECT a_integer FROM aTable WHERE ?=organization_id and 2.5 = (CAST a_integer AS DECIMAL)/2 ";
    Properties props = new Properties(TEST_PROPERTIES);
    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
    Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
    try {
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, tenantId);
        ResultSet rs = statement.executeQuery();
        assertTrue (rs.next());
        assertEquals(5, rs.getInt(1));
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 

@Override
public ArrayList<AuthInfo> query(int start, int length) throws SQLException {

    Connection conn = DBUtil.getConnection();

    String sql = "select * from authInfo limit ?, ?;";
    PreparedStatement pstmt = conn.prepareStatement(sql);
    pstmt.setInt(1, start - 1);
    pstmt.setInt(2, length);
    ResultSet rs = pstmt.executeQuery();

    ArrayList<AuthInfo> list = new ArrayList<>();
    AuthInfo authInfo;

    while (rs.next()) {
        authInfo = new AuthInfo(rs.getInt(1), rs.getString(2), rs.getString(3)
                , rs.getString(4), rs.getString(5), rs.getString(6));
        list.add(authInfo);
    }

    rs.close();
    pstmt.close();

    return list;
}
 

public void deleteVersions(Connection connection, String versionIds) throws Exception {
	logger.debug("IN");
	logger.debug("Deleting the versions " + versionIds + " from the cube");
	String editCubeTableName = getEditCubeName();
	Monitor deleteSQL = MonitorFactory.start("WhatIfEngine/it.eng.spagobi.engines.whatif.WhatIfEngineInstance.VersionDAO.deleteVersion.deleteMethodDAO.all");
	String sqlQuery = "delete from " + editCubeTableName + " where " + getVersionColumnName() + " in (" + versionIds + ")";

	Monitor deleteSQLExeCube = MonitorFactory.start("WhatIfEngine/it.eng.spagobi.engines.whatif.WhatIfEngineInstance.VersionDAO.deleteVersion.deleteMethodDAO.executeSQL.cube");
	SqlUpdateStatement queryStatement = new SqlUpdateStatement(sqlQuery);
	queryStatement.executeStatement(connection);
	deleteSQLExeCube.stop();
	
	
	logger.debug("Deleting the versions " + versionIds + " from the version dimension");
	sqlQuery = "delete from " + getVersionTableName() + " where " + getVersionColumnName() + " in (" + versionIds + ")";
	
	
	Monitor deleteSQLExeVersion = MonitorFactory.start("WhatIfEngine/it.eng.spagobi.engines.whatif.WhatIfEngineInstance.VersionDAO.deleteVersion.deleteMethodDAO.executeSQL.version");
	queryStatement = new SqlUpdateStatement(sqlQuery);
	queryStatement.executeStatement(connection);
	deleteSQLExeVersion.stop();
	
	logger.debug("Version deleted");
	logger.debug("OUT");
	deleteSQL.stop();
}
 

private void insert(final Connection connection,
                    final String linkKey,
                    final LinkDefinition<? extends BaseSource, ? extends BaseTarget> linkDefinition)
        throws SQLException
{
    try (PreparedStatement statement = connection.prepareStatement(String.format(
            "INSERT INTO %s (link_key, remote_container_id, link_name, link_role, source, target) VALUES (?,?,?,?,?,?)",
            getLinksTableName())))
    {
        statement.setString(1, linkKey);
        saveStringAsBlob(statement, 2, linkDefinition.getRemoteContainerId());
        saveStringAsBlob(statement, 3, linkDefinition.getName());
        statement.setInt(4, linkDefinition.getRole().getValue() ? 1 : 0);
        saveObjectAsBlob(statement, 5, linkDefinition.getSource());
        saveObjectAsBlob(statement, 6, linkDefinition.getTarget());
        if (statement.executeUpdate() != 1)
        {
            throw new StoreException(String.format("Cannot save link %s", new LinkKey(linkDefinition)));
        }
    }
}
 

@Test
public void testScanByUnsignedDoubleValue() throws Exception {
    String query = "SELECT a_string, b_string, a_unsigned_double FROM " + tableName + " WHERE ?=organization_id and ?=a_unsigned_double";
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    try {
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, getOrganizationId());
        statement.setDouble(2, 0.0001);
        ResultSet rs = statement.executeQuery();
        assertTrue (rs.next());
        assertEquals(rs.getString(1), A_VALUE);
        assertEquals(rs.getString("B_string"), B_VALUE);
        assertTrue(Doubles.compare(rs.getDouble(3), 0.0001) == 0);
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 

public boolean removeGroup(Connection connection, String resourceType, String resourceName,
                           String action, String group) throws AuthServerException {
    PreparedStatement insertMappingsStmt = null;
    try {
        insertMappingsStmt = connection.prepareStatement(RdbmsConstants.PS_DELETE_AUTH_RESOURCE_MAPPING);
        insertMappingsStmt.setString(1, resourceType);
        insertMappingsStmt.setString(2, resourceName);
        insertMappingsStmt.setString(3, action);
        insertMappingsStmt.setString(4, group);

        int updateRows = insertMappingsStmt.executeUpdate();

        return updateRows != 0;
    } catch (SQLException e) {
        throw new AuthServerException("Error occurred while persisting resource.", e);
    } finally {
        close(insertMappingsStmt);
    }
}
 

@Test
public void testSingleRouterResult0() throws Exception {
    DataSource ds = (DataSource) context.getBean("zebraDS");
    Connection conn = null;
    try {
        conn = ds.getConnection();
        PreparedStatement stmt = conn.prepareStatement("select name from test where id=?");
        stmt.setInt(1, 0);
        stmt.execute();
        ResultSet rs = stmt.getResultSet();
        List<String> rows = new ArrayList<String>();
        while (rs.next()) {
            rows.add(rs.getString("name"));
        }
        Assert.assertEquals(2, rows.size());
        Assert.assertEquals("leo0", rows.get(0));
        Assert.assertEquals("leo0", rows.get(1));
    } catch (Exception e) {
        Assert.fail();
    } finally {
        if (conn != null) {
            conn.close();
        }
    }
}
 

public static void sqlControl2(String[] e1, String[] e2, String[] e3,
        String[] e4, String[] e5, String[] e6, String[] e7)
        throws SQLException {

    Connection conn = DriverManager
            .getConnection("jdbc:default:connection");

    Statement s = conn.createStatement();

    executeStatement(
            s,
            "CREATE VIEW SQLCONTROL_VIEW AS SELECT * FROM SQLC.SQLCONTROL_DML",
            e1);
    executeStatement(s, "DROP VIEW SQLCONTROL_VIEW", e2);

    executeStatement(s, "LOCK TABLE SQLC.SQLCONTROL_DML IN EXCLUSIVE MODE",
            e3);
    executeStatement(s, "VALUES 1,2,3", e4);
    executeStatement(s, "SET SCHEMA SQLC", e5);
    executeStatement(s, "CREATE SCHEMA SQLC_M", e6);
    executeStatement(s, "DROP SCHEMA SQLC_M RESTRICT", e7);

    conn.close();

}
 

@Override
public Connection getConnection() throws SQLException {
    TransactionProxy proxy = getProxy();
    if (proxy != null) {
        return proxy.getConnection();
    }
    //根据当前激活的数据源 获取jdbc链接
    DataSource dataSource = DataSourceHolder.currentDataSource().getNative();
    String dsId = switcher().currentDataSourceId();
    Connection connection = DataSourceUtils.getConnection(dataSource);
    proxy = new TransactionProxy(dsId, connection, dataSource);
    addProxy(proxy);

    if (LOGGER.isDebugEnabled()) {
        LOGGER.debug(
                "DataSource (" + (dsId == null ? "default" : dsId) + ") JDBC Connection ["
                        + connection
                        + "] will"
                        + (proxy.isConnectionTransactional ? " " : " not ")
                        + "be managed by Spring");
    }

    return connection;
}
 

@Test
public void shouldGetConnectionAndCommit() throws SQLException {
    Connection mockConnection = mock(Connection.class);
    XAConnection mockXaConnection = mock(XAConnection.class);
    given(mockXaConnection.getConnection()).willReturn(mockConnection);
    given(this.mockXaDataSource.getXAConnection()).willReturn(mockXaConnection);

    // TODO properties not used
    Properties properties = new Properties();
    properties.put(TransactionalDriver.XADataSource, this.mockXaDataSource);

    Connection connection = this.dataSourceBean.getConnection();
    assertThat(connection).isInstanceOf(ConnectionImple.class);

    connection.commit();

    verify(this.mockXaDataSource, times(1)).getXAConnection();
    verify(mockXaConnection, times(1)).getConnection();
    verify(mockConnection, times(1)).commit();
}
 

@Override
public Iterator<String> getUsernames(String jid) {
    List<String> answer = new ArrayList<>();
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        con = DbConnectionManager.getConnection();
        pstmt = con.prepareStatement(LOAD_USERNAMES);
        pstmt.setString(1, jid);
        rs = pstmt.executeQuery();
        while (rs.next()) {
            answer.add(rs.getString(1));
        }
    }
    catch (SQLException e) {
        Log.error(LocaleUtils.getLocalizedString("admin.error"), e);
    }
    finally {
        DbConnectionManager.closeConnection(rs, pstmt, con);
    }
    return answer.iterator();
}
 

@Test public void bigintArrays() throws Exception {
  final Random r = new Random();
  try (Connection conn = DriverManager.getConnection(url)) {
    ScalarType component = ColumnMetaData.scalar(Types.BIGINT, "BIGINT", Rep.LONG);
    List<Array> arrays = new ArrayList<>();
    // Construct the data
    for (int i = 0; i < 3; i++) {
      List<Long> elements = new ArrayList<>();
      for (int j = 0; j < 7; j++) {
        long element = r.nextLong();
        if (r.nextBoolean()) {
          element *= -1;
        }
        elements.add(element);
      }
      arrays.add(createArray("BIGINT", component, elements));
    }
    writeAndReadArrays(conn, "long_arrays", "BIGINT", component, arrays,
        PRIMITIVE_LIST_VALIDATOR);
  }
}
 

public int serverCheckDatedBitemporalRowCounts(int balanceId) throws SQLException
{
    Connection con = this.getServerSideConnection();
    String sql = "select count(*) from TINY_BALANCE where BALANCE_ID = ?";
    PreparedStatement ps = con.prepareStatement(sql);
    ps.setInt(1, balanceId);
    ResultSet rs = ps.executeQuery();
    assertTrue(rs.next());

    int counts = rs.getInt(1);
    assertFalse(rs.next());
    rs.close();
    ps.close();
    con.close();

    return counts;
}
 

@Test
public void testValidateConnection() throws Exception {
    SnowflakeConnectionProperties properties = new SnowflakeConnectionProperties("connection");
    properties.account.setValue("talend");
    properties.userPassword.password.setValue("teland_password");
    properties.userPassword.userId.setValue("talend_dev");
    properties.schemaName.setValue("LOAD");
    properties.db.setValue("TestDB");
    Connection connection = Mockito.mock(Connection.class);
    Mockito.when(connection.isClosed()).thenReturn(false);
    DatabaseMetaData metaData = Mockito.mock(DatabaseMetaData.class);
    Mockito.when(metaData.getTables(Mockito.any(), Mockito.any(), Mockito.any(),
            Mockito.eq(new String[] { "TABLE", "VIEW" }))).thenReturn(Mockito.mock(ResultSet.class));
    Mockito.when(connection.getMetaData()).thenReturn(metaData);
    Mockito.when(
            DriverManagerUtils.getConnection(Mockito.any()))
    .thenReturn(connection);

    SnowflakeSourceOrSink sss = new SnowflakeSourceOrSink();
    sss.initialize(null, properties);
    Assert.assertEquals(ValidationResult.Result.OK, sss.validateConnection(properties).getStatus());
}
 

public static void procedureWithInAndOutParameters(int number,
    String[] name,
    int[]    total,
    ResultSet[] rs1,
    ResultSet[] rs2, ResultSet[] rs3, ResultSet[] rs4) throws SQLException {
    Connection c = DriverManager.getConnection("jdbc:default:connection");
  
  name[0]=name[0]+"Modified";
  total[0]=number;
  
  if (number > 0) {
    rs1[0] = c.createStatement().executeQuery("VALUES(1)");
  }
  if (number > 1) {
    rs2[0] = c.createStatement().executeQuery("VALUES(1)");
  }
  if (number > 2) {
    rs3[0] = c.createStatement().executeQuery("VALUES(1)");
  }
  if (number > 3) {
    rs4[0] = c.createStatement().executeQuery("VALUES(1)");
  }
}
 

@Test
public void testIgnoreIndexesBasedOnHint() throws Exception {
    Connection conn = DriverManager.getConnection(getUrl());
    conn.createStatement().execute("CREATE TABLE t (k INTEGER NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR) IMMUTABLE_ROWS=true");
    conn.createStatement().execute("CREATE INDEX idx1 ON t(v1) INCLUDE(v2)");
    conn.createStatement().execute("CREATE INDEX idx2 ON t(v1,v2)");
    PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class);
    QueryPlan plan = stmt.optimizeQuery("SELECT /*+NO_INDEX*/ k FROM t WHERE v1 = 'foo' AND v2 = 'bar'");
    assertEquals("T", plan.getTableRef().getTable().getTableName().getString());
}
 

public static void returnConnection(Connection conn, PreparedStatement pst, ResultSet rs) {
	closePreparedStatement(pst);
	closeResultSet(rs);
	switch (DATABASE_TYPE) {
	case PERSONAL:
		break;
	case TEAM:
		MysqlManager.returnConnection(conn);
		return;
	}
}
 

private AccountProfileDataBean getAccountProfileData(Connection conn, String userID) throws Exception {
    PreparedStatement stmt = getStatement(conn, getAccountProfileSQL);
    stmt.setString(1, userID);

    ResultSet rs = stmt.executeQuery();

    AccountProfileDataBean accountProfileData = getAccountProfileDataFromResultSet(rs);
    stmt.close();
    return accountProfileData;
}
 

ResultSet doQuery3( String query) throws SQLException {
    Connection conn = getConnection();
    Statement statement = conn.createStatement();
    try {
        return statement.executeQuery(query);
    } catch (SQLException e) {
        statement.close();
        throw e;
    }
}
 

@Override
public void updateFields(JDBCServiceManagerProperties jdbcProperties, Logger log, Connection connection, ISQLQueryObject sqlQueryObject, long tableId, UpdateModel ... updateModels) throws NotFoundException, NotImplementedException, ServiceException, Exception {
	java.util.List<Object> ids = new java.util.ArrayList<>();
	ids.add(tableId);
	JDBCUtilities.updateFields(jdbcProperties, log, connection, sqlQueryObject, 
			this.getOperatoreFieldConverter().toTable(Operatore.model()), 
			this._getMapTableToPKColumn(), 
			ids,
			this.getOperatoreFieldConverter(), this, updateModels);
}
 

@Test
public void testDataSourceConfig() throws Exception {
    try (final Connection connection = jdbcRule.getConnectionSource().getConnection()) {
        final Error exception = new Error("Final error massage is fatal!");
        final ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        try (final PrintWriter writer = new PrintWriter(outputStream)) {
            exception.printStackTrace(writer);
        }
        final String stackTrace = outputStream.toString();

        final long millis = System.currentTimeMillis();

        final Logger logger = LogManager.getLogger(this.getClass().getName() + ".testDataSourceConfig");
        logger.trace("Data source logged message 01.");
        logger.fatal("Error from data source 02.", exception);

        try (final Statement statement = connection.createStatement();
                final ResultSet resultSet = statement.executeQuery("SELECT * FROM dsLogEntry ORDER BY id")) {

            assertTrue("There should be at least one row.", resultSet.next());

            final long date = resultSet.getTimestamp("eventDate").getTime();
            assertTrue("The date should be later than pre-logging (1).", date >= millis);
            assertTrue("The date should be earlier than now (1).", date <= System.currentTimeMillis());
            assertEquals("The literal column is not correct (1).", "Literal Value of Data Source",
                    resultSet.getString("literalColumn"));
            assertEquals("The level column is not correct (1).", "FATAL", resultSet.getNString("level"));
            assertEquals("The logger column is not correct (1).", logger.getName(), resultSet.getNString("logger"));
            assertEquals("The message column is not correct (1).", "Error from data source 02.",
                    resultSet.getString("message"));
            assertEquals("The exception column is not correct (1).", stackTrace,
                    IOUtils.readStringAndClose(resultSet.getNClob("exception").getCharacterStream(), -1));

            assertFalse("There should not be two rows.", resultSet.next());
        }
    }
}
 

protected void executeReloadPublicContentReferences(Content content, Connection conn) throws Throwable {
	super.deleteRecordsByEntityId(content.getId(), DELETE_CONTENT_SEARCH_RECORD, conn);
	super.deleteRecordsByEntityId(content.getId(), DELETE_CONTENT_REL_RECORD, conn);
	super.deleteRecordsByEntityId(content.getId(), DELETE_ATTRIBUTE_ROLE_RECORD, conn);
	this.addPublicContentSearchRecord(content.getId(), content, conn);
	this.addContentRelationsRecord(content, conn);
	this.addContentAttributeRoleRecord(content.getId(), content, ADD_ATTRIBUTE_ROLE_RECORD, conn);
}
 

@Before
public void before() throws Exception {
  Mango mango = Mango.newInstance();
  for (int i = 0; i < 4; i++) {
    DataSource ds = DataSourceConfig.getDataSource(i + 1);
    Connection conn = ds.getConnection();
    Table.ORDER_PARTITION.load(conn);
    conn.close();
    mango.addDataSourceFactory(new SimpleDataSourceFactory(dsns[i], ds));
  }
  orderDao = mango.create(OrderDao.class);
}
 

protected boolean queryGfxdOnly(Connection gConn){
  try {
    return super.queryGfxdOnly(gConn);
  } catch (TestException te) {
    if (te.getMessage().contains("X0Z02") && !reproduce49935 ) {
      Log.getLogWriter().info("hit #49935, continuing test");
      return false;
    }
     else throw te;
  }
}
 

private void testBug65871_testCatalogs(Connection conn1) throws Exception {
    testBug65871_testCatalog("db1`testbug65871", StringUtils.quoteIdentifier("db1`testbug65871", ((ConnectionProperties) conn1).getPedantic()), conn1);

    testBug65871_testCatalog("db2`testbug65871", StringUtils.quoteIdentifier("db2`testbug65871", "\"", ((ConnectionProperties) conn1).getPedantic()),
            conn1);

    testBug65871_testCatalog("`db3`testbug65871`", StringUtils.quoteIdentifier("`db3`testbug65871`", "\"", ((ConnectionProperties) conn1).getPedantic()),
            conn1);
}
 

private void assertExecuteForPreparedStatement(final Connection connection) throws SQLException, ParseException, JAXBException, IOException {
    try (PreparedStatement preparedStatement = connection.prepareStatement(getSql())) {
        for (SQLValue each : assertion.getSQLValues()) {
            preparedStatement.setObject(each.getIndex(), each.getValue());
        }
        assertTrue("Not a DQL statement.", preparedStatement.execute());
        try (ResultSet resultSet = preparedStatement.getResultSet()) {
            assertResultSet(resultSet);
        }
    }
}
 

@Override
public void execute(Database database) throws CustomChangeException {
    Connection conn = ((JdbcConnection) (database.getConnection())).getWrappedConnection();
    try {
        conn.setAutoCommit(false);
        List<InstallationData> list = new ArrayList<>();
        String query = "select installation.id as installation_id," +
                " installation.variant_id as installation_variant_id," +
                " variant.id as variant_id," +
                " variant.api_key as variant_api_key" +
                " from installation join variant on installation.variant_id = variant.api_key";
        PreparedStatement statement = conn.prepareStatement(query);
        ResultSet rs = statement.executeQuery();
        while (rs.next()) {
            String installationId = rs.getString("installation_id");
            String installationVariantId = rs.getString("installation_variant_id");
            String variantId = rs.getString("variant_id");
            String variantApiKey = rs.getString("variant_api_key");
            list.add(new InstallationData(installationId,variantId));
        }
        String update = "update installation" +
                " set variant_id = ?" +
                " where id = ?";
        PreparedStatement updateInstallationsStatement = conn.prepareStatement(update);
        for (InstallationData data: list) {
            updateInstallationsStatement.setString(1, data.variantId);
            updateInstallationsStatement.setString(2, data.installationId);
            updateInstallationsStatement.executeUpdate();
        }

        conn.commit();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}
 

@SuppressWarnings("unchecked")
@Override
public void initSpatialMetadata( String options ) throws Exception {
    if (!wasInitialized) {
        Connection jdbcConnection = getJdbcConnection();

        if (jdbcConnection instanceof PGConnection) {
            // FIXME how to enter in pooled mode
            PGConnection pgconn = (PGConnection) jdbcConnection;
            pgconn.addDataType("geometry", (Class< ? extends PGobject>) Class.forName("org.postgis.PGgeometry"));
            pgconn.addDataType("box3d", (Class< ? extends PGobject>) Class.forName("org.postgis.PGbox3d"));
            pgconn.addDataType("box2d", (Class< ? extends PGobject>) Class.forName("org.postgis.PGbox2d"));
        }
        wasInitialized = true;
    }
}