package com.test;
import cn.hutool.json.JSONUtil;
import org.junit.Test;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class SqlTest {
@Test
public void testMap() {
Map<String, Object> map = new HashMap<>();
map.put("sex", "0");
map.put("sex1", "1");
System.out.println(map.get("sex"));
System.out.println(map.get("sex1"));
}
//通过map插入
@Test
public void insertByMap() {
Map<String, Object> paranMap = new HashMap<>();
paranMap.put("id", String.valueOf(System.currentTimeMillis()));
paranMap.put("name", "小张");
paranMap.put("sex", "0");
paranMap.put("phone", "17362162267");
String sql = "insert into user (id,name,sex,phone) values(#{id},#{name},#{sex},#{phone})";
Connection con = DBUtil.connectDB();
boolean row1 = DBUtil.insertByMap(con, sql,
paranMap);
System.out.println("insertByBean 查询结果：" + row1);
DBUtil.close(con);
}
public static void main(String[] args) throws Exception {
//连接
Connection con = DBUtil.connectDB();
// 通过bean插入
User users = new User();
users.setId(String.valueOf(System.currentTimeMillis()));
users.setName("小方1");
users.setAge("10");
users.setSex("男");
users.setPhone("8708979898");
boolean row1 = DBUtil.insertByBean(con, "insert into user (id,name,sex,phone) values(#{id},#{name},#{sex},#{phone})",
users);
System.out.println("insertByBean 查询结果：" + row1);
DBUtil.close(con);
//查询，结果为类
List<User> users = DBUtil.queryListToBean(con, "select * from user", User.class);
System.out.println("queryListToBean 查询结果：" + JSONUtil.toJsonStr(users));
//查询，结果为List
List<Map<String, Object>> resultMapList = DBUtil.queryList(con, "select * from user");
System.out.println("queryList 查询结果：" + JSONUtil.toJsonStr(resultMapList));
//插入一条记录
boolean row = DBUtil.insert(con, "insert into user (id,name,sex,phone) values(?,?,?,?)",
String.valueOf(System.currentTimeMillis()), "小明", "男", "1123123213");
System.out.println("insert 查询结果：" + row);
}
}
package com.test;
import cn.hutool.core.util.ReflectUtil;
import cn.hutool.core.util.StrUtil;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.*;
public class DBUtil {
private static String URL = "jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai";
private static String Driver = "com.mysql.jdbc.Driver";
private static String user = "root";
private static String password = "666666";
public static void main(String[] args) throws Exception {
String s = "insert into user (id,name,sex,phone) values(#{id},#{name},#{sex},#{phone})";
List<String> param = new ArrayList<>();
//返回#在字符串中的位置
while (s.contains("#{")) {
int i = s.indexOf("#{");
int i2 = s.indexOf("}");
//从下标0开始截取，截取到i；从下标i2+1开始截取，截取到末尾
String substring = s.substring(0, i) + "?" + s.substring(i2 + 1);
//截取param
String paramstring = s.substring(i + 2, i2);
param.add(paramstring);
System.out.println(substring);
s = substring;
}
System.out.println(param);
//截取sql语句，将#{}替换成？，结果s = "insert into user (id,name,sex,phone) values(？，？，？，？)"
}
public void test1() throws Exception {
/* 加载驱动 */
Class.forName(Driver);
/* 创建连接， */
Connection conn = DriverManager.getConnection(URL, user, password);
/* sql 预处理 */
PreparedStatement preparedStatement = conn.prepareStatement("select * from user");
// preparedStatement.setString(1, "1");
/* 执行sql */
ResultSet resultSet = preparedStatement.executeQuery();
// 解析数据
ResultSetMetaData rsmd = resultSet.getMetaData();
int count = rsmd.getColumnCount();
String[] name = new String[count];
// ColumnNames : [id, name, age, sex, phone]
for (int i = 0; i < count; i++) {
name[i] = rsmd.getColumnName(i + 1);
System.out.println(rsmd.getColumnType(i + 1));
}
List<Map<String, Object>> resultList = new ArrayList<>();
List<User> resultUserList = new ArrayList<>();
System.out.println("ColumnNames : " + Arrays.toString(name));
while (resultSet.next()) {
Map rowData = new HashMap();
for (int i = 1; i <= count; i++) {
rowData.put(rsmd.getColumnName(i), resultSet.getObject(i));
}
resultList.add(rowData);
System.out.println();
for (String s : name) {
Object string = resultSet.getObject(s);
System.out.print(s + ":" + string + ";");
}
}
//关闭数据库连接
resultSet.close();
preparedStatement.close();
conn.close();
}
//连接
public static Connection connectDB() {
try {
Class.forName(Driver);
Connection conn = DriverManager.getConnection(URL, user, password);
return conn;
} catch (Exception e) {
throw new RuntimeException();
}
}
//泛型T
public static <T> List<Map<String, Object>> queryList(Connection con, Class<T> Objclass, String s) {
try {
T t = Objclass.newInstance();
} catch (Exception e) {
throw new RuntimeException(e);
}
return null;
}
//查询，结果为类
public static <T> List<T> queryListToBean(Connection con, String s, Class<T> tClass) {
List<T> resultList;
try {
resultList = new ArrayList<>();
PreparedStatement ps = con.prepareStatement(s);
ResultSet resultSet = ps.executeQuery(s);
ResultSetMetaData rsmd = resultSet.getMetaData();
int count = rsmd.getColumnCount();
while (resultSet.next()) {
// java 反射
T t = tClass.newInstance();
for (int i = 1; i <= count; i++) {
Field declaredField = tClass.getDeclaredField(rsmd.getColumnName(i));
declaredField.setAccessible(true);
declaredField.set(t, resultSet.getObject(i));
// 关闭允许访问
declaredField.setAccessible(false);
}
resultList.add(t);
}
try {
resultSet.close();
ps.close();
} catch (Exception ignored) {
}
} catch (Exception e) {
throw new RuntimeException();
}
return resultList;
}
//查询,结果为List
public static List<Map<String, Object>> queryList(Connection con, String s) {
List<Map<String, Object>> resultList;
try {
resultList = new ArrayList<>();
PreparedStatement ps = con.prepareStatement(s);
ResultSet resultSet = ps.executeQuery(s);
ResultSetMetaData rsmd = resultSet.getMetaData();
int count = rsmd.getColumnCount();
while (resultSet.next()) {
Map<String, Object> rowData = new HashMap<>();
for (int i = 1; i <= count; i++) {
rowData.put(rsmd.getColumnName(i), resultSet.getObject(i));
}
resultList.add(rowData);
}
try {
resultSet.close();
ps.close();
} catch (Exception ignored) {
}
} catch (Exception e) {
throw new RuntimeException();
}
return resultList;
}
/**
* User user = new User();
* // Field[] fields = User.class.getDeclaredFields();
* // for (Field field : fields) {
* // // 设置允许访问
* // field.setAccessible(true);
* // field.set(user,field.getName()+"value");
* // // 关闭允许访问
* // field.setAccessible(false);
* // }
* // // {"sex":"sexvalue","phone":"phonevalue","name":"namevalue","id":"idvalue","age":"agevalue"}
* // System.out.println(JSONUtil.toJsonStr(user));
*/
public static void close(Connection con) {
try {
con.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
//插入一条信息，多个参数
public static boolean insert(Connection con, String s, Object... param) {
try {
PreparedStatement ps = con.prepareStatement(s);
// param.length = 4
for (int i = 0; i < param.length; i++) {
ps.setObject(i + 1, param[i]);
}
int update = ps.executeUpdate();
if (update >= 1) {
return true;
}
try {
ps.close();
con.close();
} catch (Exception e) {
throw new RuntimeException();
}
} catch (Exception e) {
throw new RuntimeException(e);
}
return false;
}
//通过bean插入，参数是object
public static boolean insertByBean(Connection con, String sql, Object dataObj) {
try {
// 参数名称数组
List<String> paramNameList = new ArrayList<>();
//截取sql语句，并且将值放在paramNameList中
sql = handleSql(sql, paramNameList);
/**
* insert into user (id,name,sex,phone) values(?,?,?,?)
* [id, name, sex, phone]
*/
PreparedStatement ps = con.prepareStatement(sql);
// param.length = 4
for (int i = 0; i < paramNameList.size(); i++) {
String paramName = paramNameList.get(i);
Class<?> aClass = dataObj.getClass();
// id
Field declaredField = aClass.getDeclaredField(paramName);
declaredField.setAccessible(true);
Object val = declaredField.get(dataObj);
// 关闭允许访问
declaredField.setAccessible(false);
ps.setObject(i + 1, val);
}
int update = ps.executeUpdate();
if (update >= 1) {
return true;
}
try {
ps.close();
con.close();
} catch (Exception e) {
throw new RuntimeException();
}
} catch (Exception e) {
throw new RuntimeException(e);
}
return false;
}
static String handleSql(String sql, List<String> param) {
//返回#在字符串中的位置
while (sql.contains("#{")) {
int i = sql.indexOf("#{");
int i2 = sql.indexOf("}");
//从下标0开始截取，截取到i；从下标i2开始截取，截取到末尾
String substring = sql.substring(0, i) + "?" + sql.substring(i2 + 1);
//截取param
String paramstring = sql.substring(i + 2, i2);
param.add(paramstring);
System.out.println(substring);
sql = substring;
}
System.out.println(param);
return sql;
}
public static boolean insertByMap(Connection con, String sql, Map<String, Object> paranMap) {
try {
// 参数名称数组 []
List<String> paramNameList = new ArrayList<>();
// 处理sql
sql = handleSql(sql, paramNameList);
/**
* sql = insert into user (id,name,sex,phone) values(?,?,?,?)
* paramNameList = [id, name, sex, phone]
*
*
* paranMap.put("id1", String.valueOf(System.currentTimeMillis()));
* paranMap.put("name", "小张");
* paranMap.put("sex", "0");
* paranMap.put("phone", "17362162267");
*
*/
PreparedStatement ps = con.prepareStatement(sql);
/* ps.setObject(1,paranMap.get("id"));
ps.setObject(2,paranMap.get("name"));
ps.setObject(3,paranMap.get("sex"));
ps.setObject(4,paranMap.get("phone"));*/
//通过i代表的键，获得对于paranMap中的值
for (int i=0;i<paramNameList.size();i++){
ps.setObject(i+1,paranMap.get(paramNameList.get(i)));
}
int update = ps.executeUpdate();
if (update >= 1) {
return true;
}
try {
ps.close();
con.close();
} catch (Exception e) {
throw new RuntimeException();
}
} catch (Exception e) {
throw new RuntimeException(e);
}
return false;
}
}
86
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
