Java 中级02 jdbc小结

如何操作数据库

使用客户端工具访问数据库，需要手工建立连接，输入用户名和密码登录，编写 SQL 语句，点击
执行，查看操作结果（客户端软件工具里是结果表，java代码返回的是结果集或受影响行数）

JDBC(Java Database Connectivity)

1.1 什么是jdbc
Java数据库连接，（Java Database Connectivity，简称JDBC）是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口，提供了诸如查询和更新数据库中数据的方法。JDBC也是Sun Microsystems的商标。我们通常说的JDBC是面向关系型数据库的。
A.是java连接数据库的标准（规范），规范了访问数据库的接口
B.提供增删改查操作

1.2 MySQL数据库驱动（jar包）
mysql-connector-java-5.1.X 适用于 5.X 版本
mysql-connector-java-8.0.X 适用于 8.X版本
1.3 JDBC API
JDBC 是由多个接口和类进行功能实现。

1.4环境搭建（导包）
在项目下新建 lib 文件夹，用于存放 jar 文件。
将 mysql 驱动mysql-connector-java-5.1.X复制到项目的 lib 文件夹中。
选中 lib 文件夹右键 Add as Libraay，点击 OK。

二、JDBC开发步骤

例子

package com.qfedu.demo;

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

public class InsertDemo {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {

        //0. 加载数据库驱动（如果是旧版 MySQL 驱动，需要手动加载）
        //MySQL8.0+ 按如下方式
        Class.forName("com.mysql.cj.jdbc.Driver");
        //MySQL8.0 之前即MySQL5.0+，按如下方式
//        Class.forName("com.mysql.jdbc.Driver");
        //1. 首先和 MySQL 之间建立连接
        //第一个参数是连接地址
        //第二个参数是数据库用户名
        //第三个参数是数据库密码
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test01?serverTimezone=Asia/Shanghai", "root", "1234");
        //2. 准备 SQL，首先创建一个 Statement，将来由 Statement 去执行 SQL
        Statement statement = connection.createStatement();
        //执行 SQL，返回值是数据库受影响的行数
        int i = statement.executeUpdate("insert into user (username,address) values ('zhangsan','广州');");
        System.out.println("i = " + i);
        //3. 释放资源
        statement.close();
        connection.close();
    }
}

1.加载驱动
MySQL5.0+版 Class.forName(“com.mysql.jdbc.Driver”);
MySQL8.0+版 多个cj,Class.forName(“com.mysql.cj.jdbc.Driver”);
2.连接数据库
通过 DriverManager.getConnection(url,user,password) 获取数据库连接对象
URL:jdbc:mysql://localhost:3306/database
username:root
password:1234
URL(Uniform Resource Locator) 统一资源定位符：由协议、IP、端口、SID（程序实例名称）组
成
3.获取发送 SQL 的对象
通过 Connection 对象获得 Statement 对象，用于对数据库进行通用访问。

Statement statement = conn.createStatement();

4.执行SQL语句
执行 SQL 语句并接收执行结果。

String sql ="INSERT INTO t_jobs(JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY)
VALUES('JAVA_Le','JAVA_Lecturer',4000,10000);";
int result = statement.executeUpdate(sql);//执行SQL语句并接收结果

注意：在编写 DML 语句时，一定要注意字符串参数的符号是单引号 ‘值’
DML 语句：增删改时，返回受影响行数（int 类型）。
DQL 语句：查询时，返回结果数据(ResultSet 结果集)。
5.处理结果

if(result == 1){
System.out.println("Success");
}

受影响行数：逻辑判断、方法返回。
查询结果集：迭代、依次获取。
6.释放资源
遵循先开后关原则，释放所使用到的资源对象。

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

三、ResultSet (结果集)

在执行查询 SQL 后，存放查询到的结果集数据。
3.1 接受结果集
ResultSet rs = statement.executeQuery(sql);

ResultSet rs= statement.executeQuery("SELECT * FROM user;");

3.2 遍历结果集
ResultSet 以表（table）结构进行临时结果的存储，需要通过 JDBC API 将其中数据进行依次获
取。
数据行指针：初始位置在第一行数据前，每调用一次 boolean next()方法ResultSet 的指针向
下移动一行，结果为 true，表示当前行有数据。
rs.getXxx(整数);代表根据列的编号顺序获得，从 1 开始。
rs.getXxx(“列名”);代表根据列名获得。

package com.qfedu.demo;

import java.sql.*;
import java.util.Scanner;

public class SelectDemo {
    /**
     * 根据用户名/密码去查询一个用户
     *
     * @param args
     */
    public static void main(String[] args) throws SQLException {
        //1. 建立数据库连接
        Connection connection = DriverManager.getConnection("jdbc:mysql:///test01?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8", "root", "1234");
        //2. 准备 SQL
        Statement statement = connection.createStatement();
        Scanner scanner = new Scanner(System.in);

        //用户在控制台输入用户密码
        System.out.println("please enter username：");
        String username = scanner.next();
        System.out.println("please enter password:");
        String password = scanner.next();
        //3. 执行查询操作
        //这里返回一个 ResultSet 结果集，可以将之理解为一个表格
        // rs 相当于 sql 执行完成之后的表格
        String sql = "select * from user where username='" + username + "' and password='" + password + "';";
        System.out.println("sql = " + sql);
        ResultSet rs = statement.executeQuery(sql);
        //表示将游标向下移动一行，返回值为 true 表示移动之后，有数据，否则表示移动之后无数据，即表格已经遍历完成
        //第一次挪动之后，游标指向 id 为 1 的记录
        boolean next = rs.next();
        //如果返回 true，表示 rs 中是有数据的
        if (next) {
            //getInt 表示读取一个 int 类型的数据，参数 1 表示第一列
            int id = rs.getInt(1);
            //getString 表示读取一个字符串，参数表示列名，一般建议使用列名
            String uname = rs.getString("username");
            String address = rs.getString("address");
            String passwd = rs.getString("password");
            System.out.println("id = " + id);
            System.out.println("username = " + uname);
            System.out.println("address = " + address);
            System.out.println("password = " + passwd);
            System.out.println("login success");
        } else {
            System.out.println("login error");
        }
        //3. 释放资源
        rs.close();
        statement.close();
        connection.close();
    }
}

四、常见错误

java.lang.ClassNotFoundException：找不到类（类名书写错误、没有导入驱动jar包）
java.sql.SQLException：与sql语句相关的错误 （约束错误、表名列名书写错误） 建议：在
客户端工具中测试SQL语句之后再粘贴在代码中
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 原
因：列值Sting类型没有加单引号
Duplicate entry ‘1’ for key ‘PRIMARY’ 原因，主键值已存在或混乱，更改主键值或清空表
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column
‘password’ in
原因：可能输入的值的类型不对，确定是否插入的元素时对应的值的类型正确

五、SQL 注入问题

5.1 什么是SQL 注入
用户输入的数据中有 SQL 关键字或语法并且参与了 SQL 语句的编译，导致 SQL 语句编译后的条件含义为 true，一直得到正确的结果。这种现象称为 SQL 注入。
例如：拼接的sql语句输入的username和password 带有单引号或者# 号，Statement statement = connection.createStatement(); ResultSet rs = statement.executeQuery(sql); 其是将sql字符串原封不动的发送到MySQL软件并执行，# 在软件中表注释，多的单引号会造成值识别错误
5.2 如何避免SQL注入
由于编写的 SQL 语句是在用户输入数据，整合后再进行编译。所以为了避免 SQL 注入的问题，我们要使 SQL 语句在用户输入数据前就已进行编译成完整的 SQL 语句，再进行填充数据。
即使用PreparedStatement 预编译

六、PreparedStatement 预编译

PreparedStatement 继承了 Statement 接口，执行 SQL 语句的方法无异。
作用：
预编译SQL 语句，效率高。
安全，避免SQL注入 。
可以动态的填充数据，执行多个同构的 SQL 语句。
语法案例：
JDBC中的所有参数都由 ？符号占位，这被称为参数标记。在执行SQL语句之前，必须为每
个参数提供值。
pstmt.setXxx(下标,值) 参数下标从 1 开始，为指定参数下标绑定值，Xxx对应值的类型

//1.预编译 SQL 语句
PreparedStatement pstmt = conn.prepareStatement("select * from user where
username=? and password=?");
//2.为参数下标赋值
pstmt.setString(1,username);
pstmt.setString(2,password);

package com.qfedu.demo;

import java.sql.*;
import java.util.Scanner;

public class PsDemo {
    public static void main(String[] args) throws SQLException {
        //1. 获取数据库连接
        Connection connection = DriverManager.getConnection("jdbc:mysql:///test01?serverTimezone=Asia/Shanghai", "root", "1234");
        //使用 PreparedStatement，会自动的进行 SQL 的预编译，同时也能防止 SQL 注入的问题
        //这里先使用 ? 做占位符，将来在这个地方填充参数
        PreparedStatement ps = connection.prepareStatement("select * from user where username=? and password=?;");
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入用户名：");
        String username = scanner.next();
        System.out.println("请输入用户密码：");
        String password = scanner.next();
        //将用户输入的参数，设置给 ps
        //1 表示第一个参数，即第一个 ?
        ps.setString(1, username);
        ps.setString(2, password);
        //接下来执行查询
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            System.out.println("登录成功");
        }else{
            System.out.println("登录失败");
        }
        rs.close();
        ps.close();
        connection.close();
    }
}

七、封装增删改查方法

创建model包下的User类

package model;

public class User {
    private Integer id;
    private String username;
    private String address;
    private String password;

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", address='" + address + '\'' +
                ", password='" + password + '\'' +
                '}';
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }
}

创建dao包下的UserDao

package dao;

import model.DBUtils;
import model.User;

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

//封装对user 的增删改查
public class UserDao {
    //添加用户
    public Integer addUser(User user) {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            //获取数据库来连接
            con = DBUtils.getConnection();
            //添加用户的sql
            ps = con.prepareStatement("insert into user(username,address,password)values (?,?,?);");
            //设置参数
            ps.setString(1, user.getUsername());
            ps.setString(2, user.getAddress());
            ps.setString(3, user.getPassword());
            //执行sql
            int i = ps.executeUpdate();
            return i;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBUtils.close(ps);
            DBUtils.close(con);
        }
        return 0;
    }

    //删除用户
    public Integer deleteUserById(Integer id) {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = DBUtils.getConnection();
            ps = con.prepareStatement("delete from user where id=?;");
            ps.setInt(1, id);
            int i = ps.executeUpdate();
            return i;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBUtils.close(ps);
            DBUtils.close(con);
        }
        return 0;
    }

    //根据id 更新用户名
    public Integer updateUsernameById(User user) {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = DBUtils.getConnection();
            ps = con.prepareStatement("update user set username =? where id=?;");
            ps.setString(1, user.getUsername());
            ps.setInt(2, user.getId());
            int i = ps.executeUpdate();
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(ps);
            DBUtils.close(con);
        }
        return 0;
    }

    //查询所有 快捷键shift +alt+ 上下键 上下移动代码
    public List<User> getAllUsers() {
        List<User> users = new ArrayList<>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = DBUtils.getConnection();
            ps = con.prepareStatement("select * from user;");
            rs = ps.executeQuery();
            while (rs.next()) {
                User u = new User();
                int id = rs.getInt("id");
                String username = rs.getString("username");
                String address = rs.getString("address");
                String password = rs.getString("password");
                u.setId(id);
                u.setUsername(username);
                u.setPassword(password);
                u.setAddress(address);
                users.add(u);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBUtils.close(rs);
            DBUtils.close(ps);
            DBUtils.close(con);
        }
        return users;
    }
}

八、主键回填

代码不全 主要是 获取主键值给外键设值
先预编译sql语句，加上Statement.RETURN_GENERATED_KEYS参数，

PreparedStatement ps=con.prepareStatement("insert into author(name,age) values(?,?);",Statement.RETURN_GENERATED_KEYS);

补充完sql语句参数，然后执行语句
int i =ps.executeUpdate();
得到执行语句后的结果，取出id，然后将id的值插入其他表

           //获取刚刚插入记录的id
            //这个rs中，只有一行一列
            rs=ps.getGeneratedKeys();
            if(rs.next()){
                //这个就是刚刚添加的作者id
                int aid=rs.getInt(1);
                //添加图书的信息
                ps=con.prepareStatement("insert into book(name,aid) values (?,?);");
                ps.setString(1,"三国演义");
                ps.setInt(2,aid);
                //添加图书
                ps.executeUpdate();
            }

部分代码

package model;

import java.sql.*;
public class BookDemo {
    public static void main(String[] args) {
        Connection con =null;
        ResultSet rs = null;
        PreparedStatement ps = null;
        try {
            con = DBUtils.getConnection();
            //添加作者
            //Statement.RETURN_GENERATED_KEYS 表示返回添加完成后生成的 id
            ps= con.prepareStatement("insert into author(name,age) values(?,?);", Statement.RETURN_GENERATED_KEYS);
            ps.setString(1,"罗贯中");
            ps.setInt(2,80);
            //添加作者信息
            //注意下面两行顺序不能写反了，必须要先添加一条记录，然后才能获取到 id
            int i =ps.executeUpdate();
            //获取刚刚插入记录的id
            //这个rs中，只有一行一列
            rs=ps.getGeneratedKeys();
            if(rs.next()){
                //这个就是刚刚添加的作者id
                int aid=rs.getInt(1);
                //添加图书的信息
                ps=con.prepareStatement("insert into book(name,aid) values (?,?);");
                ps.setString(1,"三国演义");
                ps.setInt(2,aid);
                //添加图书
                ps.executeUpdate();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBUtils.close(rs);
            DBUtils.close(ps);
            DBUtils.close(con);
        }

    }
}

九、DBUtils(封装工具类)

在实际JDBC的使用中，存在着大量的重复代码:例如连接数据库、关闭数据库等这些操作！
我们需要把传统的JDBC代码进行重构，抽取出通用的JDBC工具类！以后连接任何数据库、释
放资源都可以使用这个工具类。

代码
一、工具类封装实现（前面代码需要的类）

package com.qfedu.demo;

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

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

/**
 * 池化技术
 * <p>
 * 用一个连接池，里边可以存放很多备用的 Connection，当需要用的时候，就拿一个出来用，用完了返回去，将来还能复用
 * <p>
 * <p>
 * <p>
 * DBCP
 * C3P0
 * <p>
 * Druid
 */
public class DBUtils {

    private static DruidDataSource ds = null;

    static {
        //创建一个数据库连接池
        ds = new DruidDataSource();
        //设置数据库连接
        ds.setUrl("jdbc:mysql:///test01?serverTimezone=Asia/Shanghai");
        //设置数据库连接用户名
        ds.setUsername("root");
        //设置数据库密码
        ds.setPassword("1234");
        //初始化连接数
        //当 datasource 创建成功后，默认里边有 100 个可用的 connection
        ds.setInitialSize(100);
        //最小空闲数，如果当前没有人使用 connection，连接池中也会随时保证有 10 个 connection 待命
        ds.setMinIdle(10);
        //最大等待时间，当所有的 connection 都被使用时，新的操作只能等待，等待时间超过 1 分钟，就会抛出异常
        ds.setMaxWait(60000);
        //连接池最大连接数
        ds.setMaxActive(200);
    }

    public static Connection getConnection() {
        //从数据库连接池中获取一个 Connection
        //DruidPooledConnection 实现了 Connection 接口
        DruidPooledConnection connection = null;
        try {
            connection = ds.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }
//    public static Connection getConnection() {
//        try {
//            return DriverManager.getConnection("jdbc:mysql:///test01?serverTimezone=Asia/Shanghai", "root", "1234");
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//        return null;
//    }

    public static void close(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void close(PreparedStatement ps) {
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void close(Connection con) {
        if (con != null) {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

二、跨平台工具类实现加Druid连接池 (即通过配置文件 properties)
在src 目录下新建db.properties文件
properties文件底层是hashtable 和hashmap类似

#配置驱动名称 也可以不配置会自动读取
# 如果配置的话，mysql8.0+,com.mysql.cj.jdbc.Driver
# mysql8.0 之前 com.mysql.jdbc.Driver
driverClassName =com.mysql.jdbc.Driver
#配置url地址
url=jdbc:mysql:///test01?severTimezone=Asia/Shanghai
#配置mysql的用户名
username=root
#配置mysql的密码
password =root
#连接池中初始化的连接数
initialSize=100
#最大连接数
maxActive=200
#最小空闲数
minIdle=10
#最大等待时间
maxWait=60000

使用了alibaba 的DruidDataSourceFactory 连接池类，用了ThreadLocal

ThreadLocal

ThreadLocal 特点：在哪个线程中存的数据，就在哪个线程中读取，换一个线程则读取不到
ThreadLocal 中只能保存一个对象，所以他的方法比较简单，只有三个:
set：存一个对象到 ThreadLocal中 get：读取一个对象 remove：移除对象
封装工具类流程
1.加载db.properties 配置文件，将数据读取到properties对象中
读取地址 有/表示去classpath的根目录下查找，classpath 就是 src，没有/就是在当前类所处的包的目录下查找
properties文件底层是hashtable 和hashmap类似，即和map的方法有些类似
load方法类似手动向properties 中put了7条数据，put完后，可以有get()读取 例：properties.get(“password”)
根据properties 配置文件，创建一个datasource数据源
2.首先从ThreadLocal 中获取Connection,保证同一个线程，拿到的始终是同一个Connection
a.拿到了Connection 说明之前已经有方法来获取过Connection了，即不为null，直接返回 connection 即可
b.如果拿到null 说明在当前线程中，是第一次来拿Connection ，就从连接池中拿一个 Connection，存入 ThreadLocal，并返回；
这样就能确保同一个线程中，拿到的始终是同一个 Connection
3.当将连接放回连接池的时候，需要从 ThreadLocal 中移除该连接
否则，由于 threadlocal 只有当前线程能够操作它，当当前请求结束的时候，当前线程的使命就完成了，下个请求来又是一个新线程，此时就没人能够将这个 threadlocal 中的数据移除了

package utils;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;

public class DBUtils {
    private static DataSource ds =null;
    private static final Properties PROPERTIES = new Properties();
    /**ThreadLocal 特点：在哪个线程中存的数据，就在哪个线程中读取，换一个线程则读取不到
     * ThreadLocal 中只能保存一个对象，所以他的方法比较简单，只有三个:
     * set：存一个对象到 ThreadLocal中 get：读取一个对象 remove：移除对象
     */
    private static final ThreadLocal<Connection> THREAD_LOCAL=new ThreadLocal<>();
    static {
        try {
            //加载db.properties 配置文件，将数据读取到properties对象中
            //读取地址 有/表示去classpath的根目录下查找，classpath 就是 src，没有/就是在当前类所处的包的目录下查找
            //properties文件底层是hashtable 和hashmap类似
            // load方法类似手动向properties 中put了7条数据，put完后，可以有get()读取 例：properties.get("password")
            PROPERTIES.load(DBUtils.class.getResourceAsStream("/db.properties"));
            //根据properties 配置文件，创建一个datasource数据源
            ds= DruidDataSourceFactory.createDataSource(PROPERTIES);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    /**
     * 同一个线程。拿到的始终是同一个Connection
     */
    public static Connection getConnection(){
        /*首先从ThreadLocal 中获取Connection
        1.拿到了Connection 说明之前已经有方法来获取过Connection了
        2.拿到null 说明在当前线程中，是第一次来拿Connection
        * */
        Connection connection = THREAD_LOCAL.get();
        if(connection==null){
            //拿到了 null：说明在当前线程中，是第一次来拿  Connection
            try {
                //从连接池中拿一个 Connection，存入 ThreadLocal，并返回
                Connection con = ds.getConnection();
                THREAD_LOCAL.set(con);
                return con;
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        //不为null，直接返回 connection 即可，这样就能确保同一个线程中，拿到的始终是同一个 Connection
        return connection;
    }

   /* public static Connection getConnection(){
        try {
            return ds.getConnection();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }*/
    /*public static Connection getConnection(){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            return DriverManager.getConnection("jdbc:mysql:///test01?serverTimezone=Asia/Shanghai","root","root");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }*/
    public static void close (ResultSet rs){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
    public static void close (Connection con){
        if(con!=null){
            try {
                con.close();
                /**
                 * 当将连接放回连接池的时候，需要从 ThreadLocal 中移除该连接
                 * 否则，由于 threadlocal 只有当前线程能够操作它，当当前请求结束的时候，当前线程的使命就完成了，下个请求来又是一个新线程，此时就没人能够将这个 threadlocal 中的数据移除了
                 */
                THREAD_LOCAL.remove();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
    public static void close (PreparedStatement rs){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

Druid连接池 ： 在程序初始化时，预先创建指定数量的数据库连接对象存储在池中。当需要连接数据库时，从连接
池中取出现有连接；使用完毕后，也不会进行关闭，而是放回池中，实现复用，节省资源。就相当于一个线程池
要使用DButils需要导包
druid-1.2.8.jar
mysql-connector-java-5.1.27.jar

十、事务

在service层使用，try-c-f其使用流程
1.获取一个数据库连接
2.开启事务
conn.setAutoCommit(false);//true 等价于 1，false 等价于 0
3.调用并执行dao类的方法
4.提交业务
conn.commit();//手动提交事务
5.执行dao类的方法出错，回滚事务
conn.rollback();//手动回滚事务
6.关闭连接
DBUtils.close(con);
例子
dao包下的类

package dao;

import utils.DBUtils;

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

public class AccountDao {
    public Integer addMoney(String username, Double money) throws SQLException {
        Connection con =null;
        PreparedStatement ps = null;
        con =DBUtils.getConnection();
        //给指定的账户加钱
        //注意，dao 层的异常不要自己捕获，有异常直接抛出给 service 层，这样方便 service 层去处理事务。
        ps = con.prepareStatement("update account set money=money+? where username=?");
        ps.setDouble(1, money);
        ps.setString(2, username);
        //执行更新操作
        int i = ps.executeUpdate();
        DBUtils.close(ps);
        return i;
    }
    //从指定的账户减钱
    public Integer minMoney(String username, Double money) throws SQLException {
        Connection con =null;
        PreparedStatement ps = null;
        con =DBUtils.getConnection();
        ps = con.prepareStatement("update account set money=money-? where username=?");
        ps.setDouble(1, money);
        ps.setString(2, username);
        int i = ps.executeUpdate();
        DBUtils.close(ps);
        return i;
    }

}

model包下的类

package model;

import service.AccountService;

public class AccountDemo {
    public static void main(String[] args) {
        AccountService accountService=new AccountService();
        accountService.transferMoney("张三","李四",50.0);
    }
}

service下的类

package service;

import dao.AccountDao;
import utils.DBUtils;

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

public class AccountService {

        AccountDao accountDao=new AccountDao();
        //转账的业务方法
        //一个转账操作，从张三账户转50到李四账户
        //我们希望minMoney 和addMoney 具备原子性，即两个同时成功或者失败
        //JDBC中 默认情况下，因为是有事物的，事物会自动提交或者回滚，所谓的开启事务，其实就是关闭JDBC默认的事务
        public void transferMoney(String from,String to,Double money){
            Connection con = DBUtils.getConnection();
            try {
                //注意，由于事务是在Connection 上开启的，所以需要确保当前事务中的所有JDBC 连接是同一个Connection，只有是同一个Connection，才能确保所有的数据库操作同时提交或者同时回滚
                //这句就是开启事务
                con.setAutoCommit(false);
                //调用并执行dao类的方法
                accountDao.minMoney(from,money);
                int i=1/0;
                accountDao.addMoney(to,money);
                //提交事务
                con.commit();

            } catch (SQLException e) {
                e.printStackTrace();
                //如果到这里，说明上面的执行出现异常了 =，那么此时应该回滚
                try {
                    con.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            } finally {
                DBUtils.close(con);
            }
        }

}

utils包下的类使用封装DbUtils 第二种方式，properties是在src目录下，DBUtils是在utils包下，导相关的包加依赖在lib目录下

DAO层 (Data Access Object 数据访问对象 )

DAO层 实现了业务逻辑与数据库访问相分离,dao层就是具体写sql语句的地方，一般一个sql语句对应一个方法

service业务逻辑层

业务 代表用户完成的一个业务功能，可以由一个或多个DAO 的调用组成。就是写功能，调用需要的DAO类的方法，执行DAO类里方法的sql语句并对返回的结果进行处理

三层架构

1.什么是三层
表示层：命名：XXXView 职责：收集用户的数据和需求、展示数据。
业务逻辑层：命名：XXXServiceImpl 职责：数据加工处理、调用DAO完成业务实现、控制事务。
数据访问层：命名：XXXDaoImpl 职责：向业务层提供数据，将业务层加工后的数据同步到数据库。

2.三层架构项目搭建（按开发步骤）
utils 存放工具类（DBUtils）
entity 存放实体类（Person）
dao 存放 DAO 接口（PersonDao）
impl 存放 DAO 接口实现类（PersonDaoImpl）
service 存放 service 接口（PersonService）
impl 存放 service 接口实现类（PersonServiceImpl）
view 存放程序启动类（main）
程序设计时，考虑易修改、易扩展，为Service层和DAO层设计接口，便于未来更换实现类

简化查询操作

创建一个 queryrunner 对象，增删改查操作 都通过这个对象完成
构造方法只有一个数据源参数，这个参数可传可不传
传：以后的增删改查就不需要传递数据源或者 Connection（一般都传，方法里就不需要传）

private QueryRunner queryRunner =new QueryRunner(DBUtils.getDs());

不传： 以后的增删改查 就需要传递Connection
如果不传，方法也没传就会出现数据库连接错误SQLException: QueryRunner requires a DataSource to be invoked in this way, or a Connection should be passed in
如果查询的结果是一行一列，那么可以使用ScalarHandler 自动将这一行一列读取出来，不是一行一列的话用 new ResultSetHandler<List< User>>() {} 自己手动处理结果集
select count(*) from user count()函数 查找表一共有多少行
如果数据库中的列名 和Java对象中的get/set 方法推断出来的属性名一一对应的话(与写属性的顺序无关，只要数据表列名 和属性名相等就行),那么有一个简写的类可以直接使用，new BeanHandler<>(User.class)或则和 BeanListHandler <>（model包下的实体类.class）分别表示返回的是一个User对象 ，list集合
增删改，用 queryRunner.update方法，参数是sql语句，补充sql的参数
查，用queryRunner.query 方法，参数是sql语句，处理结果集，补充sql的参数
当需要事务的时候，在首位多加一个连接池参数
看代码

package dao;

import model.User;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import utils.DBUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDao {
    /**
     * 创建一个 queryrunner 对象，增删改查操作 都通过这个对象完成
     * 构造方法只有一个数据源参数，这个参数可传可不传
     * 传：以后的增删改查就不需要传递数据源或者 Connection
     *不传： 以后的增删改查 就需要传递Connection
     */
    private QueryRunner queryRunner =new QueryRunner(DBUtils.getDs());
    //特殊情况简化 queryRunner.query 里的参数
    public Long getCount() throws SQLException {
        /**
         * 如果查询的结果是一行一列，那么可以使用ScalarHandler 自动将这一行一列读取出来
         * select count(*) from user count()函数 查找表一共有多少行
         */
        Long count = queryRunner.query("select count(*) from user", new ScalarHandler<Long>());
        return count;

    }

    public List<User> getAllUsers2() throws SQLException {
        return queryRunner.query("select * from user",new BeanListHandler<>(User.class));
        //BeanListHandler 表示返回的是一个list集合
    }

    /**
     * 如果数据库中的列名 和Java对象中的get/set 方法推断出来的属性名一一对应的话，那么有一个简写的类可以直接使用
     * @return
     * @throws SQLException
     */
    public User getUserById2(Integer id) throws SQLException {
        /**
         * 注意这里的第二个参数，是由系统提供的
         * 如果数据库查询结果的列名和Java对象中的get/set 方法推断出来的属性名是一一对应的，那么可以自动映射
         * 与写属性的顺序无关，只要列明 和属性名相等就行
         */
        return queryRunner.query("select * from user where id=?",new BeanHandler<>(User.class),id);
    }


    public List<User> getAllUsers() throws SQLException {
        List<User> list =queryRunner.query("select * from user", new ResultSetHandler<List<User>>() {
            @Override
            public List<User> handle(ResultSet resultSet) throws SQLException {
                List<User> users =new ArrayList<>();
                while (resultSet.next()){
                    User u =new User();
                    int id = resultSet.getInt("id");
                    String username = resultSet.getString("username");
                    String password = resultSet.getString("password");
                    String address = resultSet.getString("address");
                    u.setId(id);
                    u.setUsername(username);
                    u.setPassword(password);
                    u.setAddress(address);
                    users.add(u);
                }
                return users;
            }
        });
        return list;
    }

    /**
     * 根据id 查询一个用户对象
     */
    public User getUserById(Integer id) throws SQLException {
        /**
         * 查询用户
         * 查询用户的sql
         * resultsethandler 结果处理器
         */
        User user = queryRunner.query("select * from user where id=?;", new ResultSetHandler<User>() {
            /**
             * 自己在这里手动处理查询结果集的映射
             * @param resultSet 这个就是查询结果集
             * @return
             * @throws SQLException
             */
            @Override
            public User handle(ResultSet resultSet) throws SQLException {
                User u = new User();
                //由于是根据id查询的，所以结果集 只有一行，用if即可
                if (resultSet.next()) {
                    int id = resultSet.getInt("id");
                    String username = resultSet.getString("username");
                    String address = resultSet.getString("address");
                    String password = resultSet.getString("password");
                    u.setId(id);
                    u.setUsername(username);
                    u.setPassword(password);
                    u.setAddress(address);
                }
                return u;
            }
        }, id);
        return user;

    }
    /**
     * 根据id 删除一条记录
     */
    public Integer deleteUserById(Integer id) throws SQLException {
        return queryRunner.update("delete from user where id=?;",id);
    }
    /**
     * 根据 id 修改用户名
     * @param username
     * @param id
     * @return
     */
    public Integer updateUsernameById(String username, Integer id) throws SQLException {
        return queryRunner.update("update user set username=? where id=?", username, id);
    }
    /**
     * 添加用户
     */
    public Integer addUser(User user) throws SQLException {
        /*
        * update 方法可以做增删改
        * 1.执行的SQL,和jdbc 中一样，用？做占位符
        * 2. 可变长度的参数，和sql 中的占位符一一对应
        * f返回值表示受影响的行数*/
        int update = queryRunner.update("insert into user(username,address,password) values(?,?,?)",user.getUsername(),user.getAddress(),user.getPassword());
        return update;
    }
}