/*
* Copyright 2011-2013 Eric F. Savage, code@efsavage.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 com.ajah.spring.jdbc;
import java.beans.BeanInfo;
import java.beans.IntrospectionException;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.lang.reflect.Modifier;
import java.lang.reflect.ParameterizedType;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.persistence.GeneratedValue;
import javax.persistence.ManyToMany;
import javax.persistence.Transient;
import javax.sql.DataSource;
import org.joda.time.LocalDate;
import org.joda.time.format.DateTimeFormat;
import org.joda.time.format.DateTimeFormatter;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import com.ajah.spring.jdbc.criteria.Criteria;
import com.ajah.spring.jdbc.criteria.Limit;
import com.ajah.spring.jdbc.criteria.Order;
import com.ajah.spring.jdbc.criteria.Where;
import com.ajah.spring.jdbc.err.DataObjectCreationException;
import com.ajah.spring.jdbc.err.DataOperationException;
import com.ajah.spring.jdbc.err.DataOperationExceptionUtils;
import com.ajah.spring.jdbc.util.JDBCMapperUtils;
import com.ajah.util.AjahUtils;
import com.ajah.util.ArrayUtils;
import com.ajah.util.Identifiable;
import com.ajah.util.StringUtils;
import com.ajah.util.ToStringable;
import com.ajah.util.data.Audited;
import com.ajah.util.reflect.IntrospectionUtils;
import com.ajah.util.reflect.ReflectionUtils;
import lombok.extern.java.Log;
/**
* This is a basic DAO object.
*
* @author <a href="http://efsavage.com">Eric F. Savage</a>,
* <a href="mailto:code@efsavage.com">code@efsavage.com</a>.
* @param <K>
* The primary key class. Note that {@link Object#toString()} will be
* invoked on this object.
* @param <T>
* The type of entity this DAO exists for, may be an interface.
* @param <C>
* The concrete type of entity this DAO exists for.
*
*/
@Log
public abstract class AbstractAjahDao<K extends Comparable<K>, T extends Identifiable<K>, C extends T> implements AjahDao<K, T> {
protected static final Logger sqlLog = Logger.getLogger("ajah.sql");
protected static final Logger sqlVarsLog = Logger.getLogger("ajah.sql.vars");
private static final DateTimeFormatter LOCAL_DATE_FORMAT = DateTimeFormat.forPattern("yyyy-MM-dd");
private static String getFieldsClause(final String[] fields) {
final StringBuffer stringBuffer = new StringBuffer();
boolean first = true;
for (final String field : fields) {
if (first) {
first = false;
} else {
stringBuffer.append("AND ");
}
stringBuffer.append(field);
stringBuffer.append("=? ");
}
return stringBuffer.toString();
}
/**
* This method will return a Long, functioning like getLong, but with the
* ability to recognize null values, instead of converting them to zero.
*
* @see ResultSet#getLong(String)
* @see ResultSet#getObject(String)
* @param rs
* The ResultSet to look in.
* @param field
* The field name to look for.
* @return The Long value of the field, may be null.
* @throws SQLException
* If thrown by ResultSet.
*/
protected static Long getLong(final ResultSet rs, final String field) throws SQLException {
if (rs.getObject(field) == null) {
return null;
}
return Long.valueOf(rs.getLong(field));
}
private static PropertyDescriptor getProp(final Field field, final PropertyDescriptor[] props) {
for (final PropertyDescriptor prop : props) {
if (prop.getName().equals(field.getName())) {
return prop;
}
}
for (final PropertyDescriptor prop : props) {
if (prop.getName().equalsIgnoreCase(field.getName())) {
return prop;
}
}
log.warning("No property descriptor found for field " + field.getName() + " in:");
for (final PropertyDescriptor prop : props) {
log.warning(" - " + prop.getName());
}
return null;
}
protected static int now() {
return (int) (System.currentTimeMillis() / 1000);
}
protected static void setPreparedStatement(final PreparedStatement ps, final int index, final Object value) throws SQLException {
if (value == null) {
ps.setNull(index, Types.NULL);
} else if (String.class.isAssignableFrom(value.getClass())) {
ps.setString(index, (String) value);
} else if (Boolean.class.isAssignableFrom(value.getClass())) {
if (((Boolean) value).booleanValue()) {
ps.setInt(index, 1);
} else {
ps.setInt(index, 0);
}
} else if (Integer.class.isAssignableFrom(value.getClass())) {
ps.setInt(index, ((Integer) value).intValue());
} else if (Long.class.isAssignableFrom(value.getClass())) {
ps.setLong(index, ((Long) value).longValue());
} else if (BigDecimal.class.isAssignableFrom(value.getClass())) {
ps.setBigDecimal(index, (BigDecimal) value);
} else {
log.warning("Unhandled type: " + value.getClass() + ", using toString()");
ps.setString(index, value.toString());
}
}
private final Map<String, Field> colMap = new HashMap<>();
private List<String> columns;
private List<String> insertColumns;
protected JdbcTemplate jdbcTemplate;
private String selectFields;
private String selectFieldsWithTablePrefix;
private String insertFields;
private String updateFields;
private List<String> updateFieldsList;
private String insertPlaceholders;
private String tableName;
private Boolean autoIdAssign;
/**
* Will automatically fill in properties from the result set. Currently
* supports:
*
* <ul>
* <li>{@link String}</li>
* <ul>
*
* @throws SQLException
* If the {@link ResultSet} throws it.
*
* @see com.ajah.spring.jdbc.AjahDao#autoPopulate(Identifiable, ResultSet)
*/
@Override
public void autoPopulate(final T entity, final ResultSet rs) throws SQLException {
try {
final BeanInfo componentBeanInfo = Introspector.getBeanInfo(entity.getClass());
final PropertyDescriptor[] props = componentBeanInfo.getPropertyDescriptors();
for (final PropertyDescriptor prop : props) {
log.finest("PropertyDescriptor: " + prop.getName() + ", Setter: " + (prop.getWriteMethod() == null ? null : prop.getWriteMethod().getName()) + " Getter: " + (prop
.getReadMethod() == null ? null : prop.getReadMethod().getName()));
}
for (final String column : getColumns()) {
final Field field = this.colMap.get(column);
if (field == null) {
log.warning("No field mapped for column: " + column);
} else if (rs.getObject(column) == null) {
propSet(entity, getProp(field, props), null);
} else if (IntrospectionUtils.isString(field)) {
propSet(entity, getProp(field, props), rs.getString(column));
} else if (IntrospectionUtils.isDate(field)) {
propSet(entity, getProp(field, props), new Date(rs.getLong(column)));
} else if (IntrospectionUtils.isFromStringable(field)) {
propSet(entity, getProp(field, props), field.getType().getConstructor(String.class).newInstance(rs.getString(column)));
} else if (IntrospectionUtils.isIdentifiableEnum(field)) {
propSet(entity, getProp(field, props), ReflectionUtils.findEnumById(field, rs.getString(column)));
} else if (IntrospectionUtils.isInt(field)) {
if (rs.getObject(column) == null && IntrospectionUtils.isPrimitive(field)) {
log.warning("Attempting to set a null value on a primitive int field, using zero");
propSet(entity, getProp(field, props), Integer.valueOf(0));
} else {
propSet(entity, getProp(field, props), Integer.valueOf(rs.getInt(column)));
}
} else if (IntrospectionUtils.isLong(field)) {
if (rs.getObject(column) == null && IntrospectionUtils.isPrimitive(field)) {
log.warning("Attempting to set a null value on a primitive long field, using zero");
propSet(entity, getProp(field, props), Long.valueOf(0));
} else {
propSet(entity, getProp(field, props), Long.valueOf(rs.getLong(column)));
}
} else if (IntrospectionUtils.isBoolean(field)) {
propSet(entity, getProp(field, props), Boolean.valueOf(rs.getBoolean(column)));
} else if (IntrospectionUtils.isEnum(field)) {
log.warning("Can't handle non-Identifiable enum for column " + column + " [" + field.getType() + "]");
} else if (BigDecimal.class.isAssignableFrom(field.getType())) {
final BigDecimal bigDecimal = rs.getBigDecimal(column);
propSet(entity, getProp(field, props), bigDecimal);
} else if (LocalDate.class.isAssignableFrom(field.getType())) {
if (!StringUtils.isBlank(rs.getString(column))) {
final int[] parts = ArrayUtils.parseInt(rs.getString(column).split("-"));
final LocalDate localDate = new LocalDate(parts[0], parts[1], parts[2]);
propSet(entity, getProp(field, props), localDate);
}
} else {
log.warning("Can't handle auto-populating of column " + column + " of type " + field.getType());
}
}
} catch (final IntrospectionException e) {
log.log(Level.SEVERE, entity.getClass().getName() + ": " + e.getMessage(), e);
} catch (final SecurityException e) {
log.log(Level.SEVERE, entity.getClass().getName() + ": " + e.getMessage(), e);
} catch (final InstantiationException e) {
log.log(Level.SEVERE, entity.getClass().getName() + ": " + e.getMessage(), e);
} catch (final IllegalAccessException e) {
log.log(Level.SEVERE, entity.getClass().getName() + ": " + e.getMessage(), e);
} catch (final InvocationTargetException e) {
log.log(Level.SEVERE, entity.getClass().getName() + ": " + e.getMessage(), e);
} catch (final NoSuchMethodException e) {
log.log(Level.SEVERE, entity.getClass().getName() + ": " + e.getMessage(), e);
}
}
protected int count(final Criteria criteria) throws DataOperationException {
return count(criteria, null);
}
protected int count(final Criteria criteria, final String[] additionalTables) throws DataOperationException {
try {
boolean tablePrefix = (additionalTables != null && additionalTables.length > 0);
final StringBuilder sql = new StringBuilder();
sql.append("SELECT COUNT(*)");
sql.append(" FROM `" + getTableName() + "`");
if (additionalTables != null && additionalTables.length > 0) {
sql.append(", " + StringUtils.join(additionalTables));
}
sql.append(criteria.getWhere().getSql());
sqlLog.finest(sql.toString());
return getJdbcTemplate().queryForObject(sql.toString(), criteria.getWhere().getValues().toArray(), Integer.class).intValue();
} catch (final EmptyResultDataAccessException e) {
log.fine(e.getMessage());
return 0;
} catch (final DataAccessException e) {
throw DataOperationExceptionUtils.translate(e, getTableName());
}
}
protected long count(final String sql) throws DataOperationException {
try {
sqlLog.finest(sql);
return getJdbcTemplate().queryForObject(sql, Integer.class).intValue();
} catch (final EmptyResultDataAccessException e) {
log.fine(e.getMessage());
return 0;
} catch (final DataAccessException e) {
throw DataOperationExceptionUtils.translate(e, null);
}
}
/**
* Decrements the field of the record by 1.
*
* @param entity
* Entity to update.
* @param field
* The field to decrease.
* @return Number of rows affected.
* @throws DataOperationException
* If an error occurs executing the query.
*/
public DataOperationResult<T> decrement(final T entity, final String field) throws DataOperationException {
return increment(entity, field, -1);
}
/**
* Note: As a safety mechanishm, this method throws
* {@link UnsupportedOperationException}. It should be overridden as needed.
*
* @see com.ajah.spring.jdbc.AjahDao#delete(com.ajah.util.Identifiable)
* @see #deleteById(Comparable)
*
* @throws DataOperationException
* If an error occurs executing the query.
*/
@Override
public DataOperationResult<T> delete(final T entity) throws DataOperationException {
throw new UnsupportedOperationException();
}
/**
* Deletes an entity by unique ID.
*
* @param id
* Value to match against the entity.entity_id column, required.
* @return Entity if found, otherwise null.
* @throws DataOperationException
* If the query could not be executed.
*/
public DataOperationResult<T> deleteById(final K id) throws DataOperationException {
AjahUtils.requireParam(id, "id");
try {
return new DataOperationResult<>(null, getJdbcTemplate().update("DELETE FROM `" + getTableName() + "` WHERE " + getTableName() + "_id = ?", new Object[] { id.toString() }));
} catch (final DataAccessException e) {
throw DataOperationExceptionUtils.translate(e, getTableName());
}
}
/**
* Finds a single object by the Criteria specified.
*
* @param criteria
* The criteria to use to find the object.
* @return The object, if found.
* @throws DataOperationException
* If the query could not be executed.
*/
public T find(final Criteria criteria) throws DataOperationException {
if (criteria.getLimit().getCount() > 1) {
throw new IllegalArgumentException("Cannot use singular find method when criteria has a limit greater than 1 (" + criteria.getLimit().getCount() + ")");
}
criteria.rows(1);
return find(criteria.getWhere(), criteria.getLimit(), criteria.getOrderBySql());
}
/**
* Finds a single object by the Criteria specified.
*
* @param field
* The field to query.
* @param value
* The value to match.
* @return The object, if found.
* @throws DataOperationException
* If the query could not be executed.
*/
public T find(final String field, final int value) throws DataOperationException {
return find(field, String.valueOf(value));
}
/**
* Finds a single object by the Criteria specified.
*
* @param field
* The field to query.
* @param value
* The value to match.
* @return The object, if found.
* @throws DataOperationException
* If the query could not be executed.
*/
public T find(final String field, final String value) throws DataOperationException {
final Criteria criteria = new Criteria().eq(field, value);
if (criteria.getLimit().getCount() > 1) {
throw new IllegalArgumentException("Cannot use singular find method when criteria has a limit greater than 1 (" + criteria.getLimit().getCount() + ")");
}
criteria.rows(1);
return find(criteria.getWhere(), criteria.getLimit(), criteria.getOrderBySql());
}
/**
* Finds a single object by the Criteria specified.
*
* @param values
* The value(s) to use to find the object. The column name must
* match the class name (e.g. a UserId would query the user_id
* column).
* @return The object, if found.
* @throws DataOperationException
* If the query could not be executed.
*/
public T find(final ToStringable... values) throws DataOperationException {
final Criteria criteria = new Criteria();
for (final ToStringable value : values) {
criteria.eq(value);
}
criteria.rows(1);
return find(criteria.getWhere(), criteria.getLimit(), criteria.getOrderBySql());
}
/**
* Finds a single object by the Where and Limit specified.
*
* @param where
* The Object to create the WHERE statement.
* @param limit
* The Object to create the LIMIT statement.
* @param orderBySql
* The SQL for the "ORDER BY" clause. Should start with
* "ORDER BY", or can be blank.
* @return The object, if found, otherwise null.
* @throws DataOperationException
* If the query could not be executed
*/
public T find(final Where where, final Limit limit, final String orderBySql) throws DataOperationException {
AjahUtils.requireParam(where, "where");
if (limit != null && limit.getCount() > 1) {
throw new IllegalArgumentException("Cannot use singular find method with a limit greater than 1 (" + limit.getCount() + ")");
}
try {
final String sql = "SELECT " + getSelectFields() + " FROM `" + getTableName() + "`" + where.getSql() + (StringUtils.isBlank(orderBySql) ? "" : orderBySql) + (limit == null ? " LIMIT 1"
: " " + limit.getSql());
final Object[] values = where.getValues().toArray();
if (sqlLog.isLoggable(Level.FINEST)) {
sqlLog.finest(sql);
sqlVarsLog.finest(values.length + " values");
for (int i = 0; i < values.length; i++) {
sqlVarsLog.finest("value " + i + ": " + values[i].toString());
}
}
return getJdbcTemplate().queryForObject(sql, values, getRowMapper());
} catch (final EmptyResultDataAccessException e) {
return null;
} catch (final DataAccessException e) {
throw DataOperationExceptionUtils.translate(e, getTableName());
}
}
/**
* Find an entity by unique ID.
*
* @param field
* Column to match against, required.
* @param value
* Value to match against the entity.field column, required.
* @return Entity if found, otherwise null.
* @throws DataOperationException
* If the query could not be executed.
*/
public T findByField(final String field, final Object value) throws DataOperationException {
AjahUtils.requireParam(field, "field");
AjahUtils.requireParam(value, "value");
try {
return getJdbcTemplate().queryForObject("SELECT " + getSelectFields() + " FROM `" + getTableName() + "` WHERE " + field + " = ?", new Object[] { value }, getRowMapper());
} catch (final EmptyResultDataAccessException e) {
return null;
} catch (final DataAccessException e) {
throw DataOperationExceptionUtils.translate(e, getTableName());
}
}
/**
* Find an entity by unique ID.
*
* @param fields
* Columns to match against, required.
* @param values
* Values to match against the entity.field column, required.
* @return Entity if found, otherwise null.
* @throws DataOperationException
* If the query could not be executed
*/
public T findByFields(final String[] fields, final Object[] values) throws DataOperationException {
AjahUtils.requireParam(fields, "fields");
AjahUtils.requireParam(values, "values");
try {
// TODO Optimize for single values
final String sql = "SELECT " + getSelectFields() + " FROM `" + getTableName() + "` WHERE " + getFieldsClause(fields);
if (sqlLog.isLoggable(Level.FINEST)) {
sqlLog.finest(sql);
sqlVarsLog.finest(values.length + " values");
for (int i = 0; i < values.length; i++) {
sqlVarsLog.finest("value " + i + ": " + values[i].toString());
}
}
return getJdbcTemplate().queryForObject(sql, values, getRowMapper());
} catch (final EmptyResultDataAccessException e) {
log.fine(e.getMessage());
return null;
} catch (final DataAccessException e) {
throw DataOperationExceptionUtils.translate(e, getTableName());
}
}
/**
* Find a collections of entities by their unique ID.
*
* @param ids
* Values to match against the entity.entity_id column, required.
* @return Entity if found, otherwise null.
* @throws DataOperationException
* If the query could not be executed
*/
public List<T> findByIds(final Collection<K> ids) throws DataOperationException {
AjahUtils.requireParam(ids, "ids");
try {
final StringBuffer sql = new StringBuffer();
sql.append("SELECT " + getSelectFields() + " FROM `" + getTableName() + "` WHERE ");
boolean first = true;
final String[] idArray = new String[ids.size()];
int i = 0;
for (final K id : ids) {
if (first) {
first = false;
} else {
sql.append(" OR ");
}
sql.append(getTableName() + "_id = ?");
idArray[i++] = id.toString();
}
if (sqlLog.isLoggable(Level.FINEST)) {
log.finest(sql.toString());
for (final Object value : ids) {
log.finest(value.toString());
}
}
return getJdbcTemplate().query(sql.toString(), idArray, getRowMapper());
} catch (final EmptyResultDataAccessException e) {
log.fine(e.getMessage());
return null;
} catch (final DataAccessException e) {
throw DataOperationExceptionUtils.translate(e, getTableName());
}
}
/**
* Find an entity by the supplied WHERE clause.
*
* @param where
* The WHERE clause to include in the query.
* @return Entity if found, otherwise null.
* @throws DataOperationException
* If the query could not be executed
*/
public T findByWhere(final String where) throws DataOperationException {
AjahUtils.requireParam(where, "where");
try {
final String sql = "SELECT " + getSelectFields() + " FROM `" + getTableName() + "` WHERE " + where + " LIMIT 1";
sqlLog.finest(sql);
return getJdbcTemplate().queryForObject(sql, null, getRowMapper());
} catch (final EmptyResultDataAccessException e) {
return null;
} catch (final DataAccessException e) {
throw DataOperationExceptionUtils.translate(e, getTableName());
}
}
/**
* Returns the list of columns for this class.
*
* @return The list of columns for this class, may be empty but will not be
* null.
*/
public List<String> getColumns() {
if (this.columns == null) {
loadColumns();
}
return this.columns;
}
/**
* {@inheritDoc}
*/
@Override
@SuppressWarnings("unchecked")
public Class<K> getIdClass() {
return (Class<K>) ((ParameterizedType) getClass().getGenericSuperclass()).getActualTypeArguments()[0];
}
/**
* Returns the list of columns for this class.
*
* @return The list of columns for this class, may be empty but will not be
* null.
*/
public List<String> getInsertColumns() {
if (this.insertColumns == null) {
loadColumns();
}
return this.insertColumns;
}
protected String getInsertFields() {
if (this.insertFields == null) {
loadColumns();
}
return this.insertFields;
}
protected String getInsertPlaceholders() {
if (this.insertPlaceholders == null) {
loadColumns();
}
return this.insertPlaceholders;
}
Object[] getInsertValues(final T entity) {
final Object[] values = new Object[getInsertColumns().size()];
try {
final BeanInfo componentBeanInfo = Introspector.getBeanInfo(entity.getClass());
final PropertyDescriptor[] props = componentBeanInfo.getPropertyDescriptors();
for (int i = 0; i < values.length; i++) {
final Field field = this.colMap.get(this.insertColumns.get(i));
if (field == null) {
throw new IllegalArgumentException("Field " + this.insertColumns.get(i) + " not found");
}
if (Modifier.isStatic(field.getModifiers())) {
continue;
}
if (LocalDate.class.isAssignableFrom(field.getType())) {
final LocalDate localDate = (LocalDate) ReflectionUtils.propGetSafe(entity, getProp(field, props));
if (localDate != null) {
values[i] = localDate.toString(LOCAL_DATE_FORMAT);
} else {
values[i] = null;
}
} else {
values[i] = ReflectionUtils.propGetSafeAuto(entity, field, getProp(field, props));
}
if (sqlLog.isLoggable(Level.FINEST)) {
log.finest(field.getName() + " set to " + values[i]);
}
}
} catch (final IntrospectionException e) {
log.log(Level.SEVERE, entity.getClass().getName() + ": " + e.getMessage(), e);
}
return values;
}
/**
* Returns the Spring JDBC template.
*
* @return The Spring JDBC template, may be null.
*/
public JdbcTemplate getJdbcTemplate() {
return this.jdbcTemplate;
}
protected RowMapper<T> getRowMapper() {
return new SimpleAjahRowMapper<>(this);
}
/**
* Returns the fields that are used when SELECTing an entity. Alias for
* {@link #getSelectFields(boolean)} with parameter value of false.
*
* @return The columns for use in SELECT statements for this class, may be
* empty but will not be null.
*/
public String getSelectFields() {
return getSelectFields(false);
}
/**
* Returns the fields that are used when SELECTing an entity.
*
* @param tablePrefix
* Should we include the name of the table as a prefix for all
* columns? Useful for complex queries.
* @return The columns for use in SELECT statements for this class, may be
* empty but will not be null.
*/
public String getSelectFields(final boolean tablePrefix) {
if (this.selectFields == null) {
loadColumns();
}
return tablePrefix ? this.selectFieldsWithTablePrefix : this.selectFields;
}
/**
* Returns the table name for this class.
*
* @return The table name for this class, may be null.
*/
public String getTableName() {
// TODO Add marker interface or annotation?
if (this.tableName == null) {
loadColumns();
}
return this.tableName;
}
/**
* {@inheritDoc}
*/
@Override
@SuppressWarnings("unchecked")
public Class<C> getTargetClass() {
return (Class<C>) ((ParameterizedType) getClass().getGenericSuperclass()).getActualTypeArguments()[2];
}
private String getUpdateFields() {
if (this.updateFields == null) {
loadColumns();
}
return this.updateFields;
}
private List<String> getUpdateFieldsList() {
if (this.updateFieldsList == null) {
loadColumns();
}
return this.updateFieldsList;
}
private Object[] getUpdateValues(final T entity) {
final Object[] values = new Object[getUpdateFieldsList().size() + 1];
try {
final BeanInfo componentBeanInfo = Introspector.getBeanInfo(entity.getClass());
final PropertyDescriptor[] props = componentBeanInfo.getPropertyDescriptors();
for (int i = 0; i < (values.length - 1); i++) {
final Field field = this.colMap.get(this.updateFieldsList.get(i));
if (LocalDate.class.isAssignableFrom(field.getType())) {
final LocalDate localDate = (LocalDate) ReflectionUtils.propGetSafe(entity, getProp(field, props));
if (localDate != null) {
values[i] = localDate.toString(LOCAL_DATE_FORMAT);
} else {
values[i] = null;
}
} else {
values[i] = ReflectionUtils.propGetSafeAuto(entity, field, getProp(field, props));
}
}
values[values.length - 1] = entity.getId().toString();
} catch (final IntrospectionException e) {
log.log(Level.SEVERE, entity.getClass().getName() + ": " + e.getMessage(), e);
}
return values;
}
/**
* Increments the field of the record by 1.
*
* @param entity
* Entity to update.
* @param field
* The field to increase.
* @return Number of rows affected.
* @throws DataOperationException
* If an error occurs executing the query.
*/
public DataOperationResult<T> increment(final T entity, final String field) throws DataOperationException {
return increment(entity, field, 1);
}
/**
* Increments the field of the record by a certain amount.
*
* @param entity
* Entity to update.
* @param field
* The field to increase.
* @param amount
* The amount to increase the field by.
* @return Number of rows affected.
* @throws DataOperationException
* If an error occurs executing the query.
*/
public DataOperationResult<T> increment(final T entity, final String field, final int amount) throws DataOperationException {
AjahUtils.requireParam(entity, "entity");
AjahUtils.requireParam(entity.getId(), "entity.id");
AjahUtils.requireParam(getJdbcTemplate(), "this.jdbcTemplate");
try {
final String sql = "UPDATE `" + getTableName() + "` SET " + field + "=" + field + " + " + amount + " WHERE " + getTableName() + "_id = ?";
if (sqlLog.isLoggable(Level.FINEST)) {
sqlLog.finest(sql);
}
return new DataOperationResult<>(entity, getJdbcTemplate().update(sql, entity.getId().toString()));
} catch (final DataAccessException e) {
throw DataOperationExceptionUtils.translate(e, getTableName());
}
}
/**
* Inserts the record. May throw an error on duplicate key exceptions.
*
* @param entity
* Entity to insert into the table.
* @return Number of rows inserted.
* @throws DataOperationException
* If an error occurs executing the query.
*/
@Override
public DataOperationResult<T> insert(final T entity) throws DataOperationException {
return insert(entity, false);
}
/**
* Inserts the record. May throw an error on duplicate key exceptions.
*
* @param entity
* Entity to insert into the table.
* @param delayed
* Use a DELAYED insert.
* @return Number of rows inserted.
* @throws DataOperationException
* If an error occurs executing the query.
*/
public DataOperationResult<T> insert(final T entity, final boolean delayed) throws DataOperationException {
AjahUtils.requireParam(entity, "entity");
if (!isAutoIdAssign()) {
AjahUtils.requireParam(entity.getId(), "entity.id");
}
AjahUtils.requireParam(getJdbcTemplate(), "this.jdbcTemplate");
try {
if (isAutoIdAssign()) {
// Generated (auto_increment) ID
final KeyHolder holder = new GeneratedKeyHolder();
final int rows = getJdbcTemplate().update(new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(final Connection connection) throws SQLException {
final String sql = "INSERT " + (delayed ? "DELAYED " : "") + "INTO `" + getTableName() + "` (" + getInsertFields() + ") VALUES (" + getInsertPlaceholders() + ")";
if (sqlLog.isLoggable(Level.FINEST)) {
sqlLog.finest(sql);
}
final PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
final Object[] values = getInsertValues(entity);
sqlVarsLog.finest(values.length + " values");
for (int i = 0; i < values.length; i++) {
sqlVarsLog.finest("value " + i + ": " + (values[i] == null ? null : values[i].toString()));
}
for (int i = 0; i < values.length; i++) {
if (sqlLog.isLoggable(Level.FINEST)) {
sqlLog.finest("Setting value " + (i + 1) + " to " + values[i]);
}
setPreparedStatement(ps, i + 1, values[i]);
}
return ps;
}
}, holder);
final int id = holder.getKey().intValue();
entity.setId(getIdClass().getConstructor(String.class).newInstance(String.valueOf(id)));
return new DataOperationResult<>(entity, rows);
}
// Pre-assigned ID
final String sql = "INSERT " + (delayed ? "DELAYED " : "") + "INTO `" + getTableName() + "` (" + getInsertFields() + ") VALUES (" + getInsertPlaceholders() + ")";
if (sqlLog.isLoggable(Level.FINEST)) {
sqlLog.finest(sql);
}
return new DataOperationResult<>(entity, getJdbcTemplate().update(sql, getInsertValues(entity)));
} catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException | NoSuchMethodException | SecurityException | InstantiationException e) {
throw new DataObjectCreationException(e);
} catch (final DataAccessException e) {
throw DataOperationExceptionUtils.translate(e, getTableName());
}
}
protected boolean isAutoIdAssign() {
if (this.autoIdAssign == null) {
try {
final Field field = getTargetClass().getDeclaredField("id");
if (field.isAnnotationPresent(GeneratedValue.class)) {
this.autoIdAssign = Boolean.TRUE;
} else {
this.autoIdAssign = Boolean.FALSE;
}
} catch (NoSuchFieldException | SecurityException e) {
this.autoIdAssign = Boolean.FALSE;
}
}
return this.autoIdAssign.booleanValue();
}
/**
* Find a list of all entities.
*
* @return Entity if found, otherwise null.
* @throws DataOperationException
* If an error occurs executing the query.
* @since 1.0.2
*/
protected List<T> list() throws DataOperationException {
try {
final String sql = "SELECT " + getSelectFields() + " FROM `" + getTableName() + "`";
if (sqlLog.isLoggable(Level.FINEST)) {
sqlLog.finest(sql);
}
return getJdbcTemplate().query(sql, getRowMapper());
} catch (final EmptyResultDataAccessException e) {
log.fine(e.getMessage());
return Collections.emptyList();
} catch (final DataAccessException e) {
throw DataOperationExceptionUtils.translate(e, getTableName());
}
}
/**
* Find a list of entities by non-unique match.
*
* @param criteria
* The criteria object to use to build the query.
* @return Entity if found, otherwise null.
* @throws DataOperationException
* If an error occurs executing the query.
* @since 1.0.1
*/
public List<T> list(final Criteria criteria) throws DataOperationException {
return list(criteria, null, null);
}
/**
* Find a list of entities by non-unique match.
*
* @param criteria
* The criteria object to use to build the query.
* @return Entity if found, otherwise null.
* @throws DataOperationException
* If an error occurs executing the query.
* @since 1.0.1
*/
public List<T> list(final Criteria criteria, String[] additionalFields, String[] additionalTables) throws DataOperationException {
AjahUtils.requireParam(criteria, "criteria");
try {
final StringBuilder sql = new StringBuilder();
boolean tablePrefix = (additionalFields != null && additionalFields.length > 0) || (additionalTables != null && additionalTables.length > 0);
sql.append("SELECT " + getSelectFields(tablePrefix));
if (additionalFields != null && additionalFields.length > 0) {
sql.append(", " + StringUtils.join(additionalFields));
}
sql.append(" FROM `" + getTableName() + "`");
if (additionalTables != null && additionalTables.length > 0) {
sql.append(", " + StringUtils.join(additionalTables));
}
sql.append(criteria.getWhere().getSql());
sql.append(criteria.getOrderBySql());
sql.append(criteria.getLimit().getSql());
if (sqlLog.isLoggable(Level.FINEST)) {
sqlLog.finest(sql.toString());
log.finest(criteria.getWhere().getValues().toString());
}
return getJdbcTemplate().query(sql.toString(), criteria.getWhere().getValues().toArray(), getRowMapper());
} catch (final EmptyResultDataAccessException e) {
log.fine(e.getMessage());
return Collections.emptyList();
} catch (final DataAccessException e) {
throw DataOperationExceptionUtils.translate(e, getTableName());
}
}
/**
* Lists an entity, ordering by it's ID field.
*
* @param page
* Page of results (offset).
* @param count
* Number of results per page to return.
* @return List of entities, or an empty list.
* @throws DataOperationException
* If an error occurs executing the query.
*/
public List<T> list(final int page, final int count) throws DataOperationException {
try {
final String sql = "SELECT " + getSelectFields() + " FROM `" + getTableName() + "` ORDER BY " + this.getTableName() + "_id LIMIT " + (page * count) + "," + count;
if (sqlLog.isLoggable(Level.FINEST)) {
sqlLog.finest(sql);
}
return getJdbcTemplate().query(sql, getRowMapper());
} catch (final EmptyResultDataAccessException e) {
log.fine(e.getMessage());
return Collections.emptyList();
} catch (final DataAccessException e) {
throw DataOperationExceptionUtils.translate(e, getTableName());
}
}
/**
* Find a list of entities by an arbitrary WHERE clause.
*
* @param where
* The WHERE clause, required. Do not include the actual "WHERE"
* phrase as it is inserted automatically.
* @return The list of entities satisfying the WHERE, may be null.
* @throws DataOperationException
* If an error occurs executing the query.
*/
public List<T> list(final String where) throws DataOperationException {
AjahUtils.requireParam(where, "where");
try {
final String sql = "SELECT " + getSelectFields() + " FROM `" + getTableName() + "` WHERE " + where;
if (sqlLog.isLoggable(Level.FINEST)) {
sqlLog.finest(sql);
}
return getJdbcTemplate().query(sql, getRowMapper());
} catch (final EmptyResultDataAccessException e) {
log.fine(e.getMessage());
return Collections.emptyList();
} catch (final DataAccessException e) {
throw DataOperationExceptionUtils.translate(e, getTableName());
}
}
/**
* Find a list of entities by an arbitrary WHERE clause, allowing
* multi-table queries as well as limit and order clauses.
*
* @param additionalTables
* The tables to include.
*
* @param where
* The WHERE clause, required. Do not include the actual "WHERE"
* phrase as it is inserted automatically.
* @param orderBy
* The ORDER BY clause, may be empty, should include the
* "ORDER BY" phrase.
* @param order
* The ASC or DESC clause, may be empty. Will be ignored if
* orderBy param is empty.
* @param limit
* The offset and number of rows to fetch.
* @return The list of entities satisfying the WHERE, may be null.
* @throws DataOperationException
* If an error occurs executing the query.
*/
public List<T> list(final String[] additionalTables, final String where, final String orderBy, final Order order, final Limit limit) throws DataOperationException {
AjahUtils.requireParam(where, "where");
try {
String orderBySql = "";
if (!StringUtils.isBlank(orderBy)) {
orderBySql += " ORDER BY " + orderBy;
if (order != null) {
orderBySql += " " + order.name();
}
}
final String sql = "SELECT " + getSelectFields(true) + " FROM `" + getTableName() + "`," + StringUtils.join(additionalTables) + " WHERE " + where + orderBySql + (limit == null ? ""
: limit.getSql());
if (sqlLog.isLoggable(Level.FINEST)) {
sqlLog.finest(sql);
}
return getJdbcTemplate().query(sql, getRowMapper());
} catch (final EmptyResultDataAccessException e) {
log.fine(e.getMessage());
return Collections.emptyList();
} catch (final DataAccessException e) {
throw DataOperationExceptionUtils.translate(e, getTableName());
}
}
/**
* Find a list of entities by a simple field match, ideal for searching by
* related entity IDs.
*
* @param values
* The value(s) to use to build the query.
* @return List of entities if found, otherwise null.
* @throws DataOperationException
* If an error occurs executing the query.
* @since 1.0.1
*/
public List<T> list(final ToStringable... values) throws DataOperationException {
final Criteria criteria = new Criteria();
for (final ToStringable value : values) {
criteria.eq(value);
}
return list(criteria);
}
/**
* Find a list of entities by a simple field match, ideal for searching by
* related entity IDs.
*
* @param value
* The value to use to build the query.
* @param page
* The page of results to fetch.
* @param count
* The number of results per page.
* @return List of entities if found, otherwise null.
* @throws DataOperationException
* If an error occurs executing the query.
* @since 1.0.7
*/
public List<T> list(final ToStringable value, final int page, final int count) throws DataOperationException {
return list(new Criteria().eq(value).rows(count).offset(page * count));
}
/**
* Find a list of entities by non-unique match.
*
* @param field
* Column to match against, required.
* @param value
* Value to match against the entity.field column, required.
* @return Entity if found, otherwise null.
* @throws DataOperationException
* If an error occurs executing the query.
*/
public List<T> listByField(final String field, final Identifiable<? extends ToStringable> value) throws DataOperationException {
AjahUtils.requireParam(value, "value");
AjahUtils.requireParam(value.getId(), "value.id");
return listByField(field, value.getId().toString(), getTableName() + "_id", 0, Integer.MAX_VALUE);
}
/**
* Find a list of entities by non-unique match.
*
* @param field
* Column to match against, required.
* @param value
* Value to match against the entity.field column, required.
* @return Entity if found, otherwise null.
* @throws DataOperationException
* If an error occurs executing the query.
*/
public List<T> listByField(final String field, final String value) throws DataOperationException {
AjahUtils.requireParam(value, "value");
return listByField(field, value, getTableName() + "_id", 0, Integer.MAX_VALUE);
}
/**
* Find a list of entities by non-unique match.
*
* @param field
* Column to match against, required.
* @param value
* Value to match against the entity.field column, required.
* @param count
* The maximum number of rows to fetch.
* @return Entity if found, otherwise null.
* @throws DataOperationException
* If an error occurs executing the query.
*/
public List<T> listByField(final String field, final String value, final int count) throws DataOperationException {
AjahUtils.requireParam(value, "value");
return listByField(field, value, getTableName() + "_id", 0, count);
}
/**
* Find a list of entities by non-unique match.
*
* @param field
* Column to match against, required.
* @param value
* Value to match against the entity.field column, required. If
* matching on "IS NULL", set this parameter to "NULL".
* @param orderBy
* @param page
* Page of results (offset).
* @param count
* Number of results per page to return.
* @return Entity if found, otherwise null.
* @throws DataOperationException
* If an error occurs executing the query.
*/
public List<T> listByField(final String field, final String value, final String orderBy, final int page, final int count) throws DataOperationException {
AjahUtils.requireParam(field, "field");
AjahUtils.requireParam(value, "value");
try {
if (value.equals("NULL")) {
final String sql = "SELECT " + getSelectFields() + " FROM `" + getTableName() + "` WHERE " + field + " IS NULL ORDER BY " + orderBy + " LIMIT " + (page * count) + "," + count;
if (sqlLog.isLoggable(Level.FINEST)) {
sqlLog.finest(sql);
}
return getJdbcTemplate().query(sql, getRowMapper());
}
final String sql = "SELECT " + getSelectFields() + " FROM `" + getTableName() + "` WHERE " + field + " = ? ORDER BY " + orderBy + " LIMIT " + (page * count) + "," + count;
if (sqlLog.isLoggable(Level.FINEST)) {
sqlLog.finest(sql);
log.finest(value.toString());
}
return getJdbcTemplate().query(sql, new Object[] { value }, getRowMapper());
} catch (final EmptyResultDataAccessException e) {
log.fine(e.getMessage());
return Collections.emptyList();
} catch (final DataAccessException e) {
throw DataOperationExceptionUtils.translate(e, getTableName());
}
}
/**
* Find a list of entities by non-unique match.
*
* @param field
* Column to match against, required.
* @param value
* Value to match against the entity.field column, required.
* @return Entity if found, otherwise null.
* @throws DataOperationException
* If an error occurs executing the query.
*/
public List<T> listByField(final String field, final ToStringable value) throws DataOperationException {
AjahUtils.requireParam(value, "value");
return listByField(field, value.toString(), getTableName() + "_id", 0, Integer.MAX_VALUE);
}
/**
* Find a list of entities by non-unique match.
*
* @param field
* Column to match against, required.
* @param value
* Value to match against the entity.field column, required.
* @param orderBy
* @param page
* Page of results (offset).
* @param count
* Number of results per page to return.
* @return Entity if found, otherwise null.
* @throws DataOperationException
* If an error occurs executing the query.
*/
public List<T> listByField(final String field, final ToStringable value, final String orderBy, final int page, final int count) throws DataOperationException {
return listByField(field, value.toString(), orderBy, page, count);
}
/**
* Find an entity by unique ID.
*
* @param id
* Value to match against the entity.entity_id column, required.
* @return Entity if found, otherwise null.
* @throws DataOperationException
* If the query could not be executed.
*/
@Override
public T load(final K id) throws DataOperationException {
AjahUtils.requireParam(id, "id");
try {
final String sql = "SELECT " + getSelectFields() + " FROM `" + getTableName() + "` WHERE " + getTableName() + "_id = ?";
if (sqlLog.isLoggable(Level.FINEST)) {
sqlLog.finest(sql);
log.finest(id.toString());
}
return getJdbcTemplate().queryForObject(sql, new Object[] { id.toString() }, getRowMapper());
} catch (final EmptyResultDataAccessException e) {
log.finest(e.getMessage());
return null;
} catch (final DataAccessException e) {
throw DataOperationExceptionUtils.translate(e, getTableName());
}
}
private synchronized void loadColumns() {
log.finest("Loading columns");
if (this.tableName == null) {
this.tableName = JDBCMapperUtils.getTableName(getTargetClass());
}
log.finest("Table set to : " + this.tableName);
final List<String> columnList = new ArrayList<>();
final List<String> newUpdateFields = new ArrayList<>();
final StringBuffer select = new StringBuffer();
final StringBuffer selectWithTablePrefix = new StringBuffer();
log.finest(getTargetClass().getDeclaredFields().length + " declared fields for " + getTargetClass().getName());
for (final Field field : getTargetClass().getDeclaredFields()) {
if (Modifier.isStatic(field.getModifiers())) {
continue;
}
if (field.isAnnotationPresent(Transient.class)) {
log.finest("Ignoring Transient field " + field.getName());
continue;
} else if (field.isAnnotationPresent(ManyToMany.class)) {
log.finest("Ignoring ManyToMany field " + field.getName());
continue;
} else if (Collection.class.isAssignableFrom(field.getType())) {
log.finest("Ignoring Collection field " + field.getName());
continue;
} else if (field.getName().startsWith("$SWITCH_TABLE")) {
log.finest("Ignoring switch statement method");
continue;
}
final String colName = JDBCMapperUtils.getColumnName(getTableName(), field);
columnList.add(colName);
this.colMap.put(colName, field);
if (select.length() > 0) {
select.append(", ");
}
if (selectWithTablePrefix.length() > 0) {
selectWithTablePrefix.append(", ");
}
select.append("`" + colName + "`");
selectWithTablePrefix.append(this.tableName + "." + colName);
if (!field.getName().equals("id")) {
newUpdateFields.add(colName);
}
if (field.isAnnotationPresent(Audited.class)) {
log.finest(field.getName() + " is audited");
// TODO Audit it!
}
}
if (this.selectFields == null) {
this.selectFields = select.toString();
this.selectFieldsWithTablePrefix = selectWithTablePrefix.toString();
if (isAutoIdAssign()) {
this.insertFields = StringUtils.join(",", StringUtils.wrap(newUpdateFields, "`"));
} else {
this.insertFields = this.selectFields;
}
}
if (this.columns == null) {
this.columns = columnList;
}
if (this.insertColumns == null) {
if (isAutoIdAssign()) {
this.insertColumns = newUpdateFields;
} else {
this.insertColumns = columnList;
}
}
log.finest(this.columns.size() + " columns");
if (this.updateFields == null) {
final StringBuffer uf = new StringBuffer();
for (final String field : newUpdateFields) {
if (uf.length() > 0) {
uf.append(",");
}
uf.append("`" + field + "`");
uf.append("=?");
}
this.updateFields = uf.toString();
this.updateFieldsList = newUpdateFields;
}
final StringBuffer iph = new StringBuffer();
for (int i = 0; i < this.insertColumns.size(); i++) {
if (i > 0) {
iph.append(",");
}
iph.append("?");
}
this.insertPlaceholders = iph.toString();
}
protected Integer maxInt(final String field, final Criteria criteria) throws DataOperationException {
try {
final String sql = "SELECT MAX(" + field + ") FROM `" + getTableName() + "`" + criteria.getWhere().getSql();
if (sqlLog.isLoggable(Level.FINEST)) {
sqlLog.finest(sql);
}
return getJdbcTemplate().queryForObject(sql, criteria.getWhere().getValues().toArray(), Integer.class);
} catch (final EmptyResultDataAccessException e) {
log.fine(e.getMessage());
return 0;
} catch (final DataAccessException e) {
throw DataOperationExceptionUtils.translate(e, getTableName());
}
}
protected long maxLong(final String field, final Criteria criteria) throws DataOperationException {
try {
final String sql = "SELECT MAX(" + field + ") FROM `" + getTableName() + "`" + criteria.getWhere().getSql();
if (sqlLog.isLoggable(Level.FINEST)) {
sqlLog.finest(sql);
}
return getJdbcTemplate().queryForObject(sql, criteria.getWhere().getValues().toArray(), Integer.class).intValue();
} catch (final EmptyResultDataAccessException e) {
log.fine(e.getMessage());
return 0;
} catch (final DataAccessException e) {
throw DataOperationExceptionUtils.translate(e, getTableName());
}
}
protected int minInt(final String field, final Criteria criteria) throws DataOperationException {
try {
final String sql = "SELECT MIN(" + field + ") FROM `" + getTableName() + "`" + criteria.getWhere().getSql();
if (sqlLog.isLoggable(Level.FINEST)) {
sqlLog.finest(sql);
}
return getJdbcTemplate().queryForObject(sql, criteria.getWhere().getValues().toArray(), Integer.class).intValue();
} catch (final EmptyResultDataAccessException e) {
log.fine(e.getMessage());
return 0;
} catch (final DataAccessException e) {
throw DataOperationExceptionUtils.translate(e, getTableName());
}
}
protected long minLong(final String field, final Criteria criteria) throws DataOperationException {
try {
final String sql = "SELECT MIN(" + field + ") FROM `" + getTableName() + "`" + criteria.getWhere().getSql();
if (sqlLog.isLoggable(Level.FINEST)) {
sqlLog.finest(sql);
}
return getJdbcTemplate().queryForObject(sql, criteria.getWhere().getValues().toArray(), Long.class).longValue();
} catch (final EmptyResultDataAccessException e) {
log.fine(e.getMessage());
return 0;
} catch (final DataAccessException e) {
throw DataOperationExceptionUtils.translate(e, getTableName());
}
}
private void propSet(final T entity, final PropertyDescriptor prop, final Object value) {
try {
AjahUtils.requireParam(prop, "prop");
final Method setter = prop.getWriteMethod();
if (setter == null) {
throw new IllegalArgumentException("No setter found for " + prop.getName());
}
setter.invoke(entity, value);
} catch (final IllegalAccessException e) {
log.log(Level.SEVERE, prop.getName() + ": " + e.getMessage(), e);
} catch (final IllegalArgumentException e) {
// TODO See if we're trying to set a null on a primitive
if (prop == null) {
log.log(Level.SEVERE, e.getMessage(), e);
} else {
log.log(Level.SEVERE, prop.getName() + ": " + e.getMessage(), e);
}
} catch (final InvocationTargetException e) {
log.log(Level.SEVERE, prop.getName() + ": " + e.getMessage(), e);
}
}
/**
* Sets up a new JDBC template with the supplied data source.
*
* @param dataSource
* DataSource to use for a new JDBC template.
*/
@Autowired
public void setDataSource(final DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
/**
* Sets the table name. This will override any auto-discovered settings.
*
* @param tableName
*/
public void setTableName(final String tableName) {
this.tableName = tableName;
}
protected long sum(final String field, final Criteria criteria) throws DataOperationException {
try {
final String sql = "SELECT SUM(`" + field + "`) FROM `" + getTableName() + "`" + criteria.getWhere().getSql();
sqlLog.finest(sql);
final Long sum = getJdbcTemplate().queryForObject(sql, criteria.getWhere().getValues().toArray(), Long.class);
return sum == null ? 0 : sum.longValue();
} catch (final EmptyResultDataAccessException e) {
log.fine(e.getMessage());
return 0;
} catch (final DataAccessException e) {
throw DataOperationExceptionUtils.translate(e, getTableName());
}
}
protected <N> N sum(final String field, final Criteria criteria, final Class<N> clazz) throws DataOperationException {
try {
final String sql = "SELECT SUM(`" + field + "`) FROM `" + getTableName() + "`" + criteria.getWhere().getSql();
sqlLog.finest(sql);
final N sum = getJdbcTemplate().queryForObject(sql, criteria.getWhere().getValues().toArray(), clazz);
return sum;
} catch (final EmptyResultDataAccessException e) {
log.fine(e.getMessage());
return null;
} catch (final DataAccessException e) {
throw DataOperationExceptionUtils.translate(e, getTableName());
}
}
protected BigDecimal sumBigDecimal(final String field, final Criteria criteria) throws DataOperationException {
try {
final String sql = "SELECT SUM(`" + field + "`) FROM `" + getTableName() + "`" + criteria.getWhere().getSql();
sqlLog.finest(sql);
final BigDecimal sum = getJdbcTemplate().queryForObject(sql, criteria.getWhere().getValues().toArray(), BigDecimal.class);
return sum == null ? BigDecimal.ZERO : sum;
} catch (final EmptyResultDataAccessException e) {
log.fine(e.getMessage());
return BigDecimal.ZERO;
} catch (final DataAccessException e) {
throw DataOperationExceptionUtils.translate(e, getTableName());
}
}
/**
* Updates the record. Will not do anything if there are no matching
* records.
*
* @param entity
* Entity to update.
* @return Number of rows affected.
* @throws DataOperationException
* If an error occurs executing the query.
*/
@Override
public DataOperationResult<T> update(final T entity) throws DataOperationException {
AjahUtils.requireParam(entity, "entity");
AjahUtils.requireParam(entity.getId(), "entity.id");
AjahUtils.requireParam(getJdbcTemplate(), "this.jdbcTemplate");
try {
final String sql = "UPDATE `" + getTableName() + "` SET " + getUpdateFields() + " WHERE " + getTableName() + "_id = ?";
if (sqlLog.isLoggable(Level.FINEST)) {
sqlLog.finest(sql);
}
return new DataOperationResult<>(entity, getJdbcTemplate().update(sql, getUpdateValues(entity)));
} catch (final DataAccessException e) {
throw DataOperationExceptionUtils.translate(e, getTableName());
}
}
/**
* Runs a {@link JdbcTemplate#update(String)}.
*
* @param sql
* The full SQL statement to execute.
* @return The number of rows returned.
* @throws DataOperationException
* If the query could not be executed.
*/
public int updateSql(final String sql) throws DataOperationException {
AjahUtils.requireParam(sql, "sql");
AjahUtils.requireParam(getJdbcTemplate(), "this.jdbcTemplate");
try {
if (sqlLog.isLoggable(Level.FINEST)) {
sqlLog.finest(sql);
}
return getJdbcTemplate().update(sql);
} catch (final DataAccessException e) {
throw DataOperationExceptionUtils.translate(e, getTableName());
}
}
}