JDBC进阶

实体类和ORM

实体类

在使用JDBC操作数据库是，我们会发现数据都是零散的，明明在数据库中是一行完整的数据，到了Java中变成了一个一个变量，不利于维护和管理

所以我们把表的设计转换成一个类，每一行都是一个对象，每一列是对象的其中的一个方法

这个类就是实体类

ORM

ORM（Object Relational Mapping），对象到关系数据库的映射思想

目前的阶段我们称之为手动ORM，后续我们回学习一些ORM框架，比如MyBatis、JPA等

package com.xiaobai.advanced;

import com.xiaobai.pojo.User;
import org.junit.Test;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class JDBCAdvanced {
    //使用ORM思想封装单个对象
    @Test
    public void testORM() throws SQLException {
        Connection connection = DriverManager.getConnection("jdbc:mysql://192.168.2.2:3306/xiaobai", "root", "Zhuwenxue2002");
        PreparedStatement preparedStatement = connection.prepareStatement("select * from user where u_id=?");
        preparedStatement.setInt(1, 1);//给占位符赋值
        ResultSet resultSet = preparedStatement.executeQuery();

        User user = null;
        if (resultSet.next()) {
            user = new User();
            int id = resultSet.getInt("u_id");
            String name = resultSet.getString("u_name");
            Double salary = resultSet.getDouble("u_salary");
            int age = resultSet.getInt("u_age");

            user.setU_id(id);
            user.setU_name(name);
            user.setU_salary(salary);
            user.setU_age(age);

            resultSet.close();
            preparedStatement.close();
            connection.close();
        }

    }

    //使用ORM思想封装多个对象（集合）
    @Test
    public void testORMList() throws SQLException {
        Connection connection = DriverManager.getConnection("jdbc:mysql://192.168.2.2:3306/xiaobai", "root", "Zhuwenxue2002");
        PreparedStatement preparedStatement = connection.prepareStatement("select * from user");
        ResultSet resultSet = preparedStatement.executeQuery();

        User user  = null;
        List<User> userList = new ArrayList<>();

        while (resultSet.next()) {
            user = new User();
            int id = resultSet.getInt("u_id");
            String name = resultSet.getString("u_name");
            Double salary = resultSet.getDouble("u_salary");
            int age = resultSet.getInt("u_age");

            user.setU_id(id);
            user.setU_name(name);
            user.setU_salary(salary);
            user.setU_age(age);
            //将每次循环封装的一行数据的对象加入到集合里
            userList.add(user);
        }

        //遍历集合
        for (User user1 : userList) {
            System.out.println(user1);
        }

        resultSet.close();
        preparedStatement.close();
        connection.close();
    }
}

主键回显

在预编译SQL语句时，告知客户端在插入一条数据时记得返回主键值

PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

使用预编译对象获取到主键

ResultSet resultSet = preparedStatement.getGeneratedKeys();

注：此方法的返回值是个单行单列的result结果集，用索引的方式获取即可

结果集需要释放资源！！！

package com.xiaobai.advanced;

import com.xiaobai.pojo.User;
import org.junit.Test;

import java.sql.*;

public class JDBCReturnPK {
    @Test
    public void testReturnPK() throws SQLException {
        Connection connection = DriverManager.getConnection("jdbc:mysql://192.168.2.2:3306/xiaobai", "root", "Zhuwenxue2002");
        String sql = "INSERT INTO `user`(u_name,u_salary,u_age) VALUES(?,?,?)";
        //告知PreparedStatement  记得回填主键列
        PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

        User user = new User(0, "xiaobai", 5000.00, 18);

        preparedStatement.setString(1, user.getU_name());
        preparedStatement.setDouble(2, user.getU_salary());
        preparedStatement.setInt(3, user.getU_age());
        int i = preparedStatement.executeUpdate();
        //既然时结果集，那么就需要释放该资源，放在if中无法释放资源则在if外提前声明
        ResultSet resultSet = null;

        if (i > 0) {
            System.out.println("成功");
            //返回的主键值是一个单行单列的结果
            resultSet = preparedStatement.getGeneratedKeys();
            if (resultSet.next()) {
                int anInt = resultSet.getInt(1);
                user.setU_id(anInt);
            }
            System.out.println(user);
        } else {
            System.out.println("失败");
        }
        
        //判断结果集非空才释放资源，否则有可能报空指针异常
        if (resultSet!=null){
            resultSet.close();
        }
        preparedStatement.close();
        connection.close();
    }

}

批量操作

在连接数据库时的链接后加入参数：?rewriteBatchedStatements=true 即允许批量传数据给数据库

Connection connection = DriverManager.getConnection("jdbc:mysql://192.168.2.2:3306/xiaobai?rewriteBatchedStatements=true", "root", "Zhuwenxue2002");

在使用批量操作时，预编译的SQL语句要以values语句操作，并且语句结束一定不能加；

• 调用addBatch()方法，将数据值批量添加到预编译语句中

• 调用executeBatch()方法，统一提交

package com.xiaobai.advanced;

import org.junit.Test;

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

public class JDBCMoreInsert {
    @Test
    public void testMoreInsert() throws SQLException {
        Connection connection = DriverManager.getConnection("jdbc:mysql://192.168.2.2:3306/xiaobai?rewriteBatchedStatements=true", "root", "Zhuwenxue2002");
        PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO `user`(u_name,u_salary,u_age) VALUES(?,?,?)");

        preparedStatement.setString(1,"xiaoma");
        preparedStatement.setDouble(2,3124.23);
        preparedStatement.setInt(3,18);
        preparedStatement.addBatch();
        preparedStatement.setString(1,"xiaolong");
        preparedStatement.setDouble(2,4124.23);
        preparedStatement.setInt(3,28);
        preparedStatement.addBatch();
        preparedStatement.setString(1,"xiaozhu");
        preparedStatement.setDouble(2,5323.09);
        preparedStatement.setInt(3,16);
        preparedStatement.executeBatch();

        preparedStatement.close();
        connection.close();
    }
}