JDBC笔记总结

JDBC: Java DataBase Connectivity， Java数据库连接API，通过执行SQL语句对数据库进行查询，修改等操作。

1.JDBC API

• DriverManager：管理JDBC驱动的服务类，getConnection()获得数据库的连接Connection对象。

     /**
     * @param user the database user on whose behalf the connection is being
     *   made
     * @param password the user's password
     * @return a connection to the URL
     * @exception SQLException if a database access error occurs
     */
    @CallerSensitive
    public static Connection getConnection(String url,
        String user, String password)

• Connection: 数据库的连接对象，每个Connection代表一个物理连接会话，通过Connection对象的方法createStatement()或PreparedStatement(String sql)得到Statement对象或PreparedStatement对象。

    /**
     * Creates a <code>Statement</code> object for sending
     * SQL statements to the database.
     * SQL statements without parameters are normally
     * executed using <code>Statement</code> objects. If the same SQL statement
     * is executed many times, it may be more efficient to use a
     * <code>PreparedStatement</code> object.
     * <P>
     * Result sets created using the returned <code>Statement</code>
     * object will by default be type <code>TYPE_FORWARD_ONLY</code>
     * and have a concurrency level of <code>CONCUR_READ_ONLY</code>.
     * The holdability of the created result sets can be determined by
     * calling {@link #getHoldability}.
     *
     * @return a new default <code>Statement</code> object
     * @exception SQLException if a database access error occurs
     * or this method is called on a closed connection
     */
    Statement createStatement() throws SQLException;

    /**
     * Creates a <code>PreparedStatement</code> object for sending
     * parameterized SQL statements to the database.
     * <P>
     * A SQL statement with or without IN parameters can be
     * pre-compiled and stored in a <code>PreparedStatement</code> object. This
     * object can then be used to efficiently execute this statement
     * multiple times.
     *
     * <P><B>Note:</B> This method is optimized for handling
     * parametric SQL statements that benefit from precompilation. If
     * the driver supports precompilation,
     * the method <code>prepareStatement</code> will send
     * the statement to the database for precompilation. Some drivers
     * may not support precompilation. In this case, the statement may
     * not be sent to the database until the <code>PreparedStatement</code>
     * object is executed.  This has no direct effect on users; however, it does
     * affect which methods throw certain <code>SQLException</code> objects.
     * <P>
     * Result sets created using the returned <code>PreparedStatement</code>
     * object will by default be type <code>TYPE_FORWARD_ONLY</code>
     * and have a concurrency level of <code>CONCUR_READ_ONLY</code>.
     * The holdability of the created result sets can be determined by
     * calling {@link #getHoldability}.
     *
     * @param sql an SQL statement that may contain one or more '?' IN
     * parameter placeholders
     * @return a new default <code>PreparedStatement</code> object containing the
     * pre-compiled SQL statement
     * @exception SQLException if a database access error occurs
     * or this method is called on a closed connection
     */
    PreparedStatement prepareStatement(String sql)
        throws SQLException;

• Statement: 执行SQL语句的工具接口， 有如下三个方法，执行SQL语句。

/**
     * Executes the given SQL statement, which returns a single
     * <code>ResultSet</code> object.
     *<p>
     * <strong>Note:</strong>This method cannot be called on a
     * <code>PreparedStatement</code> or <code>CallableStatement</code>.
     * @param sql an SQL statement to be sent to the database, typically a
     *        static SQL <code>SELECT</code> statement
     * @return a <code>ResultSet</code> object that contains the data produced
     *         by the given query; never <code>null</code>
     * @exception SQLException if a database access error occurs,
     * this method is called on a closed <code>Statement</code>, the given
     *            SQL statement produces anything other than a single
     *            <code>ResultSet</code> object, the method is called on a
     * <code>PreparedStatement</code> or <code>CallableStatement</code>
     * @throws SQLTimeoutException when the driver has determined that the
     * timeout value that was specified by the {@code setQueryTimeout}
     * method has been exceeded and has at least attempted to cancel
     * the currently running {@code Statement}
     */
    ResultSet executeQuery(String sql) throws SQLException;

    /**
     * Executes the given SQL statement, which may be an <code>INSERT</code>,
     * <code>UPDATE</code>, or <code>DELETE</code> statement or an
     * SQL statement that returns nothing, such as an SQL DDL statement.
     *<p>
     * <strong>Note:</strong>This method cannot be called on a
     * <code>PreparedStatement</code> or <code>CallableStatement</code>.
     * @param sql an SQL Data Manipulation Language (DML) statement, such as <code>INSERT</code>, <code>UPDATE</code> or
     * <code>DELETE</code>; or an SQL statement that returns nothing,
     * such as a DDL statement.
     *
     * @return either (1) the row count for SQL Data Manipulation Language (DML) statements
     *         or (2) 0 for SQL statements that return nothing
     *
     * @exception SQLException if a database access error occurs,
     * this method is called on a closed <code>Statement</code>, the given
     * SQL statement produces a <code>ResultSet</code> object, the method is called on a
     * <code>PreparedStatement</code> or <code>CallableStatement</code>
     * @throws SQLTimeoutException when the driver has determined that the
     * timeout value that was specified by the {@code setQueryTimeout}
     * method has been exceeded and has at least attempted to cancel
     * the currently running {@code Statement}
     */
    int executeUpdate(String sql) throws SQLException;

     /**
     * Executes the given SQL statement, which may return multiple results.
     * In some (uncommon) situations, a single SQL statement may return
     * multiple result sets and/or update counts.  Normally you can ignore
     * this unless you are (1) executing a stored procedure that you know may
     * return multiple results or (2) you are dynamically executing an
     * unknown SQL string.
     * <P>
     * The <code>execute</code> method executes an SQL statement and indicates the
     * form of the first result.  You must then use the methods
     * <code>getResultSet</code> or <code>getUpdateCount</code>
     * to retrieve the result, and <code>getMoreResults</code> to
     * move to any subsequent result(s).
     * <p>
     *<strong>Note:</strong>This method cannot be called on a
     * <code>PreparedStatement</code> or <code>CallableStatement</code>.
     * @param sql any SQL statement
     * @return <code>true</code> if the first result is a <code>ResultSet</code>
     *         object; <code>false</code> if it is an update count or there are
     *         no results
     * @exception SQLException if a database access error occurs,
     * this method is called on a closed <code>Statement</code>,
     * the method is called on a
     * <code>PreparedStatement</code> or <code>CallableStatement</code>
     * @throws SQLTimeoutException when the driver has determined that the
     * timeout value that was specified by the {@code setQueryTimeout}
     * method has been exceeded and has at least attempted to cancel
     * the currently running {@code Statement}
     * @see #getResultSet
     * @see #getUpdateCount
     * @see #getMoreResults
     */
    boolean execute(String sql) throws SQLException;

• PreparedStatement: Statement子接口，预编译的Statement对象，运行预编译SQL语句，每次只改变其中的参数。该接口同样有上述3个方法（Statement），但是方法无需传sql字符串，因为在创建PreparedStatement实例时，已经预编译了SQL语句，只需通过setXxx(int paramerterIndex, Xxx value), Xxx为某种类型，比如SetString(1, “str”)， 第一个参数传入”str”;
• ResultSet: 结果集对象，可通过索引或列名获得列数据。

    /**
     * Moves the cursor froward one row from its current position.
     * A <code>ResultSet</code> cursor is initially positioned
     * before the first row; the first call to the method
     * <code>next</code> makes the first row the current row; the
     * second call makes the second row the current row, and so on.
     * <p>
     * When a call to the <code>next</code> method returns <code>false</code>,
     * the cursor is positioned after the last row. Any
     * invocation of a <code>ResultSet</code> method which requires a
     * current row will result in a <code>SQLException</code> being thrown.
     *  If the result set type is <code>TYPE_FORWARD_ONLY</code>, it is vendor specified
     * whether their JDBC driver implementation will return <code>false</code> or
     *  throw an <code>SQLException</code> on a
     * subsequent call to <code>next</code>.
     *
     * <P>If an input stream is open for the current row, a call
     * to the method <code>next</code> will
     * implicitly close it. A <code>ResultSet</code> object's
     * warning chain is cleared when a new row is read.
     *
     * @return <code>true</code> if the new current row is valid;
     * <code>false</code> if there are no more rows
     * @exception SQLException if a database access error occurs or this method is
     *            called on a closed result set
     */
    boolean next() throws SQLException;

next()方法将记录指针定位到下一行:

the first call to the method next makes the first row the current row; the second call makes the second row the current row, and so on.

When a call to the next method returns false,the cursor is positioned after the last row.

getXxx(int columnIndex)：在现在所在的行获得指定列columnIndex的值 
getXxx(String columnLabel)：在现在所在的行获得列名columnLabel的值

2.JDBC实现

* 流程如下：*

• 加载数据库驱动
• DriverManager获得数据库连接Conneciton
• 通过Connection对象创建Statement或PreparedStatement
• 使用Statement或PreparedStatement执行SQL
• 操作结果集ResultSet
• 回收数据库资源，ResultSet,Statement或PreparedStatement,Connection等资源

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 插入数据
 * 
 * @author hugang
 *
 */
public class JDBCTest {

    public static void main(String[] args) throws ClassNotFoundException {
        Class.forName("com.mysql.jdbc.Driver");
        try (Connection conn = DriverManager.getConnection(
                "jdbc:mysql://10.13.1.139:3306/test", "root", "test");
                PreparedStatement ps = conn
                        .prepareStatement("insert into teacher values(null, ?)");) {
            ps.setString(1, "nice");
            ps.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

}

/**
 * 查询数据
 * 
 * @author hugang
 *
 */
public class JDBCTest {

    public static void main(String[] args) throws ClassNotFoundException {
        Class.forName("com.mysql.jdbc.Driver");
        try (Connection conn = DriverManager.getConnection(
                "jdbc:mysql://10.13.1.139:3306/test", "root", "test");
                PreparedStatement ps = conn
                        .prepareStatement("select * from teacher");) {

            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                System.out.println(rs.getInt("teacher_id") + " " + rs.getString("teacher_name"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

}

3.数据库连接池

建立足够的数据库连接，并将这些连接组成一个连接池，应用每次请求数据库连接时，无需重新打开物理连接，直接从连接池取出已有连接，使用完毕，将连接归还给连接池，供其他请求使用，大大提高系统性能；无需每次请求数据都要打开一个物理连接，使用完毕立即关闭连接，频繁的打开关闭会导致系统性能下降。

* 以C3PO为例，介绍用法：*

C3P0优势：自动清理Connection, Statement 和 ResultSet。

import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.SQLException;

import com.mchange.v2.c3p0.ComboPooledDataSource;

/**
 * 连接池只需创建一次，使用static语句块初始化
 * @author hugang
 *
 */
public class C3P0Pool {
    // C3P0连接池实例
    static ComboPooledDataSource ds = new ComboPooledDataSource();
    static {
        try {
            // 设置数据库驱动
            ds.setDriverClass("com.mysql.jdbc.Driver");
            // 连接数据库url
            ds.setJdbcUrl("jdbc:mysql://10.13.1.139:3306/test");
            // 数据库账号
            ds.setUser("root");
            // 数据库密码
            ds.setPassword("test");
            // 连接池最小连接数
            ds.setMinPoolSize(10);
            // 连接池最大连接数
            ds.setMaxPoolSize(50);
            // 连接池初始连接数
            ds.setInitialPoolSize(20);

        } catch (PropertyVetoException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }
    // 返回连接池Connection
     public static Connection getConnection(){
            try {
                return ds.getConnection();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            return null;
        }
}

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 使用C3P0连接池
 * 查询数据
 * 
 * @author hugang
 *
 */
public class JDBCTest {

    public static void main(String[] args) throws ClassNotFoundException {

        try (Connection conn = C3P0Pool.getConnection();
                PreparedStatement ps = conn
                        .prepareStatement("select * from teacher");) {

            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                System.out.println(rs.getInt("teacher_id") + " " + rs.getString("teacher_name"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

}