服务器之家

服务器之家 > 正文

Mybatis基于注解形式的sql语句生成实例代码

时间：2021-01-01 12:13     来源/作者：jjb_hz

对其做了些优化，但此种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
 */
@Component
public 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

标签：
SQL 注解 mybatis 

相关文章

热门资讯

2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全
2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全 2019-12-26
Intellij idea2020永久破解，亲测可用！！！
Intellij idea2020永久破解，亲测可用！！！ 2020-07-29
歪歪漫画vip账号共享2020_yy漫画免费账号密码共享
歪歪漫画vip账号共享2020_yy漫画免费账号密码共享 2020-04-07
背刺什么意思 网络词语背刺是什么梗
背刺什么意思 网络词语背刺是什么梗 2020-05-22
电视剧《琉璃》全集在线观看 琉璃美人煞1-59集免费观看地址
电视剧《琉璃》全集在线观看 琉璃美人煞1-59集免费观看地址 2020-08-12
返回顶部