了解JDBC

 JDBC Intro

　　JDBC stands for Java Database Connectivity. Java与各种数据库连接的标准Java api.

The JDBC library includes APIs for each of the tasks commonly associated with database usage:
　　 Making a connection to a database

　　 Creating SQL or MySQL statements

　　 Executing that SQL or MySQL queries in the database

　　 Viewing & Modifying the resulting records

 

jdbc API由以下两个package构成：

• java.sql
• javax.sql

Package java.sql Description

　　Provides the API for accessing and processing data stored in a data source (usually a relational database) using the Java^TM programming language. This API includes a framework whereby different drivers can be installed dynamically to access different data sources. Although the JDBC^TM API is mainly geared to passing SQL statements to a database, it provides for reading and writing data from any data source with a tabular format. The reader/writer facility, available through the javax.sql.RowSet group of interfaces, can be customized to use and update data from a spread sheet, flat file, or any other tabular data source.

Package javax.sql Description

　　Provides the API for server side data source access and processing from the Java^TM programming language. This package supplements the java.sql package and, as of the version 1.4 release, is included in the Java Platform, Standard Edition (Java SE^TM). It remains an essential part of the Java Platform, Enterprise Edition (Java EE^TM).

　　The javax.sql package provides for the following:

1. The DataSource interface as an alternative to the DriverManager for establishing a connection with a data source
2. Connection pooling and Statement pooling
3. Distributed transactions
4. Rowsets

　　Applications use the DataSource and RowSet APIs directly, but the connection pooling and distributed transaction APIs are used internally by the middle-tier infrastructure.

 

JDBC API主要包括（斜体代表接口，需驱动程序提供者来具体实现）：

• DriverManager：负责加载各种不同驱动程序（Driver），并根据不同的请求，向调用者返回相应的数据库连接（Connection）。
• Driver：驱动程序，会将自身加载到DriverManager中去，并处理相应的请求并返回相应的数据库连接（Connection）。
• Connection：数据库连接，负责进行与数据库间的通讯，SQL执行以及事务处理都是在某个特定Connection环境中进行的。可以产生用以执行SQL的Statement。
• Statement：用以执行SQL查询和更新（针对静态SQL语句和单次执行）。
• PreparedStatement：用以执行包含动态参数的SQL查询和更新（在服务器端编译，允许重复执行以提高效率）。
• CallableStatement：用以调用数据库中的存储过程。
• SQLException：代表在数据库连接的建立和关闭和SQL语句的执行过程中发生了例外情况（即错误）。

 

指定driver的方法(old)：

Class.forName("com.mysql.jdbc.Driver");  // method 1

new com.mysql.jdbc.Driver() ;    //method 2, mysql as an exmple, 创建driver对象，加载数据库驱动

System.setProperty("jdbc.drivers","com.mysql.jdbc.Driver");  // method 3

　　

注意JDBC 4.0 已不需要手动注册driver, JDBC文档原文：

　　Applications no longer need to explictly load JDBC drivers using Class.forName(). Existing programs which currently load JDBC drivers using Class.forName() will continue to work without modification.

　　JDBC 4.0 Drivers must include the file META-INF/services/java.sql.Driver

 JDBC4.0 Example

　　mysql-connector-java:

        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>6.0.4</version>
        </dependency>    

Code Example

Driver: mysql-connector-java

/**
 * Created by artificerPi on 2016/2/23.
 * JDBC Program, create database
 *
 */

// STEP 1. Import required packages
import java.sql.*;

public class JDBCExample {
    // JDBC driver name and database URL
    static final String JDBC_DRIVER="com.mysql.jdbc.Driver";
    static final String DB_URL="jdbc:mysql://localhost";

    // Database credentials
    static final String USER ="root";
    static final String PASS="passw0rd";

    public static void main(String[] args){
        Connection conn = null;
        Statement stmt = null;

        try{
            //STEP2 : Register JDBC driver
            Class.forName(JDBC_DRIVER);   // load driver class

            //STEP3 : Open a connection
            System.out.println("Connecting to database...");
            conn = DriverManager.getConnection(DB_URL,USER,PASS);

            // STEP 4: Execute a query
            System.out.println("Creating statement ...");
            stmt = conn.createStatement();

            String sql;
            sql="CREATE DATABASE STUDENTS";
            stmt.executeUpdate(sql);
            System.out.println("Database created successfully...");
        }catch(SQLException se){
            //Handle errors for JDBC
            se.printStackTrace();
        }catch (Exception e){
            // Handle errors for Class.forName
            e.printStackTrace();
        }finally {
            //STEP5: finally block used to close resources
            try{
                if (stmt != null)
                    stmt.close();
            }catch (SQLException se2){// nothing we can do
            }try{
                if(conn!=null)
                    conn.close();
            }catch (SQLException se){
                se.printStackTrace();
            }// end finally try
        }// end try
        System.out.println("GoodBye!");
    }//end main
}// end FirstExample

 

使用PreparedStatement

PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement("SELECT i.*, j.* FROM Omega i, Zappa j WHERE i = ? AND j = ?");
            // 使用问号作为参数的标示

            // 进行参数设置
            // 与大部分Java API中下标的使用方法不同，字段的下标从1开始，1代表第一个问号
            // 当然，还有其他很多针对不同类型的类似的PreparedStatement.setXXX()方法
            ps.setString(1, "Poor Yorick");
            ps.setInt(2, 8008);

            // 结果集
            rs = ps.executeQuery();
            while (rs.next()) {
                int numColumns = rs.getMetaData().getColumnCount();
                for (int i = 1; i <= numColumns; i++) {
                    // 与大部分Java API中下标的使用方法不同，字段的下标从1开始
                    // 当然，还有其他很多的方式（ResultSet.getXXX()）获取数据
                    System.out.println("COLUMN " + i + " = " + rs.getObject(i));
                }
            }
        } catch (SQLException e) {
            // 异常处理
        } finally { // 使用finally进行资源释放
            try {
                rs.close();
                ps.close();
            } catch (SQLException e) {
            } // 异常处理：忽略close()时的错误
        }

 

 

事务 Example

 boolean autoCommitDefault = conn.getAutoCommit();
        try {
            conn.setAutoCommit(false);
 
            /* 在此基于有事务控制的conn执行你的代码 */

            conn.commit();
        } catch (Throwable e) {
            try {
                conn.rollback();
            } catch (Throwable ignore) {
            }
            throw e;
        } finally {
            try {
                conn.setAutoCommit(autoCommitDefault);
            } catch (Throwable ignore) {
            }
        }

 

JDBC Cycle

 

 

参考：

　　http://www.tutorialspoint.com/jdbc/

　　https://docs.oracle.com/javase/8/docs/technotes/guides/jdbc/

　　https://zh.wikipedia.org/wiki/Java%E6%95%B0%E6%8D%AE%E5%BA%93%E8%BF%9E%E6%8E%A5

　　https://www.ntu.edu.sg/home/ehchua/programming/java/JDBC_Basic.html