package cn.ksource.core.dao;
|
|
import java.lang.reflect.Method;
|
import java.util.ArrayList;
|
import java.util.HashMap;
|
import java.util.Iterator;
|
import java.util.List;
|
import java.util.Map;
|
|
import javax.servlet.http.HttpServletRequest;
|
|
import org.apache.commons.lang.StringUtils;
|
import org.springframework.jdbc.core.JdbcTemplate;
|
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
|
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
|
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
|
import org.springframework.jdbc.support.GeneratedKeyHolder;
|
import org.springframework.jdbc.support.KeyHolder;
|
|
import cn.ksource.core.page.PageBean;
|
import cn.ksource.core.page.PageInfo;
|
import cn.ksource.core.util.ConvertUtil;
|
import cn.ksource.core.util.StringUtil;
|
import cn.ksource.core.web.SplitPage;
|
|
|
|
public class BaseDao {
|
|
private JdbcTemplate jdbcTemplate;
|
|
private NamedParameterJdbcTemplate paraJdbcTemplate;
|
|
|
/**
|
* GG_USER user = baseDao.queryForBean("select * from GG_USER where id=:id", new GG_USER().setId('aasaaa'));
|
* @param <T>
|
* @param sql
|
* @param bean
|
* @return
|
*/
|
public <T> T queryForBean(String sql,BaseBean bean) {
|
List<T> list = this.paraJdbcTemplate.query(sql, bean.getBeanValues(), bean);
|
if (list == null || list.size() == 0) {
|
return null;
|
} else {
|
return list.get(0);
|
}
|
}
|
|
|
/**
|
*
|
* GG_USER user = baseDao.queryForBean("select * from GG_USER where id=:id",
|
new SqlParameter("id","1dcd7d8483c2434dabbb142a76003df9"),
|
new GG_USER());
|
* @param <T>
|
* @param sql
|
* @param param
|
* @param bean
|
* @return
|
*/
|
public <T> T queryForBean(String sql,Map param,BaseBean bean) {
|
List<T> list = this.paraJdbcTemplate.query(sql, param, bean);
|
if (list == null || list.size() == 0) {
|
return null;
|
} else {
|
return list.get(0);
|
}
|
}
|
|
/**
|
* 只提供了对bean中set方法的注入
|
* @param bean
|
* @param map
|
* @return
|
*/
|
private BaseBean map2Bean(BaseBean bean,Map map){
|
Iterator iterator = map.keySet().iterator();
|
Class<?> c = bean.getClass();
|
Method[] methods = c.getDeclaredMethods();
|
try {
|
while (iterator.hasNext()) {
|
String key = iterator.next().toString();
|
for (Method method : methods) {
|
if (StringUtils.equals(method.getName().toLowerCase(), "set"+key.toLowerCase())) {
|
String parameterType = method.getParameterTypes()[0].getSimpleName();
|
if (StringUtils.equals("String", parameterType)) {
|
method.invoke(bean, ConvertUtil.obj2Str(map.get(key)));
|
} else if (StringUtils.equals("Integer", parameterType)) {
|
method.invoke(bean, ConvertUtil.obj2Integer(map.get(key)));
|
} else if (StringUtils.equals("Long", parameterType)) {
|
method.invoke(bean, ConvertUtil.obj2Long(map.get(key)));
|
} else if (StringUtils.equals("Double", parameterType)) {
|
method.invoke(bean, ConvertUtil.obj2Double(map.get(key)));
|
} else {
|
System.out.println(method.getName() + "方法的参数类型为：" + parameterType + "该类型暂时没有处理，请联系杨凯");
|
}
|
}
|
}
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
throw new RuntimeException("com.zzsb.core.utils.ConvertUtil.map2Bean(BaseBean, Map)方法执行失败");
|
}
|
return bean;
|
}
|
|
public <T> T queryForList(String sql,BaseBean baseBean){
|
List<Map> list = this.queryForList(sql, baseBean.getBeanValues());
|
if (list != null && list.size() > 0) {
|
List<BaseBean> beanList = new ArrayList<BaseBean>();
|
for (Map map : list) {
|
beanList.add(map2Bean(baseBean.newInstance(), map));
|
}
|
return (T)beanList;
|
}
|
return (T)new ArrayList<BaseBean>();
|
}
|
|
|
/**
|
* @param sql
|
* @param param
|
* @return
|
*/
|
public List<Map> queryForList(String sql,Map param) {
|
List<Map<String,Object>> list = this.paraJdbcTemplate.queryForList(sql, param);
|
if (list == null) {
|
list = new ArrayList<Map<String,Object>>();
|
}
|
return (List)list;
|
}
|
|
public List<Map> queryForList(String sql) {
|
List<Map<String,Object>> list = this.jdbcTemplate.queryForList(sql);
|
if (list == null) {
|
list = new ArrayList<Map<String,Object>>();
|
}
|
return (List)list;
|
}
|
|
|
/**
|
* ��ҳ��ѯ
|
* @param sql
|
* @param param
|
* @param splitPage
|
* @return
|
* @version V1.0.0
|
* @author �
|
* @date Sep 19, 2013 8:21:07 AM
|
*/
|
public List<Map> queryForSplit(String sql,SqlParameter param,SplitPage splitPage){
|
/*String infoCountSql = "select count(*) from ("+ sql +") as infoCount";
|
int infoCount = ConvertUtil.obj2Integer(this.queryForString(infoCountSql,param));
|
splitPage.setInfoCount(infoCount);
|
String infoListSql = sql + " limit " + (splitPage.getCurrentPageIndex()-1)*splitPage.getPageSize() +"," + splitPage.getPageSize();
|
return queryForList(infoListSql,param);*/
|
|
List<Map> list = queryForList(sql,param);
|
int infoCount = list.size();
|
splitPage.setInfoCount(infoCount);
|
int bin = (splitPage.getCurrentPageIndex()-1)*splitPage.getPageSize();
|
int end = (splitPage.getCurrentPageIndex()-1)*splitPage.getPageSize()+splitPage.getPageSize();
|
if(end>infoCount){
|
end=infoCount;
|
}
|
return list.subList(bin,end );
|
|
}
|
|
|
public Map queryForDataGrid(HttpServletRequest request,String sql,SqlParameter param){
|
int pageindex = ConvertUtil.obj2Integer(request.getParameter("page")); //当前页码
|
int pagesize = ConvertUtil.obj2Integer(request.getParameter("rows")); //每页展示数量
|
|
//TODO 此处分页采用的是将所有数据加载至内存后，使用JAVA分页，需优化
|
List<Map> list = queryForList(sql,param);
|
int infoCount = list.size();
|
int bin = (pageindex-1)* pagesize ;
|
int end = (pageindex-1)* pagesize + pagesize;
|
if(end>infoCount){
|
end=infoCount;
|
}
|
List<Map> subList = list.subList(bin,end);
|
|
Map rootMap = new HashMap();
|
rootMap.put("total", infoCount); //信息总数
|
rootMap.put("rows", subList);
|
return rootMap;
|
}
|
|
|
public Map queryForMap(String sql,Map paramMap){
|
List<Map> list = queryForList(sql, paramMap);
|
if (list == null || list.size() == 0) {
|
return new HashMap();
|
}
|
return list.get(0);
|
}
|
|
|
public Map queryForMap(String sql){
|
Map map = this.queryForMap(sql,new SqlParameter());
|
if (map == null) {
|
map = new HashMap();
|
}
|
return map;
|
}
|
|
|
public String queryForString(String sql,Map paramMap){
|
Map map = queryForMap(sql, paramMap);
|
if (map == null || map.isEmpty()) {
|
return null;
|
} else {
|
return ConvertUtil.obj2Str(map.get(map.keySet().iterator().next()));
|
}
|
}
|
|
public String queryForString(String sql){
|
Map map = queryForMap(sql);
|
if (map == null || map.isEmpty()) {
|
return null;
|
} else {
|
return ConvertUtil.obj2Str(map.get(map.keySet().iterator().next()));
|
}
|
}
|
|
|
public Integer queryForInteger(String sql){
|
Map map = queryForMap(sql);
|
if (map == null || map.isEmpty()) {
|
return null;
|
} else {
|
return ConvertUtil.obj2Integer(map.get(map.keySet().iterator().next()));
|
}
|
}
|
|
public Long queryForLong(String sql){
|
Map map = queryForMap(sql);
|
if (map == null || map.isEmpty()) {
|
return null;
|
} else {
|
return ConvertUtil.obj2Long(map.get(map.keySet().iterator().next()));
|
}
|
}
|
|
public Double queryForDouble(String sql){
|
Map map = queryForMap(sql);
|
if (map == null || map.isEmpty()) {
|
return null;
|
} else {
|
return ConvertUtil.obj2Double(map.get(map.keySet().iterator().next()));
|
}
|
}
|
|
public Double queryForDouble(String sql,Map paramMap){
|
Map map = queryForMap(sql,paramMap);
|
if (map == null || map.isEmpty()) {
|
return null;
|
} else {
|
return ConvertUtil.obj2Double(map.get(map.keySet().iterator().next()));
|
}
|
}
|
|
public Long queryForLong(String sql,Map paramMap){
|
Map map = queryForMap(sql,paramMap);
|
if (map == null || map.isEmpty()) {
|
return null;
|
} else {
|
return ConvertUtil.obj2Long(map.get(map.keySet().iterator().next()));
|
}
|
}
|
|
|
public Integer queryForInteger(String sql,Map paramMap){
|
Map map = queryForMap(sql,paramMap);
|
if (map == null || map.isEmpty()) {
|
return null;
|
} else {
|
return ConvertUtil.obj2Integer(map.get(map.keySet().iterator().next()));
|
}
|
}
|
|
|
|
|
|
/**
|
* @param sql
|
* @param param
|
*/
|
public Integer execute(String sql,Map paramMap) {
|
KeyHolder keyHolder = new GeneratedKeyHolder();
|
SqlParameterSource source = new MapSqlParameterSource(paramMap);
|
this.paraJdbcTemplate.update(sql, source, keyHolder);
|
if (keyHolder.getKey() == null) {
|
return null;
|
}
|
return keyHolder.getKey().intValue();
|
}
|
|
/**
|
*
|
* 在执行insert操作之后获取最新的ID
|
* @return
|
* @version V1.0.0
|
* @author 杨凯
|
* @date Dec 3, 2013 1:35:52 PM
|
*/
|
public int getCurrentPK(){
|
return queryForInteger("SELECT @@IDENTITY");
|
}
|
|
|
public void executeBatch(String sql,List<SqlParameter> paramList){
|
if (paramList == null || paramList.size() == 0) {
|
return;
|
}
|
Map[] maps = new Map[paramList.size()];
|
for (int i = 0; i < paramList.size(); i++) {
|
maps[i] = paramList.get(i);
|
}
|
this.paraJdbcTemplate.batchUpdate(sql, maps);
|
}
|
|
public void executeBatch(List<String> sqlList){
|
String[] sqls = new String[sqlList.size()];
|
for (int i = 0; i < sqlList.size(); i++) {
|
sqls[i] = sqlList.get(i);
|
}
|
this.jdbcTemplate.batchUpdate(sqls);
|
}
|
|
|
public JdbcTemplate getJdbcTemplate() {
|
return jdbcTemplate;
|
}
|
|
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
|
this.jdbcTemplate = jdbcTemplate;
|
}
|
|
public NamedParameterJdbcTemplate getParaJdbcTemplate() {
|
return paraJdbcTemplate;
|
}
|
|
public void setParaJdbcTemplate(NamedParameterJdbcTemplate paraJdbcTemplate) {
|
this.paraJdbcTemplate = paraJdbcTemplate;
|
}
|
|
public Map queryForDataGrid(Integer pageindex, Integer pagesize, String sql, SqlParameter sqlParameter) {
|
if(pageindex==null){
|
pageindex=1;
|
}
|
if(pagesize==null){
|
pagesize=10;//默认
|
}
|
//TODO 此处分页采用的是将所有数据加载至内存后，使用JAVA分页，需优化
|
List<Map> list = queryForList(sql,sqlParameter);
|
int infoCount = list.size();
|
int bin = (pageindex-1)* pagesize ;
|
int end = (pageindex-1)* pagesize + pagesize;
|
if(end>infoCount){
|
end=infoCount;
|
}
|
List<Map> subList = list.subList(bin,end);
|
|
Map rootMap = new HashMap();
|
rootMap.put("total", infoCount); //信息总数
|
rootMap.put("rows", subList);
|
rootMap.put("page",pageindex);
|
return rootMap;
|
}
|
|
public Map queryForDataGrid(Integer pageindex, Integer pagesize, String sql) {
|
if(pageindex==null){
|
pageindex=1;
|
}
|
if(pagesize==null){
|
pagesize=10;//默认
|
}
|
//TODO 此处分页采用的是将所有数据加载至内存后，使用JAVA分页，需优化
|
List<Map> list = queryForList(sql);
|
int infoCount = list.size();
|
int bin = (pageindex-1)* pagesize ;
|
int end = (pageindex-1)* pagesize + pagesize;
|
if(end>infoCount){
|
end=infoCount;
|
}
|
List<Map> subList = list.subList(bin,end);
|
|
Map rootMap = new HashMap();
|
rootMap.put("total", infoCount); //信息总数
|
rootMap.put("rows", subList);
|
rootMap.put("page",pageindex);
|
return rootMap;
|
}
|
|
|
|
/**
|
* 获取分页信息
|
* @param sql
|
* @param paramMap
|
* @return
|
*/
|
public List<Map> queryforSplitPage(HttpServletRequest request,String sql,Map paramMap) {
|
StringBuilder builder = new StringBuilder(sql);
|
String pageSize = request.getParameter("pageSize");
|
String currPage = request.getParameter("currPage");
|
if(!StringUtil.notEmptyNum(pageSize)){
|
pageSize = "10";
|
}
|
if(!StringUtil.notEmptyNum(currPage)){
|
currPage = "1";
|
}
|
int begin = Integer.valueOf(currPage);
|
int size = Integer.valueOf(pageSize);
|
|
builder.append( " LIMIT :begin,:size ");
|
paramMap.put("begin", (begin-1)*size);
|
paramMap.put("size", size);
|
List<Map> result = queryForList(builder.toString(),paramMap);
|
return result;
|
}
|
|
/**
|
* 获取分页信息
|
* @param sql
|
* @param paramMap
|
* @return
|
*/
|
public PageInfo queryforSplitPageInfo(PageInfo pageInfo,String sql,Map paramMap) {
|
StringBuilder builder = new StringBuilder(sql);
|
String pageSize = pageInfo.getPageSize();
|
String currPage = pageInfo.getCurrPage();
|
if(!StringUtil.notEmptyNum(pageSize)){
|
pageSize = "10";
|
}
|
if(!StringUtil.notEmptyNum(currPage)){
|
currPage = "1";
|
}
|
int begin = Integer.valueOf(currPage);
|
int size = Integer.valueOf(pageSize);
|
|
builder.append( " LIMIT :begin,:size ");
|
int beginSize =(begin-1)*size;
|
paramMap.put("begin", (begin-1)*size);
|
paramMap.put("size", size);
|
List<Map> result = queryForList(builder.toString(),paramMap);
|
|
PageInfo info = new PageInfo();
|
info.setRowNum(beginSize);
|
info.setDatas(result);
|
return info;
|
}
|
|
/**
|
* 获取分页信息
|
* @param sql
|
* @param paramMap
|
* @return
|
*/
|
public PageInfo queryforSplitPageInfo(HttpServletRequest request,String sql,Map paramMap) {
|
StringBuilder builder = new StringBuilder(sql);
|
String pageSize = request.getParameter("pageSize");
|
String currPage = request.getParameter("currPage");
|
if(!StringUtil.notEmptyNum(pageSize)){
|
pageSize = "10";
|
}
|
if(!StringUtil.notEmptyNum(currPage)){
|
currPage = "1";
|
}
|
int begin = Integer.valueOf(currPage);
|
int size = Integer.valueOf(pageSize);
|
|
builder.append( " LIMIT :begin,:size ");
|
int beginSize =(begin-1)*size;
|
paramMap.put("begin", (begin-1)*size);
|
paramMap.put("size", size);
|
List<Map> result = queryForList(builder.toString(),paramMap);
|
|
PageInfo info = new PageInfo();
|
info.setRowNum(beginSize);
|
info.setDatas(result);
|
return info;
|
}
|
public PageBean getPageList(String sql, Map param) {
|
Integer pageSize = ConvertUtil.obj2Integer(param.get("pageSize"));
|
Integer page = ConvertUtil.obj2Integer(param.get("page"));
|
Integer start = (page - 1) * pageSize;
|
if (pageSize != null && page != null) {
|
Integer count = queryForInteger("select count(*) from ( " + sql + " ) query_sql ", param);
|
sql = sql + " limit :start,:size ";
|
param.put("start", start);
|
param.put("size", pageSize);
|
List<Map> list = queryForList(sql, param);
|
return new PageBean(page, pageSize, count, list);
|
}
|
return new PageBean();
|
}
|
}
|
