Java Code Examples for java.sql.Connection#createStatement()

@Test
public void testCount() throws Exception {
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    String tableName = generateUniqueName();
    initData(conn, tableName);
    Statement stmt = conn.createStatement();
    QueryBuilder queryBuilder = new QueryBuilder()
        .setSelectExpression("count(1)")
        .setFullTableName(tableName);
    ResultSet rs = executeQuery(conn, queryBuilder);
    assertTrue(rs.next());
    assertEquals(8, rs.getLong(1));
    assertFalse(rs.next());
    conn.close();
}
 

private void testDeleteByFilterAndRow(boolean autoCommit) throws SQLException {
    Connection conn = DriverManager.getConnection(getUrl());
    initTableValues(conn);

    assertTableCount(conn, "IntIntKeyTest", NUMBER_OF_ROWS);

    conn.setAutoCommit(autoCommit);

    Statement stmt = conn.createStatement();

    // This shouldn't delete anything, because the key matches but the filter doesn't
    assertEquals(0, stmt.executeUpdate("DELETE FROM IntIntKeyTest WHERE i = 1 AND j = 1"));
    if (!autoCommit) {
        conn.commit();
    }
    assertTableCount(conn, "IntIntKeyTest", NUMBER_OF_ROWS);

    // This shouldn't delete anything, because the filter matches but the key doesn't
    assertEquals(0, stmt.executeUpdate("DELETE FROM IntIntKeyTest WHERE i = -1 AND j = 20"));
    if (!autoCommit) {
        conn.commit();
    }
    assertTableCount(conn, "IntIntKeyTest", NUMBER_OF_ROWS);

    // This should do a delete, because both the filter and key match
    assertEquals(1, stmt.executeUpdate("DELETE FROM IntIntKeyTest WHERE i = 1 AND j = 10"));
    if (!autoCommit) {
        conn.commit();
    }
    assertTableCount(conn, "IntIntKeyTest", NUMBER_OF_ROWS - 1);

}
 

@Test
public void testSaveAssetWithFields() throws ClassNotFoundException, SQLException{

    Class.forName("com.impetus.fabric.jdbc.FabricDriver");
    File configFolder = new File("src/test/resources/blockchain-query");
    String configPath = configFolder.getAbsolutePath();
    Connection conn = DriverManager.getConnection("jdbc:fabric://" + configPath+":mychannel", "impadmin", "impadminpw");
    Statement stat = conn.createStatement();

    //Delete Asset if Exists
    String sqlDelete = "Drop ASSET user_asset1";
    stat.execute(sqlDelete);


    String sqlCreate = "CREATE ASSET user_asset1(a Integer, b String)"
            + " WITH STORAGE TYPE CSV "
            + "FIELDS DELIMITED BY ',' "
            + "RECORDS DELIMITED BY \"\\n\"";
    stat.execute(sqlCreate);


}
 

@Override
public void executeUpgrade(final AlpineQueryManager alpineQueryManager, final Connection connection) throws Exception {
    LOGGER.info("Updating existing components to be non-internal");
    try {
        DbUtil.executeUpdate(connection, STMT_1);
    } catch (Exception e) {
        LOGGER.info("Internal field is likely not boolean. Attempting component internal status update assuming bit field");
        DbUtil.executeUpdate(connection, STMT_1_ALT);
    }

    LOGGER.info("Removing legacy SCAN_UPLOAD permission");
    final Statement q = connection.createStatement();
    final ResultSet rs = q.executeQuery(STMT_2);
    while(rs.next()) {
        final long id = rs.getLong(1);
        LOGGER.info("Removing SCAN_UPLOAD from the TEAMS_PERMISSIONS table");
        DbUtil.executeUpdate(connection, String.format(STMT_3, id));
        LOGGER.info("Removing SCAN_UPLOAD from the LDAPUSERS_PERMISSIONS table");
        DbUtil.executeUpdate(connection, String.format(STMT_4, id));
        LOGGER.info("Removing SCAN_UPLOAD from the MANAGEDUSERS_PERMISSIONS table");
        DbUtil.executeUpdate(connection, String.format(STMT_5, id));
        LOGGER.info("Removing SCAN_UPLOAD from the PERMISSION table");
        DbUtil.executeUpdate(connection, String.format(STMT_6, id));
    }

    LOGGER.info("Removing legacy SCANS_COMPONENTS data");
    DbUtil.executeUpdate(connection, STMT_7);

    LOGGER.info("Removing legacy LAST_SCAN_IMPORTED project dates");
    DbUtil.executeUpdate(connection, STMT_8);

    LOGGER.info("Removing legacy SCAN data");
    DbUtil.executeUpdate(connection, STMT_9);

    LOGGER.info("Removing legacy Dependency-Check configuration settings");
    DbUtil.executeUpdate(connection, STMT_10);
}
 

protected void setUp() throws Exception {
    Connection con = getConnection();
    Statement stmt = con.createStatement();
    stmt.execute ("create table blobtest (id integer, data Blob)");
    stmt.close();
    con.close();
}
 

/**
     * Tests if the connection allows modifying the topic map. The test is done
     * using an update sql statement that does not change anything in the database
     * but should raise an exception if modifying is not allowed. Note that this
     * only tests modifying the topic table and most database implementations
     * allow specifying different privileges for each tables.
     */
    protected boolean testReadOnly() {
        if(connection==null) return true;
        try{
            Connection con=connection;
            Statement stmt=con.createStatement();
            stmt.executeUpdate("UPDATE TOPIC set TOPICID='READONLYTEST' where TOPICID='READONLYTEST';");
            stmt.close();
            return false;
        } 
        catch(SQLException sqle){
//            sqle.printStackTrace();
            return true;
        }
    }
 

static String getLongString(Connection conn, int key) throws Exception {
	Statement s = conn.createStatement();
	ResultSet rs = s.executeQuery("select b from foo where a = " + key);
	if (!rs.next())
		throw new Exception("there weren't any rows for key = " + key);
	String answer = rs.getString(1);
	if (rs.next())
		throw new Exception("there were multiple rows for key = " + key);
	rs.close();
	s.close();
	return answer;
}
 

public void testColumnResolver_5() throws SQLException, StandardException {
  // Create a schema
  Connection conn = getConnection();
  Statement s = conn.createStatement();
  s.execute("create schema trade");

  s
      .execute("create table trade.portfolio (cid int not null,"
          + "qty int not null, availQty int not null, tid int, sid int not null, "
          + "constraint portf_pk primary key (cid, sid), "
          + "constraint qty_ck check (qty>=0), constraint avail_ch check (availQty>=0 and availQty<=qty))"
          + "partition by primary key");

  GfxdPartitionByExpressionResolver cpr = (GfxdPartitionByExpressionResolver)Misc
      .getGemFireCache().getRegion("/TRADE/PORTFOLIO").getAttributes()
      .getPartitionAttributes().getPartitionResolver();

  assertNotNull(cpr);

  String[] sarr = cpr.getColumnNames();
  assertEquals(2, sarr.length);
  assertEquals(0, cpr.getPartitioningColumnIndex("CID"));
  assertEquals(1, cpr.getPartitioningColumnIndex("SID"));
  assertEquals(2, cpr.getPartitioningColumnsCount());
  // Integer robj = (Integer)cpr.getRoutingKeyForColumn(new Integer(5)); //
  // this will give assertion error
  // assertEquals(5, robj.intValue());
  DataValueDescriptor cid = new SQLInteger(6);
  DataValueDescriptor sid = new SQLInteger(1);
  DataValueDescriptor[] values = new DataValueDescriptor[] { cid,
      new SQLInteger(71), new SQLInteger(10), new SQLInteger(100),
      new SQLInteger(1) };
  int hashcode = GfxdPartitionByExpressionResolver.computeHashCode(cid, null,
      0);
  hashcode = GfxdPartitionByExpressionResolver.computeHashCode(sid, null,
      hashcode);
  Integer robj = (Integer)cpr.getRoutingObjectFromDvdArray(values);
  assertEquals(hashcode, robj.intValue());
}
 

/**
 * @description:query id and last modify time from sql
 * @date:2014-5-6 下午5:24:45
 * @version:v1.0
 * @param sql
 * @return
 */
public Map<String, String> queryDataIdAndLastModifyTime(String sql) {
	sql = CynthiaUtil.cancelGroupOrder(sql);
	String[] sqlArray = sql.split("union");  //每个表单独处理，避免union组合将所有表都锁定
	Connection conn = null;
	Statement stat = null;
	ResultSet rs = null;
	Map<String, String> idAndModifyTimeMap = new HashMap<String, String>();
	try
	{
		conn = DbPoolConnection.getInstance().getReadConnection();
		for (String sqlStr:sqlArray) {
			sqlStr += (sqlStr.indexOf("where") != -1 ? " and " : " where ") + " is_valid=1";
			stat = conn.createStatement();
			rs = stat.executeQuery(sqlStr);
			while (rs.next()) {
				idAndModifyTimeMap.put(rs.getString("id"), rs.getString("lastModifyTime"));
			}
		}
	}catch(Exception e){
		e.printStackTrace();
	}finally
	{
		DbPoolConnection.getInstance().closeAll(rs, stat, conn);
	}
	return idAndModifyTimeMap;
}
 

@Test
public void testFunctionalIndexesWithUDFFunction() throws Exception {
    Connection conn = driver.connect(url, EMPTY_PROPS);
    Statement stmt = conn.createStatement();
    stmt.execute("create table t5(k integer primary key, k1 integer, lastname_reverse varchar)");
    stmt.execute("create function myreverse5(VARCHAR) returns VARCHAR as 'org.apache.phoenix.end2end."+MY_REVERSE_CLASS_NAME+"'");
    stmt.execute("upsert into t5 values(1,1,'jock')");
    conn.commit();
    stmt.execute("create index idx on t5(myreverse5(lastname_reverse))");
    String query = "select myreverse5(lastname_reverse) from t5";
    ResultSet rs = stmt.executeQuery("explain " + query);
    assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER IDX\n"
            + "    SERVER FILTER BY FIRST KEY ONLY", QueryUtil.getExplainPlan(rs));
    rs = stmt.executeQuery(query);
    assertTrue(rs.next());
    assertEquals("kcoj", rs.getString(1));
    assertFalse(rs.next());
    stmt.execute("create local index idx2 on t5(myreverse5(lastname_reverse))");
    query = "select k,k1,myreverse5(lastname_reverse) from t5 where myreverse5(lastname_reverse)='kcoj'";
    rs = stmt.executeQuery("explain " + query);
    assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER T5 [1,'kcoj']\n"
            + "    SERVER FILTER BY FIRST KEY ONLY\n"
            +"CLIENT MERGE SORT", QueryUtil.getExplainPlan(rs));
    rs = stmt.executeQuery(query);
    assertTrue(rs.next());
    assertEquals(1, rs.getInt(1));
    assertEquals(1, rs.getInt(2));
    assertEquals("kcoj", rs.getString(3));
    assertFalse(rs.next());
}
 

@SuppressWarnings("deprecation")
@Test
public void testNullBigDecimalWithScale() throws Exception {
    final String table = generateUniqueName();
    final Connection conn = DriverManager.getConnection(getUrl());
    conn.setAutoCommit(true);
    try (Statement stmt = conn.createStatement()) {
        assertFalse(stmt.execute("CREATE TABLE IF NOT EXISTS " + table + " (\n" +
            "PK VARCHAR(15) NOT NULL\n," +
            "\"DEC\" DECIMAL,\n" +
            "CONSTRAINT TABLE_PK PRIMARY KEY (PK))"));
    }

    try (PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + table + " (PK, \"DEC\") VALUES(?, ?)")) {
        stmt.setString(1, "key");
        stmt.setBigDecimal(2, null);
        assertFalse(stmt.execute());
        assertEquals(1, stmt.getUpdateCount());
    }

    try (Statement stmt = conn.createStatement()) {
        final ResultSet rs = stmt.executeQuery("SELECT * FROM " + table);
        assertNotNull(rs);
        assertTrue(rs.next());
        assertEquals("key", rs.getString(1));
        assertNull(rs.getBigDecimal(2));
        assertNull(rs.getBigDecimal(2, 10));
    }
}
 

private void dropTableStltConn(Connection connection) throws SQLException
{
    // This potentially drops various foreign key constraints from other tables
    // Fixed in migration 2019_03_15_FixConstraints
    String dropOldSc =
        "DROP TABLE " + OLD_TBL_SC;
    Statement dropTblStmt = connection.createStatement();
    dropTblStmt.executeUpdate(dropOldSc);
    dropTblStmt.close();
}
 

/**
 * Referred from log4j2-jdbc-appender.xml.
 */
public static Connection getConnectionHSQLDB() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    final Connection connection = DriverManager.getConnection("jdbc:hsqldb:mem:Log4j", "sa", "");
    final Statement statement = connection.createStatement();
    statement.executeUpdate("CREATE TABLE jpaBasicLogEntry ( "
            + "id INTEGER IDENTITY, timemillis BIGINT, level VARCHAR(10), loggerName VARCHAR(255), "
            + "message VARCHAR(1024), thrown VARCHAR(1048576), contextMapJson VARCHAR(1048576),"
            + "loggerFQCN VARCHAR(1024), contextStack VARCHAR(1048576), marker VARCHAR(255), source VARCHAR(2048),"
            + "threadName VARCHAR(255)" + " )");
    statement.close();
    return connection;
}
 

private static void execute(final Connection connection, final String sql) throws SQLException {
    final Statement statement = connection.createStatement();
    statement.executeUpdate(sql);
    statement.close();
    connection.close();
}
 

private void createDBFromDDL(String newDBName,
	String scriptName) throws Exception
{

	System.out.println("\n\nCreating database '" + newDBName +
		"' from ddl script '" + scriptName + "'");

	Connection conn = DriverManager.getConnection(
			"jdbc:derby:" + newDBName + ";create=true" + territoryBased);

	Statement stmt = conn.createStatement();
	BufferedReader ddlScript =
		new BufferedReader(new FileReader(scriptName));

	for (String sqlCmd = ddlScript.readLine(); sqlCmd != null;
		sqlCmd = ddlScript.readLine()) {

		if (sqlCmd.indexOf("--") == 0)
		// then this is a script comment; ignore it;
			continue;
		else if (sqlCmd.trim().length() == 0)
		// blank line; ignore it.
			continue;

		// Execute the command.
		if ((sqlCmd.charAt(sqlCmd.length()-1) == TEST_DELIMITER)
		  || (sqlCmd.charAt(sqlCmd.length()-1) == ';'))
		// strip off the delimiter.
			sqlCmd = sqlCmd.substring(0, sqlCmd.length()-1);

		try {
			stmt.execute(sqlCmd);
		} catch (Exception e) {
			System.out.println("FAILED: to execute cmd " +
				"from DDL script:\n" + sqlCmd + "\n");
			System.out.println(e.getMessage());
		}

	}

	// Cleanup.
	ddlScript.close();
	stmt.close();
	conn.close();

	return;

}
 

public void testNonKeyBasedTransactionalUpdatesRollbackAndCommit()
    throws Exception {
  startVMs(1, 1);
  Connection conn = TestUtil.jdbcConn;
  Statement st = conn.createStatement();
  st.execute("create schema trade");

  st.execute("create table trade.securities (sec_id int not null, "
      + "symbol varchar(10) not null, price decimal (30, 20), "
      + "exchange varchar(10) not null, tid int, "
      + "constraint sec_pk primary key (sec_id) ) "
      + " partition by column (tid) "+ getSuffix());

  conn.setTransactionIsolation(getIsolationLevel());
  conn.setAutoCommit(false);
  
  final int numRows = 5;
  PreparedStatement ps = conn
      .prepareStatement("insert into trade.securities values "
          + "(?, ?, ?, ?, ?)");
  for (int i = 0; i < numRows; i++) {
    ps.setInt(1, i);
    ps.setString(2, "XXXX" + i);
    ps.setDouble(3, i);
    ps.setString(4, "nasdaq");
    ps.setInt(5, i);
    ps.executeUpdate();
  }
  conn.commit();

  PreparedStatement psUpdate = conn
      .prepareStatement("update trade.securities "
          + "set symbol = ? where sec_id = ? and tid = ?");
  for (int i = 0; i < numRows; i++) {
    psUpdate.setString(1, "YYY" + i);
    psUpdate.setInt(2, i);
    psUpdate.setInt(3, i);
    psUpdate.executeUpdate();
  }
  // psUpdate.executeUpdate();
  // InternalDistributedSystem.getAnyInstance().getLogWriter().info("XXXX update is done");
  ResultSet rs = st.executeQuery("select * from trade.securities");
  int numRowsReturned = 0;
  while (rs.next()) {
    assertTrue("Got" + rs.getString("SYMBOL").trim(),
        (rs.getString("SYMBOL").trim()).startsWith("YYY"));
    numRowsReturned++;
  }
  assertEquals("Expected " + numRows + " row but found " + numRowsReturned,
      numRows, numRowsReturned);
  conn.rollback();
  // now commit, should be an empty tran.
  conn.commit();
  rs = st.executeQuery("select * from trade.securities");

  int numUpdates = 0;
  while (rs.next()) {
    assertTrue("Got " + rs.getString("SYMBOL").trim(), rs.getString("SYMBOL")
        .trim().startsWith("XXXX"));
    numUpdates++;
  }
  assertEquals(numRows, numUpdates);
  rs.close();

  for (int i = 0; i < numRows; i++) {
    psUpdate.setString(1, "YYY" + i);
    psUpdate.setInt(2, i);
    psUpdate.setInt(3, i);
    psUpdate.executeUpdate();
  }
  conn.commit();

  // verify.
  rs = st.executeQuery("select * from trade.securities");
  numUpdates = 0;
  while (rs.next()) {
    assertTrue("Got " + rs.getString("SYMBOL").trim(), rs.getString("SYMBOL")
        .trim().startsWith("YYY"));
    numUpdates++;
  }
  assertEquals(numRows, numUpdates);

  conn.commit();
  rs.close();
  st.close();
  psUpdate.close();
  ps.close();
  conn.close();
}
 

private void createVertexLabelOnDb(Map<String, PropertyType> columns, ListOrderedSet<String> identifiers) {
    StringBuilder sql = new StringBuilder(this.sqlgGraph.getSqlDialect().createTableStatement());
    sql.append(this.sqlgGraph.getSqlDialect().maybeWrapInQoutes(this.schema.getName()));
    sql.append(".");
    sql.append(this.sqlgGraph.getSqlDialect().maybeWrapInQoutes(VERTEX_PREFIX + getLabel()));
    sql.append(" (");

    if (identifiers.isEmpty()) {
        sql.append(this.sqlgGraph.getSqlDialect().maybeWrapInQoutes("ID"));
        sql.append(" ");
        sql.append(this.sqlgGraph.getSqlDialect().getAutoIncrementPrimaryKeyConstruct());
        if (columns.size() > 0) {
            sql.append(", ");
        }
    }
    buildColumns(this.sqlgGraph, identifiers, columns, sql);
    if (!identifiers.isEmpty()) {
        sql.append(", PRIMARY KEY(");
        int count = 1;
        for (String identifier : identifiers) {
            sql.append(this.sqlgGraph.getSqlDialect().maybeWrapInQoutes(identifier));
            if (count++ < identifiers.size()) {
                sql.append(", ");
            }
        }
        sql.append(")");
    }
    sql.append(")");
    if (this.sqlgGraph.getSqlDialect().needsSemicolon()) {
        sql.append(";");
    }
    if (logger.isDebugEnabled()) {
        logger.debug(sql.toString());
    }
    Connection conn = this.sqlgGraph.tx().getConnection();
    try (Statement stmt = conn.createStatement()) {
        stmt.execute(sql.toString());
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
}
 

@Override
public String[] getColumnNames(String tableName) {
  Connection c = null;
  Statement s = null;
  ResultSet rs = null;
  List<String> columns = new ArrayList<String>();
  String listColumnsQuery = getListColumnsQuery(tableName);
  try {
    c = getConnection();
    s = c.createStatement();
    rs = s.executeQuery(listColumnsQuery);
    while (rs.next()) {
      columns.add(rs.getString(1));
    }
    c.commit();
  } catch (SQLException sqle) {
    try {
      if (c != null) {
        c.rollback();
      }
    } catch (SQLException ce) {
      LoggingUtils.logAll(LOG, "Failed to rollback transaction", ce);
    }
    LoggingUtils.logAll(LOG, "Failed to list columns from query: "
      + listColumnsQuery, sqle);
    throw new RuntimeException(sqle);
  } finally {
    if (rs != null) {
      try {
        rs.close();
      } catch (SQLException re) {
        LoggingUtils.logAll(LOG, "Failed to close resultset", re);
      }
    }
    if (s != null) {
      try {
        s.close();
      } catch (SQLException se) {
        LoggingUtils.logAll(LOG, "Failed to close statement", se);
      }
    }
  }

  return filterSpecifiedColumnNames(columns.toArray(new String[columns.size()]));
}
 

private void executeSQL(Connection conn, String sql) throws SQLException {
  try (Statement stmt = conn.createStatement()) {
    stmt.executeUpdate(sql);
  }
}
 

/**
 * Assert that a user has references privilege on a given table / column
 * set.
 *
 * @param hasPrivilege whether or not the user has the privilege
 * @param c connection to use
 * @param schema the schema to check
 * @param table the table to check
 * @param columns the set of columns to check
 * @throws SQLException throws all exceptions
 */
private void assertReferencesPrivilege(int hasPrivilege,
                                       Connection c,
                                       String schema,
                                       String table,
                                       String[] columns)
        throws SQLException {

    Statement s = c.createStatement();

    columns = ((columns == null)
               ? getAllColumns(schema, table)
               : columns);

    for (int i = 0; i < columns.length; i++) {
        try {
            s.execute("create table referencestest (c1 int" +
                      " references " + schema + "." +
                      table + "(" + columns[i] + "))" );

            s.execute("drop table referencestest");

            if (hasPrivilege == NOPRIV) {
                fail("Unexpected references privilege. " +
                     formatArgs(c, schema, table,
                                new String[]{columns[i]}));
            }
        } catch (SQLException e) {
            if (hasPrivilege == NOPRIV) {
                assertSQLState(NOCOLUMNPERMISSION, e);
            } else {
                fail("Unexpected lack of references privilege. " +
                     formatArgs(c, schema, table,
                                new String[]{columns[i]}),
                     e);
            }
        }
    }

    s.close();

    assertPrivilegeMetadata
        (hasPrivilege, c, "REFERENCES", schema, table, columns);
}