Mysql的JDBC增删改查

前言

可以自己建个表，创建对应的实体类。将下面测试类里的SQL语句换了就行。
感觉麻烦的话，就按下面的步骤来。提供了实体类和表。

首先建立一个maven工程，按照下面步骤操作。

1、创建lib文件夹

将mysql-connector-java-·····.jar包放在lib文件夹下面
jar包下载地址 参考这位博主的 https://blog.csdn.net/kt1776133839/article/details/124539192
并右键lib选择Add as Library导入驱动包

2、建立包和类

ConnectionConfig类
作为数据库的连接

package com.hmw.demo.config;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class ConnectionConfig {

    // mysql地址
    public static final String URL="jdbc:mysql://localhost:3306/fo_service?useUnicode=true&useSSL=false&characterEncoding=utf8&serverTimezone=GMT";
    // 驱动地址
    public static final String DRIVER="com.mysql.jdbc.Driver";
    // Mysql用户名
    public static final String USERNAME="root";
    // 密码
    public static final String PASSWORD="root";
    // 加载驱动信息到程序中
    static{
        try {
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }


    // 取得Connection连接对象,
    public static Connection getConnection() throws Exception{
        Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        return conn;
    }

    // 取得Connection连接对象,
    public static void close(Connection conn, Statement pst) throws Exception{
       if(conn!=null){
           conn.close();
       }
       if(pst!=null){
           pst.close();
       }
    }
}


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

SimpleUtil类

package com.hmw.demo.config;

import lombok.extern.slf4j.Slf4j;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Slf4j
public class SimpleUtil {


    public static List<Map<String, Object>> queryTableInfo(String sql) throws Exception {
        Connection connection = null;
        Statement statement = null;
        try {
            connection = ConnectionConfig.getConnection();
            statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(sql);
            List<Map<String, Object>> list = new ArrayList<>();
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            while (rs.next()) {
                Map<String, Object> map = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    String key = metaData.getColumnLabel(i);
                    Object value = rs.getObject(i);
                    map.put(key, value);
                }
                list.add(map);
            }
            return list;
        } catch (Exception e) {
            log.info(e.getMessage(), e);
            throw new Exception("查询数据异常");
        } finally {
            try {
                ConnectionConfig.close(connection, statement);
            } catch (Exception e) {
                log.info(e.getMessage(), e);
            }

        }
    }

    /**
     * 返回实体数据
     * @param clazz
     * @param tempList
     * @param 
     * @return
     */
    public static <T> List<T> handleObject(Class<T> clazz, List<Map<String, Object>> tempList) throws Exception {
        List<T> resList = new ArrayList<>();
        try
        {
            //加载数据到对象
            if (tempList.size() > 0) {
                for (Map<String, Object> map : tempList) {
                    //解析对象
                    T bean = clazz.newInstance();
                    for (Map.Entry<String, Object> entry : map.entrySet()) {
                        String propertyName = entry.getKey();
                        Object value = entry.getValue();
                        // 这里propertyName和实体的属性名称必须一模一样
                        Field field = clazz.getDeclaredField(propertyName);
                        // 设置对象的访问权限，保证对private的正常访问
                        field.setAccessible(true);
                        String str = field.getName();
                        String s = str.substring(0, 1).toUpperCase() + str.substring(1);
                        Method method = clazz.getMethod("set" + s, field.getType());
                        method.invoke(bean, value);
                    }
                    resList.add(bean);
                }
            }
        }
        catch (Exception e) {
            log.info(e.getMessage(), e);
            throw new Exception("整理表数据,发生异常!!!");
        }
        return resList;
    }


}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94

JDBCTest测试类
JDBC增删改查

package com.hmw.demo.controller;


import com.alibaba.fastjson.JSONObject;
import com.hmw.demo.config.ConnectionConfig;
import com.hmw.demo.config.SimpleUtil;
import com.hmw.demo.model.TSdataTag;
import lombok.extern.slf4j.Slf4j;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Timestamp;
import java.util.*;

@Slf4j
public class JDBCTest {

    public static void main(String[] args)  throws Exception{
        log.info("=====新增数据条数{}=====", addData());
        log.info("=====修改数据条数{}=====", updateData());
        log.info("=====删除数据条数{}=====", deleteData());
        log.info("=====开始批量新增数据=====");
        addBatchData();
        log.info("=====批量新增数据结束=====");
        log.info("=====批量查询数据=====");
        queryDataBatch();
    }

    // 新增数据
    public static  int addData() throws Exception{
        Connection conn = null;
        PreparedStatement pst=null;

        try {
            //获得连接对象
            conn = ConnectionConfig.getConnection();
            //准备sql语句
            String sql = "insert into t_sdata_tag (id, tag_name, app_id, create_member, create_time, last_modifier, last_modify_time)"
                    + " values (?, ?, ?, ?, ?, ?, ?)";
            //取得发送sql语句的对象
            pst = conn.prepareStatement(sql);
            pst.setString(1, UUID.randomUUID().toString());
            pst.setString(2, "新增");
            pst.setString(3, "China");
            pst.setString(4, "99999");
            pst.setTimestamp(5, new Timestamp(new Date().getTime()));
            pst.setString(6, null);
            pst.setTimestamp(7,null);
            //发送sql语句指令
            return pst.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally{
            //关闭资源
            ConnectionConfig.close(conn,pst);
        }
        return 0;
    }

    // 修改数据
    public static  int updateData() throws Exception{
        Connection conn = null;
        PreparedStatement pst=null;

        try {
            //获得连接对象
            conn = ConnectionConfig.getConnection();
            //准备sql语句
            String sql = "Update t_sdata_tag SET  tag_name= '新增修改' " + " WHERE app_id='China' ";
            //取得发送sql语句的对象
            pst = conn.prepareStatement(sql);
            //发送sql语句指令
            return pst.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally{
            //关闭资源
            ConnectionConfig.close(conn,pst);
        }
        return 0;
    }

    // 删除数据
    public static  int deleteData() throws Exception{
        Connection conn = null;
        PreparedStatement pst=null;

        try {
            //获得连接对象
            conn = ConnectionConfig.getConnection();
            //准备sql语句
            String sql = "DELETE FROM t_sdata_tag WHERE app_id= 'China'";
            //取得发送sql语句的对象
            pst = conn.prepareStatement(sql);
            //发送sql语句指令
            return pst.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally{
            //关闭资源
            ConnectionConfig.close(conn,pst);
        }
        return 0;
    }


    // 批量查询数据
    public static void queryDataBatch()  throws Exception
    {
        // 准备数据
        List<String> dataList = new ArrayList();
        dataList.add("999");
        dataList.add("888");

        StringBuilder query = new StringBuilder();
        for (String item : dataList) {
            query.append("'").append(item).append("',");
        }
        String sql = "select * from t_sdata_tag"
                + " where create_member in (" + query.substring(0,query.length()-1) + ")";

        // 查询表数据 key 为字段，value为值
        List<Map<String, Object>> mapList = SimpleUtil.queryTableInfo(sql);
        // 转换成对象集合
        List<TSdataTag> tagList = SimpleUtil.handleObject(TSdataTag.class, mapList);
        log.info("实体JSON数据：" + JSONObject.toJSONString(tagList));
    }

    // 批量新增数据
    public static void addBatchData() throws Exception {

        // 准备数据
        List<TSdataTag> dataList = new ArrayList();
        for (int i = 0; i < 11; i++) {
            TSdataTag tag = new TSdataTag();
            tag.setId(UUID.randomUUID().toString());
            tag.setTag_name("测试"+ i);
            dataList.add(tag);
        }
        Connection conn = null;
        PreparedStatement pst=null;

        try {
            //获得连接对象
            conn = ConnectionConfig.getConnection();
            // 关闭自动提交
            conn.setAutoCommit(false);

            //准备sql语句
            String sql = "insert into t_sdata_tag (id, tag_name, app_id, create_member, create_time, " +
                    "last_modifier, last_modify_time) values (?, ?, ?, ?, ?, ?, ?)";
            pst = conn.prepareStatement(sql);
            for (int i = 0; i < dataList.size(); i++) {
                pst.setString(1,dataList.get(i).getId());
                pst.setString(2, dataList.get(i).getTag_name());
                pst.setString(3, "9999");
                pst.setString(4, null);
                pst.setTimestamp(5, new Timestamp(new Date().getTime()));
                pst.setString(6, null);
                pst.setTimestamp(7,null);
                // 将sql语句打包到一个容器中
                pst.addBatch();
                if(i % 5 == 0)
                {
                    // 将容器中的sql语句提交
                    pst.executeBatch();
                    // 清空容器，为下一次打包做准备
                    pst.clearBatch();
                }
            }
            // 为防止有sql语句漏提交【如i结束时%5！=0的情况】，需再次提交sql语句
            pst.executeBatch();//将容器中的sql语句提交
            pst.clearBatch();//清空容器
            // 预处理完成后统一提交  保证事务一致性。尤其是当还有其他的SQL操作时，比如先删后增
            conn.commit();
        }catch (Exception e){
            e.printStackTrace();
        }finally{
            //关闭资源
            ConnectionConfig.close(conn,pst);
        }
    }

}


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186

TSdataTag实体类

package com.hmw.demo.model;

import lombok.Data;

import java.io.Serializable;
import java.util.Date;

@Data
public class TSdataTag implements Serializable {
    private String id;

    private String tag_name;

    private String app_id;

    private String create_member;

    private Date create_time;

    private String last_modifier;

    private Date last_modify_time;

    private static final long serialVersionUID = 1L;

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

3、建立数据库表

DROP TABLE IF EXISTS `t_sdata_tag`;
CREATE TABLE `t_sdata_tag`  (
  `id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `tag_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '标签名',
  `app_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '应用id',
  `create_member` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建人',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `last_modifier` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '修改人',
  `last_modify_time` datetime(0) NULL DEFAULT NULL COMMENT '修改时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '按钮标签表' ROW_FORMAT = Dynamic;
1
2
3
4
5
6
7
8
9
10
11

4、运行测试类中的main方法

注意：事务的一致性尤其重要。可看批量新增操作 conn.commit()的位置。