工具类：
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
/**
* MySql数据库连接类
* @author zql
* @createTime 2020-11-29 18:41:45
* @version:1.0
*
*/
public class MySqlConnection {
/**
* MySql数据库Connection
*/
private Connection connection;
static {
try {
// 加载MySQL 8.0 以下版本 - JDBC 驱动名
Class.forName("com.mysql.jdbc.Driver");
//MySQL 8.0 以上版本 - JDBC 驱动名
//Class.forName("com.mysql.cj.jdbc.Driver");
} catch (Exception e) {
throw new RuntimeException(e.getMessage());
}
}
/**
* 开启数据库连接
* @author zql
* @createTime 2020-11-29 18:44:25
*
* @param dbName 数据库名
* @param user 用户账号
* @param pwd 用户密码
*/
public void openMySqlConnection(String dbName, String user, String pwd) {
this.openMySqlConnection(null, dbName, user, pwd);
}
/**
* 开启数据库连接
* @author zql
* @createTime 2020-11-29 18:49:50
*
* @param host 数据库带端口主机，为null时默认localhost:3306，示例：127.0.0.1:3306
* @param dbName 数据库名
* @param user 用户账号
* @param pwd 用户密码
*/
public void openMySqlConnection(String host, String dbName, String user, String pwd) {
try {
if (Objects.nonNull(this.connection) && !this.connection.isClosed()) {
this.connection.close();
}
// MySQL 8.0 以下版本 - 数据库URL
String dburl = "jdbc:mysql://localhost:3306/" + dbName;
// MySQL 8.0 以上版本 - 数据库URL
//String dburl = "jdbc:mysql://localhost:3306/" + dbName + "?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
if (Objects.nonNull(host)) {
dburl="jdbc:mysql://" + host + "/" + dbName;
}
this.connection = DriverManager.getConnection(dburl, user, pwd);
} catch (SQLException e) {
throw new RuntimeException(e.getMessage());
}
}
/**
* 获取所有表名
* @author zql
* @createTime 2020-11-29 18:53:07
*
* @return 表名字符串数组
* @throws Exception
*/
public String[] selectTableName() throws Exception {
DatabaseMetaData dbmd = this.connection.getMetaData();
ResultSet result = dbmd.getTables(null, null, "%", null);
List<String> tableNameList = new ArrayList<String>();
while (result.next()) {
tableNameList.add(result.getString(3));
}
result.close();
String[] tableNames = new String[tableNameList.size()];
for (int i = 0; i < tableNames.length; i++) {
tableNames[i] = tableNameList.get(i).toString();
}
return tableNames;
}
/**
* 执行查询
* @author zql
* @createTime 2020-11-29 18:53:35
*
* @param tableName 表名
* @param condition 条件键值对，键为字段，值为条件，该方法仅支持and连接条件，如需其他请自行修改
* @return
* @throws Exception
*/
public List<Map<String, String>> select(String tableName,Map<String, Object> condition) throws Exception {
List<Map<String, String>> list = new ArrayList<Map<String, String>>();
/*
* ``是为了防止表名是关键字
*/
String sql = "select * from `" + tableName + "` ";
List<Object> params = new ArrayList<Object>();
StringBuffer conditions = new StringBuffer();
if (Objects.nonNull(condition) && condition.size() > 0) {
int conCount = condition.size();
int index = 0;
conditions.append(" where 1=1 ");
for (Map.Entry<String, Object> m : condition.entrySet()) {
if (index != conCount) {
conditions.append(" and ");
}
conditions.append("`").append(m.getKey()).append("`").append("=? ");
params.add(m.getValue());
index++;
}
sql = sql + conditions.toString();
}
PreparedStatement stmt = this.connection.prepareStatement(sql);
// 设置参数类型
this.setParamsType(stmt, params);
ResultSet result = stmt.executeQuery();
ResultSetMetaData data = result.getMetaData();
int columnCount = data.getColumnCount();
String[] columnNames = new String[columnCount];
for (int i = 1; i <= data.getColumnCount(); i++) {
// 获得列名
columnNames[i-1] = data.getColumnName(i);
}
while (result.next()) {
Map<String, String> map = new HashMap<String, String>();
for (int i = 1; i <= columnCount; i++) {
map.put(columnNames[i - 1], result.getString(i));
}
list.add(map);
}
result.close();
stmt.close();
return list;
}
/**
* 执行添加
* @author zql
* @createTime 2020-11-29 18:53:58
*
* @param tableName 表名
* @param map 键值对，键为表的字段，值为字段对应的值（即要添加的值）
* @return 受影响的行数
* @throws Exception
*/
public int insert(String tableName,Map<String,Object> map) throws Exception {
StringBuffer tableFields = new StringBuffer();
List<Object> params = new ArrayList<Object>();
for (Map.Entry<String, Object> m : map.entrySet()) {
tableFields.append("`").append(m.getKey()).append("`").append(",");
params.add(m.getValue());
}
tableFields.deleteCharAt(tableFields.length() - 1);
String sql = this.getInsertSql(tableName,tableFields.toString());
PreparedStatement stmt = this.connection.prepareStatement(sql);
// 设置参数类型
this.setParamsType(stmt, params);
int r = stmt.executeUpdate();
stmt.close();
return r;
}
/**
* 执行更新
* @author zql
* @createTime 2020-11-29 18:54:24
*
* @param tableName 表名
* @param valueMap 键值对，键为要修改的字段，值为字段对应的值（即要修改的值）
* @param condition 条件键值对，键为字段，值为条件，该方法仅支持and连接条件，如需其他请自行修改
* @return 受影响的行数
* @throws Exception
*/
public int update(String tableName,Map<String,Object> valueMap,Map<String,Object> condition) throws Exception {
StringBuffer tableFields = new StringBuffer();
List<Object> params = new ArrayList<Object>();
for (Map.Entry<String, Object> m : valueMap.entrySet()) {
tableFields.append("`").append(m.getKey()).append("`").append(",");
params.add(m.getValue());
}
tableFields.deleteCharAt(tableFields.length() - 1);
StringBuffer conditions = new StringBuffer();
int conCount = condition.size();
int index = 0;
for (Map.Entry<String, Object> m : condition.entrySet()) {
conditions.append(m.getKey()).append("=? ");
index++;
if (index != conCount) {
conditions.append(" and ");
}
params.add(m.getValue());
}
String sql = this.getUpdateSql(tableName, tableFields.toString(), conditions.toString());
PreparedStatement stmt = this.connection.prepareStatement(sql);
// 设置参数类型
this.setParamsType(stmt, params);
int r = stmt.executeUpdate();
stmt.close();
return r;
}
/**
* 执行删除
* @author zql
* @createTime 2020-11-29 18:54:55
*
* @param tableName 表名
* @param condition 条件键值对，键为字段，值为条件，该方法仅支持and连接条件，如需其他请自行修改
* @return 受影响的行数
* @throws Exception
*/
public int delete(String tableName,Map<String,Object> condition) throws Exception {
String sql ="delete from `"+tableName+"` ";
StringBuffer conditions = new StringBuffer();
int conCount = condition.size();
int index = 0;
// where不加1 = 1，防止无条件时把所有的数据都删除
conditions.append(" where ");
List<Object> params = new ArrayList<Object>();
for (Map.Entry<String, Object> m : condition.entrySet()) {
conditions.append("`").append(m.getKey()).append("`").append("=? ");
index++;
if (index != conCount) {
conditions.append(" and ");
}
params.add(m.getValue());
}
// 没有条件时不执行删除语句
if (Objects.isNull(condition) || condition.size() == 0) {
return 0;
}
PreparedStatement stmt = connection.prepareStatement(sql+conditions.toString());
// 设置参数类型
this.setParamsType(stmt, params);
int r = stmt.executeUpdate();
stmt.close();
return r;
}
/**
* 关闭数据库连接
* @author zql
* @createTime 2020-11-29 18:55:16
*
*/
public void closeDbConnection(){
try {
this.connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 获取插入sql语句
* @author zql
* @createTime 2020-11-29 18:55:26
*
* @param tableName 表名
* @param tableFields 字段名字符串，字段名以英文逗号隔开，例：a,b,c
* @return 返回 占位符方式的语句，?占位
*/
private final String getInsertSql(String tableName,String tableFields) {
return "insert into `" + tableName + "` (" + tableFields + ") values (" + this.getLoopSpellOn("?", tableFields.split(",").length) + ") ";
}
/**
* 获取更新sql语句
* @author zql
* @createTime 2020-11-29 18:55:47
*
* @param tableName 表名
* @param tableFields 字段名字符串，字段名以英文逗号隔开，例：a,b,c
* @param where 条件字符串
* @return 返回 占位符方式的语句 ，?占位
*/
private final String getUpdateSql(String tableName,String tableFields,String where) {
return "update `" + tableName + "` set " + this.getLoopSplit(tableFields.split(","), "=?") + " where " + where + " ";
}
/**
* 循环拼接字符串
* @author zql
* @createTime 2020-11-29 18:56:11
*
* @param str 字符串
* @param times 拼接的次数
* @return 拼接后的字符串
*/
private final String getLoopSpellOn(String str, int times) {
StringBuffer sb = new StringBuffer();
if (times > 0) {
for (int i = 0; i < times; i++) {
sb.append(str).append(",");
}
sb.deleteCharAt(sb.length() - 1);
}
return sb.toString();
}
/**
* 字符串组装
* <pre>
* 例： String tableFields = "a,b,c,d";
* getLoopSplit(tableFields.split(","), "c") = ac,bc,cc,dc
* </pre>
*
* @author zql
* @createTime 2020-11-29 18:56:32
*
* @param split 字符串数组
* @param mark 要组装的字符串
* @return 以逗号隔开的字符串
*/
private final String getLoopSplit(String[] split, String mark) {
if(Objects.isNull(split) || split.length <= 0) {
return "";
}
if (split.length == 1) {
return split[0] + mark;
}
StringBuffer sb = new StringBuffer();
for (int i = 0; i < split.length; i++) {
sb.append(split[i]).append(mark).append(",");
}
sb.deleteCharAt(sb.length() - 1);
return sb.toString();
}
/**
* 设置参数类型
* @author zql
* @createTime 2020-11-29 18:57:06
*
* @param stmt
* @param params
* @throws SQLException
*/
private void setParamsType(PreparedStatement stmt, List<Object> params) throws SQLException {
for(int i = 0, len = params.size(); i < len; i++) {
Object value = params.get(i);
if (value instanceof Integer) {
int integer = ((Integer) value).intValue();
stmt.setInt(i + 1, integer);
} else if (value instanceof String) {
String s = (String) value;
stmt.setString(i + 1, s);
} else if (value instanceof Double) {
double d = ((Double) value).doubleValue();
stmt.setDouble(i + 1, d);
} else if (value instanceof Float) {
float f = ((Float) value).floatValue();
stmt.setFloat(i + 1, f);
} else if (value instanceof Long) {
long l = ((Long) value).longValue();
stmt.setLong(i + 1, l);
} else if (value instanceof Boolean) {
boolean bl = ((Boolean) value).booleanValue();
stmt.setBoolean(i + 1, bl);
} else if (value instanceof Date) {
Date d = (Date) value;
stmt.setDate(i + 1, (Date) d);
} else if (value instanceof BigDecimal) {
BigDecimal bd = (BigDecimal) value;
stmt.setBigDecimal(i + 1, bd);
}
}
}
}
测试类：
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* MySql数据库连接测试类
* @author zql
* @createTime 2020-11-29 18:41:45
* @version:1.0
*
*/
public class MySqlConnectionTest {
private MySqlConnection db = new MySqlConnection();
@Before
public void openMySqlConnection() {
// 开启连接
db.openMySqlConnection("192.168.174.158","test", "root", "root");
}
@Test
public void selectTableName() throws Exception {
// 查询所有表名
String[] tableName = db.selectTableName();
for (int i = 0, len = tableName.length; i < len; i++) {
System.out.println(tableName[i]);
}
}
@Test
public void select() throws Exception {
// 执行查询1
Map<String, Object> condition = new HashMap<String, Object>();
condition.put("account", 1);
List<Map<String, String>> list = db.select("user", condition);
for (Map<String,String> map : list) {
for (Map.Entry<String, String> m : map.entrySet()) {
System.out.print(m.getValue() + " | ");
}
System.out.println();
}
// 执行查询2
Map<String,Object> condition2 = new HashMap<String,Object>();
condition2.put("name", "Chance");
condition2.put("account", 10);
List<Map<String, String>> list1 = db.select("user", condition2);
list1.forEach(map -> {
map.forEach((key,value) -> {
System.out.print(value + " | ");
});
System.out.println();
});
}
@Test
public void insert() throws Exception {
// 插入数据准备
List<Map<String, Object>> list2 = new ArrayList<Map<String, Object>>();
for (int i = 0; i < 10; i++) {
Map<String,Object> map1 = new HashMap<String,Object>();
map1.put("account", i);
map1.put("pwd", "123456");
map1.put("name", "姓名" + i);
list2.add(map1);
}
// 开始插入数据
int num = 0;
while (num < list2.size()) {
try {
for (int i = num; i < list2.size(); i++, num++) {
int r1 = db.insert("user", list2.get(i));
System.out.println("add:" + r1);
}
} catch (SQLException e) {
if (e.getMessage().contains("user_un")) {
System.out.println("重复数据！" + list2.get(num).toString());
}
num++;
}
}
}
@Test
public void update() throws Exception {
// 执行更新
Map<String,Object> val = new HashMap<String,Object>();
val.put("name", "测试更新");
Map<String,Object> con1 = new HashMap<String,Object>();
con1.put("account", 1);
int r2 = db.update("user", val, con1);
System.out.println("update:" + r2);
}
@Test
public void delete() throws Exception {
Map<String,Object> con2 = new HashMap<String,Object>();
con2.put("account", 9);
int r3 = db.delete("user", con2);
System.err.println("delete:" + r3);
}
@After
public void closeDbConnection() {
db.closeDbConnection();
}
}
普通项目需要引入的包
mysql-connector-java-5.1.18.jar
maven项目依赖：
<!-- mysql驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.18</version>
</dependency>
4326
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
