后端开发——JDBC的学习（二）

上篇对JDBC基础使用进行了简单的总结，了解了JDBC的基本流程，接下来对prepareStatement的使用、增删改查的练习、主键回显以及插入操作的优化进行总结，以下代码可以直接复制到idea中运行，便于理解和练习；

代码一：prepareStatement的使用：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Scanner;
//TODO prepareStatement（载具）的使用：以后不用statement载具；
/*与statement有点不同：
1.首先编写sql语句 不包含动态值部分的语句，动态值部分使用占位符？替代，注意：？只能替代动态值；
2.创建preparationStatement，并传入动态值
3.动态值 占位符 赋值？
4.发送sql语句，并获取返回结果
 */
public class data_test3 {
    public static void main(String[] args)throws Exception {
        Scanner scan=new Scanner(System.in);
        System.out.println("请输入department_id");
        int id1=scan.nextInt();
        System.out.println("请输入location_id");
        int id2=scan.nextInt();
        //1.创建驱动：
        Class.forName("com.mysql.jdbc.Driver");
        //2.创建链接：
        Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/atguigudb", "root", "dir99");
        //3.编写sql语句 不包含动态值部分的语句，动态值部分使用占位符？替代，注意：？只能替代动态值；
        String sql="select * from departments where department_id=? and location_id=?";
        //4.创建预编译statement并设置sql语句结果 注意这里就要传入sql语句：
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //5.单独的占位符进行赋值：这个方法第一个参数：index，占位符的位置，从左向右，从一开始；第二个参数：Object，占位符的值；
        preparedStatement.setObject(1,id1);
        preparedStatement.setObject(2,id2);
        //6.发送sql语句并获取返回结果：查询调用Query，非查询调用Update；此时不用再传sql语句了，因为上面已经传好了
        ResultSet resultSet = preparedStatement.executeQuery();
        //7.结果解析：也就是移动一次，如果有数据说明登录成功；
        if(resultSet.next()){
            System.out.println("登陆成功！");
        }else{
            System.out.println("登陆失败！");
        }
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }
}

代码二： 增删改查的练习：

import org.junit.Test;
import java.sql.*;
import java.util.*;
//TODO 增删改查的使用：注意查询方法！

public class data_test4 {
    public static void main(String[] args) {

    }
    @Test
    public void testInsert() throws Exception {
        //对于job_grades表：添加A 1500 3000这条数据
        //1.创建驱动：
        Class.forName("com.mysql.jdbc.Driver");
        //2.创建连接：
        Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/atguigudb","root","dir99");
        //3.编写sql语句以及创建prepareStatement
        String sql="insert into job_grades values(?,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setObject(1,"A");
        preparedStatement.setObject(2,1500);
        preparedStatement.setObject(3,3000);
        //发送sql语句：
        int i = preparedStatement.executeUpdate();
        if(i>0){
            System.out.println("数据插入成功！");
        }else{
            System.out.println("数据插入失败！");
        }
        preparedStatement.close();
        connection.close();

    }
    @Test
    public void testDelete()throws Exception{
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/atguigudb", "root", "dir99");
        String sql="delete from job_grades where grade_level=?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setObject(1,"F");
        int i = preparedStatement.executeUpdate();
        if(i>0){
            System.out.println("删除成功！");
        }else{
            System.out.println("删除失败！");
        }
        preparedStatement.close();
        connection.close();

    }
    @Test
    public void testUpdate()throws Exception{
        //对于job_grades表：将刚添加的A 1500 3000这条数据中3000改为9999；
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/atguigudb", "root", "dir99");
        String sql="update job_grades set highest_sal=? where lowest_sal=?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setObject(1,9999);
        preparedStatement.setObject(2,1500);
        int i = preparedStatement.executeUpdate();
        if(i>0){
            System.out.println("更新成功！");
        }else{
            System.out.println("更新失败！");
        }
        preparedStatement.close();
        connection.close();


    }
    @Test
    //注意此处查询想查询所有数据，然后将数据放入到List<Map>list集合中：
    //可知查询结果是一行一行的，返回的是resultSet,然后将一行存入到map中，map(key=列名，value=列的内容)-》List<Map> list;
    //实现思路：遍历每一行数据，一行对应一个map，获取一行的列名和对应的列的属性，装配即可；然后将map装到一个集合当中就完成了；
    public void testSearch()throws Exception{
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/atguigudb", "root", "dir99");
        String sql="select * from job_grades";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        //TODO 获取列的信息对象:避免手动获取每一个列的数据,metaData装的是当前结果集中列的信息对象（它可以根据下角标获取列的名称，也可以获取列的数量）；
        ResultSetMetaData metaData = resultSet.getMetaData();
        //列的数量：有了它以后就可以水平遍历列：
        int columnCount = metaData.getColumnCount();
        List<Map> list=new ArrayList<>();
        while(resultSet.next()){
            //一行对应一个map；
            Map map=new HashMap();
            //下面这种方式纯手动提取，如果列的个数更多会非常麻烦并且换一个表就得重新写，效率很低；
//            map.put("gradelevel",resultSet.getString(1));
//            map.put("lowestsal",resultSet.getInt(2));
//            map.put("highestsal",resultSet.getInt(3));
            //新的方法读取每一个属性的数据，自动遍历列，注意要从一开始，和数据区分开
            for(int i=1;i<=columnCount;i++){
                //获取对应列的属性值：
                Object value = resultSet.getObject(i);
                //要是想加入map中，需要传入key和value，value已经有了，但是key：列名还没有：获取列的名称：这个方法可以获取列的别名，getcolumnname方法会获取列的名称，万一要是起了别名，就找不到了；
                String columnLabel = metaData.getColumnLabel(i);
                map.put(columnLabel,value);
            }
            list.add(map);
        }
        System.out.println(list);
        for(Object data:list){
            System.out.println(data);
        }
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }
}

代码三：主键回显（获取主键的值）

import java.sql.*;
//主键回显：也就是一个从表关联一个主表中的主键，这个主键是自增长的，然后在向从表插入数据的时候，不知道主键的值，因此需要主键回显，得到主键的值；
public class data_test5 {
    public static void main(String[] args) throws Exception {
        returnPrimarykey();
    }
    /*所用的数据库：
CREATE TABLE t_user(
id INT PRIMARY KEY AUTO_INCREMENT COMMENT'用户主键',
`account` VARCHAR(20) NOT NULL UNIQUE COMMENT '账号',
`password` VARCHAR(64)NOT NULL COMMENT'密码',
nickname VARCHAR(20) NOT NULL COMMENT'昵称');
     */
    //TODO 向表中插入一条数据，并且获得数据库自增长主键
    //TODO 总结：在创建statement的时候，需要传入第二个参数，带回自增长的主键;获取带回来的结果集对象，一行一列，获取对应的数据即可；就得到了本次插入数据的主键id值是多少了；
    public static void returnPrimarykey()throws Exception{
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/atguigudb","root","dir99");
        String sql="insert into t_user(account,password,nickname)values(?,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        preparedStatement.setObject(1,"test1");
        preparedStatement.setObject(2,"123456");
        preparedStatement.setObject(3,"hello");
        int i = preparedStatement.executeUpdate();
        if(i>0){
            System.out.println("插入成功！");
            //获取带回的主键的结果集对象，一行一列  id=值
            ResultSet generatedKeys = preparedStatement.getGeneratedKeys();
            //向下移动一行，移动一下光标，此时位于第一行
            generatedKeys.next();
            //取第一列：
            int id = generatedKeys.getInt(1);
            System.out.println("主键id的值为："+id);

        }else{
            System.out.println("插入失败！");
        }
        preparedStatement.close();
        connection.close();
    }
}

代码四：插入数据操作的优化（批量插入）

import org.junit.Test;

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

//TODO 插入优化操作：批量插入;
public class data_test6 {
    public static void main(String[] args)throws Exception {
        //insert_test();
        insert_test1();
    }
    //优化之前：
    @Test
    public static void insert_test()throws Exception{
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/atguigudb","root","dir99");
        String sql="insert into t_user(account,password,nickname)values(?,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        long start=System.currentTimeMillis();
        for(int i=1;i<=1000;i++){
            preparedStatement.setObject(1,"root"+i);
            preparedStatement.setObject(2,"123"+i);
            preparedStatement.setObject(3,"hello"+i);
            preparedStatement.executeUpdate();
        }
        long end=System.currentTimeMillis();
        System.out.println("执行一千次消耗的时间ms："+(end-start));
    }
    //优化后：使用批量插入：
    //TODO 首先在建立连接路径上数据库后面需要添加一个参数，然后需要使用addBatch将数据追加到values的后面，然后最后执行；
    public static void insert_test1()throws Exception{
        Class.forName("com.mysql.cj.jdbc.Driver");
        //TODO 注意此处数据库后面需要添加一个参数：
        Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/atguigudb?rewriteBatchedStatement=true","root","dir99");
        String sql="insert into t_user(account,password,nickname)values(?,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        long start=System.currentTimeMillis();
        for(int i=1;i<=1000;i++){
            preparedStatement.setObject(1,"root_test"+i);
            preparedStatement.setObject(2,"1234a"+i);
            preparedStatement.setObject(3,"helloa"+i);
            //TODO 下面这段代码是将上面的数据添加到values的后面，就是说sql语句values后面可以跟多个括号，一次插入多条数据；
            preparedStatement.addBatch();
        }
        //TODO 执行批量操作：
        preparedStatement.executeBatch();
        long end=System.currentTimeMillis();
        System.out.println("执行一千次消耗的时间ms："+(end-start));

    }
}