2_JDBC

一. 引言

1.1 如何操作数据库

• 使用客户端工具访问数据库, 需要手工建立连接, 输入用户名和密码登陆, 编写SQL语句, 点击执行, 查看操作结果(结果集或受行数影响)

1.2 实际开发中, 会采用客户端操作数据库吗?

• 在实际开发中, 当用户的数据发生改变时, 不可能通过客户端操作执行SQL语句, 因为操作量过大, 无法保证效率和正确性

二. JDBC

2.1 什么是JDBC?

• JDBC(Java Database Connectivity) Java连接数据库, 可以使用Java语言连接数据库完成CRUD操作

2.2 JDBC核心思想

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

2.2.1 MySQL数据库驱动

• mysql-connector-java-5.1.X-bin.jar: 适用于5.X版本
• mysql-connector-java-8.0.X-bin.jar: 适用于8.X版本
• 下载链接:

2.2.2 JDBC API

• JDBC是由多个接口和类进行功能实现

类型	权限定名	简介
class	java.sql.DriverManager	管理多个数据库驱动类, 提供了获取数据库连接的方法
interface	java.sql.Connection	代表一个数据库连接(当Connection不是null时, 表示已连接数据库)
interface	java.sql.Statement	发送SQL语句到数据库工具
interface	java.sql.ResultSet	保存SQL查询语句的结果数据(结果集)
class	java.sql.SQLException	处理数据库应用程序时所发生的异常

2.3 环境搭建

1. 在项目下新建lib文件夹,用于存放jar文件
2. 将mysql驱动mysql-connector-java-5.1.X-bin.jar复制到项目中的lib文件夹中
3. 选中lib文件夹右键Add as Library, 点击OK

三. JDBC开发步骤[重点]

3.1 注册驱动

• 使用class.forName("com.mysql.jdbc.Driver");手动加载字节码文件到 JVM 中

Class.forName("com.mysql.jdbc.Driver");//加载驱动

3.2 连接数据库

• 通过DriverManager.getConection(url, user, password)获取数据库连接对象
  • URL: jdbc: msql: //localhost: 3306/database
  • username: root
  • password: root

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/database?useUnicode=true&characterEncoding=utf8", "root", "root");

• URL(Union Resource Location) 统一资源定位符: 由协议, IP, 端口, SID(程序实例名称)组成

3.3 获取发送SQL的对象

• 通过Connection对象获得Statement对象, 用于对数据库进行通用访问

Statement statement = conn.createStatement();

3.4 执行SQL语句

• 执行SQL语句并接收执行结果

String sql = "insert into t_jobs(job_id,job_title,min_salary,max_salary) values('JAVA_Mgr','JAVA_Manager',4000,10000)";

int result = statement.executeUpdate(sql);//执行SQL语句并接收结果

• 注意: 在编写DML语句时, 一定要注意字符串参数的符号是单引号'值'
• DML语句: 增删改时, 返回受影响的行数(int 类型)
• DQL语句: 查询时, 返回结果数据(ResultSet结果集)

3.5 处理结果

• 接收处理操作结果

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

• 受影响行数: 逻辑判断, 方法返回
• 查询结果集: 迭代, 依次获取

3.6 释放资源

• 遵循先开后关原则,释放所使用到的资源对象

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

3.7 综合案例

• 整合以上核心六步, 实现在数据库表删除一条数据

public class DeleteJDBC {

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

        //1. 加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2. 获得连接对象
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/companydb", "root", "root");
        //3. 获得执行SQL的对象
        Statement statement = conn.createStatement();
        //4. 执行SQL语句, 并接收结果
        int result = statement.executeUpdate("delete from t_jobs where job_id = 'JAVA_Mgr'");
        //5. 处理结果
        if (result == 1) {
            System.out.println("删除成功!");
        }else{
            System.out.println("删除失败!");
        }
        //6. 释放资源
        statement.close();
        conn.close();
    }
}

四. ResultSet(结果集)

• 在执行查询SQL后,存放查询到的结果数据集

4.1 接收结果集

• ResultSet rs = statement.executeQuery(sql);

ResultSet rs = statement.executeQuery("select * from t_employees");

4.2 遍历ResultSet中的数据

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

boolean next() throws SQLException //判断 rs 的结果集中 下一行是否存在数据

4.2.1 遍历方法

int getInt(int columnIndex) throws SQLException //获得当前行第N列的int值
int getInt(String columnLabel) throws SQLException //获得当前行columnLabel列的int值
    
double getDouble(int columnIndex) throws SQLException //获得当前行第N列的double值
double getDouble(String columnLabel) throws SQLException //获得当前行columnLabel列的double值
    
String getString(int columnIndex) throws SQLException //获得当前行第N列的String值
String getString(String columnLabel) throws SQLException //获得当前行columnLabel列的String值
    

• 注意: 列的编号从1开始

4.3 综合案例

4.3.1 根据列的名称获取

public class QueryJdbc {

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

        //1. 注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2. 获取连接对象
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/companydb", "root","root");
        //3. 获取执行SQL的对象
        Statement statement = conn.createStatement();
        //4. 执行SQL语句,接收结果
        ResultSet resultSet = statement.executeQuery("select * from t_jobs");
        //5. 处理结果
        while (resultSet.next()) {
            //对当前行每列数据进行获取, 根据列的编号
            String job_id = resultSet.getString("job_id");
            String job_title = resultSet.getString("job_title");
            String min_salary = resultSet.getString("min_salary");
            String max_salary = resultSet.getString("max_salary");
            System.out.println(job_id+"\t"+job_title+"\t"+min_salary+"\t"+max_salary);
        }
        //6. 释放资源
        resultSet.close();
        statement.close();
        conn.close();
    }
}

4.3.2根据列的编号获取

public class QueryJdbc2 {

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

        //1. 注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2. 获取连接对象
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/companydb", "root","root");
        //3. 获取执行SQL的对象
        Statement statement = conn.createStatement();
        //4. 执行SQL语句,接收结果
        ResultSet resultSet = statement.executeQuery("select * from t_jobs");
        //5. 处理结果
        while (resultSet.next()) { //判断下一行是否有数据
            //对当前行每列数据进行获取, 根据列的编号
            String job_id = resultSet.getString(1);
            String job_title = resultSet.getString(2);
            String min_salary = resultSet.getString(3);
            String max_salary = resultSet.getString(3);
            System.out.println(job_id+"\t"+job_title+"\t"+min_salary+"\t"+max_salary);
        }
        //6. 释放资源
        resultSet.close();
        statement.close();
        conn.close();
    }
}

五. 常见错误

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

六. 综合案例[登陆]

6.1 创建表

• 创建一张用户表user
  • id, 主键, 自动增长
  • 用户名, 字符串类型, 唯一, 非空
  • 密码, 字符串类型, 非空
  • 手机号码. 字符串类型
• 插入;两条测试语句

CREATE TABLE users(
	id INT PRIMARY KEY AUTO_INCREMENT,
	username VARCHAR(18) UNIQUE NOT NULL,
	`password` VARCHAR(18) NOT NULL,
	phone VARCHAR(11)
)CHARSET=utf8;

INSERT INTO users(username,`password`,phone) VALUES('zhangsan','123','12345678901'); 
INSERT INTO users(username,`password`,phone) VALUES('lisi','321','12345678902');

6.2 实现登录

• 用户通过控制台输入用户名和密码
• 将用户输入的用户名和密码作为条件, 编写查询SQL语句
• 如果该用户存在, 提示登录成功, 反之提示失败

public class LoginJdbc {
    public static void main(String[] args) throws Exception{
        Scanner input = new Scanner(System.in);
        System.out.println("请输入用户名: ");
        String username = input.next();
        System.out.println("请输入密码: ");
        String password = input.next();

        Class.forName("com.mysql.jdbc.Driver");

        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/companydb","root","root");

        Statement statement = conn.createStatement();

        ResultSet resultSet = statement.executeQuery("select username,password from users where username = '"+username+"' and password = '"+password+"'");

        if (resultSet.next()) { //查询到了数据
            System.out.println("登陆成功!");
        }else{
            System.out.println("登陆失败!");
        }

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

七. SQL注入问题

7.1 什么是SQL注入

• 用户输入的数据中有SQL关键字或语法并且参与了SQL语句的编译, 导致SQL语句编译后的条件含义为true, 一直得到正确的结果, 这种现象称为SQL注入

7.2 如何避免SQL注入

• 由于编写的SQL语句是在用户输入数据, 整合后再进行编译, 所以为了避免SQL注入的问题, 我们要使SQL语句在用户输入数据前就已进行编译成完整的SQL语句, 再进行填充数据

八. PreparedStatement[重点]

• PreparedStatement继承了Statement接口, 所以执行SQL语句的方法无异

8.1 PreparedStatement的应用

• 作用:
  1. 预编译SQL语句, 效率高
  2. 安全, 避免SQL注入
  3. 可以动态的填充数据, 执行多个同构的SQL语句

8.1.1 参数标记

//1. 预编译 SQL 语句
PreparedStatement ps = conn.preparedStatement("select * from users where username=? and password=?");

• 注意: JDBC中的所有参数都由?符号占位, 这被称为参数标记, 在执行SQL语句之前, 必须为每个参数提供值

8.1.2 动态参数绑定

• ps.setXxx(下标,值) 参数下标从1开始, 为指定参数下标绑定值

//2. 为参数下标赋值
ps.setString(1,username);
ps.setString(2,password);

• 完整代码

public class LoginJdbc2 {
    public static void main(String[] args) throws Exception{
        Scanner input = new Scanner(System.in);
        System.out.println("请输入用户名: ");
        String username = input.nextLine();
        System.out.println("请输入密码: ");
        String password = input.nextLine();

        Class.forName("com.mysql.jdbc.Driver");

        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/companydb", "root", "root");

        //获得PreparedStatement对象,预编译SQL语句
        PreparedStatement ps = conn.prepareStatement("select * from users where username = ? and password = ?");
        //为?占位符赋值
        ps.setString(1,username);
        ps.setString(2,password);
        ResultSet resultSet = ps.executeQuery();

        if (resultSet.next()) {
            System.out.println("登陆成功!");
        }else{
            System.out.println("登陆失败");
        }

        resultSet.close();
        ps.close();
        conn.close();
    }
}

九. 封装工具类

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

9.1 重用性方案

• 封装获取连接, 释放资源两个方法
  • 提供public static Connection getConnection(){}方法
  • 提供public static void closeAll(Connection coon, Statement st, ResultSet rs){}方法

9.1.1 重用工具类实现

public class JdbcUtils {

    static{//类加载,只执行一次
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    //1. 获取连接
    public static Connection getConnection(){
        Connection conn = null;
        try {
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/companydb","root","root");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return conn;
    }
    //2. 释放资源
    public static void closeAll(Connection conn, Statement statement, ResultSet resultSet){
        try {
            if (resultSet != null){
                resultSet.close();
            }
            if (statement != null){
                statement.close();
            }
            if (conn != null){
                conn.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

}

9.2 跨平台方案

• 定义public static final Properties prop = new Properties();//读取配置文件的Map

• 定义static{

  • //首次使用工具类时, 加载驱动

    InputStream is = JdbcUtils.class.getResourceAsStream("路径");//通过复用本类自带流, 读取jdbc.properties配置文件. classpath = bin

  • prop.load(is);//通过prop对象将流中的配置信息分割成键值对

  • String driverName = prop.getProperty("driver");//通过driverName的键获取对应的值(com.mysql.jdbc.Driver)

  • Class.forName(driverName);//加载驱动

• }

9.2.1 跨平台工具类实现

(1) 在src目录下新建jdbc.properties配置文件

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/companydb?useUnicode=true&characterEncoding=utf8
username=root
password=root

(2) 工具类的封装

public class JdbcUtils2 {
    private static final Properties PROPERTIES = new Properties();//存储配置文件的map
    static {
        InputStream is = JdbcUtils2.class.getResourceAsStream("/jdbc.properties");//通过复用本类自带流, 读取jdbc.properties配置文件
        try {
            PROPERTIES.load(is);//通过流,将配置文件内容加载到properties集合
            Class.forName(PROPERTIES.getProperty("driver"));//通过键"driver"得到值"com.mysql.jdbc.Driver"
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(PROPERTIES.getProperty("url"), PROPERTIES.getProperty("username"), PROPERTIES.getProperty("password"));//通过键"url"得到值"jdbc:mysql://localhost:3306/companydb",通过键"username"得到值"root",通过键"password"得到值"root"
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return conn;
    }

    public static void closeAll(Connection conn, Statement statement, ResultSet resultSet) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (conn != null) {//先开后关
                conn.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

}

十. ORM 对象关系映射

• ORM: (Object Relational Mapping)
• 从数据库查询到的结果集(ResultSet)在进行遍历时, 逐行遍历, 取出的都是零散的数据, 在实际应用开发中, 我们需要将零散的数据进行封装整理

10.1 实体类(entity): 零散数据的载体

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

10.1.1 ORM应用

• entity实体类
  • 表名是t_jobs, 表有四列,分别是job_id, job_title, min_salary, max_salary所以我们创建一个T_Jobs类, 属性分别为四个列名

public class T_Jobs {
    private String job_id;
    private String job_title;
    private String min_salary;
    private String max_salary;

    public T_Jobs() {
    }

    public String getJob_id() {
        return job_id;
    }

    public void setJob_id(String job_id) {
        this.job_id = job_id;
    }

    public String getJob_title() {
        return job_title;
    }

    public void setJob_title(String job_title) {
        this.job_title = job_title;
    }

    public String getMin_salary() {
        return min_salary;
    }

    public void setMin_salary(String min_salary) {
        this.min_salary = min_salary;
    }

    public String getMax_salary() {
        return max_salary;
    }

    public void setMax_salary(String max_salary) {
        this.max_salary = max_salary;
    }

    @Override
    public String toString() {
        return "T_Jobs{" +
                "job_id='" + job_id + '\'' +
                ", job_title='" + job_title + '\'' +
                ", min_salary='" + min_salary + '\'' +
                ", max_salary='" + max_salary + '\'' +
                '}';
    }
}

• 将查询到的数据进行封装, 将所有对象存入集合中, 方便后续使用

public class TestORM {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        List<T_Jobs> t_jobsList = new ArrayList<>();
        try {
            conn = JdbcUtils2.getConnection();
            ps = conn.prepareStatement("select * from t_jobs");
            resultSet = ps.executeQuery();
            while (resultSet.next()) {
                String job_id = resultSet.getString("job_id");
                String job_title = resultSet.getString("job_title");
                String min_salary = resultSet.getString("min_salary");
                String max_salary = resultSet.getString("max_salary");

                //创建对象, 封装查询到的零散数据
                T_Jobs t_jobs = new T_Jobs();

                t_jobs.setJob_id(job_id);
                t_jobs.setJob_title(job_title);
                t_jobs.setMin_salary(min_salary);
                t_jobs.setMax_salary(max_salary);
                //每遍历一次得到一个对象, 把对象存到集合里, 方便后续的使用
                t_jobsList.add(t_jobs);
            }
            //集合遍历
            for (T_Jobs t_jobs : t_jobsList) {
                System.out.println(t_jobs);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils2.closeAll(conn,ps,resultSet);
        }
    }
}

十一. DAO 数据访问对象

• DAO: (Data Access Object)
• DAO实现了业务逻辑与数据库访问相分离
  • 对同一张表的所有操作封装在XxxDaoImpl对象中
  • 根据增删改查的不同功能实现具体的方法(insert, update, delete, select, selectAll)

11.1 创建数据库

• 创建一张person表, 有以下列:
  • id: int, 主键 自动增长
  • name: varchar(20) 非空
  • age: int 非空
  • birthday: date
  • email: 字符串
  • address: 字符串

CREATE TABLE person(
	id INT PRIMARY KEY AUTO_INCREMENT,
	`name` VARCHAR(20) NOT NULL,
	age INT NOT NULL,
	birthday DATE,
	email VARCHAR(20),
	address VARCHAR(20)
)CHARSET=utf8;

11.2 封装实体类

• 创建entity实体类Person,编写属性私有化, 构造方法, get/set 方法

public class Person {//根据表名person, 创建Person类
    private int id;//根据表的列名编写类的各个属性(私有)
    private String name;
    private int age;
    private Date birthday;
    private String email;
    private String address;

    public Person() {
    }

    public Person(String name, int age, Date birthday, String email, String address) {
        this.name = name;
        this.age = age;
        this.birthday = birthday;
        this.email = email;
        this.address = address;
    }

    public Person(int id, String name, int age, Date birthday, String email, String address) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.birthday = birthday;
        this.email = email;
        this.address = address;
    }

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getAddress() {
        return address;
    }

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

    @Override
    public String toString() {
        return "Person{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", birthday=" + birthday +
                ", email='" + email + '\'' +
                ", address='" + address + '\'' +
                '}';
    }
}

11.3 编写DaoImpl类

• 编写DaoImpl类, 提供增删改查方法. 使用JDBC开发步骤, 完成功能

public class PersonDaoImpl {
    //1. 新增
    public int insert(Person person) {
        Connection conn = null;
        PreparedStatement ps = null;
        String sql = "insert into person(name,age,birthday,email,address) values(?,?,?,?,?)";//往person表里新增一条数据
        try {
            conn = DBUtils.getConnection();//调用跨平台工具类获取数据库连接对象
            ps = conn.prepareStatement(sql);//获取执行SQL语句的对象
            //为指定的参数下标赋值
            ps.setString(1,person.getName());
            ps.setInt(2,person.getAge());
            ps.setDate(3,null);
            ps.setString(4,person.getEmail());
            ps.setString(5,person.getAddress());
            int result = ps.executeUpdate();
            return result;//返回执行结果,result为1则成功,0则失败
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBUtils.closeAll(conn,ps,null);//最后调用跨平台工具类释放资源
        }

        return 0;
    }
    //2. 修改
    public int update(Person person) {
        Connection conn = null;
        PreparedStatement ps = null;
        String sql = "update person set name=?,age=?,birthday=?,email=?,address=? where id = ?";
        try {
            conn = DBUtils.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1,person.getName());
            ps.setInt(2,person.getAge());
            ps.setDate(3,null);
            ps.setString(4,person.getEmail());
            ps.setString(5,person.getAddress());
            ps.setInt(6,person.getId());
            int result = ps.executeUpdate();
            return result;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBUtils.closeAll(conn,ps,null);
        }
        return 0;
    }
    //3. 删除
    public int delete(int id) {
        Connection conn = null;
        PreparedStatement ps = null;
        String sql = "delete from person where id = ?";
        try {
            conn = DBUtils.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            int result = ps.executeUpdate();
            return result;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBUtils.closeAll(conn,ps,null);
        }
        return 0;
    }
    //4. 查单条数据
    public Person select(int id) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        String sql = "select * from person where id = ?";
        Person person = null;
        try {
            conn = DBUtils.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            resultSet = ps.executeQuery();
            if (resultSet.next()) {//如果查到数据
                person = new Person();//则创建一个对象
                int pid = resultSet.getInt("id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                Date birthday = resultSet.getDate("birthday");
                String email = resultSet.getString("email");
                String address = resultSet.getString("address");
                //通过set方法为属性赋值
                person.setId(pid);
                person.setName(name);
                person.setAge(age);
                person.setBirthday(birthday);
                person.setEmail(email);
                person.setAddress(address);
            }
            return person;

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBUtils.closeAll(conn,ps,resultSet);
        }
        return null;
    }
    //5. 查所有数据
    public List<Person> selectAll() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        String sql = "select * from person";
        Person person = null;
        List<Person> personList = new ArrayList<>();//创建一个集合,用来存放Person对象
        try {
            conn = DBUtils.getConnection();
            ps = conn.prepareStatement(sql);
            resultSet = ps.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                Date birthday = resultSet.getDate("birthday");
                String email = resultSet.getString("email");
                String address = resultSet.getString("address");
                person = new Person(id,name,age,birthday,email,address);//通过构造方法为属性赋值
                personList.add(person);
            }
            return personList;

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBUtils.closeAll(conn,ps,resultSet);
        }
        return null;
    }
}

十二. Date工具类

• 现有问题: 数据库存储的数据类型为java.sql.Date. 而我们Java应用层存储日期类型为java.util.Date. 当我们用Java应用程序插入带有日期的数据到数据库中时, 需要进行转换

12.1 java.util.Date

• Java语言常规应用层面的日期类型, 可以通过字符串创建对应的时间对象
• 无法直接通过JDBC插入到数据库

12.2 java.sql.Date

• 不可以通过字符串创建对应的时间对象, 只能通过毫秒值创建对象(1970年至今的毫秒值)
• 可以直接通过JDBC插入到数据库

12.3 SimpleDateFormat

• 格式化和解析日期的具体类. 允许进行格式化(日期-->文本), 解析(文本-->日期) 和规范化

12.3.1 SimpleDateFormat应用

public class TestTimes {
    public static void main(String[] args) throws Exception{
        //格式化
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        //将字符串转日期 parse
        Date date = sdf.parse("2021-03-22");
        System.out.println(date);
        //将日期转字符串 format
        String str = sdf.format(date);
        System.out.println(str);

        //sql.Date 不支持字符串转换, 只支持毫秒值创建
        //通过util.Date拿到指定日期的毫秒值, 转换为sql.Date
        java.sql.Date sqlDate = new java.sql.Date(date.getTime());
        System.out.println(sqlDate);

    }
}

12.4 封装DateUtils工具类

public class DateUtils {

    private static final SimpleDateFormat sdf = new SimpleDateFormat("yy-MM-dd");
    //1. 字符串转为util.Date
    public static java.util.Date strToUtil(String str) {
        try {
            return sdf.parse(str);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return null;
    }

    //2. util.Date转sql.Date
    public static java.sql.Date utilToSql(java.util.Date date) {
        return new java.sql.Date(date.getTime());
    }

    //3. util.Date转字符串
    public static String utilToString(java.util.Date date) {
        return sdf.format(date);
    }
}

十三. Service业务逻辑

13.1 什么是业务?

(软件所提供的一个功能就叫业务)

service层核心思想

站在用户角度, 一个功能即是一个业务,可能需要多个DAO组成

• 转账(业务): A账户存款, B账户取款
  • Service业务逻辑层业务功能对应Service中一个方法

AccountServiceImpl{
transfer(){
saveMoney();
takeMoney();
}
}

AccountDaoImpl{
saveMoney();
takeMoney();
}

• 查看电商所有商品(业务): 销量最高的, 优惠打折的, 最新上架的

ProductServiceImpl{
showProduct(){
hostProduct();
salesProduct();
newProduct();
}
}

ProductDaoImpl{
hostProduct();
salesProduct();
newProduct();
}

13.2 Service开发流程

client
int result = AccountServiceImpl.transfer();

if (result > 0) {
//成功
}else {
//失败
}

AccountServiceImpl {
public int transfer(){
//1.验证卡号密码
//2.验证金额是否充足
//3.验证对方卡号
//4.A账户扣钱
//5.B账户加钱
return 0/1;
}
}

AccountDaoImpl{
//1.验证卡号密码
//2.验证金额是否充足
//3.验证对方卡号
//4.A账户扣钱
//5.B账户加钱
}

13.2.1 编写service实现转账功能

/**
 * @description: 转账业务
 * @param: fromNo 转账卡号
 * @param: pwd    转账卡的密码
 * @param: toNo   收钱卡号
 * @param: money  转账金额
 */

public class AccountsServiceImpl {

    public String transfer(String fromNo,String pwd,String toNo,double money) {//1. 收参
        String result = "转账失败!"
        //2. 组织完善业务功能
        AccountsDaoImpl accountsDao = new AccountsDaoImpl();
        try {                       
            //2.1 验证fromNo是否存在
            Accounts account = accountsDao.select(fromNo);
            if (account == null) {
                throw new RuntimeException("您的卡号不存在!");
            }

            //2.2 验证fromNo的密码是否正确
            if (!account.getPassword().equals(pwd)) {
                throw new RuntimeException("您的密码错误!");
            }

            //2.3 验证fromNo的余额是否充足
            if (account.getBalance() < money) {
                throw new RuntimeException("您的余额不足!");
            }

            //2.4 验证toNo的卡号是否存在
            Accounts toAccount = accountsDao.select(toNo);
            if (toAccount == null) {
                throw new RuntimeException("抱歉,对方卡号不存在!");
            }

            //2.5 减少fromNo的余额
            //修改自己的金额,将余额减去转账金额 替换原有的属性
            account.setBalance(account.getBalance() - money);
            accountsDao.update(account);

            //2.6 增加toNo的余额
            toAccount.setBalance(toAccount.getBalance() + money);
            accountsDao.update(toAccount);

            result = "转账成功!";
            
        } catch (Exception e) {
            e.printStackTrace();              
        } finally {
            DBUtils.closeAll(conn,null,null);
        }
        return result;
    }
}

十四. 事务

• 在JDBC中, 获得Connection对象开始事务---提交或回滚---关闭连接. 其事务策略是
  • conn.setAutoCommit(false);//true等价于1, false等价于0
  • conn.commit();//手动提交事务
  • conn.rollback();//手动回滚事务

14.1 service层控制事务

public class AccountsServiceImpl {

    public String transfer(String fromNo,String pwd,String toNo,double money) {//1. 收参
        String result = "转账失败!";
        //2. 组织完善业务功能
        AccountsDaoImpl accountsDao = new AccountsDaoImpl();

        //拿一个连接
        Connection conn = null;

        try {
            //建立了一个数据库连接
            conn = DBUtils.getConnection();
            System.out.println("service: "+conn);
            //开启事务,并关闭事务的自动提交
            conn.setAutoCommit(false);

            //2.1 验证fromNo是否存在
            Accounts account = accountsDao.select(fromNo);
            if (account == null) {
                throw new RuntimeException("您的卡号不存在!");
            }

            //2.2 验证fromNo的密码是否正确
            if (!account.getPassword().equals(pwd)) {
                throw new RuntimeException("您的密码错误!");
            }

            //2.3 验证fromNo的余额是否充足
            if (account.getBalance() < money) {
                throw new RuntimeException("您的余额不足!");
            }

            //2.4 验证toNo的卡号是否存在
            Accounts toAccount = accountsDao.select(toNo);
            if (toAccount == null) {
                throw new RuntimeException("抱歉,对方卡号不存在!");
            }

            //2.5 减少fromNo的余额
            //修改自己的金额,将余额减去转账金额 替换原有的属性
            account.setBalance(account.getBalance() - money);
            accountsDao.update(account);

            //2.6 增加toNo的余额
            toAccount.setBalance(toAccount.getBalance() + money);
            accountsDao.update(toAccount);

            result = "转账成功!";
            //执行到这里,没有异常,则提交事务
            conn.commit();
        } catch (RuntimeException | SQLException e) {
            e.printStackTrace();
            //转账失败,则整个事务回滚
            try {
                //出现异常,回滚
                System.out.println("出现了异常,回滚整个事务!");
                conn.rollback();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        } finally {
            DBUtils.closeAll(conn,null,null);
        }
        return result;
    }
}

• 问题: 当转账程序出现异常,事务控制成功了吗?

14.2 解决方案1: 传递Connection

• 为解决线程中Connection对象不同步的问题,可以将Connection对象通过service传递给各个DAO方法吗?

14.2.1 传递的问题

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

14.3 解决方案2: ThreadLocal

• 可以将整个线程中(单线程), 存储一个共享值
• 线程拥有一个类似Map的属性, 键值对结构<ThreadLocal对象, 值>

14.4 ThreadLocal应用

• 一个线程共享同一个ThreadLocal, 在整个流程中任一环节可以存值或取值

14.4.1 参数绑定

• 在DBUtils中, 将当前Connection对象添加到ThreadLocal中

public class DBUtils {

    private static final Properties PROPERTIES = new Properties();

    private static ThreadLocal<Connection> threadLocal = new ThreadLocal<>();
    static {
        InputStream is = DBUtils.class.getResourceAsStream("/jdbc.properties");

        try {
            PROPERTIES.load(is);
            Class.forName(PROPERTIES.getProperty("driver"));
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {
        Connection conn = threadLocal.get();//获取当前线程中存储的Connection对象,赋值给conn

        try {
            if (conn == null) {
                conn = DriverManager.getConnection(PROPERTIES.getProperty("url"), PROPERTIES.getProperty("username"), PROPERTIES.getProperty("password"));
                threadLocal.set(conn);//把连接存储到当前线程共享中
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return conn;
    }

    //关闭所有连接 增加 threadLocal.remove(); 移除
    public static void closeAll(Connection conn, Statement statement, ResultSet resultSet) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (conn != null) {
                conn.close();
                threadLocal.remove();//将conn移除
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

十五. 事务的封装

• 将事务的开启, 提交, 回滚都封装在工具类中, 业务层调用即可

15.1 完善工具类

//开启事务
    public static void begin() {
        try {
            Connection conn = getConnection();
            conn.setAutoCommit(false);//开启事务,关闭自动提交
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    //提交事务
    public static void commit() {
        Connection conn = null;
        try {
            conn = getConnection();
            conn.commit();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll(conn,null,null);
        }
    }
    //回滚事务
    public static void rollback() {
        Connection conn = null;
        try {
            conn = getConnection();
            conn.rollback();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll(conn,null,null);
        }
    }

十六. 三层架构

16.1 什么是三层

• 表示层(UI, Main)
  • 命名: XXXView
  • 职责:
    1. 收集用户输入数据
    2. 调用业务逻辑层, 完成业务方法
    3. 展示数据或展示操作结果
• 业务逻辑层(service)
  • 命名: XXXServiceImpl
  • 职责:
    1. 开启事务
    2. 调用DAO层
    3. 处理数据
    4. 提交或回滚
• 数据访问层(DAO)
  • 命名: XXXDaoImpl
  • 职责:
    1. 查询相关业务逻辑的数据
    2. 根据相关业务逻辑修改的数据

16.2 三层架构项目搭建(按开发步骤)

• utils 存放工具类 (DBUtils)
• entity 存放实体类 (Person)
• dao 存放 DAO 接口 (PersonDao)
  • impl存放 DAO 接口实现类 (PersonDaoImpl)
• service 存放 service 接口 (PersonServiceImpl)
  • impl存放 service 接口实现类 (PersonServiceImpl)
• view 存放程序启动类 (main)

• 程序设计时, 考虑易修改, 易扩展, 为service层和DAO层设计接口, 便于未来更换实现类

//1. 更自然的使用多态(父类接口引用指向子类对象)
//2. 更容易更换具体实现

public interface AccountsDao {

    int insert(Accounts accounts);
    int delete(String cardNo);
    int update(Accounts accounts);
    Accounts select(String cardNo);
    List<Accounts> selectAll();
    
}
public interface AccountsService {
    String transfer(String fromNo,String pwd,String toNo,double money);
}

十七. DaoUtils

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

17.1 commonUpdate

/*
* 公共处理增, 删, 改的方法
*
* @param sql 执行的sql语句
* @param args 参数列表,为占位符赋值
* @return  受影响的行数
*/
public class DaoUtils {

    public int commonUpdate(String sql,Object... args) {
        Connection conn = null;
        PreparedStatement ps = null;

        conn = DbUtils.getConnection();
        try {
            ps = conn.prepareStatement(sql);

            for (int i = 0; i < args.length; i++) {
                ps.setObject(i+1,args[i]);
            }
            return ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DbUtils.closeAll(null,ps,null);
        }
        return 0;
    }
}

17.2 commonselect

/*
* 公共查询方法 (可查询单个对象, 也可以查询多个对象,可以查任何一张表)
*
* @param sql
* @param args
* @return 集合
*/
//	select * from t_account;
//	select * from t_student;
//  select * from person;
//	工具不知道查的是什么 调用者知道
//	封装对象,对象赋值 调用者清除
public List<T> commonSelect(String sql, RowMapper<T> rowMapper, Object... args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        List<T> list = new ArrayList<>();

        conn = DbUtils.getConnection();
        try {
            ps = conn.prepareStatement(sql);
            if (args != null) {
                for (int i = 0; i < args.length; i++) {
                    ps.setObject(i+1,args[i]);//为占位符赋值
                }
            }
            resultSet = ps.executeQuery();
            while (resultSet.next()) {
                // id.name,age,birthday,email,address
                // 如何根据查询结果完成ORM,如何进行对象的创建及赋值
                T t = rowMapper.getRow(resultSet);//回调--->调用者提供一个封装方法ORM
                list.add(t);
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

RowMapper接口

public interface RowMapper<T> {
    T getRow(ResultSet resultSet);
}

PersonRowMapper实现类

public class PersonRowMapper implements RowMapper<Person> {
    @Override
    public Person getRow(ResultSet resultSet) {
        Person person = null;
        try {
            int pid = resultSet.getInt("id");
            String name = resultSet.getString("name");
            int age = resultSet.getInt("age");
            Date birthday = resultSet.getDate("birthday");
            String email = resultSet.getString("email");
            String address = resultSet.getString("address");

            person = new Person(pid,name,age,birthday,email,address);
            return person;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }
}

十八. Druid连接池

• 在程序初始化时, 预先创建指定数量的数据库连接对象存储在池中, 当需要连接数据库时, 从连接池中取出现有连接, 使用完毕后, 也不会进行关闭, 而是放回池中, 实现复用, 节省资源

18.1 Druid连接池使用步骤

• 创建 database properties配置文件
• 引入 druid.1.1.5.jar文件

18.1.1 database.properties配置文件

#<!-- 连接设置 -->
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/companydb?useUnicode=true&characterEncoding=utf8
username=root
password=root
#<!-- 初始化连接 -->
initialSize=10
#<!-- 最大连接数量 -->
maxActive=30
#<!-- 最小空闲连接 -->
minIdle=5
#<!-- 超时等待时间以毫秒为单位 60000毫秒/1000等于60秒 -->
maxWait=5000

18.1.2 连接池工具类

public class DbUtils {
    //声明连接池对象
    private static DruidDataSource ds;
    static {
        Properties properties = new Properties();
        InputStream is = DbUtils.class.getResourceAsStream("/database.properties");
        try {
            properties.load(is);
            //创建连接池
            ds = (DruidDataSource)DruidDataSourceFactory.createDataSource(properties);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection() {
        try {
            return ds.getConnection();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }
}

十九. Apache的DbUtils的使用

• Commons DbUtils 是Apache组织提供的一个对JDBC进行简单封装的开源工具类库, 使用它能够简化JDBC应用程序的开发! 同时, 不会影响程序的性能

19.1 DbUtils简介

• DbUtils是java编程中数据库操作实用小工具, 小巧, 简单, 实用
  • 对于数据库表的查询操作, 可以把结果转换为List, Array, Set等集合, 便于操作
  • 对于数据库表的DML操作, 也变得很简单(只需要写SQL语句)

19.1.1 DbUtils主要包含

• ResultSetHandler接口: 转换类型接口
  • BeanHandler类: 实现类, 把一条记录转换成对象
  • BeanListHandler类: 实现类, 把多条数据转换成List集合
  • ScalarHandler类: 实现类, 适合获取一行一列的数据
• QueryRunner: 执行sql语句的类
  • 增, 删, 改: update();
  • 查询: query();

19.2 DbUtils的使用步骤

• 导入jar 包
  • mysql连接驱动jar包: mysql-connector-java-5.1.25-bin.jar
  • Druid连接池jar包: druid-1.1.5.jar
  • database.properties配置文件
  • DbUtils工具jar包: commons-dbutils-1.7.jar

19.2.1 DbUtils工具类

public class DbUtils {
    //声明连接池对象
    private static DruidDataSource ds;

    static {
        //实例化配置对象
        Properties properties = new Properties();
        InputStream is = DbUtils.class.getResourceAsStream("/database.properties");
        try {
            //加载配置文件内容
            properties.load(is);
            //创建连接池
            ds = (DruidDataSource)DruidDataSourceFactory.createDataSource(properties);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //获取连接对象
    public static Connection getConnection() {
        try {
            return ds.getConnection();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }
    //返回一个数据源ds
    public static DataSource getDataSource() {
        return ds;
    }
    //释放资源
    //调用close()方法,实际调用的是DruidPooledConnection实现类里的close()
}

19.2.2 UserDaoImpl数据访问对象

public class UserDaoImpl implements UserDao {
    //创建QueryRunner对象,并传递一个数据源对象
    private QueryRunner queryRunner = new QueryRunner(DbUtils.getDataSource());
    @Override
    //1. 新增
    public int insert(User user) {
        Object[] params = {user.getUserId(),user.getUsername(),user.getPassword(),user.getAddress(),user.getPhone()};
        try {
            int result = queryRunner.update("insert into user(userId,username,password,address,phone) values(?,?,?,?,?)", params);
            return result;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    //2. 删除
    public int delete(int userId) {
        try {
            int result = queryRunner.update("delete from user where userId=?", userId);
            return result;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    //3. 修改
    public int update(User user) {
        Object[] params = {user.getUsername(),user.getPassword(),user.getAddress(),user.getPhone(),user.getUserId()};
        try {
            int result = queryRunner.update("update user set username=?,password=?,address=?,phone=? where userId=?", params);
            return result;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    //4. 查单个
    public User select(int userId) {
        try {
            //把查询到的记录封装成 指定对象
            User user = queryRunner.query("select * from user where userId=?", new BeanHandler<User>(User.class), userId);
            return user;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    //5. 查所有
    public List<User> selectAll() {
        try {
            List<User> userList = queryRunner.query("select * from user", new BeanListHandler<User>(User.class));
            return userList;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    //6. 查数量(单行单列)
    public long selectUserNums() {
        try {
            long count = queryRunner.query("select count(*) from user", new ScalarHandler<>());
            return count;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }
}