园龄：2年8个月粉丝：9 关注：0

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 环境搭建

在项目下新建lib文件夹,用于存放jar文件
将mysql驱动mysql-connector-java-5.1.X-bin.jar复制到项目中的lib文件夹中
选中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

职责:

收集用户输入数据

调用业务逻辑层, 完成业务方法

展示数据或展示操作结果

业务逻辑层(service)

命名: XXXServiceImpl

职责:

开启事务

调用DAO层

处理数据

提交或回滚

数据访问层(DAO)

命名: XXXDaoImpl

职责:

查询相关业务逻辑的数据

根据相关业务逻辑修改的数据

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;
    }
}

上一篇1_Layui

下一篇1_MySQL

本文作者：柒木木木

本文链接：https://www.cnblogs.com/qimu666/p/16565734.html

posted @ 2022-08-09 13:46 柒木木木阅读(21) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

	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语句并接收结果

	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();
	}
	}

	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值

	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();
	}
	}

	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();
	}
	}

	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');

	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();
	}
	}

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

	//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();
	}
	}

	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();
	}
	}

	}

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

	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();
	}
	}

	}

	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);
	}
	}
	}

	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;

	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 + '\'' +
	'}';
	}
	}

	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;
	}
	}

	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);

	}
	}

	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);
	}
	}

	/**
	* @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;
	}
	}

	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;
	}
	}

	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();
	}
	}
	}

	//开启事务
	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);
	}
	}

	//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);
	}

	/*
	* 公共处理增, 删, 改的方法
	*
	* @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;
	}
	}

	/*
	* 公共查询方法 (可查询单个对象, 也可以查询多个对象,可以查任何一张表)
	*
	* @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;
	}

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

	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;
	}
	}

	#<!-- 连接设置 -->
	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