/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package com.quickorm.core.impl;
import com.quickorm.config.Database;
import com.quickorm.core.PagedList;
import com.quickorm.core.QuickormTemplate;
import com.quickorm.dao.DataAccessException;
import com.quickorm.dialect.DefaultDialect;
import com.quickorm.dialect.Dialect;
import com.quickorm.dialect.MySqlDialect;
import com.quickorm.dialect.SqlServerDialect;
import com.quickorm.entity.DBNull;
import com.quickorm.entity.EntityMetaData;
import com.quickorm.entity.SqlAndArgumentsData;
import com.quickorm.support.JdbcAccessor;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.*;
import javax.sql.DataSource;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
/**
*
* @author aaa
*/
public class QuickormTemplateImpl extends JdbcAccessor implements QuickormTemplate {
private final static Log log = LogFactory.getLog(QuickormTemplate.class);
private Map<Class, EntityMetaData> entityClassMetaDataMap;
private Database database = Database.DEFAULT;
private Dialect dialect = new DefaultDialect();
private String showSqlLogLevel = "TRACE";
private boolean showSql = false;
/**
* @return 设置所采用的数据库
*/
public Database getDatabase() {
return database;
}
/**
* @param 获取所采用的数据库
*/
public void setDatabase(Database database) {
this.database = database;
//设置方言
switch (database) {
case MYSQL:
setDialect(new MySqlDialect());
break;
case SQL_SERVER:
setDialect(new SqlServerDialect());
break;
default:
setDialect(new DefaultDialect());
break;
}
}
/**
* @return 获取方言对象
*/
public Dialect getDialect() {
return dialect;
}
/**
* @param 设置方言对象
*/
public void setDialect(Dialect dialect) {
this.dialect = dialect;
log.info(String.format("Quickorm:Using dialect class [%s]", dialect.getClass().getName()));
}
/**
* @return 获取显示SQL的日志级别(默认为TRACE)
*/
public String getShowSqlLogLevel() {
return showSqlLogLevel;
}
/**
* @param 设置显示SQL的日志级别(默认为TRACE),可设置的值有:TRACE、INFO、DEBUG、WARN、ERROR、FATAL
*/
public void setShowSqlLogLevel(String showSqlLogLevel) {
this.showSqlLogLevel = showSqlLogLevel;
}
/**
* @return 是否显示SQL日志
*/
public boolean isShowSql() {
return showSql;
}
/**
* @param 设置是否显示SQL日志
*/
public void setShowSql(boolean showSql) {
this.showSql = showSql;
}
//得到连接
private Connection getConnection() throws SQLException {
return this.getDataSource().getConnection();
}
//得到实体类的元数据
public EntityMetaData getEntityMetaData(Class entityClass) {
EntityMetaData rtnEMD = null;
if (entityClassMetaDataMap.containsKey(entityClass)) {
rtnEMD = entityClassMetaDataMap.get(entityClass);
} else {
rtnEMD = new EntityMetaData(entityClass);
entityClassMetaDataMap.put(entityClass, rtnEMD);
}
return rtnEMD;
}
public QuickormTemplateImpl() {
init();
}
public QuickormTemplateImpl(DataSource dataSource) {
this.setDataSource(dataSource);
init();
}
private void init() {
entityClassMetaDataMap = new LinkedHashMap<Class, EntityMetaData>();
}
//设置对象字段的值
private void setObjectFieldValue(Object target, String columnName, Object value) throws IllegalArgumentException, IllegalAccessException, InstantiationException {
//得到实体元数据
EntityMetaData entityMetaData = this.getEntityMetaData(target.getClass());
//要设置的字段
Field toSetField = entityMetaData.getFieldByDataBaseColumnName(columnName);
//如果没有此字段
if (toSetField == null) {
//如果包含下划线
if (columnName.contains("_")) {
String[] columnNameArray = columnName.split("_");
String firstColumnName = columnNameArray[0];
toSetField = entityMetaData.getFieldByDataBaseColumnName(firstColumnName);
if (toSetField == null) {
return;
}
Class fieldClass = toSetField.getType();
Object fieldObject = toSetField.get(target);
if (fieldObject == null) {
fieldObject = fieldClass.newInstance();
toSetField.set(target, fieldObject);
}
setObjectFieldValue(fieldObject, columnName.substring(firstColumnName.length() + 1), value);
}
} else {
toSetField.set(target, value);
}
}
private <T extends Object> T convertRowSetCurrentRowToObject(ResultSet rs, Class<T> clazz) throws SQLException, InstantiationException, IllegalAccessException {
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
//clazz是否是Map类
boolean isClazzMapClass = clazz == Map.class;
T tObject = null;
Map<String, Object> tMap = null;
if (isClazzMapClass) {
tMap = new LinkedHashMap<String, Object>();
tObject = (T) tMap;
} else {
tObject = clazz.newInstance();
}
for (int i = 1; i <= columnCount; i++) {
String columnName = rsmd.getColumnLabel(i);
Object fieldValue = rs.getObject(i);
if (isClazzMapClass) {
tMap.put(columnName, fieldValue);
} else {
setObjectFieldValue(tObject, columnName, fieldValue);
}
}
return tObject;
}
private <T extends Object> List<T> convertRowSetToObjectList(ResultSet rs, Class<T> clazz, int pageSize) throws SQLException, InstantiationException, IllegalAccessException {
//已读取记录数记数
int readRecordCount = 0;
List<T> rtnList = new ArrayList<T>();
//根据设定的页码与页大小读取数据
while (rs.next()) {
T obj = convertRowSetCurrentRowToObject(rs, clazz);
rtnList.add(obj);
readRecordCount++;
if (pageSize > 0) {
if (readRecordCount >= pageSize) {
break;
}
}
}
return rtnList;
}
private void log_show_sql(String sql) {
String logString = "Quickorm:" + sql;
if (isShowSql()) {
String logLevel = this.getShowSqlLogLevel();
if ("TRACE".equals(logLevel)) {
log.trace(logString);
} else if ("DEBUG".equals(logLevel)) {
log.debug(logString);
} else if ("INFO".equals(logLevel)) {
log.info(logString);
} else if ("WARN".equals(logLevel)) {
log.warn(logString);
} else if ("ERROR".equals(logLevel)) {
log.error(logString);
} else if ("FATAL".equals(logLevel)) {
log.fatal(logString);
} else {
log.trace(logString);
}
}
}
/**
* 根据ID得到实体
*
* @param <T> 范型
* @param entityClass 实体类
* @param id 编号
* @return
*/
@Override
public <T> T get(Class<T> entityClass, Object id) {
//实体元数据
EntityMetaData entityMetaData = getEntityMetaData(entityClass);
String sql = "SELECT * FROM " + this.dialect.addIdentifierSeparatingChar(entityMetaData.getTableName());
//准备条件Map
Map<String, Object> conditionArgumentMap = new LinkedHashMap<String, Object>();
if (id == null) {
id = DBNull.value;
}
conditionArgumentMap.put(dialect.addIdentifierSeparatingChar(entityMetaData.getPrimaryKey()) + " = ?", id);
//执行查询
List<T> tList = this.executeQuery(sql, null, conditionArgumentMap, null, entityClass);
if (tList == null || tList.isEmpty()) {
return null;
} else {
return tList.get(0);
}
}
/**
* 保存实体对象
*
* @param t 实体对象
* @return
*/
@Override
public void save(Object t) {
//实体类
Class entityClass = t.getClass();
//实体元数据
EntityMetaData entityMetaData = getEntityMetaData(entityClass);
//得到SQL语句与参数
SqlAndArgumentsData sqlAndArgumentsData = entityMetaData.getInsertSqlAndArgumentData(t, dialect);
int rowCount = this.executeUpdate(sqlAndArgumentsData.getSql(), sqlAndArgumentsData.getArgumentList(), null);
if (rowCount <= 0) {
throw new DataAccessException("更改的数据行数为0!");
}
}
/**
* 批量保存
*
* @param tList 实体列表
*/
@Override
public void saveBatch(List tList) {
if (tList == null || tList.isEmpty()) {
return;
}
//实体类
Class entityClass = tList.get(0).getClass();
//实体元数据
EntityMetaData entityMetaData = getEntityMetaData(entityClass);
//得到SQL语句与参数
SqlAndArgumentsData sqlAndArgumentsData = entityMetaData.getBatchInsertSqlAndArgumentData(tList, dialect);
int[] rowCountArray = this.executeBatchUpdate(sqlAndArgumentsData.getSql(), sqlAndArgumentsData.getArgumentListList());
for (int i = 0; i <= rowCountArray.length - 1; i++) {
int rowCount = rowCountArray[i];
if (rowCount <= 0) {
throw new DataAccessException(String.format("第%s行更改的数据行数为0!", i));
}
}
}
/**
* 更新实体数据
*
* @param t 实体对象
* @return
*/
@Override
public void update(Object t) {
//实体类
Class entityClass = t.getClass();
//实体元数据
EntityMetaData entityMetaData = getEntityMetaData(entityClass);
//列名字段对应Map
Map<String, Field> columnFieldMap = entityMetaData.getColumnNameFieldMap();
//参数列表
List<Object> argumentList = new LinkedList<Object>();
//有值的字段名称列表(不包括主键)
List<String> hasValueColumnNameList = new LinkedList<String>();
for (String columnName : columnFieldMap.keySet()) {
//如果是主键
if (columnName.equals(entityMetaData.getPrimaryKey())) {
continue;
}
Field field = columnFieldMap.get(columnName);
try {
Object value = field.get(t);
if (value == null) {
continue;
}
hasValueColumnNameList.add(columnName);
argumentList.add(value);
} catch (Exception ex) {
}
}
StringBuilder sb = new StringBuilder();
sb.append("UPDATE ").append(dialect.addIdentifierSeparatingChar(entityMetaData.getTableName())).append(" SET ");
for (int i = 0; i <= hasValueColumnNameList.size() - 1; i++) {
String columnName = hasValueColumnNameList.get(i);
if (i != 0) {
sb.append(", ");
}
sb.append(dialect.addIdentifierSeparatingChar(columnName)).append(" = ?");
}
sb.append(" WHERE ").append(dialect.addIdentifierSeparatingChar(entityMetaData.getPrimaryKey())).append(" = ?;");
//添加主键的值
argumentList.add(entityMetaData.getPrimaryKeyValue(t));
String sql = sb.toString();
int rowCount = this.executeUpdate(sql, argumentList, null);
if (rowCount <= 0) {
throw new DataAccessException("更改的数据行数为0!");
}
}
/**
* 根据主键ID删除数据
*
* @param entityClass 实体类
* @param id 主键ID
* @return
*/
@Override
public void delete(Class entityClass, Object id) {
if (id == null) {
HandleException(new SQLException("主键的值不能为空!"), null);
}
//实体元数据
EntityMetaData entityMetaData = getEntityMetaData(entityClass);
String sql = String.format("DELETE FROM %s WHERE %s = ?", dialect.addIdentifierSeparatingChar(entityMetaData.getTableName()), dialect.addIdentifierSeparatingChar(entityMetaData.getPrimaryKey()));
//准备参数
List<Object> argumentList = new LinkedList<Object>();
argumentList.add(id);
int rowCount = this.executeUpdate(sql, argumentList, null);
if (rowCount <= 0) {
throw new DataAccessException("更改的数据行数为0!");
}
}
/**
* 根据主键列表删除一批数据
*
* @param entityClass 实体类
* @param idList
* @return
*/
@Override
public void deleteList(Class entityClass, List<Object> idList) {
if (idList == null || idList.isEmpty()) {
return;
}
//实体元数据
EntityMetaData entityMetaData = getEntityMetaData(entityClass);
StringBuilder sb = new StringBuilder();
sb.append(String.format("DELETE FROM %s WHERE %s IN (", dialect.addIdentifierSeparatingChar(entityMetaData.getTableName()), dialect.addIdentifierSeparatingChar(entityMetaData.getPrimaryKey())));
for (int i = 0; i <= idList.size() - 1; i++) {
if (i > 0) {
sb.append(",");
}
sb.append("?");
}
sb.append(")");
String sql = sb.toString();
this.executeUpdate(sql, idList, null);
}
/**
* 得到所有实体列表
*
* @param <T> 范型
* @param entityClass 实体类
* @return
*/
@Override
public <T> List<T> getList(Class<T> entityClass) {
return getList(entityClass, null, null);
}
/**
* 得到指定条件的实体列表(不分页)
*
* @param <T> 范型
* @param entityClass 实体类
* @param conditionArgumentMap 条件及对应参数Map
* @return
*/
@Override
public <T> List<T> getList(Class<T> entityClass, Map<String, Object> conditionArgumentMap) {
return getList(entityClass, conditionArgumentMap, null);
}
/**
* 得到指定条件的实体列表(不分页)
*
* @param <T> 范型
* @param entityClass 实体类
* @param conditionArgumentMap 条件及对应参数Map
* @param sqlSuffix SQL后缀
* @return
*/
@Override
public <T> List<T> getList(Class<T> entityClass, Map<String, Object> conditionArgumentMap, String sqlSuffix) {
//实体元数据
EntityMetaData entityMetaData = getEntityMetaData(entityClass);
String sql = String.format("SELECT * FROM %s", dialect.addIdentifierSeparatingChar(entityMetaData.getTableName()));
//执行查询
return this.executeQuery(sql, null, conditionArgumentMap, sqlSuffix, entityClass);
}
/**
* 得到指定条件和指定页的实体列表(带分页)
*
* @param <T> 范型
* @param entityClass 实体类
* @param conditionArgumentMap 条件及对应参数Map
* @param pageIndex 页码(从第1页开始)
* @param pageSize 页大小
* @return
*/
@Override
public <T> PagedList<T> getList(Class<T> entityClass, Map<String, Object> conditionArgumentMap, int pageIndex, int pageSize) {
return getList(entityClass, conditionArgumentMap, null, pageIndex, pageSize);
}
/**
* 得到指定条件和指定页的实体列表(带分页)
*
* @param <T> 范型
* @param entityClass 实体类
* @param conditionArgumentMap 条件及对应参数Map
* @param sqlSuffix SQL后缀
* @param pageIndex 页码(从第1页开始)
* @param pageSize 页大小
* @return
*/
@Override
public <T> PagedList<T> getList(Class<T> entityClass, Map<String, Object> conditionArgumentMap, String sqlSuffix, int pageIndex, int pageSize) {
//实体元数据
EntityMetaData entityMetaData = getEntityMetaData(entityClass);
String sql = String.format("SELECT * FROM %s", dialect.addIdentifierSeparatingChar(entityMetaData.getTableName()));
//执行查询
return this.executePagedQuery(sql, null, conditionArgumentMap, sqlSuffix, entityClass, pageIndex, pageSize);
}
/**
* 执行查询
*
* @param sql 查询SQL语句
* @param clazz 映射的类
* @param argumentArray 参数数组
* @return
*/
@Override
public <T> List<T> executeQuery(String sql, Class<T> clazz, Object... argumentArray) {
return executeQuery(sql, Arrays.asList(argumentArray), null, clazz);
}
/**
* 执行查询
*
* @param sql 查询SQL语句
* @param argumentList 参数列表
* @param conditionArguemntMap 条件及对应参数Map
* @param clazz 映射的类
* @return
*/
@Override
public <T> List<T> executeQuery(String sql, List<Object> argumentList, Map<String, Object> conditionArguemntMap, Class<T> clazz) {
return executeQuery(sql, argumentList, conditionArguemntMap, null, clazz);
}
/**
* 执行查询
*
* @param sql 查询SQL语句
* @param argumentList 参数列表
* @param conditionArguemntMap 条件及对应参数Map
* @param sqlSuffix SQL后缀
* @param clazz 映射的类
* @return
*/
@Override
public <T> List<T> executeQuery(String sql, List<Object> argumentList, Map<String, Object> conditionArguemntMap, String sqlSuffix, Class<T> clazz) {
//执行查询
return executePagedQuery(sql, argumentList, conditionArguemntMap, sqlSuffix, clazz, -1, -1);
}
/**
* 执行带分页的查询
*
* @param sql 查询SQL语句
* @param clazz 映射的类
* @param pageIndex 页码(从第1页开始)
* @param pageSize 页大小
* @param argumentArray 参数数组
* @return
*/
@Override
public <T> PagedList<T> executePagedQuery(String sql, Class<T> clazz, int pageIndex, int pageSize, Object... argumentArray) {
return executePagedQuery(sql, Arrays.asList(argumentArray), null, clazz, pageIndex, pageSize);
}
/**
* 执行带分页的查询
*
* @param sql 查询SQL语句
* @param argumentList 参数列表
* @param conditionArguemntMap 条件及对应参数Map
* @param clazz 映射的类
* @param pageIndex 页码(从第1页开始)
* @param pageSize 页大小
* @return
*/
@Override
public <T> PagedList<T> executePagedQuery(String sql, List<Object> argumentList, Map<String, Object> conditionArguemntMap, Class<T> clazz, int pageIndex, int pageSize) {
return executePagedQuery(sql, argumentList, conditionArguemntMap, null, clazz, pageIndex, pageSize);
}
//执行SQL查询
private ResultSet executeSqlQuery(Connection conn, String sql) throws SQLException {
//记录日志
log_show_sql(sql);
//得到PreparedStatement对象
PreparedStatement cmd = conn.prepareStatement(sql);
//执行查询
ResultSet rs = cmd.executeQuery();
return rs;
}
/**
* 执行带分页的查询
*
* @param sql 查询SQL语句
* @param argumentList 参数列表
* @param conditionArguemntMap 条件及对应参数Map
* @param sqlSuffix SQL后缀
* @param clazz 映射的类
* @param pageIndex 页码(从第1页开始)
* @param pageSize 页大小
* @return
*/
@Override
public <T> PagedList<T> executePagedQuery(String sql, List<Object> argumentList, Map<String, Object> conditionArguemntMap, String sqlSuffix, Class<T> clazz, int pageIndex, int pageSize) {
String finalSql = null;
//执行查询
Connection conn = null;
try {
conn = this.getConnection();
//得到最终SQL
finalSql = this.dialect.getFinalSql(sql, argumentList, conditionArguemntMap, sqlSuffix);
int recordCount = 0;
List dataList = null;
//如果不分页
if (pageIndex < 0) {
ResultSet rs = executeSqlQuery(conn, finalSql);
dataList = this.convertRowSetToObjectList(rs, clazz, -1);
} //如果要分页
else {
if (pageSize < 0) {
throw new SQLException("页大小不能小于0");
}
//当前方言是否支持分页优化
if (this.dialect.isSupportPagedQuerySqlOptimize()) {
String getRecordCountSql = this.dialect.getRecordCountSql(finalSql);
String getPagedQuerySql = this.dialect.getPagedQuerySql(finalSql, (pageIndex - 1) * pageSize, pageSize);
//得到记录数
ResultSet rs = executeSqlQuery(conn, getRecordCountSql);
if (rs.next()) {
recordCount = rs.getInt(1);
} else {
throw new SQLException("获取查询语句结果数量时出错,rs.next返回false");
}
rs = executeSqlQuery(conn, getPagedQuerySql);
dataList = this.convertRowSetToObjectList(rs, clazz, -1);
} else {
//执行查询
ResultSet rs = executeSqlQuery(conn, finalSql);
//从第n行开始
int startRowIndex = (pageIndex - 1) * pageSize;
if (startRowIndex > 0) {
rs.absolute(startRowIndex);
}
dataList = this.convertRowSetToObjectList(rs, clazz, pageSize);
//得到数据总数
rs.last();
recordCount = rs.getRow();
}
}
PagedListImpl pagedList = new PagedListImpl(dataList);
//设置页码
pagedList.setPageIndex(pageIndex);
//设置页大小
pagedList.setPageSize(pageSize);
//设置记录数
pagedList.setRecordCount(recordCount);
return pagedList;
} catch (Exception ex) {
HandleException(ex, finalSql);
} finally {
try {
if (conn != null) {
conn.close();
}
} catch (Exception ex) {
HandleException(ex, "关闭连接时异常!");
}
}
return null;
}
/**
* 执行更改
*
* @param sql 更改SQL语句
* @param argumentArray 参数列表
* @return
*/
@Override
public int executeUpdate(String sql, Object... argumentArray) {
List<Object> argumentList = null;
if (argumentArray != null && argumentArray.length > 0) {
argumentList = Arrays.asList(argumentArray);
if (List.class.isInstance(argumentArray[0])) {
throw new RuntimeException(
"参数argumentArray的第1个参数为List!");
}
}
return executeUpdate(sql, argumentList, null);
}
/**
* 执行更改
*
* @param sql 更改SQL语句
* @param argumentList 参数列表
* @param conditionArguemntMap 条件列表
* @return
*/
@Override
public int executeUpdate(String sql, List<Object> argumentList, Map<String, Object> conditionArguemntMap) {
String finalSql = null;
//执行更改
Connection conn = null;
try {
conn = this.getConnection();
finalSql = this.dialect.getFinalSql(sql, argumentList, conditionArguemntMap, null);
//记录日志
log_show_sql(finalSql);
//得到PreparedStatement对象
PreparedStatement cmd = conn.prepareStatement(finalSql);
//执行查询
return cmd.executeUpdate();
} catch (SQLException ex) {
HandleException(ex, finalSql);
} finally {
try {
if (conn != null) {
conn.close();
}
} catch (Exception ex) {
HandleException(ex, "关闭连接时异常!");
}
}
return -1;
}
/**
* 执行批量更改
*
* @param sql 更改语句
* @param argumentListList 参数列表列表
* @return
*/
@Override
public int[] executeBatchUpdate(String sql, List<List<Object>> argumentListList) {
String finalSql = null;
//执行批量更改
try {
finalSql = "/*批量更改*/" + sql;
Connection conn = this.getConnection();
try {
conn.setAutoCommit(false);
//得到PreparedStatement对象
PreparedStatement cmd = conn.prepareStatement(sql);
int i = 0;
for (List<Object> argumentList : argumentListList) {
int j = 1;
for (Object obj : argumentList) {
cmd.setObject(j, obj);
j++;
}
cmd.addBatch();
if (i % 1000 == 0) {
cmd.executeBatch();
cmd.clearBatch();
}
i++;
}
cmd.executeBatch();
conn.commit();
return new int[0];
} catch (SQLException ex) {
throw ex;
} finally {
conn.close();
}
} catch (SQLException ex) {
HandleException(ex, finalSql);
}
return null;
}
//处理异常
private void HandleException(Throwable ex, String sql) {
DataAccessException dae = new DataAccessException();
dae.initCause(ex);
dae.setSql(sql);
throw dae;
}
}