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();
}
}
}
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 <T>
* @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;
}
}
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);
}
}
}
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;
}
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;
4、运行测试类中的main方法
注意：事务的一致性尤其重要。可看批量新增操作 conn.commit()的位置。
