数据库操作

package com;

 

import java.io.Serializable;

import java.lang.annotation.Annotation;

import java.lang.reflect.Field;

import java.lang.reflect.ParameterizedType;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.List;

import java.util.Map;

import java.util.Set;

 

import javax.persistence.Id;

import javax.persistence.Table;

import javax.sql.DataSource;

 

import org.apache.commons.collections.CollectionUtils;

import org.apache.commons.collections.MapUtils;

import org.apache.commons.lang.ArrayUtils;

import org.apache.commons.lang.StringUtils;

import org.apache.commons.lang.builder.ReflectionToStringBuilder;

import org.apache.commons.lang.reflect.FieldUtils;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.jdbc.core.ColumnMapRowMapper;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.core.RowMapper;

import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;

import org.springframework.jdbc.core.simple.SimpleJdbcInsert;

import org.springframework.stereotype.Repository;

 

 

/**

 * Usage:

 * This is a generic class for data access.

 * Entity must have @Id annotation on identity field.

 * @author lixiaodong

 *

 * @param <T>

 */

@Repository

public class GenericDao<T extends Serializable>{

private final Logger log = LoggerFactory.getLogger(getClass());

 

@Autowired

private DataSource dataSource;

 

private String tableName;

 

/**

* 

* @return

*/

private JdbcTemplate getJdbcTemplate() {

return new JdbcTemplate(dataSource);

}

public GenericDao() {

}

 

private Class<T> entityClass;

 

/**

* Get generic class

* @return Class<T>

*/

@SuppressWarnings("unchecked")

protected Class<T> getEntityClass() {

if (entityClass == null) {

entityClass = (Class<T>) ((ParameterizedType) getClass()

.getGenericSuperclass()).getActualTypeArguments()[0];

}

return entityClass;

}

 

 

/**

* Get table name of Entity

* @return tableName

*/

protected String getTableName(){

if(this.tableName == null){

Table table = getEntityClass().getAnnotation(Table.class);

if(table == null){

log.error("Annotation @Table not found!");

}

this.tableName = table.name();

}

return this.tableName;

}

 

private String id;

 

/**

* delete by id

*/

public void delete(Serializable... entityIds) {

String sql = "delete from "+getTableName()+" where "+getId()+" = ?";

for (Serializable entityId : entityIds) {

getJdbcTemplate().update(sql,entityId);

log.debug(sql);

}

}

/**

* Get field name of id annotation

* @return fieldName

*/

protected String getId(){

String fieldName = StringUtils.EMPTY;

if(this.id==null){

Field []fields = getEntityClass().getDeclaredFields();

for (Field field : fields) {

if(isIdField(field)){

fieldName = field.getName();

break;

}

}

if(StringUtils.isEmpty(fieldName)){

log.error(getTableName()+" have not specific Id");

}

}else{

return this.id;

}

 

return fieldName;

}

/**

* Is this a id field

* @param field

* @return true or false

*/

private boolean isIdField(Field field){

Annotation []annotations = field.getAnnotations();

for (Annotation annotation : annotations) {

if(annotation instanceof Id){

return true;

}

}

return false;

}

 

/**

* Find a object by Id

* Find <T>

*/

public T find(Serializable entityId) {

String sql = "select * from "+getTableName()+" where "+getId()+" = ?";

return (T)getJdbcTemplate().queryForObject(sql, new Object[]{entityId},new GenericRowMapper());

}

 

/**

* save entity

* @return 

*/

public int save(T entity) {

SimpleJdbcInsert insertActor = new SimpleJdbcInsert(getJdbcTemplate());

insertActor.setTableName(getTableName());

log.debug(insertActor.getInsertString());

insertActor.setGeneratedKeyName(getId());

return insertActor.executeAndReturnKey(new BeanPropertySqlParameterSource(entity)).intValue();

};

/**

* Get field count which is not null.

* @param fields

* @param entity

* @return

*/

private int getNotNullFieldSize(Field []fields,T entity){

int size = 0;

try {

for (Field field : fields) {

Object value = FieldUtils.readDeclaredField(entity, field.getName(), true);

if(value != null){

size ++;

}

}

} catch (IllegalAccessException e) {

e.printStackTrace();

}

return size;

}

 

/**

* update single entity

* Entity Id required

* update non-null field.

*/

public int update(T entity) {

String id = getId();

StringBuilder sql = new StringBuilder();

sql.append("update ");

sql.append(getTableName());

sql.append(" set ");

Field []fields = entity.getClass().getDeclaredFields();

Object []params = new Object[getNotNullFieldSize(fields, entity)];

int index = 0;

try {

for (int i = 0;i < fields.length; i++) {

Field field = fields[i];

Object value = FieldUtils.readDeclaredField(entity, field.getName(), true);

//if value is null,then discard

boolean isNotIdField = !id.equals(field.getName());

if(isNotIdField && null != value){

sql.append(" ");

sql.append(field.getName());

sql.append(" = ? ");

params[index++] = FieldUtils.readDeclaredField(entity, field.getName(), true);

sql.append(",");

}

 

}

sql.append("where"+id);

sql = new StringBuilder(sql.toString().replace(",where", " where "));

sql.append(" = ?");

params[index] = FieldUtils.readDeclaredField(entity, id, true);

} catch (IllegalAccessException e) {

log.debug(e.getMessage());

e.printStackTrace();

}

log.debug("Excute SQL:"+sql.toString());

log.debug("params:"+ReflectionToStringBuilder.toString(params));

return update(sql.toString(),params);

}

 

 

/**

* update through sql with params

* @param sql

* @param params

* @return result count.

*/

public int update(String sql,Object ...params){

return getJdbcTemplate().update(sql,params);

}

 

 

 

/**

* find all of entity

*/

public List<T> findAll() {

StringBuilder sql = new StringBuilder();

sql.append("select * from ").append(getTableName());

return findBySQL(sql.toString());

}

 

/**

* find all by sql

*/

public List<T> findBySQL(String sql) {

return getJdbcTemplate().query(sql.toString(),new GenericRowMapper());

}

 

/**

* find by sql with params

* @param sql

* @param params

* @return

*/

public List<T> find(String sql,Object ...params) {

return getJdbcTemplate().query(sql.toString(),params,new GenericRowMapper());

}

 

/**

* Find Pager

* @param pageIndex

* @param pageSize

* @param params like name="jetty" you can put it in map

*  params.put("name","jetty"); 

* @param orders

* @return

*/

public Pager<T> findPager(int pageIndex,int pageSize,Map<String, Object> params,List<Order> orders){

String sql = "select * from "+getTableName();

Object [] sqlParams = null;

if(MapUtils.isNotEmpty(params)){

//log.error("Throw error when execute findPager method,params is empty!");

//Append params to sql string.

Set<String> pKeys = params.keySet();

sqlParams = new Object[params.size()];

sql += " where ";

int i = 0;

for (String key : pKeys) {

sql += key + " like ? ";

sqlParams[i] = "%"+params.get(key)+"%"; 

//last parameter can't contact ','

if(i != params.size()-1){

sql += ",";

}

i ++;

}

}

 

//Append orders to sql string.

if(CollectionUtils.isNotEmpty(orders)){

sql += " order by ";

int j = 0;

for (Order order : orders) {

sql += order.getName() + " " + order.getSort();

if(j != orders.size() - 1){

sql += ",";

}

j ++;

}

}

 

return findPager(sql, pageIndex, pageSize,sqlParams);

}

 

 

public Pager<T> findPager(String sql,int pageIndex,int pageSize,Object ...sqlParams){

int start = (pageIndex -1)*pageSize;

int totalCount = getCount();

//Page count

int pageSum = 1;

if (totalCount % pageSize == 0) {

pageSum = totalCount / pageSize;

} else {

pageSum = totalCount / pageSize + 1;

}

sql += " limit "+ start +","+pageSize;

log.debug("execute findPager SQL : "+sql);

List<T> list = null;

if(ArrayUtils.isNotEmpty(sqlParams)){

list = find(sql, sqlParams);

}else{

list = findBySQL(sql);

}

return new Pager<T>(totalCount, pageIndex, pageSum, pageSize, list);

}

 

/**

* Find a pager

* @param pageIndex

* @param pageSize

* @return

*/

public Pager<T> findPager(int pageIndex,int pageSize){

int totalCount = getCount();

int start = (pageIndex -1)*pageSize;

//Page count

int pageSum = 1;

if (totalCount % pageSize == 0) {

pageSum = totalCount / pageSize;

} else {

pageSum = totalCount / pageSize + 1;

}

 

String sql = "select * from "+getTableName()+" limit "+ start +","+pageSize;

List<T> list = findBySQL(sql);

log.debug(sql);

return new Pager<T>(totalCount, pageIndex, pageSum, pageSize, list);

}

 

 

 

/**

* Get count of entity

*/

public int getCount() {

String sql="select count(1) from "+getTableName();

return getJdbcTemplate().queryForInt(sql);

}

 

/**

* A generic row mapper class.

* @author lixiaodong

*/

class GenericRowMapper implements RowMapper<T>{

@SuppressWarnings("unchecked")

@Override

public T mapRow(ResultSet rs, int rowNum) throws SQLException {

Map<String, Object> columns = new ColumnMapRowMapper().mapRow(rs, rowNum);

Object object = null;

try {

  object = getEntityClass().newInstance();

  String fieldName = null;

  for (String key : columns.keySet()) {

 fieldName = lowerCaseFirst(key);

 FieldUtils.writeDeclaredField(object, fieldName, columns.get(key), true);

  }

} catch (InstantiationException e) {

log.debug(e.getMessage());

e.printStackTrace();

} catch (IllegalAccessException e) {

log.debug(e.getMessage());

e.printStackTrace();

}

return (T)object;

}

}

 

 

private String lowerCaseFirst(String str) {

return (str != null) ? str.substring(0, 1).toLowerCase()

+ str.substring(1) : null;

}

 

 

}