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

The following examples show how to use java.sql.Connection#createStatement() . 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
Source Project: phoenix   File: BaseAggregateIT.java    License: Apache License 2.0 6 votes vote down vote up
@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();
}
 
Example 2
@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);


}
 
Example 3
Source Project: phoenix   File: DeleteIT.java    License: Apache License 2.0 5 votes vote down vote up
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);

}
 
Example 4
/**
 * 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;
}
 
Example 5
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();
}
 
Example 6
Source Project: phoenix   File: QueryMoreIT.java    License: Apache License 2.0 5 votes vote down vote up
@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));
    }
}
 
Example 7
Source Project: phoenix   File: UserDefinedFunctionsIT.java    License: Apache License 2.0 5 votes vote down vote up
@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());
}
 
Example 8
/**
 * @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;
}
 
Example 9
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());
}
 
Example 10
Source Project: gemfirexd-oss   File: streamingColumn.java    License: Apache License 2.0 5 votes vote down vote up
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;
}
 
Example 11
/**
     * 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;
        }
    }
 
Example 12
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();
}
 
Example 13
Source Project: dependency-track   File: v370Updater.java    License: Apache License 2.0 5 votes vote down vote up
@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);
}
 
Example 14
Source Project: sqlg   File: VertexLabel.java    License: MIT License 4 votes vote down vote up
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);
    }
}
 
Example 15
Source Project: gemfirexd-oss   File: dblook_test.java    License: Apache License 2.0 4 votes vote down vote up
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;

}
 
Example 16
private static void execute(final Connection connection, final String sql) throws SQLException {
    final Statement statement = connection.createStatement();
    statement.executeUpdate(sql);
    statement.close();
    connection.close();
}
 
Example 17
Source Project: gemfirexd-oss   File: TransactionDUnit.java    License: Apache License 2.0 4 votes vote down vote up
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();
}
 
Example 18
@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()]));
}
 
Example 19
Source Project: hadoop-ozone   File: SQLCLI.java    License: Apache License 2.0 4 votes vote down vote up
private void executeSQL(Connection conn, String sql) throws SQLException {
  try (Statement stmt = conn.createStatement()) {
    stmt.executeUpdate(sql);
  }
}
 
Example 20
/**
 * 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);
}