JDBC 入门 - 操作SQL语句

操作 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.

以下三个例子分别示例了如何使用他们.

在运行 CallableStatement 之前, 要先插入以下存储过程:

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

例子:

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<Post> queryAll() throws SQLException {
        ArrayList<Post> list = new ArrayList<Post>();
        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<Post> 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();
        }
    }
}

Statement

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.prepareCall(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.prepareCall(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<Post> queryByTitle(String title) throws SQLException {
        ArrayList<Post> list = new ArrayList<Post>();
        String sql = "SELECT * FROM posts WHERE title like ?";
        Connection conn = getConnection();
        PreparedStatement stmt = conn.prepareCall(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<Post> 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();
        }
    }
}

PreparedStatement

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
    }

}

CallableStatement

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();
        }
    }

posted @ 2015-01-25 11:52 still_water 阅读(382) 评论(0) 收藏举报

刷新页面返回顶部

静水

JDBC 入门 - 操作SQL语句

操作 SQL 语句

Statement, PreparedStatement, CallableStatement

SQL 的批处理操作

SQL异常处理