java数据库工具包_java 数据库常用工具类

在开发中遇到了如果该表调用次数少有的时候整个项目就会调用一次如果这样我们在去创一个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> findAll(String table, String where, String order,String fields, Integer page, Integer size, Map param)

throws Exception{

HashMap 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> 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 param) throws Exception{

HashMap 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 findFirst(String table, String where, String order,String fields, Map param) throws Exception{

List list = this.findAll(table,where,order,fields,1,1,param) ;

if (list != null && list.size() > 0){

return (Map)list.get(0) ;

}

return null ;

}

/**

* 执行一条自定义的select语句

* @param sql sql语句

* @param param where条件中需要提供的键值对

* @return

* @throws Exception

*/

public List> findSql(String sql,Map param) throws Exception{

Map params = new HashMap<>() ;

params.putAll(param);

params.put("sql",sql) ;

List> list = iSuper.findSql(params) ;

return listToLowerKey(list) ;

}

/**

*

* @param table 表名

* @param param 需要插入的值

*/

public void create(String table,Map param){

Map 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 data,Map param)throws Exception{

Map 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 param)throws Exception{

Map 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> listToLowerKey(List> oldlist){

if (oldlist != null && oldlist.size() > 0){

List> list2 = new ArrayList<>() ;

for (Object o : oldlist){

Map row = (Map)o ;

Map 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

@Componentpublic interfaceINrrsSuper {

@SelectProvider(type= SuperProvider.class,method = "findall")

List> findAll(Mapparam) ;

@SelectProvider(type= SuperProvider.class,method = "findcount")int findCount(Mapparam) ;

@SelectProvider(type=SuperProvider.class,method = "findsql")

List> findSql(Mapparam) ;

@InsertProvider(type=SuperProvider.class,method = "save")void save(Mapdata) ;

@UpdateProvider(type= SuperProvider.class,method = "update")int update(Mapparam) ;

@DeleteProvider(type= SuperProvider.class,method = "delete")int delete(Mapparam) ;

}

对参数进行拼接和分页等

public class SuperProvider {

public String findall(Map 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 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 param) throws Exception{

String sql = param.get("sql").toString() ;

return sql ;

}

public String save(Map 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 param){

String table = param.get("table").toString() ;

String where = param.getOrDefault("where","").toString() ;

StringBuilder where0 = new StringBuilder("") ;

if (where.equals("")){

Map pp = (Map)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 data = (Map)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 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() ;

}

}