JAVA进阶 JDBC技术

getConnection(String jdbcUrl, String user, String password)

该方法通过访问数据库的 url、用户以及密码，返回对应的数据库的 Connection 对象。
JDBC URL

与数据库连接时，用来连接到指定数据库标识符。在 URL 中包括了该数据库的类型、地址、端口、库名称等信息。不同品牌数据库的连接 URL 不同。
连接 MySql 数据库：Connection conn = DriverManager.getConnection("jdbc:mysql://host:port/database", "user", "password");
连接 Oracle 数据库：Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@host:port:database", "user", "password");

2.3Connection 接口

Connection 是数据库的连接（会话）对象。对数据库的一切操作都是在这个连接基础之上进行的，我们可以通过该对象执行 sql 语句并返回结果。

常用方法

createStatement()

创建向数据库发送 sql 的 Statement 接口类型的对象。
preparedStatement(sql)

创建向数据库发送预编译 sql 的 PrepareSatement 接口类型的对象。
setAutoCommit(boolean autoCommit)

设置事务是否自动提交。
commit()

在链接上提交事务。
rollback()

在此链接上回滚事务。

2.4Statement 接口

用于执行静态 SQL 语句并返回它所生成结果的对象。由 createStatement 创建，用于发送简单的 SQL 语句（不支持动态绑定）。

2.4.1常用方法

execute(String sql)

执行参数中的 SQL，返回是否有结果集。
executeQuery(String sql)

运行 select 语句，返回 ResultSet 结果集。
executeUpdate(String sql)

运行 insert/update/delete 操作，返回更新的行数。
addBatch(String sql)

把多条 sql 语句放到一个批处理中。
executeBatch()

向数据库发送一批 sql 语句执行。

注意：

由于Statement对象是一个执行静态SQL语句的对象，所以该对象存在SQL注入风险。

2.4.2JDBC中三种Statement对象

Statement：用于执行静态 SQL 语句。
PreparedStatement：用于执行预编译SQL语句。
CallableStatement：用于执行数据库存储过程。

2.5PreparedStatement接口

继承自 Statement 接口，由 preparedStatement 创建，用于发送含有一个或多个参数的 SQL 语句。PreparedStatement 对象比 Statement 对象的效率更高，由于实现了动态的参数绑定，所以可以防止 SQL 注入，所以我们一般都使用 PreparedStatement。

常用方法

addBatch()

把当前 sql 语句加入到一个批处理中。
execute()

执行当前 SQL，返回个 boolean 值
executeUpdate()

运行 insert/update/delete 操作，返回更新的行数。
executeQuery()

执行当前的查询，返回一个结果集对象
setDate(int parameterIndex, Date x)

向当前SQL语句中的指定位置绑定一个java.sql.Date值
setDouble(int parameterIndex, double x)

向当前 SQL 语句中的指定位置绑定一个 double值
setFloat(int parameterIndex, float x)

向当前 SQL 语句中的指定位置绑定一个 float 值
setInt(int parameterIndex, int x)

向当前 SQL 语句中的指定位置绑定一个 int 值
setString(int parameterIndex, String x)

向当前 SQL 语句中的指定位置绑定一个 String 值

PreparedStatement对象的特点：

PreparedStatement 接口继承 Statement 接口
PreparedStatement 效率高于 Statement
PreparedStatement 支持动态绑定参数
PreparedStatement 具备 SQL 语句预编译能力
使用 PreparedStatement 可防止出现 SQL 注入问题

PreparedStatement 的预编译能力

语句的执行步骤

语法和语义解析
优化 sql 语句，制定执行计划
执行并返回结果

但是很多情况，我们的一条 sql 语句可能会反复执行，或者每次执行的时候只有个别的值不同（比如 select 的 where 子句值不同，update 的 set 子句值不同,insert 的 values 值不同）。如果每次都需要经过上面的词法语义解析、语句优化、制定执行计划等，则效率就明显不行了。所谓预编译语句就是将这类语句中的值用占位符替代，可以视为将 sql 语句模板化或者说参数化预编译语句的优势在于：一次编译、多次运行（将编译好的sql放在缓存中，但是只有在不同connection之间的缓存不会共享），省去了解析优化等过程；此外预编译语句能防止 sql 注入

2.6ResultSet 接口

ResultSet 用来暂时存放数据库查询操作获得结果集（实际上查询到的是指向查询到的结果集的指针）。

常用方法

getString(int index)、getString(String columnName)

获得在数据库里是 varchar、char 等类型的数据对象。
getFloat(int index)、getFloat(String columnName)

获得在数据库里是 Float 类型的数据对象。
getDate(int index)、getDate(String columnName)

获得在数据库里是 Date 类型的数据。
getBoolean(int index)、getBoolean(String columnName)

获得在数据库里是 Boolean 类型的数据。
getObject(int index)、getObject(String columnName)

获取在数据库里任意类型的数据。

三、JDBC编写步骤

我们可以通过DriverManager对象下的getConnection()方法创建与当前数据库的连接，得到connection对象。得到该对象表示完成和数据库的一次连接。创建好连接之后需要使用Connection对象创建statement对象或者是PreparedStatement对象发送SQL语句，如果执行的语句是查询语句的话返回一个resultSet对象。

3.1获取连接

package cn.it.bz.JDBC;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

//获取数据库连接
public class GetConnection {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //连接MySQL的url、username、password
        String url = "jdbc:mysql://localhost:3306/test?&useSSL=false&serverTimezone=UTC";
        String username = "root";
        String password = "123456";


            //通过反射实现数据库驱动对象的加载和注册
            Class.forName("com.mysql.cj.jdbc.Driver");
        /*在加载com.mysql.jdbc.Driver类信息时,会执行静态块中的代码。
        在静态块中,数据库驱动会实例化自己并通过DriverManager的registerDriver方法,
        将自己注册DriverManager驱动管理器中.*/

             // 获取数据库连接对象
            Connection conn = DriverManager.getConnection(url, username, password);
            System.out.println(conn);
    }
}

3.1.1Properties文件的使用

properties文件介绍

后缀properties的文件是一种属性文件。这种文件以key=value格式存储内容。Java中可以使用Properties工具类来读取这个文件。项目中会将一些配置信息放到properties文件中，所以properties文件经常作为配置文件来使用。

Properties工具类

Properties工具类，位于java.util包中，该工具类继承自Hashtable<Object,Object>。通过Properties工具类可以读取.properties类型的配置文件。

工具类中常用方法

load(InputStream is)：通过给定的输入流对象读取properties文件并解析，将解析的结果放在Properties工具类存储。

getProperty(String key)：根据key获取对应的value

注意

如果properties文件中含有中文那么需要对idea进行设置。

使用properties文件

package cn.it.bz.JDBC;

import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;

//读取配置文件信息
public class PropertiesTest {
    public static void main(String[] args) throws IOException {
        //实例化操作配置文件的对象
        Properties prop = new Properties();
        //获取能读取配置文件的输入流对象
        InputStream resourceAsStream = PropertiesTest.class.getClassLoader().getResourceAsStream("cn/it/bz/JDBC/JDBC.properties");
        //通过Properties工具类读取已经转换为输入流的JDBC.properties文件并解析
        prop.load(resourceAsStream);
        //解析后的文件放在Properties工具类中,
        String name = prop.getProperty("name");
        System.out.println(name);
        String name1 = prop.getProperty("name1");
        System.out.println(name1);
    }
}

3.1.2优化获取数据库连接

# 连接MySQL的url、username、password、driverName
url = jdbc:mysql://localhost:3306/test?&useSSL=false&serverTimezone=UTC
username = root
password = 123456
driverName = com.mysql.cj.jdbc.Driver

package cn.it.bz.JDBC;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class GetConnection2 {
    public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
        //实例化配置文件工具类
        Properties props = new Properties();
        //通过类加载器获取配置文件的字节输入流数据
        InputStream resourceAsStream = GetConnection2.class.getClassLoader().getResourceAsStream("cn/it/bz/JDBC/JDBC.properties");
        //读取并解析文件
        props.load(resourceAsStream);

        //获取连接数据库的url
        String url = props.getProperty("url");
        //获取连接数据库的用户名
        String username = props.getProperty("username");
        //获取连接数据库的密码
        String password = props.getProperty("password");
        //获取连接数据库驱动的全名
        String driverName = props.getProperty("driverName");

        //加载并注册驱动
        Class.forName(driverName);
        //获取连接数据库的对象
        Connection conn = DriverManager.getConnection(url, username, password);
        System.out.println(conn);
    }
}

3.1.3封装JDBC工具类

package cn.it.bz.JDBC;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

//数据库连接工具（工具类的异常不能往外抛出）
public class JdbcUtils {

   private static String url = null;
   private static String username = null;
   private static String password = null;

    //类加载的时候执行，只进行一次加载，适合读取配置文件的IO操作。
    static {
        Properties prop = new Properties();
        try {
            InputStream resourceAsStream = JdbcUtils.class.getClassLoader().getResourceAsStream("cn/it/bz/JDBC/JDBC.properties");
            prop.load(resourceAsStream);
            //获取配置文件中的连接数据库的url
            url = prop.getProperty("url");
            //获取配置文件中的连接数据库的用户名
            username = prop.getProperty("username");
            //获取配置文件中的连接数据库的密码
            password = prop.getProperty("password");
            //获取驱动程序的全名
            String  driverName = prop.getProperty("driverName");
            //实例化驱动
            Class.forName(driverName);
        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }


    //获取数据库连接对象
    public static Connection getConnection(){
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url, username,password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    //关闭数据库连接对象
    public static void closeConnection(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

     //提交事务
    public  static void commit(Connection conn) {
        if (conn != null) {
            try {
                conn.commit();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //事务回滚
    public static void rollback(Connection conn) {
        if (conn != null) {
            try {
                conn.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //关闭statement对象（preparedStatement）
    public static void closeStatement(Statement stmt) {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    //关闭resultSet
    public static void closeResultSet(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    //关闭DML操作时的资源
    public static void closeDMLResource(Statement ps, Connection con){
        closeStatement(ps);//先关闭statement对象
        closeConnection(con);//再关闭Connection对象


    }
    //关闭DQL时的资源
    public static void closeDQLResource(Connection conn, Statement stmt, ResultSet rs) {
        closeResultSet(rs);//先关闭ResultSet
        closeStatement(stmt);//再关闭Statement
        closeConnection(conn);//最后关闭Connection
    }
}

3.2发送SQL、处理结果

3.2.1通过Statement添加、修改、删除数据

package cn.it.bz.JDBC;

import java.sql.Connection;
import java.sql.Statement;


public class TestStatement {

    //添加用户
    public void insertUser(String username,int userAge) {

        Connection conn = null;
        Statement stmt = null;
        try {
            //获取数据库连接对象
            conn = JdbcUtils.getConnection();
            //获取statement对象
            stmt = conn.createStatement();
            //定义需要statement对象执行的sql
            String sql = "insert into users values(default,'"+username + "',"+userAge + ")";
            //执行该sql;true表示添加成功，false表示添加失败
            boolean execute = stmt.execute(sql);
            System.out.println(execute);
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            //关闭连接(先关闭Statement,再关闭Connection)
            JdbcUtils.closeDMLResource(stmt,conn);
        }
    }

    //修改数据
    public void updateUser(int id,String username, int userAge) {
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = JdbcUtils.getConnection();
            stmt = conn.createStatement();
            String sql = "update users set username='"+username + "',userage="+userAge + " where userid=" + id;
            int i = stmt.executeUpdate(sql);
            System.out.println(i);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.closeDMLResource(stmt, conn);
        }
    }

    //删除数据
    public void deleteUser(int id) {
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = JdbcUtils.getConnection();
            stmt = conn.createStatement();
            String sql = "delete from users where userid="+id;
            int i = stmt.executeUpdate(sql);
            System.out.println(i);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeDMLResource(stmt, conn);
        }
    }

    public static void main(String[] args) {
        TestStatement testStatement = new TestStatement();
        testStatement.insertUser("zhangsan", 18);
        testStatement.updateUser(1, "lisi", 20);
        testStatement.deleteUser(1);
    }
}

3.2.2通过PreparedStatement添加、修改、删除数据

package cn.it.bz.JDBC;

import java.sql.Connection;
import java.sql.PreparedStatement;

public class TestPreparedStatement {

   //添加数据
    public void insertUser(String username, int userAge){
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            // 获取数据库连接
            conn = JdbcUtils.getConnection();
            //定义sql,?是参数占位符位置是从1开始计算的。
            String sql = "insert into users values(default,?,?)";
            //完成参数绑定
            ps = conn.prepareStatement(sql);
            ps.setString(1, username);
            ps.setInt(2, userAge);
            int i = ps.executeUpdate();
            System.out.println(i);
        }catch (Exception exception){
             exception.printStackTrace();
        }finally {
            //PreparedStatement是Statement的子接口
            JdbcUtils.closeDMLResource(ps,conn);
        }
    }

    //修改数据
    public void updateUser(int id, String username, int userAge) {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = JdbcUtils.getConnection();
            String sql = "update users set username=?,userage=? where userid=?";
            ps = con.prepareStatement(sql);
            ps.setString(1, username);
            ps.setInt(2, userAge);
            ps.setInt(3, id);
            int i = ps.executeUpdate();
            System.out.println(i);
        }catch (Exception exception) {
            exception.printStackTrace();
        }finally {
             JdbcUtils.closeDMLResource(ps, con);
        }
    }

    //删除数据
    public void deleteUser(int id) {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = JdbcUtils.getConnection();
            String sql = "delete from users where userid=?";
            ps = con.prepareStatement(sql);
            ps.setInt(1, id);
            int i = ps.executeUpdate();
            System.out.println(i);
        } catch (Exception exception) {
            exception.printStackTrace();
        } finally {
            JdbcUtils.closeDMLResource(ps, con);
        }
    }
    public static void main(String[] args) {
          TestPreparedStatement testPreparedStatement = new TestPreparedStatement();
          testPreparedStatement.insertUser("Java",123);
          testPreparedStatement.updateUser(3, "Tom", 20);
          testPreparedStatement.deleteUser(3);
    }
}

3.2.3ResultSet的使用

ResultSet接口的特点

ResultSet用来存放数据库查询操作获得结果集，通过对ResultSet的操作可以获取查询到的结果集数据。

注意：

ResultSet 对象中存放的并不是我们查询到的所有的结果集。它采用分块加载的方式来载入结果集数据，防止大量数据涌入到内存。

ResultSet特点

ResultSet 对象具有指向其当前数据行的指针。最初，指针被置于第一行之前。next 方法将指针移动到下一行；因为该方法在 ResultSet 对象中没有下一行时返回 false，所以可以在 while 循环中使用它来迭代结果集。
默认的 ResultSet 对象仅有一个向前移动的指针。因此，只能迭代它一次，并且只能按从第一行到最后一行的顺序进行。
ResultSet 接口提供用于获取当前行检索列值的获取方法（getBoolean、getLong 等）。可以使用列的索引位置或列的名称检索值。

ResultSet使用原理

3.2.4通过ResultSet获取查询结果

package cn.it.bz.JDBC;

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

public class TestResultSet {

    //查询所有用户
    public ResultSet getAllUsers(){
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
           //获取Connection对象
            con = JdbcUtils.getConnection();
            String sql = "select * from users";
            ps = con.prepareStatement(sql);
            resultSet = ps.executeQuery();

        }catch (Exception e) {
            e.printStackTrace();
        }
        return resultSet;
    }


    public static void main(String[] args) throws SQLException {
        TestResultSet testResultSet = new TestResultSet();
        ResultSet allUsers = testResultSet.getAllUsers();
        while (allUsers.next()) {
            System.out.println(allUsers.getInt("userid"));
            System.out.println(allUsers.getString("username"));
            System.out.println(allUsers.getString("userAge"));
        }
    }


}

四、ORM编程思想

4.1ORM简介

对象关系映射（英语：Object Relational Mapping，简称ORM，或O/R mapping）是一种为了解决面向对象语言与关系数据库存在的互不匹配的现象。

4.2实体类

实体类就是一个定义了属性，拥有getter、setter、无参构造方法（基本必备）的一个类。实体类可以在数据传输过程中对数据进行封装，相当于一个“工具”、“容器”、“载体”，能存储、传输数据，能管理数据。

实体类特点：

实体类名，尽量和数据库中的表名一一对应
实体类中的属性对应数据库表中的字段，相关的命名最好也一一对应
实体类内方法主要有，getter、setter方法，用于设置、获取数据
实体类属性一般为private类型，方法为public类型
实体类应该有，无参、有参构造方法

4.3ORM的使用

   List<Users> usersList = new ArrayList<Users>();
        while (allUsers.next()) {
            //ORM映射
            Users users = new Users();
            users.setUserid(allUsers.getInt("userid"));
            users.setUsername(allUsers.getString("username"));
            users.setUserage(allUsers.getInt("userage"));
            usersList.add(users);
        }

五、SQL注入

5.1什么是SQL注入

所谓 SQL 注入，就是通过把含有 SQL 语句片段的参数插入到需要执行的 SQL 语句中，最终达到欺骗数据库服务器执行恶意操作的 SQL 命令。

5.2SQL注入案例

statement容易产生sql注入是因为，它将参数和SQL语句组装后再交给MySQL编译。

package cn.it.bz.JDBC;

import java.sql.*;

//SQL注入测试类
public class TestSQLInjection {

    public void sqlInjection(String username,int userage){
        Connection conn = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            conn = JdbcUtils.getConnection();
            statement = conn.createStatement();
            String sql = "select * from users where username ='"+username+"'and userage="+userage;
            System.out.println(sql);
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                String name = resultSet.getString("username");
                int age = resultSet.getInt("userage");
                System.out.println(name + " "+age);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JdbcUtils.closeDQLResource(resultSet,statement,conn);
        }

    }

    public static void main(String[] args) throws SQLException {
        TestSQLInjection testSQLInjection = new TestSQLInjection();
        testSQLInjection.sqlInjection("lisi' or 1=1 -- ",12);
    }
}

5.3解决SQL注入

就是将statement换成PreparedStatement,使用PreparedStatement时数据库驱动会先对sql语句进行编译处理被数据库识别，然后再将参数绑定到已经编译完成的sql语句上，然后再去执行SQL语句。

六、JDBC批量添加数据

6.1批量添加数据简介

在JDBC中通过PreparedStatement的对象的addBatch()和executeBatch()方法进行数据的批量插入。

addBatch()把若干SQL语句装载到一起，然后一次性传送到数据库执行，即是批量处理sql数据的。
executeBatch()会将装载到一起的SQL语句执行。

注意：

MySql默认情况下是不开启批处理的。

数据库驱动从5.1.13开始添加了一个对rewriteBatchStatement的参数的处理，该参数能够让MySql开启批处理。在url中添加该参数：rewriteBatchedStatements=true

6.2Mysql的URL参数说明

useUnicode	[true \| false]	是否使用编码集,需配合 characterEncoding 参数使用。
characterEncoding	[utf-8 \| gbk \| ...]	编码类型。
useSSL	[true \| false]	是否使用SSL协议。
rewriteBatchedStatements	[true \| false]	可以重写向数据库提交的SQL语句。

url = jdbc:mysql://localhost:3306/test?&useSSL=false&serverTimezone=UTC

6.3实现批量数据添加

在url中开启批量添加

url = jdbc:mysql://localhost:3306/test?&useSSL=false&rewriteBatchedStatements=true

6.3.1实现方式一

将数据放在缓存中这样进行一次交互就能将数据添加到数据库中。

package cn.it.bz.JDBC;

import java.sql.Connection;
import java.sql.PreparedStatement;

public class TestAddBatch1 {
    public void addBatch1(){
        Connection con = null;
        PreparedStatement ps = null;
        try {
            //创建连接
            con = JdbcUtils.getConnection();
            //创建PreparedStatement
            ps = con.prepareStatement("insert into users values(default,?,?)");
            //参数绑定
            for (int i = 0; i < 1000; i++) {
                //绑定username
                ps.setString(1,"zhangsan"+i);
                //绑定userage
                ps.setInt(2,20);
                //缓存数据,实现一次交互提高性能
                ps.addBatch();
            }
            //将1000个数据一次性添加到数据库中
            ps.executeBatch();
        }catch (Exception e) {
                e.printStackTrace();
        }finally {
              JdbcUtils.closeDMLResource(ps,con);
        }
    }

    public static void main(String[] args) {
            TestAddBatch1 add = new TestAddBatch1();
            add.addBatch1();
    }
}

6.3.2方式二

一次缓存大量的数据可能造成内存溢出的情况，需要分批将数据添加到数据库中。

 if (i % 500 == 0) {
          ps.executeBatch();
          //清除缓存
          ps.clearBatch();
    }

七、JDBC事务处理

事务简介

事务：

事务是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。
事务操作流程：
- 开启事务
- 提交事务
- 回滚事务

JDBC中事务处理特点

在JDBC中，使用Connection对象来管理事务，默认为自动提交事务。可以通过setAutoCommit(boolean autoCommit)方法设置事务是否自动提交，参数为boolean类型，默认值为true，表示自动提交事务，如果值为false则表示不自动提交事务，需要通过commit方法手动提交事务或者通过rollback方法回滚事务。建议采用这种方式处理事务。

事务处理实现

package cn.it.bz.JDBC;

import java.sql.Connection;
import java.sql.PreparedStatement;

public class TestAddBatch2 {
    /**
     * 批量添加数据方式二
     * 支持事务处理
     */
    public void addBatch(){
        Connection conn = null;
        PreparedStatement ps =null;
        try{
            //创建连接
            conn = JdbcUtils.getConnection();
            //设置事务的提交方式，将自动提交修改为手动提交
            conn.setAutoCommit(false);
            //创建PreparedStatement
            ps = conn.prepareStatement("insert into users  values(default ,?,?)");

            //参数绑定
            for(int i=1;i<=1000;i++){
                //绑定username
                ps.setString(1,"zhangsan"+i);
                //绑定userage
                ps.setInt(2,20);

                //缓存sql
                ps.addBatch();

                if(i%500 == 0){
                    //执行sql
                    ps.executeBatch();
                    //清除缓存
                    ps.clearBatch();
                }

                if(i==501){
                    String str = null;
                    str.length();
                }
            }
            //提交事务
            JdbcUtils.commit(conn);
        }catch(Exception e){
            e.printStackTrace();
            //出问题回滚
            JdbcUtils.rollback(conn);
        }finally{
            JdbcUtils.closeDMLResource(ps,conn);
        }
    }

    public static void main(String[] args) {
        TestAddBatch2 testAddBatch2 = new TestAddBatch2();
        testAddBatch2.addBatch();
    }
}

八、模糊查询

package cn.it.bz.JDBC;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

//模糊查询
public class TestFuzzyQuery {
    //根据用户名字模糊查找用户信息
    public List<Users> fuzzyQuery(String username){
        List<Users> list = new ArrayList<>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            //获取数据库连接
            con = JdbcUtils.getConnection();
            //创建PreparedStatement对象
            ps = con.prepareStatement("select * from users where username like ?");
            //参数绑定
            ps.setString(1,username);
            resultSet = ps.executeQuery();
            while (resultSet.next()) {
                Users user = new Users();
                user.setUserid(resultSet.getInt("userid"));
                user.setUsername(resultSet.getString("username"));
                user.setUserage(resultSet.getInt("userage"));
                list.add(user);
            }
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
             JdbcUtils.closeDQLResource(resultSet,ps,con);
        }
        return list;
    }

    public static void main(String[] args) {
        TestFuzzyQuery testFuzzyQuery = new TestFuzzyQuery();
        List<Users> usersList = testFuzzyQuery.fuzzyQuery("%张%");
        for (Users users : usersList) {
            System.out.println(users);
        }
    }
}

九、动态条件查询

package cn.it.bz.JDBC;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

//动态条件查询
public class TestDynamicConditionQuery {
    public List<Users> dynamicConditionQuery(Users users){
        List<Users> list = new ArrayList<>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            //获取数据库连接
            con = JdbcUtils.getConnection();
            //拼接查询SQL
            String sql = this.combineSQL(users);
            System.out.println(sql);
            //创建PreparedStatement对象
            ps = con.prepareStatement(sql);
            resultSet = ps.executeQuery();
            while (resultSet.next()) {
                Users user = new Users();
                user.setUserid(resultSet.getInt("userid"));
                user.setUsername(resultSet.getString("username"));
                user.setUserage(resultSet.getInt("userage"));
                list.add(user);
            }
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
             JdbcUtils.closeDQLResource(resultSet,ps,con);
        }
        return list;
    }

    //SQL拼接
    private String combineSQL(Users user){
        StringBuilder stringBuilder = new StringBuilder("select * from users where 1=1 ");
        stringBuilder.append(user.getUserid() == 0?" ":" and userid="+user.getUserid());
        stringBuilder.append(user.getUsername() == null||user.getUsername().equals("") ? " ":" and username= '"+user.getUsername()+"'");
        stringBuilder.append(user.getUserage()==0?" ":" and userage = "+user.getUserage());
        return stringBuilder.toString();
    }

    public static void main(String[] args) {
        TestDynamicConditionQuery testDynamicConditionQuery = new TestDynamicConditionQuery();
        List<Users> usersList = testDynamicConditionQuery.dynamicConditionQuery(new Users(0, "", 12));
        for (Users users : usersList) {
            System.out.println(users);
        }

    }
}

十、分页查询

10.1分页查询简介

当一个操作数据库进行查询的语句返回的结果集内容如果过多，那么内存极有可能溢出，所以在查询中含有大数据的情况下分页是必须的。

分页查询分类：

物理分页：
- 在数据库执行查询时（实现分页查询），查询需要的数据—依赖数据库的SQL语句
- 在SQL查询时，从数据库只检索分页需要的数据
- 通常不同的数据库有着不同的物理分页语句
- MySql物理分页采用limit关键字
逻辑分页：

在sql查询时，先从数据库检索出所有数据的结果集，在程序内，通过逻辑语句获得分页需要的数据

如何在MySql中实现物理分页查询

select * from tableName limit m,n

其中m与n为数字。n代表需要获取多少行的数据项，而m代表从哪开始(以0为起始)。

例如我们想从users表中先获取前两条数据SQL为：

select * from users limit 0,2;

那么如果要继续看下两条的数据则为：

select * from users limit 2,2;

以此类推

分页公式：(当前页-1)*每页大小；

10.2Page模型实现分页

package cn.it.bz.JDBC;

import java.util.ArrayList;
import java.util.List;

//分页查询的实体类
public class Page<T> {
    //当前页
    private int currentPage;
    //每页显示的条数
    private int pageSize;
    //总条数
    private int total;
    //总页数
    private int totalPages;
    //结果集,泛型更具通用性
    private List<T> list;

    public Page() {
    }

    public Page(int currentPage, int pageSize, int total, int totalPages, List<T> list) {
        this.currentPage = currentPage;
        this.pageSize = pageSize;
        this.total = total;
        this.totalPages = totalPages;
        this.list = list;
    }

    public int getCurrentPage() {
        return currentPage;
    }

    public void setCurrentPage(int currentPage) {
        this.currentPage = currentPage;
    }

    public int getPageSize() {
        return pageSize;
    }

    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }

    public int getTotal() {
        return total;
    }

    public void setTotal(int total) {
        this.total = total;
    }

    public int getTotalPages() {
        return totalPages;
    }

    public void setTotalPages(int totalPages) {
        this.totalPages = totalPages;
    }

    public List<T> getList() {
        return list;
    }

    public void setList(List<T> list) {
        this.list = list;
    }
}

package cn.it.bz.JDBC;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

//分页查询
public class TestPage {

    //page作用有两个：传递分页参数；存储查询结果。
    public Page<Users> getUsers(Page page){
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        List<Users> list = new ArrayList<Users>();
          try {
              con = JdbcUtils.getConnection();
              ps =  con.prepareStatement("select * from users limit ?,?");
              //绑定参数:(当前页-1)*每页显示的条数
              ps.setInt(1,(page.getCurrentPage()-1)*page.getPageSize());
              ps.setInt(2,page.getPageSize());
              //执行查询
              resultSet = ps.executeQuery();
              while (resultSet.next()) {
                  Users users = new Users();
                  users.setUserid(resultSet.getInt("userid"));
                  users.setUsername(resultSet.getString("username"));
                  users.setUserage(resultSet.getInt("userage"));
                  list.add(users);
              }
              //存结果集
              page.setList(list);
              //查询总条数
              ps = con.prepareStatement("select count(*) from users");
              resultSet = ps.executeQuery();
              while (resultSet.next()) {
                  int total = resultSet.getInt(1);
                  page.setTotal(total);
                  //总页数=总条数/每页显示的条数；不能整除就向上取整。
                  int totalPage = (int)Math.ceil(total / page.getPageSize() * 1.0);
                  page.setTotalPages(totalPage);
              }
          }catch (Exception e) {
              e.printStackTrace();
          }finally {
             JdbcUtils.closeDQLResource(resultSet,ps,con);
          }
        return page;
    }

    public static void main(String[] args) {
            Page page = new Page();
            page.setCurrentPage(2);
            page.setPageSize(20);
            TestPage testPage = new TestPage();
            Page<Users> users = testPage.getUsers(page);
        for (Users users1 : users.getList()) {
            System.out.println(users1);
        }
        System.out.println("当前是第"+page.getCurrentPage()+"页。共"+page.getTotalPages()+"页。");
        System.out.println("当前页有"+page.getPageSize()+"条数据。共"+page.getTotal()+"条数据。");

    }
}

十一、数据库连接池

11.1数据库连接池简介

11.1.1什么是数据库连接池

数据库连接池（Connection pooling）是程序启动时建立足够的数据库连接，并将这些连接组成一个连接池，由程序动态地对池中的连接进行申请，使用，释放。

它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个；释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的数据库连接遗漏。这项技术能明显提高对数据库操作的性能。

11.1.2不使用数据库连接池存在的问题

普通的JDBC数据库连接使用 DriverManager 来获取，每次向数据库建立连接的时候都要将 Connection加载到内存中，再验证用户名和密码，所以整个过程比较耗时。
需要数据库连接的时候，就向数据库要求一个，执行完成后再断开连接。这样的方式将会消耗大量的资源和时间。数据库的连接资源并没有得到很好的重复利用。若同时有几百人甚至几千人在线，频繁的进行数据库连接操作将占用很多的系统资源，严重的甚至会造成服务器的崩溃。
对于每一次数据库连接，使用完后都得断开。否则，如果程序出现异常而未能关闭，将会导致数据库系统中的内存泄漏，最终将导致重启数据库。

11.1.3JDBC数据库连接池的必要性

数据库连接池的基本思想：为数据库连接建立一个“缓冲池”。预先在缓冲池中放入一定数量的连接，当需要建立数据库连接时，只需从“缓冲池”中取出一个，使用完毕之后再放回去。
数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是重新建立一个。
数据库连接池在初始化时将创建一定数量的数据库连接放到连接池中，这些数据库连接的数量是由最小数据库连接数来设定的。无论这些数据库连接是否被使用，连接池都将一直保证至少拥有这么多的连接数量。连接池的最大数据库连接数量限定了这个连接池能占有的最大连接数，当应用程序向连接池请求的连接数超过最大连接数量时，这些请求将被加入到等待队列中。

11.1.4数据库连接池的优点

资源重用：由于数据库连接得以重用，避免了频繁创建，释放连接引起的大量性能开销。在减少系统消耗的基础上，另一方面也增加了系统运行环境的平稳性。
更快的系统反应速度：数据库连接池在初始化过程中，往往已经创建了若干数据库连接置于连接池中备用。此时连接的初始化工作均已完成。对于业务请求处理而言，直接利用现有可用连接避免了数据库连接初始化和释放过程的时间开销，从而减少了系统的响应时间。
新的资源分配手段：对于多应用共享同一数据库的系统而言，可在应用层通过数据库连接池的配置实现某一应用最大可用数据库连接数的限制避免某一应用独占所有的数据库资源.
统一的连接管理：避免数据库连接泄露在较为完善的数据库连接池实现中，可根据预先的占用超时设定，强制回收被占用连接，从而避免了常规数据库连接操作中可能出现的资源泄露。

11.1.5常用的数据库连接池

c3p0：是一个开源组织提供的数据库连接池，速度相对较慢，稳定性还可以。
DBCP：是Apache提供的数据库连接池。速度相对c3p0较快，但自身存在bug。
Druid：是阿里提供的数据库连接池，据说是集DBCP、c3p0优点于一身的数据库连接池，目前经常使用。

11.2Druid使用

11.2.1Druid使用步骤

1、导入druid-1.2.8.jar包到lib目录下，并引入到项目中

2、在src下创建一个druid.properties类型的文件，并写入

url=jdbc:mysql://localhost:3306/test
driverClassName=com.mysql.cj.jdbc.Driver
username=root
password=123456
initialSize=10
maxActive=20

3、加载配置文件

4、获取连接池对象

5、通过连接池对象获取连接

11.2.2druid配置信息

配置	缺省值	说明
name		配置这个属性的意义在于，如果存在多个数据源，监控的时候可以通过名字来区分开来。如果没有配置，将会生成一个名字，格式是："DataSource-" + System.identityHashCode(this)
url		连接数据库的url。
username		连接数据库的用户名。
password		连接数据库的密码。
driverClassName	根据url自动识别	这一项可配可不配，如果不配置druid会根据url自动识别dbType，然后选择相应的driverClassName(建议配置下)
initialSize	0	初始化时建立物理连接的个数。
maxActive	8	最大连接池数量
minIdle		最小连接池数量
maxWait		获取连接时最大等待时间，单位毫秒。配置了maxWait之后，缺省启用公平锁，并发效率会有所下降，如果需要可以通过配置useUnfairLock属性为true使用非公平锁。
poolPreparedStatements	false	是否缓存preparedStatement，也就是PSCache。PSCache对支持游标的数据库性能提升巨大，比如说oracle。在mysql下建议关闭。
maxOpenPreparedStatements	-1	要启用PSCache，必须配置大于0，当大于0时，poolPreparedStatements自动触发修改为true。在Druid中，不会存在Oracle下PSCache占用内存过多的问题，可以把这个数值配置大一些，比如说100
validationQuery		用来检测连接是否有效的sql，要求是一个查询语句。如果validationQuery为null，testOnBorrow、testOnReturn、testWhileIdle都不会其作用。
testOnBorrow	true	申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。
testOnReturn	false	归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能
testWhileIdle	false	建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。
timeBetweenEvictionRunsMillis		有两个含义： 1) Destroy线程会检测连接的间隔时间2) testWhileIdle的判断依据，详细看testWhileIdle属性的说明
numTestsPerEvictionRun		不再使用，一个DruidDataSource只支持一个EvictionRun
minEvictableIdleTimeMillis
connectionInitSqls		物理连接初始化的时候执行的sql
exceptionSorter	根据dbType自动识别	当数据库抛出一些不可恢复的异常时，抛弃连接
filters		属性类型是字符串，通过别名的方式配置扩展插件，常用的插件有：监控统计用的filter:stat日志用的filter:log4j防御sql注入的filter:wall
proxyFilters		类型是List<com.alibaba.druid.filter.Filter>，如果同时配置了filters和proxyFilters，是组合关系，并非替换关系

11.2.3获取数据库连接

package cn.it.bz.JDBC;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;

//Druid连接池
public class TestDruid {
    public static void main(String[] args) throws Exception {
        //加载配置字节输入流
        InputStream resourceAsStream = TestDruid.class.getClassLoader().getResourceAsStream("cn/it/bz/JDBC/druid.properties");
        //创建配置文件工具类
        Properties properties = new Properties();
        //加载配置文件
        properties.load(resourceAsStream);
        //获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        //获取连接
        Connection connection = dataSource.getConnection();
        System.out.println(connection);

    }
}

11.2.4基于Druid封装工具类

package cn.it.bz.JDBC;


import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.rmi.ConnectIOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JdbcDruidUtil {
    //数据库连接池对象
    private static DataSource dataSource;
    //初始化
    static {
        try {
            //获取读取配置文件的字节输入流对象
            InputStream resourceAsStream = JdbcDruidUtil.class.getClassLoader().getResourceAsStream("cn/it/bz/JDBC/druid.properties");
            //创建配置文件工具类
            Properties properties = new Properties();
            //加载配置文件
            properties.load(resourceAsStream);
            //创建连接池对象
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //获取数据库连接对象
    public static Connection getConnection(){
        Connection con = null;
        try {
            con = dataSource.getConnection();
        }catch (Exception e) {
            e.printStackTrace();
        }
        return con;
    }
    //归还连接对象
    public static void closeConnection(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    //提交事务
    public  static void commit(Connection conn) {
        if (conn != null) {
            try {
                conn.commit();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    //事务回滚
    public static void rollback(Connection conn) {
        if (conn != null) {
            try {
                conn.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    //关闭statement对象（preparedStatement）
    public static void closeStatement(Statement stmt) {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    //关闭resultSet
    public static void closeResultSet(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    //关闭DML操作时的资源
    public static void closeDMLResource(Statement ps, Connection con){
        closeStatement(ps);//先关闭statement对象
        closeConnection(con);//再关闭Connection对象


    }
    //关闭DQL时的资源
    public static void closeDQLResource(ResultSet rs, Statement stmt, Connection conn) {
        closeResultSet(rs);//先关闭ResultSet
        closeStatement(stmt);//再关闭Statement
        closeConnection(conn);//最后关闭Connection
    }
}

十二、应用程序分层

12.1应用程序分层简介

应用程序分层是指通过创建不同的包来实现项目的分层，将项目中的代码根据功能做具体划分，并存放在不同的包下。

三层结构

三层结构就是将整个业务应用划分为：表述层、业务逻辑层、数据访问层。区分层次的目的即为了“高内聚低耦合”的思想。在软件体系架构设计中，分层式结构是最常见，也是最重要的一种结构。

分层优点

分层结构将应用系统划分为若干层，每一层只解决问题的一部分，通过各层的协作提供整体解决方案。大的问题被分解为一系列相对独立的子问题，局部化在每一层中，这样就有效的降低了单个问题的规模和复杂度，实现了复杂系统的第一步也是最为关键的一步分解。
分层结构具有良好的可扩展性，为应用系统的演化增长提供了一个灵活的支持，具有良好的可扩展性。增加新的功能时，无须对现有的代码做修改，业务逻辑可以得到最大限度的重用。
分层结构易于维护。在对系统进行分解后，不同的功能被封装在不同的层中，层与层之间的耦合显著降低。因此在修改某个层的代码时，只要不涉及层与层之间的接口，就不会对其他层造成严重影响。

分层命名

表述层：web或controller

业务层：service

数据访问层：dao (Data Access Object)