Java Statement类代码示例

import java.sql.Statement; //导入依赖的package包/类
/**
    * Extract the Large Object Input Stream from PostgreSQL
    * 
    * @param resultSet
    *            the Result Set to extract the blob from
    * @param columnIndex
    *            the index of column
    * @return the Large Object Input Stream from PostgreSQL
    * @throws SQLException
    */
   public static InputStream getPostgreSqlnputStream(ResultSet resultSet,
    int columnIndex) throws SQLException {
InputStream in;
Statement statement = resultSet.getStatement();
Connection conn = statement.getConnection();

// Get the Large Object Manager to perform operations with
LargeObjectManager lobj = ((org.postgresql.PGConnection) conn)
	.getLargeObjectAPI();
long oid = resultSet.getLong(columnIndex);

if (oid < 1) {
    return null;
}

LargeObject obj = lobj.open(oid, LargeObjectManager.READ);

in = obj.getInputStream();
return in;
   }
 

import java.sql.Statement; //导入依赖的package包/类
public static void updateUnidadMedida(String id, String nombre,
		String abrev, String simbolo, String descripcion) {
	Connection conect = ConnectionConfiguration.conectar();
	Statement statement = null;
	String query = "update unidad_medida set ";
	// if (id!="") query+= "id=\""+id+"\", ";
	if (nombre != "")
		query += "nombre=\"" + nombre + "\", ";
	if (abrev != "")
		query += "abrev=\"" + abrev + "\", ";
	if (simbolo != "")
		query += "simbolo=\"" + simbolo + "\", ";
	if (descripcion != "")
		query += "descripcion=\"" + descripcion + "\", ";
	query = query.substring(0, query.length() - 2);
	query += "where id=" + id;

	try {
		statement = conect.createStatement();
		statement.execute(query);
		conect.close();
	} catch (SQLException e) {
		e.printStackTrace();
	}
}
 

import java.sql.Statement; //导入依赖的package包/类
private void initSql() {
	try {
		Connection conn = mockDataSource.getConnection();
		Statement stmt = conn.createStatement();

		List<String> aa = IOUtils.readLines(getClass().getClassLoader().getResourceAsStream("sqlite.sql"));
		String s = StringUtils.join(aa, "\r\n");
		String[] sqls = s.split(";");

		for (int i = 0; i < sqls.length; i++) {
			String sql = sqls[i];
			System.out.println("初始化sql : " + sql);
			stmt.execute(sql);
		}
		stmt.close();
		conn.close();
	} catch (Exception e) {
	}
}
 

import java.sql.Statement; //导入依赖的package包/类
@Test public void testAutomaticLogin() throws Exception {
  final String tableName = "automaticAllowedClients";
  // Avatica should log in for us with this info
  String url = jdbcUrl + ";principal=" + SpnegoTestUtil.CLIENT_PRINCIPAL + ";keytab="
      + clientKeytab;
  LOG.info("Updated JDBC url: {}", url);
  try (Connection conn = DriverManager.getConnection(url);
      Statement stmt = conn.createStatement()) {
    assertFalse(stmt.execute("DROP TABLE IF EXISTS " + tableName));
    assertFalse(stmt.execute("CREATE TABLE " + tableName + "(pk integer)"));
    assertEquals(1, stmt.executeUpdate("INSERT INTO " + tableName + " VALUES(1)"));
    assertEquals(1, stmt.executeUpdate("INSERT INTO " + tableName + " VALUES(2)"));
    assertEquals(1, stmt.executeUpdate("INSERT INTO " + tableName + " VALUES(3)"));

    ResultSet results = stmt.executeQuery("SELECT count(1) FROM " + tableName);
    assertTrue(results.next());
    assertEquals(3, results.getInt(1));
  }
}
 

import java.sql.Statement; //导入依赖的package包/类
public static List<ProductoUnidadMedida> selectAllProductoUnidadMedida(String condition) throws SQLException{
   	 Connection conect=ConnectionConfiguration.conectar();
String query = " select * from producto_unidad_medida "+condition;
		 Statement statement = null;
		 ResultSet rs=null;
		 List<ProductoUnidadMedida> objetos = new ArrayList<ProductoUnidadMedida>();
		 try {
			statement = conect.createStatement();
			rs=statement.executeQuery(query);
			while(rs.next()){
				ProductoUnidadMedida objeto = new ProductoUnidadMedida();
				
				objeto.setUnidadMedidaId(rs.getInt("unidad_medida_id"));
				objeto.setUnidadMedidaNombre(rs.getString("uni_nombre"));
				objetos.add(objeto);
			}
		}
		catch (SQLException e) {e.printStackTrace();}
		finally{
			if (statement != null) {statement.close();}
			if (conect != null) {conect.close();}
		}
		return objetos;
   }
 

import java.sql.Statement; //导入依赖的package包/类
@Test
public void test() throws Exception {
  JdbcAssert
  .withNoDefaultSchema()
  .withConnection(
      new Function<Connection, Void>() {
        public Void apply( Connection connection ) {
          try {
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery( "USE dfs_test.tmp" );
            // TODO:  Purge nextUntilEnd(...) and calls when remaining fragment
            // race conditions are fixed (not just DRILL-2245 fixes).
            // resultSet.close( resultSet );
            statement.close();
            // connection.close() is in withConnection(...)
            return null;
          } catch ( SQLException e ) {
            throw new RuntimeException( e );
          }
        }
      });
}
 

import java.sql.Statement; //导入依赖的package包/类
public static void deleteProyectoSnipAutorizado(String id, String entidad_id, String entidad_nivel_id, String proyecto_snip_id, String organismo_financiador_id, String fuente_financiamiento_id){
 	 Connection conect=ConnectionConfiguration.conectar();
 	 Statement statement = null;
 String 								query = "delete from proyecto_snip_autorizado ";
 //if (id!="")							query+= "id=\""+id+"\", ";
 /*if (nombre!="")						query+= "nombre=\""+nombre+"\", ";
 if (descripcion!="")					query+= "descripcion=\""+descripcion+"\", ";
 if (anho!="")							query+= "anho=\""+anho+"\", ";
 if (monto!="")							query+= "monto=\""+monto+"\", ";
 //if (entidad_id!="")					query+= "entidad_id=\""+entidad_id+"\", ";
 //if (entidad_nivel_id!="")			query+= "entidad_nivel_id=\""+entidad_nivel_id+"\", ";
 //if (proyecto_snip_id!="")			query+= "proyecto_snip_id=\""+proyecto_snip_id+"\", ";
 //if (organismo_financiador_id!="")	query+= "organismo_financiador_id=\""+organismo_financiador_id+"\", ";
 //if (fuente_financiamiento_id!="")	query+= "fuente_financiamiento_id=\""+fuente_financiamiento_id+"\", ";
 query = query.substring(0, query.length()-2);*/
 query+="where id="+id+" and entidad_id="+entidad_id+" and entidad_nivel_id="+entidad_nivel_id+" and proyecto_snip_id="+proyecto_snip_id+" and organismo_financiador_id="+organismo_financiador_id+" and fuente_financiamiento_id="+fuente_financiamiento_id;
		
try {
	statement=conect.createStatement();
	statement.execute(query);
    conect.close();
} catch (SQLException e) {e.printStackTrace();}
 }
 

import java.sql.Statement; //导入依赖的package包/类
@Test
public void shouldApplyMigrations() throws Exception {

    try (DrinkWaterApplication app = DrinkWaterApplication.create(options().use(TestMigrationConfiguration.class).autoStart())) {

        EmbeddedPostgresDataStore store = app.getStore("test");
        store.executeNoQuery("INSERT INTO contact(id, first_name, last_name) VALUES (2 , 'Jean-Marc', 'Canon');");

        try (Connection c = store.getConnection()) {
            Statement s = c.createStatement();
            ResultSet rs = s.executeQuery("SELECT * from contact");
            assertTrue(rs.next());
            assertEquals(2, rs.getInt(1));
            assertFalse(rs.next());
        }
    }
}
 

import java.sql.Statement; //导入依赖的package包/类
private static void deleteDynamicDeploymentSpecs(Statement stmt) throws SQLException{
    // for any given VS/Region/Tenant, having both static and dynamic deployment specs,
    // delete the dynamic deployment spec
    String sql = "   DELETE FROM DEPLOYMENT_SPEC ds WHERE EXISTS   "  +
                 "   (  "  +
                 "       select * from (  "  +
                 "         select ds1.ID, ds1.region, ds1.tenant_id, ds1.vs_fk, ds1.DYNAMIC, sel_cnt.countByVSRegTenant  from DEPLOYMENT_SPEC AS ds1   "  +
                 "         JOIN   "  +
                 "         (  "  +
                 "             select region, tenant_id, vs_fk, count(*) as countByVSRegTenant from DEPLOYMENT_SPEC  group by region, tenant_id, vs_fk  "  +
                 "         ) AS sel_cnt   "  +
                 "         ON ds1.region = sel_cnt.region and ds1.tenant_id = sel_cnt.tenant_id and ds1.vs_fk = sel_cnt.vs_fk  "  +
                 "       )  AS sel  "  +
                 "       WHERE ds.region = sel.region AND ds.tenant_id = sel.tenant_id AND ds.vs_fk = sel.vs_fk AND sel.countByVSRegTenant =2 and ds.DYNAMIC=TRUE"  +
                 "  ) ; ";
    execSql(stmt, sql);
}
 

import java.sql.Statement; //导入依赖的package包/类
private static void setFKColumnsNull(Connection conn, String user,
        String tableName) throws SQLException {
    ResultSet rs = conn.getMetaData()
            .getExportedKeys(null, null, tableName);
    Statement stmt = conn.createStatement();
    while (rs.next()) {
        String sourceTableName = rs.getString("FKTABLE_NAME");
        String columnName = rs.getString("FKCOLUMN_NAME");
        ResultSet columns = conn.getMetaData().getColumns(null, user,
                sourceTableName, columnName);
        while (columns.next()) {
            if (columns.getInt("NULLABLE") != DatabaseMetaData.columnNoNulls) {
                String queryString = String.format(
                        "UPDATE %s SET %s = NULL", sourceTableName,
                        columnName);
                stmt.executeUpdate(queryString);
            }
        }
    }
}
 

import java.sql.Statement; //导入依赖的package包/类
public void testChangeUser() throws Exception {
    Properties props = getPropertiesFromTestsuiteUrl();

    Connection testConn = getConnectionWithProps(props);
    Statement testStmt = testConn.createStatement();

    for (int i = 0; i < 500; i++) {
        ((com.mysql.jdbc.Connection) testConn).changeUser(props.getProperty(NonRegisteringDriver.USER_PROPERTY_KEY),
                props.getProperty(NonRegisteringDriver.PASSWORD_PROPERTY_KEY));

        if (i % 10 == 0) {
            try {
                ((com.mysql.jdbc.Connection) testConn).changeUser("bubba", props.getProperty(NonRegisteringDriver.PASSWORD_PROPERTY_KEY));
            } catch (SQLException sqlEx) {
                if (versionMeetsMinimum(5, 6, 13)) {
                    assertTrue(testConn.isClosed());
                    testConn = getConnectionWithProps(props);
                    testStmt = testConn.createStatement();
                }
            }
        }

        this.rs = testStmt.executeQuery("SELECT 1");
    }
    testConn.close();
}
 

import java.sql.Statement; //导入依赖的package包/类
/**
 * Returns true if the specified database already exists on the DBMS.
 * @param aDatabaseName
 * @return
 */
public boolean databaseAlreadyExists(String aDatabaseName) {
	aDatabaseName = aDatabaseName.toLowerCase();
	
	try {
		Statement statement = connection.createStatement();
		ResultSet rs = statement.executeQuery(
				"SELECT datname FROM pg_catalog.pg_database WHERE datname='" + aDatabaseName + "'");
		
		if (!rs.next()) {
			return false;
		} else {
			return true;
		}
		
	} catch (SQLException e) {
		e.printStackTrace();
	}
	
	return false;
}
 

import java.sql.Statement; //导入依赖的package包/类
Connection getConnection() throws SQLException {
      DBHostConfig cfg = getConfig();
Connection connection = DriverManager.getConnection(cfg.getUrl(), cfg.getUser(), cfg.getPassword());
String initSql=getHostConfig().getConnectionInitSql();
if (initSql != null && !"".equals(initSql)) {
	Statement statement = null;
	try {
		statement = connection.createStatement();
		statement.execute(initSql);
	} finally {
		if (statement != null) {
			statement.close();
		}
	}
}
return connection;
  }
 

import java.sql.Statement; //导入依赖的package包/类
/**
 * Checks the blob type columns have the correct type
 *
 * @throws SQLException not really thrown
 */
@Test
public void testBlobType() throws SQLException {

  ConnectionResourcesBean databaseConnection = new ConnectionResourcesBean();
  databaseConnection.setDatabaseName("database");
  databaseConnection.setDatabaseType(H2.IDENTIFIER);
  Connection connection = databaseConnection.getDataSource().getConnection();

  try {
    Statement createStatement = connection.createStatement();
    createStatement.execute("CREATE TABLE test ( BLOBCOL longvarbinary )");

    DatabaseMetaDataProvider provider = new DatabaseMetaDataProvider(connection, null);

    Table table = provider.getTable("test");
    assertEquals("Exactly 1 column expected", 1, table.columns().size());
    assertEquals("Column name not correct", "BLOBCOL", table.columns().get(0).getName());
    assertEquals("Column type not correct", DataType.BLOB, table.columns().get(0).getType());
    assertEquals("Column width not correct", 2147483647, table.columns().get(0).getWidth());
    assertEquals("Column scale not correct", 0, table.columns().get(0).getScale());
  } finally {
    connection.close();
  }
}
 

import java.sql.Statement; //导入依赖的package包/类
/**
 * Executes a query containing the clause LIMIT with a Statement and a PreparedStatement, using a combination of
 * Connection properties, maxRows value and limit clause value, and tests if the results count is the expected.
 */
private void testBug71396MultiSettingsCheck(String connProps, int maxRows, int limitClause, int expRowCount) throws SQLException {
    Connection testConn = getConnectionWithProps(connProps);

    Statement testStmt = testConn.createStatement();
    if (maxRows > 0) {
        testStmt.setMaxRows(maxRows);
    }
    testStmt.execute("SELECT 1"); // force limit to be applied into current session

    testBug71396StatementCheck(testStmt, String.format("SELECT * FROM testBug71396 LIMIT %d", limitClause), expRowCount);
    testBug71396PrepStatementCheck(testConn, String.format("SELECT * FROM testBug71396 LIMIT %d", limitClause), expRowCount, maxRows);

    testStmt.close();
    testConn.close();
}