JDBC基础

JDBC的全称是Java Database Connectivity,即Java数据库连接,它是一种可以执行SQL语句的Java API。程序可通过JDBC API连接到关系数据库,并使用结构化查询语言(SQL,数据库标准的查询语言)来完成对数据库的查询、更新

与其他数据库编程环境相比,JDBC为数据库开发提供了标准的API,使用JDBC开发的数据库应用可以跨平台运行,而且还可以跨数据库(如果全部使用标准的SQL语句)。也就是说如果使用JDBC开发一个数据库应用,则该应用既可以在Windows操作系统上运行,又可以在Unix等其他操作系统上运行,既可以使用MySQL数据库,又可以使用Oracle等其他的数据库,应用程序不需要做任何的修改

JDBC简介

Java语言的各种跨平台特性,都采用相似的结构。因为他们都需要让相同的程序在不同的平台上运行,所以需要中间的转换程序(为了实现Java程序的跨平台,Java为不同的操作系统提供了不同的Java虚拟机)。同样,为了JDBC程序可以跨平台,也需要不同的数据库厂商提供相应的驱动程序

clipboard.png

Sun提供的JDBC可以完成以下三个基本操作:

  • 建立与数据库的链接

  • 执行SQL语句

  • 获得SQL语句的执行结果

JDBC驱动程序

数据库驱动程序是JDBC程序和数据库之间的转换层,数据库驱动程序负责将JDBC调用映射成特定的数据库调用

clipboard.png

ODB,Open Database Connectivity,即开放数据库链接。ODBC和JDBC很像,严格来说,应该是JDBC模仿了ODBC是设计。ODBC也允许应用程序通过一种通用的API访问不同的数据库管理系统,从而使得基于ODBC的应用程序可以在不同的数据库之间切换。同样,ODBC也需要各数据库厂商提供相应的驱动程序,而ODBC负责管理这些驱动程序

JDBC驱动通常有如下4种类型

  • JDBC + ODBC桥的方式

  • 直接将JDBC API隐射成数据库特定的客户端API。这种驱动包含特定数据库的本地代码,用于访问特定数据库的客户端

  • 支持三层结构的JDBC访问方式,主要用于Applet阶段,通过Applet访问数据库

  • 纯java的,直接与数据库实例交互,。这种驱动是智能型的,它知道数据库使用的底层协议,是目前最流行的JDBC驱动

通常建议选择第4种JDBC驱动,这种驱动避开了本地代码,减少了应用开发的复杂性,也减少了产生冲突和出错的可能。如果对性能有严格的要求,则可以考虑使用第2种JDBC驱动,但使用这种驱动,则势必增加编码和维护的困难

JDBC比ODBC多了如下几个优势

  • ODBC更复杂,ODBC中有几个命令需要配置很多复杂的选项,而JDBC则采用简单、直观的方式来管理数据库连接

  • JDBC比ODBC安全性更高,更易部署

JDBC的经典用法

JDBC 4.2常用接口和类简介

JAVA8关于JDBC4.2的新增功能:
DriverManager:用于管理JDBC驱动的服务类。程序中使用该类的主要功能是获取Connection对象,该类包含如下方法

  • public static synchronized Connection getConnection(String url, String user, String password) throws SQLException:该方法获得url对应数据库的连接

Connection

Connection:代表数据库连接对象,每个Connection代表一个物理连接会话。要想访问数据库,必须先得到数据库连接。该接口的常用方法如下:

  • Statement createStatement() throws SQLException:该方法返回一个Statement对象

  • PreparedStatement prepareStatement(String sql) throws SQLException:该方法返回预编译的Statement对象,即将SQL语句提交到数据库进行预编译

  • CallableStatement prepareCall(String sql) throws SQLException:该方法返回CallableStatement对象,该对象用于调用存储过程

上面三个方法都返回用于执行SQL语句的Statement对象,PreparedStatement、CallableStatement是Statement的子类,只有获得了Statement之后才可以执行SQL语句

除此之外,Connection还有如下几个用于控制事务的方法:

  • Savepoint setSavepoint() throws SQLException:创建一个保存点

  • Savepoint setSavepoint(String name):以指定名字来创建一个保存点

  • void setTransactionIsolation(int level):设置事务的隔离级别

  • void rollback():回滚事务

  • void rollback(Savepoint savepoint):将事务回滚到指定的保存点

  • void setAutoCommit(boolean autoCommit):关闭自动提交,打开事务

  • void commit() throws SQLException:提交事务

Java7位Connection新增了setSchema(String schema)、getSchema()两个方法,这两个方法用于控制该Connection访问的数据库Schema。还为Connection新增了setNetworkTimeout(Executor executor, int milliseconds)、getNetworkTimeout()两个方法来控制数据库连接的超时行为

Statement

Statement:用于执行SQL语句的工具接口。该对象既可以执行DDL、DCL语句,也可以用于执行DML语句,还可以用于执行SQL查询。当执行SQL查询时,返回查询到的结果集。它的常用方法如下:

  • ResultSet executeQuery(String sql) throws SQLException:该方法用于执行查询语句,并返回查询结果对应ResultSet对象。该方法只能用于执行查询语句

  • int executeUpdate(String sql) throws SQLException:该方法用于执行DML语句,并返回受影响的行数;该方法也可用于执行DDL语句,执行DDL语句将返回0

  • boolean execute(String sql) throws SQLException:该方法可以执行任何SQL语句。如果执行后第一个结果为ResultSet对象,则返回true;如果执行后第一个结果为受影响的行数或没有任何结果,则返回false

Java7为Statement新增了closeOnCompletion()方法,如果Statement执行了此方法,则当所有依赖于该Statement的ResultSet关闭时,该Statement会自动关闭。Java7还为Statement提供了一个isCloseOnCompletion()方法,该方法用于判断该Statement是否打开了“closeOnCompletion”

PreparedStatement

PreparedStatement:预编译的Statement对象,PreparedStatement是Statement的子接口,它允许数据库预编译SQL语句(这些SQL语句通常带有参数),以后每次只改变sql命令的参数,避免数据库每次都需要编译SQL语句,无需再传入SQL语句,因此性能更好。使用PreparedStatement执行SQL语句时,无须再传入SQL语句,只要为预编译的SQL语句传入参数值即可

PreparedStatement同样有executeQuery()、executeUpdate()和execute()方法,只是这三个方法无须接收SQL字符串,因为PreparedStatement对象已预编译了SQL命令,只要为这些方法传入参数即可。所以它比Statement多了如下方法:

  • void setXxx(int parameterIndex, Xxx value):该方法根据传入参数值的类型不同,需要使用不同的方法。传入的值根据索引传给SQL语句中指定位置的参数

ResultSet

ResultSet:结果集对象。该对象包含访问查询结果的方法,ResultSet可以通过列索引或列名获得列数据。它包含了如下常用方法来移动记录指针

  • void close():释放ResultSet对象

  • boolean absolute(int row):将结果集的记录指针移动到第row行,如果row是负数,则移动到倒数第row行,如果移动后的记录指针指向一条有效记录,则该方法返回true

  • void beforeFisrt():将ResultSet的记录指针定位到首行之前,这是ResultSet结果集记录指针的初始状态——记录指针的起始位置位于第一行之前。

  • boolean first():将ResultSet的记录指针定位到首行。如果移动后的记录指针指向一条有效记录,则该方法返回true

  • boolean previous():将ResultSet的记录指针定位到上一行,如果移动后的记录指针指向一条有效记录,则该方法返回true

  • boolean next():将结果集的记录指针定位到下一行,如果移动后的记录指针指向一条有效的记录,则该方法返回true

  • boolean last():将结果集的记录指针定位到最后一行,如果移动后的记录指针指向一条有效的记录,则该方法返回true

  • void afterLast():将ResultSet的记录指针定位到最后一行之后

当把记录指针移动到指定行之后,ResultSet可通过getXxx(int columnIndex)或getXxx(String columnLabel)方法来获取当前行、指定列的值,前者根据列索引获取值,后者根据列名获取值

JDBC编程步骤

1 加载数据库驱动

通常使用Class类的forName()静态方法来加载驱动

// 加载驱动,driverClass就是数据库驱动类所对应的字符串
Class.forName(driverClass);
// 加载MySQL的驱动
Class.forName("com.mysql.jdbc.Driver");
// 加载Oracle的驱动
Class.forName("oracle.jabc.driver.OracleDriver");

2 通过DriverManager获取数据库的链接

// 获取数据库连接
DriverManager.getConnection(String url, Stirng user, String pass)

当使用DriverManager来获取链接,通常需要传入三个参数:数据库URL、登录数据库的用户名和密码

数据库URL通常遵循如下写法:jdbc是固定的;subprotocol指定连接到特定数据库的驱动;other和stuff也是不固定的

jdbc:subprotocol:other stuff

3 通过Connection对象创建Statement(或者PreparedStatement)对象

  • createStatement():创建基本的Statement对象

  • prepareStatement(String sql):根据传入的SQL语句创建预编译的Statement对象

  • prepareCall(String sql):根据传入的SQL语句创建CallableStatement对象

4 使用Statement执行SQL语句

  • execute():可以执行任何SQL语句,但比较麻烦

  • executeUpdate():主要用于执行DML和DDL语句。执行DML返回受影响的SQL语句行数,执行DDL返回0

  • executeQuery():只能执行查询语句,执行后返回代表查询结果的ResultSet对象

5 操作结果集

如果执行的SQL语句是查询语句,则执行结果将返回一个ResultSet对象,该对象里保存了SQL语句查询的结果。程序可以通过操作该ResultSet对象来取出查询结果。ResultSet对象主要提供了如下两类方法

  • next()、previous()、first()、last()、beforeFrist()、afterLast()、absolute()等移动指针的方法

  • getXxx()方法获取记录指针指向行,特定列的值。既可使用列名作为参数可读性更好、使用索引作为参数性能更好

6 回收数据库资源

包括关闭ResultSet、Statement和Connection等资源

import java.sql.*;
public class ConnMySql
{
    public static void main(String[] args) throws Exception
    {
        // 1.加载驱动,使用反射的知识,现在记住这么写。
        Class.forName("com.mysql.jdbc.Driver");
        try(
            // 2.使用DriverManager获取数据库连接,
            // 其中返回的Connection就代表了Java程序和数据库的连接
            // 不同数据库的URL写法需要查驱动文档知道,用户名、密码由DBA分配
            Connection conn = DriverManager.getConnection(
                "jdbc:mysql://127.0.0.1:3306/select_test"
                , "root" , "32147");
            // 3.使用Connection来创建一个Statment对象
            Statement stmt = conn.createStatement();
            // 4.执行SQL语句
            /*
            Statement有三种执行sql语句的方法:
            1 execute 可执行任何SQL语句。- 返回一个boolean值,
              如果执行后第一个结果是ResultSet,则返回true,否则返回false
            2 executeQuery 执行Select语句 - 返回查询到的结果集
            3 executeUpdate 用于执行DML语句。- 返回一个整数,
              代表被SQL语句影响的记录条数
            */
            ResultSet rs = stmt.executeQuery("select s.* , teacher_name"
                + " from student_table s , teacher_table t"
                + " where t.teacher_id = s.java_teacher"))
        {
            // ResultSet有系列的getXxx(列索引 | 列名),用于获取记录指针
            // 指向行、特定列的值,不断地使用next()将记录指针下移一行,
            // 如果移动之后记录指针依然指向有效行,则next()方法返回true。
            while(rs.next())
            {
                System.out.println(rs.getInt(1) + "\t"
                    + rs.getString(2) + "\t"
                    + rs.getString(3) + "\t"
                    + rs.getString(4));
            }
        }
    }
}

执行SQL语句的方式

使用Java8新增的executeLargeUpdate方法执行DDL和DML语句

以下程序示范了使用executeUpdate()方法(MySQL驱动暂不支持executeLargeUpdate()方法)创建数据表。该示例并没有直接把数据库连接信息写在程序里,而是使用一个mysql.ini文件(properties文件)来保存数据库连接信息,这是比较成熟的做法——当需要把应用程序从开发环境移植到生产环境时,无须修改源代码,只需修改mysql.ini配置文件即可

import java.util.*;
import java.io.*;
import java.sql.*;

public class ExecuteDDL
{
    private String driver;
    private String url;
    private String user;
    private String pass;
    public void initParam(String paramFile)
        throws Exception
    {
        // 使用Properties类来加载属性文件
        Properties props = new Properties();
        props.load(new FileInputStream(paramFile));
        driver = props.getProperty("driver");
        url = props.getProperty("url");
        user = props.getProperty("user");
        pass = props.getProperty("pass");
    }
    public void createTable(String sql)throws Exception
    {
        // 加载驱动
        Class.forName(driver);
        try(
        // 获取数据库连接
        Connection conn = DriverManager.getConnection(url , user , pass);
        // 使用Connection来创建一个Statment对象
        Statement stmt = conn.createStatement())
        {
            // 执行DDL,创建数据表
            stmt.executeUpdate(sql);
        }
    }
    public static void main(String[] args) throws Exception
    {
        ExecuteDDL ed = new ExecuteDDL();
        ed.initParam("mysql.ini");
        ed.createTable("create table jdbc_test "
            + "( jdbc_id int auto_increment primary key, "
            + "jdbc_name varchar(255), "
            + "jdbc_desc text);");
        System.out.println("-----建表成功-----");
    }
}

下面程序执行一条insert语句,这条insert语句会向刚刚建立的jdbc_test数据表中插入几条记录。因为使用了带子查询的insert语句,所以可以一次插入多条语句

import java.util.*;
import java.io.*;
import java.sql.*;

public class ExecuteDML
{
    private String driver;
    private String url;
    private String user;
    private String pass;

    public void initParam(String paramFile)
        throws Exception
    {
        // 使用Properties类来加载属性文件
        Properties props = new Properties();
        props.load(new FileInputStream(paramFile));
        driver = props.getProperty("driver");
        url = props.getProperty("url");
        user = props.getProperty("user");
        pass = props.getProperty("pass");
    }
    public int insertData(String sql)throws Exception
    {
        // 加载驱动
        Class.forName(driver);
        try(
            // 获取数据库连接
            Connection conn = DriverManager.getConnection(url
                , user , pass);
            // 使用Connection来创建一个Statment对象
            Statement stmt = conn.createStatement())
        {
            // 执行DML,返回受影响的记录条数
            return stmt.executeUpdate(sql);
        }
    }
    public static void main(String[] args)throws Exception
    {
        ExecuteDML ed = new ExecuteDML();
        ed.initParam("mysql.ini");
        int result = ed.insertData("insert into jdbc_test(jdbc_name,jdbc_desc)"
            + "select s.student_name , t.teacher_name "
            + "from student_table s , teacher_table t "
            + "where s.java_teacher = t.teacher_id;");
        System.out.println("--系统中共有" + result + "条记录受影响--");
    }
}

使用execute方法执行SQL语句

Statement的execute()方法几乎可以执行任何SQL语句,但它执行SQL语句时比较麻烦,通常没有必要使用execute()方法来执行SQL语句,使用executeQuery()或executeUpdate()方法更简单。但如果不清楚SQL语句的类型,则只能使用execute()方法来执行该SQL语句

  • getResult():获取该Statement执行查询语句所返回的ResultSet对象

  • getUpdateCount():获取该Statement()执行DML语句所影响的记录行数

import java.util.*;
import java.io.*;
import java.sql.*;

public class ExecuteSQL
{
    private String driver;
    private String url;
    private String user;
    private String pass;
    public void initParam(String paramFile)throws Exception
    {
        // 使用Properties类来加载属性文件
        Properties props = new Properties();
        props.load(new FileInputStream(paramFile));
        driver = props.getProperty("driver");
        url = props.getProperty("url");
        user = props.getProperty("user");
        pass = props.getProperty("pass");
    }
    public void executeSql(String sql)throws Exception
    {
        // 加载驱动
        Class.forName(driver);
        try(
            // 获取数据库连接
            Connection conn = DriverManager.getConnection(url
                , user , pass);
            // 使用Connection来创建一个Statement对象
            Statement stmt = conn.createStatement()
            )
        {
            // 执行SQL,返回boolean值表示是否包含ResultSet
            boolean hasResultSet = stmt.execute(sql);
            // 如果执行后有ResultSet结果集
            if (hasResultSet)
            {
                try(
                    // 获取结果集
                    ResultSet rs = stmt.getResultSet()
                    )
                {
                    // ResultSetMetaData是用于分析结果集的元数据接口
                    ResultSetMetaData rsmd = rs.getMetaData();
                    int columnCount = rsmd.getColumnCount();
                    // 迭代输出ResultSet对象
                    while (rs.next())
                    {
                        // 依次输出每列的值
                        for (int i = 0 ; i < columnCount ; i++ )
                        {
                            System.out.print(rs.getString(i + 1) + "\t");
                        }
                        System.out.print("\n");
                    }
                }
            }
            else
            {
                System.out.println("该SQL语句影响的记录有"
                    + stmt.getUpdateCount() + "条");
            }
        }
    }
    public static void main(String[] args) throws Exception
    {
        ExecuteSQL es = new ExecuteSQL();
        es.initParam("mysql.ini");
        System.out.println("------执行删除表的DDL语句-----");
        es.executeSql("drop table if exists my_test");
        System.out.println("------执行建表的DDL语句-----");
        es.executeSql("create table my_test"
            + "(test_id int auto_increment primary key, "
            + "test_name varchar(255))");
        System.out.println("------执行插入数据的DML语句-----");
        es.executeSql("insert into my_test(test_name) "
            + "select student_name from student_table");
        System.out.println("------执行查询数据的查询语句-----");
        es.executeSql("select * from my_test");
    }
}

使用PreparedStatement执行SQL语句

创建PreparedStatement对象使用Connection的preparedStatement()方法,该方法需要传入一个SQL字符串,该字符串可以包含符参数

// 创建一个PreparedStatement对象
pstmt = conn.preparedStatement("insert into student_table values(null,?,1)");

PreparedStatement也提供了execute()、executeUpdate()、executeQuery()三个方法来执行SQL语句,不过这三个方法无须参数,因为PreparedStatement提供了一系列的setXxx(int index, Xxx value)方法来传入参数值

如果程序很清楚PreparedStatement预编译SQL语句中各参数的类型,则使用相应的setXxx()方法来传入参数即可;如果程序不清楚编译SQL语句中各参数的类型,则可以使用setObject()方法来传入参数,由PreparedStatement来负责类型转换

下面程序示范使用Statement和PreparedStatement分别插入100条记录的对比。使用Statement需要传入100条SQL语句,但使用PreparedStatement则只需传入1条预编译的SQL语句,然后100次为该PreparedStatement的参数设值即可

import java.util.*;
import java.io.*;
import java.sql.*;

public class PreparedStatementTest
{
    private String driver;
    private String url;
    private String user;
    private String pass;
    public void initParam(String paramFile)throws Exception
    {
        // 使用Properties类来加载属性文件
        Properties props = new Properties();
        props.load(new FileInputStream(paramFile));
        driver = props.getProperty("driver");
        url = props.getProperty("url");
        user = props.getProperty("user");
        pass = props.getProperty("pass");
        // 加载驱动
        Class.forName(driver);
    }
    public void insertUseStatement()throws Exception
    {
        long start = System.currentTimeMillis();
        try(
            // 获取数据库连接
            Connection conn = DriverManager.getConnection(url
                , user , pass);
            // 使用Connection来创建一个Statment对象
            Statement stmt = conn.createStatement())
        {
            // 需要使用100条SQL语句来插入100条记录
            for (int i = 0; i < 100 ; i++ )
            {
                stmt.executeUpdate("insert into student_table values("
                    + " null ,'姓名" + i + "' , 1)");
            }
            System.out.println("使用Statement费时:"
                + (System.currentTimeMillis() - start));
        }
    }
    public void insertUsePrepare()throws Exception
    {
        long start = System.currentTimeMillis();
        try(
            // 获取数据库连接
            Connection conn = DriverManager.getConnection(url
                , user , pass);
            // 使用Connection来创建一个PreparedStatement对象
            PreparedStatement pstmt = conn.prepareStatement(
                "insert into student_table values(null,?,1)"))

        {
            // 100次为PreparedStatement的参数设值,就可以插入100条记录
            for (int i = 0; i < 100 ; i++ )
            {
                pstmt.setString(1 , "姓名" + i);
                pstmt.executeUpdate();
            }
            System.out.println("使用PreparedStatement费时:"
                + (System.currentTimeMillis() - start));
        }
    }
    public static void main(String[] args) throws Exception
    {
        PreparedStatementTest pt = new PreparedStatementTest();
        pt.initParam("mysql.ini");
        pt.insertUseStatement();
        pt.insertUsePrepare();
    }
}

SQL注入是一个较常见的Cracker入侵方式,它利用SQL语句的漏洞来入侵。以下程序以一个简单的登录窗口为例来介绍这种SQL注入的结果。下面登录窗口包含两个文本框,一个用于输入用户名,一个用于输入密码,系统根据用户输入与jdbc_test表里的记录进行匹配,如果找到相应记录则提示登陆成功

public class LoginFrame
{
    private final String PROP_FILE = "mysql.ini";
    private String driver;
    // url是数据库的服务地址
    private String url;
    private String user;
    private String pass;
    // 登录界面的GUI组件
    private JFrame jf = new JFrame("登录");
    private JTextField userField = new JTextField(20);
    private JTextField passField = new JTextField(20);
    private JButton loginButton = new JButton("登录");
    public void init()throws Exception
    {
        Properties connProp = new Properties();
        connProp.load(new FileInputStream(PROP_FILE));
        driver = connProp.getProperty("driver");
        url = connProp.getProperty("url");
        user = connProp.getProperty("user");
        pass = connProp.getProperty("pass");
        // 加载驱动
        Class.forName(driver);
        // 为登录按钮添加事件监听器
        loginButton.addActionListener(e -> {
            // 登录成功则显示“登录成功”
            if (validate(userField.getText(), passField.getText()))
            {
                JOptionPane.showMessageDialog(jf, "登录成功");
            }
            // 否则显示“登录失败”
            else
            {
                JOptionPane.showMessageDialog(jf, "登录失败");
            }
        });
        jf.add(userField , BorderLayout.NORTH);
        jf.add(passField);
        jf.add(loginButton , BorderLayout.SOUTH);
        jf.pack();
        jf.setVisible(true);
    }
    private boolean validate(String userName, String userPass)
    {
        // 执行查询的SQL语句
        String sql = "select * from jdbc_test "
            + "where jdbc_name='" + userName
            + "' and jdbc_desc='" + userPass + "'";
        System.out.println(sql);
        try(
            Connection conn = DriverManager.getConnection(url , user ,pass);
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql))
        {
            // 如果查询的ResultSet里有超过一条的记录,则登录成功
            if (rs.next())
            {
                return true;
            }
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        return false;
    }

    public static void main(String[] args) throws Exception
    {
        new LoginFrame().init();
    }
}

如果用户正常输入其用户名、密码,输入正确时可以正常登陆,输入错误将提示输入失败。但如果这个用户是一个Cracker,可以输入'or true or',也会显示登陆成功。运行的后台可以看到如下SQL语句

# 利用SQL注入后生成的SQL语句
select * from jdbc_test where jdbc_name = '' or true or '' and jdbc_desc = ''

如果换成使用PreparedStatement来执行验证,而不是直接使用Statement

private boolean validate(String userName, String userPass)
{
    try(
        Connection conn = DriverManager.getConnection(url
            , user ,pass);
        PreparedStatement pstmt = conn.prepareStatement(
            "select * from jdbc_test where jdbc_name=? and jdbc_desc=?"))
    {
        pstmt.setString(1, userName);
        pstmt.setString(2, userPass);
        try(
            ResultSet rs = pstmt.executeQuery())
        {
            //如果查询的ResultSet里有超过一条的记录,则登录成功
            if (rs.next())
            {
                return true;
            }
        }
    }
    catch(Exception e)
    {
        e.printStackTrace();
    }
    return false;
}
  • PreparedStatement预编译SQL语句,性能更好

  • PreparedStatement无须“拼接”SQL字符串,编程更简单

  • 使用PreparedStatement可防止SQL注入,安全性更好

使用PreparedStatement执行带占位符参数的SQL语句时,SQL语句中的占位符参数只能代替普通值,不要使用占位符参数代替表名、列名等数据库对象,更不要用占位符参数来代替SQL语句中的insert、select等关键字

使用CallableStatement调用存储过程

MySQL数据库中创建一个简单的存储过程的SQL语句

delimiter //
create procedure add_pro(a int, b int ,out sum int)
begin
set sum = a + b;
end;
//

上面的SQL语句将MySQL的语句结束符改为双斜线(//),这样就可以在创建存储过程中使用分号作为分隔符(默认使用分号作为语句结束符)程序创建了名为add_pro的存储过程,该存储过程包含三个参数:a、b是传入参数,而sum使用out修饰,是传出参数

调用存储过程使用CallableStatement,通过Connection的prepareCall()方法来创建CallableStatement对象,创建该对象时需要传入调用存储过程的SQL语句。调用存储过程的SQL语句总是这种格式:{call 过程名(?,?,?...)},其中的问号作为存储过程参数的占位符

// 使用Connection来创建一个CallableStatement对象
cstmt = conn.prepareCall("{call add_pro(?,?,?)}");

存储过程的参数既有传入参数,也有传出参数,所谓传入参数就是Java程序必须为这些参数传入值,可以通过CallableStatement的setXxx()方法为传入参数设置值;所谓传出参数就是Java程序可以通过该参数获取存储过程里的值,CallableStatement需要调用registerOutParameter()方法来注册该参数

// 注册CallableStatement的第三个参数是int类型
cstmt.registerOutParameter(3, Types.INTEGER);

之后调用CallableStatement的execute()方法来执行存储过程,执行结束后通过CallableStatement对象的getXxx(int index)方法来获取指定传出参数的值

import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import java.util.*;
import java.io.*;
import java.sql.*;

public class CallableStatementTest
{
    private String driver;
    private String url;
    private String user;
    private String pass;
    public void initParam(String paramFile)throws Exception
    {
        // 使用Properties类来加载属性文件
        Properties props = new Properties();
        props.load(new FileInputStream(paramFile));
        driver = props.getProperty("driver");
        url = props.getProperty("url");
        user = props.getProperty("user");
        pass = props.getProperty("pass");
    }
    public void callProcedure()throws Exception
    {
        // 加载驱动
        Class.forName(driver);
        try(
            // 获取数据库连接
            Connection conn = DriverManager.getConnection(url
                , user , pass);
            // 使用Connection来创建一个CallableStatment对象
            CallableStatement cstmt = conn.prepareCall(
                "{call add_pro(?,?,?)}"))
        {
            cstmt.setInt(1, 4);
            cstmt.setInt(2, 5);
            // 注册CallableStatement的第三个参数是int类型
            cstmt.registerOutParameter(3, Types.INTEGER);
            // 执行存储过程
            cstmt.execute();
            // 获取,并输出存储过程传出参数的值。
            System.out.println("执行结果是: " + cstmt.getInt(3));
        }
    }
    public static void main(String[] args) throws Exception
    {
        CallableStatementTest ct = new CallableStatementTest();
        ct.initParam("mysql.ini");
        ct.callProcedure();
    }
}

管理结果集

JDBC使用ResultSet来封装执行查询得到的查询结果,然后通过移动ResultSet的记录指针来取出结果集的内容。除此之外,JDBC还允许通过ResultSet来更新记录,并提供了ResultSetMetaData来获得ResultSet对象的相关信息

可滚动、可更新的结果集

可滚动的结果集:可以使用absolute()、previous()、afterLast()等方法只有移动指针记录的ResultSet

以默认形式打开的ResultSet是不可更新的,如果希望创建可更新的ResultSet,则必须在Connection在创建Statement或PreparedStatement时,传入额外的参数:

  • resultSetType:控制ResultSet的类型,该参数可以取如下三个值

    • ResultSet.TYPE_FORWARD_ONLY:该常量控制记录指针只能向前移动

    • ResultSet.TYPE_SCROLL_INSENSITIVE:该常量控制记录指针自由移动(可滚动结果集),但底层的数据改变不影响结果集ResultSet的内容

    • ResultSet.TYPE_SCROLL_SENSITIVE:该常量控制记录指针自由移动,但底层数据的影响会改变结果集ResultSet的内容

  • resultSetConcurrency:控制ResultSet的并发类型,该参数可以接收如下两个值

    • ResultSet.CONCUR_READ_ONLY:该常量表示ResultSet是只读并发模式(默认)

    • ResultSet.CONCUR_UPDATABLE:该常量表示ResultSet是更新并发模式

// 使用Connection创建一个PreparedStatement对象
// 传入控制结果集可滚动、可更新的参数
pstmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

可更新的结果集还需要满足如下两个条件:

  • 所有数据都应该来自一个表

  • 选出的数据集必须包含主列键

通过该PreparedStatement创建的ResultSet就是可滚动的、可更新的,程序可调用的updateXxx(int columnIndex, Xxx value)方法来修改记录指针所指记录、特定列的值,最后调用ResultSet的updateRow()方法来提交修改

Java8为ResultSet添加了updateObject(String columnLabel, Object x, SQLType targetSqlType)和updateObject(int columnIndex, Object x, SQLType targetSqlType)两个默认方法,这两个方法可以直接用Object来修改记录指针所指记录、特定列的值,其中SQLType用于指定该数据列的类型

import java.util.*;
import java.io.*;
import java.sql.*;

public class ResultSetTest
{
    private String driver;
    private String url;
    private String user;
    private String pass;
    public void initParam(String paramFile)throws Exception
    {
        // 使用Properties类来加载属性文件
        Properties props = new Properties();
        props.load(new FileInputStream(paramFile));
        driver = props.getProperty("driver");
        url = props.getProperty("url");
        user = props.getProperty("user");
        pass = props.getProperty("pass");
    }
    public void query(String sql)throws Exception
    {
        // 加载驱动
        Class.forName(driver);
        try(
            // 获取数据库连接
            Connection conn = DriverManager.getConnection(url , user , pass);
            // 使用Connection来创建一个PreparedStatement对象
            // 传入控制结果集可滚动,可更新的参数。
            PreparedStatement pstmt = conn.prepareStatement(sql
                , ResultSet.TYPE_SCROLL_INSENSITIVE
                , ResultSet.CONCUR_UPDATABLE);
            ResultSet rs = pstmt.executeQuery())
        {
            rs.last();
            int rowCount = rs.getRow();
            for (int i = rowCount; i > 0 ; i-- )
            {
                rs.absolute(i);
                System.out.println(rs.getString(1) + "\t"
                    + rs.getString(2) + "\t" + rs.getString(3));
                // 修改记录指针所有记录、第2列的值
                rs.updateString(2 , "学生名" + i);
                // 提交修改
                rs.updateRow();
            }
        }
    }
    public static void main(String[] args) throws Exception
    {
        ResultSetTest rt = new ResultSetTest();
        rt.initParam("mysql.ini");
        rt.query("select * from student_table");
    }
}

处理Blob类型数据

Blob——Binary long Object——二进制长对象,Blob列通常用于存储大文件,典型的Blob内容是一张图片或者一个声音文件,由于他们的特殊性,必须使用特殊的方式来存储。使用Blob列可以把照片声音等文件的二进制数据保存在数据库里,并可以从数据库里恢复指定文件

如果需要将图片插入数据库,显然不能通过普通的SQL语句来完成,因为有一个关键的问题,Blob常量无法表示,所以将Blob数据插入数据库需要使用PreparedStatement。该对象有一个方法:setBinaryStream(int parameterIndex, InputStream x)该方法可以为指定参数传入二进制流,从而可以实现将Blob数据保存到数据库的功能

当需要从ResultSet里取出Blob数据时,可以调用ResultSet的getBlob(int columnIndex)方法,该方法将返回一个Blob对象Blob对象提供了getBinaryStream()方法获取该获取该Blob数据的输入流,也可以使用Blob对象的getBytes()方法直接取出该Blob对象封装的二进制数据

为了把图片放入数据库,本程序先使用如下SQL语句来建立一个数据表:

create table img_table 
{ 
img_id int auto_increment primary key, 
img_name varchar(255), 
# 创建一个mediumblob类型的数据列,用于保存图片数据 
ima_data mediumblob 
};

img_data列使用mediumblob类型,而不是blob类型。因为MySQL数据库里的blob类型最多只能存储64kb的内容,所以使用mediumblob类型,该类型可以存储16M内容

// ---------将指定图片放入数据库---------
public void upload(String fileName)
{
    // 截取文件名
    String imageName = fileName.substring(fileName.lastIndexOf('\\') + 1, fileName.lastIndexOf('.'));
    File f = new File(fileName);
    try(
        InputStream is = new FileInputStream(f)
        )
    {
        // 设置图片名参数
        insert.setString(1, imageName);
        // 设置二进制流参数
        insert.setBinaryStream(2, is, (int)f.length());
        int affect = insert.executeUpdate();
        if (affect == 1)
        {
            // 重新更新ListModel,将会让JList显示最新的图片列表
            fillListModel();
        }
    }
    catch (Exception e)
    {
        e.printStackTrace();
    }
}
// ---------根据图片ID来显示图片----------
public void showImage(int id)throws SQLException
{
    // 设置参数
    query.setInt(1, id);
    try(
        // 执行查询
        ResultSet rs = query.executeQuery()
        )
    {
        if (rs.next())
        {
            // 取出Blob列
            Blob imgBlob = rs.getBlob(1);
            // 取出Blob列里的数据
            ImageIcon icon=new ImageIcon(imgBlob.getBytes(1L, (int)imgBlob.length()));
            imageLabel.setIcon(icon);
        }
    }
}

使用resultsetmetaData分析结果集

描述ResultSet信息的数据——ResultSetMetaData

MetaData即元数据,即描述其它数据的数据,因此ResultSetMetaData封装了描述ResultSet对象的数据

ResultSet的getMetaData()方法返回该ResultSet对应的ResultSetMetaData对象,就可通过ResultSetMetaData提供的大量方法返回ResultSet的描述信息

  • int getColumnCount():返回该ResultSet的列数量

  • String getColumnName(int column):返回指定索引的列名

  • int getColumnType(int column):返回指定索引的列类型

Java7的RowSet1.1

RowSet接口继承了ResultSet接口,RowSet接口下包含JdbcRowSet、CachedRowSet、FilteredRowSet、JoinRowSet和WebRowSet常用子接口。除了JdbcRowSet需要保持与数据库的连接之外,其余4个子接口都是离线的RowSet,无须保持与数据库的连接

RowSet默认是一个可滚动,可更新,可序列化的结果集,而且它作为JavaBeans,可以方便地在网络间传输,用于两端的数据同步。对于离线RowSet而言,程序在创建RowSet时已把数据从底层数据库读取到了内存,因此可以充分利用计算机的内存,从而降低数据库服务器的负载,提供程序性能

RowSet规范的接口类图
clipboard.png

Java7新增的RowSetFactory与RowSet

RowSet接口中定义的常用方法:

  • setUrl(String url):设置该RowSet要访问的数据库的URL

  • setUsername(String name):设置该RowSet要访问的数据库的用户名

  • setPassword(String password):设置该RowSet要访问的数据库的密码

  • setCommand(String sql):设置使用该sql语句的查询结果来装填该RowSet

  • execute():执行查询

  • populate(ResultSet rs):让该RowSet直接包装给定的ResultSet对象

Java7新增了RowSetProvider类和RowSetFactory接口,其中RowSetProvider负载创建RowSetFactory,而RowSetFactory则提供了如下方法来创建RowSet实例:

  • CachedRowSet createCachedRowSet():创建一个默认的CachedRowSet

  • FilteredRowSet createFilteredRowSet():创建一个默认的FilteredRowSet

  • JoinRowSet createJoinRowSet():创建一个默认的JoinRowSet

  • WebRowSet createWebRowSet():创建一个默认的WebRowSet

  • JdbcRowSet createJdbcRowSet():创建一个默认的JdbcRowSet

提供使用RowSetFactory,就可以把应用程序与RowSet实现类分离开,避免直接使用JdbcRow SetImpl等非公开的API,也更有利于后期的升级、扩展

import java.util.*;
import java.io.*;
import java.sql.*;
import javax.sql.rowset.*;

public class RowSetFactoryTest
{
    private String driver;
    private String url;
    private String user;
    private String pass;
    public void initParam(String paramFile) throws Exception
    {
        // 使用Properties类来加载属性文件
        Properties props = new Properties();
        props.load(new FileInputStream(paramFile));
        driver = props.getProperty("driver");
        url = props.getProperty("url");
        user = props.getProperty("user");
        pass = props.getProperty("pass");
    }

    public void update(String sql)throws Exception
    {
        // 加载驱动
        Class.forName(driver);
        // 使用RowSetProvider创建RowSetFactory
        RowSetFactory factory = RowSetProvider.newFactory();
        try(
            // 使用RowSetFactory创建默认的JdbcRowSet实例
            JdbcRowSet jdbcRs = factory.createJdbcRowSet()
            )
        {
            // 设置必要的连接信息
            jdbcRs.setUrl(url);
            jdbcRs.setUsername(user);
            jdbcRs.setPassword(pass);
            // 设置SQL查询语句
            jdbcRs.setCommand(sql);
            // 执行查询
            jdbcRs.execute();
            jdbcRs.afterLast();
            // 向前滚动结果集
            while (jdbcRs.previous())
            {
                System.out.println(jdbcRs.getString(1)
                    + "\t" + jdbcRs.getString(2)
                    + "\t" + jdbcRs.getString(3));
                if (jdbcRs.getInt("student_id") == 3)
                {
                    // 修改指定记录行
                    jdbcRs.updateString("student_name", "源博雅");
                    jdbcRs.updateRow();
                }
            }
        }
    }
    public static void main(String[] args)throws Exception
    {
        RowSetFactoryTest jt = new RowSetFactoryTest();
        jt.initParam("mysql.ini");
        jt.update("select * from student_table");
    }
}

离线RowSet

离线RowSet会直接将底层数据读入内存中,封装成RowSet对象,而RowSet对象则完全可以当成Java Bean来使用。因此不仅安全,而且编程简单。CachedRowSet是所有离线RowSet的父接口

如下程序①处调用了RowSet的populate(ResultSet rs)方法来包装给的的ResultSet,接着关闭了ResultSet、Statement、Connection等数据库资源。如果程序直接返回ResultSet,那么这个Result无法使用——因为底层的Connection已经关闭;但程序返回的是CachedRowSet,一个离线RowSet,因此程序依然可以读取、修改RowSet中的记录

为了将程序对离线RowSet所做的修改同步到底层数据库,程序在调用RowSet的acceptChanges()方法时必须传入Connection

public class CachedRowSetTest
{
    private static String driver;
    private static String url;
    private static String user;
    private static String pass;
    public void initParam(String paramFile) throws Exception
    {
        // 使用Properties类来加载属性文件
        Properties props = new Properties();
        props.load(new FileInputStream(paramFile));
        driver = props.getProperty("driver");
        url = props.getProperty("url");
        user = props.getProperty("user");
        pass = props.getProperty("pass");
    }

    public CachedRowSet query(String sql) throws Exception
    {
        // 加载驱动
        Class.forName(driver);
        // 获取数据库连接
        Connection conn = DriverManager.getConnection(url, user, pass);
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        // 使用RowSetProvider创建RowSetFactory
        RowSetFactory factory = RowSetProvider.newFactory();
        // 创建默认的CachedRowSet实例
        CachedRowSet cachedRs = factory.createCachedRowSet();
        // 使用ResultSet装填RowSet
        cachedRs.populate(rs);    // ①
        // 关闭资源
        rs.close();
        stmt.close();
        conn.close();
        return cachedRs;
    }
    public static void main(String[] args)throws Exception
    {
        CachedRowSetTest ct = new CachedRowSetTest();
        ct.initParam("mysql.ini");
        CachedRowSet rs = ct.query("select * from student_table");
        rs.afterLast();
        // 向前滚动结果集
        while (rs.previous())
        {
            System.out.println(rs.getString(1)
                + "\t" + rs.getString(2)
                + "\t" + rs.getString(3));
            if (rs.getInt("student_id") == 3)
            {
                // 修改指定记录行
                rs.updateString("student_name", "安倍晴明");
                rs.updateRow();
            }
        }
        // 重新获取数据库连接
        Connection conn = DriverManager.getConnection(url, user, pass);
        conn.setAutoCommit(false);
        // 把对RowSet所做的修改同步到底层数据库
        rs.acceptChanges(conn);
    }
}

离线RowSet的查询分页

CachedRowSet的分页功能:一次只装载ResultSet里的某几条记录,这样就可以避免CachedRowSet占用内存过大的问题

CachedRowSet提供了如下方法来控制分页:

  • populate(ResultSet rs, int startRow):使用给定的Result装填RowSet,从ResultSet的第startRow条记录可是装填

  • setPageSize(int pageSize):设置CachedRowSet每次返回记录条数

  • previousPage():在底层ResultSet可用情况下,让CachedRowSet读取上一页记录

  • nextPage():在底层ResultSet可用情况下,让CachedRowSet读取下一页记录

    public class CachedRowSetPage
    {

    private String driver;
    private String url;
    private String user;
    private String pass;
    public void initParam(String paramFile)throws Exception
    {
        // 使用Properties类来加载属性文件
        Properties props = new Properties();
        props.load(new FileInputStream(paramFile));
        driver = props.getProperty("driver");
        url = props.getProperty("url");
        user = props.getProperty("user");
        pass = props.getProperty("pass");
    }
    
    public CachedRowSet query(String sql, int pageSize, int page) throws Exception
    {
        // 加载驱动
        Class.forName(driver);
        try(
            // 获取数据库连接
            Connection conn = DriverManager.getConnection(url , user , pass);
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql)
            )
        {
            // 使用RowSetProvider创建RowSetFactory
            RowSetFactory factory = RowSetProvider.newFactory();
            // 创建默认的CachedRowSet实例
            CachedRowSet cachedRs = factory.createCachedRowSet();
            // 设置每页显示pageSize条记录
            cachedRs.setPageSize(pageSize);
            // 使用ResultSet装填RowSet,设置从第几条记录开始
            cachedRs.populate(rs, (page - 1) * pageSize + 1);
            return cachedRs;
        }
    }
    public static void main(String[] args)throws Exception
    {
        CachedRowSetPage cp = new CachedRowSetPage();
        cp.initParam("mysql.ini");
        CachedRowSet rs = cp.query("select * from student_table", 3, 2);   // ①
        // 向后滚动结果集
        while (rs.next())
        {
            System.out.println(rs.getString(1)
                + "\t" + rs.getString(2)
                + "\t" + rs.getString(3));
        }
    }

    }

事务处理

事务的概念和MySQL事务支持

事务是由一步或几步数据库操作序列组成的逻辑执行单元,这系列操作要么全部执行,要么全部放弃执行。

事务具有四个特性:原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)和持续性(Durability)。这四个特性也简称ACID性

  • 原子性:事务是应用中最小的执行单位,就如原子是自然界最小颗粒,具有不可再分的特征一样。事务是应用中不可再分的最小逻辑执行体

  • 一致性:事务执行的结果,必须使数据库从一个一致性状态,变到另一个一致性状态。当数据库中只包含事务成功提交的结果时,数据库处于一致性状态。一致性是通过原子性来保证的

  • 隔离性:各个事务的执行互不干扰,任意一个事务的内部操作对其他并发的事务,都是隔离的。也就是说:并发执行的事务之间不能看到对方的中间状态,并发执行的事务之间不能相互影响

  • 持续性:持续性也称为持久性,指事务一旦提交,对数据所做的任何改变,都要记录到永久存储器中,通常是保存进物理数据库

数据库的事务有下列语句组成:

  • 一组DML(Data Manipulate Language,即数据操作语言),经过这组DML修改后数据将保持较好的一致性

  • 一个DDL(Data Definition Language,即数据定义语言)语句

  • 一个DCL(Data control Language,即数据控制语言)语句

DDL和DCL语句最多只能有一个,因为DDL和DCL语句都会导致事务立即提交

当事务所包含的全部数据库操作都成功执行后,应该提交(commit)事务,使这些修改永久生效。事务提交有两种方式:显式提交和自动提交

  • 显式提交:使用commit

  • 自动提交:执行DDL或DCL,或者程序正常退出

当事务所包含的任意一个数据库操作执行失败后,应该回滚(rollback)事务,使该事务中所做的修改全部失效。事务回滚的方式有两种:显式回滚和自动回滚

  • 显式回滚:使用rollback关键字

  • 隐式回滚:系统错误或者强行退出

MySQL默认关闭事务(即打开自动提交事务),在默认情况下,在MySQL控制台输入一条DML语句,该语句会立刻保存到数据库中。可以使用下面的语句来开启事务(即关闭自动提交事务):

// 关闭自动提交,即开启事务
set autocommit = 0; 
// 开启自动提交,即关闭事务
set autocommit = 1;

调用 set autocommit = 0; 命令后,该命令行窗口里的所有DML语句都不会立即生效,上一个事务结束后第一条DML语句将开始一个新的事务,而后续执行的所有SQL语句都处于该事务中。除非使用commit提交事务、或正常退出、或运行DDL语句或DCL语句导致事务隐式提交。也可以使用rollback回滚来结束事务,使用rollback结束事务将会使此事务中的DML语句所做的修改全部失效

一个MySQL命令行窗口代表一个Session,在该窗口里设置set autocommit = 0; 相当于关闭了该连接Session的自动提交,对其他连接不会有任何影响

如果不想使得整个Session都打开事务,可以使用start transaction或begin这两个命令,它们都表示临时性地开始一次事务。处于start transaction或begin后的DML语句不会立即生效,除非使用commit显式提交事务,或者使用DDL语句或DCL语句隐式提交事务

如下SQL将不会对数据库有任何影响

# 临时开始事务
begin;
# 向player_table表插入3条数据
insert into player_table
values(null, 'Westbrook', 1);
insert into player_table
values(null, 'Harden', 2); 
insert into player_table
values(null, 'Durant', 3); 
# 查询player_table表的记录
select * from player_table;    # ①
# 回滚事务
rollback;
# 再次查询
select * from player_table;    # ②   

通过使用savepoint设置事务的中间点可以让事务回滚到指定中间点,而不是回滚全部事务。普通的提交、回滚都会结束当前事务,但回滚到指定中间点因为依然处于事务之中,所以不会结束当前事务

savepoint a;
# 回滚到指定中间点
rollback to a;

JDBC的事务支持

JDBC连接的事务支持由Connection提供,Connection默认打开自动提交,即关闭事务,在这种情况下,每条SQL语句一旦执行,便会立即提交到数据库,永久生效,无法对其进行回滚操作

可以调用Connection的setAutoCommit()方法来关闭自动提交,开启事务

//关闭自动提交,开启事务
conn.setAutoCommit(false);

一旦事务开始之后,程序可以像平常一样创建Statement对象,创建了Statement对象之后,可以执行任意多条DML语句,这些SQL语句虽然被执行了,但这些SQL语句所作的修改不会生效,因为事务还没有结束。如果所有SQL语句执行成功,程序可以调用Connection的commit方法来提交事务

//提交事务
conn.commit();

如果任意一条SQL语句执行失败,应该用Connection的rollback来回滚事务

//回滚事务
conn.rollback();

当Connection遇到一个未处理的SQLException异常时,系统将会非正常退出,事务也会自动回滚。但如果程序捕获了该异常,则需要在异常处理块中显式地回滚事务

Connection设置中间点的方法:

  • Savepoint setSavepoint():在当前事务中创建一个未命名的中间点,并返回代表该中间点的Savepoint对象

  • Savepoint setSavepoint(String name):在当前事务中创建一个具有指定名称的中间点,并返回代表该中间点的Savepoint对象

通常来说,设置中间点时没有太大的必要指定名称,因为Connection回滚到指定中间点时,并不是根据名字回滚的,而是根据中间点对象回滚的。Connection提供了rollback(Savepoint savepoint)方法来回滚到指定中间点

Java8增强的批量更新

批量更新必须得到底层数据库的支持,可通过调用DatabaseMetaData的supportsBatchUpdates()方法来查看底层数据库是否支持批量更新

批量更新需要先创建一个Statement对象,然后利用该对象的addBatch()方法将多条SQL语句同时收集起来,最后调用Statement对象的executeBatch()(或executeLargeBatch())方法同时执行这些SQL语句

批量更新代码:

Statement stmt = conn.createStatement();  
//使用Statement同时收集多个SQL语句  
stmt.addBatch(sql1);  
stmt.addBatch(sql2);  
stmt.addBatch(sql3);  
...  
//同时执行所有的SQL语句  
stmt.executeBatch();  

为了让批量操作可以正确地处理错误,必须把批量执行的操作视为单个事务,如果批量更新在执行过程中失败,则让事务回滚到批量操作开始之前的状态。程序应该在开始批量操作之前先关闭自动提交,然后开始收集更新语句,当批量操作结束之后,提交事务,并恢复之前的自动提交模式

//保存当前的自动的提交模式  
Boolean autoCommit = conn.getAutoCommit();  
//关闭自动提交  
conn.setAutoCommit(false);  
Statement stmt = conn.createStatement();  
//使用Statement同时收集多条SQL语句  
stmt.addBatch(sql1);  
stmt.addBatch(sql2);  
stmt.addBatch(sql3);  
...  
//同时提交所有的SQL语句  
stmt.executeBatch();  
//提交修改  
conn.commit();  
//恢复原有的自动提交模式  
conn.setAutoCommit(autoCommit);  

分析数据库信息

使用DatabaseMetaData分析数据库信息

JDBC提供了DatabaseMetaData来封装数据库连接对应数据库的信息,通过Connection提供的getMetaData()方法就可以获取数据库对应的DatabaseMetaData对象

DatabaseMetaData接口通常由驱动程序供应商提供实现,其目的是让用户了解底层数据库的相关信息。使用该接口的目的是发现如何处理底层数据库,尤其是对于试图与多个数据库一起使用的应用程序

许多DatabaseMetaData方法以ResultSet对象的形式返回查询信息,然后使用ResultSet的常规方法(如getString()和getInt())即可从这些ResultSet对象中获取数据。如果查询的信息不可用,则将返回一个空ResultSet对象

DatabaseMetaData的很多方法都需要传入一个xxxPattern模式字符串,这里的xxxPattern不是正则表达式,而是SQL里的模式字符串,即用百分号(%)代表任意多个字符,使用下划线(_)代表一个字符。在通常情况下,如果把该模式字符串的参数值设置为null,即表明该参数不作为过滤条件

import java.sql.*;  
import java.io.*;  
import java.util.*;  
public class DatabaseMetaDataTest{  
    private String driver;  
    private String url;  
    private String user;  
    private String pass;  
    public void initParam(String paramFile) throws Exception{  
        //使用Properties类来加载属性文件  
        Properties props = new Properties();  
        props.load(new FileInputStream(paramFile));  
        driver = props.getProperty("driver");  
        url = props.getProperty("url");  
        user = props.getProperty("user");  
        pass = props.getProperty("pass");  
    }  
    public void info() throws Exception{  
        //加载驱动  
        Class.forName(driver);  
        try(  
            //获取数据库连接  
            Connection conn = DriverManager.getConnection(url, user, pass);  
        ){  
            //获取DatabaseMetaData对象  
            DatabaseMetaData dbmd = conn.getMetaData();  
            //获取MySQL支持的所有表类型  
            ResultSet rs = dbmd.getTableTypes();  
            System.out.println("---MySQL支持的表类型信息---");  
            printResultSet(rs);  
            //获取当前数据库的全部数据表  
            rs = dbmd.getTables(null, null, "%", new String[]{"TABLE"});  
            System.out.println("---当前数据库里的数据表信息---");  
            printResultSet(rs);  
            //获取student_table表的主键  
            rs = dbmd.getPrimaryKeys(null, null, "student_table");  
            System.out.println("---student_table表的主键信息---");  
            printResultSet(rs);  
            //获取当前数据库的全部存储过程  
            rs = dbmd.getProcedures(null, null, "%");  
            System.out.println("---当前数据库里的存储过程信息---");  
            printResultSet(rs);  
            //获取teacher_table表和student_table表之间的外键约束  
            rs = dbmd.getCrossReference(null, null, "teacher_table", null, null, "student_table");  
            System.out.println("---teacher_table表和student_table表之间的外键约束---");  
            printResultSet(rs);  
            //获取student_table表的全部数据列  
            rs = dbmd.getColumns(null, null, "student_table", "%");  
            System.out.println("---student_table表的全部数据列---");  
            printResultSet(rs);  
        }  
    }  
    public void printResultSet(ResultSet rs) throws SQLException{  
        ResultSetMetaData rsmd = rs.getMetaData();  
        //打印ResultSet的所有列标题  
        for(int i = 0; i < rsmd.getColumnCount(); i++){  
            System.out.print(rsmd.getColumnName(i + 1) + "\t");  
        }  
        System.out.print("\n");  
        //打印ResultSet的全部数据  
        while(rs.next()){  
            for(int i = 0; i < rsmd.getColumnCount(); i ++){  
                System.out.print(rs.getString(i + 1) + "\t");  
            }  
            System.out.print("\n");  
        }  
        rs.close();  
    }  
    public static void main(String args[]) throws Exception{  
        DatabaseMetaDataTest dmdt = new DatabaseMetaDataTest();  
        dmdt.initParam("sql.ini");  
        dmdt.info();  
    }  
}  

使用系统表分析数据库信息

如已确定应用程序所使用的数据库系统,则可以通过数据库的系统表来分析数据库信息。系统表又称为数据字典,数据字典的数据通常由数据库系统负责维护,用户通常只能查询数据字典,而不能修改数据字典的内容

MySQL数据库使用information_schema数据库来保存系统表,在该数据库里包含了大量系统表,常用系统表的简单介绍如下:

  • tables:存放数据库里所有数据表的信息

  • schemata:存放数据库里所有数据库(与MySQL的Schema对应)的信息

  • views:存放数据库里所有视图的信息

  • columns:存放数据库里所有列的信息

  • triggers:存放数据库里所有触发器的信息

  • routines:存放数据库里所有存储过程和函数的信息

  • key_column_usage:存放数据库里所有具有约束的键信息

  • table_constraints:存放数据库里全部约束的表信息

  • statistics:存放数据库里全部索引的信息

select * from schemata;  
select * from tables where table_schema = 'select_test';  
select * from columns where table_name = 'student_table';  

选择合适的分析方式

通常而言,如果使用DatabaseMetaData来分析数据库信息,则具有更好的跨数据库特性,应用程序可以做到数据库无关;但可能无法准确获取数据库的更多细节

使用数据库系统表来分析数据库系统信息会更加准确,但使用系统表也有坏处——这种方式与底层数据库耦合严重,采用这种方式将会导致程序只能运行在特定的数据库之上

通常来说,如果需要获得数据库信息,包括该数据库驱动提供了哪些功能,则应该利用DatabaseMetaData来了解该数据库支持哪些功能。完全可能出现这样一种情况:对于底层数据库支持的功能,但数据库驱动没有提供该功能,程序还是不能使用该功能。使用DatabaseMetaData则不会出现这种问题

如果需要纯粹地分析数据库的静态对象,例如分析数据库系统里包含多少数据库、数据表、视图、索引等信息,则利用系统会更加合适

使用连接池管理连接

数据库连接的建立以及关闭是极耗费系统资源的操作,在多层结构的应用环境中,这种资源的耗费对系统性能影响尤为明显。通过前面介绍的方式(通过DriverManager获取连接)获取的数据库连接,一个数据库连接对象均对应一个物理数据库连接,每次操作都打开一个物理连接,使用完后立即关闭连接。频繁地打开、关闭连接将造成系统性能低下

数据库连接池的解决方案是: 当应用程序启动时,系统主动建立足够的数据库连接,并将这些连接组成一个连接池。每次应用程序请求数据库连接时,无须重新打开连接,而是从连接池中取出已有的连接使用,使用完后不再关闭数据库连接,而是直接将连接归还给连接池。通过使用连接池,将大大提供程序的运行效率

对于共享资源的恶情况,有一个通用的设计模式:资源池(Resource Pool),用于解决资源的频繁请求、释放所造成的性能下降。为了解决数据库连接的频繁请求,JDBC2.0规范引入了数据库连接池技术

数据库连接池是Connection对象的工厂。数据库连接池的常用参数如下:

  • 数据库的初始连接数

  • 连接池的最大连接数

  • 连接池的最小连接数

  • 连接池每次增加的容量

JDBC的数据库连接池使用javax.sql.DataSource来表示,DataSource只是一个接口,该接口通常由商用服务器(如WebLogic、WeSphere)等提供实现,也有一些开源组织提供实现(如DBCP和C3P0)

DataSource通常被称为数据源,它包含连接池和连接池管理两个部分,但习惯上我们也经常把DataSource称为连接池

DBCP数据源

DBCP是Apache软件基金组织下的开源连接池实现,该连接池依赖该组织下的另一个开源系统:common-pool。如果需要使用该连接池实现,则应在系统中增加如下两个jar文件:

  • commons-dbcp.jar:连接池的实现

  • commons-pool.jar:连接池实现的依赖库

Tomcat的连接池正是采用该连接池实现的。数据库连接池既可以与应用服务器整合使用,也可以由应用程序独立使用。下面的代码片段示范了使用DBCP来获得数据库连接的方式:

// 创建数据源对象  
BasicDataSource ds = new BasicDataSource();  
// 设置连接池所需的驱动  
ds.setDriverClassName("com.mysql.jdbc.Driver");  
// 设置连接数据库的URL  
ds.setUrl("jdbc:mysql://localhost:3306/javaee");  
// 设置连接数据库的用户名  
ds.setUsername("root");  
/ /设置连接数据库的密码  
ds.setPassword("pass");  
// 设置连接池的初始连接数  
ds.setInitialSize(5);  
// 设置连接池最多可有多少个活动连接数  
ds.setMaxActive(20);  
// 设置连接池中最少有2个空闲的连接  
ds.setMinIdle(2);  

数据源和数据库连接不同,数据源无须创建多个,它是产生数据库连接的工厂,因此整个应用只需要一个数据源即可。也就是说,对于一个应用,上面代码只要执行一次即可。建议把上面程序中的ds设置成static成员变量,并且在应用开始时立即初始化数据源对象,程序中所有需要获取数据库连接的地方直接访问该ds对象,并获取数据库连接即可

// 通过数据源获取数据库连接  
Connection conn = ds.getConnection();  
// 当数据库访问结束后,释放数据库连接  
conn.close();    // 上面代码并没有关闭据库的物理连接
                 // 仅仅把数据库连接释放,归还给连接池
                 // 让其他客户端可以使用该连接
              

C3P0数据源

C3P0数据源性能更胜一筹,Hibernate推荐使用该连接池。C3P0连接池不仅可以自动清理不再使用的Connection,还可以自动清理Statement和ResultSet

如果需要使用C3P0连接池,则应在系统中增加如下JAR文件

  • c3p0-0.9.1.2.jar: C3P0连接池的实现

    // 创建连接池实例  
    ComboPooledDataSource ds = new ComboPooledDataSource();  
    // 设置连接池连接数据库所需的驱动  
    ds.setDriverClass("com.mysql.jdbc.Driver");  
    // 设置连接数据库的url  
    ds.setJdbcUrl("jdbc:mysql://localhost:3306/javaee");  
    // 设置连接数据库的用户名、密码  
    ds.setUser("root");  
    ds.setPassword("root");  
    // 设置连接池的最大连接数  
    ds.setMaxPoolSize(40);  
    // 设置连接池的最小连接数  
    ds.setMinPoolSize(2);  
    // 设置连接池的初始连接数  
    ds.setInitialPoolSize(10);  
    // 设置连接池的缓存Statment的最大数  
    ds.setMaxStatements(180);  
    //获得数据库连接  
    Connection conn = ds.getConnection(); 
    

布still
461 声望32 粉丝

数据挖掘、用户行为研究、用户画像