Mybatis是个好东西,只是有时候,代码写起来不方便,比如一个具有10多个属性的实体对象,要写插入语句,那就要写长长的sql,很容易拼错,查询要写Results,又是老长的一段,简直忍受不了。
我来更新了,使用 SELECT COLUMN AS FIELD,在查询结果中将表中字段转换成实体对应的属性名就行了,不用写@Results注解
所以,在下就使用注解、反射实现了实体的基本的增、删、改、查
//插入, 会将主键一块插入
int insert(Object o);
//插入,不会插入主键,适合主键自动生成的情景下使用
int add(Object o);
/**
* 批量插入实例
* @param entities 实例集合
* @param hasPrimary 是否需要插入主键值
*/
void batchInsert(Collection<T> entities, boolean hasPrimary);
//修改
int update(Object o);
/**
* 删除实例对象
* @param obj 可以是主键、实例对象
* @return 被删除的数量
*/
int delete(Object obj);
/**
* 通过主键批量删除实例对象
* @param keys 主键集合
* @return 被删除的数量
*/
int batchDelete(Collection<?> keys);
/**
* 通过主键获取实例对象
* @param key 主键值
* @return 实例对象
*/
T get(Object key);
/**
* 通过若干主键获取实例对象list
* @param ids 若干主键值,以“,”分隔
* @return
*/
List<T> findByIds(String ids);
//查找所有的实体对象
List<T> findAll();
/**
* 分页查询
* @param req 查询封装对象
* @param pageNum 当前页码
* @param pageSize 每页数量
* @return list
*/
List<T> pageSeek(SeekReq req, int pageNum, int pageSize);
/**
* 通用查询
* @param req 查询封装对象
* @return list
*/
List<T> seek(SeekReq req);
/**
* 统计查询
* @param field 统计字段,可以为空
* @param filters 若干过滤条件
* @return 数目
*/
int countBy(String field, SeekFilter... filters);
github:点击打开链接,如果各位客官心情好,请劳烦点个star;
1、使用Table注解保存实体对象对应的数据表的表名与主键对应的实体属性名
/* 定义表的注解*/
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.TYPE)
public @interface Table {
String name() default "";
String primaryKey() default "";
}
2、使用Column保存属性对应的表中字段
/* 定义字段的注解*/
@Retention(RetentionPolicy.RUNTIME)
/*该注解只能用在成员变量上*/
@Target(ElementType.FIELD)
public @interface Column {
String name() default "";
}
3、EntityDao<T>是一个抽象类,使用EntityDao<T>通过注解获取实体的以上信息,以下是它的属性与构造器
/**
* 表名称
*/
private String tableName;
/**
* 表格主键对应的实体属性名称
*/
private String primaryField;
/**
* 众列名称,以逗号分割的字符串
*/
private String columns;
//众属性名称
private List<String> fields = new ArrayList<>();
//主键以外的所有属性名称
private List<String> fieldsExcPrimary = new ArrayList<>();
/**
* 不包括主键的众列名称,同样是以逗号分割的字符串
*/
private String columnsExcPrimary;
/**
* 结构[属性:字段]
*/
private Map<String, String> fieldsAndCols = new HashMap<>();
/**
* [属性:字段],不包括主键
*/
private Map<String, String> fiesAndColsExcPrimary = new HashMap<>();
/**
* [属性:get方法]
*/
private Map<String, Method> fieldAndMethod = new HashMap<>();
/**
* 实体的class
*/
private Class<T> clazz;
/**
* 由具体的对象实现
* @return 实体类的class
*/
public abstract Class<T> init();
protected EntityDao() {
log.info("entitySql无参构造器被调用------------> " + this);
clazz = init();
Table table = clazz.getAnnotation(Table.class);
this.tableName = table.name();
this.primaryField = table.primaryKey();
Field[] fields = clazz.getDeclaredFields();
int size = fields.length;
Field field;
String colName;
String fieldName;
StringBuilder sb = new StringBuilder();
StringBuilder stringBuilder = new StringBuilder();
try {
for (int i = 0; i < size; i++) {
field = fields[i];
fieldName = field.getName();
//只针对做了com.digital.util.search.Column注解的字段
if (fields[i].isAnnotationPresent(Column.class)) {
this.fields.add(fieldName);
fieldAndMethod.put(fieldName, clazz.getMethod("get" + upperCaseFirst(fieldName)));
colName = field.getAnnotation(Column.class).name();
if (!this.primaryField.equals(field.getName())) {//过滤有问题
this.fiesAndColsExcPrimary.put(fieldName, colName);
stringBuilder.append(colName).append(i < size - 1 ? "," : "");
this.fieldsExcPrimary.add(fieldName);
}
this.fieldsAndCols.put(fieldName, colName);
sb.append(colName).append(" AS ").append(fieldName).append(i < size - 1 ? "," : "");
}
}
} catch (NoSuchMethodException ex) {
log.error("entityDao.constructor.error-------> " + ex.getMessage());
}
this.columns = sb.toString();
this.columnsExcPrimary = stringBuilder.toString();
}
4、在SqlProvider中使用EntityDao
public static EntityDao entityDao = new EntityDao() {
@Override
public Class init() {
return Report.class;
}
};
5、EntityDao中生成sql的方法:
//会插入主键
public String insert(final Object entity) {
String sql = new SQL() {{
INSERT_INTO(tableName);
getFieldsAndCols().forEach((k, v) -> VALUES(v, "#{" + k + "}"));
}}.toString();
log.info("entityDao.insert.sql----> " + sql);
return sql;
}
//不会插入主键
public String add(final Object entity) {
String sql = new SQL() {{
INSERT_INTO(tableName);
getFiesAndColsExcPrimary().forEach((k, v) -> VALUES(v, "#{" + k + "}"));
}}.toString();
log.info("entityDao.insert.sql----> " + sql);
return sql;
}
private String batchInsert(Collection<T> entities, List<String> fields) {
StringBuilder re = new StringBuilder(" VALUES ");
try {
for (T entity : entities) {
StringBuilder sb = new StringBuilder("(");
for (String key : fields) {
Method m = fieldAndMethod.get(key);
Object val = m.invoke(entity);
sb.append("'").append(val).append("',");
}
sb.append("),");
re.append(sb);
}
} catch (Exception ex) {
ex.printStackTrace();
log.error("entityDao.batchInsert.error----> " + ex.getMessage());
}
System.out.println("----" + re.toString().replaceAll("\\),$", ")"));
String sql = new SQL() {{
INSERT_INTO(tableName);
INTO_COLUMNS(columnsExcPrimary);
}}.toString() + re.toString()
.replaceAll(",\\)", ")")
.replaceAll("'null'", "null")
.replaceAll("\\),$", ")");
log.info("entityDao.batchInsert.sql----> " + sql);
return sql;
}
/**
* 批量插入
*
* @param entities 实体集合
* @param hasPrimary 插入的属性是否包括主键
* @return
*/
public String batchInsert(Collection<T> entities, boolean hasPrimary) {
return batchInsert(entities, hasPrimary ? fields : fieldsExcPrimary);
}
/**
* 删除实例对象
*
* @param obj 可以是主键、实例对象
* @return
*/
public String delete(Object obj) {
String sql;
if (obj == null) {
log.error("操作的对象为空!");
throw new RuntimeException("对象为空!");
} else {
if (isEntity(obj)) {
sql = new SQL() {{
DELETE_FROM(getTableName());
WHERE(getColumnByField(getPrimaryField()) + "=#{" + getPrimaryField() + "}");
}}.toString();
} else {
sql = new SQL() {{
DELETE_FROM(getTableName());
WHERE(getColumnByField(getPrimaryField()) + "=" + obj);
}}.toString();
}
log.info("entityDao.delete.sql--------------> " + sql);
return sql;
}
}
/**
* @param keys 以“,”分隔的主键值
* @return sql
*/
public String batchDelete(String keys) {
String sql = new SQL() {{
DELETE_FROM(getTableName());
WHERE(getColumnByField(getPrimaryField()) + " IN (" + keys + ")");
}}.toString();
log.info("entityDao.batchDelete.sql----------> " + sql);
return sql;
}
/**
* 更新实例对象
* 如果传入map,则据键值对修改对象,
* 如果传入是一个已在的实例,则会将此实例的所有属性更新至数据库,
* 建议使用map作为参数
*
* @param group 包含主键的map:[keyName: value] 或者 实例对象
* @return sql
*/
public String update(final Object group) {
String sql;
if (group == null) {
throw new RuntimeException("传入对象不存在!");
} else {
if (isEntity(group)) {
sql = new SQL() {{
UPDATE(tableName);
getFiesAndColsExcPrimary().forEach((k, v) -> SET(v + "=#{" + k + "}"));
WHERE(getColumnByField(primaryField) + "=#{" + primaryField + "}");
}}.toString();
} else if (group instanceof Map) {
Map temp = (Map) group;
if (temp.containsKey(primaryField)) {
sql = new SQL() {{
UPDATE(tableName);
temp.forEach((k, v) -> {
SET(getColumnByField(k.toString()) + "=#{" + k + "}");
});
WHERE(getColumnByField(primaryField) + "=#{" + primaryField + "}");
}}.toString();
} else {
throw new RuntimeException("未传入主键值!");
}
} else {
throw new IllegalArgumentException("只接收实体对象或者[属性-属性值]结构的map对象");
}
log.info("entityDao.update.sql-------> " + sql);
return sql;
}
}
/**
* 批量更新实例
*
* @param params 需要更新的字段与值组成的键值对
* @param seekFilters 过滤条件
* @return sql
*/
public String batchUpdate(Map<String, Object> params, SeekFilter... seekFilters) {
String sql = new SQL() {{
UPDATE(getTableName());
if (params instanceof Map) {
params.forEach((k, v) -> {
SET(getColumnByField(k.toString()) + "=#{" + k + "}");
});
parseFilters(this, seekFilters);
} else {
throw new IllegalArgumentException("只接收[属性:值]结构的map对象");
}
}}.toString();
return sql;
}
//通过主键获取对象
public String get(Object primaryKey) {
String sql = new SQL() {{
FROM(tableName);
SELECT(columns);
WHERE(getColumnByField(getPrimaryField()) + "=" + primaryKey);
}}.toString();
log.info("entityDao.get.sql------------> " + sql);
return sql;
}
/**
* 通过若干主键获取对象集合
*
* @param ids 以“,”分隔的id
* @return sql
*/
public String findByIds(String ids) {
String sql = new SQL() {{
FROM(tableName);
SELECT(getColumns());
WHERE(getColumnByField(getPrimaryField()) + " IN (" + ids + ")");
}}.toString();
log.info("entityDao.findByIds.sql ---> " + sql);
return sql;
}
/**
* 统计查询
*
* @param field 统计的字段,默认是主键
* @param filters 过滤条件
* @return sql
*/
public String countBy(String field, SeekFilter... filters) {
String sql = new SQL() {{
FROM(tableName);
if (field != null && "".equals(field)) {
if (getColumnByField(field) == null) {
SELECT("count(" + primaryField + ")");
} else {
SELECT("count(" + field + ")");
}
} else {
SELECT("count(" + primaryField + ")");
}
parseFilters(this, filters);
}}.toString();
sql = rename(sql, getFieldsAndCols());
log.info("entityDao.countBy.sql------> " + sql);
return sql;
}
/**
* 分页查询
*
* @param req 查询封装对象
* @param pageIndex 当前在页码
* @param pageSize 每页大小
* @return sql
*/
public String pageSeek(SeekReq req, int pageIndex, int pageSize) {
List<SeekFilter> filters = req.search;
int offset = (pageIndex - 1) * pageSize;
String sql = new SQL() {{
FROM(getTableName());
if (req.selectFields == null || req.selectFields.equals("")) {
SELECT(getColumns());
} else {
SELECT(req.selectFields);
}
parseFilters(this, filters.toArray(new SeekFilter[]{}));
if (req.sort != null) {
ORDER_BY(req.sort);
}
}}.toString() + " LIMIT " + pageSize + " OFFSET " + offset;
sql = rename(sql, getFieldsAndCols());
log.info("entityDao.pageSeek.sql----------> " + sql);
return sql;
}
/**
* 根据指定的若干条件查询若干字段查询
*
* @param req 查询封装对象
* @return sql
*/
public String seek(SeekReq req) {
List<SeekFilter> filters = req.search;
String sql = new SQL() {{
FROM(getTableName());
if (req.selectFields == null || req.selectFields.equals("")) {
SELECT(getColumns());
} else {
SELECT(req.selectFields);
}
parseFilters(this, filters.toArray(new SeekFilter[]{}));
if (req.sort != null) {
ORDER_BY(req.sort);
}
}}.toString();
sql = rename(sql, fieldsAndCols);
log.info("entityDao.seek.sql-----> " + sql);
return sql;
}
private void parseFilters(SQL sql, SeekFilter... seekFilters) {
sql.WHERE("1 = 1");
for (SeekFilter filter : seekFilters) {
if (filter.getLogic().equals(SeekFilter.LOGIC_AND)) {
sql.AND();
sql.WHERE(filter.toSqlPart());
} else {
sql.OR();
sql.WHERE(filter.toSqlPart());
}
}
}
public String findAll() {
String sql = new SQL() {{
SELECT(columns);
FROM(tableName);
}}.toString();
log.info("entityDao.findAll.sql----------> " + sql);
return sql;
}
例:Report实体的mapper,很简洁
@Mapper
public interface ReportMapper extends BaseMapper<Report>{
String RESULT_MAP = "cn.guoyka.simplermybatis.entity.allOfReport";
@Override
@InsertProvider(type = ReportSql.class, method = BaseMapper.INSERT)
int insert(Object o);
@Override
@InsertProvider(type = ReportSql.class, method = BaseMapper.ADD)
int add(Object o);
@Override
@UpdateProvider(type = ReportSql.class, method = BaseMapper.UPDATE)
int update(Object o);
@Override
@DeleteProvider(type = ReportSql.class, method = BaseMapper.DELETE)
int delete(Object obj);
//@Override
@DeleteProvider(type = ReportSql.class, method = BaseMapper.BATCH_DELETE)
int batchDelete(String keys);
@Override
@SelectProvider(type = ReportSql.class, method = BaseMapper.GET)
@ResultMap(RESULT_MAP)
Report get(Object key);
@Override
@SelectProvider(type = ReportSql.class, method = BaseMapper.FIND_BY_IDS)
@ResultMap(RESULT_MAP)
List<Report> findByIds(String ids);
@Override
@SelectProvider(type = ReportSql.class, method = BaseMapper.FIND_ALL)
@ResultMap(RESULT_MAP)
List<Report> findAll();
@Override
@SelectProvider(type = ReportSql.class, method = BaseMapper.PAGE_SEEK)
@ResultMap(RESULT_MAP)
List<Report> pageSeek(SeekReq req, int pageNum, int pageSize);
@Override
@SelectProvider(type = ReportSql.class, method = BaseMapper.SEEK)
@ResultMap(RESULT_MAP)
List<Report> seek(SeekReq req);
@Override
@SelectProvider(type = ReportSql.class, method = BaseMapper.COUNT_BY)
int countBy(String field, SeekFilter... filters);
@SelectProvider(type = ReportSql.class, method = "batchInsert")
void batchInsert(Collection<Report> entities, boolean hasPrimary);
}
看看ReportSql中都是什么,有点长,都是可以做模块的代码,到处拷贝, 可以考虑写脚本生成或者用其它更漂亮的方式实现:
public class ReportSql implements BaseSql<Report> {
public static EntityDao entityDao = new EntityDao() {
@Override
public Class init() {
return Report.class;
}
};
/**
* 批量插入
* @param entities 实例集合
* @param hasPrimary 是否需要插入主键
* @return sql
*/
public String batchInsert(Collection<Report> entities, boolean hasPrimary){
return entityDao.batchInsert(entities, hasPrimary);
}
@Override
public String pageSeek(SeekReq req, int pageIndex, int pageSize) {
return entityDao.pageSeek(req, pageIndex, pageSize);
}
@Override
public String countBy(String field, SeekFilter... filters) {
return entityDao.countBy(field, filters);
}
@Override
public String findByIds(String ids) {
return entityDao.findByIds(ids);
}
@Override
public String insert(Object o) {
return entityDao.insert(o);
}
@Override
public String add(Object o) {
return entityDao.add(0);
}
@Override
public String update(Object o) {
return entityDao.update(o);
}
@Override
public String delete(Object o) {
return entityDao.delete(o);
}
@Override
public String findAll() {
return entityDao.findAll();
}
@Override
public String seek(SeekReq req) {
return entityDao.seek(req);
}
@Override
public String get(Object o) {
return entityDao.get(o);
}
//@Override
public String batchDelete(String keys) {
return entityDao.batchDelete(keys);
}
}
另外再附一个生成xml的脚本
@org.junit.Test
void buildMapperXml() {
StreamingMarkupBuilder builder = new groovy.xml.StreamingMarkupBuilder()
builder.encoding = "UTF-8"
/*
准备一些输入
*/
// 项目包名
String packName = "cn.guoyka.simplermybatis.entity"
// 实体类的包绝对路径
File entityFilePath = new File("E:\\Kitty\\workspace\\simpler-mybatis\\src\\main\\java\\cn\\guoyka\\simplermybatis\\entity")
// 保存生成的Mapper.xml的路径
String resultPath = "E:\\Kitty\\workspace\\simpler-mybatis\\src\\main\\resources\\mybatis\\mapper\\Mapper.xml"
String root = "E:\\Kitty\\workspace\\simpler-mybatis\\src\\main\\java\\"
List<String> entityNames = []
peekFile(entityFilePath, root, entityNames)
final def d = ('<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">');
def mapper = {
mkp.xmlDeclaration()
mkp.yieldUnescaped(d)
mapper(namespace: packName) {
for (String className : entityNames) {
try {
if (className != null || className != "") {
Class clazz = Class.forName(className)
if (clazz.isAnnotationPresent(Table.class)) {
Field[] fields = clazz.getDeclaredFields()
String primaryKey = clazz.getAnnotation(Table.class).primaryKey()
String type = (clazz.getPackage().name + "." + clazz.simpleName)
resultMap(id: "allOf" + className.substring(className.lastIndexOf(".") + 1), type: type) {
for (int i = 0; i < fields.length; i++) {
if (Modifier.isStatic(fields[i].getModifiers())) {
pr(fields[i].getName() + "是静态属性,过滤过滤.")
} else {
if (fields[i].getName() == primaryKey) {
id(property: (fields[i].name), javaType: fields[i].getType().simpleName, column: getCol(fields[i]))
} else {
result(property: (fields[i].name), javaType: fields[i].getType().simpleName, column: getCol(fields[i]))
}
}
}
}
}
}
} catch (Exception ex) {
println("${className} ---> " + ex.getMessage())
}
}
}
}
def writer = new FileWriter(resultPath)
writer << builder.bind(mapper)
}
/**
* 获取目录下的java类名及完整包名,如 cn.guoyka.simplermybatis.entity.ZCCRequest
* @param file 实体类的包绝对路径
* @param root 项目中java文件夹的路径
* @param fileNames 若干实体类名称
* @return
*/
def peekFile(File file, String root, List<String> fileNames) {
if (file.exists()) {
if (file.isDirectory()) {
for (File e : file.listFiles()) {
if (e.isDirectory()) {
peekFile(e, root, fileNames)
} else {
if (e.getName().endsWith(".java")) {
fileNames.add(e.getPath()
.replace(root, "")
.replace("\\", ".").replace(".java", ""))
}
}
}
return fileNames
} else {
if (file.getName().endsWith(".java")) {
fileNames.add(file.getPath()
.replace("E:\\Kitty\\workspace\\simpler-mybatis\\src\\main\\java\\", "")
.replace("\\", ".").replace(".java", ""))
}
return fileNames
}
} else {
return new RuntimeException("目录或者文件不存在!");
}
}
/**
* 获取字段上的Column注解
* @param field
* @return
*/
static def getCol(Field field) {
if (field.isAnnotationPresent(Column.class)) {
return field.getAnnotation(Column.class).name()
} else {
throw new RuntimeException(field.name + "未有添加注解cn.guoyka.simplermybatis.annotation.Column,请添加!")
}
}