通過反射根據提供的表名、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
