JDBC

最新推荐文章于 2022-09-22 14:29:39 发布

NULL_KUN

最新推荐文章于 2022-09-22 14:29:39 发布

阅读量328

点赞数 3

分类专栏： Java进阶文章标签： mysql entity java 数据库

本文链接：https://blog.csdn.net/weixin_44255829/article/details/105310028

版权

Java进阶专栏收录该内容

10 篇文章 0 订阅

订阅专栏

JDBC

一、引言

JavaWeb学习思维导图

1.1 如何操作数据

使用客户端工具访问数据库，需要手工建立链接，输入用户名和密码登录，编写SQL语句，点击执行，查看操作结果（结果集或受影响行数）。

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

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

二、JDBC（Java DataBase Connectivity）

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 适用于5.X版本
mysql-connector-java-8.0.X 适用于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.25-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.getConnection(url,user,password);获得数据库连接对象

URL:jdbc:mysql://localhost:3306/database?useUnicode=true&characterEncoding=utf8

user:root

password:123456

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

URL(Uniform Resource Locator)统一资源定位符：由协议、IP、端口、SID（程序实例名称）组成

3.3 获取发送SQL的对象

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

 Statement statement = connection.createStatement();

3.4 执行SQL语句

编写SQL语句，并执行，接收执行后的结果

int result = statement.executeUpdate("INSERT INTO grade(GradeName) VALUES ('NZ2008');“);

3.5 处理结果

接收并处理操作结果

if(result>0){
    System.out.println("执行成功！");
}

3.6 释放资源

遵循的是先开后关的原则，释放过程中用到的所有资源对象

 statement.close();
 connection.close();

3.7 综合案例

综合核心六步：实现增、删、改

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

public class TestJDBC {
    public static void main(String[] args) throws Exception {
        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");//将驱动字节码文件加载到JVM中

        //2.连接数据库
        String url = "jdbc:mysql://localhost:3306/companydb?useUnicode=true&characterEncoding=utf8";//数据库连接地址
        String user = "root";//用户名
        String password ="123456";//密码
        Connection connection = DriverManager.getConnection(url,user,password);

        //3.获取发送SQL语句的对象 Statement
        Statement statement = connection.createStatement();

        //4.编写SQL语句,并执行SQL语句
//        String sql = "INSERT INTO grade(GradeName) VALUES ('NZ2008');";
//        String sql = "delete from grade where GradeName='NZ2008'";
          String sql = "update grade set GradeName = 'NZ2008' WHERE GradeId='6' ";
        int result = statement.executeUpdate(sql);
        //5.处理结果
        if(result>0){
            System.out.println("执行成功！");
        }else{
            System.out.println("执行失败");
        }

        //6.释放资源 先开后管
        statement.close();
        connection.close();
    }
}

四、ResultSet（结果集）

在执行查询SQL后，存放查询的结果集数据

4.1 接收结果集

ResultSet rs =statement.executeQuery(sql)

ResultSet resultSet = statement.executeQuery(select GradeId, GradeName from grade);

4.2 遍历ResultSet中的数据

ResultSet以表（Table）结构进行临时结果的存储，需要通过JDBC API将其中的数据进行依次获取

数据行指针：初始行位置在第一行数据前，没调用一次boolean next()方法，ResultSet中指针向下移动一行，结果为true，表示当前行有数据
rs.getXxx(“列名”); 根据列名获得数据
rs.getXxx(整数下标);代表根据列的编号顺序获得！从1开始

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

4.2.1 遍历方法

int getInt(int columnIndex)throws SQLException;//获得当前行的第N列的int值
int getInt(String columnLable)throws SQLException;//获得当前行columnLable列的int值

注意：列的编号从1开始

4.3 综合案例

对grade表中所有数据进行遍历

4.3.1 根据列的名称获取

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

public class TestDql {
    public static void main(String[] args) throws Exception {
        //1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        //2,获得连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/companydb?useUnicode=true&characterEncoding=utf8","root","123456");

        //3.获得执行SQL的对象
        Statement statement = connection.createStatement();

        //4.编写SQL语句
        String sql = "select GradeId, GradeName from grade";
        ResultSet resultSet = statement.executeQuery(sql);
        //5.处理结果（结果集）
        while(resultSet.next()){//判断结果集是否有下一行
            //1.根据列名获取当前每一列的数据
            Integer GradeId = resultSet.getInt("GradeId");
            String gradeName = resultSet.getString("GradeName");
            System.out.println(GradeId+"\t"+gradeName);
        }
        //6.释放资源
        resultSet.close();
        statement.close();
        connection.close();
    }
}

4.3.2 根据列的下标获取

 //5.处理结果（结果集）
        while(resultSet.next()){//判断结果集是否有下一行
            //1.根据列名获取当前每一列的数据
           Integer GradeId = resultSet.getInt(1);
           String  GradeName = resultSet.getString(2);
            System.out.println(GradeId+"\t"+GradeName);
        }

五、常见错误

java.lang.ClassNotFoundException 找不到类(类名书写错误，没有导入jar包)
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException与SQL语句相关的错误（表名、列民书写错误、约束错误、插入的值是String类型但是没加单引号）建议：在客户端工具中测试sql语句后，再粘贴到代码中来
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry ‘NZ2008’ for key ‘GradeName’ 原因：主键值已存在，要改插入的主键值，或者插入了列约束UNIQUE重复值
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorExceptin:Unknown column “password” in
可能输入的值的类型不对，确定插入元素时，对应的值的类型的是否正确。

六、综合案例

6.1 创建表

创建一张用户表 User

id 主键、自动增长

username 字符串类型非空

password 字符串类型非空

phone 字符串类型

插入两条测试语句

create table user(
	userId int primary key auto_increment,
  	 username varchar(20) not null,
  	 password varchar(18) not null,
	address varchar(100),
     phone varchar(11)
)charset=utf8;
INSERT INTO USER(username,PASSWORD,address,phone) VALUES('张三','123','北京昌平沙河','13812345678');
INSERT INTO USER(username,PASSWORD,address,phone) VALUES('王五','5678','北京海淀','13812345141');
INSERT INTO USER(username,PASSWORD,address,phone) VALUES('赵六','123','北京朝阳','13812340987');
INSERT INTO USER(username,PASSWORD,address,phone) VALUES('田七','123','北京大兴','13812345687');

6.2 实现登录

通过控制台，用户输入用户名和密码
用户输入的用户名和密码作为参数，编写查询SQL语句
如果查询到用户，则用户存在，提示登录成功，反之，提示失败

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;

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

        //1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2,获得连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/companydb?useUnicode=true&characterEncoding=utf8","root","123456");
        //3.获得执行SQL的对象
        Statement statement = connection.createStatement();
        //4.编写SQL语句
        String sql = "select * from user where username = '" +username+"' and  password ='"+password+"'";
//String sql = "select * from user where username = 'xxx'  or 1=1 ;# 'and  password ='123456';
        ResultSet resultSet = statement.executeQuery(sql);
        //5.处理结果（结果集）
        if(resultSet.next()){//通过参数，查找了一行数据 提示用户登录成功
            System.out.println("登录成功！");
        }else{
            System.out.println("登录失败");
        }
        //6.释放资源
        resultSet.close();
        statement.close();
        connection.close();
    }
}

xxx’ or 1=1；#

七、SQL注入问题

7.1 什么是SQL注入

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

7.2 如何避免SQL注入

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

八、PreparedStatement【 `重点`】

PreparedStatement接口继承了Statement接口，执行SQL语句的方法没有区别！

8.1 PreparedStatement的应用

作用: 1.预编译SQL语句，效率高！

2.安全，避免SQL注入

3.可以动态的填充数据，执行多个同构的SQL语句

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

        //1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2,获得连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/companydb?useUnicode=true&characterEncoding=utf8","root","123456");

        //3.获得执行SQL的对象
        String sql = "select * from user where username = ? and  password =?" ;
       PreparedStatement preparedStatement = connection.prepareStatement(sql);//预编译SQL语句
        //为占位符下标赋值
        preparedStatement.setString(1,username);
        preparedStatement.setString(2,password);
        //4.执行SQL语句
        ResultSet resultSet = preparedStatement.executeQuery();
        //5.处理结果（结果集）
        if(resultSet.next()){//通过参数，查找了一行数据 提示用户登录成功
            System.out.println("登录成功！");
        }else{
            System.out.println("登录失败");
        }
        //6.释放资源
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }
}

8.1.1 参数标记

 //1.预编译SQL语句
 PreparedStatement preparedStatement = connection.prepareStatement(sql);

注意:PreparedStatement应用时，SQL字符串的参数都由？符号站位，被称为参数标记，在执行该SQL语句前，要为每个参数赋值

8.1.2 动态参数绑定

pstmt.setXxx(下标,值)；参数下标是从1开始，为指定占位符下标绑定值

//2.为展位符下标赋值
  pstmt.setString(1,username);
  pstmt.setString(2,password);

九、综合案例

9.1 创建数据库、表

数据库Account

创建一张表 Account，有以下列
cardId:字符串，主键
password：字符串，非空
username：字符串，非空
balance：小数，非空
phone：字符串，非空

CREATE DATABASE Account CHARACTER SET utf8;
USE Account;
CREATE TABLE t_account(
	cardId VARCHAR(20) PRIMARY KEY,
	PASSWORD VARCHAR(20) NOT NULL,
	username VARCHAR(10) NOT NULL,
	balance DOUBLE NOT NULL,
	phone VARCHAR(11)
)CHARSET = utf8;

9.2 创建项目通过JDBC实现功能

创建AccountSystem类，完成下列功能

开户：控制台输入所有的账号信息，使用PreparedStatement添加至t_account表
存款：控制台输入卡号、密码、存储金额进行进行修改
取款：输入卡号、密码、取款金额
转账：输入卡号、密码、对方卡号、转账金额进行修改
修改密码：控制台进行输入卡号、密码、再输入新密码进行修改
注销：控制台输入卡号、密码、删除对应的账户信息

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

/**
 * - 开户：控制台输入所有的账号信息，使用PreparedStatement添加至t_account表
 * - 存款：控制台输入卡号、密码、存储金额进行进行修改
 * - 取款：输入卡号、密码、取款金额
 * - 转账：输入卡号、密码、对方卡号、转账金额进行修改
 * - 修改密码：控制台进行输入卡号、密码、再输入新密码进行修改
 * - 注销：控制台输入卡号、密码、删除对应的账户信息
 */
public class AccountSystem {
    Scanner scanner = new Scanner(System.in);
    private static Connection connection = null;
    PreparedStatement preparedStatement=null ;
    ResultSet resultSet =null ;

    static {
        try {
            //1.注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.获得连接
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/Account?useUnicode=true&characterEncoding=utf8", "root", "123456");
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    //开户
    public void register() {
        System.out.println("请输入卡号:");
        String cardId = scanner.next();
        System.out.println("请输入密码:");
        String password = scanner.next();
        System.out.println("请输入用户名:");
        String username = scanner.next();
        System.out.println("请输入存款金额:");
        double balance = scanner.nextDouble();
        System.out.println("请输入预留手机号:");
        String phone = scanner.next();

        try {
            //3.创建PreparedStatement
            String sql = "insert into t_account(cardId,password,username,balance,phone)value(?,?,?,?,?)";
            preparedStatement = connection.prepareStatement(sql);
            //4.为占位符赋值
            preparedStatement.setString(1, cardId);
            preparedStatement.setString(2, password);
            preparedStatement.setString(3, username);
            preparedStatement.setDouble(4, balance);
            preparedStatement.setString(5, phone);
            //5.执行SQL语句
            int i = preparedStatement.executeUpdate();
            //6.处理结果
            if (i > 0) {
                System.out.println("开户成功！");
            } else {
                System.out.println("开户失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally { //6.释放资源
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //存款
    public void saveMoney() {
        System.out.println("请输入卡号:");
        String cardId = this.scanner.next();
        System.out.println("请输入密码:");
        String password = this.scanner.next();
        System.out.println("请输入存款金额:");
        double money = this.scanner.nextDouble();

        if (money > 0) {
            //存款操作
            String sql = "update t_account set balance = balance + ? where cardId=? and password=?";
            try {
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setDouble(1, money);
                preparedStatement.setString(2, cardId);
                preparedStatement.setString(3, password);
                int i = preparedStatement.executeUpdate();
                if (i > 0) {
                    System.out.println("存款成功！");
                } else {
                    System.out.println("存款失败");
                }

            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                if (preparedStatement != null) {
                    try {
                        preparedStatement.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
        } else {
            System.out.println("输入的金额不正确");
        }
    }

    //取款
    public void takeMoney() {
        System.out.println("请输入卡号:");
        String cardId = scanner.next();
        System.out.println("请输入密码:");
        String password = scanner.next();
        System.out.println("请输入取款金额:");
        double money = scanner.nextDouble();
        if (money > 0) {
            String sql = "select balance from t_account where cardId=? and password= ?";
            try {
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1, cardId);
                preparedStatement.setString(2, password);
                resultSet = preparedStatement.executeQuery();
                if (resultSet.next()) {
                    double balance = resultSet.getDouble(1);
                    if (money <= balance) {
                        //取款操作
                        String sql2 = "update t_account set balance = balance - ? where cardId=? and password= ?";
                        preparedStatement = connection.prepareStatement(sql2);
                        preparedStatement.setDouble(1, money);
                        preparedStatement.setString(2, cardId);
                        preparedStatement.setString(3, password);
                        int i = preparedStatement.executeUpdate();
                        if (i > 0) {
                            System.out.println("取款成功！");
                        }
                    } else {
                        System.out.println("您的余额不足！");
                    }
                } else {
                    System.out.println("请核实用户名或密码");
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (this.resultSet != null) {
                        this.resultSet.close();
                    }
                    if (this.preparedStatement != null) {
                        this.preparedStatement.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        } else {
            System.out.println("您的金额不正确");
        }
    }

    //转账
    public void transfer(){
        System.out.println("请输入您的卡号:");
        String cardId = scanner.next();
        System.out.println("请输入您的密码:");
        String password = scanner.next();
        System.out.println("请输入转账金额:");
        double money = scanner.nextDouble();
        if (money > 0) {
            //查询用户自身
            String sql = "select balance from t_account where cardId = ? and password = ?";
            try {
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1, cardId);
                preparedStatement.setString(2, password);
                resultSet = preparedStatement.executeQuery();

                if (resultSet.next()) {
                    double balance = resultSet.getDouble("balance");
                    if (money <= balance) {
                        System.out.println("请输入对方卡号");
                        String toCardId = scanner.next();
                        String ss = "select * from t_account where cardId=?";
                        preparedStatement = connection.prepareStatement(ss);
                        preparedStatement.setString(1, toCardId);
                        resultSet = preparedStatement.executeQuery();
                        if (resultSet.next()) {
                            String sql2 = "update t_account set balance = balance - ? where cardId = ?";
                            preparedStatement = connection.prepareStatement(sql2);
                            preparedStatement.setDouble(1, money);
                            preparedStatement.setString(2, cardId);
                            preparedStatement.executeUpdate();

                            String sql3 = "update t_account set balance = balance + ? where cardId = ?";
                            preparedStatement = connection.prepareStatement(sql3);
                            preparedStatement.setDouble(1, money);
                            preparedStatement.setString(2, toCardId);
                            preparedStatement.executeUpdate();
                        }else{
                            System.out.println("对方账户不存在");
                        }
                    }
                } else {
                    System.out.println("卡号或密码错误");
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (resultSet != null) {
                        resultSet.close();
                    }
                    if ( this.preparedStatement != null) {
                        this.preparedStatement.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        } else {
            System.out.println("转账金额输入错误！");
        }
    }

    //修改密码
    public void updatePwd() {
        System.out.println("请输入卡号:");
        String cardId = scanner.next();
        System.out.println("请输入密码:");
        String password = scanner.next();
        String sql = "select * form t_account where cardId= ? and password = ?";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, cardId);
            preparedStatement.setString(2, password);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                System.out.println("请输入新密码");
                String newPwd = scanner.next();
                String sql2 = "update t_account set password = ? where cardId= ?";
                preparedStatement = connection.prepareStatement(sql2);
                preparedStatement.setString(1, newPwd);
                preparedStatement.setString(2, cardId);
                int i = preparedStatement.executeUpdate();
                if (i > 0) {
                    System.out.println("修改成功");
                } else {
                    System.out.println("修改失败");
                }
            } else {
                System.out.println("请核对卡号或密码！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if (this.resultSet != null) {
                    this.resultSet.close();
                }
                if (this.preparedStatement != null) {
                    this.preparedStatement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //注销
    public void destroy() {
        System.out.println("请输入卡号:");
        String cardId = scanner.next();
        System.out.println("请输入密码:");
        String password = scanner.next();
        System.out.println("确定要注销账户吗？");
        String answer = scanner.next();
        if(answer.equals("y")){
            String sql = "delete from t_account where cardId= ? and password = ?";
            try {
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1,cardId);
                preparedStatement.setString(2,password);
                int i = preparedStatement.executeUpdate();
                if(i>0){
                    System.out.println("注销成功");
                }else{
                    System.out.println("注销失败");
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }finally{
                try {
                    if (resultSet != null) {
                        resultSet.close();
                    }
                    if (preparedStatement != null) {
                        preparedStatement.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }else{
            System.out.println("退出注销！");
        }
    }

    //只服务关闭
    public void closeConnecttion() {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

十、封装工具栏

10.1 重用性方案

封装了获取连接、释放资源的两个方法
提供 public static Connection getConnection()方法
提供public static void closeALL(Connection connection, Statement statement, ResultSet resultSet)

import java.sql.*;

/**
 * 数据库工具类
 * 1.提供连接-->Connection
 * 2.提供统一资源关闭
 * 可重用性方案
 */
public class DBUtils {
    static {
        try {
                Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
                e.printStackTrace();
        }
    }
    //硬编码
    //获得连接
    public static Connection getConnection(){
        Connection connection=null;
        try {
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/Account?useUnicode=true&characterEncoding=utf8","root","123456");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }
    //释放资源
    public static void closeALL(Connection connection, Statement statement, ResultSet resultSet){
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        }catch (SQLException e) {
                e.printStackTrace();
        }
    }
}

10.2 跨平台方案

定义private static final Properties properties = new Properties();配置文件集合

定义static{

//首次使用工具类、触发类加载

InputStream is =DBUtils.class.getResourceAsStream("/db.properties");//复用本类自带流，读取配置文件

properties.load(is);//将is流中的配置文件信息，加载到集合中

Class.forName(properties.getProperty(“driver”));

}

在getConnection方法中，应用properties.getProperty(“url”), properties.getProperty(“username”), properties.getProperty(“password”)

10.2.1 实现

在src目录下新建db.properties文件

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

DBUtils代码实现

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

/**
 *数据库工具类
 *  1.提供连接-->Connection
 *  2.释放资源
 *  可跨平台方案
 */
public class DBUtils {
    private static final Properties properties = new Properties();
    static {
        try {
            //适用类自身自带的流
            InputStream is =DBUtils.class.getResourceAsStream("/db.properties");
            properties.load(is);//通过流，将配置信息的内容分割成键值对
            Class.forName(properties.getProperty("driver"));
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection(){
        Connection connection=null;
        try {
            connection = DriverManager.getConnection( properties.getProperty("url"), properties.getProperty("username"), properties.getProperty("password"));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    //释放资源
    public static void closeALL(Connection connection, Statement statement, ResultSet resultSet){
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

十一、ORM

ORM(Object Relational Mapping)

从数据库查询到的结果集[ResultSet]在进行遍历时，逐行遍历，取出的都是零散的数据，在实际应用开发中，我们需要将零散的数据进行封装整理

CREATE TABLE `user`(
	id int primary key,
	username varchar(20) not null,
	password varchar(20) not null,
	sex char(2),
	email varchar(50) not null,
	address varchar(20) not null
)CHARSET = utf8;

11.1 ORM实体(entity):零散数据的载体

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

11.1 ORM应用

/**
 * id INT PRIMARY KEY,
 * 	username VARCHAR(20) NOT NULL,
 * 	PASSWORD VARCHAR(20) NOT NULL,
 * 	sex CHAR(2),
 * 	email VARCHAR(50) NOT NULL,
 * 	address VARCHAR(20) NOT NULL
 */
public class User {
    private int id;
    private String username;
    private String password;
    private String sex;
    private String email;
    private String address;
    public User(){}

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

    public int getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

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

    public String getPassword() {
        return password;
    }

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

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

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

    public User(int id, String username, String password, String sex, String email, String address) {
        this.id = id;
        this.username = username;
        this.password = password;
        this.sex = sex;
        this.email = email;
        this.address = address;
    }
}

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

public class OrmSelect {
    public static void main(String[] args) {
        Connection connection = DBUtils.getConnection();
        String sql = "select id,username,PASSWORD,sex,email,address from `user`;";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            System.out.println(preparedStatement);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {//拿到每一行数据、
                //拿到每一列的数据
                User user = new User();
                int id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                String password = resultSet.getString("password");
                String sex = resultSet.getString("sex");
                String email = resultSet.getString("email");
                String address = resultSet.getString("address");
                //将一行中零散的数据，封装在一个User对象里。
                user.setId(id);
                user.setUsername(username);
                user.setPassword(password);
                user.setSex(sex);
                user.setEmail(email);
                user.setAddress(address);
                System.out.println(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeALL(connection, preparedStatement, resultSet);
        }
    }
}

十二、DAO(Data Access Object)

数据访问对象

将所有对同一张表的操作都封装在一个XXXDaoImpl对象中。
根据增删改查的不同功能，实现具体的方法(insert,update,delete,select,selectAll)
经验：对于任何一张表中的数据进行操作时，无非就是增、删、改、查。应将对于一张表的所有操作统一封装在一个数据访问对象中。重用

import com.qf.t2.DBUtils;
import com.qf.t2.User;

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

/**
*对数据库中User表的一系列操作。
 * 只做对数据库访问的操作
 * 复用！对同一张表的操作 实现复用
 */
public class UserDaoImpl {
    private Connection connection = null;
    private PreparedStatement preparedStatement = null;
    private ResultSet resultSet = null;
    //增  int id,int username,String password,sex,email,address
    public int insert(User user){
        try {
            connection = DBUtils.getConnection();
            String sql = "insert into user(id,username,password,sex,email,address)values(?,?,?,?,?,?)";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,user.getId());
            preparedStatement.setString(2,user.getUsername());
            preparedStatement.setString(3,user.getPassword());
            preparedStatement.setString(4,user.getSex());
            preparedStatement.setString(5,user.getEmail());
            preparedStatement.setString(6,user.getAddress());
            int i = preparedStatement.executeUpdate();
            return i;//将操作结果返回给调用者
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBUtils.closeALL(connection,preparedStatement,null);
        }
        return 0;
    }
    //删
    public int delete(int id){
        connection = DBUtils.getConnection();
        String sql = "delete from user where id = ?";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);
            return preparedStatement.executeUpdate() ;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.closeALL(connection,preparedStatement,resultSet);
        }
        return 0 ;
    }
    //改
    public int update(User user){
        connection = DBUtils.getConnection();
        String sql = "update user username=?,password=?,sex=?,email=?,address=? where id = ?";
        try {
            preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setString(1,user.getUsername());
            preparedStatement.setString(2,user.getPassword());
            preparedStatement.setString(3,user.getSex());
            preparedStatement.setString(4,user.getEmail());
            preparedStatement.setString(5,user.getAddress());
            preparedStatement.setInt(6,user.getId());
            return preparedStatement.executeUpdate() ;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.closeALL(connection,preparedStatement,resultSet);
        }
        return 0;
    }
    //查单个
    public  User select(int id){
        connection = DBUtils.getConnection();
        String sql = "select id,username,password,sex,email,address from user where id = ?";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                int id1 = resultSet.getInt("id");
                String username = resultSet.getString("username");
                String password = resultSet.getString("password");
                String sex = resultSet.getString("sex");
                String email = resultSet.getString("email");
                String address = resultSet.getString("address");
                User  user = new User(id1,username,password,sex,email,address);
                return user;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.closeALL(connection,preparedStatement,resultSet);
        }
        return  null;
    }
    //查多个
    public List<User> selectAll(){
        connection = DBUtils.getConnection();
        String sql = "select id,username,password,sex,email,address from user";
        List<User> userList = new ArrayList<>();
        try {
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                int id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                String password = resultSet.getString("password");
                String sex = resultSet.getString("sex");
                String email = resultSet.getString("email");
                String address = resultSet.getString("address");
                User  user = new User(id,username,password,sex,email,address);
                userList.add(user);//每封装一个对象封装在集合中
            }
            return userList;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.closeALL(connection,preparedStatement,resultSet);
        }
        return null;
    }
}

import com.qf.t2.User;

import java.util.List;

public class TestDao {
    public static void main(String[] args) {
        //调用新增功能
        UserDaoImpl udi =new UserDaoImpl();
        User user = new User(2,"kunyan2","123456","女","1402683563@qq.com","wuhan");
//        int result = udi.insert(user);
//        if (result>0){
//            System.out.println("新增成功");
//        }else{
//            System.out.println("新增失败");
//        }
//        int result = udi.delete(2);
//        System.out.println(result);
//        User user2 = new User(2,"kunyan2","123","女","1402683563@qq.com","wuhan2");
//        int result =  udi.update(user2);
//        System.out.println(result);

//        User stu = udi.select(1);
//        System.out.println(stu);

        List<User> userList = udi.selectAll();
        userList.forEach(System.out::println);
    }
}

十三、日期类型

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

java.sql.Date
不可以通过字符串创建对应的时间对象。只能通过毫秒值创建对象(1970年1月1日至今的毫秒值)
可以直接通过JDBC插入数据库

  //1.java.util.Date
        System.out.println(new java.util.Date());

//        System.out.println("请输入入职日期：");
//        String date = Scanner.next();
        //自定义一个日期
        String str = "1999-06-24";
        //日期的转换  字符串转换为java.util.Date
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        //将日期字符串转换成util.Date类型
        java.util.Date parse = sdf.parse(str);

13.1 日期格式化工具

SimpleDateFormat 日期格式化

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");//按照指定格式转换成util.Date类型
java.util.Date date = sdf.parse("2000-01-01");

13.2 日期工具类 DateUtil

package com.qf.day43.t4;

import java.text.ParseException;
import java.text.SimpleDateFormat;

/**
 * 日期转换
 * 字符串转UtilDate
 * 字符串转SqlDate
 * utilDate转成Sqldate
 */
public class DateUtils {
    private static final SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");

    //字符串转Util
    public static java.util.Date strToUtilDate(String str) {
        try {
            return simpleDateFormat.parse(str);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return null;
    }
    //字符串转sql
//    public static java.sql.Date strToSqlDate(String str){
//        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
//        try {
//            java.util.Date date = simpleDateFormat.parse(str);
//            return new java.sql.Date(date.getTime());
//        } catch (ParseException e) {
//            e.printStackTrace();
//        }
//        return null;
//    }

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

}

13.2.1 测试

package com.qf.day43.t4;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;


public class TestDatetimes {
    public static void main(String[] args) throws ParseException {
        //1.java.util.Date 当前系统时间
//        System.out.println(new java.util.Date());
//
//        //自定义一个时间
//        String str = "1999-09-09";
//        //日期转换   字符串转为 java.util.Date
//        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        
//        //将日期字符串转换成 util.Date类型
//        java.util.Date utilDate  = sdf.parse(str);
//        System.out.println(utilDate);
//
//        //sql.Date  需要毫秒值，来构建一个日期
//        java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());
//        System.out.println(sqlDate);
//
//
//        java.util.Date date = DateUtils.strToUtilDate("2002-3-18");
//        System.out.println(date);
//
//        java.sql.Date date2 = DateUtils.utilToSql(date);
//        System.out.println(date2);

        System.out.println(new java.util.Date());

        System.out.println(new java.sql.Date(new java.util.Date().getTime()));
    }
}

作业：user\userinfo

列名	类型	说明
user_id	整数、主键	用户编号
user_name	字符串，唯一，非空	用户名称
user_pwd	字符串，非空	用户密码
user_borndate	DATE	出生日期
user_email	字符串，非空	邮箱
user_address	字符串	地址

注意：采用DAO+Entity完成

com.qf.xxx.entity

User

com.qf.xxx.dao

UserDaoImpl

完成五个方法、增、删、改、查、查所有

CREATE DATABASE userinfo CHARACTER SET utf8;
USER userinfo;
CREATE TABLE userinfo(
	user_id INT PRIMARY KEY,
	user_name VARCHAR(20) UNIQUE NOT NULL,
	user_pwd VARCHAR(20) NOT NULL,
	user_borndate DATE,
	user_email VARCHAR(30) NOT NULL,
	user_address VARCHAR(20)
)CHARSET = utf8;
SELECT * FROM userinfo;

import java.text.ParseException;
import java.text.SimpleDateFormat;

public class DateUtils {
    private static SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");

    public static java.util.Date strToUtil(String str){
        try {
            return simpleDateFormat.parse(str);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return null;
    }

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

}

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

public class DBUtils {
    private static final Properties PROPERTIES = new Properties();

    static {
        InputStream is = DBUtils.class.getResourceAsStream("/db.properties");
        try {
            PROPERTIES.load(is);
            Class.forName(PROPERTIES.getProperty("driver"));
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    //获取连接
    public static Connection getConnection() {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(PROPERTIES.getProperty("url"), PROPERTIES.getProperty("username"), PROPERTIES.getProperty("password"));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }
    //释放资源
    public static void closeAll(Connection connection, Statement statement, ResultSet resultSet) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

import java.util.Date;

public class Userinfo {
    private int user_id;
    private String user_name;
    private String  user_pwd;
    private Date user_borndate;
    private String user_email;
    private String user_address;

    public Userinfo() {
    }

    public Userinfo(int user_id, String user_name, String user_pwd, Date user_borndate, String user_email, String user_address) {
        this.user_id = user_id;
        this.user_name = user_name;
        this.user_pwd = user_pwd;
        this.user_borndate = user_borndate;
        this.user_email = user_email;
        this.user_address = user_address;
    }

    public int getUser_id() {
        return user_id;
    }

    public void setUser_id(int user_id) {
        this.user_id = user_id;
    }

    public String getUser_name() {
        return user_name;
    }

    public void setUser_name(String user_name) {
        this.user_name = user_name;
    }

    public String getUser_pwd() {
        return user_pwd;
    }

    public void setUser_pwd(String user_pwd) {
        this.user_pwd = user_pwd;
    }

    public Date getUser_borndate() {
        return user_borndate;
    }

    public void setUser_borndate(Date user_borndate) {
        this.user_borndate = user_borndate;
    }

    public String getUser_email() {
        return user_email;
    }

    public void setUser_email(String user_email) {
        this.user_email = user_email;
    }

    public String getUser_address() {
        return user_address;
    }

    public void setUser_address(String user_address) {
        this.user_address = user_address;
    }

    @Override
    public String toString() {
        return "Userinfo{" +
                "user_id=" + user_id +
                ", user_name='" + user_name + '\'' +
                ", user_pwd='" + user_pwd + '\'' +
                ", user_borndate=" + user_borndate +
                ", user_email='" + user_email + '\'' +
                ", user_address='" + user_address + '\'' +
                '}';
    }
}

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

public class UserinfoDaoImpl {

    private Connection connection = null;
    private PreparedStatement preparedStatement = null;
    private ResultSet resultSet = null;

    public int insert(Userinfo userinfo) {
        connection = DBUtils.getConnection();
        String sql = "insert into userinfo (user_id,user_name,user_pwd,user_borndate,user_email,user_address)values(?,?,?,?,?,?)";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,userinfo.getUser_id());
            preparedStatement.setString(2,userinfo.getUser_name());
            preparedStatement.setString(3,userinfo.getUser_pwd());
            preparedStatement.setDate(4,DateUtils.utilToSql(userinfo.getUser_borndate()));
            preparedStatement.setString(5,userinfo.getUser_email());
            preparedStatement.setString(6,userinfo.getUser_address());
            return preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.closeAll(connection,preparedStatement,resultSet);
        }
        return 0;
    }

    public int update(Userinfo userinfo) {
        connection = DBUtils.getConnection();
        String sql = "update userinfo set user_name = ?,user_pwd = ?,user_borndate = ?,user_email = ?,user_address = ? where user_id = ?";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,userinfo.getUser_name());
            preparedStatement.setString(2,userinfo.getUser_pwd());
            preparedStatement.setDate(3,DateUtils.utilToSql(userinfo.getUser_borndate()));
            preparedStatement.setString(4,userinfo.getUser_email());
            preparedStatement.setString(5,userinfo.getUser_address());
            preparedStatement.setInt(6,userinfo.getUser_id());
            return preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.closeAll(connection,preparedStatement,resultSet);
        }
        return 0;
    }

    public int delete(int user_id) {
        connection = DBUtils.getConnection();
        String sql = "delete from userinfo where user_id = ?";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,user_id);
            return preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.closeAll(connection,preparedStatement,resultSet);
        }
        return 0;
    }

    public Userinfo select(String user_name) {
        connection = DBUtils.getConnection();
        String sql = "select user_id,user_name,user_pwd,user_borndate,user_email,user_address from userinfo where user_name = ? ";
        Userinfo userinfo = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,user_name);
            resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                userinfo = new Userinfo(resultSet.getInt(1),resultSet.getString(2),resultSet.getString(3),resultSet.getDate(4),resultSet.getString(5),resultSet.getString(6));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.closeAll(connection,preparedStatement,resultSet);
        }
        return userinfo;
    }

    public List<Userinfo> selectAll() {
        connection = DBUtils.getConnection();
        String sql = "select user_id,user_name,user_pwd,user_borndate,user_email,user_address from userinfo ";
        Userinfo userinfo = null;
        List<Userinfo> userinfos = new ArrayList<>();
        try {
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                userinfo = new Userinfo(resultSet.getInt(1),resultSet.getString(2),resultSet.getString(3),resultSet.getDate(4),resultSet.getString(5),resultSet.getString(6));
                userinfos.add(userinfo);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.closeAll(connection,preparedStatement,resultSet);
        }
        return userinfos;
    }

}

import java.util.List;

public class TestUserinfo {
    public static void main(String[] args) {
        //新增
        UserinfoDaoImpl userinfoDao = new UserinfoDaoImpl();
//        //Userinfo u1 = new Userinfo(1,"kunyan","123456",DateUtils.strToUtil("1999-03-03"),"1402683563@qq.com","武汉");
//        Userinfo u2 = new Userinfo(2,"kunyan1","123456",DateUtils.strToUtil("1999-03-03"),"1402683563@qq.com","武汉");
//        int result = userinfoDao.insert(u2);
//        if(result>0){
//            System.out.println("新增成功");
//        }else{
//            System.out.println("新增失败");
//        }

        //修改
//        Userinfo u1 = new Userinfo(1,"kunyan","123456",DateUtils.strToUtil("1999-03-06"),"1402683563@qq.com","武汉");
//        userinfoDao.update(u1);

        //删除
        userinfoDao.delete(2);

        //查所有
        List<Userinfo> userinfos = userinfoDao.selectAll();
        userinfos.stream().forEach(System.out::println);

        //查单个
        Userinfo kunyan = userinfoDao.select("kunyan");
        System.out.println(kunyan);
    }
}

十四、连接池

每次连接数据库，都会获得一个连接对象。每次创建一个连接对象，都是一个较大的资源，如果在连接量较大的场景下，会极大的浪费资源，容易内存溢出。

14.1 自定义连接池

Java中提供了一个接口DataSource，通过实现该接口，可以创建连接池。

import javax.sql.DataSource;
import java.io.InputStream;
import java.io.PrintWriter;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.logging.Logger;

/**
 * 数据库连接池
 */
public class MyDbPool implements DataSource {
    //存储连接对象的集合
    private static List<Connection> connections = new ArrayList<>();
   //类加载时，就往集合中存放指定数量的连接对象
    static {
       InputStream is = MyDbPool.class.getResourceAsStream("/db.properties");
       Properties properties = new Properties();
       try {
           properties.load(is);
           Class.forName(properties.getProperty("driver"));
           //通过循环创建五个连接对象放进集合中
           for(int i = 1;i<=5;i++){
               Connection connection = DriverManager.getConnection(properties.getProperty("url"),properties.getProperty("username"),properties.getProperty("password"));
                connections.add(connection);
           }
       } catch (Exception e) {
           e.printStackTrace();
       }

   }
    @Override
    public Connection getConnection() throws SQLException {
        //获得连接
        System.out.println("池中有："+connections.size());
        Connection connection = null;
        if (connections.size()>0) {
            connection = connections.remove(0);
        }
        return connection;
    }
    //将连接放回池中
    public void release(Connection connection){
        connections.add(connection);
        System.out.println("放回一个"+connection);

    }




    @Override
    public Connection getConnection(String s, String s1) throws SQLException {
        return null;
    }

    @Override
    public PrintWriter getLogWriter() throws SQLException {
        return null;
    }

    @Override
    public void setLogWriter(PrintWriter printWriter) throws SQLException {

    }

    @Override
    public void setLoginTimeout(int i) throws SQLException {

    }

    @Override
    public int getLoginTimeout() throws SQLException {
        return 0;
    }

    @Override
    public Logger getParentLogger() throws SQLFeatureNotSupportedException {
        return null;
    }

    @Override
    public <T> T unwrap(Class<T> aClass) throws SQLException {
        return null;
    }

    @Override
    public boolean isWrapperFor(Class<?> aClass) throws SQLException {
        return false;
    }
}

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

public class TestDBPool {
    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        MyDbPool myDbPool = new MyDbPool();
        for(int i =1;i<10;i++){
            connection = myDbPool.getConnection();
            System.out.println(connection);
            myDbPool.release(connection);
        }
    }
}

14.2 Druid（德鲁伊）

Druid是目前比较流行高性能的，分布式列存储

一、亚秒级查询

二、实时数据注入

三、可扩展的PB级存储

四、多环境部署

五、丰富的社区

14.2.1 Druid配置

创建database properties配置文件

引入druid-1.1.5 jar

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/userinfo?useUnicode=true&characterEncoding=utf8
username=root
password=123456
#初始化连接
initialSize=10
#最大连接数量
maxActive=30
#最小空闲连接
minIdle=5
#超时等待时间以毫秒为单位
maxWait=5000

14.2.2 database.properties 文件配置

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

import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class DBPoolUtils {
    private  static DruidDataSource ds;
    static{
        Properties properties = new Properties();
        InputStream is = DBPoolUtils.class.getResourceAsStream("/database.properties");
        try {
            properties.load(is);
            //使用德鲁伊工厂创建连接池
            ds = (DruidDataSource)DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection(){
        try {
            return ds.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static void closeAll(Connection connection, Statement statement, ResultSet resultSet){
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

import java.sql.Connection;

public class TestDruid {
    public static void main(String[] args) throws Exception {
        for(int i = 0 ; i <30;i++){
            Connection connection = DBPoolUtils.getConnection();
            System.out.println(connection);
            connection.close();//不是释放资源关闭，而是放回连接池
//            DBPoolUtils.closeAll(connection,null,null
//            );
            //此时Connection存放的是DruidPooledConnection实现类
            //调用的close不是connection关闭的，而是归还连接池的close
        }
    }
}

注意：连接池中获得的Connection是DruidPooledConnection实现类，调用的close（）方法不是关闭数据库，而是将资源放回池中

十五、Service（Biz/Business）

15.1 业务

概念：用户要完成的一个业务功能，是由一个或多个的DAO调用组成

软件、程序提供一个功能都能叫做业务

在这里插入图片描述

15.2 业务层的实现

/**
*Userinfo的业务层逻辑层对象
 */
public class UserinfoServiceImpl {
    /**
    *用户的注册功能（业务）
     */
    public String register(Userinfo userinfo){  //1.接收参数 前端传过来的值
        UserinfoDaoImpl userinfoDao = new UserinfoDaoImpl();
        //2.调用数据访问层对象的查询方法
        Userinfo check = userinfoDao.select(userinfo.getUser_name());
        if(check!=null){//用户存在
            return "用户已存在！";
        }
        //3.调用数据访问层对象的新增方法
        int result = userinfoDao.insert(userinfo);
        //4.将操作结果返回给调用者
        if(result>0){
            return "注册成功！";
        }else{
            return "注册失败！";
        }
    }
	/**
	*登录功能业务
	*/
    public Userinfo login(String user_name,String user_pwd){//收参
        UserinfoDaoImpl userinfoDao = new UserinfoDaoImpl();

        //2.调用数据访问层对象的查询方法
        Userinfo userinfo = userinfoDao.select(user_name);

        //3.接收结果，处理结果
        if(userinfo!=null){//用户存在
            //检查查询到的用户密码和输入的密码是否一致
            if(userinfo.getUser_pwd().equals(user_pwd)){
                return userinfo;
            }
        }
        //4.响应给调用者结果
        return null;
    }
}

15.3 复用

DAO数据访问操作复用
业务功能的复用 //不同的终端访问

15.4 转账案例

代码参考day44 account项目

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

public class DBUtils {
    private static final Properties PROPERTIES = new Properties();
    private static final ThreadLocal<Connection> THREAD_LOCAL = new ThreadLocal<>();

    static {
        InputStream is = DBUtils.class.getResourceAsStream("/db1.properties");
        try {
            PROPERTIES.load(is);
            Class.forName(PROPERTIES.getProperty("driver"));
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    //获取连接
    public static Connection getConnection() {
        //在ThreadLocal里取
        Connection connection = THREAD_LOCAL.get();
        try {
            //没有，新建
            if(connection==null) {
                connection = DriverManager.getConnection(PROPERTIES.getProperty("url"), PROPERTIES.getProperty("username"), PROPERTIES.getProperty("password"));
                THREAD_LOCAL.set(connection);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }
    //释放资源
    public static void closeAll(Connection connection, Statement statement, ResultSet resultSet) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
                THREAD_LOCAL.remove();//关闭连接后移除线程中绑定的连接对象
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

public class T_Account {
    private String cardId;
    private String password;
    private String username;
    private double balance;
    private String phone;

    @Override
    public String toString() {
        return "T_Account{" +
                "cardId='" + cardId + '\'' +
                ", password='" + password + '\'' +
                ", username='" + username + '\'' +
                ", balance=" + balance +
                ", phone='" + phone + '\'' +
                '}';
    }

    public String getCardId() {
        return cardId;
    }

    public void setCardId(String cardId) {
        this.cardId = cardId;
    }

    public String getPassword() {
        return password;
    }

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

    public String getUsername() {
        return username;
    }

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

    public double getBalance() {
        return balance;
    }

    public void setBalance(double balance) {
        this.balance = balance;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public T_Account(String cardId, String password, String username, double balance, String phone) {
        this.cardId = cardId;
        this.password = password;
        this.username = username;
        this.balance = balance;
        this.phone = phone;
    }
}

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

public class T_AccountServiceImpl {
    /**
     * 转账业务
     * @param fromNo 扣钱卡号
     * @param pwd  转账卡号密码
     * @param toNo 收钱卡号
     * @param money 转账金额
     */
    public String transfer(String fromNo,String pwd,String toNo,double money) {
        String result = "转账失败";
        //2.组织业务功能
        T_AcoountDaoImpl accountDao = new T_AcoountDaoImpl();

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

        try {
            //建立了一个数据库连接
            connection = DBUtils.getConnection();
            //开启事务!并且关闭事务的自动提交
            connection.setAutoCommit(false);

            //2.1 验证fromNo是否存在
            T_Account fromAcc = accountDao.select(fromNo);
            if (fromAcc == null) {
                throw new RuntimeException("----卡号不存在----");
            }
            //2.2 验证fromNo的密码是否正确
            if (fromAcc.getPassword().equals(pwd)) {
                throw new RuntimeException("----密码错误----");
            }
            //2.3 验证余额是否充足
            if (fromAcc.getBalance() < money) {
                throw new RuntimeException("----余额不足----");
            }
            //2.4 验证toNo是否存在
            T_Account toAcc = accountDao.select(toNo);
            if (toAcc == null) {
                throw new RuntimeException("----对方卡号不存在----");
            }
            //2.5 减少fromNo的余额
            //修改自己的金额，将余额-转账金额替换原有的属性
            fromAcc.setBalance(fromAcc.getBalance() - money);
            accountDao.update(fromAcc);

            //出现异常！导致程序终止
            int i = 10/0;

            //2.6 增加toNo的余额
            toAcc.setBalance(toAcc.getBalance() + money);
            accountDao.update(toAcc);
            result = "转账成功";
            //执行到这里，没有异常，则提交事务
            connection.commit();
        }catch(Exception e){
            e.printStackTrace();
            try {
                //出现异常回滚
                System.out.println("出现了异常！回滚整个事务！");
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }finally{
            DBUtils.closeAll(connection,null,null);
        }
        return result;
    }
}

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

public class T_AcoountDaoImpl {
    private Connection connection = null;
    //为当前线程绑定一个Connection连接，从头越到尾
    private PreparedStatement preparedStatement = null;
    private ResultSet resultSet = null;

    public int insert(T_Account account){
        return 0 ;
    }

    public int delete(String cardId){
        return 0;
    }
    //修改操作！复用性更强
    public int update(T_Account account){
        connection = DBUtils.getConnection();
        String sql = "update t_account set password=?,username=?,balance = ?,phone = ? where cardId=?";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,account.getPassword());
            preparedStatement.setString(2,account.getUsername());
            preparedStatement.setDouble(3,account.getBalance());
            preparedStatement.setString(4,account.getPhone());
            preparedStatement.setString(5,account.getCardId());
            return preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.closeAll(null,preparedStatement,resultSet);
        }
        return 0;
    }

    public T_Account select(String cardId){
        connection = DBUtils.getConnection();
        String sql = "SELECT cardId,password,username,balance,phone from t_account where cardId=?";
        T_Account account = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,cardId);
            resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                account = new T_Account(resultSet.getString(1),resultSet.getString(2),resultSet.getString(3),resultSet.getDouble(4),resultSet.getString(5));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.closeAll(null,preparedStatement,resultSet);
        }
        return account;
    }
}

public class TestTransfer {
    public static void main(String[] args) {
        T_AccountServiceImpl t_accountService = new T_AccountServiceImpl();

        String result = t_accountService.transfer("6002","123456","6001",100);
        System.out.println(result);
    }
}

15.5 解决转账事务问题

1.传递Connection：如果将Service获得的Connection对象，传递给DAO各个方法。可以。//BadSmell臭味

定义接口是为了更容易更换实现，而将Connection参数定义在接口方法中，就会污染当前接口，而无法复用。JDBC-Connection。MyBatis使用SqlSession

2.单例：当前项目只能有一个客户端连接

十六、ThreadLocal

线程工具类：在整个线程中，一直到释放资源，用的是同一个Connection连接对象。

16.1 ThreadLocal

在整个线程（单条执行路径中）所持有的Map中，存储一个键（threadlocal）值（connection）对

线程（thread）对象中只有一个ThreadLocalMap类型的对象（ThreadLocals ），threadLocals中保持了以ThreadLocal对象为Key，set进去的值为Value

每个线程均可绑定多个ThreadLocal，一个线程中可存储多个ThreadLocal

16.1.1 ThreadLocal代码

import java.sql.Connection;

public class Test {
    public static void main(String[] args) {
        //绑定到线程中！
        ThreadLocal<Connection> threadLocal = new ThreadLocal<Connection>();//0x1122333

        //Thread
        //获得当前线程对象--->t.threadLocals集合为空-->create-->table[entry]-->key=0x1122333 value=connection
        threadLocal.set(null);
        //获得当前线程对象-->getMap-->t.threadLocals-->getEntry(0x1122333)-->entry-->entry.value
        Connection connection = threadLocal.get();

        //每个线程都可以绑定多个ThreadLocal
        ThreadLocal<Integer> threadLocal1 = new ThreadLocal<Integer>();
        threadLocal1.set(123);
        Integer i = threadLocal1.get();
        System.out.println(i);
    }
}

16.1.2 ThreadLocal的安全

import java.sql.Connection;

public class TestThread {
    public static void main(String[] args) {
        Thread t1 = new Thread(new Runnable() {
            @Override
            public void run() {
                Connection connection = DBUtils.getConnection();
                System.out.println(connection);

                try {
                    Thread.sleep(5000);
                } catch (InterruptedException e) {
                    e.printStackTrace();
                }

            }
        });

        Thread t2 = new Thread(new Runnable() {
            @Override
            public void run() {
                Connection connection = DBUtils.getConnection();
                System.out.println(connection);

                try {
                    Thread.sleep(5000);
                } catch (InterruptedException e) {
                    e.printStackTrace();
                }

            }
        });
        t1.start();
        t2.start();
    }
}

16.2 ThreadLocal事务控制优化

将业务层的多步事务操作，封装在DBUtils工具类里，实现复用

16.2.1 DBUtils封装事务的控制

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

public class DBUtils {
    private static final Properties PROPERTIES = new Properties();
    private static final ThreadLocal<Connection> THREAD_LOCAL = new ThreadLocal<>();

    static {
        InputStream is = DBUtils.class.getResourceAsStream("/db1.properties");
        try {
            PROPERTIES.load(is);
            Class.forName(PROPERTIES.getProperty("driver"));
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    //获取连接
    public static Connection getConnection() {
        //在ThreadLocal里取
        Connection connection = THREAD_LOCAL.get();
        try {
            //没有，新建
            if(connection==null) {
                connection = DriverManager.getConnection(PROPERTIES.getProperty("url"), PROPERTIES.getProperty("username"), PROPERTIES.getProperty("password"));
                THREAD_LOCAL.set(connection);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    //开启事务
    public static void begin(){
        Connection connection = getConnection();
        try {
            connection.setAutoCommit(false);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //提交事务
    public static void commit(){
        Connection connection = getConnection();
        try {
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.closeAll(connection,null,null);
        }
    }

    //回滚事务
    public static void rollback(){
        Connection connection = getConnection();
        try {
            connection.rollback();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.closeAll(connection,null,null);
        }
    }


    //释放资源
    public static void closeAll(Connection connection, Statement statement, ResultSet resultSet) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
                THREAD_LOCAL.remove();//关闭连接后移除线程中绑定的连接对象
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

十七、三层架构设计

表示层：
命名：xxxView
职责：收集用户的数据和需求，展示数据

业务逻辑层：
命名：xxxServiceImpl
职责：数据的加工处理，调用Dao组合完成业务实现、控制事务

数据访问层：
命名：xxxDaoImpl
职责：向业务层提供数据，将业务层加工处理的数据同步给数据库

在这里插入图片描述

十八、工具类型的封装及普适性泛型工具

18.1 封装DML方法、DQL方法

import com.qf.day45.t3.advanced.RowMapper;

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

public class DaoUtils<T> {
    /**
     * 公共处理增、删、改的方法
     * sql语句，参数列表
     * @param sql 执行的sql语句
     * @param args 参数列表，为占位符赋值
     * @return
     */
    public int commonsUpdate(String sql,Object... args){
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = DBUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);

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

    /**
     * 公共查询方法（可查询单个对象，也可查询多个对象）
     * @param sql
     * @param args
     * @return
     */
    //工具不知道查的是什么 调用者知道
    //封装对象，对象赋值，调用者知道
    public List<T> commonSelect(String sql, RowMapper<T> rowMapper, Object... args){
        List<T> elements = new ArrayList<T>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = DBUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            if(args!=null) {
                for (int i = 0; i < args.length; i++) {
                    preparedStatement.setObject(i + 1, args[i]);
                }
            }
           resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                //根据查询结果完成orm，如何完成对象的创建及赋值?
                T t = rowMapper.getRow(resultSet);//回调-->调用者提供的一个封装方法ORM
                elements.add(t);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.closeAll(null,preparedStatement,null);
        }
        return elements;
    }
}

十九、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包
druid-1.1.5.jar
database.properties配置文件
commons -dbutils-1.7.jar

19.2.1 代码实现

DBUtils工具类

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

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

/**
 * 连接池工具类
 */
public class DBUtils {
    private static DruidDataSource dataSource;

    static{
        Properties properties = new Properties();
        InputStream is = DBUtils.class.getResourceAsStream("/database.properties");
        try {
            properties.load(is);
            dataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //返回一个数据源
    public static DataSource getDataSource(){
        return dataSource;
    }
}

UserDaoImpl 数据访问对象

import com.project.dao.UserDao;
import com.project.entity.User;
import com.project.utils.DBUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

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

public class UserDaoImpl implements UserDao {
    //1.创建QueryRunner对象，并传递一个数据源对象
    private QueryRunner queryRunner = new QueryRunner(DBUtils.getDataSource());
    @Override
    public int insert(User user) {
        Object[] params={user.getId(),user.getUsername(),user.getPassword(),user.getSex(),user.getEmail(),user.getAddress()};
        try {
            return queryRunner.update("insert into user(id,username,password,sex,email,address)values(?,?,?,?,?,?)",params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int update(User user) {
        Object[] params={user.getUsername(),user.getPassword(),user.getSex(),user.getEmail(),user.getAddress(),user.getId()};
        try {
            return queryRunner.update("update user set username=?,password=?,sex=?,email=?,address=? where id =?",params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int delete(int id) {
        try {
            return queryRunner.update("delete from user where id =?",id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public User select(int id) {

        try {
            //把查询的记录封装成指定对象
            // user = new User(resultSet.getInt(1), resultSet.getString(2), resultSet.getString(3), resultSet.getString(4), resultSet.getString(5), resultSet.getString(6));
            return queryRunner.query("select * from user where id=?",new BeanHandler<User>(User.class),id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<User> selectAll() {
        try {
            return queryRunner.query("select * from user",new BeanListHandler<User>(User.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}

atabase.properties");
try {
properties.load(is);
dataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
//返回一个数据源
public static DataSource getDataSource(){
return dataSource;
}
}


##### UserDaoImpl 数据访问对象

```java
import com.project.dao.UserDao;
import com.project.entity.User;
import com.project.utils.DBUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

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

public class UserDaoImpl implements UserDao {
    //1.创建QueryRunner对象，并传递一个数据源对象
    private QueryRunner queryRunner = new QueryRunner(DBUtils.getDataSource());
    @Override
    public int insert(User user) {
        Object[] params={user.getId(),user.getUsername(),user.getPassword(),user.getSex(),user.getEmail(),user.getAddress()};
        try {
            return queryRunner.update("insert into user(id,username,password,sex,email,address)values(?,?,?,?,?,?)",params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int update(User user) {
        Object[] params={user.getUsername(),user.getPassword(),user.getSex(),user.getEmail(),user.getAddress(),user.getId()};
        try {
            return queryRunner.update("update user set username=?,password=?,sex=?,email=?,address=? where id =?",params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int delete(int id) {
        try {
            return queryRunner.update("delete from user where id =?",id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public User select(int id) {

        try {
            //把查询的记录封装成指定对象
            // user = new User(resultSet.getInt(1), resultSet.getString(2), resultSet.getString(3), resultSet.getString(4), resultSet.getString(5), resultSet.getString(6));
            return queryRunner.query("select * from user where id=?",new BeanHandler<User>(User.class),id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<User> selectAll() {
        try {
            return queryRunner.query("select * from user",new BeanListHandler<User>(User.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}

NULL_KUN

关注

3
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
JDBC

JDBC一、引言JavaWeb学习思维导图1.1 如何操作数据使用客户端工具访问数据库，需要手工建立链接，输入用户名和密码登录，编写SQL语句，点击执行，查看操作结果（结果集或受影响行数）。1.2 实际开发中，会采用客户端操作数据库吗？在实际开发过程中，当用户的数据发生改变时，不可能通过客户端操作执行SQL语句，因为操作量过大！无法保证效率和正确性二、JDBC（Java Da...
复制链接

扫一扫