/*******************************************************************************
* Copyright 2013 butor.com
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
******************************************************************************/
package org.butor.dao;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Collections;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.butor.dao.extractor.MaxRowsResultSetExtractor;
import org.butor.utils.ApplicationException;
import org.butor.utils.CommonDateFormat;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcOperations;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterUtils;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator;
import org.springframework.jdbc.support.SQLExceptionTranslator;
import org.springframework.util.CollectionUtils;
import com.google.common.base.Throwables;
public abstract class AbstractDao {
private NamedParameterJdbcOperations _namedParameterJdbcTemplate = null;
private DataSource _dataSource = null;
private static final SQLExceptionTranslator DEFAULT_DAO_EXCEPTION_TRANSLATOR = new SQLErrorCodeSQLExceptionTranslator();
protected SQLExceptionTranslator _daoExceptionTranslator = DEFAULT_DAO_EXCEPTION_TRANSLATOR;
private Logger sqlLogger = LoggerFactory.getLogger("SQL");
protected Logger logger = LoggerFactory.getLogger(getClass());
private abstract class CallTemplate<T> {
abstract T doCall(String sql, MapSqlParameterSource params);
T call(String sql_, Object... args_) {
long start = System.currentTimeMillis();
boolean success=false;
String reqId=null;
String userId=null;
String sessionId=null;
T result = null;
try {
MapSqlParameterSource params = prepParams(args_);
if (params.hasValue("reqId"))
reqId = (String) params.getValue("reqId");
if (params.hasValue("userId"))
userId = (String) params.getValue("userId");
if (params.hasValue("sessionId"))
sessionId = (String) params.getValue("sessionId");
logQuery(sql_,reqId,sessionId,userId, params);
result = doCall(sql_,params);
success=true;
return result;
} catch (Exception e){
translateException("call", sql_, e);
return null;
} finally {
long elapsed = System.currentTimeMillis()-start;
logStats(success, reqId, userId, sessionId, elapsed,result);
}
}
}
protected String queryForString(final String sql_, Object... args_)
throws DataAccessException {
return new CallTemplate<String>() {
@Override
String doCall(String sql, MapSqlParameterSource params) {
return (String) getJdbcTemplate().queryForObject(sql,
params, String.class);
}
}.call(sql_, args_);
}
protected int queryForInt(String sql_, Object... args_)
throws DataAccessException {
return new CallTemplate<Integer>() {
@Override
Integer doCall(String sql, MapSqlParameterSource params) {
return getJdbcTemplate().queryForInt(sql,
params);
}
}.call(sql_, args_);
}
protected <T> T query(String sql_, Class<T> resultSetClass_, Object... args_)
throws DataAccessException {
return this.query(sql_, resultSetClass_, null, args_);
}
protected <T> T query(String sql_, final Class<T> resultSetClass_,final
ResultSetExtractor<T> extractor_, Object... args_)
throws DataAccessException {
return new CallTemplate<T>() {
@Override
T doCall(String sql, MapSqlParameterSource params) {
ResultSetExtractor<T> extractor = extractor_;
if (extractor == null)
extractor = getDefaultResultSetExtractor(resultSetClass_);
List<T> t = (List<T>) getJdbcTemplate().query(sql, params, extractor);
if (CollectionUtils.isEmpty(t)){
return null;
} else {
return t.get(0);
}
}
}.call(sql_, args_);
}
protected <T> void queryList(final String sql_,final Class<T> resultSetClass_,final RowHandler<T> handler, Object... args_) {
new CallTemplate<List<T>>() {
@Override
List<T> doCall(String sql, MapSqlParameterSource params) {
final RowMapper<T> mapper = new BeanPropertyRowMapper<T>(resultSetClass_);
NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(_dataSource) {
@Override
protected PreparedStatementCreator getPreparedStatementCreator(String sql,
SqlParameterSource paramSource) {
PreparedStatementCreator originalCreator = super.getPreparedStatementCreator(sql, paramSource);
return new StreamingStatementCreator(originalCreator);
}
};
template.query(sql_, params, new RowCallbackHandler() {
int rowNum=0;
@Override
public void processRow(ResultSet rs) throws SQLException {
handler.handleRow(mapper.mapRow(rs, rowNum++));
}
});
return null;
}
}.call(sql_, args_);
}
protected <T> List<T> queryList(String sql_, Class<T> resultSetClass_, Object... args_)
throws DataAccessException {
return this.queryList(sql_, resultSetClass_, (ResultSetExtractor<T>)null, args_);
}
protected <T> List<T> queryList(String sql_, final Class<T> resultSetClass_,
final ResultSetExtractor<T> extractor_, Object... args_)
throws DataAccessException {
return new CallTemplate<List<T>>() {
@Override
List<T> doCall(String sql, MapSqlParameterSource params) {
ResultSetExtractor<T> extractor = extractor_;
if (extractor == null)
extractor = getDefaultResultSetExtractor(resultSetClass_);
List<T> t = (List<T>) getJdbcTemplate().query(sql, params, extractor);
if (CollectionUtils.isEmpty(t)){
t = Collections.emptyList();
}
return t;
}
}.call(sql_, args_);
}
/**
* @see JdbcTemplate.update
* @param sql_
* @param args_
* @return the affected
* @throws DataAccessException
*/
protected final UpdateResult update(String sql_, Object... args_)
throws DataAccessException {
return new CallTemplate<UpdateResult>() {
@Override
UpdateResult doCall(String sql, MapSqlParameterSource params) {
KeyHolder kh = new GeneratedKeyHolder();
int rowsAffected = getJdbcTemplate().update(sql,
params, kh);
return new UpdateResult(kh, rowsAffected);
}
}.call(sql_, args_);
}
private <T> ResultSetExtractor<T> getDefaultResultSetExtractor(
Class<T> resultSetClass_) {
ResultSetExtractor<T> maxRowsResultSetExtractor = new MaxRowsResultSetExtractor(
resultSetClass_);
return maxRowsResultSetExtractor;
}
private NamedParameterJdbcOperations getJdbcTemplate() {
if (_namedParameterJdbcTemplate == null)
_namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(
_dataSource);
return _namedParameterJdbcTemplate;
}
protected DataAccessException translateException(String task_, String sql_,
Exception ex_) {
if (ex_ instanceof SQLException) {
DataAccessException cause = _daoExceptionTranslator.translate(this.getClass().getName(),
sql_, (SQLException) ex_);
throw ApplicationException.exception(cause,DAOMessageID.SQL_EXCEPTION.getMessage());
} else if (ex_ instanceof DataAccessException){
Throwable cause = ex_.getCause();
throw ApplicationException.exception(cause, DAOMessageID.SQL_EXCEPTION.getMessage(cause.getMessage()));
}
throw ApplicationException.exception(String.format(
"Failed to execute %s.%s with SQL=%s", this.getClass()
.getName(), task_, sql_), ex_,DAOMessageID.DAO_FAILURE.getMessage());
}
private void logQuery(String sql,String reqId, String sessionId, String userId, MapSqlParameterSource args) {
if (sqlLogger.isInfoEnabled()) {
String parsedSql = NamedParameterUtils.substituteNamedParameters(sql, args);
List<?> valueList = Arrays.asList(NamedParameterUtils.buildValueArray(sql, args.getValues()));
parsedSql=parsedSql.replaceAll("\n", " ");
parsedSql=parsedSql.replaceAll("\\s", " ");
parsedSql=parsedSql.replaceAll(" ", " ");
for (Object value : valueList) {
parsedSql = prepareParsedSQLLog(parsedSql, value);
}
sqlLogger.info("class:{}, reqId: {}, sessionId: {}, userId: {}, Statement: {};",new Object[]{getClass().getName(),reqId,sessionId,userId,parsedSql});
}
}
private String prepareParsedSQLLog(String parsedSql, Object v) {
String replacedValue=String.valueOf(v);
if (v == null) {
replacedValue="NULL";
} else if (v instanceof String || v instanceof Enum<?>) {
replacedValue="\""+replacedValue+"\"";
} else if (v instanceof Date) {
replacedValue="\""+CommonDateFormat.YYYYMMDD_HHMMSS_WITHMS.format((Date)v)+"\"";
} else if (v instanceof List) {
for (Object o : (List<?>)v) {
parsedSql=prepareParsedSQLLog(parsedSql,o);
}
return parsedSql;
}
parsedSql = parsedSql.replaceFirst("\\?", replacedValue);
return parsedSql;
}
private <T> void logStats(boolean success, String reqId, String userId, String sessionId, long elapsed, T result) {
int rowCount=0;
if (result instanceof UpdateResult) {
rowCount = ((UpdateResult)result).numberOfRowAffected;
} else if (result instanceof Collection<?>) {
rowCount = ((Collection<?>)result).size();
} else if (result != null){
rowCount = 1;
}
sqlLogger.info("STATS class: {} reqId: {}, sessionId: {}, userId: {}, success: {}, elapsed: {} ms, rowCount/rowAffected : {}", new Object[] {getClass().getName(),reqId,sessionId,userId, success,elapsed, rowCount});
}
private MapSqlParameterSource prepParams(Object... args_) {
MapSqlParameterSource msp = new MapSqlParameterSource();
for (Object arg : args_) {
if (null == arg) {
continue;
}
if (arg instanceof Map) {
msp.addValues((Map)arg);
continue;
}
BeanPropertySqlParameterSource sps = new BeanPropertySqlParameterSource(arg);
List<Field> fields = new ArrayList<Field>();
for (Class<?> c = arg.getClass(); c != null; c = c.getSuperclass()) {
if (c.equals(Object.class))
continue;
fields.addAll(Arrays.asList(c.getDeclaredFields()));
}
for (Field f : fields) {
try {
String fn = f.getName();
if (msp.hasValue(fn))
sqlLogger.warn(String
.format("Field with name=%s has "
+ "been already mapped by another arg bean. Overriding!",
fn));
if (Enum.class.isAssignableFrom(f.getType())) {
sps.registerSqlType(f.getName(), Types.VARCHAR);
}
msp.addValue(fn, sps.getValue(fn), sps.getSqlType(fn),
sps.getTypeName(fn));
logger.debug(String.format("prepared sql arg: name=%s, value=%s, type=%s",
fn, sps.getValue(fn), sps.getTypeName(fn)));
} catch (Exception e) {
Throwables.propagate(e);
}
}
}
return msp;
}
public void setDaoExceptionMapper(
SQLExceptionTranslator daoExceptionTranslator_) {
_daoExceptionTranslator = daoExceptionTranslator_;
}
public void setDataSource(DataSource dataSource_) {
_dataSource = dataSource_;
}
protected static class UpdateResult {
public final Long key;
public final int numberOfRowAffected;
public UpdateResult(KeyHolder keyHolder, int numberOfRowAffected) {
this.numberOfRowAffected = numberOfRowAffected;
List<Map<String, Object>> keys = keyHolder.getKeyList();
if (keys.size() != 1) {
key=null;
return;
}
Iterator<Object> keyIt =keys.get(0).values().iterator();
if (keyIt.hasNext()) {
Object keyObj = keyIt.next();
if (keyObj instanceof Number) {
this.key = keyObj == null ? null : ((Number)keyObj).longValue();
return;
}
}
key=null;
}
@Override
public String toString() {
return "UpdateResult [key=" + key + ", numberOfRowAffected=" + numberOfRowAffected + "]";
}
}
private class StreamingStatementCreator implements PreparedStatementCreator {
final PreparedStatementCreator delegate;
public StreamingStatementCreator(PreparedStatementCreator delegate) {
this.delegate = delegate;
}
@Override
public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
final PreparedStatement statement =delegate.createPreparedStatement(connection);
statement.setFetchDirection(ResultSet.FETCH_FORWARD);
try {
// this is for mysql streaming. It makes the driver send the rows as soon as it gets it from the database.
statement.setFetchSize(Integer.MIN_VALUE);
} catch (SQLException e) {
logger.warn("Unable to set fetch size to MIN_VALUE for enabling streaming in some DB engine");
}
return statement;
}
}
}