Java PreparedStatement.getResultSet方法代码示例

import java.sql.PreparedStatement; //导入方法依赖的package包/类
public static ResultSet getDadosCliente(String valor)
{
    ResultSet rs= null;
    try
    {
        System.err.println(valor +" Cliente Selecionado");
        Conexao conexao = new Conexao();
        String sql ="SELECT *from VER_CLIENTE WHERE UPPER(ID) = UPPER( '"+valor+ "')";
        if(conexao.getCon()!=null)
        {
            PreparedStatement ps = conexao.getCon().prepareStatement(sql);
            ps.execute();
            rs = ps.getResultSet();
            rs.next();
        }
    } 
    catch (SQLException ex) 
    {
        Logger.getLogger(ClienteDao.class.getName()).log(Level.SEVERE, null, ex);
    }
    return rs;
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
@Test
  public void testPreparedStatementExecute() throws Exception {
    long expectedCount = getTimerCount(executeStatementName) + 1;

    Connection connection = datasource.getConnection();
    PreparedStatement preparedStatement = connection.prepareStatement(SELECT_QUERY);

    int rnd = ThreadLocalRandom.current().nextInt(0, presetElements.size());
    String key = presetElementKeys.get(rnd);
    Integer value = presetElements.get(key);

    preparedStatement.setString(1, key);

//    ResultSet resultSet = preparedStatement.executeQuery();
    preparedStatement.execute();
    ResultSet resultSet = preparedStatement.getResultSet();
    resultSet.next();
    assertEquals(value.intValue(), resultSet.getInt(2));
    connection.close();

    assertEquals(expectedCount,
        getTimerCount(executeStatementName));
  }
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
public ResultSet getDadosCliente(String valor)
{
    ResultSet rs= null;
    try
    {
        Conexao conexao = new Conexao();
        String sql ="SELECT *from VER_CLIENTE WHERE UPPER(ID) = UPPER( '"+valor+ "')";
        PreparedStatement ps = conexao.getCon().prepareStatement(sql);
        ps.execute();
        rs = ps.getResultSet();
        rs.next();
    } 
    catch (SQLException ex) 
    {
        Logger.getLogger(ClienteDao.class.getName()).log(Level.SEVERE, null, ex);
    }
    return rs; 
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
private long calculateSumDB(String table, int columns) throws Exception {
  long result = 0;
  PreparedStatement ps = prepareStatement("SELECT * FROM " + table);

  ps.execute();
  ResultSet rs = ps.getResultSet();

  while (rs.next()) {
    for (int c = 1; c <= columns; c++) {
      String v = rs.getString(c);
      for (int i = 0; v != null && i < v.length(); i++)
        result += v.charAt(i);
    }
  }

  return result;
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
private void checkPingQuery(Connection c) throws SQLException {
    // Yes, I know we're sending 2, and looking for 1 that's part of the test, since we don't _really_ send the query to the server!
    String aPingQuery = "/* ping */ SELECT 2";
    Statement pingStmt = c.createStatement();
    PreparedStatement pingPStmt = null;

    this.rs = pingStmt.executeQuery(aPingQuery);
    assertTrue(this.rs.next());
    assertEquals(this.rs.getInt(1), 1);

    assertTrue(pingStmt.execute(aPingQuery));
    this.rs = pingStmt.getResultSet();
    assertTrue(this.rs.next());
    assertEquals(this.rs.getInt(1), 1);

    pingPStmt = c.prepareStatement(aPingQuery);

    assertTrue(pingPStmt.execute());
    this.rs = pingPStmt.getResultSet();
    assertTrue(this.rs.next());
    assertEquals(this.rs.getInt(1), 1);

    this.rs = pingPStmt.executeQuery();
    assertTrue(this.rs.next());
    assertEquals(this.rs.getInt(1), 1);

}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
 * Tests fix for BUG#55340 - initializeResultsMetadataFromCache fails on second call to stored proc
 * 
 * @throws Exception
 *             if the test fails.
 */
public void testBug55340() throws Exception {
    Connection testConnCacheRSMD = getConnectionWithProps("cacheResultSetMetadata=true");
    ResultSetMetaData rsmd;

    createTable("testBug55340", "(col1 INT, col2 CHAR(10))");
    createProcedure("testBug55340", "() BEGIN SELECT * FROM testBug55340; END");

    assertEquals(this.stmt.executeUpdate("INSERT INTO testBug55340 (col1, col2) VALUES (1, 'one'), (2, 'two'), (3, 'three')"), 3);

    for (Connection testConn : new Connection[] { this.conn, testConnCacheRSMD }) {
        String testDesc = testConn == testConnCacheRSMD ? "Conn. with 'cacheResultSetMetadata=true'" : "Default connection";

        // bug occurs in 2nd call only
        for (int i = 1; i <= 2; i++) {
            for (PreparedStatement testStmt : new PreparedStatement[] { testConn.prepareStatement("SELECT * FROM testBug55340"),
                    testConn.prepareCall("CALL testBug55340()") }) {

                assertTrue(testStmt.execute());
                this.rs = testStmt.getResultSet();
                assertResultSetLength(this.rs, 3);

                rsmd = this.rs.getMetaData();
                assertEquals("(" + i + ") " + testDesc + " - " + testStmt.getClass().getSimpleName() + ":RSMetaData - wrong column count.", 2,
                        rsmd.getColumnCount());
                assertEquals("(" + i + ") " + testDesc + " - " + testStmt.getClass().getSimpleName() + ":RSMetaData - wrong column(1) type.",
                        Integer.class.getName(), rsmd.getColumnClassName(1));
                assertEquals("(" + i + ") " + testDesc + " - " + testStmt.getClass().getSimpleName() + ":RSMetaData - wrong column(2) type.",
                        String.class.getName(), rsmd.getColumnClassName(2));

                testStmt.close();
            }
        }
    }

    testConnCacheRSMD.close();
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
@Test
  public void testPreparedStatementExecuteError() throws Exception {
    long expectedCount = getTimerCount(executeStatementName);

    Connection connection = datasource.getConnection();
    PreparedStatement preparedStatement = connection.prepareStatement(SELECT_QUERY);

    boolean gotSqlException = false;
    try {
      preparedStatement.execute();
    } catch (SQLException e) {
      gotSqlException = true;
    }
    assertTrue(gotSqlException);
    assertEquals(expectedCount,
        getTimerCount(executeStatementName));

    expectedCount++;
    int rnd = ThreadLocalRandom.current().nextInt(0, presetElements.size());
    String key = presetElementKeys.get(rnd);
    Integer value = presetElements.get(key);

    preparedStatement.setString(1, key);

//    ResultSet resultSet = preparedStatement.executeQuery();
    preparedStatement.execute();
    ResultSet resultSet = preparedStatement.getResultSet();
    resultSet.next();
    assertEquals(value.intValue(), resultSet.getInt(2));
    connection.close();

    assertEquals(expectedCount,
        getTimerCount(executeStatementName));
  }
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
private void reportQuantity(String table) throws Exception {
  PreparedStatement ps;
  ResultSet rs;

  ps = prepareStatement("SELECT COUNT(*) FROM " + table);
  ps.execute();
  rs = ps.getResultSet();
  rs.first();
  System.err.println(table + " #" + rs.getLong(1));
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
/**
 * Executes one query using a PreparedStatement and tests if the results count is the expected.
 */
private void testBug71396PrepStatementCheck(PreparedStatement testPStmt, String query, int expRowCount) throws SQLException {
    ResultSet testRS;

    testRS = testPStmt.executeQuery();
    assertTrue(testRS.last());
    assertEquals(String.format("Wrong number of rows for query '%s'", query), expRowCount, testRS.getRow());
    testRS.close();

    testPStmt.execute();
    testRS = testPStmt.getResultSet();
    assertTrue(testRS.last());
    assertEquals(String.format("Wrong number of rows for query '%s'", query), expRowCount, testRS.getRow());
    testRS.close();
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
public void testPrepareOfMultiRs() throws Exception {
    if (!serverSupportsStoredProcedures()) {
        return;
    }

    createProcedure("p", "() begin select 1; select 2; end;");

    PreparedStatement ps = null;

    try {
        ps = this.conn.prepareStatement("call p()");

        ps.execute();
        this.rs = ps.getResultSet();
        assertTrue(this.rs.next());
        assertEquals(1, this.rs.getInt(1));
        assertTrue(ps.getMoreResults());
        this.rs = ps.getResultSet();
        assertTrue(this.rs.next());
        assertEquals(2, this.rs.getInt(1));
        assertTrue(!ps.getMoreResults());
    } finally {
        if (this.rs != null) {
            this.rs.close();
            this.rs = null;
        }

        if (ps != null) {
            ps.close();
        }
    }

}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
public static String DevolverValorCampo(String campoRetorno,String campoSql, String view, String valor)
{
    String sql;
    String resultado = null;
    PreparedStatement ps;
    ResultSet rs;
    sql = "SELECT "+campoRetorno+" FROM "+view+" WHERE "+campoSql+"=?";
       Conexao conexao = new Conexao();
    try
    {
        if(conexao.getCon()!=null)
        {
            ps = conexao.getCon().prepareStatement(sql);
            ps.setString(1, valor);
            ps.execute();
            rs = ps.getResultSet();
            if( rs!= null)
            {
                while(rs.next())
                {
                    resultado = rs.getString(campoRetorno);
                }
                rs.close();
                conexao.destruir();
            }
        }
        System.out.println("resultado "+resultado);
    }
    catch (SQLException ex)
    {
        Logger.getLogger(ClienteDao.class.getName()).log(Level.SEVERE, null, ex);
    }
    return resultado;
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
@Override
public void execute (Database database) throws CustomChangeException
{
   String rootName = CollectionDao.COLLECTION_ROOT_NAME;
   String rootId = "SELECT UUID FROM COLLECTIONS WHERE NAME='%s'";
   String auth = "DELETE FROM COLLECTION_USER_AUTH WHERE COLLECTIONS_UUID='%s'";
   String delete = "DELETE FROM COLLECTIONS WHERE NAME='%s'";
   try
   {
      String cid = null;
      String sql;
      PreparedStatement statement;
      JdbcConnection connection = (JdbcConnection) database.getConnection ();

      // get root collection id
      sql = String.format (rootId, rootName);
      statement = connection.prepareStatement (sql);
      statement.execute ();
      ResultSet resultSet = statement.getResultSet ();
      if (resultSet.next ())
      {
         cid = resultSet.getString (1);
      }
      statement.close ();

      if (cid != null)
      {
         // remove default authorization on root collection
         sql = String.format (auth, cid);
         statement = connection.prepareStatement (sql);
         statement.execute ();
         statement.close ();

         // delete root collection
         sql = String.format (delete, rootName);
         statement = connection.prepareStatement (sql);
         statement.execute ();
         statement.close ();
      }
   }
   catch (DatabaseException | SQLException e)
   {
      throw new CustomChangeException (e);
   }
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
public static ResultSet getTablesFromCache(final Connection connCache, String catalog, String schemaPattern,
		String tableNamePattern, String[] types) throws SQLException {
	int indexCol = 1;
	String sql = "SELECT * FROM GMETA_TABLES";
	boolean isFirstCondition = true;
	if (catalog != null && catalog.trim().length() != 0) {
		if (isFirstCondition) {
			isFirstCondition = false;
			sql += " WHERE";
		} else {
			sql += " AND";
		}
		sql += " TABLE_CAT LIKE ?";
	}
	if (schemaPattern != null && schemaPattern.trim().length() != 0) {
		if (isFirstCondition) {
			isFirstCondition = false;
			sql += " WHERE";
		} else {
			sql += " AND";
		}
		sql += " TABLE_SCHEM LIKE ?";
	}
	if (tableNamePattern != null && tableNamePattern.trim().length() != 0) {
		if (isFirstCondition) {
			isFirstCondition = false;
			sql += " WHERE";
		} else {
			sql += " AND";
		}
		sql += " TABLE_NAME LIKE ?";
	}
	sql += " ORDER BY TABLE_TYPE, TABLE_CAT, TABLE_SCHEM, TABLE_NAME";

	final PreparedStatement pstmt = connCache.prepareStatement(sql);

	if (catalog != null && catalog.trim().length() != 0) {
		pstmt.setString(indexCol++, catalog);
	}
	if (schemaPattern != null && schemaPattern.trim().length() != 0) {
		pstmt.setString(indexCol++, schemaPattern);
	}
	if (tableNamePattern != null && tableNamePattern.trim().length() != 0) {
		pstmt.setString(indexCol++, tableNamePattern);
	}

	pstmt.executeQuery();
	return pstmt.getResultSet();
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
public static ResultSet getColumnsFromCache(final Connection connCache, String cacheTableName, String catalog,
		String schemaPattern, String tableNamePattern, String columnNamePattern) throws SQLException {
	if (cacheTableName == null) {
		cacheTableName = "GMETA_COLUMNS";
	}

	String sql = "SELECT * FROM " + cacheTableName; //
	boolean isFirstCondition = true;
	if (catalog != null && catalog.trim().length() != 0) {
		if (isFirstCondition) {
			isFirstCondition = false;
			sql += " WHERE";
		} else {
			sql += " AND";
		}
		sql += " TABLE_CAT = ?";
	}
	if (schemaPattern != null && schemaPattern.trim().length() != 0) {
		if (isFirstCondition) {
			isFirstCondition = false;
			sql += " WHERE";
		} else {
			sql += " AND";
		}
		sql += " TABLE_SCHEM LIKE ?";
	}
	if (tableNamePattern != null && tableNamePattern.trim().length() != 0) {
		if (isFirstCondition) {
			isFirstCondition = false;
			sql += " WHERE";
		} else {
			sql += " AND";
		}
		sql += " TABLE_NAME LIKE ?";
	}
	if (columnNamePattern != null && columnNamePattern.trim().length() != 0) {
		if (isFirstCondition) {
			isFirstCondition = false;
			sql += " WHERE";
		} else {
			sql += " AND";
		}
		sql += " COLUMN_NAME LIKE ?";
	}

	sql += " ORDER BY TABLE_CAT, TABLE_SCHEM, TABLE_NAME, ORDINAL_POSITION";

	final PreparedStatement pstmt = connCache.prepareStatement(sql);

	int indexCol = 1;
	if (catalog != null && catalog.trim().length() != 0) {
		pstmt.setString(indexCol++, catalog);
	}
	if (schemaPattern != null && schemaPattern.trim().length() != 0) {
		pstmt.setString(indexCol++, schemaPattern);
	}
	if (tableNamePattern != null && tableNamePattern.trim().length() != 0) {
		pstmt.setString(indexCol++, tableNamePattern);
	}
	if (columnNamePattern != null && columnNamePattern.trim().length() != 0) {
		pstmt.setString(indexCol++, columnNamePattern);
	}

	pstmt.executeQuery();
	return pstmt.getResultSet();
}
 

import java.sql.PreparedStatement; //导入方法依赖的package包/类
public static boolean isCacheDatabaseMetaDataColumnsCached(final Connection connCache, String catalog,
		String schemaPattern, String tableName) throws SQLException {
	boolean isCached = false;

	String sql = "SELECT COUNT(*) FROM GMETA_COLUMNS";

	boolean isFirstCondition = true;
	if (catalog != null && catalog.trim().length() != 0) {
		if (isFirstCondition) {
			isFirstCondition = false;
			sql += " WHERE";
		} else {
			sql += " AND";
		}
		sql += " TABLE_CAT = ?";
	}
	if (schemaPattern != null && schemaPattern.trim().length() != 0) {
		if (isFirstCondition) {
			isFirstCondition = false;
			sql += " WHERE";
		} else {
			sql += " AND";
		}
		sql += " TABLE_SCHEM LIKE ?";
	}
	{
		if (isFirstCondition) {
			isFirstCondition = false;
			sql += " WHERE";
		} else {
			sql += " AND";
		}
		sql += " TABLE_NAME = ?";
	}

	final PreparedStatement pstmt = connCache.prepareStatement(sql);
	try {
		int indexCol = 1;
		if (catalog != null && catalog.trim().length() != 0) {
			pstmt.setString(indexCol++, catalog);
		}
		if (schemaPattern != null && schemaPattern.trim().length() != 0) {
			pstmt.setString(indexCol++, schemaPattern);
		}
		pstmt.setString(indexCol++, tableName);

		pstmt.executeQuery();
		ResultSet rs = pstmt.getResultSet();
		rs.next();
		if (rs.getInt(1) > 0) {
			isCached = true;
		}
		rs.close();
		return isCached;
	} finally {
		pstmt.close();
	}
}