Statement (操作 SQL 語句)

Statement, PreparedStatement, CallableStatement

一旦建立好連線, 就可以與資料庫互動. JDBC 中Statement, PreparedStatement 和 CallableStatement 提供了SQL操作的相關API. 其中 CallableStatement 繼承自 PreparedStatement, 而 PreparedStatement 又繼承自 Statement. 他們的區別是:

Statement 提供基本的 SQL 操作. 適合靜態SQL語句, 且傳入的 SQL 語句無法接受引數.
PreparedStatement 可以在 SQL 中傳遞引數, 適合多次使用的 SQL 語句.
CallableStatement 可以呼叫 PL/SQL 儲存過程.

儘管介面功能有不同, 但是使用方式大體相同, 分以下幾步:

建立 Statement
執行 SQL 語句
關閉 Statement

在執行 SQL 語句的時候, 常用以下幾個方法:

boolean execute(String SQL) : 如果有 ResultSet 產生返回true, 否則, 返回 false. 一般用於 CREATE, ALTER 這些操作, 或者用來檢查一個Query有沒有返回.
int executeUpdate(String SQL) : 返回被影響的記錄的條數, 一般用於 INSERT, UPDATE, DELETE 這些操作.
ResultSet executeQuery(String SQL) : 返回查詢結果集, 專用語 SELECT.

以下三個例子分別示例瞭如何適用他們.

Statement 例子.

public class StatementExample {


private Properties dbProps = new Properties();


StatementExample() {}


public void setDBProperties(Properties dbProps) {
    this.dbProps = dbProps;
}


public Connection getConnection() throws SQLException {
    String url = dbProps.getProperty("url");
    String user = dbProps.getProperty("user");
    Connection conn = null;
    if (user.length() == 0) {
        conn = DriverManager.getConnection(url);
    } else {
        conn = DriverManager.getConnection(url, dbProps);
    }
    String dbName = dbProps.getProperty("dbName");
    conn.setCatalog(dbName);
    return conn;
}


public void deleteAll() throws SQLException {
    String sql = "DELETE FROM posts";
    Connection conn = getConnection();
    Statement stmt = conn.createStatement();
    int nRows = stmt.executeUpdate(sql);
    System.out.println(nRows + (nRows == 1 ? " post is " : " posts are ") + "deleted.");
    stmt.close();
    conn.close();
}


public void insertPost(Post post) throws SQLException {
    String sql = "INSERT INTO posts VALUES";
    String title = post.getTitle();
    String content = post.getContent();
    Boolean visible = post.isVisible();
    sql += "("
            + "NULL" + ","
            + "\"" +title + "\"" + ","
            + "\"" + content + "\"" + ","
            + "DEFAULT" + ","
            + (visible ? "TRUE" : "FALSE")
            + ")";
    Connection conn = getConnection();
    Statement stmt = conn.createStatement();
    int nRows = stmt.executeUpdate(sql);
    stmt.close();
    conn.close();
}


public ArrayList&lt;Post&gt; queryAll() throws SQLException {
    ArrayList&lt;Post&gt; list = new ArrayList&lt;Post&gt;();
    String sql = "SELECT * FROM posts";
    Connection conn = getConnection();
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery(sql);
    rs.beforeFirst();
    while (rs.next()) {
        Post temp = new Post();
        temp.setId(rs.getInt("id"));
        temp.setTitle(rs.getString("title"));
        temp.setContent(rs.getString("content"));
        temp.setDate(rs.getTimestamp("dt_create"));
        temp.setVisible(rs.getBoolean("visible"));
        list.add(temp);
    }
    stmt.close();
    conn.close();
    return list;
}


public static void main(String[] args) {
    try {
        Properties props = new Properties();
        props.load(ClassLoader.getSystemResourceAsStream("db.mysql.props"));
        StatementExample example = new StatementExample();
        example.setDBProperties(props);


        ArrayList&lt;Post&gt; posts = example.queryAll();
        System.out.println(posts);


        Post toInsert = new Post();
        toInsert.setTitle("new Post");
        toInsert.setContent("This is a new post!");
        example.insertPost(toInsert);


        posts = example.queryAll();
        System.out.println(posts);


        example.deleteAll();
        posts = example.queryAll();
        System.out.println(posts);


    } catch (SQLException e) {
        DBUtils.printSQLException(e);
    } catch (Exception e) {
        e.printStackTrace();
    }
}


}

PreparedStatement 例子.

public class PreparedStatExample {


private Properties dbProps = new Properties();


PreparedStatExample() {}


public void setDBProperties(Properties dbProps) {
    this.dbProps = dbProps;
}


public Connection getConnection() throws SQLException {
    String url = dbProps.getProperty("url");
    String user = dbProps.getProperty("user");
    Connection conn = null;
    if (user.length() == 0) {
        conn = DriverManager.getConnection(url);
    } else {
        conn = DriverManager.getConnection(url, dbProps);
    }
    String dbName = dbProps.getProperty("dbName");
    conn.setCatalog(dbName);
    return conn;
}


public void deletePost(int id) throws SQLException {
    String sql = "DELETE FROM posts WHERE id = ?";
    Connection conn = getConnection();
    PreparedStatement stmt = conn.prepareStatement(sql);
    stmt.setInt(1, id);
    stmt.executeUpdate();
    DBUtils.printWarnings(stmt.getWarnings());
    stmt.close();
    conn.close();
}


public void insertPost(Post post) throws SQLException {
    String sql = "INSERT INTO posts VALUES(NULL, ?, ?, DEFAULT, ?)";
    Connection conn = getConnection();
    PreparedStatement stmt = conn.prepareStatement(sql);
    stmt.setString(1, post.getTitle());
    stmt.setString(2, post.getContent());
    stmt.setBoolean(3, post.isVisible());
    stmt.executeUpdate();
    DBUtils.printWarnings(stmt.getWarnings());
    stmt.close();
    conn.close();
}


public ArrayList&lt;Post&gt; queryByTitle(String title) throws SQLException {
    ArrayList&lt;Post&gt; list = new ArrayList&lt;Post&gt;();
    String sql = "SELECT * FROM posts WHERE title like ?";
    Connection conn = getConnection();
    PreparedStatement stmt = conn.prepareStatement(sql);
    stmt.setString(1, title);
    ResultSet rs = stmt.executeQuery();
    rs.beforeFirst();
    while (rs.next()) {
        Post temp = new Post();
        temp.setId(rs.getInt("id"));
        temp.setTitle(rs.getString("title"));
        temp.setContent(rs.getString("content"));
        temp.setDate(rs.getTimestamp("dt_create"));
        temp.setVisible(rs.getBoolean("visible"));
        list.add(temp);
    }
    stmt.close();
    conn.close();
    return list;
}


public static void main(String[] args) {
    try {
        Properties props = new Properties();
        props.load(ClassLoader.getSystemResourceAsStream("db.mysql.props"));
        PreparedStatExample example = new PreparedStatExample();
        example.setDBProperties(props);


        // 此時資料庫中有一條 title 為 111 的資料
        ArrayList&lt;Post&gt; posts = example.queryByTitle("111");
        System.out.println(posts); //[Post{id=34, title='111', content='111', date=2015-01-25 12:58:32.0, visible=true}]


        Post toInsert = new Post();
        toInsert.setTitle("111");
        toInsert.setContent("111111");
        example.insertPost(toInsert);


        posts = example.queryByTitle("111");
        System.out.println(posts); // [Post{id=39, title='111', content='111', date=2015-01-25 13:00:49.0, visible=true}, Post{id=41, title='111', content='111111', date=2015-01-25 13:00:59.0, visible=false}]


        example.deletePost(posts.get(0).getId());
        posts = example.queryByTitle("111");
        System.out.println(posts); // [Post{id=41, title='111', content='111111', date=2015-01-25 13:00:59.0, visible=false}]


    } catch (SQLException e) {
        DBUtils.printSQLException(e);
    } catch (Exception e) {
        e.printStackTrace();
    }
}


}

CallableStatement 例子.

public class CallableStatExample {


private Properties dbProps = new Properties();


public CallableStatExample() {}


public void setDBProperties(Properties dbProps) {
    this.dbProps = dbProps;
}


public Connection getConnection() throws SQLException {
    String url = dbProps.getProperty("url");
    String user = dbProps.getProperty("user");
    Connection conn = null;
    if (user.length() == 0) {
        conn = DriverManager.getConnection(url);
    } else {
        conn = DriverManager.getConnection(url, dbProps);
    }
    String dbName = dbProps.getProperty("dbName");
    conn.setCatalog(dbName);
    return conn;
}


public String getTitleById(int id) throws SQLException {
    Connection conn = getConnection();
    String sql = "{call getTitleById (?, ?)}";
    CallableStatement stmt = conn.prepareCall(sql);
    // 繫結傳入引數
    stmt.setInt(1, id);
    // 對於傳出引數, 要先註冊
    stmt.registerOutParameter(2, java.sql.Types.VARCHAR);
    stmt.execute();
    String title = stmt.getString(2);
    stmt.close();
    conn.close();
    return title;
}


public static void main(String[] args) throws IOException, SQLException {
    Properties props = new Properties();
    props.load(ClassLoader.getSystemResourceAsStream("db.mysql.props"));
    CallableStatExample example = new CallableStatExample();
    example.setDBProperties(props);
    int id = 35;
    String title = example.getTitleById(id);
    System.out.println("Find title : " + title + " by ID : " + id); // Find title : 222 by ID : 35
}


}

Note: 請先將以下儲存過程存入 MySQL 資料庫

    DELIMITER $$
    CREATE PROCEDURE `testdb`.`getTitleById` 
       (IN post_id INT, OUT post_name VARCHAR(255))
    BEGIN
       SELECT title INTO post_name
       FROM posts
       WHERE ID = post_id;
    END $$
    DELIMITER ;

SQL 的批處理操作

SQL 批處理能夠允許新增多個 SQL 到一個Statement物件, 並一併提交執行結果. 這減少了與 SQL 通訊的頻率. 但是, SQL 批處理不是 JDBC 要求一定要支援的. 使用前應該用 DatabaseMetaData.supportsBatchUpdates() 檢查支援情況.

SQL 批處理相關的 API 有:

Statement.addBatch(): 往批處理中新增 SQL 語句
Statement.executeBatch(): 執行批處理, 並返回一個整型陣列, 其中每個元素代表對應序號 SQL 的執行結果.
Statement.clearBatch(): 從批處理中刪除已新增的所有 SQL 語句.

以下示例如何使用批處理往資料庫新增資料:

    public static void batchInsertPosts(ArrayList<Post> posts) throws SQLException {
        Connection conn = getConnectionFromDS(dbProps);
        conn.setAutoCommit(false);    // 見 "事務" 一章
        DatabaseMetaData md = conn.getMetaData();
        System.out.println("If support batch updates: " + md.supportsBatchUpdates());
        String sql = "INSERT INTO POSTS\n"
                   + "VALUES(NULL, ?, ?, DEFAULT, ?)";
        PreparedStatement stmt = conn.prepareCall(sql);

        try {
            for (Post post : posts) {
                stmt.setString(1, post.getTitle());
                stmt.setString(2, post.getContent());
                stmt.setBoolean(3, post.isVisible());
                stmt.addBatch();
            }
            stmt.executeBatch();
            conn.commit();
        } catch (SQLException e) {
            DBUtils.printSQLException(e);
            conn.rollback();
        }

        DBUtils.printWarnings(stmt.getWarnings());
        stmt.close();
        conn.close();
    }

SQL 異常處理

JDBC 中最常用的異常就是 SQLException, 不管是在建立連線, 還是在執行 SQL 語句的時候, 都有可能丟擲這個異常. SQLException 包含以下資訊:

關於錯誤的描述. 通過呼叫getMessage() 獲得.
一個 SQL 狀態碼. 通過呼叫 getSQLState( ) 獲取. SQL 狀態碼由5位字母和數字組成, 符合 XOPEN 規範.
一個錯誤碼. 這個錯誤碼的含義由實現規定, 有可能是資料庫的錯誤碼. 通過呼叫 SQLException.getErrorCode() 獲取.
錯誤緣由. 引發異常的緣由, 有可能是一個或者多個 Throwable 的物件組成的一條鏈. 要想檢查這些緣由, 要遞迴遍歷 SQLException.getCause() 直到返回一個 null.
異常鏈. 通過 getNextException() 獲取下一個異常.

以下程式碼示例如何列印異常鏈中的每個SQLException異常, 並且列印每個異常的 cause 鏈.

    public static void printSQLException(SQLException ex) {
        for (Throwable e : ex) {     // Iterator 會呼叫 getNextException()
            if (e instanceof SQLException) {

                e.printStackTrace(System.err);
                System.err.println("SQLState: " +
                        ((SQLException)e).getSQLState());

                System.err.println("Error Code: " +
                        ((SQLException)e).getErrorCode());

                System.err.println("Message: " + e.getMessage());

                Throwable t = ex.getCause();
                while(t != null) {    // 列印每個 cause
                    System.out.println("Cause: " + t);
                    t = t.getCause();
                }
            }
        }
    }

除了發生致命錯誤產生丟擲 SQLException 之外, Connection, Statement, ResultSet 都有一個 getWarnings() 方法, 它返回一個 SQLWarning. SQLWarning 繼承自 SQLException, 可以向遍歷 SQLException 一樣遍歷它:

    public static void printWarnings(SQLWarning warning)
        throws SQLException {
        while (warning != null) {
            System.out.println("Message: " + warning.getMessage());
            System.out.println("SQLState: " + warning.getSQLState());
            System.out.print("Vendor error code: ");
            System.out.println(warning.getErrorCode());
            System.out.println("");
            warning = warning.getNextWarning();
        }
    }

Statement (操作 SQL 語句)

Statement, PreparedStatement, CallableStatement

SQL 的批處理操作

SQL 異常處理

相關文章