通过反射根据提供的表名、POJO类型、数据对象自动生成sql语句。
如名为 User 的JavaBean与名为 user 的数据库表对应，可以提供一个封装有数据的User对象user，根据user中含有的数据自动生成sql语句。
1、生成插入语句（插入user中包含的非空数据的语句）：
String insertSql = getInsertSql("user", User.class, user);
2、生成更新语句（user中id不能为空）：
String updateSql = getUpdateSql("user", User.class, user);
3、生成删除语句（根据user中第一个非空属性值作为查找条件删除）：
|
1
2
3
4
|
//生成删除id为1的语句
User user = new User();
user.setId(1);
String deleteSql = getDeleteSql("user", User.class, user);
|
4、生成查询语句（根据user中第一个非空属性值为查找条件）：
|
1
2
3
4
|
//生成查询id为1的语句
User user = new User();
user.setId(1);
String selectSql = getSelectSql("user", User.class, user);
|
|
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
|
package com.hims.util;
import cn.hutool.core.util.ReflectUtil;
import cn.hutool.core.util.StrUtil;
import com.hims.bean.User;
import java.lang.reflect.Field;
public class ProduceSql {
//String insertSql = getInsertSql("user", User.class, user);
/**
* 生成插入语句
* @param tablename 表明
* @param t 有数据的实体
* @param <T> 数据实体类型 如 User
*/
public static <T> String getInsertSql(String tablename, T t) throws IllegalArgumentException {
//insert into table_name (column_name1,column_name2, ...) values (value1,value2, ...)
boolean flag = false;
String sql = "";
Field[] fields = ReflectUtil.getFieldsDirectly(t.getClass(), false);
StringBuffer topHalf = new StringBuffer("insert into "+tablename+" (");
StringBuffer afterAalf = new StringBuffer("values (");
for (Field field : fields) {
if ("ID".equals(field.getName()) || "id".equals(field.getName())){
continue; //id 自动生成无需手动插入
}
topHalf.append(field.getName() + ",");
if (ReflectUtil.getFieldValue(t, field.getName()) instanceof String) {
afterAalf.append("'" + ReflectUtil.getFieldValue(t, field.getName()) + "',");
flag = true;
} else {
afterAalf.append(ReflectUtil.getFieldValue(t, field.getName()) + ",");
flag = true;
}
}
if (!flag) {
throw new IllegalArgumentException(t.getClass() + "NullException.\nThere is no attribute that is not empty.You must provide an object with at least one attribute.");
}
topHalf = new StringBuffer(StrUtil.removeSuffix(topHalf.toString(), ","));
afterAalf = new StringBuffer(StrUtil.removeSuffix(afterAalf.toString(), ","));
topHalf.append(") ");
afterAalf.append(") ");
sql = topHalf.toString() + afterAalf.toString();
return sql;
}
/**
* 生成更新语句
* 必须含有id
* 数据实体中 null 与 空字段不参与更新
* @param tablename 数据库中的表明
* @param t 有数据的实体
* @param <T> 数据实体类型,如 User
*/
public static <T> String getUpdateSql(String tablename, T t) throws IllegalArgumentException {
//UPDATE table_name SET column_name1 = value1, column_name2 = value2, ... where ID=xxx
//or
//UPDATE table_name SET column_name1 = value1, column_name2 = value2, ... where id=xxx
boolean flag = false;
String sql = "";
String id = ""; //保存id列名：ID or id
Field[] fields = ReflectUtil.getFieldsDirectly(t.getClass(), false);
sql = "update "+tablename+" set ";
for (Field field : fields) {
StringBuffer tmp = new StringBuffer();
if ("ID".equals(field.getName()) || "id".equals(field.getName())){
id = field.getName();
continue;//更新的时候无需set id=xxx
}
if (ReflectUtil.getFieldValue(t, field.getName()) != null && (String)ReflectUtil.getFieldValue(t, field.getName()) != "") {
tmp.append( field.getName() + "=");
if (ReflectUtil.getFieldValue(t, field.getName()) instanceof String) {
tmp.append( "'" + ReflectUtil.getFieldValue(t, field.getName()) + "',");
flag = true;
} else {
tmp.append(ReflectUtil.getFieldValue(t, field.getName()) + ",");
flag = true;
}
sql += tmp;
}
}
if (!flag) {
throw new IllegalArgumentException(t.getClass() + "NullException.\nThere is no attribute that is not empty except for ID.You must provide an object with at least one attribute exclude ID.");
}
sql = StrUtil.removeSuffix(sql, ",") + " where " + id + "='" + ReflectUtil.getFieldValue(t, id)+"'";
return sql;
}
/**
* 生成删除语句
* 根据 user 中第一个不为空的字段删除,应该尽量使用 id,提供至少一个非空属性
* @param tablename 表明
* @param t 有数据的实体
* @param <T> 数据实体类型 如 User
*/
public static <T> String getDeleteSql(String tablename, T t) throws IllegalArgumentException {
//delete from table_name where column_name = value
return getSelectOrDeleteSql(tablename, t, "delete");
}
/**
* 生成查询语句
* 根据 user 中第一个不为空的字段查询
* @param tablename 表名
* @param t 有数据的实体
* @param <T> 数据实体类型 如 User
*/
public static <T> String getSelectSql(String tablename, T t) throws IllegalArgumentException {
//delete from table_name where column_name = value
return getSelectOrDeleteSql(tablename, t, "select *");
}
/**
* 根据 operation 生成一个如：operation from table_name where column_name = value 的sql语句
* @param tablename
* @param t
* @param operation "select *" or "delete"
* @param <T>
* @return
* @throws IllegalArgumentException
*/
private static <T> String getSelectOrDeleteSql(String tablename, T t, String operation) throws IllegalArgumentException {
//operation from table_name where column_name = value
boolean flag = false;
String sql = "";
Field[] fields = ReflectUtil.getFieldsDirectly(t.getClass(), false);
StringBuffer topHalf = new StringBuffer(operation + " from " + tablename + " where ");
for (Field field : fields) {
if ("ID".equals(field.getName()) || "id".equals(field.getName())) {
if (ReflectUtil.getFieldValue(t, field.getName()) != null && (int)ReflectUtil.getFieldValue(t, field.getName()) != 0) {
//id 不为空
topHalf.append(field.getName() + " = " + ReflectUtil.getFieldValue(t, field.getName()));
flag = true;
break;
}
}
else {
if (ReflectUtil.getFieldValue(t, field.getName()) != null && (String)ReflectUtil.getFieldValue(t, field.getName()) != "") {
topHalf.append(field.getName() + " = '" + ReflectUtil.getFieldValue(t, field.getName()) + "'");
flag = true;
break;
}
}
}
if (!flag) {
throw new IllegalArgumentException(t.getClass() + "NullException.\nThere is no attribute that is not empty.You must provide an object with at least one attribute.");
}
sql = topHalf.toString();
return sql;
}
}
|
补充知识：通过java反射实现对javabean生成各种sql语句
通过java反射实现对javabean生成各种sql语句，有请大家评论，更改
|
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
|
package com.pdt.util;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
public class BeanUtil {
/**
* @param args
*/
public static void main(String[] args) {
System.out.println(getBeanFilesList("com.pdt.bean.Dictionary"));
System.out.println(genCreateTableSql("com.pdt.bean.Dictionary"));
System.out.println(genInsertSql("com.pdt.bean.Dictionary"));
}
public static String getBeanName(String bean){
try {
Class clz = Class.forName(bean);
String clzStr = clz.toString();
//得到类名
String beanName = clzStr.substring(clzStr.lastIndexOf(".")+1).toLowerCase();
return beanName;
} catch (ClassNotFoundException e) {
e.printStackTrace();
return "";
}
}
public static List<String> getBeanPropertyList(String bean){
try {
Class clz = Class.forName(bean);
Field[] strs = clz.getDeclaredFields();
List<String> propertyList = new ArrayList<String>();
for (int i = 0; i < strs.length; i++) {
String protype = strs[i].getType().toString();
propertyList.add(protype.substring(protype.lastIndexOf(".")+1)+"`"+strs[i].getName());
}
return propertyList;
} catch (ClassNotFoundException e) {
e.printStackTrace();
return null;
}
}
public static String getBeanFilesList(String bean){
try {
Class clz = Class.forName(bean);
Field[] strs = clz.getDeclaredFields();
StringBuffer sb = new StringBuffer();
for (int i = 0; i < strs.length; i++) {
String protype = strs[i].getType().toString();
if (!strs[i].getName().equals("tableName")&&!strs[i].getType().equals("List")) {
sb.append(strs[i].getName()+",");
}
}
sb.deleteCharAt(sb.toString().lastIndexOf(","));
return sb.toString();
} catch (ClassNotFoundException e) {
e.printStackTrace();
return null;
}
}
/**
* 生成建表語句
* @param bean
* @return
*/
public static String genCreateTableSql(String bean){
List<String> beanPropertyList = getBeanPropertyList(bean);
StringBuffer sb = new StringBuffer("create table wnk_pdt_"+getBeanName(bean)+"(\n");
for (String string : beanPropertyList) {
String[] propertys = string.split("`");
if (!propertys[1].equals("tableName")&&!propertys[1].equals("param")&&!propertys[0].equals("List")) {
if (propertys[1].equals("id")) {
sb.append(" id bigint primary key auto_increment,\n");
} else {
if (propertys[0].equals("int")) {
sb.append(" " + propertys[1] + " int default 0 comment '',\n");
} else if (propertys[0].equals("String")) {
sb.append(" " + propertys[1] + " varchar(2000) default '' comment '',\n");
} else if (propertys[0].equals("double")) {
sb.append(" " + propertys[1] + " double(10,2) default 0.0 comment '',\n");
} else if (propertys[0].equals("Date")) {
sb.append(" " + propertys[1] + " datetime comment '',\n");
}
}
}
}
sb.append(")");
sb.deleteCharAt(sb.lastIndexOf(","));
return sb.toString();
}
/**
* 生成查询语句
* @param bean
* @return
*/
public static String genSelectAllSql(String bean){
String filesList = getBeanFilesList(bean);
return "select \n "+filesList+" \n from \n wnk_pdt_"+getBeanName(bean)+"";
}
/**
* 生成插入语句
* @param bean
* @return
*/
public static String genInsertSql(String bean){
String filesList = getBeanFilesList(bean);
int fl = DataUtil.getCountSonStr(filesList,",")+1;
String wenhao = "";
for (int i = 0; i < fl; i++) {
if(i==fl-1){
wenhao = wenhao+"?";
}else{
wenhao = wenhao+"?,";
}
}
return "insert into wnk_pdt_"+getBeanName(bean)+"("+filesList+") values("+wenhao+")";
}
}
|
以上这篇Java反射 JavaBean对象自动生成插入,更新,删除,查询sql语句操作就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持服务器之家。
原文链接：https://blog.csdn.net/qq_37499840/article/details/89599829