JDBC使用

JDBC

现在我们使用客户端工具访问数据库，手动建立连接，输入用户名和密码，编写sql语句来执行查看结果，那么我们在实际开发中是这样的方式吗？

• 不是，因为操作量太大，无法保证效率和正确性，所以需要使用JDBC来完成这些操作

一、概念与核心思想

JDBC(Java Databases Connectivity)，Java连接数据库的规范（标准）。

使用JDBC连接数据库可以完成CRUD操作

核心思想：

• Java中定义了访问数据库的接口，可以为多种关系型数据库提供统一的访问方式。
• 由数据库厂商提供驱动实现类（Driver数据库驱动）

我们使用java程序来使用接口，进行连接、发送、接收等操作，由数据库驱动包去接收这些操作然后再去操作数据库。

二、驱动与接口

• Mysql数据库驱动
• 可在mysql官网下载对应mysql版本的jar包（数据库驱动）

官网：https://downloads.mysql.com/archives/c-j/

• JDBC API

二、JDBC开发流程

前提：先将数据库驱动器jar包放到lib目录

JDBC开发流程如下：

• 注册驱动（对应mysql版本）：
  • 5.x版本：Class.forName("com.mysql.jdbc.Driver");
  • 8.x版本：Class.forName("com.mysql.cj.jdbc.Driver")
• 连接数据库
  • Connection conn = DriverManager.getConnection(url,name,pwd)
  • 不同版本的url设置不同
    • 5.5版本：String url = "jdbc:mysql://ip地址:端口号/库名?userUnicode=true&characterEncoding=utf-8";
    • 5.7版本：String url = "jdbc:mysql://ip地址:端口号/库名?userUnicode=true&characterEncoding=utf-8&userSSL=false";
    • 8.x版本：String url = "jdbc:mysql://ip地址:端口号/库名?userUnicode=true&characterEncoding=utf-8&userSSL=false&serverTimezone=UTC;"
• 获取执行SQL对象
  • Statement statement = conn.createStatement();
• 执行SQL语句
  • int result = statement.executeUpdate(sql语句);
• 释放资源：
  • statement.close();
  • conn.closr();

注意：最好将释放资源放入到finally中，因为就算代码异常也会释放资源，避免占用连接数。

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

    //            1.加载驱动
    Class.forName("com.mysql.jdbc.Driver"); // 5.x 对应版本可以选择对应驱动加载

    //            2.获取连接
    String url = "jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf-8";
    String username = "root";
    String password = "123";
    Connection connection = DriverManager.getConnection(url,username,password);
    //可以打印测试，当返回值为地址时，表示连接成功
    System.out.println(connection); //com.mysql.jdbc.JDBC4Connection@66048bfd

    //            3.获取执行对象
    String sql = "insert into student values('123','zhangsan','男','20','2001-1-1',2)";
    PreparedStatement preparedStatement = connection.prepareStatement(sql);

    //           4.执行
    int count = preparedStatement.executeUpdate();//返回值为影响的行数
    System.out.println("影响了：" + count + "行");
    //            5.处理结果
    if (count > 0){
        System.out.println("添加成功");
    }else{
        System.out.println("添加失败");
    }

    //            6.释放资源
    preparedStatement.close();
    connection.close();
}

五、IDEA可视化Mysql

如何在idea中查看mysql中有哪些表以及哪些字段名，数据，且进行可视化增删改查，如下配置

五、ResultSet使用

• 概念
  • 在执行查询SQL后，存放查询到的结果集的数据
• 接收结果集
  • ResultSet rs = statement.executeQuery(sql);
• 注意
  • ResultSet完成了查询结果的存储功能，而且只能读取一次，不能够来回的滚动读取。

通过遍历ResultSet来获取所有结果

• ResultSet以表结构进行临时结果的存储，通过JDBC API将其中数据进行一次获取

方法名	返回值类型	描述
next()	boolean	数据行指针，每调用一次，指针向下移动一行，结果为true，表示当前行有数据
getString(编号)；	String	代表根据列的编号顺序获得，从1开始
getString(“列名”)	String	代表根据列名获取

经验：

• getXXX方法可以获取的类型有：基本数据类型、引用数据类型

常见错误

5.1 jdbc规范写法

这样写比之前代码更加简洁且规范

public class Test2 {

    public static void main(String[] args) {

        //将sql语句定义在try..catch外赋值语句，不用放到异常中
        String sql = "select * from student";

        try(
                //将建立连接和获取执行对象放到try中，结束后会自动关闭资源，就不需要添加到finally中
                //将账号、密码、以及连接的url都封装到类中，提供公有方法来获取连接对象
                //将加载驱动也放到类中，只需要加载一次即可
                Connection connection = DBHelp.getConnection();
            	//获取执行对象
                PreparedStatement preparedStatement = connection.prepareStatement(sql);

        ) {
            //3.执行查询
            ResultSet resultSet = preparedStatement.executeQuery();

            //4.查看查询结果，使用next方法可以一行行读，但是不能回滚到之前
            while(resultSet.next()){
                //需要查看更多字段，只需要如下格式打印，在后面继续拼接字段
             	//获取当前行的每一列值
            	//可以通过序号来获取，也可以通过列名来获取，推荐使用名称，聚合函数字段不太好使用，推荐使用别名代替聚合函数字段
                System.out.println("id = " + resultSet.getInt("id") + ",name=" + resultSet.getString("name"));
            }

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

//数据库帮助类
public class DBHelp {

    //将加载驱动设置在静态代码块，会随类一起加载且只用加载一次
    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    //将建立连接的信息封装到该方法中，返回值为建立连接后的对象
    public static Connection getConnection() throws SQLException {
        String url = "jdbc:mysql://localhost:3306/school?useUnicode = true?characterEncoding = utf-8";
        String username = "root";
        String password = "123";
        return DriverManager.getConnection(url,username,password);
    }
}

六、两种SQL传参方式

当SQL执行不需要传入参数时，Statement和PrepareStatemnt是一样的作用，但是当需要传入参数时，两种方式就不同了

先创建一个Jobs类（用来模拟用户输入的各字段值）

//Jobs类表示各字段名，用户输入的个字段值
class Jobs{

    //将表中各字段定义为属性
    Integer id;
    String name;
    String gender;
    Integer age;
    String birther;
    String grand_id;

	//此处省略无参、有参构造以及getter和setter方法
}

6.1 PrepareStatemnt

该方式可以防止SQL注入

    //prepareStatement接收参数后执行的方式
    public boolean insert1(Jobs jobs){
        try(
                //建立连接
                Connection connection = DBHelp.getConnection()
        ){

            //prepareStatement先使用占位符占位，然后给每一位赋值
            //这里的？是占位符，后面pst.set数据类型(第一个占位符,传入的数据)
            String sql = "insert into student values (?,?,?,?,?,?)";
            PreparedStatement pst = connection.prepareStatement(sql);
            pst.setInt(1,jobs.getId());
            pst.setString(2,jobs.getName());
            pst.setString(3,jobs.getGender());
            pst.setInt(4,jobs.getAge());
            pst.setString(5, jobs.getBirther());
            pst.setString(6, jobs.getGrand_id());
            //根据执行后的返回值（返回值为影响的行数）来判断是否执行成功
            return pst.executeUpdate() > 0;
        }catch (Exception e){
            e.printStackTrace();
        }
        return false;
    }

6.2 Statement【不推荐】

    //Statement接收参数后执行的方式
    public boolean insert2(Jobs jobs){

        try(
                //建立连接
                final Connection connection = DBHelp.getConnection()
        ){
            //Statement是拼接SQL语句
            String sql = "insert into student values ("+ jobs.getId()+",'" +
                     jobs.getName()+"','"+ jobs.getGender() +"',"+ jobs.getAge()
                    +",'" + jobs.getBirther() + "','"+ jobs.getGrand_id() +"')";

            //打印查看是否正确 insert into student values (1000,'AAA','男',50,'1999-1-1','6')
            System.out.println(sql);

            //获取执行对象
            Statement statement = connection.createStatement();

            //执行(将sql传入)后的返回值（返回值为影响的行数）来判断是否执行成功
            return  statement.executeUpdate(sql)>0;

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

        return false;
    }

七、SQL注入

利用拼接SQL字符串中的单引号，来注入关键字来实现非法操作。

例如：简单登录操作如下：

        String username = "zhangsan";
        String p = "123456";
        String sql = "select count(1) from stu where username = '" + username + "' and password = '" + p + "'";

        //使用Statement来拼接字符串传参
        //如果用户正常输入username和passwordname就不会出现sql注入攻击，如果用户将密码输入为 aa' or '1' = '1 后，
        // 拼接字符串后，数据库执行的语句就如下
        // select count(1) from stu where username = ' username ' and password = 'aa' or '1' = '1'
        //此时这个where后面条件就会搜索所有数据，此时count就不是0，就会登录成功(因为我们是判断count(1)是0还是1来决定是否输入正确或者错误)

上面的代码执行看似没有问题，但是当用户将密码输入sdadsd' OR '1' = '1，无论用户名是什么，都会显示登录成功。就是简单的SQL注入攻击。

八、PreparedStatement

概念：PreparedStatement继承了Statement接口，执行SQL语句的方法无异。

作用：

• 预编译SQL语句，效率高
• 安全，避免了SQL注入
• 可以动态的填充数据，执行多个同构的SQL语句

应用：

• 预编译SQL语句，参数使用？占位

PreparedStatement pstmt = null;
pstmt = conn.prepareStatement("select * from 表名 where 字段名 = ? and pwd = ?");

• 参数下标赋值

pstmt.setString(1,"lmz"); //1 表示第一个问号占位符，为他赋值
pstmt.setString(2,"123456");

注意：

• JDBC中的所有参数都由？符号占位，这种称为参数标记
• 再执行SQL语句之前，必须为每一个参数提供值

    //prepareStatement接收参数后执行的方式
    public boolean insert1(Jobs jobs){
        try(
                //建立连接
                Connection connection = DBHelp.getConnection()
        ){

            //prepareStatement先使用占位符占位，然后给每一位赋值
            //这里的？是占位符，后面pst.set数据类型(第一个占位符,传入的数据)
            String sql = "insert into student values (?,?,?,?,?,?)";
            PreparedStatement pst = connection.prepareStatement(sql);
            pst.setInt(1,jobs.getId());
            pst.setString(2,jobs.getName());
            pst.setString(3,jobs.getGender());
            pst.setInt(4,jobs.getAge());
            pst.setString(5, jobs.getBirther());
            pst.setString(6, jobs.getGrand_id());
            //根据执行后的返回值（返回值为影响的行数）来判断是否执行成功
            return pst.executeUpdate() > 0;
        }catch (Exception e){
            e.printStackTrace();
        }
        return false;
    }

九、封装工具类

9.1 概念与使用

用途：

• 在JDBC的使用中，连接数据库、关闭连接等存在大量的重复代码
• 把传统的JDBC代码进行重构，抽取出通用的JDBC工具类

重用性方案

• 封装获取连接方法

public static Connection getConnection(){}

• 封装释放资源方法

public static void closeAll(Connection conn,Statement sm,ResultSet rs){}

public class DBHelp {

    //将加载驱动设置在静态代码块，会随类一起加载且只用加载一次
    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    //将建立连接的信息封装到该方法中，返回值为建立连接后的对象
    public static Connection getConnection() throws SQLException {
        String url = "jdbc:mysql://localhost:3306/school?useUnicode = true?characterEncoding = utf-8";
        String username = "root";
        String password = "123";
        return DriverManager.getConnection(url,username,password);
    }
}

上面的内容，无法热修改，因为需要修改源代码，所以对于在上线后还需要修改的参数，应该使用配置文件，如下处理

• 先创建一个配置文件，以 .properties后缀的文件(里面存储的是键值对形式)
• 然后再JDBC工具类中加载配置文件即可

dbconfig.properties

#properties配置文件中存放为键值对形式
#键值取名注意不要和其他变量冲突，不要加空格和引号
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf-8
jdbc.username=root
jdbc.password=123

DBConnection（数据库连接类）:在5.1中将DBHelp换成该类运行测试即可，其他不变

public class DBConnection {
    //创建Properties对象来加载存放文件内容
    private final static Properties PROPERTIES = new Properties();

    static {
        try {
            //类加载配置文件
            final InputStream inputStream = DBConnection.class.getResourceAsStream("/dbconfig.properties");
            //读取配置文件内容
            PROPERTIES.load(inputStream);
            //加载驱动(直接加载配置文件键值对)
            Class.forName(PROPERTIES.getProperty("jdbc.driver"));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //封装获取连接方法，返回值为连接对象
    public static Connection getConnection() throws SQLException {
        //通过配置文件内容来连接数据库
        return DriverManager.getConnection(PROPERTIES.getProperty("jdbc.url"),
                PROPERTIES.getProperty("jdbc.username"),
                PROPERTIES.getProperty("jdbc.password"));
    }

}

9.2 JDBC增删改查完整案例

资料test1中有全部代码

准备：

• 在数据库中准备一个student1表，表中有 id，name，age，gender等字段
• 再项目中导入数据库驱动jar包和lombok的jar包然后add as Library（放到lib目录下）， lombok的jar包可以简化类，使用注解代替getter和setter、有参构造、无参构造等代码。也可不下载，编写这些也是一样的

9.2.1 配置文件（resources）

dbconfig.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf-8
jdbc.username=root
jdbc.password=123

9.2.2 实体类（Entity）

实体类应该与表名一样且属性名最好与表中字段名一致，因为这些属性中存放这应该存储到数据库中的数据

//该实体类对应学生信息表
public class Student {
    private int id;
    private String name;
    private int age;
    private String gender;

    //此处省略有参无参构造、getter和setter、toString方法
}

//该实体类对应查出的学生成绩表
public class StudentScore {

    private int id;
    private String name;
    private int China;
    private int Math;
    private int English;
    private int total;

	//省略setter、getter、有参无参构造、toString方法
}

9.2.3 工具类（util）

• 工具类中有数据库获取连接的方法，以及加载一些配置信息
• 定义一个常量接口在工具类中

连接类

public class DBConnection {

    private final static Properties PROPERTIES = new Properties();

    //加载和读取配置文件信息，并加载驱动
    static {
        try {

            final InputStream inputStream = DBConnection.class.getResourceAsStream("/dbconfig.properties");

            PROPERTIES.load(inputStream);
            Class.forName(PROPERTIES.getProperty("jdbc.driver"));

        }catch (Exception e){
            e.printStackTrace();
        }
    }
    
    //封装连接过程，返回连接对象
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(PROPERTIES.getProperty("jdbc.url"),
                PROPERTIES.getProperty("jdbc.username"),
                PROPERTIES.getProperty("jdbc.password"));
    }
}

常量接口，定义各种操作语句

//常量接口
public interface Constants {
    //将增删改查语句定义在常量接口中，将可变的参数定义为？，可以对？进行传参
    String STUDENT_FIND_ALL = "select id, name, gender, age, grand_id from student;";
    String STUDENT_FIND_BY_ID = "select id, name, gender, age, grand_id from student where id = ?;";
    String STUDENT_ADD = "insert into student(id, name, gender, age, grand_id) value (?,?,?,?,?);";
    String STUDENT_DELETE = "delete from student where id = ?;";
    String STUDENT_UPPDATE = "update student set name = ?, gender = ?, age = ?, grand_id = ?  where id = ?;";

    //扩展：查询所有学生的分数
    String STUDENT_SCOREALL = "SELECT s.id,s.NAME,sum(CASE WHEN course='China' THEN score ELSE 0 END) China,sum(CASE WHEN course='Math' THEN score ELSE 0 END) Math,sum(CASE WHEN course='English' THEN score ELSE 0 END) English,sum(score) total FROM student s LEFT JOIN results r ON s.grand_id=r.id GROUP BY id;";
}

9.2.4 访问对象（DAO）

DAO中存放着sql操作，先定义一个StudentDAO接口规定一共有哪些操作（增删改查等），然后实现该接口

StudentDAO

public interface StudentDAO {

    /**
     * 查询所有数据
     * @return 返回所有数据的集合
     */
    List<Student> findALL();

    /**
     * 根据id查询学生信息
     * @param id
     * @return 返回查询的结果对象
     */
    Student findByID(int id);

    /**
     * 添加数据
     * @param student
     * @return 返回是否添加成功
     * */
    boolean add(Student student);

    /**
     * 根据id删除数据
     * @param id
     * @return 返回是否删除成功
     * */
    boolean delete(int id);

    /**
     * 修改学生信息
     * @param student
     * @return
     */
    boolean update(Student student);

    /**
     * 查询所有学生的成绩
     * @return
     */
    List<StudentScore> allScore();

}

StudentDAOImpl

public class StudentDAOImpl implements StudentDAO {

    /**
     * 查询所有
     * @return
     */
    @Override
    public List<Student> findALL() {
        //集合存放结果然后返回
        List<Student> list = new ArrayList();
        try(
                //获得连接对象并获得执行对象
                final Connection connection = DBConnection.getConnection();
                final PreparedStatement pstt = connection.prepareStatement(Constants.STUDENT_FIND_ALL);
        ){
            //select id, name, gender, age, grand_id from student;
            //执行查询
            final ResultSet resultSet = pstt.executeQuery();
            //遍历所有查询到的结果，然后封装成Student对象，再添加到list集合中
            while (resultSet.next()){
//                Student s = new Student();
//                s.setId(resultSet.getInt("id"));
//                s.setName( resultSet.getString("name"));
//                s.setAge(resultSet.getInt("age"));
//                s.setGender(resultSet.getString("gender"));
//                s.setGrand_id(resultSet.getInt("grand_id"));

                //上面可以简写成有参构造传参
                Student s = new Student(resultSet.getInt("id"),
                        resultSet.getString("name"),
                        resultSet.getInt("age"),
                        resultSet.getString("gender"),
                        resultSet.getInt("grand_id"));
                list.add(s);
            }

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

        return list;
    }

    /**
     * 根据id查询数据
     * @param id
     * @return
     */
    @Override
    public Student findByID(int id) {
        try(
                //获得连接和执行对象
                final Connection connection = DBConnection.getConnection();
                final PreparedStatement pstt = connection.prepareStatement(Constants.STUDENT_FIND_BY_ID);
        ){
            //select id, name, gender, age, grand_id from student where id = ?
            pstt.setInt(1,id);
            //因为id唯一，根据id查询只有一个对象，所以直接返回，不需要while循环
            final ResultSet resultSet = pstt.executeQuery();
            //必须让resultSet向下移动才能访问到查询到的数据
            resultSet.next();
            //直接将查询到的数据封装对象然后返回
            return new Student(resultSet.getInt("id"),
                    resultSet.getString("name"),
                    resultSet.getInt("age"),
                    resultSet.getString("gender"),
                    resultSet.getInt("grand_id"));
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 添加数据
     * @param student
     * @return
     */
    @Override
    public boolean add(Student student) {
        try(
                final Connection connection = DBConnection.getConnection();
                final PreparedStatement pstt = connection.prepareStatement(Constants.STUDENT_ADD)
        ){
            //insert into student(id, name, gender, age, grand_id) value (?,?,?,?,?);
            //给sql语句中每一个占位符赋值，值为传入的student属性值
            pstt.setInt(1,student.getId());
            pstt.setString(2,student.getName());
            pstt.setString(3,student.getGender());
            pstt.setInt(4,student.getAge());
            pstt.setInt(5,student.getGrand_id());
            return pstt.executeUpdate() > 0;

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

    /**
     * 根据id删除
     * @param id
     * @return
     */
    @Override
    public boolean delete(int id) {
        try(
                //获得连接对象和执行对象
                final Connection connection = DBConnection.getConnection();
                final PreparedStatement pstt = connection.prepareStatement(Constants.STUDENT_DELETE);
        ){
            //delete from student where id = ?;
            //执行前先赋值，将id赋值到语句中，替换占位符
            pstt.setInt(1,id);
            //执行判断影响行数是否大于0，是则为true否则false
            return pstt.executeUpdate() > 0;
        }catch (Exception e){
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 根据id修改数据
     * @param student
     * @return
     */
    @Override
    public boolean update(Student student) {
        try(
                final Connection connection = DBConnection.getConnection();
                final PreparedStatement pstt = connection.prepareStatement(Constants.STUDENT_UPPDATE)
        ) {
            //update student set name = ?, gender = ?, age = ?, grand_id = ?  where id = ?;
            //替换占位符？的值
            pstt.setString(1,student.getName());
            pstt.setString(2,student.getGender());
            pstt.setInt(3,student.getAge());
            pstt.setInt(4,student.getGrand_id());
            pstt.setInt(5,student.getId());
            //判断是否修改成功
            return pstt.executeUpdate() > 0;

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

    @Override
    public List<StudentScore> allScore() {
        List<StudentScore> list = new ArrayList<>();
        try(
                final Connection connection = DBConnection.getConnection();
                final PreparedStatement pstt = connection.prepareStatement(Constants.STUDENT_SCOREALL)
        ){

            final ResultSet resultSet = pstt.executeQuery();
            while (resultSet.next()){
                list.add(new StudentScore(
                        resultSet.getInt("id"),
                        resultSet.getString("name"),
                        resultSet.getInt("China"),
                        resultSet.getInt("Math"),
                        resultSet.getInt("English"),
                        resultSet.getInt("total")
                ));
            }
            return list;
        }catch (Exception e){
            e.printStackTrace();
        }

        return null;
    }
}

9.2.5 main方法（test）

在主方法中测试是否成功

//main方法
public class Test1 {

    public static void main(String[] args) {

        StudentDAO studentDAO = new StudentDAOImpl();
        //查询
        System.out.println(studentDAO.findALL());
        //根据id查询
        System.out.println(studentDAO.findByID(1));
        //删除
        System.out.println(studentDAO.delete(2));
        //添加
        System.out.println(studentDAO.add(new Student(100,"lmz",15,"男",5)));
        //修改
        System.out.println(studentDAO.update(new Student(100,"lmz123",15,"男",5)));

        //查询所有学生的分数
        System.out.println(studentDAO.allScore());
    }

}

十、ORM与DAO

10.1 ORM

概念：

• ORM(Object Relational Mapping)：对象关系映射
• 对结果集(ResultSet)遍历时，取出的都是零散的数据
• 在实际应用开发中，我们㤇将零散的数据进行封装整理

实体类（Entity）：

• 一行数据中，多个零散的数据进行整理
• 通过entity的规则对表中的数据进行对象的封装
• 表名=类名，列名=属性名；提供各个属性的get、set方法
• 提供无参构造方法（视情况添加有参构造）

注意：ORM就是对象与数据库表中各字段的映射，表中字段取出来存到对象中，一个对象对应一条数据

例如9.2.2实体类，就是ORM

10.2 DAO

概念：

• Data Access Object：数据访问对象
• DAO实现了业务逻辑与数据库访问相分离
• 对同一张表的所有操作封装在XXXDAOImp对象中
• 根据增删改查提供具体的方法（insert、update、delete、select、selectAll）

例如9.2.4

十一、Service层

什么是业务？

• 业务代表用户完成了一个业务功能，可以由一个或多个DAO的调用组成
• 软件所提供的一个功能叫业务

数据操作的复用：使用DAO

业务操作的复用：使用Service

十二、三层架构

三层架构是javaee规范中的推荐架构，传统意义上是分为表示层（UI）、业务逻辑层（BLL）、数据访问层（DAL）。在javaee的开发中，三层架构具体分为表示层（web层）、业务逻辑层（service）、数据访问层（dao层）

① web层：与客户端交互，包含获取用户请求，传递数据，封装数据，展示数据。

② service层：复杂的业务处理，包含各种实际的逻辑运算。

③ dao层：与数据库进行交互，与数据库相关的代码在此处实现。

十三、事务

什么是业务？

• 业务就是一个原子操作。是一个最小执行单元。由一个或多个SQL语句组成
• 在同一个事务当中，所有SQL语句都成功执行时，整个业务成功
• 有一个SQL语句执行失败，整个事务都执行失败

13.1 事务的基本使用

事务的使用：

• 在JDBC中，先获得Connection对象。
• 开始事务：conn.setAutoCommit(false)；
• 手动提交事务：conn.commit();
• 手动回滚事务：conn.rollback();

注意：Service中调用多次DAO操作。每一个业务功能都要控制事务

准备：这里模拟银行转账操作，提前建立一个 bank表，有id，account（卡号） ，money（余额）字段，至少添加两条数据

完整代码可见资料test2文件夹

13.1.1 未用事务

在不使用事务，出现异常时会造成转出账户减了钱，但是转入账户没有加钱的风险

public class AccountServiceIml implements AccountService {
    private AccountDAO accountDAO = new AccountDAOImpl();
    @Override
    public boolean transfer(String from, String to, int money) {
        Account fromAccount = accountDAO.findByAccount(from);
        //判断账户是否存在、账户金额是否足够、对方账户是否存在
        if (fromAccount != null){
            if (fromAccount.getMoney() > money){
                Account toAccount = accountDAO.findByAccount(to);
                if (toAccount == null){
                    System.out.println("对方账户不存在");
                }else{
                    //转账
                    //先减钱
                    accountDAO.outMoney(from,money);

                    //模拟转账时出错(此时就会出现转出账户减了钱，但是转入账户没收到)，需要使用事务
                    int a = 1/0;

                    //再加钱
                    accountDAO.inMoney(to,money);
                    return true;
                }
            }else{
                System.out.println("账户余额不足");
            }
        }else{
            System.out.println("转账账户不存在");
        }
        return false;
    }
}

13.1.2 使用事务

此处使用事务较为繁琐，可以参考13.2的解决方案

public class AccountServiceIml implements AccountService {
    private AccountDAO accountDAO = new AccountDAOImpl();
    @Override
    public boolean transfer(String from, String to, int money) {
        Account fromAccount = accountDAO.findByAccount(from);

        //判断账户是否存在、账户金额是否足够、对方账户是否存在
        if (fromAccount != null){
            if (fromAccount.getMoney() > money){
                Account toAccount = accountDAO.findByAccount(to);
                if (toAccount == null){
                    System.out.println("对方账户不存在");
                }else{

                    //启用事务需要获得Connection连接对象，且连接对象必须与转出方法和转入方法的对象相同，才能回滚
                    //所以需要在这里获取连接对象，然后将连接对象通过传参的方式传给转出方法和转入方法
                    Connection connection = null;

                    try{
                        //获取连接
                        connection = DBConnection.getConnection();
                        //开始事务
                        connection.setAutoCommit(false);

                        //转账
                        //先减钱
                        accountDAO.outMoney(from,money,connection);

                        //模拟转账时出错(此时就会出现转出账户减了钱，但是转入账户没收到)，需要使用事务
                        int a = 1/0;

                        //再加钱
                        accountDAO.inMoney(to,money,connection);
                        //没有异常就提交
                        connection.commit();
                        return true;
                    }catch (Exception e){

                        e.printStackTrace();
                        try {
                            //如果出现异常就回滚
                            connection.rollback();
                            connection.setAutoCommit(true);
                        } catch (SQLException throwables) {
                            throwables.printStackTrace();
                        }
                    }finally {
                        //关闭资源
                        if (connection != null){
                            try {
                                connection.close();
                            } catch (SQLException throwables) {
                                throwables.printStackTrace();
                            }
                        }
                    }

                }
            }else{
                System.out.println("账户余额不足");
            }
        }else{
            System.out.println("转账账户不存在");
        }
        return false;
    }
}

13.2 ThreadLocal【经典面试题】

上面使用事务的方案：

• 将Connection连接对象通过Service获取并传递给各个DAO方法

弊端：

• 如果使用传递Connection，容易造成接口污染（BadSmell）
• 定义接口是为了更容易更换实现，而将Connection定义在接口中，会污染当前接口

最佳方案：

• 可以将整个线程中（单线程）中，存储一个共享值（因为用户的转账操作一直都在一个线程中执行，所以可以定义一个共享值，共享值中存放Connection连接即可）
• 线程拥有一个类似Map的属性，键值对结构<ThreadLocal对象，值>

案例代码可见资料中test3

ThreadLocal使用场景：

• 维护JDBC的java.sql.Connection对象，因为每个线程都需要保持特定的Connection对象。
• Web开发时，有些信息需要从controller传到service传到dao，甚至传到util类。看起来非常不优雅，这时便可以使用ThreadLocal来优雅的实现。
• 包括线程不安全的工具类，比如Random、SimpleDateFormat等

//DBConnection代码
public class DBConnection {

    private final static Properties PROPERTIES = new Properties();
    private final static ThreadLocal<Connection> THREAD_LOCAL = new ThreadLocal<>();

    static {
        try {
            final InputStream inputStream = DBConnection.class.getResourceAsStream("/mysql.properties");
            PROPERTIES.load(inputStream);
            Class.forName(PROPERTIES.getProperty("jdbc.driver"));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws SQLException {
        //每次进来先判断是否存在共享值，如果有就直接使用，判断会为false，就直接返回该值
        Connection connection = THREAD_LOCAL.get();
        //判断是否有共享值或共享值是否已经关闭（只有第一次调用该方法才为true）
        if (connection == null || connection.isClosed()){
            //就建立一个连接，存到共享值中
            connection =  DriverManager.getConnection(PROPERTIES.getProperty("jdbc.url"),
                    PROPERTIES.getProperty("jdbc.username"),
                    PROPERTIES.getProperty("jdbc.password"));
            THREAD_LOCAL.set(connection);
        }
        //返回
        return connection;
    }

    //当转账执行结束后需要关闭且清空共享值ThreadLocal
    public static void close(){
        try{
            Connection connection = THREAD_LOCAL.get();
            if (connection != null && !connection.isClosed()){
                connection.close();
                THREAD_LOCAL.remove();
            }
        }catch (Exception e){
            e.printStackTrace();
        }

    }
}

Service核心代码

public class AccountServiceIml implements AccountService {
    private AccountDAO accountDAO = new AccountDAOImpl();
    @Override
    public boolean transfer(String from, String to, int money) throws SQLException {
        Account fromAccount = accountDAO.findByAccount(from);

        //判断账户是否存在、账户金额是否足够、对方账户是否存在
        if (fromAccount != null){
            if (fromAccount.getMoney() > money){
                Account toAccount = accountDAO.findByAccount(to);
                if (toAccount == null){
                    System.out.println("对方账户不存在");
                }else{

                    //启用事务需要获得Connection连接对象，且连接对象必须与转出方法和转入方法的对象相同，才能回滚
                    //所以需要在这里获取连接对象，然后将连接对象通过传参的方式传给转出方法和转入方法

                    try{
                        //开始事务
                        //直接使用DBConnection.getConnection()获取连接
                        //因为DBConnection.getConnection()是在一个ThreadLocal中获取共享值，都是使用相同的连接对象
                        DBConnection.getConnection().setAutoCommit(false);

                        //转账
                        //先减钱
                        accountDAO.outMoney(from,money);

                        //模拟转账时出错(此时就会出现转出账户减了钱，但是转入账户没收到)，需要使用事务
                        int a = 1/0;

                        //再加钱
                        accountDAO.inMoney(to,money);
                        //没有异常就提交
                        DBConnection.getConnection().commit();
                        return true;
                    }catch (Exception e){

                        e.printStackTrace();
                        try {
                            DBConnection.getConnection().rollback();
                            DBConnection.getConnection().setAutoCommit(true);
                        } catch (SQLException throwables) {
                            throwables.printStackTrace();
                        }
                    }finally {
                        //关闭所有资源和ThreadLocal
                        DBConnection.close();
                    }

                }
            }else{
                System.out.println("账户余额不足");
            }
        }else{
            System.out.println("转账账户不存在");
        }
        return false;
    }
}

十四、DAO的封装DaoUtils

在DAO层中，对数据库表的增、删、改、查操作存在代码冗余，可对其进行抽取封装DaoUtils工具类实现复用。

可见案例的17.2 中DAOUtils 代码

public class DAOUtils<T> {

    /**
     *
     * @param sql sql语句
     * @param args 传入替换sql占位符的参数
     * @return
     * @throws SQLException
     */
    //封装DAO增删改操作
    //所有类的增删改的通用的DAOutils
    public boolean update(String sql, Object... args) throws SQLException {
        //获取连接
        final PreparedStatement pstt = DBConnection.getConnection().prepareStatement(sql);
        if (args != null) {
            for (int i = 0; i < args.length; i++) {
                //从第一个开始设置值
                pstt.setObject(i + 1, args[i]);
            }
            return pstt.executeUpdate() > 0;
        }
        return false;
    }

}

十五、连接池

避免频繁创建和关闭连接

连接池有很多种：DBCP、C3P0、DRUID(阿里)

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf-8
username=root
password=123
#初始连接数量
initialSize=5
#最大连接数
maxActive=10
#最小空闲连接
minWait=5
#最大超时时间
maxWait=3000		

上面配置信息，key不能变，采用约定优于配置原则

约定优于配置：实现约定号key的名称，如果遵守约定，那么就无需配置，如果不遵守约定，就需要添加额外的配置

public class DBConnection {

    //创建ThreadLocal实现同一线程之间共享值，Properties加载配置文件，dataSource数据源
    private static final ThreadLocal<Connection> THREAD_LOCAL = new ThreadLocal();
    private static final Properties PROPERTIES = new Properties();
    private static DataSource dataSource;

    //加载配置文件以及将配置文件数据源赋值给dataSource（会加载驱动），
    static {
        try {
            final InputStream inputStream = DBConnection.class.getResourceAsStream("/druid.properties");
            PROPERTIES.load(inputStream);
            dataSource = DruidDataSourceFactory.createDataSource(PROPERTIES);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //提供一个获取数据源的方法（可能会用到）
    public static DataSource getDataSource(){
        return dataSource;
    }

    //获取连接对象
    //先判断ThreadLocal中是否存在连接对象，存在就直接返回，不存在就创建，使用数据源创建连接对象，将其存储到Thread共享值中
    public static Connection getConnection() throws SQLException {
        Connection connection = THREAD_LOCAL.get();

        if (connection == null || connection.isClosed()){
            connection = dataSource.getConnection();
            THREAD_LOCAL.set(connection);
        }
        return connection;
    }

    //关闭
    public static void close() throws SQLException {
        Connection connection = THREAD_LOCAL.get();
        if (connection != null && !connection.isClosed()){
            connection.close();
            THREAD_LOCAL.remove();
        }
    }
}

十六、DBUtils帮助类用法

使用第三方类库dbutils

• 当没有事务时，可以直接使用：

//将连接直接交给DBUtils，我们也不需要关闭连接，这种只适用于没有事务的场景
private QueryRunner runner = new QueryRunner(DBConnection.getDataSource());

• 当有事务时，需要使用

//不能把连接传给DBUtils，我们在每个方法执行时将连接传入，保证都在同一个线程中执行这些方法，需要关闭连接
private QueryRunner runner = new QueryRunner();

16.1 DBUtils使用

• 增删改等操作使用 update 方法，用法与我们自己写DBUtils一样，案例3 v3可见，案例v2是我们自己写的DBUtils

//例如：如下，返回值为影响的行数   
//Constants.COMMUNITY_DELETE 是我们在常量接口中写的sql语句，id为我们传入的参数
queryRunner.update(Constants.COMMUNITY_DELETE, id)

• 查操作根据返回值不同使用方法也不同（案例v3中有相关代码）
  • 注意：查询的结果名称必须与实例类属性一致，因此我们可以使用别名的方式来保证一致
  • 1. 当返回值为单行单列的数据时（例如统计一共多少人），传入参数需要 new ScalarHandler<>()
  • 2. 当返回值为一个对象时（例如根据一个主键查询一条数据），需要传入 new BeanHandler<>(指定实例对象.class) 底层也是基于反射实现的
  • 3. 当返回值为集合对象时（例如查询所有数据），需要传入 new BeanListHandler<>(指定实例对象.class) 底层使用了反射来实现ResoultSet转为实例对象，再存入集合

//当返回值为单行单列的数据时
//整型的返回值必须为long,如果是其他类型就将返回值设置为该类型，返回值类型设置错误就异常
public long selectCount() throws SQLException {
    return queryRunner.query(Constants.COMMYNITY_COUNT,new ScalarHandler<>());
}

//当方法返回值为一个对象时，使用 new BeanHandler<>(表映射的实例类.class)
//id为我们传入的参数，可以是多个也可以是一个
public Community findById(int id) throws SQLException {
    return queryRunner.query(Constants.COMMUNITY_FINDBYID, new BeanHandler<>(Community.class), id);
}

//当方法返回值为一个集合对象时，使用new BeanListHandler<>(表映射的实例类.class)
public List<Community> findAll() throws SQLException {
    return queryRunner.query(Constants.COMMUNITY_FINDALL, new BeanListHandler<>(Community.class));
}

注意：结果集的封装，使用反射的方式需要字段名和属性名一致，因此我们可以使用别名的方式来保证一致

select community_id as communityId, community_num as communityNum, community_name as communityName, area, belongs, person, establish from wymessage;

十七、案例

物业表实现增删改查（使用所学知识实现增删改查）

准备：物业表如下 资料中有该表，

• 表名为Wymessage

案例最终的文件目录

17.1 v1

资料-->案例-->demo1 属于为初始版本，未使用封装和工具，也没有创建业务层Service，待优化。

17.2 v2

资料-->案例-->demo2 属于优化版本，使用了DAOUtils封装，也没有创建业务层Service，未使用工具。

• v2比v1多了DAOUtils类和RowMapper接口，以及ConnectionDAOImpl类中的代码不同，其他代码一致

17.3 v3

资料-->案例-->demo3 属于最终版本，使用了第三方DAOUtils封装工具包，且建立了业务层Service

• 使用第三方工具包需要区分开有事务和没有事务时的代码，因为写法略有不同，代码中 CommunityDAOImpl.class、CommunityServiceImpl.class是没有事务的写法，代码中 CommunityDAOImpl2.class、CommunityServiceImpl2.class是有事务的写法
• 使用第三方工具包我们就不需要自己写DBUtils，我们只需要编写SQL语句以及实例类和方法名，直接使用工具包的方法传入SQL语句和参数，可以参考16.1的DBUtils使用