Java PreparedStatement.close方法代码示例

import java.sql.PreparedStatement; //导入方法依赖的package包/类
@Test
public void testPreparedStatementCache() throws Exception {
    config(true,false,100);
    Connection con = datasource.getConnection();
    PreparedStatement ps1 = con.prepareStatement("select 1");
    PreparedStatement ps2 = con.prepareStatement("select 1");
    Assert.assertEquals(0,interceptor.getCacheSize().get());
    ps1.close();
    Assert.assertTrue(ps1.isClosed());
    Assert.assertEquals(1,interceptor.getCacheSize().get());
    PreparedStatement ps3 = con.prepareStatement("select 1");
    Assert.assertEquals(0,interceptor.getCacheSize().get());
    ps2.close();
    Assert.assertTrue(ps2.isClosed());
    ps3.close();
    Assert.assertTrue(ps3.isClosed());
    Assert.assertEquals(1,interceptor.getCacheSize().get());
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
 * Test Bug#77217 - ClassCastException when executing a PreparedStatement with Fabric (using a streaming result with timeout set)
 */
public void testBug77217() throws Exception {
    if (!this.isSetForFabricTest) {
        return;
    }

    this.conn = (FabricMySQLConnection) getNewDefaultDataSource().getConnection(this.username, this.password);
    this.conn.setServerGroupName("ha_config1_group");

    PreparedStatement ps = this.conn.prepareStatement("select ? from dual");
    ps.setFetchSize(Integer.MIN_VALUE);
    ps.setString(1, "abc");
    ResultSet rs = ps.executeQuery();
    rs.next();
    assertEquals("abc", rs.getString(1));
    rs.close();
    ps.close();
    this.conn.close();
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
public static String getOracleObjectType(Connection connection,
    OracleTable table) throws SQLException {

  PreparedStatement statement =
      connection.prepareStatement("SELECT object_type " + " FROM dba_objects"
          + " WHERE owner = ?" + " and object_name = ?");
  statement.setString(1, table.getSchema());
  statement.setString(2, table.getName());

  ResultSet resultSet = statement.executeQuery();

  String result = null;
  if (resultSet.next()) {
    result = resultSet.getString("object_type");
  }

  resultSet.close();
  statement.close();

  return result;
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
public static boolean tempban(String reason, Calendar duration, int greason, int accountid) {
    try {
        Connection con = DatabaseConnection.getConnection();
        PreparedStatement ps = con.prepareStatement("UPDATE accounts SET tempban = ?, banreason = ?, greason = ? WHERE id = ?");
        Timestamp TS = new Timestamp(duration.getTimeInMillis());
        ps.setTimestamp(1, TS);
        ps.setString(2, reason);
        ps.setInt(3, greason);
        ps.setInt(4, accountid);
        ps.executeUpdate();
        ps.close();
        return true;
    } catch (SQLException ex) {
        ex.printStackTrace();
        //log.error("Error while tempbanning", ex);
    }
    return false;
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
@Override
public AcademicInstitute getAcademicInstituteById(int id) throws SQLException {
	AcademicInstitute a = null;
	PreparedStatement stm = c.prepareStatement(SQLStatements.selectAcademicInstituteId);
	stm.setInt(1, id);
	ResultSet rs = stm.executeQuery();
	if (rs.next()) {
		City city = getCityById(rs.getInt(DataContract.AcademicInstituteTable.COL_CITY));
		Area area = getAreaById(rs.getInt(DataContract.AcademicInstituteTable.COL_AREA));
		a = new AcademicInstitute(rs.getInt(DataContract.AcademicInstituteTable.COL_ID),
				rs.getString(DataContract.AcademicInstituteTable.COL_NAME), area.getName(), city.getName(),
				area.getId(), city.getId());
	}
	rs.close();
	stm.close();

	return a;
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
private void insertTestData() throws Exception {

//        conn.createStatement().executeUpdate("INSERT INTO \"SITE_LIST_SITE\" VALUES(23,1,'ru','RU',NULL,'\u041f\u0440\u043e\u0431\u043d\u044b\u0439 \u0441\u0430\u0439\u0442',NULL,0,'''/front_styles.css''',1,NULL,0)");
//        conn.createStatement().executeUpdate("INSERT INTO \"SITE_VIRTUAL_HOST\" VALUES(36,23,'test-host')");
//        conn.createStatement().executeUpdate("INSERT INTO \"SITE_SUPPORT_LANGUAGE\" VALUES(115,23,1,'ru_RU','ru_RU')");
//        conn.createStatement().executeUpdate("INSERT INTO \"CASH_CURRENCY\" VALUES(134,'\u0420\u0443\u0431',1,'\u0420\u0443\u0431',0,3,23,0.0)");
//        conn.createStatement().executeUpdate("INSERT INTO \"CASH_CURRENCY\" VALUES(135,'EURO',1,'EURO',0,7,23,0.0)");
        conn.createStatement().executeUpdate(
            "INSERT INTO \"CASH_CURRENCY\" VALUES(134,23)");
        conn.createStatement().executeUpdate(
            "INSERT INTO \"CASH_CURRENCY\" VALUES(135,23)");

        PreparedStatement ps = conn.prepareStatement("insert into "
            + nameTable + "(T, ID) values (?, ?)");

        ps.setTimestamp(1, testTS);
        ps.setLong(2, id);
        ps.executeUpdate();
        ps.close();

        ps = null;

        conn.commit();
    }
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
@Override
public boolean exePreparedSQLDrop(String sql, PreparedParam preparedParam) {
	// TODO Auto-generated method stub
	ConnectionObject conn = ConnectionManager.borrowConnectionObject(connInfo);
	try 
	{
		PreparedStatement preStmt = conn.getConnection().prepareStatement(sql);
		SQLPreparedParamUtil.setSQLPreparedParam(preStmt, preparedParam);
		preStmt.execute();
		preStmt.close();
		return true;
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
		DBCException.logException(DBCException.E_SQL, e);
		return false;
	}
	finally
	{
		ConnectionManager.returnConnectionObject(conn);
	}
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
@Test
public void testgetPrimaryKeyFromTable() {
  // first, create a table with a primary key
  Connection conn = null;
  try {
    conn = getManager().getConnection();
    dropTableIfExists(TABLE_WITH_KEY);
    PreparedStatement statement = conn.prepareStatement("CREATE TABLE "
        + TABLE_WITH_KEY + "(" + KEY_FIELD_NAME
        + " INT NOT NULL PRIMARY KEY, foo INT)",
        ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
    statement.executeUpdate();
    statement.close();

    String primaryKey = getManager().getPrimaryKey(TABLE_WITH_KEY);
    assertEquals("Expected null pkey for table without key",
        primaryKey, KEY_FIELD_NAME);

  } catch (SQLException sqlException) {
    LOG.error(StringUtils.stringifyException(sqlException));
    fail("Could not create table with primary key: "
        + sqlException.toString());
  } finally {
    if (null != conn) {
      try {
        conn.close();
      } catch (SQLException sqlE) {
        LOG.warn("Got SQLException during close: "
            + sqlE.toString());
      }
    }
  }

}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
@Override
public void run(OrgaoOrgaoUnidadeUnidadePendenciasGetRequest req,
		OrgaoOrgaoUnidadeUnidadePendenciasGetResponse resp)
		throws Exception {
	resp.list = new ArrayList<>();

	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rset = null;
	try {
		conn = Utils.getConnection();
		pstmt = conn.prepareStatement(Utils.getSQL("pendencias"));
		pstmt.setInt(1, Integer.valueOf(req.orgao));
		pstmt.setInt(2, Integer.valueOf(req.unidade));
		rset = pstmt.executeQuery();

		while (rset.next()) {
			Indicador o = new Indicador();
			o.nome = rset.getString("NOME");
			o.descricao = rset.getString("DESCRICAO");
			o.valor = rset.getDouble("VALOR");
			o.memoriaDeCalculo = rset.getString("MEMORIA_DE_CALCULO");
			resp.list.add(o);
		}
	} finally {
		if (rset != null)
			rset.close();
		if (pstmt != null)
			pstmt.close();
		if (conn != null)
			conn.close();
	}

}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
 * @param threadConn
 * @param threadStmt
 * @param threadNumber
 */
void contentiousWork(Connection threadConn, Statement threadStmt, int threadNumber) {
    Date now = new Date();

    try {
        for (int i = 0; i < 1000; i++) {
            ResultSet threadRs = threadStmt.executeQuery("SELECT 1, 2");

            while (threadRs.next()) {
                threadRs.getString(1);
                threadRs.getString(2);
            }

            threadRs.close();

            PreparedStatement pStmt = threadConn.prepareStatement("SELECT ?");
            pStmt.setTimestamp(1, new Timestamp(now.getTime()));

            threadRs = pStmt.executeQuery();

            while (threadRs.next()) {
                threadRs.getTimestamp(1);
            }

            threadRs.close();
            pStmt.close();
        }
    } catch (Exception ex) {
        throw new RuntimeException(ex.toString());
    }
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
@Override
protected void dropTableIfExists(String table) throws SQLException {
  Connection conn = getManager().getConnection();
  PreparedStatement statement = conn.prepareStatement(
      "DROP TABLE IF EXISTS " + table,
      ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
  try {
    statement.executeUpdate();
    conn.commit();
  } finally {
    statement.close();
  }
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
public void saveInTransaction(Object object, final boolean cached)
        throws SQLException {
    Class clazz = object.getClass();
    PersistentRow row = rowHash.get(clazz);
    PersistentWrapper wrapper = rowWrappers.get(clazz);
    List<PersistentField> fields = row.getFields();

    Object[] objects = new Object[fields.size()];

    StringBuffer insert = new StringBuffer("INSERT INTO "
            + row.getTableName() + " (");
    StringBuffer values = new StringBuffer(") VALUES (");

    boolean first = true;

    for (int i = 0; i < objects.length; i++) {
        PersistentField field = fields.get(i);
        if (first)
            first = false;
        else {
            insert.append(", ");
            values.append(", ");
        }
        insert.append(field.getDatabaseName());
        values.append('?');
        objects[i] = wrapper.getField(object, field.getName());
    }
    String sql = insert.toString() + values.toString() + ")";
    PreparedStatement ps = template.getPreparedStatement(sql, cached);
    synchronized (ps) {
        template.setParams(ps, objects);
        if (!cached)
            ps.close();
        ps.execute();
    }
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
@Override
public void execute (Database database) throws CustomChangeException
{
   JdbcConnection databaseConnection =
      (JdbcConnection) database.getConnection ();
   try
   {         
      PreparedStatement getSearches =
         databaseConnection.prepareStatement ("SELECT ID FROM SEARCHES");
      ResultSet res = getSearches.executeQuery ();
      while (res.next ())
      {
         String uuid = UUID.randomUUID ().toString ();
         PreparedStatement updateSearch =
            databaseConnection
               .prepareStatement ("UPDATE SEARCHES SET UUID = '" + uuid +
                  "' WHERE ID = " + res.getObject ("ID"));
         updateSearch.execute ();
         updateSearch.close ();

         PreparedStatement updateAdvanced =
            databaseConnection
               .prepareStatement ("UPDATE SEARCH_ADVANCED SET SEARCH_UUID = '" +
                  uuid + "' WHERE SEARCH_ID = " + res.getObject ("ID"));
         updateAdvanced.execute ();
         updateAdvanced.close ();

         PreparedStatement updatePreferences =
            databaseConnection
               .prepareStatement ("UPDATE SEARCH_PREFERENCES SET SEARCHES_UUID = '" +
                  uuid + "' WHERE SEARCHES_ID = " + res.getObject ("ID"));
         updatePreferences.execute ();
         updatePreferences.close ();
      }
      getSearches.close ();
   }
   catch (Exception e)
   {
      e.printStackTrace ();
   }
   
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
 * Tests fix for Bug#23197238 - EXECUTEQUERY() FAILS FOR JSON DATA WHEN RESULTSETCONCURRENCY=CONCUR_UPDATABLE.
 */
public void testBug23197238() throws Exception {
    if (!versionMeetsMinimum(5, 7, 9)) {
        return;
    }

    createTable("testBug23197238", "(id INT AUTO_INCREMENT PRIMARY KEY, doc JSON DEFAULT NULL)");

    String[] docs = new String[] { "{\"key1\": \"value1\"}", "{\"key2\": \"value2\"}", "{\"key3\": \"value3\"}" };
    Connection testConn = getConnectionWithProps("useCursorFetch=true");

    Statement testStmt = testConn.createStatement();
    testStmt.execute("INSERT INTO testBug23197238 (doc) VALUES ('" + docs[2] + "')");
    testStmt.close();

    testBug23197238Assert(new String[] { docs[2] });

    testStmt = testConn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
    this.rs = testStmt.executeQuery("SELECT * FROM testBug23197238");
    assertTrue(this.rs.next());
    this.rs.updateObject(2, docs[1]);
    this.rs.updateRow();
    this.rs.moveToInsertRow();
    this.rs.updateObject(2, docs[1]);
    this.rs.insertRow();
    testStmt.close();

    testBug23197238Assert(new String[] { docs[1], docs[1] });

    PreparedStatement testPstmt = testConn.prepareStatement("SELECT * FROM testBug23197238 WHERE id = ?", ResultSet.TYPE_FORWARD_ONLY,
            ResultSet.CONCUR_UPDATABLE);
    testPstmt.setObject(1, 1, Types.INTEGER);
    this.rs = testPstmt.executeQuery();
    assertTrue(this.rs.next());
    this.rs.updateObject(2, docs[0]);
    this.rs.updateRow();
    this.rs.moveToInsertRow();
    this.rs.updateObject(2, docs[2]);
    this.rs.insertRow();
    testPstmt.close();

    testBug23197238Assert(docs);

    testConn.close();
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
public void testBasicDefaultTimeSupport() throws Throwable {

        final String INSERT_TIME =
            "insert into time_test(time_test) values (?)";

        // See OracleTests class why we need to select tablename.*
        final String SELECT_TIME =
            "select time_test.* from time_test where time_test = ?";
        final String DELETE_TIME = "delete from time_test where time_test = ?";
        java.sql.Time     insertTime;
        Connection        connection = super.newConnection();
        PreparedStatement insertStatement;
        int               iUpdateCount = 0;

        insertTime      = new java.sql.Time(3600000);
        insertStatement = connection.prepareStatement(INSERT_TIME);

        insertStatement.setTime(1, insertTime);

        iUpdateCount = insertStatement.executeUpdate();

        insertStatement.close();
        Assert.assertEquals(
            "Exactly one record with time data shoud have been inserted.",
            iUpdateCount, 1);

        // Now select it back to be sure it is there
        PreparedStatement selectStatement = null;
        PreparedStatement deleteStatement = null;
        ResultSet         results         = null;
        java.sql.Time     retrievedTime;
        int               iDeletedCount = 0;
        java.sql.Time     selectTime;

        selectStatement = connection.prepareStatement(SELECT_TIME);
        selectTime      = new java.sql.Time(3600000);

        selectStatement.setTime(1, selectTime);

        results = selectStatement.executeQuery();

        // Get the date from the database
        Assert.assertTrue("The inserted time is not in the database.",
                          results.next());

        retrievedTime = results.getTime(1);

        //
        deleteStatement = connection.prepareStatement(DELETE_TIME);

        deleteStatement.setTime(1, insertTime);

        iDeletedCount = deleteStatement.executeUpdate();

        Assert.assertEquals(
            "Exactly one record with time data shoud have been deleted.",
            iDeletedCount, 1);

        // And now test the date
        Assert.assertNotNull(
            "The inserted time shouldn't be retrieved as null from the database",
            retrievedTime);

        // Ignore milliseconds when comparing dates
        String  selectString    = selectTime.toString();
        String  retrievedString = retrievedTime.toString();
        boolean result          = retrievedString.equals(selectString);

        Assert.assertTrue(
            "The time retrieved from database "
            + DateFormat.getDateTimeInstance().format(retrievedTime)
            + " is not the same as the inserted one "
            + DateFormat.getDateTimeInstance().format(insertTime), result);
    }