JDBC初级学习 ------（师承尚硅谷）

JDBC

前言：看懂项目—学习java反射！还要学会maven基础！
去尚硅谷下载宋红康老师的资料学习。。

1. Driver接口-连接数据库（5种方式，最后一种最重要）

首先你要学会用maven创建模块。再次不做过多赘述了。
几点注意事项：

mysql和对应的驱动版本要一致（我的mysql8.0.29，驱动也是如此）
关于url后面要不要加入代码注释的内容看情况，作用是（驱动和mysql的不一致导致有的参数要变，具体实践具体查去）

上代码：

package com.jdbc;

import com.mysql.cj.jdbc.Driver;
import com.mysql.cj.jdbc.integration.c3p0.MysqlConnectionTester;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class Driverconnect {

    //方式一：最普通的
    @Test
    public void testConnection1() throws SQLException {
        Driver driver = new Driver();

        String url = "jdbc:mysql://localhost:3306/jdbc";
        Properties info = new Properties();
        info.setProperty("user", "root");
        info.setProperty("password", "123456");

        Connection connect = driver.connect(url, info);
        System.out.println(connect);
    }

    //方式二--进阶 用反射（方式一第一步还不够模块化）
    @Test
    public void testConnection2() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
        //1. 获取Driver实现类对象，使用反射
        Class aClass = Class.forName("com.mysql.cj.jdbc.Driver");
        Driver driver = (Driver) aClass.newInstance();

        //?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
        //2.连接数据库,用户名和密码
        String url = "jdbc:mysql://localhost:3306/jdbc";
        Properties info = new Properties();
        info.setProperty("user", "root");
        info.setProperty("password", "123456");

        //3.获取连接
        Connection connection = driver.connect(url, info);
        System.out.println(connection);
    }


    //方式三：用DriverManager实现数据库的连接。体会获取连接必要的4个基本要素。
    @Test
    public void testConnection() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
        //?useSSL=false&useUnicode=true&characterEncoding=utf-8&useLegacyDatetimeCode=false&serverTimezone=UTC
        //1.四个要素
        String url = "jdbc:mysql://localhost:3306/jdbc?";
        String user = "root";
        String password = "123456";
        String driverName = "com.mysql.cj.jdbc.Driver";

        //2.实例化Driver
        Class aClass = Class.forName(driverName);
        Driver driver = (Driver) aClass.newInstance();

        //3.注册Driver驱动
        DriverManager.registerDriver(driver);

        //4.获取连接
        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println(connection);
    }

    //方式四：-可省略版
    @Test
    public void connnection4() throws ClassNotFoundException, SQLException {
        //1.需要的四个要素
        String url = "jdbc:mysql://localhost:3306/jdbc";
        String user = "root";
        String password = "123456";
        String driverClass = "com.mysql.cj.jdbc.Driver";//接口位置

        //2.加载驱动
        Class.forName(driverClass);

        //3.获取连接
        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println(connection);

    }

    //方式五 --final最终版本 --加载配置文件的方法
    @Test
    public void finalconnection() throws IOException, ClassNotFoundException, SQLException {

        //1. 加载配置文件
        InputStream is = Driverconnect.class.getClassLoader().getResourceAsStream("properties");
//        --Driverconnect 当前类
//         * --Driverconnect.class.getClassLoader() 该加载器是系统加载器，--自定义的类都是系统加载器
//         * --getResourceAsStream指定文件路径 默认是src下
        Properties properties = new Properties();
        properties.load(is);

        //2.读取配置信息
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driverClass = properties.getProperty("driverClass");

        //3.加载驱动
        Class.forName(driverClass);

        //4.获取连接
        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println(connection);
    }
}
/
    @Test
    public void testInsert() throws IOException, ClassNotFoundException, SQLException, ParseException {
        //1. 加载配置文件
        InputStream is = Driverconnect.class.getClassLoader().getResourceAsStream("properties");
        Properties properties = new Properties();
        properties.load(is);

        //2.读取配置信息
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driverClass = properties.getProperty("driverClass");

        //3.加载驱动
        Class.forName(driverClass);

        //4.获取连接
        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println(connection);

        //5.预编译sql语句
        String sql = "insert into customers(name,email,birth)values(?,?,?)";//?是占位符的意思
        PreparedStatement ps = connection.prepareStatement(sql);
        //6.填充占位符
        ps.setString(1, "文大牛");
        ps.setString(2, "wts@666.com");
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        Date date = simpleDateFormat.parse("2001-2-28");
        ps.setDate(3, new java.sql.Date(date.getTime()));

        //7.执行
        ps.execute();

        //8.关闭
        ps.close();
        connection.close();
    }
}

效果如下：

由此引出通用代码：

首先是固定的连接数据库和关闭操作的部分
上代码：

package com.statement;


import com.jdbc.Driverconnect;

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

public class JDBCUtils {

    
    public static Connection getConnection() throws IOException, ClassNotFoundException, SQLException {
        //1. 加载配置文件
        InputStream is = Driverconnect.class.getClassLoader().getResourceAsStream("properties");
        Properties properties = new Properties();
        properties.load(is);

        //2.读取配置信息
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driverClass = properties.getProperty("driverClass");

        //3.加载驱动
        Class.forName(driverClass);

        //4.获取连接
        Connection connection = DriverManager.getConnection(url, user, password);
        return connection;
    }

    /

import com.jdbc.Driverconnect;

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

public class JDBCUtils {

    
    public static Connection getConnection() throws IOException, ClassNotFoundException, SQLException {
        //1. 加载配置文件
        InputStream is = Driverconnect.class.getClassLoader().getResourceAsStream("properties");
        Properties properties = new Properties();
        properties.load(is);

        //2.读取配置信息
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driverClass = properties.getProperty("driverClass");

        //3.加载驱动
        Class.forName(driverClass);

        //4.获取连接
        Connection connection = DriverManager.getConnection(url, user, password);
        return connection;
    }

    /
            for (int i = 0; i < columnCount; i++) {
                Object value = rs.getObject(i + 1);//得到列的值 任然是从1开始的！

                //获取结果集的列名
                String columnName = rsmd.getColumnName(i + 1);

                //反射-- 给cust对象 指定的columnName属性，赋值value：通过反射
                Field declaredField = Customer.class.getDeclaredField(columnName);
                declaredField.setAccessible(true);
                declaredField.set(customer, value);
            }
            return customer;
        }

        //资源的关闭--方法重载
        JDBCUtils.closeResource(connection, ps, rs);
        return null;
    }

4.测试+效果：

    @Test   //查询通用测试
    public void testQuery() throws SQLException, IOException, NoSuchFieldException, ClassNotFoundException, IllegalAccessException {
        String sql = "select id, name, email, birth from customers where id = ?";
        Customer customer = queryForCustomers(sql, 21);
        System.out.println(customer);
    }

通用 — 查询 — 针对Order表

需要注意的是，order作为一个敏感词汇（对于sql来说），如何处理？
order_id，order_name,order_date用的别称orderId,orderName,orderDate如何处理？

上代码：
1.Order类（ORM编程思维）：需要注意的是这里的属性已更换别名

package com.statement;

import java.sql.Date;


public class Order {
    private int orderId;
    private String orderName;
    private Date orderDate;

    public Order() {
    }

    public Order(int orderId, String orderName, Date orderDate) {
        this.orderId = orderId;
        this.orderName = orderName;
        this.orderDate = orderDate;
    }

    public int getOrderId() {
        return orderId;
    }

    public void setOrderId(int orderId) {
        this.orderId = orderId;
    }

    public String getOrderName() {
        return orderName;
    }

    public void setOrderName(String orderName) {
        this.orderName = orderName;
    }

    public Date getOrderDate() {
        return orderDate;
    }

    public void setOrderDate(Date orderDate) {
        this.orderDate = orderDate;
    }

    @Override
    public String toString() {
        return "Order{" +
                "orderId=" + orderId +
                ", orderName='" + orderName + ''' +
                ", orderDate=" + orderDate +
                '}';
    }
}

2.order表的通用查询 + 验证

    
    public Order queryForOrder(String sql, Object... args) throws SQLException, IOException, ClassNotFoundException, NoSuchFieldException, IllegalAccessException {
        //1.获取连接
        Connection connection = JDBCUtils.getConnection();

        //2.设置占位符,预编译sql语句
        PreparedStatement ps = connection.prepareStatement(sql);
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]); //第i+1个占位符(从1开始计)--对应args[i]（从0开始计数）
        }

        //3.执行
        ResultSet rs = ps.executeQuery();

        //4.处理结果集
        //获取元数据
        ResultSetMetaData rsmd = rs.getMetaData();
        //获取列数
        int columnCount = rsmd.getColumnCount();

        if (rs.next()) {
            Order order = new Order();
            for (int i = 0; i < columnCount; i++) {
                Object value = rs.getObject(i + 1);//获取列的值，（从1开始计
                //获取列的别名 ---- 这里区别于获取表的列名，而是获取表的别名。。
                String columnLabel = rsmd.getColumnLabel(i + 1);
                //通过反射（动态加载）是实现：给order对象中的（别名）属性，赋值value
                Field declaredField = Order.class.getDeclaredField(columnLabel);
                declaredField.setAccessible(true);
                declaredField.set(order, value);
            }
            return order;
        }
        return null;
    }

    @Test   //测试order表的通用查询
    public void testQueryForOrder() throws SQLException, IOException, NoSuchFieldException, ClassNotFoundException, IllegalAccessException {
        String sql = "select order_id orderId , order_name orderName, order_date orderDate from `order` where order_id = ?";//注意这里的sql语句中order用``符号框出，避免敏感词
        Order order = queryForOrder(sql, 2);
        System.out.println(order);

    }

效果：

小结

总结和对比：
针对某一个特定表格的通用查询。

对于某个表而言，不知道要查询几个字段，所以用到了rs.getMetaDate()获取字段数
用类对象的方式呈现查询的结果，通过反射动态加载
用rsdm.getColumnLabel(i+1)更靠谱，应对类的属性名和表的字段名不一致的问题（order表）
反射

通用 — 泛型模板

通过泛型搞定任意一个表的通用查询

上代码：

    
    public  T queryForAnyone(Class clazz, String sql, Object... args) throws SQLException, IOException, ClassNotFoundException, IllegalAccessException, NoSuchFieldException, InstantiationException {
        //1.获取连接
        Connection connection = JDBCUtils.getConnection();

        //2.设置占位符,预编译sql语句
        PreparedStatement ps = connection.prepareStatement(sql);
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]); //第i+1个占位符(从1开始计)--对应args[i]（从0开始计数）
        }

        //3.执行
        ResultSet rs = ps.executeQuery();

        //4.处理结果集
        //获取元数据
        ResultSetMetaData rsmd = rs.getMetaData();
        //获取列数
        int columnCount = rsmd.getColumnCount();

        if (rs.next()) {
            //创建对象
            T t = clazz.newInstance();
            for (int i = 0; i < columnCount; i++) {
                Object value = rs.getObject(i + 1);//获取列的值，（从1开始计
                //获取列的别名
                String columnLabel = rsmd.getColumnLabel(i + 1);
                //通过反射（动态加载）是实现：给order对象中的（别名）属性，赋值value
                Field declaredField = clazz.getDeclaredField(columnLabel);
                declaredField.setAccessible(true);
                declaredField.set(t, value);
            }
            return t;
        }
        return null;
    }

    @Test //测试上述代码 -- 泛型
    public void testQueryForAnyone() throws SQLException, IOException, NoSuchFieldException, ClassNotFoundException, IllegalAccessException, InstantiationException {
        String sql1 = "select name , email , birth from customers where id = ?";
        Object result1 = queryForAnyone(Customer.class, sql1, 1);
        System.out.println(result1);

        String sql2 = "select order_id orderId, order_name orderName, order_date orderDate from `order` where order_id = ?";
        Order result2 = queryForAnyone(Order.class, sql2, 1);
        System.out.println(result2);

    }

效果：

通用–泛型查询–多条数据

if改成while，用list查收

上代码：

    
    public  List getQueryList(Class clazz, String sql, Object... args) throws SQLException, IOException, ClassNotFoundException, InstantiationException, IllegalAccessException, NoSuchFieldException {
        //1.获取连接
        Connection connection = JDBCUtils.getConnection();

        //2.设置占位符,预编译sql语句
        PreparedStatement ps = connection.prepareStatement(sql);
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]); //第i+1个占位符(从1开始计)--对应args[i]（从0开始计数）
        }

        //3.执行
        ResultSet rs = ps.executeQuery();

        //4.处理结果集
        //获取元数据
        ResultSetMetaData rsmd = rs.getMetaData();
        //获取列数
        int columnCount = rsmd.getColumnCount();
        //创建list集合
        ArrayList list = new ArrayList<>();

        while (rs.next()) {
            //创建对象
            T t = clazz.newInstance();
            for (int i = 0; i < columnCount; i++) {
                Object value = rs.getObject(i + 1);//获取列的值，（从1开始计
                //获取列的别名
                String columnLabel = rsmd.getColumnLabel(i + 1);
                //通过反射（动态加载）是实现：给order对象中的（别名）属性，赋值value
                Field declaredField = clazz.getDeclaredField(columnLabel);
                declaredField.setAccessible(true);
                declaredField.set(t, value);
            }
            list.add(t);
        }
        return list;
    }

    @Test //-猜测是上述代码， --- 泛型查询多条数据
    public void testGetQueryList() throws SQLException, IOException, NoSuchFieldException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        String sql1 = "select name, email, birth from customers where id < ?";
        List result1 = getQueryList(Customer.class, sql1, 4);
        result1.forEach(System.out::println);//浪木塔表达式--说实话有点忘记了淦！只要记住是列表一行行输出就行。。

        String sql2 = "select order_id orderId, order_name orderName, order_date orderDate from `order` where order_id < ?";
        List result2 = getQueryList(Order.class, sql2, 4);
        result2.forEach(System.out::println);
    }

效果：

课后练习：

1.向customers表格插入一条数据（键输入）

上代码：

   //通用的-增删改-操作 ---适合任何一张表格
    public int update(String sql, Object... args) throws SQLException, IOException, ClassNotFoundException {
        //!!!需要注意的是：sql里面占位符? == 可变参数数组args[]长度相同

        //1.获取连接
        Connection connection = JDBCUtils.getConnection();
        //2.预编译sql语句
        PreparedStatement ps = connection.prepareStatement(sql);
        //3.填充占位符
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]);
        }
        //4.执行
        // ps.execute()
        int n = ps.executeUpdate(); //这里的n表示n条数据有改动，0则没有变化
        //5.关闭
        JDBCUtils.closeResource(connection, ps);

        return n;
    }

    
    @Test   //----练习1 插入数据
    public void test1_AddOnetoCustomers() throws SQLException, IOException, ClassNotFoundException {
        Scanner sc = new Scanner(System.in);
        System.out.print("输入name:");
        String name = sc.nextLine();
        System.out.print("输入email:");
        String email = sc.nextLine();
        System.out.println("输入birth:");
        String birth = sc.nextLine();

        String sql1 = "insert into customers (name, email, birth) values(?,?,?)";
        int i = update(sql1, name, email, birth);

        if (i!=0){
            System.out.println("添加成功！");
        }else {
            System.out.println("添加失败！");
        }
    }

过程问题：关于idea中，@Test中的键盘键输入导致的一直阻塞的问题
参考 IDEA单元测试一直转圈，阻塞，控制台无法键入数据

解决方法：

效果：

2.添加四六级成绩
表格examstudent（FlowID设置为主键,not null,自增）

上代码：

 
    @Test
    public void test2_AddGrade() throws SQLException, IOException, ClassNotFoundException {
        Scanner sc = new Scanner(System.in);
        System.out.print("四级/六级：");
        int Type = sc.nextInt();
        System.out.print("学号：");
        String IDCard = sc.next();
        System.out.print("准考证号：");
        String ExamCard = sc.next();
        System.out.print("姓名：");
        String StudentName = sc.next();
        System.out.print("地区：");
        String Location = sc.next();
        System.out.print("分数：");
        int Grade = sc.nextInt();

        String sql = "insert into examstudent(type,idcard,examcard,studentname,location,grade) values(?,?,?,?,?,?)";
        int i = update(sql, Type, IDCard, ExamCard, StudentName, Location, Grade);
        if (i != 0) {
            System.out.println("添加成功！");
        } else {
            System.out.println("添加失败！");
        }
    }

效果：

3.输入学号/准考证号，查询学生信息

上代码：
Student类：

package com.statement;

public class Student {
    private int FlowID;
    private int Type;
    private String IDCard;
    private String ExamCard;
    private String StudentName;
    private String Location;
    private int Grade;

    public Student() {
    }

    public Student(int flowID, int type, String IDCard, String examCard, String studentName, String location, int grade) {
        FlowID = flowID;
        Type = type;
        this.IDCard = IDCard;
        ExamCard = examCard;
        StudentName = studentName;
        Location = location;
        Grade = grade;
    }

    public int getFlowID() {
        return FlowID;
    }

    public void setFlowID(int flowID) {
        FlowID = flowID;
    }

    public int getType() {
        return Type;
    }

    public void setType(int type) {
        Type = type;
    }

    public String getIDCard() {
        return IDCard;
    }

    public void setIDCard(String IDCard) {
        this.IDCard = IDCard;
    }

    public String getExamCard() {
        return ExamCard;
    }

    public void setExamCard(String examCard) {
        ExamCard = examCard;
    }

    public String getStudentName() {
        return StudentName;
    }

    public void setStudentName(String studentName) {
        StudentName = studentName;
    }

    public String getLocation() {
        return Location;
    }

    public void setLocation(String location) {
        Location = location;
    }

    public int getGrade() {
        return Grade;
    }

    public void setGrade(int grade) {
        Grade = grade;
    }

    @Override
    public String toString() {
        return "n------------n" +
                "n Type=" + Type +
                "n IDCard='" + IDCard +
                "n ExamCard='" + ExamCard +
                "n StudentName='" + StudentName +
                "n Location='" + Location +
                "n Grade=" + Grade;
    }
}

查询操作：

   
    @Test
    public void test3_QueryForStudent() throws SQLException, IOException, NoSuchFieldException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        System.out.println("a:学号查询");
        System.out.println("b:准考证号查询");
        System.out.print("(a/b):");
        Scanner sc = new Scanner(System.in);
        String k = sc.next();
        if ("a".equals(k)) {
            System.out.print("输入学号：");
            String IDCard = sc.next();

            String sql = "select FlowID, Type, IDCard, ExamCard, StudentName, Location, Grade from examstudent where IDCard = ?";
            List queryList = getQueryList(Student.class, sql, IDCard);
            queryList.forEach(System.out::println);
            
        } else if ("b".equals(k)) {
			//不高兴写了，淦！
        } else {
            System.out.println("输入错误！");
        }
    }

    /
    @Test
    public void test3_Delete() throws SQLException, IOException, ClassNotFoundException {
        System.out.println("输入需要删除的学号（IDCard）：");
        Scanner sc = new Scanner(System.in);
        String IDCard = sc.next();

        String sql = "delete from examstudent where IDCard = ?";
        int i = update(sql, IDCard);
        if (i!=0){
            System.out.println("删除成功。");
        }else {
            System.out.println("查无此人，删除失败！");
        }
    }

update代码上述有，不多赘述。

效果：

小结：

7.13复习中，JDBCUtils中没有写static
JDBCUtils_re.class.getClassLoader().getResourceAsStream(“properties”) 忘记写了

2.3 Blob大型数据类型

大型数据就没有通用的方法了，最基本的插入来。

1.添加图片 — 大型数据的添加
上代码：

    
    @Test
    public void BlobAddImage() throws SQLException, IOException, ClassNotFoundException {
        //1.获取连接
        Connection connection = JDBCUtils.getConnection();

        //2.预编译 + 填占位符
        String sql = "insert into customers(name,email,birth,photo) value (?,?,?,?)";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setObject(1, "文二牛");
        ps.setObject(2, "wenerniu@666.com");
        ps.setDate(3, null);

        FileInputStream fis = new FileInputStream("G:\1_Program\java_数据结构\代码\JDBC\src\test\java\com\statement\wen.png");
        ps.setObject(4, fis);

        //3.执行
        ps.execute();

        //4.
        fis.close();
        JDBCUtils.closeResource(connection, ps);
    }

效果：使用blob添加图片到数据库

2.查询图片 — 从数据库中下载图片

上代码：

    
    @Test
    public void BlobDownLoad() throws SQLException, IOException, ClassNotFoundException {
        Connection connection = JDBCUtils.getConnection();

        String sql = "select id, name, email, birth, photo from customers where id = ?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setInt(1, 16);

        ResultSet rs = ps.executeQuery();

        //4.处理结果集
        if (rs.next()) {
            int id = rs.getInt("id");
            String name = rs.getString("name");
            String email = rs.getString("email");
            Date birth = rs.getDate("birth");

            Customer customer = new Customer(id, name, email, (java.sql.Date) birth);
            System.out.println(customer);

            //读取blob类型的字段
            Blob photo = rs.getBlob("photo");
            InputStream is = photo.getBinaryStream();//核心
            OutputStream os = new FileOutputStream("G:\1_Program\java_数据结构\代码\JDBC\src\test\java\com\statement\zhuyin.jpg");
            byte[] buffer = new byte[1024];
            int len = 0;
            while ((len = is.read(buffer))!= -1){
                os.write(buffer,0,len);
            }

            //关闭
            JDBCUtils.closeResource(connection,ps,rs);
            is.close();
            os.close();
        }
    }

效果：

3.批量插入

PreparedStatement好就好在会预编译sql语句

一步一步优化代码:
高效批量插入

数据库提供一个goods表格

CREATE TABLE goods( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAr(20) );

实现层次一：使用Statement

        Connection conn = JDBCUtils.getConnection();
        Statement st = conn.createStatement();
        for (int i = 1; i <= 20000; i++) {
            String sql = "insert into goods(name) values('name_' + " + i + ")";
            st.executeUpdate(sql);
        }

实现层次二：使用PreparedStatement

        long start = System.currentTimeMillis();
        Connection conn = JDBCUtils.getConnection();
        String sql = "insert into goods(name)values(?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        for (int i = 1; i <= 20000; i++) {
            ps.setString(1, "name_" + i);
            ps.executeUpdate();
        }
        long end = System.currentTimeMillis();
        System.out.println("花费的时间为：" + (end - start));
		JDBCUtils.close.......
    }

ok分析一二：

PreparedStatement存在预编译
二，存在缓存的。相比之下，一，每次都要执行前都要运行sql语句；然后，二，有缓存，就可以少用点资源（大白话分析，淦）

现在起飞
实现层次三：

    
    @Test
    public void AddBigData() throws SQLException, IOException, ClassNotFoundException {

        long start = System.currentTimeMillis();
        Connection connection = JDBCUtils.getConnection();
        //设置不自动提交
        connection.setAutoCommit(false);

        String sql = "insert into goods(name) values(?)";
        PreparedStatement ps = connection.prepareStatement(sql);

        for (int i = 0; i <= 1000000; i++) {
            ps.setObject(1, "name" + i);

            //1.赞”sql“
            ps.addBatch();
            if (i % 1000 == 0) {
                //2.执行
                ps.executeBatch();
                //3.清空
                ps.clearBatch();
            }
        }

        //提交数据
        connection.commit();

        long end = System.currentTimeMillis();
        System.out.println("耗时：" + (end - start));

        JDBCUtils.closeResource(connection,ps);
    }

效果：

上述总结和补充

首先Statement – vs – PreparedStatement：

Statement就是来传送sql语句到数据库进行操作的。
但，存在两个弊端：繁琐+sql注入问题。
PreparedStatement是Statement子接口。 PreparedStatement 用到的占位符，预编译。

补：（会对预编译语句提供性能优化。因为预编译语句有可能被重复调用，所以语句在被DBServer的编译器编译后的执行代码被缓存下来，那么下次调用时只要是相同的预编译语句就不需要编译，只要将参数直接传入编译过的语句执行代码中就会得到执行。）

22.7.13

花了一上午时间复习这里的基础，有几点问题：
对于增删改的理解进一步加深了这很好。

JDBCUtils的static不知道要加
JDBCUtils获取构造器老是忘记
另外对于流的字节数组写入的方式不太熟悉（啧）
对于批量插入的循环有误解（是重复操作多次，而不是占位符有多个）
另外对于Blob大型数据的查询下载，中间有一部核心的操作很重要，不熟悉。

自此，就像是三九北风熬了一碗鲫鱼汤，出家和尚瞄了一眼小娇娘。
说不出的欢喜和难熬。

JDBC初级学习 ------（师承尚硅谷）

Java相关栏目本月热门文章