JavaSE学习笔记(22.Java之JDBC的使用)

1. JDBC概念说明

JDBC是Java Database Connectivity的简称，是Java语言访问数据库的接口规范，具体详细规范内容可以参考JSR221

 

2. SQL语句分类

2.1 DML语句：

Data Manipulation Language的简称，数据操作语言(对数据表中的数据进行操作的行为)；主要是由insert、update和delete三个关键词完成！

2.2 DCL语句：

Data Control language的简称，数据控制语言(数据库及数据表权限相关的行为)；主要是由grant和revoke两个关键词完成！

2.3 DDL语句：

Data Definition Language的简称，数据定义语言(对数据库中数据表进行操作的行为)；主要是由create、alter、drop和truncate四个关键词完成！

2.4 DQL语句：

Data Query Language的简称，数据库查询语言(对数据表中的数据进行查询的行为)；主要是由select关键词完成！

2.5 TCL语句：

Transaction Control Language的简称，事物控制语言(数据库中事物相关操作的行为)；主要是由commit、rollback和savepoint三个关键词完成！

 

2. JDBC使用说明

2.1 JDBC与数据库的链接：

JDBC链接数据库主要是通过DriverManager类和Connection类实现：

public class JDBCTest {
    public static void main(String[] args) throws Exception  {
        /*通过DriverManager类的getConnection()静态方法获取数据库链接，再通过数据库链接访问数据库*/
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql_test?serverTimezone=UTC", "root", "root");
        }

        return;
    }
}

Ps：

• 示例环境使用的是mysql数据库，用户名为root，密码为root，端口号为3306
• 5.1.6及以上版本的mysql，已经不需要手动通过调用Class.forName("com.mysql.jdbc.Driver")方法进行mysql数据库驱动的注册了，已经可以通过JDK的SPI功能自动完成加载注册驱动类！
• 不同类型的数据库都会实现自己的Driver驱动类；在类加载的时候，会将Driver驱动类注册到DriverManager类中，当使用DriverManager.getConnection()方法获取对应数据库的Connection连接类的时候，需要通过不同的URL进行区分！
• 不同数据库的URL存在不同的格式：

RDBMS	JDBC驱动程序名称	URL格式	默认端口	举例
oracle	oracle.jdbc.driver.OracleDriver	jdbc:oracle:thin:@dbip:port:databasename	1521	jdbc:oracle:thin:@10.10.10.10:1521:dataBase
mysql	com.mysql.jdbc.Driver	jdbc:mysql://dbip:port/databasename	3306	jdbc:mysql://10.10.10.10:3306/dataBase
SQLServer	com.microsoft.jdbc.sqlserver.SQLServerDriver	jdbc:microsoft:sqlserver://dbip:port; DatabaseName=databasename	1433	jdbc:sqlserver://10.10.10.10:1433;DatabaseName=dataBase
DB2	com.ibm.db2.jdbc.app.DB2Driver	jdbc:db2://dbip:port/databasename	5000	jdbc:db2://10.10.10.10:5000/dataBase
PostgreSQL	org.postgresql.Driver	jdbc:postgresql://dbip:port/databasename	5432	jdbc:postgresql://10.10.10.10:5432/dataBase

• DriverManager.getConnection()方法主要是通过URL及数据库连接参数对数据库进行连接，参数可以拼接到URL上(URL与参数直接通过?连接，key-value之间通过=连接，参数之间通过&连接)，也可以作为配置项传入方法中；<mysql连接参数说明>
• 切记每个Connection连接类都是连接到数据库中的一个具体的数据库(database)，但并不是当前Connection连接类只能操作这个database；只要用户权限满足依然可以切换到其他database上面进行操作！

2.2 JDBC操作数据库：

JDBC操作数据库主要是通过Statement、PreparedStatement、CallableStatement这三个类实现，这三个类分别是由Connection类的createStatement()方法、prepareStatement()方法、prepareCall()方法获取！

示例数据表格式：

建表语句：

CREATE TABLE `person`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `name` VARCHAR(100) NOT NULL,
   `info` VARCHAR(40) NOT NULL,
   `age` INT,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.2.1 Statement类：

Statement类提供了如下四个主要的方法：

• execute()方法：可以执行所有类型的语句，如果语句类型为DQL语句的时候，返回值为true，并可以通过Statement类的getResultSet()方法获取到结果集；如果语句类型为DML语句的时候，返回值为false，并可以通过Statement类的getUpdateCount()方法获取Sql指令影响的行数；如果语句类型为其他类型，返回值为false，并不任何结果可获取！
• executeQuery()方法：只能执行DQL语句，并返回结果集！
• executeUpdate()方法：可以执行DML语句和DDL语句，当执行DML语句的时候，返回Sql指令影响函数；当执行DDL语句的时候，返回0！
• executeLargeUpdate()方法：与executeUpdate()方法类似，只是返回值从int改为了long，当Sql指令影响行数大于Interge.MAX_VALUE的时候，可以考虑使用executeLargeUpdate()方法，但是mysql不支持！

代码示例：

public class JDBCTest {
    public static void main(String[] args) throws Exception  {
        /*获取数据库连接*/
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql_test?serverTimezone=UTC&user=root&password=root");
        Statement stmt = conn.createStatement();
        /*executeQuery方法的使用*/
        /*使用executeQuery方法进行数据库查询，并返回结果集*/
        ResultSet rs = stmt.executeQuery("SELECT * FROM person");
        /*遍历结果集展示查询结果数据*/
        while (rs.next()) {
            System.out.println(rs.getString("id"));
            System.out.println(rs.getString("name"));
            System.out.println(rs.getString("info"));
            System.out.println(rs.getString("age"));
        }

        /*execute方法的使用*/
        boolean b = stmt.execute("SELECT * FROM person");
        ResultSet rs1 = null;
        if (b) {
            rs1 = stmt.getResultSet();
        }

        while ((null != rs1) && (rs1.next())) {
            System.out.println(rs1.getString("id"));
            System.out.println(rs1.getString("name"));
            System.out.println(rs1.getString("info"));
            System.out.println(rs1.getString("age"));
        }

        /*executeUpdate方法的使用*/
        int n = stmt.executeUpdate("INSERT INTO person (name,info,age) VALUES ('qq','qq_info',20)");
        System.out.println(n);

        /*关闭所有资源*/
        rs.close();
        if (null != rs1) {
            rs1.close();
        }
        stmt.close();
        conn.close();
        return;
    }
}

Ps：ResultSet结果集、Connection连接类、Statement类用完后，都需要手动关闭；Statement类还提供了两个方法，closeOnCompletion()方法和isCloseOnCompletion()方法；Statement类调用closeOnCompletion()方法后，当所有依赖Statement类的ResultSet结果集都关闭后，Statement类会自动关闭！同时可以使用isCloseOnCompletion()方法来判断Statement类是否打开了这个功能！

2.2.2 PreparedStatement类：

PreparedStatement类与Statement类相似，也提供上述四个操作方法(只是没有参数)，使用原则也一致；只是在通过Connection类获取PreparedStatement类的时候，使用带占位符(?符号)的Sql和设置预编译参数，多进行一步预编译过程！

代码示例：

public class JDBCTest {
    public static void main(String[] args) throws Exception  {
        /*获取数据库连接*/
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql_test?serverTimezone=UTC&user=root&password=root");
        /*DML示例*/
        /*通过Sql指令+占位符获取PreparedStatement对象*/
        PreparedStatement preparedStatement = conn.prepareStatement("INSERT INTO person (name,info,age) VALUES (?,?,?)");
        /*给预编译设置参数*/
        preparedStatement.setString(1,"n2");
        preparedStatement.setString(2,"info2");
        preparedStatement.setInt(3,100);
        /*执行executeUpdate方法*/
        preparedStatement.executeUpdate();
        /*关闭资源*/
        preparedStatement.close();

        /*DQL示例*/
        preparedStatement = conn.prepareStatement("SELECT * FROM person WHERE name = ? ");
        /*给预编译设置参数*/
        preparedStatement.setString(1,"n2");
        /*执行executeQuery方法*/
        ResultSet rs = preparedStatement.executeQuery();
        while (rs.next()) {
            System.out.println(rs.getString("id"));
            System.out.println(rs.getString("name"));
            System.out.println(rs.getString("info"));
            System.out.println(rs.getString("age"));
        }
        /*关闭所有资源*/
        rs.close();
        preparedStatement.close();
        conn.close();
        return;
    }
}

Ps：

• PreparedStatement类在设置预编译参数的时候，下标序号是从1开始，不是从0开始
• 更加推荐使用PreparedStatement方式，性能更好、无需拼接Sql、而且通过占位符预编译可以有效的防止Sql注入，更加安全！
• 占位符只能替换Sql语句的普通值，不能替换库名、表名、字段名和关键词！！！

2.2.3 CallableStatement类：

CallableStatement类是JDBC提供用来调用数据库存储过程的一个操作类，使用说明如下：

存储过程示例：

//存储过程示例
delimiter $$
create procedure sum(a int, b int, out sum)
begin
set sum = a + b;
end$$

代码示例：

public class JDBCTest {
    public static void main(String[] args) throws Exception  {
        /*获取数据库连接*/
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql_test?serverTimezone=UTC&user=root&password=root");
        /*生成CallableStatement实例*/
        CallableStatement call = conn.prepareCall("call fun(?,?,?)");
        /*设置存储过程输入参数*/
        call.setInt(1, 100);
        call.setInt(2, 100);
        /*设置存储过程输出类型*/
        call.registerOutParameter(3,Types.INTEGER);
        /*执行存储过程*/
        call.execute();
        /*获取存储过程输出值*/
        System.out.println(call.getInt(3));
        
        call.close();
        conn.close();
        return;
    }
}

 

2.3 JDBC获取结果集

2.3.1 ResultSet结果集：

在实例化PreparedStatement类和Statement类的时候，可以设置返回结果集的类型，是否可以更新、是否可以自由移动指针等等！

• resultSetType参数：

1. ResultSet.TYPE_FORWARD_ONLY：设置指针只能向前移动
2. ResultSet.TYPE_SCROLL_INSENSITIVE：设置指针可以自由移动，但是底层数据的改变不会影响ResultSet结果集的内容
3. ResultSet.TYPE_SCROLL_SENSITIVE：设置指针可以自由移动，但是底层数据的改变会影响ResultSet结果集的内容

• resultSetConcurrency参数：

1. ResultSet.CONCUR_READ_ONLY：设置ResultSet结果集为只读模式
2. ResultSet.CONCUR_UPDATABLE：设置ResultSet结果集为可更新模式

Statement createStatement(int resultSetType, int resultSetConcurrency,
                              int resultSetHoldability)
PreparedStatement prepareStatement(String sql, int resultSetType,
                                       int resultSetConcurrency)

ResultSet结果集常用方法：

        /*光标移动相关方法*/
        //向前滚动
        rs.previous();
        //向后滚动
        rs.next();
        //得到当前行号
        rs.getRow();
        //光标定位到n行
        rs.absolute(n);
        //相对移动n行
        rs.relative(int n);
        //将光标定位到结果集中首行
        rs.first();
        //将光标定位到结果集中尾行
        rs.last();
        //将光标定位到结果集中首行之前
        rs.beforeFirst();
        //将光标定位到结果集中尾行之后
        rs.afterLast();

        /*确认光标位置相关方法*/
        //光标是否在首行
        rs.isFirst();
        //光标是否在尾行
        rs.isLast();
        //光标是否在首行之前
        rs.isBeforeFirst();
        //光标是否在尾行之后
        rs.isAfterLast();

        /*更新结果集相关方法*/
        //将数据插入结果集和数据表中
        rs.insertRow();
        //将数据从结果集和数据表中删除
        rs.deleteRow();
        //更新当前行某一列的数据
        rs.updateXXX(int column, XXX data);
        //更新当前行某一列的数据
        rs.updateXXX(String columnName, XXX Data);
        //将结果集中的更新的数据推送到数据表中
        rs.updateRow();

当指针移动到指定行后，可以通过getXXX(int column)方法或者getXXX(String columnName)方法，获取当前行，特定列的数据！也可以使用<T> T getObject(int colum, Class<T> type)方法或者<T> T getObject(String columnName, Class<T> type)获取任意类型的值！

Ps：

• 使用可更新的结果集，要保证结果集中的数据全部来自一张数据表，并且结果集中有包含主键列，否则会更新失败！
• ResultSet结果集是一个与数据库长连接的结果集，使用过程中要保证Connection对象处于连接状态，否则访问ResultSet结果集的时候会报错！
• 结果集的列数据获取，下标都是从1开始的！

2.3.1 RowSet结果集：

ResultSet结果集是一个与数据库长连接的结果集，RowSet是ResultSet的子接口，其实现类有JdbcRowSet、CachedRowSet、FilteredRowSet、JoinRowSet、WebRowSet！其中只有JdbcRowSet是长连接结果集，其余的都是离线结果集！离线结果集便于存储、传输、序列化，而且也提高了处理性能！

2.3.2 CachedRowSet离线结果集：

ResultSet结果集属于数据库长连接结果集，如果断开与数据库的连接，在访问Result结果集，程序会报错！所有更多的情况，是将ResultSet结果集在内存合理的范围内转换为离线结果集，再提供给外部使用！

代码示例：

public class JDBCTest {
    public static void main(String[] args) throws Exception  {
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql_test?serverTimezone=UTC&user=root&password=root");
        preparedStatement = conn.prepareStatement("SELECT * FROM person WHERE name = ? ");
        preparedStatement.setString(1,"n1");
        ResultSet rs = preparedStatement.executeQuery();

        /*通过ResultSet结果集生成离线CachedRowSet结果集*/
        RowSetFactory factory = RowSetProvider.newFactory();
        CachedRowSet cachedRowSet = factory.createCachedRowSet();
        cachedRowSet.populate(rs);

        /*关闭所有资源*/
        rs.close();
        preparedStatement.close();
        conn.close();

        /*通过CachedRowSet结果集获取数据*/
        while (cachedRowSet.next()) {
            System.out.println(cachedRowSet.getString("id"));
            System.out.println(cachedRowSet.getString("name"));
            System.out.println(cachedRowSet.getString("info"));
            System.out.println(cachedRowSet.getString("age"));
        }

        return;
    }
}

CachedRowSet结果集的分页：

如果ResultSet结果集比较大的时候，就不能全量加载到CachedRowSet结果集，否则可能会造成内存不足的情况；所有需要对CachedRowSet结果集进行分页操作；每次只从ResultSet结果集中获取一部分数据！当然这样的操作依然需要保证底层与数据库处于连接状态！

分页相关方法：

 

2.4 JDBC操作事物：

JDBC对于数据库事物相关操作都是基于Connection连接类的如下方法实现的

Ps：

• 通过setAutoCommit()方法关闭自动提交后，即开启了事务；当前连接中的所有DML语句都不会立即生效，除非执行commit()方法显示提交事务，或者通过当执行DDL语句、DCL语句、session退出的方式隐形提交事务；提交事务后，之前的DML语句才会生效！
• 除了通过关闭自动提交开启事务，也可以通过begin命令或者start transaction命令，开启事务！
• 不管是隐性事务提交、显性事务提交或者回滚操作，都会导致事务终止，不再继续执行！
• 还可以根据保存点进行回滚操作，这种回滚操作，会保留保存点之前的事务行为；由于这种回滚行为保留了保存点之前的事务，所以已经记得及时commit事物，保证数据更新！
• 使用try-with-resource打开Connection连接类的时候，如果抛出一个未处理的SqlException异常，Connection类会自动回滚事务，并关闭事务！否则就需要人工去手动捕获SqlException异常，在catch代码块中进行回滚操作！

 

2.5 Java8批量更新数据：

Java8后的版本新增了批量更新数据库操作，可以执行DDL语句和DML语句；以前版本只能通过execute()方法一条条的去执行Sql，并发送给数据库；现在可以使用addBatch()方法和executeBatch()方法批量执行Sql，再批量发送给数据库！

public class JDBCTest {
    public static void main(String[] args) throws Exception  {
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql_test?serverTimezone=UTC&user=root&password=root");
        /*获取当前自动提交状态*/
        boolean autoCommitFlag = conn.getAutoCommit();
        Statement statement = conn.createStatement();
        /*将自动提交状态，设置为flase，开启事务*/
        conn.setAutoCommit(false);
        /*添加批量更新sql*/
        statement.addBatch(sql1);
        statement.addBatch(sql2);
        statement.addBatch(sql3);
        /*同时执行所有批量更新sql*/
        statement.executeBatch();
        /*提交事务*/
        conn.commit();
        /*还原自动提交状态*/
        conn.setAutoCommit(autoCommitFlag);
        statement.close();
        conn.close();
        return;
    }
}

Ps：

• 批量更新数据可以执行DDL语句和DML语句，针对DML语句建议在事务中处理，这样可以保证批量处理数据的一致性！
• 在使用批量更新数据的时候，需要先确认当前数据库是否支持！可以通过DatabaseMetaData的supportsBatchUpdates()方法来查看！
• 当批量更新数据量超过Interge.MAX_VALUE的时候，需要使用executeLargeUpdate()方法代替executeBatch()方法

 

2.6 JDBC获取数据库元数据

2.6.1 结果集的元数据：

通过ResultSet结果集的getMetaData()方法可以获取对应结果集的元数据，可以通过ResultSetMetaData对象获取到结果集的列相关信息！

public class JDBCTest {
    public static void main(String[] args) throws Exception  {
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql_test?serverTimezone=UTC&user=root&password=root");
        Statement statement = conn.createStatement();
        ResultSet resultSet = statement.executeQuery("select * from person");
        ResultSetMetaData data = resultSet.getMetaData();
        /*获取列个数*/
        System.out.println(data.getColumnCount());
        /*获取列名*/
        System.out.println(data.getColumnName(1));
        /*获取列类型*/
        System.out.println(data.getColumnType(1));

        resultSet.close();
        statement.close();
        conn.close(); 

        return;
    }
}

2.6.2 数据库的元数据：

通过Connection连接类的getMetaData()方法，可以获取到数据库的元数据DatabaseMetaData对象，通过DatabaseMetaData对象可以获取到数据库的详细信息！

public class JDBCTest {
    public static void main(String[] args) throws Exception  {
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql_test?serverTimezone=UTC&user=root&password=root");
        DatabaseMetaData data = conn.getMetaData();
        /*获取所有符合规则的数据表信息*/
        ResultSet rs = data.getTables(null,null,"%",new String[]{"TABLE"});
        while (rs.next()) {
            for (int i = 1; i < rs.getMetaData().getColumnCount(); i++) {
                System.out.println(rs.getMetaData().getColumnName(i) + " : " + rs.getString(i));
            }
        }
        rs.close();
        conn.close();
        return;
    }
}

Ps：DatabaseMetaData类其中的方法，基本上都是存在过滤条件的；可以使用null对象来忽略过滤条件，也可以使用"%"代替任意字符串、使用"_"代替任意一个字符！

2.6.3 使用系统表获取数据库信息：

常规数据都会有一个系统数据表，用来存放数据库信息，可以通过系统数据表获取对应的系统信息！

例如Mysql系统表如下：

 

2.7 数据库连接池

频繁的与数据库的进行连接、关闭操作，会造成IO和性能的浪费；所以引入数据库连接池的概念；常用的数据库连接池有DBCP和C3P0！

2.7.1 DBCP连接池(说明文档)：

pom配置：

<dependency>
	<groupId>org.apache.commons</groupId>
	<artifactId>commons-dbcp2</artifactId>
	<version>2.7.0</version>
</dependency>
<dependency>
	<groupId>org.apache.commons</groupId>
	<artifactId>commons-pool2</artifactId>
	<version>2.8.0</version>
</dependency>

代码示例： 

public class JDBCTest {
    public static void main(String[] args) throws Exception  {
        /*创建数据源*/
        BasicDataSource ds = new BasicDataSource();
        /*设置驱动类*/
        ds.setDriverClassName("com.mysql.jdbc.Driver");
        /*设置URL*/
        ds.setUrl("jdbc:mysql://localhost:3306/mysql_test?serverTimezone=UTC&user=root&password=root");
        /*设置数据库连接池的初始池子大小*/
        ds.setInitialSize(5);
        /*设置连接池最大连接个数*/
        ds.setMaxTotal(20);
        /*设置连接池最小空闲个数*/
        ds.setMinIdle(5);
        /*通过连接池获取数据库连接*/
        Connection conn = ds.getConnection();

        Statement stmt = conn.createStatement();
        boolean b = stmt.execute("SELECT * FROM person");
        ResultSet rs = null;
        if (b) {
            rs = stmt.getResultSet();
        }
        while ((null != rs) && (rs.next())) {
            System.out.println(rs.getString("id"));
            System.out.println(rs.getString("name"));
            System.out.println(rs.getString("info"));
            System.out.println(rs.getString("age"));
        }
        rs.close();
        stmt.close();
        conn.close();
        return;
    }
}

2.7.2 C3P0连接池(说明文档)：

pom配置：

<dependency>
    <groupId>com.mchange</groupId>
    <artifactId>c3p0</artifactId>
    <version>0.9.5.2</version>
</dependency>

代码示例：

public class JDBCTest {
    public static void main(String[] args) throws Exception  {
        /*创建数据源*/
        ComboPooledDataSource ds = new ComboPooledDataSource();
        /*设置驱动类*/
        ds.setDriverClass("com.mysql.jdbc.Driver");
        /*设置URL*/
        ds.setJdbcUrl("jdbc:mysql://localhost:3306/mysql_test?serverTimezone=UTC&user=root&password=root");
        /*设置数据库连接池的初始池子大小*/
        ds.setInitialPoolSize(5);
        /*设置连接池最大连接个数*/
        ds.setMaxPoolSize(20);
        /*设置连接池最小空闲个数*/
        ds.setMinPoolSize(5);
        /*通过连接池获取数据库连接*/
        Connection conn = ds.getConnection();

        Statement stmt = conn.createStatement();
        boolean b = stmt.execute("SELECT * FROM person");
        ResultSet rs = null;
        if (b) {
            rs = stmt.getResultSet();
        }
        while ((null != rs) && (rs.next())) {
            System.out.println(rs.getString("id"));
            System.out.println(rs.getString("name"));
            System.out.println(rs.getString("info"));
            System.out.println(rs.getString("age"));
        }
        rs.close();
        stmt.close();
        conn.close();
        return;
    }
}