Java Statement.executeQuery方法代码示例

import java.sql.Statement; //导入方法依赖的package包/类
public void mbushTabelen(){
    Thread t = new Thread(new Runnable() {
        public void run() {
            try {
                Connection conn = DriverManager.getConnection(CON_STR, "test", "test");
                Statement stmt = conn.createStatement();
                ResultSet rs = stmt.executeQuery("select * from Konsumatori");
                ObservableList<TabelaKonsumatori> data = FXCollections.observableArrayList();
                while (rs.next()){
                    data.add(new TabelaKonsumatori(rs.getInt("id"), rs.getString("emri"), rs.getString("mbiemri"),
                            rs.getString("makina"), rs.getString("komuna"), rs.getString("pershkrimi")));
                }
                table.setItems(data);
                conn.close();
            }catch (Exception ex){ex.printStackTrace();}
        }
    });
    t.start();
}
 

import java.sql.Statement; //导入方法依赖的package包/类
public void executeQuerySamples(String sql) throws SQLException {

        Statement stmt = con.createStatement();

        //Normal query
        stmt.executeQuery(sql);

        stmt.execute(sql);
        stmt.execute(sql, Statement.RETURN_GENERATED_KEYS);
        stmt.execute(sql, new int[]{1, 2, 3});
        stmt.execute(sql, new String[]{"firstname", "middlename", "lastname"});
    }
 

import java.sql.Statement; //导入方法依赖的package包/类
public static ArrayList<EmpenhoItem> retreaveAll() throws SQLException {
    Statement stm = Database.createConnection().createStatement();
    String sql = "SELECT * FROM itens_empenho";
    ResultSet rs = stm.executeQuery(sql);
    ArrayList<EmpenhoItem> pe = new ArrayList<>();
    while (rs.next()) {
        pe.add(new EmpenhoItem(
                rs.getInt("id"),
                rs.getInt("empenho"),
                NaturezaDespesaDAO.retreave(rs.getInt("natureza_despesa")),
                ProdutoDAO.retreave(rs.getInt("produto")),
                rs.getInt("sequencia"),
                rs.getDouble("quantidade"),
                rs.getDouble("valor_unitario"),
                rs.getString("item_processo")));
    }
    rs.next();
    return pe;
}
 

import java.sql.Statement; //导入方法依赖的package包/类
/********************SQL PARA PIVOT CADENA DE VALOR	***********************/					
public static String selectAllPivotCadenaValor(String condition)
		throws SQLException {
	Connection conect = ConnectionConfiguration.conectar();

	String query = " select array_to_json(" + "		array_agg(row_to_json(t))"
			+ "	) as cadena_valor" + "	from("
			+ "			select * FROM pnd_resul_ent_prod_presupuesto" + condition
			+ "		)t";

	Statement statement = null;
	ResultSet rs = null;
	String objetos = "";

	try {
		statement = conect.createStatement();
		rs = statement.executeQuery(query);
		while (rs.next()) {
			objetos += rs.getString("cadena_valor");
		}
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		if (statement != null) {
			statement.close();
		}
		if (conect != null) {
			conect.close();
		}
	}
	return objetos;
}
 

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包/类
private List<Answer> readAnswers(int questionDataBaseId, Connection connection) throws SQLException {
	Statement statement = connection.createStatement();
	statement.setQueryTimeout(10);
	List<Answer> answers = new ArrayList<Answer>();

	ResultSet answerSet = statement
			.executeQuery("SELECT answerid, answertext  " + "FROM answer WHERE questionid=" + questionDataBaseId);
	while (answerSet.next()) {
		int awId = answerSet.getInt("answerid");
		String awText = answerSet.getString("answertext");
		answers.add(new Answer(this,awId, awText));

	}
	if (answers.size() == 0) {
		answers.add(new Answer(this,-1, "Richtig"));
		answers.add(new Answer(this,-2, "Falsch"));
	}
	return answers;
}
 

import java.sql.Statement; //导入方法依赖的package包/类
private static ResultSet setUpRow( final String schemaName,
                                   final String tableOrViewName,
                                   final String columnName ) throws SQLException
{
  System.out.println( "(Setting up row for " + tableOrViewName + "." + columnName + ".)");
  final Statement stmt = connection.createStatement();
  final ResultSet mdrUnk =
      stmt.executeQuery(
          "SELECT * FROM INFORMATION_SCHEMA.COLUMNS "
          + "WHERE TABLE_CATALOG = " + encodeAsSqlCharStrLiteral( "DREMIO" )
          + "  AND TABLE_SCHEMA  = " + encodeAsSqlCharStrLiteral( schemaName )
          + "  AND TABLE_NAME    = " + encodeAsSqlCharStrLiteral( tableOrViewName )
          + "  AND COLUMN_NAME   = " + encodeAsSqlCharStrLiteral( columnName )
          );
  assertTrue( "Test setup error:  No row for column DREMIO . `" + schemaName + "` . `"
              + tableOrViewName + "` . `" + columnName + "`", mdrUnk.next() );
  return mdrUnk;
}
 

import java.sql.Statement; //导入方法依赖的package包/类
public static ArrayList<RequisicaoProduto> retreaveByRequisicao(int requisicaoId) throws SQLException {
    Statement stm
            = Database.createConnection().
                    createStatement();
    String sql = "SELECT * FROM produtos_requisicoes WHERE requisicao = " + requisicaoId;
    ResultSet rs = stm.executeQuery(sql);
    ArrayList<RequisicaoProduto> pr = new ArrayList<>();
    while (rs.next()) {
        pr.add(new RequisicaoProduto(
                rs.getInt("id"),
                rs.getInt("requisicao"),
                ProdutoDAO.retreave(rs.getInt("produto")),
                rs.getDouble("quantidade")));
    }
    rs.next();
    return pr;
}
 

import java.sql.Statement; //导入方法依赖的package包/类
private static DataIterator<ResultSet> createSource(String query, Connection connection, int fetchSize) {
    try {
        Statement statement = connection.createStatement(
        		ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
        statement.setFetchSize(fetchSize);
        ResultSet resultSet = statement.executeQuery(query);
        return new ResultSetDataIterator(resultSet, query);
    } catch (SQLException e) {
        throw new RuntimeException("Error in query: " + query, e);
    }
}
 

import java.sql.Statement; //导入方法依赖的package包/类
/**
 * Test Bug#21296840 - CONNECTION DATA IS NOT UPDATED DURING FAILOVER.
 * Test Bug#17910835 - SERVER INFORMATION FROM FABRIC NOT REFRESHED WITH SHORTER TTL.
 * 
 * Test that the local cache is refreshed after expired TTL. This test connects to the master of "ha_config1_group" and requires the master to be changed
 * manually during the wait period. The Fabric must also be setup to communicate a TTL of less than 10s to the client.
 */
public void manualTestRefreshFabricStateCache() throws Exception {
    if (!this.isSetForFabricTest) {
        return;
    }

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

    Statement stmt = this.conn.createStatement();

    ResultSet rs = stmt.executeQuery("show variables like 'server_uuid'");
    rs.next();
    String firstServerUuid = rs.getString(2);
    rs.close();
    this.conn.commit();

    // sleep for TTL+1 secs
    int seconds = 10;
    System.err.println("Waiting " + seconds + " seconds for new master to be chosen");
    Thread.sleep(TimeUnit.SECONDS.toMillis(1 + seconds));

    // force the LB proxy to pick a new connection
    this.conn.rollback();

    // verify change is seen by client
    rs = stmt.executeQuery("show variables like 'server_uuid'");
    rs.next();
    String secondServerUuid = rs.getString(2);
    rs.close();

    System.err.println("firstServerUuid=" + firstServerUuid + "\nsecondServerUuid=" + secondServerUuid);
    if (firstServerUuid.equals(secondServerUuid)) {
        fail("Server ID should change to reflect new topology");
    }

    this.conn.close();
}
 

import java.sql.Statement; //导入方法依赖的package包/类
public List<sensor> sensor_listele() throws SQLException{
    baglan();
    List<sensor> gelen = new ArrayList<>();
 
    sensor veriler ;
    Statement stmt;
    try {
        stmt = connection.createStatement();
        ResultSet rs;
        rs = stmt.executeQuery("SELECT * FROM gelen_sensor");
       
        while (rs.next()) {
            System.out.println(rs.getString("gelen_sicaklik"));
            System.out.println(rs.getString("gelen_gaz"));
            veriler = new sensor();
            veriler.setSicaklik(rs.getString("gelen_sicaklik"));
            veriler.setGaz(rs.getString("gelen_gaz"));
            
                gelen.add(veriler);
            
        }
        
        
    } catch (SQLException ex) {
        Logger.getLogger(dbConnection.class.getName()).log(Level.SEVERE, null, ex);
    }
    return gelen;
}
 

import java.sql.Statement; //导入方法依赖的package包/类
public static void updateDemPronouns(Connection con) throws SQLException, IOException {
    MyDictionary dictionary = new MyDictionary();
    Statement stmt = con.createStatement();
    long k = 0;
    ResultSet rs = stmt.executeQuery("SELECT \n"
            + "             inflectedform.formUtf8General as inflForm, \n"
            + "             inflectedform.inflectionId as inflId, \n"
            + "		lexem.formUtf8General as lemma,\n"
            + "             definition.internalRep as definition\n"
            + "FROM inflectedform \n"
            + "JOIN lexemmodel on inflectedform.lexemmodelId=lexemmodel.id \n"
            + "JOIN lexem on lexemmodel.lexemId=lexem.id \n"
            + "JOIN lexemdefinitionmap on lexemdefinitionmap.lexemId=lexem.Id \n"
            + "JOIN definition on definition.id = lexemdefinitionmap.definitionId \n"
            + "WHERE (inflectedform.inflectionId=84 || (inflectedform.inflectionId>40 && inflectedform.inflectionId<49)) AND definition.internalRep LIKE '% #pron\\. dem\\.%' \n"
            + "ORDER BY inflectedform.formUtf8General");

    BufferedWriter out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(MyDictionary.folder + "grabedForUse/" + pronDemFile), "UTF8"));
    while (rs.next()) {
        MyDictionaryEntry entry = new MyDictionaryEntry(rs.getString("inflForm"), null, null, null);
        entry.setLemma(rs.getString("lemma"));
        entry.setMsd("Pd");
        preprocessEntry(entry, rs.getInt("inflId"));

        if (!dictionary.contains(entry)) {
            out.write(entry.toString() + "\n");
            dictionary.Add(entry);
            k++;
        }
    }
    stmt.close();
    rs.close();
    out.close();
    System.out.println("Finished demonstrative pronouns (" + k + " added)");
}
 

import java.sql.Statement; //导入方法依赖的package包/类
public static List<Map<String, Object>> query(String sql) throws Exception {
	Properties info = new Properties();
	info.setProperty("lex", "JAVA");
	String jsonFile = SolrSqlTest.class.getClassLoader().getResource("solr.json").toString().replaceAll("file:/",
			"");
	try {
		Class.forName("org.apache.calcite.jdbc.Driver");
	} catch (ClassNotFoundException e1) {
		e1.printStackTrace();
	}
	Connection connection = DriverManager.getConnection("jdbc:calcite:model=" + jsonFile, info);
	Statement statement = connection.createStatement();
	System.out.println("executing sql: " + sql);
	ResultSet resultSet = statement.executeQuery(sql);
	List<Map<String, Object>> rows = new ArrayList<Map<String, Object>>();
	while (resultSet.next()) {
		Map<String, Object> row = new HashMap<String, Object>();
		rows.add(row);
		for (int i = 1; i <= resultSet.getMetaData().getColumnCount(); i++) {
			row.put(resultSet.getMetaData().getColumnName(i), resultSet.getObject(i));
		}
	}
	resultSet.close();
	statement.close();
	connection.close();
	System.out.println(rows);
	return rows;
}
 

import java.sql.Statement; //导入方法依赖的package包/类
private void testBug80615CheckComStmtStatus(int prepCount, boolean isSPS, String testCase, Statement testStmt, int expectedPrepCount, int expectedExecCount,
        int expectedCloseCount) throws Exception {
    System.out.print(prepCount + ". ");
    System.out.print(isSPS ? "[SPS]" : "[CPS]");

    testCase += "\nIteration: " + prepCount;

    int actualPrepCount = 0;
    int actualExecCount = 0;
    int actualCloseCount = 0;
    this.rs = testStmt.executeQuery("SHOW SESSION STATUS WHERE Variable_name IN ('Com_stmt_prepare', 'Com_stmt_execute', 'Com_stmt_close')");
    while (this.rs.next()) {
        System.out.print(" (" + this.rs.getString(1).replace("Com_stmt_", "") + " " + this.rs.getInt(2) + ")");
        if (this.rs.getString(1).equalsIgnoreCase("Com_stmt_prepare")) {
            actualPrepCount = this.rs.getInt(2);
        } else if (this.rs.getString(1).equalsIgnoreCase("Com_stmt_execute")) {
            actualExecCount = this.rs.getInt(2);
        } else if (this.rs.getString(1).equalsIgnoreCase("Com_stmt_close")) {
            actualCloseCount = this.rs.getInt(2);
        }
    }
    System.out.println();

    assertEquals(testCase, expectedPrepCount, actualPrepCount);
    assertEquals(testCase, expectedExecCount, actualExecCount);
    assertEquals(testCase, expectedCloseCount, actualCloseCount);
}
 

import java.sql.Statement; //导入方法依赖的package包/类
/**
 * Tests fix(es) for BUG#21379 - column names don't match metadata in cases
 * where server doesn't return original column names (functions) thus
 * breaking compatibility with applications that expect 1-1 mappings between
 * findColumn() and rsmd.getColumnName().
 * 
 * @throws Exception
 *             if the test fails.
 */
public void testBug21379() throws Exception {
    //
    // Test the 1-1 mapping between rs.findColumn() and rsmd.getColumnName() in the case where original column names are not returned, thus preserving 
    // pre-C/J 5.0 behavior for these cases
    //

    this.rs = this.stmt.executeQuery("SELECT LAST_INSERT_ID() AS id");
    this.rs.next();
    assertEquals("id", this.rs.getMetaData().getColumnName(1));
    assertEquals(1, this.rs.findColumn("id"));

    if (versionMeetsMinimum(4, 1)) {
        //
        // test complete emulation of C/J 3.1 and earlier behavior through configuration option
        //

        createTable("testBug21379", "(field1 int)");
        Connection legacyConn = null;
        Statement legacyStmt = null;

        try {
            Properties props = new Properties();
            props.setProperty("useOldAliasMetadataBehavior", "true");
            legacyConn = getConnectionWithProps(props);
            legacyStmt = legacyConn.createStatement();

            this.rs = legacyStmt.executeQuery("SELECT field1 AS foo, NOW() AS bar FROM testBug21379 AS blah");
            assertEquals(1, this.rs.findColumn("foo"));
            assertEquals(2, this.rs.findColumn("bar"));
            assertEquals("blah", this.rs.getMetaData().getTableName(1));
        } finally {
            if (legacyConn != null) {
                legacyConn.close();
            }
        }
    }
}