uas-eis/src/main/java/com/uas/eis/dao/BaseDao.java

package com.uas.eis.dao;

import com.uas.eis.model.SqlRowList;
import com.uas.eis.utils.BaseUtil;
import com.uas.eis.utils.Constant;
import com.uas.eis.utils.DateUtil;
import net.sf.json.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.*;

@Repository
public class BaseDao{
	
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public List<Map<String, Object>> queryForList(String sql) {
		try {
			return jdbcTemplate.queryForList(sql);
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
	}

	public <T> List<T> queryForList(String sql, Class<T> elementType) {
		try {
			return jdbcTemplate.queryForList(sql, elementType);
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
	}
	
	public List<Map<String, Object>> queryForList(String sql, Object... args) {
		try {
			return jdbcTemplate.queryForList(sql, args);
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
	}

	public <T> List<T> queryForList(String sql, Object[] args, Class<T> elementType) {
		try {
			return jdbcTemplate.queryForList(sql, args, elementType);
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
	}

	public <T> List<T> queryForList(String sql, Class<T> elementType, Object... args) {
		try {
			return jdbcTemplate.queryForList(sql, elementType, args);
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
	}
	
	public <T> List<T> query(String sql, Object[] args, Class<T> elementType) {
		try {
			return jdbcTemplate.query(sql, args, new BeanPropertyRowMapper<T>(elementType));
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
	}

	public <T> List<T> query(String sql, Class<T> elementType) {
		try {
			return jdbcTemplate.query(sql, new BeanPropertyRowMapper<T>(elementType));
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
	}

	public <T> List<T> query(String sql, Class<T> elementType, Object... args) {
		try {
			return jdbcTemplate.query(sql, new BeanPropertyRowMapper<T>(elementType), args);
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
	}

	public <T> T queryForObject(String sql, Class<T> elementType, Object... args) {
		try {
			return jdbcTemplate.queryForObject(sql, elementType, args);
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
	}
	
	public void execute(String sql) {
		jdbcTemplate.execute(sql);
	}

	public synchronized boolean execute(String sql, Object... objs) {
		try {
			jdbcTemplate.update(sql, objs);
			return true;
		} catch (Exception e) {
			return false;
		}
	}

	public void execute(List<String> sqls) {
		if (sqls.size() > 0) {
			StringBuffer sb = new StringBuffer("begin ");
			for (String sql : sqls) {
				sb.append("execute immediate '").append(sql.replace("'", "''")).append("';");
			}
			sb.append("end;");
			jdbcTemplate.execute(sb.toString());
		}
	}
	

	
	public void deleteById(String tablename, String keyField, long id) {
		deleteByCondition(tablename, keyField + "=" + id);
	}

	public void deleteByCondition(String tablename, String condition, Object... params) {
		StringBuffer sb = new StringBuffer();
		sb.append("DELETE FROM ");
		sb.append(tablename);
		sb.append(" WHERE ");
		sb.append(condition);
		execute(sb.toString(), params);
	}
	
	/**
	 * 一个字段，一条结果
	 * 
	 * @param tableName
	 *            对应要查询的表
	 * @param field
	 *            要查询的字段
	 * @param condition
	 *            查询条件
	 * @return field对应的数据
	 */
	public Object getFieldDataByCondition(String tableName, String field, String condition) {
		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append(field);
		sql.append(" FROM ");
		sql.append(tableName);
		sql.append(" WHERE ");
		sql.append(condition);
		SqlRowList srs = queryForRowSet(sql.toString());
		if (srs.next()) {
			return srs.getObject(1);
		} else {
			return null;
		}
	}
	
	/**
	 * 一个字段，多条结果
	 * 
	 * @param tableName
	 *            对应要查询的表
	 * @param field
	 *            要查询的字段
	 * @param condition
	 *            查询条件
	 * @return field对应的数据
	 */
	public List<Object> getFieldDatasByCondition(String tableName, String field, String condition) {
		StringBuffer sb = new StringBuffer("SELECT ");
		sb.append(field);
		sb.append(" FROM ");
		sb.append(tableName);
		sb.append(((condition == null || "".equals(condition)) ? "" : (" WHERE " + condition)));
		SqlRowList srs = queryForRowSet(sb.toString());
		List<Object> list = new ArrayList<Object>();
		while (srs.next()) {
			list.add(srs.getObject(1));
		}
		return list;
	}

	/**
	 * 多个字段，<=1条结果
	 * 
	 * @param tableName
	 *            对应要查询的表
	 * @param fields
	 *            要查询的字段,用逗号隔开
	 * @param condition
	 *            查询条件
	 * @return fields对应的数据
	 */
	public Object[] getFieldsDataByCondition(String tableName, String fields, String condition) {
		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append(fields);
		sql.append(" FROM ");
		sql.append(tableName);
		sql.append(" WHERE ");
		sql.append(condition);
		String[] strs = fields.split(",");
		int length = strs.length;
		List<Map<String, Object>> list = jdbcTemplate.queryForList(sql.toString());
		Iterator<Map<String, Object>> iter = list.iterator();
		Object[] results = new Object[length];
		Object value = null;
		if (iter.hasNext()) {
			Map<String, Object> m = iter.next();
			for (int i = 0; i < length; i++) {
				value = m.get(strs[i].toUpperCase());
				if (value != null && value.getClass().getSimpleName().toUpperCase().equals("TIMESTAMP")) {
					Timestamp time = (Timestamp) value;
					try {
						value = DateUtil.parseDateToString(new Date(time.getTime()), Constant.YMD_HMS);
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
				results[i] = value;
			}
			return results;
		}
		return null;
	}
	
	/**
	 * 多个字段，多条结果
	 * 
	 * @param tableName
	 *            对应要查询的表
	 * @param fields
	 *            要查询的字段集合
	 * @param condition
	 *            查询条件
	 * @return fields对应的数据
	 */
	public List<Object[]> getFieldsDatasByCondition(String tableName, String[] fields, String condition) {
		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append(BaseUtil.parseArray2Str(fields, ","));
		sql.append(" FROM ");
		sql.append(tableName);
		sql.append(" WHERE ");
		sql.append(condition);
		List<Map<String, Object>> list = getJdbcTemplate().queryForList(sql.toString());
		Iterator<Map<String, Object>> iter = list.iterator();
		List<Object[]> datas = new ArrayList<Object[]>();
		Object value = null;
		Map<String, Object> m = null;
		Object[] results = null;
		int length = fields.length;
		while (iter.hasNext()) {
			results = new Object[length];
			m = iter.next();
			for (int i = 0; i < length; i++) {
				value = m.get(fields[i].toUpperCase());
				if (value != null && value.getClass().getSimpleName().toUpperCase().equals("TIMESTAMP")) {
					Timestamp time = (Timestamp) value;
					try {
						value = DateUtil.parseDateToString(new Date(time.getTime()), Constant.YMD_HMS);
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
				results[i] = value;
			}
			datas.add(results);
		}
		return datas;
	}
	
	/**
	 * if resultSet is null return true
	 */
	public boolean checkByCondition(String caller, String condition) {
		int count = getCountByCondition(caller, condition);
		if (count == 0) {
			return true;
		}
		return false;
	}
	
	/**
	 * if resultSet not null return true
	 */
	public boolean checkIf(String table, String condition) {
		int count = getCountByCondition(table, condition);
		if (count > 0) {
			return true;
		}
		return false;
	}
	
	/**
	 * 修改操作
	 * 
	 * @param tableName
	 *            表
	 * @param update
	 *            修改内容
	 * @param condition
	 *            条件语句
	 */
	public void updateByCondition(String tableName, String update, String condition) {
		StringBuffer sb = new StringBuffer("UPDATE ");
		sb.append(tableName);
		sb.append(" SET ");
		sb.append(update);
		sb.append(" WHERE ");
		sb.append(condition);
		execute(sb.toString());
	}
	
	/**
	 * 调用存储过程 无返回值
	 * 
	 * @param procedureName
	 *            存储过程名称
	 * @param args
	 *            参数
	 */
	public void procedure(String procedureName, Object[] args) {
		StringBuffer sql = new StringBuffer("{call ").append(procedureName).append("(");
		for (int i = 0; i < args.length; i++) {
			if (i > 0) {
				sql.append(",");
			}
			sql.append("?");
		}
		sql.append(")}");
		jdbcTemplate.update(sql.toString(), args);
	}

	/**
	 * 调用存储过程
	 * 
	 * @param procedureName
	 *            存储过程名称
	 * @param args
	 *            参数
	 * @return varchar类型结果
	 */
	public List<String> callProcedureWithOut(final String procedureName, final Object[] args, final Integer[] inIndex,
			final Integer[] outIndex) {

		StringBuffer sql = new StringBuffer("{call " + procedureName + "(");

		for (int i = 0; i < inIndex.length + outIndex.length; i++) {
			if (sql.toString().contains("?")) {
				sql.append(",?");
			} else {
				sql.append("?");
			}
		}
		sql.append(")}");

		List<String> listR = jdbcTemplate.execute(sql.toString(), new CallableStatementCallback<List<String>>() {

			@Override
			public List<String> doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
				for (int i = 0; i < inIndex.length; i++) {
					cs.setObject(inIndex[i], args[i]);
				}

				for (int i = 0; i < outIndex.length; i++) {
					cs.registerOutParameter(outIndex[i], java.sql.Types.VARCHAR);
				}

				cs.execute();
				List<String> list = new ArrayList<String>();
				for (int i = 0; i < outIndex.length; i++) {

					list.add(cs.getString(outIndex[i]));
				}

				return list;
			}
		});
		return listR;

	}

	/**
	 * 调用存储过程
	 * 
	 * @param procedureName
	 *            存储过程名称
	 * @param args
	 *            参数
	 * @return varchar类型结果
	 */
	public String callProcedure(final String procedureName, final Object... args) {
		try {
			return jdbcTemplate.execute(new CallableStatementCreator() {
				@Override
				public CallableStatement createCallableStatement(Connection conn) throws SQLException {
					StringBuffer storedProcName = new StringBuffer("{call ");
					int i = 0;
					storedProcName.append(procedureName + "(");
					for (i = 0; i < args.length; i++) {
						if (storedProcName.toString().contains("?")) {
							storedProcName.append(",");
						}
						storedProcName.append("?");
					}
					if (storedProcName.toString().contains("?")) {
						storedProcName.append(",");
					}
					storedProcName.append("?");
					storedProcName.append(")}");
					CallableStatement cs = conn.prepareCall(storedProcName.toString());
					for (i = 0; i < args.length; i++) {
						cs.setObject(i + 1, args[i]);
					}
					cs.registerOutParameter(args.length + 1, java.sql.Types.VARCHAR);
					return cs;
				}
			}, new CallableStatementCallback<String>() {
				@Override
				public String doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
					cs.execute();
					return cs.getString(args.length + 1);
				}

			});
		} catch (Exception e) {
			BaseUtil.showError(e.getMessage());
		}
		return null;
	}

	/**
	 * 调用存储过程
	 * 
	 * @param procedureName
	 *            存储过程名称
	 * @param cls
	 *            返回结果java类型
	 * @param sqlType
	 *            返回结果的sql类型
	 * @param args
	 *            参数
	 * @return varchar类型结果
	 */
	public <T> T callbackProcedure(final String procedureName, final Class<T> cls, final int sqlType, final Object... args) {
		try {
			return jdbcTemplate.execute(new CallableStatementCreator() {
				@Override
				public CallableStatement createCallableStatement(Connection conn) throws SQLException {
					StringBuffer storedProcName = new StringBuffer("{call ");
					int i = 0;
					storedProcName.append(procedureName + "(");
					for (i = 0; i < args.length; i++) {
						if (storedProcName.toString().contains("?")) {
							storedProcName.append(",");
						}
						storedProcName.append("?");
					}
					if (storedProcName.toString().contains("?")) {
						storedProcName.append(",");
					}
					storedProcName.append("?");
					storedProcName.append(")}");
					CallableStatement cs = conn.prepareCall(storedProcName.toString());
					for (i = 0; i < args.length; i++) {
						cs.setObject(i + 1, args[i]);
					}
					cs.registerOutParameter(args.length + 1, sqlType);
					return cs;
				}
			}, new CallableStatementCallback<T>() {
				@SuppressWarnings("unchecked")
				@Override
				public T doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
					cs.execute();
					// do not use method: getObject(paramInt, paramClass)
					return (T) cs.getObject(args.length + 1);
				}

			});
		} catch (Exception e) {
			e.printStackTrace();
			BaseUtil.showError(e.getMessage());
		}
		return null;
	}

	/**
	 * 查询结果集
	 * 
	 * @param sql
	 *            查询语句
	 */
	public SqlRowList queryForRowSet(String sql) {
		SqlRowList rs = new SqlRowList();
		rs.setResultList(jdbcTemplate.queryForList(sql));
		return rs;
	}
	
	/**
	 * @param tableName
	 *            对应要查询的表
	 * @param condition
	 *            查询条件
	 * @return Count
	 */
	public int getCountByCondition(String tableName, String condition) {
		StringBuffer sql = new StringBuffer("SELECT count(1) FROM ");
		sql.append(tableName);
		sql.append(" WHERE ");
		sql.append(condition);
		SqlRowList srs = queryForRowSet(sql.toString());
		if (srs.next()) {
			return srs.getInt(1);
		} else {
			try {
				throw new Exception("Condition:" + condition + " is wrong!");
			} catch (Exception e) {
				return -1;
			}
		}
	}
	
	public List<JSONObject> getFieldsJSONDatasByCondition(String tableName, String[] fields, String condition) {
		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append(BaseUtil.parseArray2Str(fields, ","));
		sql.append(" FROM ");
		sql.append(tableName);
		sql.append(" WHERE ");
		sql.append(condition);
		List<JSONObject> datas = new ArrayList<JSONObject>();
		JSONObject obj = null;
		Object value = null;
		SqlRowList sl = queryForRowSet(sql.toString());
		while (sl.next()) {
			obj = new JSONObject();
			for (int i = 0; i < fields.length; i++) {
				value = sl.getObject(i + 1);
				if (value != null && "TIMESTAMP".equals(value.getClass().getSimpleName().toUpperCase())) {
					Timestamp time = (Timestamp) value;
					value = DateUtil.parseDateToString(new Date(time.getTime()), "yyyy-MM-dd HH:mm:ss");
				}
				obj.put(fields[i], value);
			}
			datas.add(obj);
		}
		return datas;
	}
	
}