下面列出了java.sql.Connection#createStatement ( ) 实例代码,或者点击链接到github查看源代码,也可以在右侧发表评论。
@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();
}
@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);
}
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);
}
/**
* 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 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();
}
@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));
}
}
@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());
}
/**
* @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;
}
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());
}
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;
}
/**
* 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;
}
}
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();
}
@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);
}
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);
}
}
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;
}
private static void execute(final Connection connection, final String sql) throws SQLException {
final Statement statement = connection.createStatement();
statement.executeUpdate(sql);
statement.close();
connection.close();
}
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();
}
@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);
}