基于preparedStatement对数据的增删改查，以及全自动遍历

package com.atsyc.api.preparedstatement;

/*
 * 使用preparedStatement进行t_user表的增删改查动作
 */

import com.mysql.cj.xdevapi.PreparableStatement;
import org.junit.Test;

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

public class PSCURDPart {
    //测试方法：需要导入junit的测试包
    @Test
    public void testInsert() throws ClassNotFoundException, SQLException {
        /*
         * t_user插入一条数据
         *      account  test
         *      password  test
         *      nickname  测试员
         */
        //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql:///atsyc","root","Yican030615");
        //3.编写SQL语句结果，动态值的部分使用?代替
        String sql = "INSERT into t_user(account,password,nickname) value(?,?,?);";
        //4.创建preparedStatement，并且传入SQL语句结果
        PreparedStatement preparableStatement = connection.prepareStatement(sql);
        //5.占位符赋值
        preparableStatement.setObject(1,"test");
        preparableStatement.setObject(2,"test");
        preparableStatement.setObject(3,"测试员");
        //6.发送SQL语句
            //DML类型
        int rows = preparableStatement.executeUpdate();
        //7.输出结果
        if(rows > 0){
            System.out.println("数据插入成功！");
        }else{
            System.out.println("数据插入失败！");
        }
        //8.关闭资源
        preparableStatement.close();
        connection.close();
    }

    @Test
    public void testUpdate() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/atsyc","root","Yican030615");
        String sql = "UPDATE t_user SET nickname=? WHERE id=?;";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setObject(1,"测试员cc");
        preparedStatement.setObject(2,3);
        int rows = preparedStatement.executeUpdate();
        if(rows > 0){
            System.out.println("数据修改成功！");
        }else{
            System.out.println("数据修改失败！");
        }
        preparedStatement.close();
        connection.close();

    }

    @Test
    public void testDelete() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql///atsyc","root","Yican030615");
        String sql = "DELETE from t_user WHERE id = ?;";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setObject(1,3);
        int rows = preparedStatement.executeUpdate();
        if(rows > 0){
            System.out.println("数据删除成功！");
        }else{
            System.out.println("数据删除失败！");
        }
        preparedStatement.close();
        connection.close();

    }

    @Test
    public void testSelect() throws ClassNotFoundException, SQLException {
        /*
         * 目标：查询所有用户数据，并且封装到一个List<Map> list集合中
         *
         * 解释：
         *      行    id  account  password  nickname
         *
         * 数据库 -> resultSet -> java -> 一行 - map(key=lieming,value=列的内容) -> List<Map> list
         *
         * 实现思路：
         *      遍历行数据，一行对应一个map  获取一行的列名和对应的列的属性，装配即可
         *
         * 难点：
         *      如何获取列的名称
         *
         */
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql:///atsyc?user=root&password=Yican030615");
        String sql = "SELECT id,account,password,nickname FROM t_user;";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();

        //结果集解析
        List<Map> list = new ArrayList<>();
        //获取列的信息对象
            //TODO:metaData 装的当前结果集列的信息对象（可以获取列的名称根据下角标，可以获取列的数量）
        ResultSetMetaData metaData = resultSet.getMetaData();
            //写了下面这句可以获取列数，水平遍历列
        int columnCount = metaData.getColumnCount();
        while (resultSet.next()){
            Map map = new HashMap();
            //一行数据对应一个map
            /*  纯手动取值，不推荐
                map.put("id",resultSet.getInt("id"));
                map.put("account",resultSet.getString("account"));
                map.put("password",resultSet.getString("password"));
                map.put("nickname",resultSet.getString("nickname"));
             */
            //TODO:自动遍历列要从 1 开始，并且小于等于总列数
            for(int i = 1 ; i <= columnCount ; i++){
                //获取指定列下角标的值  获取值相关用resultSet对象
                Object value = resultSet.getObject(i);
                //获取指定列下角标的名称  获取名相关用metaData对象
                // select * [列名] | xxx_xxx_xx_syc as name
                //要用getColumnLabel:会获取别名，如果没有写别名才是列的名称  不要用getColumnName：只会获取列的名称
                String columnLabel = metaData.getColumnLabel(i);
                map.put(columnLabel,value);
            }
            //一行数据的所有列全存到了map中，将map存储到集合中
            list.add(map);
        }
        System.out.println("list = " + list);
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }

}