JDBC检索数据（数据库学习之三）

获取表中的 行 信息

try {

        // 获取指定数据库表的全部的行数据
   
   

        Statement stmt = connection.createStatement();

        ResultSet rs = stmt.executeQuery("SELECT * FROM my_table");

    } catch (SQLException e) {

    }

获取数据从Result Set 中

A result set contains the results of a SQL query. The results are kept in a set of rows, one of which is designated the current row. A row must be made current before data can be retrieved from it. The result set maintains a reference to the current row called the cursor.

The cursor is positioned before the first row when a result set is created. When a result set's next() method is called, the cursor moves to the first row of the result set and that row becomes the current row.

There are two ways to retrieve the data from the current row. The first uses a column index starting from 1. The second uses a column name. For example, with the query `/cv{SELECT col1, col2 FROM table}', the value for col2 can be retrieved using a column index of 2 or with the column name col2. This example demonstrates both methods.

    try {

        // Create a result set containing all data from my_table

        Statement stmt = connection.createStatement();

        ResultSet rs = stmt.executeQuery("SELECT * FROM my_table");

   

        // Fetch each row from the result set 提取数据的擦操作

        while (rs.next()) {

            // Get the data from the row using the column index 索引取值

            String s = rs.getString(1);

   

            // Get the data from the row using the column name 列名取值

            s = rs.getString("col_string");

        }

    } catch (SQLException e) {

    }

Here is another example of retrieving data from a result that uses the various getXXX() methods. This example uses the table created in e248 Creating a MySQL Table to Store Java Types.

    try {

        // Create a result set containing all data from mysql_all_table

        Statement stmt = connection.createStatement();

        ResultSet rs = stmt.executeQuery("SELECT * FROM mysql_all_table");

   

        // Fetch each row from the result set

        while (rs.next()) {

            boolean bool = rs.getBoolean("col_boolean");

            byte b = rs.getByte("col_byte");

            short s = rs.getShort("col_short");

            int i = rs.getInt("col_int");

            long l = rs.getLong("col_long");

            float f = rs.getFloat("col_float");

            double d = rs.getDouble("col_double");

            BigDecimal bd = rs.getBigDecimal("col_bigdecimal");

            String str = rs.getString("col_string");

            Date date = rs.getDate("col_date");

            Time t = rs.getTime("col_time");

            Timestamp ts = rs.getTimestamp("col_timestamp");

            InputStream ais = rs.getAsciiStream("col_asciistream");

            InputStream bis = rs.getBinaryStream("col_binarystream");

            Blob blob = rs.getBlob("col_blob");

        }

    } catch (SQLException e) {

    }

处理提取数据库中一些值为 NULL 的情况

When a ResultSet.getXXX() method encounters a NULL in the database, it will convert it to a default value. For example, if NULL was encountered in a NUMBER field, ResultSet.getInt() will return 0. In order to determine whether or not the actual value is a NULL, wasNull() must be called. This method must be called immediately after the value is fetched from the result set.

   

    // Fetch each row from the result set

    while (resultSet.next()) {

从结果集中获取列名称

try {

        // Create a result set
   
   

        Statement stmt = connection.createStatement();

        ResultSet rs = stmt.executeQuery("SELECT * FROM my_table");

        // Get result set meta data 获取元素据集合
   
   

        ResultSetMetaData rsmd = rs.getMetaData();

        int numColumns = rsmd.getColumnCount();

        // Get the column names; column indices start from 1
   
   

        for (int i=1; i<numColumns+1; i++) {

            String columnName = rsmd.getColumnName(i);

            // Get the name of the column's table name
   
   

            String tableName = rsmd.getTableName(i);

        }

    } catch (SQLException e) {}

获取数据表中的行数

This example gets the number of rows in a table using the SQL statement `SELECT COUNT(*)'.

    try {

        // Select the number of rows in the table

        Statement stmt = connection.createStatement();

        ResultSet resultSet = stmt.executeQuery("SELECT COUNT(*) FROM my_table");

   

        // Get the number of rows from the result set

        resultSet.next();

        int rowcount = resultSet.getInt(1);

    } catch (SQLException e) {

    }

Getting BLOB Data from a Database Table

try {

        Statement stmt = connection.createStatement();

        ResultSet rs = stmt.executeQuery("SELECT col_blob FROM mysql_all_table");

        if (rs.next()) {

            // Get the BLOB from the result set
   
   

            Blob blob = rs.getBlob("col_blob");

            // Get the number bytes in the BLOB
   
   

            long blobLength = blob.length();

            // Get bytes from the BLOB in a byte array
   
   

            int pos = 1;   // position is 1-based

            int len = 10;

            byte[] bytes = blob.getBytes(pos, len);

            // Get bytes from the BLOB using a stream
   
   

            InputStream is = blob.getBinaryStream();

            int b = is.read();

        }

    } catch (IOException e) {

    } catch (SQLException e) {

    }

SQL语法：匹配通用符在SQL Statement

try {

        // Create a statement
   
   

        Statement stmt = connection.createStatement();

        // Select the row if col_string contains the word pat
   
   

        String sql = "SELECT * FROM my_table WHERE col_string LIKE '%pat%'";

        // Select the row if col_string ends with the word pat
   
   

        sql = "SELECT * FROM my_table WHERE col_string LIKE 'pat%'";

        // Select the row if col_string starts with abc and ends with xyz
   
   

        sql = "SELECT * FROM my_table WHERE col_string LIKE 'abc%xyz'";

        // Select the row if col_string equals the word pat%
   
   

        sql = "SELECT * FROM my_table WHERE col_string LIKE 'pat//%'";

        // Select the row if col_string has 3 characters and starts with p and ends with t
   
   

        sql = "SELECT * FROM my_table WHERE col_string LIKE 'p_t'";

        // Select the row if col_string equals p_t
   
   

        sql = "SELECT * FROM my_table WHERE col_string LIKE 'p//_t'";

        // Execute the query
   
   

        ResultSet resultSet = stmt.executeQuery(sql);

    } catch (SQLException e) {

    }