springboot+jdbctemplate+mysql示例

package com.intepower.mysqldemo.demo.dao; import com.alibaba.fastjson.JSON; import com.alibaba.fastjson.JSONObject; import com.alibaba.fastjson.TypeReference; import com.intepower.mysqldemo.demo.utils.CommUtil; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.context.annotation.Lazy; import org.springframework.dao.DataAccessException; import org.springframework.jdbc.core.BatchPreparedStatementSetter; import org.springframework.jdbc.core.CallableStatementCallback; import org.springframework.jdbc.core.CallableStatementCreator; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Repository; import java.sql.CallableStatement; import java.sql.PreparedStatement; import java.sql.SQLException; import java.sql.Types; import java.util.ArrayList; import java.util.LinkedHashMap; import java.util.List; import java.util.Map; @Repository public class Dao implements IDao { @Lazy @Autowired private JdbcTemplate jdbcTemplate; /** The Table name to access */ private String tableName=""; public Dao() {} public void setTableName(String tableName) { String prex="";//=Global.sysConfig.getTableprex(); this.tableName=(prex == null ||prex.equals("")?tableName:prex+tableName); } /** * Query recode by specified id. * @return */ @Override public List<Map<String, Object>> select() { return this.jdbcTemplate.queryForList( "select * from "+tableName); } /** * Query all recodes. * @param fields Fields list(with comma to split) * @param orderClause Order Clause，ex:id,name desc * @return List */ @Override public List<Map<String, Object>> select(String fields,String orderClause) { String fld,ord=""; if(fields==null||fields.equals("")||fields.isEmpty()) fld="*"; else fld=fields; if(orderClause!=null&&orderClause.equals("")&&orderClause.isEmpty()) ord=" ORDER BY "+orderClause; return this.jdbcTemplate.queryForList("select "+fld+" from "+tableName+ord); } /** * Query recode by specified id. * @param fields Fields list(with comma to split) * @param id * @return */ @Override public List<Map<String, Object>> select(int id,String fields) { String fld,ord=""; if(fields==null||fields.equals("")||fields.isEmpty()) fld="*"; else fld=fields; return this.jdbcTemplate.queryForList( "select "+fld+" from "+tableName+" where id=?",id); } /** * Query recodes by where clause. * @param fields Fields list(with comma to split) * @param orderClause Order Clause，ex:id,name desc * @param where * @return */ @Override public List<Map<String, Object>> select(String fields,Map<String,Object> where,String orderClause) { String fld,ord=""; if(fields==null||fields.equals("")||fields.isEmpty()) fld="*"; else fld=fields; if(orderClause!=null&&orderClause.equals("")&&orderClause.isEmpty()) ord=" ORDER BY "+orderClause; List<Object> params=new ArrayList<Object>(); String str=""; for (Map.Entry<String, Object> entry : where.entrySet()) { str=(str==""?"":" AND ")+entry.getKey()+"=?"; params.add(entry.getValue()); } String sql="select "+fld+" from "+tableName+" where "+str+ord; return this.jdbcTemplate.queryForList(sql, params.toArray()); } /** * Query recode by specified id. * @param fields Fields list(with comma to split) * @param orderClause Order Clause，ex:id,name desc * @param where String of where, like "name='Li' AND age>20" * @return */ @Override public List<Map<String, Object>> select(String fields,String where,String orderClause) { String fld,ord="",w=""; if(fields==null||fields.equals("")||fields.isEmpty()) fld="*"; else fld=fields; if(orderClause!=null && !orderClause.equals("") && !orderClause.isEmpty()) ord=" ORDER BY "+orderClause; if(where!=null && !where.equals("") && !where.isEmpty()) w=" WHERE "+where; return this.jdbcTemplate.queryForList( "select "+fld+" from "+tableName + w + ord); } /** * 是否存在记录 * @param where where clause * @return */ @Override public boolean exists(String where) { int n=this.count(where); return n>0; } /** * Query recode by specified id. * @param fields Fields list(with comma to split) * @param orderClause Order Clause，ex:id,name desc * @param where String of where, like "name='Li' AND age>20" * @return */ @Override public List<Map<String, Object>> select(String fields,String where,String orderClause,int page,int limit) { String fld,ord="",w=""; if(fields==null||fields.equals("")||fields.isEmpty()) fld="*"; else fld=fields; if(orderClause!=null && !orderClause.equals("") && !orderClause.isEmpty()) ord=" ORDER BY "+orderClause; if(where!=null && !where.equals("") && !where.isEmpty()) w=" WHERE "+where; String sql="select "+fld+" from "+tableName+w+ord; sql += " LIMIT " +(page-1) * limit ; if(this._count<1 ) this.count(where); if(this._count>page*limit) sql+="," + limit; else sql+="," +(this._count-(page-1) * limit); return this.jdbcTemplate.queryForList(sql); } private int _count=-1; /** * Get count * @param where where cluase * @return */ public int count(String where) { int cnt=0; String sql="SELECT COUNT(*) count FROM "+this.tableName+(where!=null && where!=""?" WHERE "+where:""); List<Map<String, Object>> rows=this.jdbcTemplate.queryForList(sql); if(rows!=null && rows.size()>0) cnt=Integer.parseInt(rows.get(0).get("count").toString()); this._count=cnt; return cnt; } /** * Delete recode by specified id. * * @param id * @return */ @Override public boolean delete(int id) { int rows= jdbcTemplate.update("DELETE FROM "+this.tableName+" WHERE ID = ?",id); return rows>0; } /** * Delete all recodes in id list. * * @param ids * @return */ @Override public boolean delete(List<Integer> ids) { return false; } /** * Delete all recodes by where clause. * * @param where * @return */ @Override public boolean delete(String where) { if(where==null || where.equals("")) return false; int rows= jdbcTemplate.update("DELETE FROM "+this.tableName+" WHERE "+where); return rows>0; } /** * Create a recode by json string. * * @param json * @return The id of the created recode. * @apiNote The json may not include id key. If has, it must is 0. */ @Override public int create(String json) { SqlStatement ss=this.jsonToSqlStatement(json); jdbcTemplate.update(ss.getSql(),ss.getParams()); return 0; } /** * Update a recode by json string. * * @param json * @return * @apiNote The json must has id k-v,and id must GT 0. */ @Override public boolean update(String json) { SqlStatement ss=this.jsonToSqlStatement(json); jdbcTemplate.update(ss.getSql(),ss.getParams()); return true; } /** * Insert or Update by id's value in json. * * @param json * @return * @apiNote If id's value is 0, then Insert. Otherwise, updat