在开发中遇到了如果该表调用次数少有的时候整个项目就会调用一次如果这样我们在去创一个Mapper类当Mapper类越来越多该项目看起来是不是太过于臃肿了。所以封装了该类的来进行常用sql语句的调用和分页等功能。希望各位能多多指教
该方法主要是用来对参数进行调用拼接
@Service
public class NrrsSuperDao {
@Autowired
INrrsSuper iSuper ;
/**
*
* @param table 表名
* @param where 字符串的where条件，如果为空，则自动使用and条件将param提供的参数构造where语句
* @param order 排序字段 可以传处类似 id asc 或 id asc,name desc 之类的字符串排序语句
* @param fields 结果中需要的列名，如果为空或空串，则使用 *
* @param page 页码 如果为空，则不分页
* @param size 每页条数 如果为空，则不分页 如果确实需要分页，page size两个参数都必须大于0
* @param param where条件中需要提供的键值对
* @return
* @throws Exception
*/
public List<Map<String,Object>> findAll(String table, String where, String order,String fields, Integer page, Integer size, Map<String,Object> param)
throws Exception{
HashMap<String,Object> params = new HashMap<>() ;
if (table == null || table.equals(""))
throw new Exception("表名不能为空") ;
params.put("table",table) ;
if (where != null && !where.equals(""))
params.put("where",where) ;
if (order != null && !order.equals(""))
params.put("order",order) ;
if (fields != null && !fields.equals(""))
params.put("fields",fields) ;
if (page != null && page > 0 && size != null && size > 0){
params.put("page",page) ;
params.put("size",size) ;
}
for (String key : param.keySet()){
params.put(key,param.get(key)) ;
}
List<Map<String,Object>> list = iSuper.findAll(params) ;
return listToLowerKey(list) ;
}
/**
*查找记录的总条数
* @param table 表名
* @param where 字符串的where条件，如果为空，则自动使用and条件将param提供的参数构造where语句
* @param fields 结果中需要的列名，如果为空或空串，则使用 *
* @param param where条件中需要提供的键值对
* @return 行数
* @throws Exception
*/
public int findCount(String table,String where,String fields,Map<String,Object> param) throws Exception{
HashMap<String,Object> params = new HashMap<>() ;
if (table == null || table.equals(""))
throw new Exception("表名不能为空") ;
params.put("table",table) ;
if (where != null && !where.equals(""))
params.put("where",where) ;
if (fields != null && !fields.equals(""))
params.put("fields",fields) ;
for (String key : param.keySet()){
params.put(key,param.get(key)) ;
}
return iSuper.findCount(params) ;
}
/**
* 查找一条记录
* @param table 表名
* @param where 字符串的where条件，如果为空，则自动使用and条件将param提供的参数构造where语句
* @param order 排序字段 可以传处类似 id asc 或 id asc,name desc 之类的字符串排序语句
* @param fields 结果中需要的列名，如果为空或空串，则使用 *
* @param param where条件中需要提供的键值对
* @return
* @throws Exception
*/
public Map<String,Object> findFirst(String table, String where, String order,String fields, Map<String,Object> param) throws Exception{
List list = this.findAll(table,where,order,fields,1,1,param) ;
if (list != null && list.size() > 0){
return (Map<String,Object>)list.get(0) ;
}
return null ;
}
/**
* 执行一条自定义的select语句
* @param sql sql语句
* @param param where条件中需要提供的键值对
* @return
* @throws Exception
*/
public List<Map<String,Object>> findSql(String sql,Map<String,Object> param) throws Exception{
Map<String,Object> params = new HashMap<>() ;
params.putAll(param);
params.put("sql",sql) ;
List<Map<String,Object>> list = iSuper.findSql(params) ;
return listToLowerKey(list) ;
}
/**
*
* @param table 表名
* @param param 需要插入的值
*/
public void create(String table,Map<String,Object> param){
Map<String,Object> params = new HashMap<>() ;
params.putAll(param);
params.put("table",table) ;
iSuper.save(params);
}
/**
*
* @param table 表名
* @param where 字符串的where条件，如果为空，则自动使用and条件将param提供的参数组成where语句
* @param data 需要更新的字段以及相应的值
* @param param where条件中需要提供的键值对
* @return
* @throws Exception
*/
public int update(String table,String where,Map<String,Object> data,Map<String,Object> param)throws Exception{
Map<String,Object> params = new HashMap<>() ;
params.put("p",param) ;
if (table == null || table.equals(""))
throw new Exception("表名不能为空") ;
params.put("table",table);
if (where != null && !where.equals(""))
params.put("where",where) ;
params.put("d",data) ;
return iSuper.update(params) ;
}
/**
*
* @param table 表名
* @param where 字符串的where条件，如果为空，则自动使用and条件将param提供的参数组成where语句
* @param param where条件中需要提供的键值对
* @return 返回影响的行数
* @throws Exception
*/
public int delete(String table,String where,Map<String,Object> param)throws Exception{
Map<String,Object> params = new HashMap<>() ;
params.putAll(param) ;
if (table == null || table.equals(""))
throw new Exception("表名不能为空") ;
params.put("table",table);
if (where != null && !where.equals(""))
params.put("where",where) ;
return iSuper.delete(params) ;
}
private List<Map<String,Object>> listToLowerKey(List<Map<String,Object>> oldlist){
if (oldlist != null && oldlist.size() > 0){
List<Map<String,Object>> list2 = new ArrayList<>() ;
for (Object o : oldlist){
Map<String,Object> row = (Map<String,Object>)o ;
Map<String,Object> newrow = new HashMap<>() ;
for (String key : row.keySet()){
newrow.put(key.toLowerCase(),row.get(key)) ;
}//for
list2.add(newrow) ;
}//for
return list2 ;
}
return oldlist ;
}
}
使用mybatis@SelectProvider注解的机制来对Sql进行编译
@Mapper
@Component
public interface INrrsSuper {
@SelectProvider(type= SuperProvider.class,method = "findall")
List<Map<String,Object>> findAll(Map<String, Object> param) ;
@SelectProvider(type= SuperProvider.class,method = "findcount")
int findCount(Map<String, Object> param) ;
@SelectProvider(type=SuperProvider.class,method = "findsql")
List<Map<String,Object>> findSql(Map<String, Object> param) ;
@InsertProvider(type=SuperProvider.class,method = "save")
void save(Map<String, Object> data) ;
@UpdateProvider(type = SuperProvider.class,method = "update")
int update(Map<String, Object> param) ;
@DeleteProvider(type = SuperProvider.class,method = "delete")
int delete(Map<String, Object> param) ;
}对参数进行拼接和分页等
public class SuperProvider {
public String findall(Map<String,Object> param){
StringBuilder sql = new StringBuilder("") ;
String table = param.get("table").toString() ;
String where = param.getOrDefault("where","").toString() ;
String order = param.getOrDefault("order","").toString() ;
String fields = param.getOrDefault("fields","*").toString() ;
Integer page = (Integer) param.getOrDefault("page",0) ;
Integer size = (Integer) param.getOrDefault("size",0) ;
if (fields.equals("") || fields.equals("*"))
fields = "t.*" ;
String[] fieldlist = fields.split("\\,") ;
if (fieldlist != null){
fields = "" ;
for (String f : fieldlist){
f = f.trim() ;
if (!f.equals("") && !f.startsWith("t."))
//fields += "t."+f+"," ;
fields += f+"," ;
else
fields += f+"," ;
}//for
fields = fields.replaceAll("\\,$","") ;
}
fields += ",rownum rn " ;
StringBuilder where0 = new StringBuilder("") ;
if (where.equals("")){
for (String key : param.keySet()){
if (key.equals("table") || key.equals("where") || key.equals("order") || key.equals("page") || key.equals("size")|| key.equals("fields"))
continue;
Object val = param.get(key) ;
if (val == null){
where0.append(String.format("and %s is null ",key)) ;
}else{
where0.append(String.format("and %s = #{%s} ",key,key)) ;
}
}//for
where = where0.toString().replaceFirst("and","") ;
}
sql.append("select "+fields+" from "+table) ;
sql.append(" where "+ where) ;
if (!order.equals("")) sql.append(" order by "+order) ;
if (page > 0 && size > 0){
sql.insert(0,"select * from (") ;
sql.append(String.format(") where rn>=%d and rn<=%d",(page-1)*size+1,(page-1)*size+size)) ;
//sql.append(String.format(" limit %d,%d",(page-1)*size,size)) ;
}
return sql.toString() ;
}
public String findcount(Map<String,Object> param){
StringBuilder sql = new StringBuilder("") ;
String table = param.get("table").toString() ;
String where = param.getOrDefault("where","").toString() ;
String fields = param.getOrDefault("fields","*").toString() ;
if (fields.equals(""))
fields = "*" ;
StringBuilder where0 = new StringBuilder("") ;
if (where.equals("")){
for (String key : param.keySet()){
if (key.equals("table") || key.equals("order") || key.equals("where") || key.equals("page") || key.equals("size")|| key.equals("fields"))
continue;
Object val = param.get(key) ;
if (val == null){
where0.append(String.format("and %s is null ",key)) ;
}else{
where0.append(String.format("and %s = #{%s} ",key,key)) ;
}
}//for
where = where0.toString().replaceFirst("and","") ;
}
sql.append("select count(0) total from (select "+fields+" from "+table) ;
sql.append(" where "+ where) ;
sql.append(") a") ;
return sql.toString() ;
}
public String findsql(Map<String,Object> param) throws Exception{
String sql = param.get("sql").toString() ;
return sql ;
}
public String save(Map<String,Object> data) throws Exception{
String table = data.get("table").toString() ;
StringBuilder sql = new StringBuilder() ;
StringBuilder ins = new StringBuilder() ;
StringBuilder val = new StringBuilder() ;
for (String key : data.keySet()){
if (key.equals("table"))
continue;
ins.append(","+key) ;
val.append(String.format(",#{%s}",key)) ;
}//for
sql.append("insert into "+table+" (")
.append(ins.toString().replaceFirst(",",""))
.append(") values (")
.append(val.toString().replaceFirst(",",""))
.append(")");
return sql.toString() ;
}
public String update(Map<String,Object> param){
String table = param.get("table").toString() ;
String where = param.getOrDefault("where","").toString() ;
StringBuilder where0 = new StringBuilder("") ;
if (where.equals("")){
Map<String,Object> pp = (Map<String,Object>)param.get("p") ;
for (String key : pp.keySet()){
if (key.equals("table") || key.equals("where"))
continue;
Object val = pp.get(key) ;
if (val == null){
where0.append(String.format("and %s is null ",key)) ;
}else{
where0.append(String.format("and %s = #{p.%s} ",key,key)) ;
}
}//for
where = where0.toString().replaceFirst("and","") ;
}
StringBuilder sets = new StringBuilder() ;
Map<String,Object> data = (Map<String,Object>)param.get("d") ;
for (String key : data.keySet()){
Object val = data.get(key) ;
if (val == null){
sets.append(String.format(",%s=null",key)) ;
}else{
sets.append(String.format(",%s=#{d.%s}",key,key)) ;
}
}//for
StringBuilder sql = new StringBuilder("update "+table+" set ") ;
sql.append(sets.toString().replaceFirst(",",""))
.append(" where "+where) ;
return sql.toString() ;
}
public String delete(Map<String,Object> param){
String table = param.get("table").toString() ;
String where = param.getOrDefault("where","").toString() ;
StringBuilder where0 = new StringBuilder("") ;
if (where.equals("")){
for (String key : param.keySet()){
if (key.equals("table") || key.equals("where"))
continue;
Object val = param.get(key) ;
if (val != null){
where0.append(String.format("and %s = #{%s} ",key,key)) ;
}else{
where0.append(String.format("and %s is null ",key)) ;
}
}//for
where = where0.toString().replaceFirst("and","") ;
}
StringBuilder sql = new StringBuilder("delete from "+ table) ;
sql.append(" where "+where) ;
return sql.toString() ;
}
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
