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

  1 package com.atsyc.api.preparedstatement;
  2 
  3 /*
  4  * 使用preparedStatement进行t_user表的增删改查动作
  5  */
  6 
  7 import com.mysql.cj.xdevapi.PreparableStatement;
  8 import org.junit.Test;
  9 
 10 import java.sql.*;
 11 import java.util.*;
 12 
 13 public class PSCURDPart {
 14     //测试方法：需要导入junit的测试包
 15     @Test
 16     public void testInsert() throws ClassNotFoundException, SQLException {
 17         /*
 18          * t_user插入一条数据
 19          *      account  test
 20          *      password  test
 21          *      nickname  测试员
 22          */
 23         //1.注册驱动
 24         Class.forName("com.mysql.cj.jdbc.Driver");
 25         //2.获取连接
 26         Connection connection = DriverManager.getConnection("jdbc:mysql:///atsyc","root","Yican030615");
 27         //3.编写SQL语句结果，动态值的部分使用?代替
 28         String sql = "INSERT into t_user(account,password,nickname) value(?,?,?);";
 29         //4.创建preparedStatement，并且传入SQL语句结果
 30         PreparedStatement preparableStatement = connection.prepareStatement(sql);
 31         //5.占位符赋值
 32         preparableStatement.setObject(1,"test");
 33         preparableStatement.setObject(2,"test");
 34         preparableStatement.setObject(3,"测试员");
 35         //6.发送SQL语句
 36             //DML类型
 37         int rows = preparableStatement.executeUpdate();
 38         //7.输出结果
 39         if(rows > 0){
 40             System.out.println("数据插入成功！");
 41         }else{
 42             System.out.println("数据插入失败！");
 43         }
 44         //8.关闭资源
 45         preparableStatement.close();
 46         connection.close();
 47     }
 48 
 49     @Test
 50     public void testUpdate() throws ClassNotFoundException, SQLException {
 51         Class.forName("com.mysql.cj.jdbc.Driver");
 52         Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/atsyc","root","Yican030615");
 53         String sql = "UPDATE t_user SET nickname=? WHERE id=?;";
 54         PreparedStatement preparedStatement = connection.prepareStatement(sql);
 55         preparedStatement.setObject(1,"测试员cc");
 56         preparedStatement.setObject(2,3);
 57         int rows = preparedStatement.executeUpdate();
 58         if(rows > 0){
 59             System.out.println("数据修改成功！");
 60         }else{
 61             System.out.println("数据修改失败！");
 62         }
 63         preparedStatement.close();
 64         connection.close();
 65 
 66     }
 67 
 68     @Test
 69     public void testDelete() throws ClassNotFoundException, SQLException {
 70         Class.forName("com.mysql.cj.jdbc.Driver");
 71         Connection connection = DriverManager.getConnection("jdbc:mysql///atsyc","root","Yican030615");
 72         String sql = "DELETE from t_user WHERE id = ?;";
 73         PreparedStatement preparedStatement = connection.prepareStatement(sql);
 74         preparedStatement.setObject(1,3);
 75         int rows = preparedStatement.executeUpdate();
 76         if(rows > 0){
 77             System.out.println("数据删除成功！");
 78         }else{
 79             System.out.println("数据删除失败！");
 80         }
 81         preparedStatement.close();
 82         connection.close();
 83 
 84     }
 85 
 86     @Test
 87     public void testSelect() throws ClassNotFoundException, SQLException {
 88         /*
 89          * 目标：查询所有用户数据，并且封装到一个List<Map> list集合中
 90          *
 91          * 解释：
 92          *      行    id  account  password  nickname
 93          *
 94          * 数据库 -> resultSet -> java -> 一行 - map(key=lieming,value=列的内容) -> List<Map> list
 95          *
 96          * 实现思路：
 97          *      遍历行数据，一行对应一个map  获取一行的列名和对应的列的属性，装配即可
 98          *
 99          * 难点：
100          *      如何获取列的名称
101          *
102          */
103         Class.forName("com.mysql.cj.jdbc.Driver");
104         Connection connection = DriverManager.getConnection("jdbc:mysql:///atsyc?user=root&password=Yican030615");
105         String sql = "SELECT id,account,password,nickname FROM t_user;";
106         PreparedStatement preparedStatement = connection.prepareStatement(sql);
107         ResultSet resultSet = preparedStatement.executeQuery();
108 
109         //结果集解析
110         List<Map> list = new ArrayList<>();
111         //获取列的信息对象
112             //TODO:metaData 装的当前结果集列的信息对象（可以获取列的名称根据下角标，可以获取列的数量）
113         ResultSetMetaData metaData = resultSet.getMetaData();
114             //写了下面这句可以获取列数，水平遍历列
115         int columnCount = metaData.getColumnCount();
116         while (resultSet.next()){
117             Map map = new HashMap();
118             //一行数据对应一个map
119             /*  纯手动取值，不推荐
120                 map.put("id",resultSet.getInt("id"));
121                 map.put("account",resultSet.getString("account"));
122                 map.put("password",resultSet.getString("password"));
123                 map.put("nickname",resultSet.getString("nickname"));
124              */
125             //TODO:自动遍历列要从 1 开始，并且小于等于总列数
126             for(int i = 1 ; i <= columnCount ; i++){
127                 //获取指定列下角标的值  获取值相关用resultSet对象
128                 Object value = resultSet.getObject(i);
129                 //获取指定列下角标的名称  获取名相关用metaData对象
130                 // select * [列名] | xxx_xxx_xx_syc as name
131                 //要用getColumnLabel:会获取别名，如果没有写别名才是列的名称  不要用getColumnName：只会获取列的名称
132                 String columnLabel = metaData.getColumnLabel(i);
133                 map.put(columnLabel,value);
134             }
135             //一行数据的所有列全存到了map中，将map存储到集合中
136             list.add(map);
137         }
138         System.out.println("list = " + list);
139         resultSet.close();
140         preparedStatement.close();
141         connection.close();
142     }
143 
144 }