對其做了些優化，但此種sql生成方式僅適用于復雜程度不高的sql，所以實用性不是很高，僅僅是寫著玩的，知道點mybatis的注解形式的使用方式，可能以后會逐漸完善起來。第一次寫博客，寫的簡單點。
|
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
|
package com.bob.config.mvc.mybatis;import java.lang.annotation.Documented;import java.lang.annotation.ElementType;import java.lang.annotation.Retention;import java.lang.annotation.RetentionPolicy;import java.lang.annotation.Target;/** * 實體類對應的列 * * @author jjb * @create 2017-09-08 14:42 */@Documented@Retention(RetentionPolicy.RUNTIME)@Target({ElementType.FIELD, ElementType.METHOD})public @interface Column { /** * 當前屬性對應的列名 * * @return */ String value() default ""; /** * 當前屬性是不是表必須的 * * @return */ boolean required() default true;} |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
package com.bob.config.mvc.mybatis;import java.lang.annotation.Documented;import java.lang.annotation.ElementType;import java.lang.annotation.Retention;import java.lang.annotation.RetentionPolicy;import java.lang.annotation.Target;/** * 實體類對應的表 * * @author jjb * @create 2017-09-08 14:44 */@Documented@Retention(RetentionPolicy.RUNTIME)@Target({ElementType.TYPE})public @interface Table { String value() default ""; /** * 當前表的主鍵 * * @return */ String key();} |
|
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
|
package com.bob.config.mvc.mybatis;import org.apache.ibatis.annotations.DeleteProvider;import org.apache.ibatis.annotations.InsertProvider;import org.apache.ibatis.annotations.Options;import org.apache.ibatis.annotations.SelectProvider;import org.apache.ibatis.annotations.UpdateProvider;/** * Mybatis基礎Mapper * * @author jjb * @create 2017-09-08 14:37 */public interface BaseMapper<T> { /** * 插入語句 * * @param bean * @return */ @Options(useGeneratedKeys = true) @InsertProvider(type = SqlProvider.class, method = "insert") public int insert(T bean); /** * 刪除語句 * * @param bean * @return */ @DeleteProvider(type = SqlProvider.class, method = "delete") public int delete(T bean); /** * 更新語句 * * @param bean * @return */ @UpdateProvider(type = SqlProvider.class, method = "update") public int update(T bean); /** * 查找語句 * * @param bean * @return */ @SelectProvider(type = SqlProvider.class, method = "select") public T findFirst(T bean);} |
|
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
|
package com.bob.config.mvc.mybatis;import java.time.LocalDate;/** * Mybatis實體類 * * @author jjb * @create 2017-09-08 17:16 */@Table(key = "id")public class MybatisEntity { @Column() private String id; @Column("USER_NAME") private String name; @Column() private Integer age; private LocalDate date; @Column("ADRESS_NUMBER") private Integer userAdressNumber; public String getId() { return id; } public void setId(String id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Integer getAge() { return age; } public void setAge(Integer age) { this.age = age; } @Column("CUR_DATE") public LocalDate getDate() { return date; } public void setDate(LocalDate date) { this.date = date; } public Integer getUserAdressNumber() { return userAdressNumber; } public void setUserAdressNumber(Integer userAdressNumber) { this.userAdressNumber = userAdressNumber; }} |
|
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
|
package com.bob.config.mvc.mybatis;import java.lang.reflect.Field;import java.util.Map;/** * 表到實體的格式化器 * * @author jjb * @create 2017-09-08 14:51 */public interface TableFormatter { /** * 根據屬性獲取字段名稱 * * @param field * @return */ public String getColumnName(Field field); /** * 獲取主鍵屬性對應的列名 * * @return */ public String getKeyColumnName(Class<?> clazz); /** * 獲取主鍵的屬性名稱 * * @param clazz * @return */ public String getKeyFiledName(Class<?> clazz); /** * 根據類獲取表名稱 * * @param clazz * @return */ public String getTableName(Class<?> clazz); /** * 獲取一個類的所有屬性的映射信息 * * @param clazz * @return */ public Map<Field, String> getFieldMappings(Class<?> clazz);} |
|
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
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
|
package com.bob.config.mvc.mybatis;import java.lang.reflect.Field;import java.lang.reflect.Modifier;import java.util.HashMap;import java.util.Map;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import org.springframework.beans.BeanUtils;import org.springframework.stereotype.Component;import org.springframework.util.Assert;import org.springframework.util.ReflectionUtils;import org.springframework.util.StringUtils;/** * 依據駝峰原則來將表的信息格式化為實體類的信息，在駝峰處改小寫同時插入下劃線 * * @author jjb * @create 2017-09-08 14:55 */@Componentpublic class HumpToUnderLineFormatter implements TableFormatter { private static final Logger LOGGER = LoggerFactory.getLogger(HumpToUnderLineFormatter.class); private static final Map<Class<?>, Map<Field, String>> FIELD_TO_COLUMN_MAPPINGS = new HashMap<Class<?>, Map<Field, String>>(); private static final Map<Class, String> CLASS_TO_TABLE_MAPPING = new HashMap<Class, String>(); private static final StringBuilder SB = new StringBuilder(); private static final Object LOCK = new Object(); @Override public String getColumnName(Field field) { Assert.notNull(field, "屬性不能為空"); Map<Field, String> mappings = FIELD_TO_COLUMN_MAPPINGS.get(field.getDeclaringClass()); if (mappings == null) { synchronized (LOCK) { mappings = FIELD_TO_COLUMN_MAPPINGS.get(field.getDeclaringClass()); if (mappings == null) { mappings = buildMapping(field.getDeclaringClass()); } } } return mappings.get(field); } @Override public String getKeyColumnName(Class<?> clazz) { Table table = checkClass(clazz); return getColumnName(ReflectionUtils.findField(clazz,table.key())); } @Override public String getKeyFiledName(Class<?> clazz) { Table table = checkClass(clazz); Field field = ReflectionUtils.findField(clazz,table.key()); Assert.state(field != null,"@Table的key()指定的屬性必須存在"); return field.getName(); } private Table checkClass(Class<?> clazz){ Assert.isTrue(clazz != null , "與Table對應的Class不能為空"); Table table = clazz.getAnnotation(Table.class); Assert.isTrue(table != null && StringUtils.hasText(table.key()),"["+clazz.getName()+"]必須標識@Table注解且key()不能為空"); return table; } @Override public String getTableName(Class<?> clazz) { Assert.notNull(clazz, "類不能為空"); Assert.isTrue(clazz.isAnnotationPresent(Table.class), "[" + clazz.getName() + "]類上必須含有@Table注解"); String name = CLASS_TO_TABLE_MAPPING.get(clazz); if (name == null) { synchronized (LOCK) { name = CLASS_TO_TABLE_MAPPING.get(clazz); if (name == null) { buildMapping(clazz); } } } return CLASS_TO_TABLE_MAPPING.get(clazz); } @Override public Map<Field, String> getFieldMappings(Class<?> clazz) { Assert.isTrue(clazz != null && clazz.isAnnotationPresent(Table.class), "與Table對應的Class不能為空且必須標識@Table注解"); Map<Field, String> mappings = FIELD_TO_COLUMN_MAPPINGS.get(clazz); if (mappings == null) { synchronized (LOCK) { mappings = FIELD_TO_COLUMN_MAPPINGS.get(clazz); if (mappings == null) { mappings = buildMapping(clazz); } } } return FIELD_TO_COLUMN_MAPPINGS.get(clazz); } /** * 創建實體到表映射 * * @param clazz */ private Map<Field, String> buildMapping(Class<?> clazz) { buildClassToTableMapping(clazz); Map<Field, String> mappings = new HashMap<Field, String>(); FIELD_TO_COLUMN_MAPPINGS.put(clazz, mappings); buildFiledToColumnMapping(clazz, mappings); buildFiledToColumnMappingWithGetter(clazz, mappings); return mappings; } /** * 創建類名到表名的名稱映射 * * @param clazz */ private void buildClassToTableMapping(Class<?> clazz) { Table table = clazz.getAnnotation(Table.class); Assert.notNull(table, "[" + clazz.getName() + "]類上必須有@Table注解"); CLASS_TO_TABLE_MAPPING.put(clazz, StringUtils.hasText(table.value()) ? table.value() : doFormatWithHunmRule(clazz.getSimpleName())); } /** * 通過Filed建立屬性名稱到字段名稱的映射 * * @param clazz * @param mappings */ private void buildFiledToColumnMapping(Class<?> clazz, Map<Field, String> mappings) { ReflectionUtils.doWithLocalFields(clazz, (field) -> { Column column = field.getAnnotation(Column.class); if (column != null) { if (Modifier.isStatic(field.getModifiers())) { LOGGER.error("[{}]注解不適用于靜態方法:[{}]", Column.class.toString(), field); return; } mappings.put(field, StringUtils.hasText(column.value()) ? column.value() : doFormatWithHunmRule(field.getName())); } } ); } /** * 通過getter()建立屬性名稱到字段名稱的映射 * * @param clazz * @param mappings */ private void buildFiledToColumnMappingWithGetter(Class<?> clazz, Map<Field, String> mappings) { ReflectionUtils.doWithLocalMethods(clazz, (method) -> { Column column = method.getAnnotation(Column.class); if (column != null) { if (Modifier.isStatic(method.getModifiers())) { LOGGER.warn("[{}]注解不適用于靜態方法: [{}]", Column.class.toString(), method); return; } if (!method.getName().startsWith("get") || method.getParameterTypes().length > 0) { LOGGER.warn("[{}]注解只適用于getter方法,而非: [{}]方法", Column.class.toString(), method); return; } String fieldName = BeanUtils.findPropertyForMethod(method).getName(); mappings.put(ReflectionUtils.findField(clazz, fieldName), StringUtils.hasText(column.value()) ? column.value() : doFormatWithHunmRule(fieldName)); } } ); } /** * 依據駝峰原則格式化屬性或者類名稱,在駝峰處改小寫同時前一位插入下劃線,忽略首字母 * * @param name * @return */ private static String doFormatWithHunmRule(String name) { Assert.hasText(name, "屬性或者類名稱不能為空"); SB.delete(0, SB.length()); SB.append(toUpperCase(name.charAt(0))); for (int i = 1; i < name.length(); i++) { if (isUpperCase(name.charAt(i))) { SB.append("_"); } SB.append(toUpperCase(name.charAt(i))); } return SB.toString(); } /** * 將字符轉換為大寫 * * @param ch * @return */ private static char toUpperCase(char ch) { return Character.toUpperCase(ch); } /** * 判斷是否為大寫 * * @param ch * @return */ private static boolean isUpperCase(char ch) { return Character.isUpperCase(ch); }} |
|
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
|
package com.bob.config.mvc.mybatis;import java.lang.reflect.Field;import java.util.ArrayList;import java.util.List;import java.util.Map.Entry;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import org.springframework.util.StringUtils;/** * Mybatis的SQL語句供應器 * * @author jjb * @create 2017-09-08 14:37 */public class SqlProvider { private static final Logger LOGGER = LoggerFactory.getLogger(SqlProvider.class); private TableFormatter tableFormat = new HumpToUnderLineFormatter(); /** * 根據Bean對象生成插入SQL語句 * * @param bean * @return */ public String insert(Object bean) { Class<?> beanClass = bean.getClass(); String tableName = tableFormat.getTableName(beanClass); StringBuilder insertSql = new StringBuilder(); List<String> columns = new ArrayList<String>(); List<String> values = new ArrayList<String>(); insertSql.append("INSERT INTO ").append(tableName).append("("); try { for (Entry<Field, String> entry : tableFormat.getFieldMappings(beanClass).entrySet()) { Field field = entry.getKey(); field.setAccessible(true); if (field.get(bean) != null) { columns.add(entry.getValue()); values.add("#{" + field.getName() + "}"); } } } catch (Exception e) { new RuntimeException("get insert sql has exceptoin:" + e); } int columnSize = columns.size(); for (int i = 0; i < columnSize; i++) { insertSql.append(columns.get(i)); insertSql.append(i != columnSize - 1 ? "," : ") VALUES("); } for (int i = 0; i < columnSize; i++) { insertSql.append(values.get(i)); insertSql.append(i != columnSize - 1 ? "," : ")"); } return insertSql.toString(); } /** * 根據Bean對象生成更新SQL語句 * * @param bean * @return */ public String update(Object bean) { Class<?> beanClass = bean.getClass(); String tableName = tableFormat.getTableName(beanClass); StringBuilder updateSql = new StringBuilder(); updateSql.append(" UPDATE ").append(tableName).append(" SET "); try { for (Entry<Field, String> entry : tableFormat.getFieldMappings(beanClass).entrySet()) { Field field = entry.getKey(); field.setAccessible(true); if (field.get(bean) != null) { updateSql.append(entry.getValue()).append("=#{").append(field.getName()).append("},"); } } updateSql.deleteCharAt(updateSql.length() - 1); } catch (Exception e) { new RuntimeException("get update sql is exceptoin:" + e); } updateSql.append(" WHERE ").append(tableFormat.getKeyColumnName(beanClass) + " =#{" + tableFormat.getKeyFiledName(beanClass) + "}"); return updateSql.toString(); } /** * 根據Bean對象生成刪除SQL語句 * * @param bean * @return */ public String delete(Object bean) { Class<?> beanClass = bean.getClass(); String tableName = tableFormat.getTableName(beanClass); StringBuilder deleteSql = new StringBuilder(); deleteSql.append(" DELETE FROM ").append(tableName).append(" WHERE "); try { for (Entry<Field, String> entry : tableFormat.getFieldMappings(beanClass).entrySet()) { Field field = entry.getKey(); field.setAccessible(true); if (field.get(bean) != null) { deleteSql.append(entry.getValue()).append("=#{").append(field.getName()).append("} AND "); } } deleteSql.delete(deleteSql.length() - 5, deleteSql.length() - 1); } catch (Exception e) { new RuntimeException("get delete sql is exceptoin:" + e); } return deleteSql.toString(); } /** * 生成查詢SQL語句 * * @param bean * @return */ public String select(Object bean) { Class<?> beanClass = bean.getClass(); String tableName = tableFormat.getTableName(beanClass); StringBuilder selectSql = new StringBuilder(); List<String> columns = new ArrayList<String>(); List<String> values = new ArrayList<String>(); selectSql.append("SELECT "); try { for (Entry<Field, String> entry : tableFormat.getFieldMappings(beanClass).entrySet()) { Field field = entry.getKey(); field.setAccessible(true); selectSql.append(entry.getValue() + ","); if (field.get(bean) != null) { columns.add(entry.getValue()); values.add("#{" + field.getName() + "}"); } } selectSql.deleteCharAt(selectSql.length() - 1); } catch (Exception e) { new RuntimeException("get select sql is exceptoin:" + e); } selectSql.append(" FROM ").append(tableName).append(" WHERE "); int columnSize = columns.size(); for (int i = 0; i < columnSize; i++) { selectSql.append(columns.get(i)).append("=").append(values.get(i)).append(" AND "); } selectSql.delete(selectSql.length() - 5, selectSql.length() - 1); return selectSql.toString(); }} |
|
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
|
package com.bob.test.concrete.mysqlGenerate;import java.time.LocalDate;import com.bob.config.mvc.mybatis.MybatisEntity;import com.bob.config.mvc.mybatis.SqlProvider;import org.junit.Before;import org.junit.Test;/** * Mysql基于注解形式的sql語句生成測試 * * @author jjb * @create 2017-09-11 11:10 */public class MysqlGenerateTest { private SqlProvider sqlProvider; private MybatisEntity mybatisEntity; @Before public void doBefore(){ sqlProvider = new SqlProvider(); mybatisEntity = new MybatisEntity(); mybatisEntity.setId("0015415"); mybatisEntity.setName("lanboal"); mybatisEntity.setAge(28); mybatisEntity.setDate(LocalDate.now()); mybatisEntity.setUserAdressNumber(24); } @Test public void testInsert(){ String sql = sqlProvider.insert(mybatisEntity); System.out.println(sql); } @Test public void testUpdate(){ String sql = sqlProvider.update(mybatisEntity); System.out.println(sql); } @Test public void testDelete(){ String sql = sqlProvider.delete(mybatisEntity); System.out.println(sql); } @Test public void testSelect(){ String sql = sqlProvider.select(mybatisEntity); System.out.println(sql); }} |
總結
以上所述是小編給大家介紹的 Mybatis基于注解形式的sql語句生成實例代碼，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回復大家的。在此也非常感謝大家對服務器之家網站的支持！
原文鏈接：http://blog.csdn.net/longdayu4544/article/details/77931795
