第七周总结

第七周总结

​ 这周主要是对JDBC的学习，是一套通用的java连接数据库的标准，用于屏蔽数据库之间的差异，使得程序员可以专注于处理数据而不需要考虑数据库之间的差异。

入门案例

第一个JDBC程序:

实现添加一条固定的数据到表格中

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

// 2.获取数据库连接对象
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");

// 3.获取处理sql语句的对象
Statement stat = conn.createStatement();

// 4.执行sql(当执行结果为resultset（执行select操作时）时返回值为true，其他任何时候都是false)
boolean f = stat.execute("insert into user(username,password,nickname) values('softeem','soft123','软帝')");

// 5.处理结果
System.out.println("执行结果："+f);

// 6.关闭资源
stat.close();
conn.close();

JDBC常见类与接口

DriverManager

DriverManager是java.sql包中提供的一个用于进行数据库驱动管理的工具类，对于驱动的注册，以及连接的获取都可以通过DriverManager来完成，另外从jdbc4开始，不再需要显示的使用Class.forName("com.mysql.jdbc.Driver")加载驱动；因为DriverManager会自动寻找驱动包里面的以下文件:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nO0eKKCE-1607845200064)(E:\Java学习\Java学习\20201209\笔记\assets\1607485243871.png)]

DriverManager提供的常见方法：

• getConnection(String url,String user,String password)

• getConnection(String url)

  Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?user=root&password=123456");
  

• getConnection(String url,Properties prop)

  Properties info = new Properties();
  info.put("user","root");
  info.put("password","123456");
  Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test",info);
  

Connection

Connection是JDBC中的核心接口位于java.sql包，用于实现应用程序和数据库管理系统（DBMS）进行通信一个通道，通过该接口可以向数据库发送可以执行sql脚本，以及将执行之后的结果返回到程序中。connection即数据库与应用程序之间的一个会话(session)

常用方法：

• createStatement() :获取一个用于执行sql语句的执行对象，主要用于执行静态的sql（存在SQL注入风险）
• parepareStatement(String sql) :获取一个预处理sql语句的执行对象，预先对sql语句编译，一般用于执行动态sql（解决SQL注入风险）
• prepareCall(String sql)：获取一个用于预处理sql存储过程的执行对象，主要用于执行存储过程（procedure）
• setAutoCommit(boolean b)：设置事物是否自动提交
• commit()：提交事务
• rollback()：事物回滚
• getMetaData(): 获取数据库的元数据对象（DatabaseMetaData），包含了数据库的详细信息

Statement与PreparedStatement

Statement

Statement是来自java.sql包中用于执行sql语句的处理接口对象，该接口声明了一些用于执行sql语句的相关方法，常见如下：

• execute(String sql)：用于执行任何的sql语句
• executeQuery(String sql)：主要用于执行sql查询(Select)操作
• executeUpdate(String sql)：主要用于执行sql更新(insert,update,delete)操作以及DDL操作
• addBatch(String sql)：用于将sql语句添加到当前Statement对象的命令列表中用于批量执行sql
• executeBatch()：用于将一批（多条sql）sql命令同时执行
• getResultSet()：获取执行当前SQL语句之后的结果集对象（ResultSet）
• getGeneratedKeys()：获取自动生成的键所在结果集

具体使用如下:

public static void insert(String name, String pwd, String nickname) throws ClassNotFoundException, SQLException {
    //准备sql语句
    String sql = "insert into user(username,password,nickname) values('" + name + "','" + pwd + "','" + nickname + "')";
    // 1.加载驱动
    Class.forName("com.mysql.jdbc.Driver");
    // 2.获取连接
    Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "123456");
    // 3.获取处理sql语句的执行对象
    Statement stat = conn.createStatement();
    // 4.执行sql语句
    boolean b = stat.execute(sql);
    // 5.处理结果
    System.out.println("是否有ResultSet：" + b);
    // 6.关闭资源
    stat.close();
    conn.close();
}

对以上程序在main方法中执行

public static void main(String[] args){
     insert("rose","rose123","肉丝");
}

通过对以上代码的观察发现，如果需要实现动态的数据操作，则需要对sql语句进行字符串拼接，使得sql语句编写起来比较麻烦，并且使用过程中存在SQL注入的风险：

SQL注入

观察以下代码

public static boolean login(String username, String password) throws ClassNotFoundException, SQLException {
    String sql = "select * from user where username='" + username + "' and password='" + password + "'";
    Connection conn = null;
    Statement stat = null;
    try {
        conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "123456");
        stat = conn.createStatement();
        //执行查询获取结果集
        ResultSet rs = stat.executeQuery(sql);
        //判断结果集中是否存在可用结果行
        if (rs.next()) {
            return true;
        }
    } finally {
        conn.close();
        stat.close();
    }
    return false;
}

若调用该方法时按如下格式提供方法参数值：

//select * from user where username='asdfasdf' and password='' or '1'='1'
boolean b = login("asdfasdf", "' or '1'='1 ");
System.out.println(b ? "登录成功" : "登录失败");

分析得知，无论账号如何提供，子需要密码按照第二个参数的写法，即可绕过登录过程直接输出如下结果:

登录成功

因为使用Statement不仅会存在字符串拼接的复杂问题，还会导致SQL注入的风险；因此JDBC中针对该问题从Statement接口还扩展了一个子接口java.sql.PraparedStatement用于解决以上问题:

PreparedStatement

PreparedStatement是从Statement接口继承而来，使用该接口可以有效的避免sql注入以及字符串拼接的问题:

public class JDBCDemo3 {

    public static boolean login(String username,String password) throws SQLException {
        //代码移动 alt+shift+up/down
        String sql = "select * from user where username=? and password=?";
        ResultSet rs = null;
        //JDK1.7新增
        try(
            Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "123456");
            //获取预编译sql的对象（已经完成的sql语句的预编译）
            PreparedStatement ps = conn.prepareStatement(sql);
        ){
            //预处理(向sql语句指定位置的占位符填充实际值)
            ps.setString(1,username);
            ps.setString(2,password);
            //执行
            rs = ps.executeQuery();
            if(rs.next()){
                return true;
            }
        }finally{
            rs.close();
        }
        return false;
    }

    public static void main(String[] args) throws SQLException {
        boolean b = login("admin", "123456");
        System.out.println(b ? "登录成功" : "登录失败");
    }
}

ResultSet

结果集对象，用于表示对于查询语句执行之后的结果，内部的数据可能是一条也可以是多条

ResultSet常见方法：

• boolean next() ： 判断是否有下一个结果行
• getXXX(int index)：根据列索引获取列值
• getXXX(String columnLabel)：根据列标签（默认列名）获取值
• ResultSetMetaData getMetaData()：返回结果集的元数据

ResultSet的基本使用：

String sql = "select * from user";
//获取连接
Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "123456");
//获取执行sql语句的对象
PreparedStatement ps = conn.prepareStatement(sql);
//执行查询
ResultSet rs = ps.executeQuery();
while (rs.next()) {
    //根据列索引获取当前列值				 // 根据列标签获取指定列值
    int uid = rs.getInt(1);             // rs.getInt("id")
    String username = rs.getString(2);  // rs.getString("username")
    String password = rs.getString(3);  // rs.getString("password")
    int status = rs.getInt(4);          // rs.getInt("status")
    String nickname = rs.getString(5);  // rs.getString("nickname")

    System.out.println(uid + "/" + username + "/" + password + "/" + status + "/" + nickname);
}
rs.close();
ps.close();
conn.close();

CRUD操作

/**
* 添加数据
*/
public static void insert(String name, String pwd, String nickname) throws ClassNotFoundException, SQLException {
    //准备sql语句
    String sql = "insert into user(username,password,nickname) values(?,?,?)";
    // 1.加载驱动
    Class.forName("com.mysql.jdbc.Driver");
    // 2.获取连接
    Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "123456");
    // 3.获取处理sql语句的执行对象
    PreparedStatement ps = conn.prepareStatement();
    //预处理
    ps.setString(1,name);
    ps.setString(2,pwd);
    ps.setString(3,nickname);
    // 4.执行sql语句
    int i = stat.executeUpdate();
    // 5.处理结果
    System.out.println("受影响代码行：" + i);
    // 6.关闭资源
    stat.close();
    conn.close();
}

/**
 * 根据提供的用户id删除用户
 */
public static boolean delete(int id) throws SQLException {
    String sql = "delete from user where id=?";
    try(
        Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "123456");
        //获取预编译sql的对象（已经完成的sql语句的预编译）
        PreparedStatement ps = conn.prepareStatement(sql);
    ){
        ps.setInt(1,id);
        return ps.executeUpdate() > 0;
    }
}

/**
* 根据提供的用户id修改用户密码为指定值
*/
public static boolean update(int id,String newPwd) throws SQLException {
    String sql = "update user set password=? where id=?";
    try(
        Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "123456");
        PreparedStatement ps = conn.prepareStatement(sql);
    ){
        ps.setString(1,newPwd);
        ps.setInt(2,id);
        return ps.executeUpdate() > 0;
    }
}

/**
* 根据id查询数据
* @param id
* @throws SQLException
 */
public static void queryById(int id) throws SQLException {
    String sql = "select * from user where id=?";
    //获取连接
    Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "123456");
    //获取执行sql语句的对象
    PreparedStatement ps = conn.prepareStatement(sql);
    ps.setInt(1, id);
    //执行查询
    ResultSet rs = ps.executeQuery();
    if (rs.next()) {
        int uid = rs.getInt(1);             // rs.getInt("id")
        String username = rs.getString(2);  // rs.getString("username")
        String password = rs.getString(3);  // rs.getString("password")
        int status = rs.getInt(4);          // rs.getInt("status")
        String nickname = rs.getString(5);  // rs.getString("nickname")

        System.out.println(uid + "/" + username + "/" + password + "/" + status + "/" + nickname);
    }
    rs.close();
    ps.close();
    conn.close();
}

JDBC封装1.0

JDBC操作常见的六个步骤中存在大量的重复操作，通过以下封装可以实现对其中三个步骤完成统一封装处理：

1. 加载驱动
2. 获取连接
3. 获取执行sql语句的对象
4. 执行
5. 处理结果
6. 关闭资源

/**
 * @Author mrchai 2020/12/10 9:16
 */
public class DBUtils {

    /**驱动类路径*/
    private static final String DRIVER_CLASS = "com.mysql.jdbc.Driver";
    /**URL地址*/
    private static final String URL = "jdbc:mysql://localhost:3306/test";
    /**登录数据库服务器的账号*/
    private static final String USER = "root";
    /**登录数据库服务器的密码*/
    private static final String PASSWORD = "123456";

    static{
        try {
            // 1.加载驱动
            Class.forName(DRIVER_CLASS);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 返回数据库连接对象
     * @return
     */
    public static Connection getConn(){
        try {
            return DriverManager.getConnection(URL,USER,PASSWORD);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 关闭资源
     * @param rs    结果集
     * @param stat  处理SQL的执行对象
     * @param conn  数据库连接
     */
    public static void close(ResultSet rs, Statement stat,Connection conn){
        //ctrl+alt+t
        try {
            if(rs != null){
                rs.close();
            }
            if(stat != null){
                stat.close();
            }
            if(conn != null){
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
}

使用以上封装完成基本的查询操作：

String sql = "select * from user";
//通过工具类获取连接
Connection conn = DBUtils.getConn();
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
    int id = rs.getInt("id");
    String username = rs.getString("username");
    String password = rs.getString("password");
    String nickname = rs.getString("nickname");
    int status = rs.getInt("status");
    System.out.println(id + "/" + username + "/" + password + "/" + nickname + "/" + status);
}
//使用工具类关闭资源
DBUtils.close(rs,ps,conn);

JDBC查询详解

单行单列查询

/**
 * 查询单行单列数据
 * @Author mrchai 2020/12/10 10:25
 */
public class Query01 {

    public static void main(String[] args) throws SQLException {
        String sql = "select count(*) as num from user";
        //获取连接
        Connection conn = DBUtils.getConn();
        //获取预处理sql的对象
        PreparedStatement ps = conn.prepareStatement(sql);
        //执行查询
        ResultSet rs = ps.executeQuery();
        if(rs.next()){
            int count = rs.getInt("num");
            //int count = rs.getInt(1);
            System.out.println("总数据条数："+count);
        }
        //关闭资源
        DBUtils.close(rs,ps,conn);
    }
}

分页查询

/**
 * @Author mrchai 2020/12/10 10:30
 */
public class Query02 {

    /**
     *
     * @param offset  查询偏移量（起始查询的数据位置）
     * @param limit   查询结果集限制行（每页最大显示数据行）
     */
    public static List<User> queryLimit(int offset, int limit) throws SQLException {
        List<User> list = new ArrayList<>();
        //参数1：开始查询的位置
        //参数2：查询的数据条数
        String sql = "select * from user limit ?,?";
        Connection conn = DBUtils.getConn();
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1,offset);
        ps.setInt(2,limit);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            int id = rs.getInt("id");
            String username = rs.getString("username");
            String password = rs.getString("password");
            String nickname = rs.getString("nickname");
            int status = rs.getInt("status");
            int gid = rs.getInt("gid");
            //每一次循环，产生一个user对象
            list.add(new User(id,username,password,status,nickname,gid));
        }
        DBUtils.close(rs,ps,conn);
        return list;
    }

    public static void main(String[] args) throws SQLException {
        //真分页（select * from XXX limit ?,?） ：物理分页，从数据库查询多少条显示多少条
        //假分页（select * from XXX）：逻辑分页，一次性将数据全部从数据库查询出来，然后再内存中通过subList截取部分显示

        //当前页码
        int pageNow = 1;
        //每页数据条数
        int pageSize = 10;
        //计算起始查询位置
        int offset = (pageNow - 1) * pageSize;
        List<User> users = queryLimit(offset, pageSize);
        //遍历输出结果
        users.forEach(u-> System.out.println(u));
    }
}

多表联合查询

public class Query03 {

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

        String sql = "select " +
            "u.id _uid," +
            "u.username _username," +
            "u.password _password," +
            "u.nickname _nickname," +
            "u.status _status," +
            "g.id _gid," +
            "g.gname _gname " +
            "from user u left join groups g on u.gid=g.id";

        Connection conn = DBUtils.getConn();
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            int uid = rs.getInt("_uid");
            String username = rs.getString("_username");
            String password = rs.getString("_password");
            String nickname = rs.getString("_nickname");
            int status = rs.getInt("_status");
            int gid = rs.getInt("_gid");
            String gname = rs.getString("_gname");
            System.out.println(uid + "/" + username + "/" + password + "/" + nickname + "/" + status + "/" + gid + "/" + gname);
        }
        DBUtils.close(rs,ps,conn);
    }
}

DAO与DTO(Entity,VO,PO,POJO)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6CCyAHnb-1607845200069)(D:\带班资料\2020\j2009\j2009课程资料\part5-数据库与JDBC\20201210\笔记\assets\1607648152774.png)]

​ 以上时序图是常见的web项目架构模式，其中对于频繁的数据访问操作，实际开发中通常会抽取一层DAO(Data Access Object)层;DAO层主要负责访问数据库，对数据表进行CRUD操作，DAO层并不关注具体的业务逻辑，因此称之为数据访问层。

常见DAO层的定义语法:

public class EmpDAO{

	public boolean insert(Emp d) {
		// 执行添加操作
		return false;
	}

	public boolean delete(int id) {
		// 执行删除操作
		return false;
	}

	public boolean update(Emp d) {
		// 执行更新操作
		return false;
	}

	public Emp findById(int id) {
		// 根据id查询
		return null;
	}
    	
    public List<Emp> findByAll(int id) {
		// 查询所有
		return null;
	}
}

JDBC封装2.0 - 封装通用的更新操作

观察以下操作相关代码：

/**
     * ctrl + shift + -/+ 语句块展开收缩
     * 用户添加
     * @param user
     * @return
     */
public boolean insert(User user){
    String sql = "insert into user_info(uname,upass,sex,birth,phone) values(?,?,?,?,?)";
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        conn = DBUtils.getConn();
        ps = conn.prepareStatement(sql);
        ps.setString(1,user.getUname());
        ps.setString(2,user.getUpass());
        ps.setString(3,user.getSex());
        ps.setDate(4,user.getBirth());
        ps.setString(5,user.getPhone());
        return ps.executeUpdate() > 0;
    } catch (SQLException e) {
        e.printStackTrace();
    }finally{
        DBUtils.close(null,ps,conn);
    }
    return false;
}

/**
     * 根据提供用户信息修改用户密码
     * @param user
     * @return
     */
public boolean updatePwd(User user){
    String sql = "update user_info set upass=? where uid=?";
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        conn = DBUtils.getConn();
        ps = conn.prepareStatement(sql);
        ps.setString(1,user.getUpass());
        ps.setInt(2,user.getUid());
        return ps.executeUpdate() > 0;
    } catch (SQLException e) {
        e.printStackTrace();
    }finally{
        DBUtils.close(null,ps,conn);
    }
    return false;
}

/**
     * 根据提供的用户信息修改用户状态
     * @param user
     * @return
     */
public boolean updateStatus(User user){
    String sql = "update user_info set status=? where uid=?";
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        conn = DBUtils.getConn();
        ps = conn.prepareStatement(sql);
        ps.setInt(1,user.getStatus());
        ps.setInt(2,user.getUid());
        return ps.executeUpdate() > 0;
    } catch (SQLException e) {
        e.printStackTrace();
    }finally{
        DBUtils.close(null,ps,conn);
    }
    return false;
}

/**
     * 根据id删除用户
     * @param uid
     * @return
     */
public boolean deleteByUid(int uid){
    String sql = "delete from user_info where uid=?";
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        conn = DBUtils.getConn();
        ps = conn.prepareStatement(sql);
        ps.setInt(1,uid);
        return ps.executeUpdate() > 0;
    } catch (SQLException e) {
        e.printStackTrace();
    }finally{
        DBUtils.close(null,ps,conn);
    }
    return false;
}

以上代码都是实现对于数据库的更新操作，其中所有功能的实现，除了SQL语句不一样（对应预处理时需要指定的参数随之变化）之外，其余代码基本是相同的，因此以上所有的增删改（insert,update,delte）功能可以通过一个封装方法统一完成:

在DBUtils.java中新增以下方法:

/**
     * 封装通用的更新操作（即通过该方法实现对于任意数据表的insert,update,delete操作）
     * @param sql       需要被执行sql语句
     * @param params    执行sql语句时需要传递进去参数
     * @return          执行结果
     */
public static boolean exeUpdate(String sql,Object... params){
    Connection conn = getConn();
    PreparedStatement ps = null;
    try {
        ps = conn.prepareStatement(sql);
        //当传入的参数不为null时执行预处理
        if(Objects.nonNull(params)){
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i + 1,params[i]);
            }
        }
        //执行更新
        return ps.executeUpdate() > 0;
    } catch (SQLException e) {
        e.printStackTrace();
    } finally{
        DBUtils.close(null,ps,conn);
    }
    return false;
}

通过对以上代码调用，之前的程序可以优化为以下代码：

/**
     * ctrl + shift + -/+ 语句块展开收缩
     * 用户添加
     *
     * @param user
     * @return
     */
public boolean insert(User user) {
    String sql = "insert into user_info(uname,upass,sex,birth,phone) values(?,?,?,?,?)";
    return DBUtils.exeUpdate(sql, user.getUname(), user.getUpass(), user.getSex(), user.getBirth(), user.getPhone());
}

/**
     * 根据提供用户信息修改用户密码
     *
     * @param user
     * @return
     */
public boolean updatePwd(User user) {
    String sql = "update user_info set upass=? where uid=?";
    return DBUtils.exeUpdate(sql, user.getUpass(), user.getUid());
}

/**
     * 根据提供的用户信息修改用户状态
     *
     * @param user
     * @return
     */
public boolean updateStatus(User user) {
    String sql = "update user_info set status=? where uid=?";
    return DBUtils.exeUpdate(sql, user.getStatus(), user.getUid());
}

/**
     * 根据id删除用户
     *
     * @param uid
     * @return
     */
public boolean deleteByUid(int uid) {
    return DBUtils.exeUpdate("delete from user_info where uid=?", uid);
}

代码量瞬间减少，世界如此清净！！！

getGeneratedKeys获取自动生成键

​ 在实际开发中，可能会遇到如下需求：比如商品添加的功能，同时需要涉及到商品图片的上传，此时商品信息和图片信息通常会存储在不同的数据表中，而图片表中的商品图片应该要关联商品表的商品ID，由于数据库商品表中商品id列可设置为自动递增，此时需要为商品图片指定所关联的商品id，因此JDBC中提供了一个功能，允许在添加数据时获取自动生成的键：

/**
     * 向数据库插入数据的同时，获取自动为当前数据行生成的键值（前提是主键列是自增列）
     *
     * @param user
     * @return   返回自动生成的键
     */
public int insertAndGetKeys(User user) {
    String sql = "insert into user_info(uname,upass,sex,birth,phone) values(?,?,?,?,?)";
    Connection conn = DBUtils.getConn();
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        //获取预处理sql的执行对象，并设置其允许获取自动生成的键
        ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        ps.setString(1, user.getUname());
        ps.setString(2, user.getUpass());
        ps.setString(3, user.getSex());
        ps.setDate(4, user.getBirth());
        ps.setString(5, user.getPhone());
        int i = ps.executeUpdate();
        if (i > 0) {
            //获取自动生成的键
            rs = ps.getGeneratedKeys();
            if (rs.next()) {
                return rs.getInt(1);
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        DBUtils.close(rs, ps, conn);
    }
    return -1;
}

SQL语句批处理操作（1）

如何同时执行多条不同类型的sql语句？

public class BatchDemo {

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

        Connection conn = DBUtils.getConn();
        Statement stat = conn.createStatement();
        //添加sql语句到Statement执行队列中
        stat.addBatch("create table tbinfo(id int primary key auto_increment,msg varchar(30) not null)");
        stat.addBatch("insert into tbinfo(msg) values('数据添加')");
        stat.addBatch("insert into tbinfo(msg) values('数据添加')");
        stat.addBatch("update tbinfo set msg='数据更新' where id=1");
        //执行批量操作，返回每一条sql语句执行影响的数据行数
        int[] results = stat.executeBatch();
        for(int i:results){
            System.out.println(i);
        }

    }
}

JDBC高级

JDBC批处理（2）

在实际开发需求中，经常会涉及到将一张excel文件中的所有数据导入到数据库的需求，此时在JDBC中就必然会执行多次添加操作，传统的方式可以对excel文件中的每一条数据进行读取，然后没读取一次执行一次insert操作，但是这种效率十分低，因此JDBC中提供了对于数据批量添加的功能，通过批处理功能可以实现高效的数据导入，如下:

public class BatchDemo2 {

    //传统的添加方式：循环添加
    public static void add1() throws SQLException {
        for (int i = 0; i < 5000; i++) {
            String sql = "insert into tbinfo(msg) values(?)";
            Connection conn = DBUtils.getConn();
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, "数据添加" + i);
            DBUtils.close(null, ps, conn);
        }
    }

    //批处理方式
    public static void add2() throws SQLException {
        String sql = "insert into tbinfo(msg) values(?)";
        Connection conn = DBUtils.getConn();
        PreparedStatement ps = conn.prepareStatement(sql);
        for (int i = 0; i < 5000; i++) {
            ps.setString(1, "数据新增" + i);
            ps.addBatch();
            if (i % 10 == 0) {
                ps.clearBatch();
            }
        }
        int[] r = ps.executeBatch();
        DBUtils.close(null, ps, conn);
    }

    public static void main(String[] args) throws SQLException {
        long start = System.currentTimeMillis();
        //add1();
        add2();
        long end = System.currentTimeMillis();
        System.out.println("耗时：" + (end - start));
    }
}

结果：

// 耗时：6917  //非批处理方式
耗时：469		//批处理

JDBC事务操作

事务即在对数据库表中数据同时进行多次更新操作时，要么同时成功，要么同时失败，这一整个逻辑操作称之为事务，事务的四大特征（ACID）:

• 原子性
• 一致性
• 隔离性
• 持久性

在没有引入事务时可能出现以下问题：

public class TxDemo {

    /**
     * 从指定账号向另一个账号转出指定金额
     * @param a 账号a
     * @param b 账号b
     */
    public static void change(int a, int b){
        String sql = "update account set money=money-1000 where id=?";
        //减少
        DBUtils.exeUpdate(sql,a);
        //抛出一个算术异常
        System.out.println(10/0);
        //增加
        sql = "update account set money=money+1000 where id=?";
        DBUtils.exeUpdate(sql,b);
    }

    public static void main(String[] args) {
        change(1,2);
    }
}

以上程序为一个简易的转账逻辑，最终结果会导致一个账号中金额减少，但是另一个账号中金额没有增加，从而最终结果不一致。

JDBC中使用事务

/**
     * 从指定账号向另一个账号转出指定金额
     */
public static void change(){
    String sql1 = "update account set money=money-1000 where id=1";
    String sql2 = "update account set money=money+1000 where id=2";
    Connection conn = DBUtils.getConn();
    PreparedStatement ps = null;
    try {
        //关闭事务的自动提交
        conn.setAutoCommit(false);
        ps = conn.prepareStatement(sql1);
        int i = ps.executeUpdate();
        //            System.out.println(10/0);
        ps = conn.prepareStatement(sql2);
        int j = ps.executeUpdate();
        if(i > 0 && j > 0){
            //提交事务
            conn.commit();
        }
    } catch (Exception e) {
        e.printStackTrace();
        try {
            //事务回滚
            conn.rollback();
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
    }finally{
        DBUtils.close(null,ps,conn);
    }
}

DBUtils中新增通用更新重载方法

保证多次更新在同一个事务中的前提是必须使所有更新操作都使用同一个数据库连接，因此exeUpdate方法中不能每次都获取一个新的连接，我们可以设置为从外部传入连接，这样一来只要调用该方法时传入的是同一个Connection即可进行事务管理：

/**
     * 实现通用更新操作的方法，连接是需要外部提供的，事务可以在外部操作
     * 注意事项：该方法中出现异常必须抛出不能捕获，否则一旦出现异常，在当前方法就已经被catch，
     * 从而导致下一级调用者无法发现该异常，而导致事务无法回滚
     * @param conn
     * @param sql
     * @param params
     * @return
     */
public static boolean exeUpdate(Connection conn,String sql,Object... params) throws SQLException {
    PreparedStatement ps = null;
    try {
        ps = conn.prepareStatement(sql);
        //当传入的参数不为null时执行预处理
        if(Objects.nonNull(params)){
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i + 1,params[i]);
            }
        }
        //执行更新
        return ps.executeUpdate() > 0;
    } finally{
        DBUtils.close(null,ps,null);
    }
}

使用：

public static void change2(){
    String sql1 = "update account set money=money-1000 where id=1";
    String sql2 = "update account set money=money+1000 where id=2";
    Connection conn = DBUtils.getConn();
    try {
        //关闭事务自动提交
        conn.setAutoCommit(false);
        DBUtils.exeUpdate(conn,sql1);
        //异常代码
        System.out.println(10/0);
        DBUtils.exeUpdate(conn,sql2);
        //提交事务
        conn.commit();
    } catch (Exception e) {
        e.printStackTrace();
        try {
            //事务回滚
            conn.rollback();
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
    }finally{
        DBUtils.close(null,null,conn);
    }
}

数据库连接池技术

​ 在之前进行任何数据库操作的时候都必须要有一个可用的数据库连接对象（Connection）；而这个连接对象的获取需要通过：DriverManager.getConnection(url,user,password)并且连接的获取是非常耗时(0.05~1s之间)的操作。因此传统的连接获取方式不满足现在的高并发需求场景的，所以JDBC中引入了一种称之为连接池(Connection Pool)的技术

​ 连接池的原理即，在程序启动时，事先开启并通过容器维护一定量的数据连接，从而为后续的数据持久化功能节省连接获取的时间开销，因为一旦dao需要使用连接时，只需要从连接池中直接获取即可（类似从一个集合中取一个元素），并且使用完毕之后只需将连接归还到连接池中即可，从程序的访问效率上得到极大提升，因此，连接池技术，是服务端技术的重要组成部分。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2udNbu6x-1607845200074)(D:\带班资料\2020\j2009\j2009课程资料\part5-数据库与JDBC\20201211\笔记\assets\1607657203007.png)]

目前市面上提供了各种丰富的连接池解决方案：

• DBCP/DBCP2
• C3P0
• Proxool
• Druid(德鲁伊：alibaba)
• HikraCP(SpringBoot推荐)

任何连接池的实现都必须要从javax.sql.DataSource进行实现

使用Druid连接池

1. 添加依赖

   druid-1.1.9.jar

2. 连接池创建并获取连接

   //创建连接池对象
   DruidDataSource dataSource = new DruidDataSource();
   //设置连接数据库的基本信息（url,user,password）
   //        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
   dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/test");
   dataSource.setUsername("root");
   dataSource.setPassword("123456");
   //设置初始连接数
   dataSource.setInitialSize(2);
   //设置最大连接数
   dataSource.setMaxActive(5);
   //设置连接获取的最长等待时间（如果连接池中连接被耗尽时，等待重新获取连接的时间超出时会抛出异常）
   dataSource.setMaxWait(5000);
   //设置最小的闲置连接数
   dataSource.setMinIdle(1);
   
   //获取连接
   Connection conn1 = dataSource.getConnection();
   

改造DBUtils

public class DBUtils2 {

    /**驱动类路径*/
    private static final String DRIVER_CLASS = "com.mysql.jdbc.Driver";
    /**URL地址*/
    private static final String URL = "jdbc:mysql://localhost:3306/test";
    /**登录数据库服务器的账号*/
    private static final String USER = "root";
    /**登录数据库服务器的密码*/
    private static final String PASSWORD = "123456";
    /**初始连接数*/
    private static final int INIT_SIZE = 1;
    /**最大活动连接数*/
    private static final int MAX_ACTIVE = 5;
    /**最长等待连接获取时间*/
    private static final long MAX_WAIT = 5000;
    /**最小闲置连接数*/
    private static final int MIN_IDLE = 1;

    /**声明数据源*/
    private static DruidDataSource dataSource;

    static{
        dataSource = createDataSource();
    }

    /**
     * 创建连接池并返回
     * @return
     */
    private static DruidDataSource createDataSource(){
        //创建连接池对象
        dataSource = new DruidDataSource();
        //设置连接数据库的基本字符串
        dataSource.setUrl(URL);
        dataSource.setUsername(USER);
        dataSource.setPassword(PASSWORD);
        //设置连接池的初始参数
        dataSource.setInitialSize(INIT_SIZE);
        dataSource.setMaxActive(MAX_ACTIVE);
        dataSource.setMaxWait(MAX_WAIT);
        dataSource.setMinIdle(MIN_IDLE);
        return dataSource;
    }

    /**
     * 返回数据库连接对象
     * @return
     */
    public static Connection getConn(){
        try {
            //当连接池对象为null或者连接池对象被关闭时重新创建连接池
            if(dataSource == null || dataSource.isClosed()){
                dataSource = createDataSource();
            }
            //返回连接
            return dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    
}

封装通用查询

ResultSetMetaData