DAO设计

最新推荐文章于 2024-08-15 14:47:41 发布

weixin_34187862

最新推荐文章于 2024-08-15 14:47:41 发布

阅读量71

点赞数

文章标签： java 数据库 python

原文链接：https://my.oschina.net/u/224865/blog/610050

版权

2019独角兽企业重金招聘Python工程师标准>>>

引用

提供不同数据源和方言实现智能分页，因Spring单例模式，可以利用Cglib实现动态数据源切换方案,基础东西不讲了，直接看代码。

引用

持久超接口,获取操作模板，可以是JdbcTemplate、SqlSessionTemplate等

Java代码

package com.sunshine.basic.dao;
public interface SuperDao<T> {
/**
* 获取操作模板
* @return
*/
T getTemplate();
}

引用

查询+分页操作接口

Java代码

package com.sunshine.basic.dao;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import org.springframework.jdbc.core.JdbcTemplate;
import com.sunshine.basic.dao.page.PagingParameter;
import com.sunshine.basic.exception.DaoAccessException;
/**
* 查询+分页操作接口
* @author OY
* @since 2016/01/20
* @see V2.0.0
*/
public interface JdbcQueryDao extends SuperDao<JdbcTemplate>{
/***
* 查询单个字段值
* 采用?占位符,如 name=?
* @param sql
* @param paras
* @return
* @throws DaoAccessException
*/
Object getField(String sql, Object...paras) throws DaoAccessException;
/**
* 查询单个字段值
* 采用?占位符
* @param sql
* @param paras
* @return
* @throws DaoAccessException
*/
Object getField(String sql, List<Object> paras) throws DaoAccessException;
/**
* 查询单个字段值
* 采用名称占位符,如 name=:name
* @param sql
* @param paras key与占位符名称一致
* @return
* @throws DaoAccessException
*/
Object getField(String sql, Map<String, Object> paras) throws DaoAccessException;
//===================================================================================
/**
* 查询单个对象
* 采用?占位符,如 name=?
* @param sql
* @param paras
* @return
*/
Map<String, Object> getObject(String sql, Object...paras) throws DaoAccessException;
/**
* 查询单个对象
* 采用?占位符,如 name=?
* @param sql
* @param paras
* @return
* @throws DaoAccessException
*/
Map<String, Object> getObject(String sql, List<Object> paras) throws DaoAccessException;
/**
* 查询单个对象
* 采用名称占位符,如 name=:name
* @param sql
* @param paras key与占位符名称一致
* @return
* @throws DaoAccessException
*/
Map<String, Object> getObject(String sql, Map<String, Object> paras) throws DaoAccessException;
/**
* 查询单个对象(封装)
* 采用?占位符,如 name=?
* @param sql
* @param classz
* @param paras
* @return
*/
<T> T getObject(String sql, Class<T> classz, Object...paras) throws DaoAccessException;
/**
* 查询单个对象(封装)
* 采用?占位符,如 name=?
* @param sql
* @param classz
* @param paras
* @return
* @throws DaoAccessException
*/
<T> T getObject(String sql, Class<T> classz, List<Object> paras) throws DaoAccessException;
/**
* 查询单个对象(封装)
* 采用名称占位符,如 name=:name
* @param sql
* @param classz
* @param paras key与占位符名称一致
* @return
* @throws DaoAccessException
*/
<T> T getObject(String sql, Class<T> classz, Map<String, Object> paras) throws DaoAccessException;
//====================================================================================================
/**
* 查询列表
* 采用?占位符,如 name=?
* @param sql SQL语句
* @param paras 参数
* @return
* @throws DaoAccessException
*/
List<Map<String, Object>> getList(String sql, Object...paras) throws DaoAccessException;
/**
* 查询列表
* 采用?占位符,如 name=?
* @param sql
* @param paras
* @return
* @throws DaoAccessException
*/
List<Map<String, Object>> getList(String sql, List<Object> paras) throws DaoAccessException;
/**
* 查询列表
* @param sql
* @param paras
* @return
* @throws DaoAccessException
*/
List<Map<String, Object>> getList(String sql, Map<String, Object> paras) throws DaoAccessException;
/**
* 查询列表(封装)
* 采用?占位符,如 name=?
* @param sql SQL语句
* @param classz 结果封装类
* @param paras 参数
* @return
* @throws DaoAccessException
*/
<T> List<T> getList(String sql, Class<T> classz, Object...paras) throws DaoAccessException;
/**
* 查询列表(封装)
* 采用?占位符,如 name=?
* @param sql
* @param classz
* @param paras
* @return
* @throws DaoAccessException
*/
<T> List<T> getList(String sql, Class<T> classz, List<Object> paras) throws DaoAccessException;
/**
* 查询列表(封装)
* 采用名称占位符,如 name=:name
* @param sql
* @param classz
* @param paras key与占位符名称一致
* @return
* @throws DaoAccessException
*/
<T> List<T> getList(String sql, Class<T> classz, Map<String, Object> paras) throws DaoAccessException;
//====================================================================================================
/**
* 查询总记录数
* 采用?占位符,如 name=?
* @param sql 查询SQL
* @param limit 查询限制,limit=0查询不受限制
* @return
* @throws SQLException
* @throws DaoAccessException
*/
public int getRecordCounts(String sql, int limit, Object...paras)throws DaoAccessException;
/**
* 查询记录总数
* 采用?占位符,如 name=?
* @param sql
* @param limit
* @param paras
* @return
* @throws DaoAccessException
*/
public int getRecordCounts(String sql, int limit, List<Object> paras)throws DaoAccessException;
/**
* 查询记录总数
* 采用名称占位符,如 name=:name
* @param sql
* @param limit
* @param paras key与占位符名称一致
* @return
* @throws DaoAccessException
*/
public int getRecordCounts(String sql, int limit, Map<String, Object> paras)throws DaoAccessException;
/**
* 查询分页列表
* 采用?占位符,如 name=?
* @param sql 查询SQL
* @param pagingParameter 分页对象,pagingParameter=null或无参数构分页对象,不做分页
* @return
* @throws SQLException
* @throws DaoAccessException
*/
public List<Map<String, Object>> getRecordData(String sql, PagingParameter pagingParameter, Object...paras)throws DaoAccessException;
/**
* 查询分页列表
* 采用?占位符,如 name=?
* @param sql
* @param pagingParameter
* @param paras
* @return
* @throws DaoAccessException
*/
public List<Map<String, Object>> getRecordData(String sql, PagingParameter pagingParameter, List<Object> paras)throws DaoAccessException;
/**
* 查询分页列表
* 采用名称占位符,如 name=:name
* @param sql
* @param pagingParameter
* @param paras key与占位符名称一致
* @return
* @throws DaoAccessException
*/
public List<Map<String, Object>> getRecordData(String sql, PagingParameter pagingParameter, Map<String, Object> paras)throws DaoAccessException;
/**
* 查询分页列表(封装)
* 采用?占位符,如 name=?
* @param <T>
* @param sql 查询SQL
* @param pagingParameter 分页对象,pagingParameter=null或无参数构分页对象,不做分页
* @param classz
* @return
* @throws SQLException
* @throws DaoAccessException
*/
public <T> List<T> getRecordData(String sql, Class<T> classz, PagingParameter pagingParameter, Object...paras)throws DaoAccessException;
/**
* 查询分页列表(封装)
* 采用?占位符,如 name=?
* @param sql
* @param classz
* @param pagingParameter
* @param paras
* @return
* @throws DaoAccessException
*/
public <T> List<T> getRecordData(String sql, Class<T> classz, PagingParameter pagingParameter, List<Object> paras)throws DaoAccessException;
/**
* 查询分页列表(封装)
* 采用名称占位符,如 name=:name
* @param sql
* @param classz
* @param pagingParameter
* @param paras key与占位符名称一致
* @return
* @throws DaoAccessException
*/
public <T> List<T> getRecordData(String sql, Class<T> classz, PagingParameter pagingParameter, Map<String, Object> paras)throws DaoAccessException;
//==================================================================================================================
/**
* 分页查询结果
* 采用?占位符,如 name=?
* @param sql
* @param curPage
* @param pageSize
* @param paras
* @return
*/
public Map<String, Object> getPageForMap(String sql, int curPage, int pageSize, Object...paras)throws DaoAccessException;
/**
* 分页查询结果
* 采用?占位符,如 name=?
* @param sql
* @param curPage
* @param pageSize
* @param paras
* @return
* @throws DaoAccessException
*/
public Map<String, Object> getPageForMap(String sql, int curPage, int pageSize, List<Object> paras)throws DaoAccessException;
/**
* 分页查询结果
* 采用名称占位符,如 name=:name
* @param sql
* @param curPage
* @param pageSize
* @param paras key与占位符名称一致
* @return
* @throws DaoAccessException
*/
public Map<String, Object> getPageForMap(String sql, int curPage, int pageSize, Map<String, Object> paras)throws DaoAccessException;
/**
* 分页查询结果(封装)
* 采用?占位符,如 name=?
* @param sql
* @param curPage
* @param pageSize
* @param clazz
* @param paras
* @return
*/
public Map<String, Object> getPageForMap(String sql, int curPage, int pageSize, Class<?> clazz, Object...paras)throws DaoAccessException;
/**
* 分页查询结果(封装)
* 采用?占位符,如 name=?
* @param sql
* @param curPage
* @param pageSize
* @param clazz
* @param paras
* @return
* @throws DaoAccessException
*/
public Map<String, Object> getPageForMap(String sql, int curPage, int pageSize, Class<?> clazz, List<Object> paras)throws DaoAccessException;
/**
* 分页查询结果(封装)
* 采用名称占位符,如 name=:name
* @param sql
* @param curPage
* @param pageSize
* @param clazz
* @param paras key与占位符名称一致
* @return
* @throws DaoAccessException
*/
public Map<String, Object> getPageForMap(String sql, int curPage, int pageSize, Class<?> clazz, Map<String, Object> paras)throws DaoAccessException;
}

Java代码

/*更新接口*/
package com.sunshine.basic.dao;
import java.util.List;
import java.util.Map;
import org.springframework.jdbc.core.JdbcTemplate;
import com.sunshine.basic.exception.DaoAccessException;
public interface JdbcUpdateDao extends SuperDao<JdbcTemplate>{
void update(String sql, Object...paras) throws DaoAccessException;
void update(String sql, List<Object> paras) throws DaoAccessException;
void update(String sql, Map<String, Object> paras) throws DaoAccessException;
}

Java代码

/*插入接口*/
package com.sunshine.basic.dao;
import java.util.List;
import java.util.Map;
import org.springframework.jdbc.core.JdbcTemplate;
import com.sunshine.basic.exception.DaoAccessException;
public interface JdbcInsertDao extends SuperDao<JdbcTemplate>{
void insert(String sql, Object...paras) throws DaoAccessException;
void insert(String sql, List<Object> paras) throws DaoAccessException;
void insert(String sql, Map<String, Object> paras) throws DaoAccessException;
}

引用

操作实现，暂未实现更新、插入。定义模板获取方法、SQL解析器由子类实现（设计模式之模板模式）

Java代码

package com.sunshine.basic.dao;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.log4j.Logger;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import com.sunshine.basic.dao.page.DataStore;
import com.sunshine.basic.dao.page.PagingParameter;
import com.sunshine.basic.dao.parser.AbstractParser;
import com.sunshine.basic.dao.parser.Dialect;
import com.sunshine.basic.dao.parser.Parser;
import com.sunshine.basic.exception.DaoAccessException;
/**
* 持久操作
* @author OY
* @since 2016/01/20
* @see V2.0.0
*/
public abstract class AbstractJdbcCommDao implements JdbcQueryDao, JdbcInsertDao, JdbcUpdateDao{
private Logger log = Logger.getLogger(getClass());
private NamedParameterJdbcTemplate nameJdbTemplate;
private JdbcTemplate jdbcTemplate;
public NamedParameterJdbcTemplate getNamedJdbcTemplate(){
nameJdbTemplate = new NamedParameterJdbcTemplate(
getTemplate());
return nameJdbTemplate;
}
/**
* 子类实现获取JdbcTemplate
* @return
* @throws DaoAccessException
*/
public abstract JdbcTemplate getSubJdbcTemplate() throws DaoAccessException ;
/**
* 子类实现解析器(根据方言构造分页语句)
* @return
* @throws DaoAccessException
*/
public abstract Parser getParser() throws DaoAccessException;
@Override
public final JdbcTemplate getTemplate() {
try {
jdbcTemplate = getSubJdbcTemplate();
if(jdbcTemplate == null) {
log.info("jdbcTemplate is not exits!");
throw new IllegalAccessException("jdbcTemplate is not exits!");
//return SpringApplicationContext.getBean(DEFAULT_JDBC_TEMPLATE, JdbcTemplate.class);
}
} catch (Exception e) {
log.error("jdbcTemplate is not exits!");
e.printStackTrace();
}
return jdbcTemplate;
}
@Override
public Object getField(String sql, Object... paras)
throws DaoAccessException {
log.info("jcbksql-" + sql);
Object result = null;
try{
result = getTemplate().queryForObject(sql, paras, Object.class);
}catch(EmptyResultDataAccessException e){
//不做处理
}
return result;
}
@Override
public Object getField(String sql, List<Object> paras)
throws DaoAccessException {
log.info("jcbksql-" + sql);
return getField(sql, paras.toArray());
}
@Override
public Object getField(String sql, Map<String, Object> paras)
throws DaoAccessException {
log.info("jcbksql-" + sql);
Object result = null;
try{
result = getNamedJdbcTemplate().queryForObject(sql, paras, Object.class);
}catch(EmptyResultDataAccessException e){
//不做处理
}
return result;
}
@Override
public Map<String, Object> getObject(String sql, Object... paras)
throws DaoAccessException {
log.info("jcbksql-" + sql);
Map<String, Object> result = null;
try{
result = getTemplate().queryForMap(sql, paras);
}catch(EmptyResultDataAccessException e){
//不做处理
}
return result;
}
@Override
public Map<String, Object> getObject(String sql, List<Object> paras)
throws DaoAccessException {
log.info("jcbksql-" + sql);
return getObject(sql, paras.toArray());
}
@Override
public Map<String, Object> getObject(String sql, Map<String, Object> paras)
throws DaoAccessException {
log.info("jcbksql-" + sql);
Map<String, Object> result = null ;
try{
result = getNamedJdbcTemplate().queryForMap(sql, paras);
}catch(EmptyResultDataAccessException e){
//不做处理
}
return result;
}
@Override
public <T> T getObject(String sql, Class<T> classz, Object... paras)
throws DaoAccessException {
log.info("jcbksql-" + sql);
T result = null;
try{
result = getTemplate().queryForObject(sql, paras, new BeanPropertyRowMapper<T>(classz));
}catch(EmptyResultDataAccessException e){
//不做处理
}
return result;
}
@Override
public <T> T getObject(String sql, Class<T> classz, List<Object> paras)
throws DaoAccessException {
log.info("jcbksql-" + sql);
return getObject(sql, classz, paras.toArray());
}
@Override
public <T> T getObject(String sql, Class<T> classz,
Map<String, Object> paras) throws DaoAccessException {
log.info("jcbksql-" + sql);
T result = null;
try{
result = getNamedJdbcTemplate().queryForObject(sql, paras, new BeanPropertyRowMapper<T>(classz));
}catch(EmptyResultDataAccessException e){
//不做处理
}
return result;
}
@Override
public List<Map<String, Object>> getList(String sql, Object... paras)
throws DaoAccessException {
log.info("jcbksql-" + sql);
return getTemplate().queryForList(sql, paras);
}
@Override
public List<Map<String, Object>> getList(String sql, List<Object> paras)
throws DaoAccessException {
log.info("jcbksql-" + sql);
return getList(sql, paras.toArray());
}
@Override
public List<Map<String, Object>> getList(String sql,
Map<String, Object> paras) throws DaoAccessException {
log.info("jcbksql-" + sql);
return getNamedJdbcTemplate().queryForList(sql, paras);
}
@Override
public <T> List<T> getList(String sql, Class<T> classz, Object... paras)
throws DaoAccessException {
log.info("jcbksql-" + sql);
return getTemplate().query(sql, paras, new BeanPropertyRowMapper<T>(classz));
}
@Override
public <T> List<T> getList(String sql, Class<T> classz, List<Object> paras)
throws DaoAccessException {
log.info("jcbksql-" + sql);
return getList(sql, classz, paras.toArray());
}
@Override
public <T> List<T> getList(String sql, Class<T> classz,
Map<String, Object> paras) throws DaoAccessException {
log.info("jcbksql-" + sql);
return getNamedJdbcTemplate().query(sql, paras, new BeanPropertyRowMapper<T>(classz));
}
@Override
public int getRecordCounts(String sql, int limit, Object... paras)
throws DaoAccessException {
log.info("jcbksql-" + sql);
String cSql = getParser().getCountSql(sql);
log.info("jcbksql-count-" + cSql);
Object result = getField(cSql, paras);
return Integer.valueOf((result == null)?"0":String.valueOf(result));
}
@Override
public int getRecordCounts(String sql, int limit, List<Object> paras)
throws DaoAccessException {
return getRecordCounts(sql, limit, paras.toArray());
}
@Override
public int getRecordCounts(String sql, int limit, Map<String, Object> paras)
throws DaoAccessException {
log.info("jcbksql-" + sql);
String cSql = getParser().getCountSql(sql);
log.info("jcbksql-count-" + cSql);
Object result = getField(cSql, paras);
return Integer.valueOf((result==null)?"0":String.valueOf(result));
}
/**
* 增加分页参数(分页启始行、页大小)
*/
private List<Object> addPageParameters(List<Object> paras, PagingParameter pagingParameter, Parser parser){
List<Object> plist = new ArrayList<Object>(2);
/* 结束行或页大小 */
plist.add(pagingParameter.getEndRow(parser.getDialect()));
/* 开始 */
plist.add(pagingParameter.getStartRow());
Dialect dialect = parser.getDialect();
switch (dialect) {
case mysql:
Collections.reverse(plist); /*MYSQL*/
break;
default:
break;
}
if(paras != null){
List<Object> temp = Collections.synchronizedList(paras);
plist.addAll(0, temp);
}
return plist;
}
/**
* 增加分页参数(分页启始行、页大小)
*/
private Map<String, Object> addPageParameters(Map<String, Object> paras, PagingParameter pagingParameter, Parser parser){
Map<String, Object> pMap = new HashMap<String, Object>(2);
pMap.put(AbstractParser.START_INDEX_NAME, pagingParameter.getStartRow());
pMap.put(AbstractParser.END_INDEX_NAME, pagingParameter.getEndRow(parser.getDialect()));
if(paras != null){
paras.putAll(pMap);
return paras;
}
return pMap;
}
@Override
public List<Map<String, Object>> getRecordData(String sql,
PagingParameter pagingParameter, Object... paras)
throws DaoAccessException {
return getRecordData(sql, pagingParameter, Arrays.asList(paras));
}
@Override
public List<Map<String, Object>> getRecordData(String sql,
PagingParameter pagingParameter, List<Object> paras)
throws DaoAccessException {
log.info("jcbksql-" + sql);
Parser parser = getParser();
String pSql = parser.getPageSql(sql);
log.info("jcbksql-page-" + pSql + " dialect-" + parser.getDialect().toString());
List<Object> _paras = addPageParameters(paras, pagingParameter, parser);
return getList(pSql, _paras);
}
@Override
public List<Map<String, Object>> getRecordData(String sql,
PagingParameter pagingParameter, Map<String, Object> paras)
throws DaoAccessException {
log.info("jcbksql-" + sql);
Parser parser = getParser();
String pSql = parser.getPageSqlForPlace(sql);
log.info("jcbksql-page-" + pSql + " dialect-" + parser.getDialect().toString());
Map<String, Object> _paras = addPageParameters(paras, pagingParameter, parser);
return getList(pSql, _paras);
}
@Override
public <T> List<T> getRecordData(String sql, Class<T> classz,
PagingParameter pagingParameter, Object... paras)
throws DaoAccessException {
return getRecordData(sql, classz, pagingParameter, Arrays.asList(paras));
}
@Override
public <T> List<T> getRecordData(String sql, Class<T> classz,
PagingParameter pagingParameter, List<Object> paras)
throws DaoAccessException {
log.info("jcbksql-" + sql);
Parser parser = getParser();
String pSql = parser.getPageSqlForPlace(sql);
log.info("jcbksql-page-" + pSql + " dialect-" + parser.getDialect().toString());
List<Object> _paras = addPageParameters(paras, pagingParameter, parser);
return getList(pSql, classz, _paras);
}
@Override
public <T> List<T> getRecordData(String sql, Class<T> classz,
PagingParameter pagingParameter, Map<String, Object> paras)
throws DaoAccessException {
log.info("jcbksql-" + sql);
Parser parser = getParser();
String pSql = parser.getPageSqlForPlace(sql);
log.info("jcbksql-page-" + pSql + " dialect-" + parser.getDialect().toString());
Map<String, Object> _paras = addPageParameters(paras, pagingParameter, parser);
return getList(pSql, classz, _paras);
}
@Override
public Map<String, Object> getPageForMap(String sql, int curPage,
int pageSize, Object... paras) throws DaoAccessException {
return getPageForMap(sql, curPage, pageSize, Arrays.asList(paras));
}
@Override
public Map<String, Object> getPageForMap(String sql, int curPage,
int pageSize, List<Object> paras) throws DaoAccessException {
int total = getRecordCounts(sql, 0, paras);
PagingParameter pp = new PagingParameter(curPage, pageSize, total);
List<Map<String, Object>> list = getRecordData(sql, pp, paras);
return new DataStore(total,list).getEntity();
}
@Override
public Map<String, Object> getPageForMap(String sql, int curPage,
int pageSize, Map<String, Object> paras) throws DaoAccessException {
int total = getRecordCounts(sql, 0, paras);
PagingParameter pp = new PagingParameter(curPage, pageSize, total);
List<Map<String, Object>> list = getRecordData(sql, pp, paras);
return new DataStore(total,list).getEntity();
}
@Override
public Map<String, Object> getPageForMap(String sql, int curPage,
int pageSize, Class<?> clazz, Object... paras)
throws DaoAccessException {
return getPageForMap(sql, curPage, pageSize, clazz, Arrays.asList(paras));
}
@Override
public Map<String, Object> getPageForMap(String sql, int curPage,
int pageSize, Class<?> clazz, List<Object> paras)
throws DaoAccessException {
int total = getRecordCounts(sql, 0, paras);
PagingParameter pp = new PagingParameter(curPage, pageSize, total);
List<?> list = getRecordData(sql, clazz, pp, paras);
return new DataStore(total,list).getEntity();
}
@Override
public Map<String, Object> getPageForMap(String sql, int curPage,
int pageSize, Class<?> clazz, Map<String, Object> paras)
throws DaoAccessException {
int total = getRecordCounts(sql, 0, paras);
PagingParameter pp = new PagingParameter(curPage, pageSize, total);
List<?> list = getRecordData(sql, clazz, pp, paras);
return new DataStore(total,list).getEntity();
}
@Override
public void update(String sql, Object... paras) throws DaoAccessException {
// TODO Auto-generated method stub
throw new DaoAccessException("暂不支持该操作!");
}
@Override
public void update(String sql, List<Object> paras)
throws DaoAccessException {
// TODO Auto-generated method stub
throw new DaoAccessException("暂不支持该操作!");
}
@Override
public void update(String sql, Map<String, Object> paras)
throws DaoAccessException {
// TODO Auto-generated method stub
throw new DaoAccessException("暂不支持该操作!");
}
@Override
public void insert(String sql, Object... paras) throws DaoAccessException {
// TODO Auto-generated method stub
throw new DaoAccessException("暂不支持该操作!");
}
@Override
public void insert(String sql, List<Object> paras)
throws DaoAccessException {
// TODO Auto-generated method stub
throw new DaoAccessException("暂不支持该操作!");
}
@Override
public void insert(String sql, Map<String, Object> paras)
throws DaoAccessException {
// TODO Auto-generated method stub
throw new DaoAccessException("暂不支持该操作!");
}
}

Java代码

/*为了实现动态修改数据源名称，增加了适配类*/
package com.sunshine.basic.dao;
import org.springframework.beans.BeansException;
import org.springframework.jdbc.core.JdbcTemplate;
import com.sunshine.basic.dao.AbstractJdbcCommDao;
import com.sunshine.basic.dao.parser.AbstractParser;
import com.sunshine.basic.dao.parser.Dialect;
import com.sunshine.basic.dao.parser.Parser;
import com.sunshine.basic.exception.DaoAccessException;
import com.sunshine.basic.tools.ApplicationContextTools;
/**
* 利用JdbcAdapterDaoProxy生成子类代理，然后修改数据源名称
* @see com.sunshine.monitor.comm.dao.adapter.JdbcAdapterDaoProxy
* @author OY
*
*/
public abstract class AbstractJdbcAdapterDao extends AbstractJdbcCommDao {
/* 静态数据源 */
private String jdbcTemplateName;
private Dialect dialect;
/**
* 子类实现静态JdbcTemplate操作模板及方言
* @param jdbcTemplateName
* @param dialect
*/
public AbstractJdbcAdapterDao(String jdbcTemplateName, Dialect dialect){
this.jdbcTemplateName = jdbcTemplateName;
this.dialect = dialect;
}
/*动态修改数据源名称*/
public final void setJdbcTemplateName(String jdbcTemplateName){
this.jdbcTemplateName = jdbcTemplateName;
}
@Override
public final JdbcTemplate getSubJdbcTemplate() throws DaoAccessException {
JdbcTemplate jdbcTemplate = null;
try {
jdbcTemplate = ApplicationContextTools.getBean(
this.jdbcTemplateName, JdbcTemplate.class);
if(jdbcTemplate == null)
throw new DaoAccessException("JdbcTemplate实例访问失败!");
} catch (BeansException e) {
e.printStackTrace();
throw new DaoAccessException("JdbcTemplate实例访问失败!");
}
return jdbcTemplate;
}
@Override
public Parser getParser() throws DaoAccessException {
return AbstractParser.newParser(this.dialect);
}
}

Java代码

/*Oracle实现类*/
package com.sunshine.basic.dao.jdbc;
import org.springframework.stereotype.Repository;
import com.sunshine.basic.dao.AbstractJdbcAdapterDao;
import com.sunshine.basic.dao.parser.Dialect;
@Repository("oracleJdbcDao")
public class OracleJdbcDao extends AbstractJdbcAdapterDao {
public OracleJdbcDao(){
super("jdbcTemplate", Dialect.of("oracle"));
}
}

Java代码

/*GreenPum实现类*/
package com.sunshine.basic.dao.jdbc;
import org.springframework.stereotype.Repository;
import com.sunshine.basic.dao.AbstractJdbcAdapterDao;
import com.sunshine.basic.dao.parser.Dialect;
/**
* @author oy
*
*/
@Repository("greenPlumJdbcDao")
public class GreenPlumJdbcDao extends AbstractJdbcAdapterDao {
public GreenPlumJdbcDao(){
super("gpJdbcTemplate",Dialect.of("postgresql"));
}
}

Java代码

package com.sunshine.basic.dao.proxy;
import java.lang.reflect.Method;
import net.sf.cglib.proxy.Enhancer;
import net.sf.cglib.proxy.MethodInterceptor;
import net.sf.cglib.proxy.MethodProxy;
/**
* 生成代理对象
* @author oy
*
*/
public class JdbcAdapterDaoProxy implements MethodInterceptor{
private Object target;
public JdbcAdapterDaoProxy(){
}
public Object createInstance(Object target){
this.target = target;
Enhancer enhancer = new Enhancer();
enhancer.setSuperclass(this.target.getClass());
// 回调方法
enhancer.setCallback(this);
// 创建代理对象
return enhancer.create();
}
@Override
public Object intercept(Object obj, Method method, Object[] args,
MethodProxy proxy) throws Throwable {
return proxy.invokeSuper(obj, args);
}
}

引用

数据库方言及SQL智能分页

Java代码

package com.sunshine.basic.dao.parser;
public interface Parser {
/**
* 获取总数sql - 如果要支持其他数据库，修改这里就可以
*
* @param sql 原查询sql
* @return 返回count查询sql
*/
String getCountSql(String sql);
/**
* 获取分页sql - 如果要支持其他数据库，修改这里就可以
*
* @param sql 原查询sql
* @return 返回分页sql
*/
String getPageSql(String sql);
/**
*
* @param sql
* @return
*/
String getPageSqlForPlace(String sql);
/**
* 获取方言
* @return
*/
Dialect getDialect();
/**
* 设置方言
* @param dialect
*/
void setDialect(Dialect dialect);
}

Java代码

package com.sunshine.basic.dao.parser;
import com.sunshine.basic.dao.parser.impl.MysqlParser;
import com.sunshine.basic.dao.parser.impl.OracleParser;
import com.sunshine.basic.dao.parser.impl.PostgreSQLParser;
public abstract class AbstractParser implements Parser{
//处理SQL
public static final SqlParser sqlParser = new SqlParser();
private Dialect dialect;
public static final String END_INDEX_NAME="endIndex";
public static final String START_INDEX_NAME = "startIndex";
public Dialect getDialect(){
return dialect;
}
public void setDialect(Dialect dialect){
this.dialect = dialect;
}
public static Parser newParser(Dialect dialect) {
Parser parser = null;
switch (dialect) {
case postgresql:
parser = new PostgreSQLParser();
break;
case mysql:
parser = new MysqlParser();
break;
case oracle:
parser = new OracleParser();
break;
default:
break;
}
if(parser != null)
parser.setDialect(dialect);
return parser;
}
public String getCountSql(final String sql) {
return sqlParser.getSmartCountSql(sql);
}
public abstract String getPageSql(String sql);
}

Java代码

package com.sunshine.basic.dao.parser.impl;
import com.sunshine.basic.dao.parser.AbstractParser;
public class OracleParser extends AbstractParser {
@Override
public String getPageSql(String sql) {
StringBuilder sqlBuilder = new StringBuilder(sql.length() + 120);
sqlBuilder.append("select * from ( select tmp_page.*, rownum row_id from ( ");
sqlBuilder.append(sql);
sqlBuilder.append(" ) tmp_page where rownum <= ? ) where row_id > ?");
return sqlBuilder.toString();
}
@Override
public String getPageSqlForPlace(String sql) {
StringBuilder sqlBuilder = new StringBuilder(sql.length() + 120);
sqlBuilder.append("select * from ( select tmp_page.*, rownum row_id from ( ");
sqlBuilder.append(sql);
sqlBuilder.append(" ) tmp_page where rownum <= :");
sqlBuilder.append(END_INDEX_NAME);
sqlBuilder.append(") where row_id > :");
sqlBuilder.append(START_INDEX_NAME);
return sqlBuilder.toString();
}
}

Java代码

/*方言*/
package com.sunshine.basic.dao.parser;
public enum Dialect {
mysql, oracle, postgresql;
public static Dialect of(String dialect) {
try {
Dialect d = Dialect.valueOf(dialect);
return d;
} catch (IllegalArgumentException e) {
String dialects = null;
for (Dialect d : Dialect.values()) {
if (dialects == null) {
dialects = d.toString();
} else {
dialects += "," + d;
}
}
throw new IllegalArgumentException("分页dialect参数值错误，可选值为[" + dialects + "]");
}
}
public static String[] dialects() {
Dialect[] dialects = Dialect.values();
String[] ds = new String[dialects.length];
for (int i = 0; i < dialects.length; i++) {
ds[i] = dialects[i].toString();
}
return ds;
}
public static String fromJdbcUrl(String jdbcUrl) {
String[] dialects = dialects();
for (String dialect : dialects) {
if (jdbcUrl.indexOf(":" + dialect + ":") != -1) {
return dialect;
}
}
return null;
}
}

Java代码

/*SQL智分析，依赖jsqlparse.jar*/
package com.sunshine.basic.dao.parser;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
import net.sf.jsqlparser.expression.Alias;
import net.sf.jsqlparser.expression.Function;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.FromItem;
import net.sf.jsqlparser.statement.select.Join;
import net.sf.jsqlparser.statement.select.LateralSubSelect;
import net.sf.jsqlparser.statement.select.OrderByElement;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectBody;
import net.sf.jsqlparser.statement.select.SelectExpressionItem;
import net.sf.jsqlparser.statement.select.SelectItem;
import net.sf.jsqlparser.statement.select.SetOperationList;
import net.sf.jsqlparser.statement.select.SubJoin;
import net.sf.jsqlparser.statement.select.SubSelect;
import net.sf.jsqlparser.statement.select.ValuesList;
import net.sf.jsqlparser.statement.select.WithItem;
import org.apache.log4j.Logger;
/**
* sql解析类，提供更智能的count查询sql
*/
public class SqlParser {
private static final List<SelectItem> COUNT_ITEM;
private static final Alias TABLE_ALIAS;
private Logger log = Logger.getLogger(SqlParser.class);
static {
COUNT_ITEM = new ArrayList<SelectItem>();
COUNT_ITEM.add(new SelectExpressionItem(new Column("count(1)")));
TABLE_ALIAS = new Alias("table_count");
TABLE_ALIAS.setUseAs(false);
}
// 缓存已经修改过的sql
private Map<String, String> CACHE = new ConcurrentHashMap<String, String>();
public void isSupportedSql(String sql) {
if (sql.trim().toUpperCase().endsWith("FOR UPDATE")) {
throw new RuntimeException("分页不支持包含for update的sql");
}
}
/**
* 获取智能的countSql
*
* @param sql
* @return
*/
public String getSmartCountSql(String sql) {
log.info("Sql parse before:" + sql);
//校验是否支持该sql
isSupportedSql(sql);
if (CACHE.get(sql) != null) {
log.info("Count sql parse(Cache):" + CACHE.get(sql));
return CACHE.get(sql);
}
//解析SQL
Statement stmt = null;
try {
stmt = CCJSqlParserUtil.parse(sql);
} catch (Throwable e) {
//无法解析的用一般方法返回count语句
String countSql = getSimpleCountSql(sql);
CACHE.put(sql, countSql);
return countSql;
}
Select select = (Select) stmt;
SelectBody selectBody = select.getSelectBody();
//处理body-去order by
processSelectBody(selectBody);
//处理with-去order by
processWithItemsList(select.getWithItemsList());
//处理为count查询
sqlToCount(select);
String result = select.toString();
CACHE.put(sql, result);
log.info("Sql parse after:" + sql);
return result;
}
/**
* 获取普通的Count-sql
* @param sql 原查询sql
* @return 返回count查询sql
*/
public String getSimpleCountSql(final String sql) {
isSupportedSql(sql);
StringBuilder stringBuilder = new StringBuilder(sql.length() + 40);
stringBuilder.append("select count(*) from (");
stringBuilder.append(sql);
stringBuilder.append(") tmp_count");
return stringBuilder.toString();
}
/**
* 将sql转换为count查询
* @param select
*/
public void sqlToCount(Select select) {
SelectBody selectBody = select.getSelectBody();
// 是否能简化count查询
if (selectBody instanceof PlainSelect && isSimpleCount((PlainSelect) selectBody)) {
((PlainSelect) selectBody).setSelectItems(COUNT_ITEM);
} else {
PlainSelect plainSelect = new PlainSelect();
SubSelect subSelect = new SubSelect();
subSelect.setSelectBody(selectBody);
subSelect.setAlias(TABLE_ALIAS);
plainSelect.setFromItem(subSelect);
plainSelect.setSelectItems(COUNT_ITEM);
select.setSelectBody(plainSelect);
}
}
/**
* 是否可以用简单的count查询方式
* @param select
* @return
*/
public boolean isSimpleCount(PlainSelect select) {
//包含group by的时候不可以
if (select.getGroupByColumnReferences() != null) {
return false;
}
//包含distinct的时候不可以
if (select.getDistinct() != null) {
return false;
}
for (SelectItem item : select.getSelectItems()) {
//select列中包含参数的时候不可以，否则会引起参数个数错误
if (item.toString().contains("?")) {
return false;
}
//如果查询列中包含函数，也不可以，函数可能会聚合列
if (item instanceof SelectExpressionItem) {
if (((SelectExpressionItem) item).getExpression() instanceof Function) {
return false;
}
}
}
return true;
}
/**
* 处理selectBody去除Order by
*
* @param selectBody
*/
public void processSelectBody(SelectBody selectBody) {
if (selectBody instanceof PlainSelect) {
processPlainSelect((PlainSelect) selectBody);
} else if (selectBody instanceof WithItem) {
WithItem withItem = (WithItem) selectBody;
if (withItem.getSelectBody() != null) {
processSelectBody(withItem.getSelectBody());
}
} else {
SetOperationList operationList = (SetOperationList) selectBody;
if (operationList.getPlainSelects() != null && operationList.getPlainSelects().size() > 0) {
List<PlainSelect> plainSelects = operationList.getPlainSelects();
for (PlainSelect plainSelect : plainSelects) {
processPlainSelect(plainSelect);
}
}
if (!orderByHashParameters(operationList.getOrderByElements())) {
operationList.setOrderByElements(null);
}
}
}
/**
* 处理PlainSelect类型的selectBody
*
* @param plainSelect
*/
public void processPlainSelect(PlainSelect plainSelect) {
if (!orderByHashParameters(plainSelect.getOrderByElements())) {
plainSelect.setOrderByElements(null);
}
if (plainSelect.getFromItem() != null) {
processFromItem(plainSelect.getFromItem());
}
if (plainSelect.getJoins() != null && plainSelect.getJoins().size() > 0) {
List<Join> joins = plainSelect.getJoins();
for (Join join : joins) {
if (join.getRightItem() != null) {
processFromItem(join.getRightItem());
}
}
}
}
/**
* 处理WithItem
*
* @param withItemsList
*/
public void processWithItemsList(List<WithItem> withItemsList) {
if (withItemsList != null && withItemsList.size() > 0) {
for (WithItem item : withItemsList) {
processSelectBody(item.getSelectBody());
}
}
}
/**
* 处理子查询
*
* @param fromItem
*/
public void processFromItem(FromItem fromItem) {
if (fromItem instanceof SubJoin) {
SubJoin subJoin = (SubJoin) fromItem;
if (subJoin.getJoin() != null) {
if (subJoin.getJoin().getRightItem() != null) {
processFromItem(subJoin.getJoin().getRightItem());
}
}
if (subJoin.getLeft() != null) {
processFromItem(subJoin.getLeft());
}
} else if (fromItem instanceof SubSelect) {
SubSelect subSelect = (SubSelect) fromItem;
if (subSelect.getSelectBody() != null) {
processSelectBody(subSelect.getSelectBody());
}
} else if (fromItem instanceof ValuesList) {
} else if (fromItem instanceof LateralSubSelect) {
LateralSubSelect lateralSubSelect = (LateralSubSelect) fromItem;
if (lateralSubSelect.getSubSelect() != null) {
SubSelect subSelect = lateralSubSelect.getSubSelect();
if (subSelect.getSelectBody() != null) {
processSelectBody(subSelect.getSelectBody());
}
}
}
//Table时不用处理
}
/**
* 判断Orderby是否包含参数，有参数的不能去
*
* @param orderByElements
* @return
*/
public boolean orderByHashParameters(List<OrderByElement> orderByElements) {
if (orderByElements == null) {
return false;
}
for (OrderByElement orderByElement : orderByElements) {
if (orderByElement.toString().contains("?")) {
return true;
}
}
return false;
}
public static void main(String[] args) {
String countSql = new SqlParser()
.getSmartCountSql("select count(t.hphm) as GCCS, t.hphm as HPHM, t.hpys as HPYS, t.hpzl as HPZL from veh_passrec t where t.hphm like '粤A_____' and t.gcsj > '2010-02-18 00:00:00' and t.gcsj <= '2010-02-21 23:59:59' group by t.hphm, t.hpys, t.hpzl");
System.out.println(countSql);
}
}