cn.tom.db.jdbc.simple.DBTemplate Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of cocook-mvc Show documentation
cocook mvc kits
There is a newer version: 1.0.3
package cn.tom.db.jdbc.simple;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import cn.tom.mvc.jdbc.DBPool;
import cn.tom.db.jdbc.DBOperations;
import cn.tom.kit.clazz.ReflectUtil;

public class DBTemplate extends DBOperations {
	
	/**
	 * get(1) 单行单列查询方法
	 * @param conn
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	private Object getObject(Connection conn,String sql, Object... params) throws SQLException {
		try{
			return DBUtil.getObject(DBUtil.query(conn, sql, params));
		}finally{
			DBUtil.close(conn);
		}
	} 
	
	@Override
	public  T queryForObject(String sql, Object... params) throws SQLException {
		return (T) getObject(DBUtil.getConnection(), sql, params);
	}

	@Override
	public  T queryForObject(String sql, Class clazz, Object... params) throws SQLException {
		Object obj = getObject(DBUtil.getConnection(), sql, params);
		return obj == null ? clazz.cast(null) : clazz.cast(ReflectUtil.covert(clazz, obj));
	}
	
	@Override
	public  T queryForObject(Connection conn, String sql, Class clazz, Object... params) throws SQLException {
		Object obj = DBUtil.getObject(DBUtil.query(conn, sql, params));
		return obj == null ? clazz.cast(null) : clazz.cast(ReflectUtil.covert(clazz, obj));
	}

	@Override
	public Long queryForLong(String sql, Object... params) throws SQLException {
		return queryForObject(sql, Long.class, params);
	}

	@Override
	public Integer queryForInt(String sql, Object... params) throws SQLException {
		return queryForObject(sql, Integer.class, params);
	}

	@Override
	public String queryForString(String sql, Object... params) throws SQLException {
		return queryForObject(sql, String.class, params);
	}
	
	/**
	 * 单列 多行 数据集合
	 * @param 
	 * @param _conn
	 * @param sql
	 * @param clazz
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	private static  List getListT(Connection conn, String sql, Class clazz, Object... params) throws SQLException{
		try{
			return DBUtil.getListT(DBUtil.query(conn, sql, params), clazz) ;
		}finally{
			DBUtil.close(conn);
		}
	}
	
	@Override
	public  List queryForList(Connection conn, String sql, Class clazz, Object... params) throws SQLException {
		return DBUtil.getListT(DBUtil.query(conn, sql, params), clazz);
	}

	@Override
	public  List queryForList(String sql, Class clazz, Object... params) throws SQLException {
		return getListT(DBUtil.getConnection(), sql, clazz, params);
	}

	/**
	 * 多行多列 key -> map 数据集合
	 * @param conn
	 * @param sql
	 * @param clazz
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	private List> getList(Connection conn, String sql, @SuppressWarnings("rawtypes") Class clazz, Object... params) throws SQLException{
		try{
			return DBUtil.getList(DBUtil.query(conn, sql, params), clazz) ;
		}finally{
			DBUtil.close(conn);
		}
	}
	
	
	@Override
	public List> queryForList(Connection conn, String sql, Object... params) throws SQLException {
		return DBUtil.getList(DBUtil.query(conn, sql, params), HashMap.class) ;
	}
	
	@Override
	public List> queryForList(String sql, Object... params) throws SQLException {
		return getList(DBUtil.getConnection(), sql,  HashMap.class, params);
	}
	
	@Override
	public List> queryForLinkedMapList(String sql, Object... params) throws SQLException {
		return getList(DBUtil.getConnection(), sql, LinkedHashMap.class, params); 
	};

	@Override
	public Map queryForMap(Connection conn, String sql, Object... params) throws SQLException {
		List> list = queryForList(conn, sql, params);
		if (list.size() == 0) {
			return new HashMap<>();
		}
		return list.get(0);
	}
	@Override
	public Map queryForMap(String sql, Object... params) throws SQLException {
		List> list = queryForList(sql, params);
		if (list.size() == 0) {
			return new HashMap<>();
		}
		return list.get(0);
	}

	@Override
	public Map queryForLinkedMap(String sql) throws SQLException {
		return queryForLinkedMap(sql, new Object[] {});
	}

	@Override
	public Map queryForLinkedMap(String sql, Object... params) throws SQLException {
		List> list = queryForLinkedMapList(sql, params);
		if (list.size() == 0) {
			return new LinkedHashMap<>();
		}
		return list.get(0);
	}

	@Override
	public int update(String sql, Object... params) throws SQLException {
		Connection conn = DBUtil.getConnection();
		try{
			return update(conn, sql, params);
		}finally{
			DBUtil.close(conn);
		}
	}
	
	@Override
	public int update(Connection conn, String sql, Object... params) throws SQLException {
		if(DBPool.show_sql) DBPool.logger.info(sql +" "+ Arrays.asList(params));
		PreparedStatement pst = conn.prepareStatement(sql);
		DBUtil.bindInputStatement(pst,params);
		return pst.executeUpdate();
	}
	
	@Override
	public int insert(String sql, Object... params) throws SQLException {
		Connection conn = DBUtil.getConnection();
		try{
			return insert(conn, sql, params);
		}finally{
			DBUtil.close(conn);
		}
	}
	
	@Override
	public int insert(Connection conn, String sql, Object... params) throws SQLException {
		if(DBPool.show_sql) DBPool.logger.info(sql +" "+ Arrays.asList(params));
		PreparedStatement pst = conn.prepareStatement(sql, 1); // 1 表示返回 当前插入数据
		DBUtil.bindInputStatement(pst,params);
		int r = pst.executeUpdate();
		try{
			ResultSet rs = pst.getGeneratedKeys();
			rs.next();
			r = rs.getInt(1);                               // 获取第一个 返回数据的值, 一般第一位是 id
			return r;
		}catch(SQLException e){
			DBPool.logger.error("insert:", e);
			return r;
		}finally{
			DBUtil.close(pst);
		}
	}
	
	@Override
	public int execute(String sql, Object... params) throws SQLException {
		return update(sql, params);
	}
	
	@Override
	public int[] batchUpdate(String sql, List list) throws SQLException {
		if(DBPool.show_sql) DBPool.logger.info(sql +" "+ list);
		Connection conn = DBUtil.getConnection();
		PreparedStatement pst = null;
		try{
			conn.setAutoCommit(false);
			pst = conn.prepareStatement(sql);
			for(Object[] obj: list){
				DBUtil.bindInputStatement(pst, obj);
				pst.addBatch();
			}
			int i[] = pst.executeBatch();
			conn.commit();
			return i;
		}catch(SQLException e){
			DBUtil.rollback(conn);
			throw e;
		}finally{
			DBUtil.close(pst);
			DBUtil.close(conn);
		}
	}

	@Override
	public int[] batchUpdate(String sql, Object... obj) throws SQLException {
		if(DBPool.show_sql) DBPool.logger.info(sql +" "+ Arrays.asList(obj));
		Connection conn = DBUtil.getConnection();
		PreparedStatement pst = null;
		try{
			conn.setAutoCommit(false);
			pst = conn.prepareStatement(sql);
			for(Object o: obj){
				DBUtil.bindInputStatement(pst, o);
				pst.addBatch();
			}
			int i[] = pst.executeBatch();
			conn.commit();
			return i;
		}catch(SQLException e){
			DBUtil.rollback(conn);
			throw e;
		}finally{
			DBUtil.close(pst);
			DBUtil.close(conn);
		}
	}

	@Override
	public int[] batchUpdate(List list) throws SQLException {
		Connection conn = DBUtil.getConnection();
		Statement stat  = null;
		try{
			conn.setAutoCommit(false);
			stat = conn.createStatement();
			for(String sql: list){
				stat.addBatch(sql);
			}
			int i[] = stat.executeBatch();
			conn.commit();
			return i;
		}catch(SQLException e){
			DBUtil.rollback(conn);
			throw e;
		}finally{
			DBUtil.close(stat);
			DBUtil.close(conn);
		}
	}
	
}