通過(guò)反射根據(jù)提供的表名、POJO類(lèi)型、數(shù)據(jù)對(duì)象自動(dòng)生成sql語(yǔ)句。
如名為 User 的JavaBean與名為 user 的數(shù)據(jù)庫(kù)表對(duì)應(yīng)，可以提供一個(gè)封裝有數(shù)據(jù)的User對(duì)象user，根據(jù)user中含有的數(shù)據(jù)自動(dòng)生成sql語(yǔ)句。
1、生成插入語(yǔ)句（插入user中包含的非空數(shù)據(jù)的語(yǔ)句）：
String insertSql = getInsertSql("user", User.class, user);
2、生成更新語(yǔ)句（user中id不能為空）：
String updateSql = getUpdateSql("user", User.class, user);
3、生成刪除語(yǔ)句（根據(jù)user中第一個(gè)非空屬性值作為查找條件刪除）：
|
1
2
3
4
|
//生成刪除id為1的語(yǔ)句User user = new User();user.setId(1);String deleteSql = getDeleteSql("user", User.class, user); |
4、生成查詢(xún)語(yǔ)句（根據(jù)user中第一個(gè)非空屬性值為查找條件）：
|
1
2
3
4
|
//生成查詢(xún)id為1的語(yǔ)句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); /** * 生成插入語(yǔ)句 * @param tablename 表明 * @param t 有數(shù)據(jù)的實(shí)體 * @param <T> 數(shù)據(jù)實(shí)體類(lèi)型 如 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 自動(dòng)生成無(wú)需手動(dòng)插入 } 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; } /** * 生成更新語(yǔ)句 * 必須含有id * 數(shù)據(jù)實(shí)體中 null 與 空字段不參與更新 * @param tablename 數(shù)據(jù)庫(kù)中的表明 * @param t 有數(shù)據(jù)的實(shí)體 * @param <T> 數(shù)據(jù)實(shí)體類(lèi)型,如 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;//更新的時(shí)候無(wú)需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; } /** * 生成刪除語(yǔ)句 * 根據(jù) user 中第一個(gè)不為空的字段刪除,應(yīng)該盡量使用 id,提供至少一個(gè)非空屬性 * @param tablename 表明 * @param t 有數(shù)據(jù)的實(shí)體 * @param <T> 數(shù)據(jù)實(shí)體類(lèi)型 如 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"); } /** * 生成查詢(xún)語(yǔ)句 * 根據(jù) user 中第一個(gè)不為空的字段查詢(xún) * @param tablename 表名 * @param t 有數(shù)據(jù)的實(shí)體 * @param <T> 數(shù)據(jù)實(shí)體類(lèi)型 如 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 *"); } /** * 根據(jù) operation 生成一個(gè)如：operation from table_name where column_name = value 的sql語(yǔ)句 * @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; }} |
補(bǔ)充知識(shí)：通過(guò)java反射實(shí)現(xiàn)對(duì)javabean生成各種sql語(yǔ)句
通過(guò)java反射實(shí)現(xiàn)對(duì)javabean生成各種sql語(yǔ)句，有請(qǐng)大家評(píng)論，更改
|
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(); //得到類(lèi)名 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; } } /** * 生成建表語(yǔ)句 * @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(); } /** * 生成查詢(xún)語(yǔ)句 * @param bean * @return */ public static String genSelectAllSql(String bean){ String filesList = getBeanFilesList(bean); return "select \n "+filesList+" \n from \n wnk_pdt_"+getBeanName(bean)+""; } /** * 生成插入語(yǔ)句 * @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對(duì)象自動(dòng)生成插入,更新,刪除,查詢(xún)sql語(yǔ)句操作就是小編分享給大家的全部?jī)?nèi)容了，希望能給大家一個(gè)參考，也希望大家多多支持服務(wù)器之家。
原文鏈接：https://blog.csdn.net/qq_37499840/article/details/89599829
