【工具】线程安全的JdbcTemplate.java

原创
2015/12/07 11:33
阅读数 958

分享一个方便好用的jdbcTemplate工具类,做过常规测试,如果bug,欢迎反馈。 

package com.servlet.demo.framework.db;

import java.lang.reflect.Constructor;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.lang.reflect.Modifier;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


/***
 * JDBCTemplate<br>
 * 线程安全的。 2015-12-07 10:48:36
 * @author yuanqy
 */
public final class JdbcTemplate {
    private String jdbcUrl;
    private String user;
    private String password;
    private Logger log = LoggerFactory.getLogger(JdbcTemplate.class);
    private DataSource datasource;
    private ThreadLocal<Connection> connection = new ThreadLocal<Connection>();
    private ThreadLocal<PreparedStatement> pstmt = new ThreadLocal<PreparedStatement>();
    private ThreadLocal<ResultSet> resultSet = new ThreadLocal<ResultSet>();

    private boolean tx = false;// 是否开启事物

    public JdbcTemplate(DataSource datasource) {
        this.datasource = datasource;
    }

    public JdbcTemplate(String jdbcUrl, String user, String password) throws ClassNotFoundException {
        init("com.mysql.jdbc.Driver", jdbcUrl, user, password);
    }

    public JdbcTemplate(String driverClass, String jdbcUrl, String user, String password) throws ClassNotFoundException {
        init(driverClass, jdbcUrl, user, password);
    }

    private void init(String driverClass, String jdbcUrl, String user, String password) throws ClassNotFoundException {
        try {
            Class.forName(driverClass);
            this.jdbcUrl = jdbcUrl;
            this.user = user;
            this.password = password;
        } catch (Exception e) {
            throw new ClassNotFoundException("找不到驱动程序类 [" + driverClass + "],加载驱动失败!");
        }
    }

    /**
     * 增加、删除、改
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    public boolean update(String sql, Object... params) throws SQLException {
        boolean flag = false;
        try {
            initConnect();
            initParam(sql, params);
            int result = pstmt.get().executeUpdate();
            flag = result > 0 ? true : false;
        } catch (SQLException e) {
            throw e;
        } finally {
            destroy();
        }
        return flag;
    }

    /**
     * 查询单条记录
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    public Map<String, Object> query(String sql, Object... params) throws SQLException {
        Map<String, Object> map = null;
        try {
            initConnect();
            initParam(sql, params);
            resultSet.set(pstmt.get().executeQuery());// 返回查询结果
            ResultSet rset = resultSet.get();
            ResultSetMetaData metaData = rset.getMetaData();
            map = new HashMap<String, Object>();
            while (rset.next()) {
                map = getMap(rset, metaData);
            }
        } catch (SQLException e) {
            throw e;
        } finally {
            destroy();
        }
        return map;
    }

    /**
     * 查询多条记录
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    public List<Map<String, Object>> queryList(String sql, Object... params) throws SQLException {
        List<Map<String, Object>> list = null;
        try {
            initConnect();
            initParam(sql, params);
            resultSet.set(pstmt.get().executeQuery());
            ResultSet rset = resultSet.get();
            ResultSetMetaData metaData = rset.getMetaData();
            list = new ArrayList<Map<String, Object>>();
            while (rset.next()) {
                list.add(getMap(rset, metaData));
            }
        } catch (SQLException e) {
            throw e;
        } finally {
            destroy();
        }
        return list;
    }

    /**
     * 通过反射机制查询单条记录
     * @param sql
     * @param params
     * @param cls
     * @return
     * @throws ReflectiveOperationException
     * @throws Exception
     */
    public <T> T queryBean(String sql, Class<T> cls, Object... params) throws SQLException, ReflectiveOperationException {
        T resultObject = null;
        try {
            initConnect();
            initParam(sql, params);
            resultSet.set(pstmt.get().executeQuery());
            ResultSet rset = resultSet.get();
            ResultSetMetaData metaData = rset.getMetaData();
            while (rset.next()) {
                resultObject = getBean(rset, metaData, cls);
            }
        } catch (SQLException e) {
            throw e;
        } catch (ReflectiveOperationException e) {
            throw e;
        } finally {
            destroy();
        }
        return resultObject;
    }

    /**
     * 通过反射机制查询多条记录
     * @param sql
     * @param params
     * @param cls
     * @return
     * @throws ReflectiveOperationException
     * @throws Exception
     */
    public <T> List<T> queryListBean(String sql, Class<T> cls, Object... params) throws SQLException, ReflectiveOperationException {
        List<T> list = null;
        try {
            initConnect();
            initParam(sql, params);
            resultSet.set(pstmt.get().executeQuery());
            ResultSet rset = resultSet.get();
            ResultSetMetaData metaData = rset.getMetaData();
            list = new ArrayList<T>();
            while (rset.next()) {
                list.add(getBean(rset, metaData, cls));
            }
        } catch (SQLException e) {
            throw e;
        } catch (ReflectiveOperationException e) {
            throw e;
        } finally {
            destroy();
        }
        return list;
    }

    /**
     * 释放数据库连接
     */
    public void destroy() {
        if (!tx) {
            if (resultSet.get() != null) { // 关闭记录集
                try {
                    resultSet.get().close();
                } catch (SQLException e) {
                    log.error("An SQLException occurs when the ResultSet do close()", e);
                } finally {
                    resultSet.remove();
                }
            }
            if (pstmt.get() != null) { // 关闭声明
                try {
                    pstmt.get().close();
                } catch (SQLException e) {
                    log.error("An SQLException occurs when the PreparedStatement do close()", e);
                } finally {
                    pstmt.remove();
                }
            }
            if (connection.get() != null) { // 关闭连接对象
                try {
                    connection.get().close();
                } catch (SQLException e) {
                    log.error("An SQLException occurs when the Connection do close()", e);
                } finally {
                    connection.remove();
                }
            }
        }
    }

    /**
     * 开启事物
     * @throws SQLException
     */
    public void openTransaction() throws SQLException {
        tx = true;
        initConnect();
        connection.get().setAutoCommit(false);
        // connection.setTransactionIsolation(TransactionIsolationLevel.REPEATABLE_READ.getLevel());
    }

    public void openTransaction(int isolationLevel) throws SQLException {
        openTransaction();
        connection.get().setTransactionIsolation(isolationLevel);
    }

    /** 事物回滚 */
    public void rollback() {
        dotx(true);
    }

    /** 事物提交 */
    public void commit() {
        dotx(false);
    }

    private void dotx(boolean bo) {
        if (connection.get() != null) {
            try {
                if (bo)
                    connection.get().rollback();
                else
                    connection.get().commit();
            } catch (SQLException e) {
                log.error("An SQLException occurs when the Connection do rollback() or commit()", e);
            }
            tx = false;
            destroy();
        }
    }

    /**
     * 获得数据库的连接
     * @return
     * @throws SQLException
     */
    private Connection initConnect() throws SQLException {
        if (connection.get() == null || connection.get().isClosed()) {
            try {
                if (datasource != null)
                    connection.set(datasource.getConnection());
                else
                    connection.set(DriverManager.getConnection(jdbcUrl, user, password));
            } catch (SQLException e) {
                log.error("JDBC连接失败:", e);
                throw e;
            }
        }
        return connection.get();
    }

    /**
     * 拼接参数
     */
    private PreparedStatement initParam(String sql, Object... params) throws SQLException {
        int index = 1;
        pstmt.set(connection.get().prepareStatement(sql));
        if (params != null && params.length >= 0) {
            for (int i = 0; i < params.length; i++) {
                pstmt.get().setObject(index++, params[i]);
            }
        }
        return pstmt.get();
    }

    private Map<String, Object> getMap(ResultSet rset, ResultSetMetaData metaData) throws SQLException {
        Map<String, Object> map = new HashMap<String, Object>();
        int col_len = metaData.getColumnCount();
        for (int i = 0; i < col_len; i++) {
            String cols_name = metaData.getColumnLabel(i + 1);
            map.put(cols_name, rset.getObject(cols_name));
        }
        return map;
    }

    @SuppressWarnings("unchecked")
    private <T> T getBean(ResultSet rset, ResultSetMetaData metaData, Class<T> cls) throws InstantiationException, IllegalAccessException, IllegalArgumentException, InvocationTargetException, SQLException {
        int mi = cls.getModifiers();
        int count = metaData.getColumnCount();
        T bean = null;
        // ================================
        if (Modifier.isInterface(mi) || Modifier.isAbstract(mi)) throw new InstantiationException(cls.getName());
        // ================================
        if (count == 1) {
            Constructor<T> consTemp = null;
            Constructor<?>[] cons = cls.getConstructors();
            for (Constructor<?> constructor : cons) {
                if (constructor.getParameterTypes().length == 1) {
                    consTemp = (Constructor<T>) constructor;
                    break;
                }
            }
            if (consTemp != null) {
                String clabel = metaData.getColumnLabel(1);
                bean = consTemp.newInstance(format(cls, rset, clabel));
                return bean;
            }
        }
        // ================================
        // 通过反射机制创建一个实例
        bean = cls.newInstance();
        // == Set方法 ===========================================
        Method[] ms = cls.getMethods();
        for (Method m : ms) {
            if (m.getName().startsWith("set") && m.getParameterTypes().length == 1) {
                String clabel = firstCharToLowerCase(m.getName().substring("set".length()));
                if (isExistColumn(rset, clabel)) 
                    m.invoke(bean, format(m.getParameterTypes()[0], rset, clabel));
            }
        }
        return bean;
    }



    /**
     * 将Obj转换成指定类型返回,
     * @throws SQLException
     */
    @SuppressWarnings({"unchecked", "rawtypes"})
    private Object format(Class<?> type, ResultSet rset, String clabel) throws SQLException {
        if (type.equals(String.class)) return rset.getString(clabel);
        if (type.equals(short.class) || type.equals(Short.class)) return rset.getShort(clabel);// short:16位,最大数据存储量是65536,数据范围是-32768~32767之间。
        if (type.equals(int.class) || type.equals(Integer.class)) return rset.getInt(clabel);// int:32位,最大数据存储容量是2的32次方减1,数据范围是负的2的31次方到正的2的31次方减1。
        if (type.equals(long.class) || type.equals(Long.class)) return rset.getLong(clabel);// long:64位,最大数据存储容量是2的64次方减1,数据范围为负的2的63次方到正的2的63次方减1。
        if (type.equals(float.class) || type.equals(Float.class)) return rset.getFloat(clabel);// float:32位,数据范围在3.4e-45~1.4e38,直接赋值时必须在数字后加上f或F。
        if (type.equals(double.class) || type.equals(Double.class)) return rset.getDouble(clabel);// double:64位,数据范围在4.9e-324~1.8e308,赋值时可以加d或D也可以不加。
        if (type.equals(boolean.class) || type.equals(Boolean.class)) return rset.getBoolean(clabel);// boolean:只有true和false两个取值。
        if (type.equals(java.math.BigDecimal.class)) return rset.getBigDecimal(clabel);
        if (type.equals(java.sql.Date.class)) return rset.getDate(clabel);
        if (type.equals(java.util.Date.class)) return rset.getDate(clabel) == null ? null : new java.util.Date(rset.getDate(clabel).getTime());
        if (type.equals(java.sql.Time.class)) return rset.getTime(clabel);
        if (type.equals(java.sql.Timestamp.class)) return rset.getTimestamp(clabel);
        if (type.isEnum()) return Enum.valueOf((Class<? extends Enum>) type, rset.getString(clabel));

        if (type.equals(byte.class) || type.equals(Byte.class)) return rset.getByte(clabel);// byte:8位,最大存储数据量是255,存放的数据范围是-128~127之间。
        if (type.equals(byte[].class) || type.equals(Byte[].class)) return rset.getBytes(clabel);
        if (type.equals(java.io.InputStream.class)) return rset.getBinaryStream(clabel);
        if (type.equals(java.io.Reader.class)) return rset.getCharacterStream(clabel);

        if (type.equals(java.sql.Array.class)) return rset.getArray(clabel);
        if (type.equals(java.sql.Blob.class)) return rset.getBlob(clabel);
        if (type.equals(java.sql.Clob.class)) return rset.getClob(clabel);

        if (type.equals(java.sql.Ref.class)) return rset.getRef(clabel);
        if (type.equals(java.sql.RowId.class)) return rset.getRowId(clabel);
        if (type.equals(java.sql.SQLXML.class)) return rset.getSQLXML(clabel);
        if (type.equals(java.net.URL.class)) return rset.getURL(clabel);
        log.warn("Type conversion failed,[ClassType:" + type.getName() + ",value:" + rset.getObject(clabel) + "],Please check the set method for parameters");
        return null;
    }

    /**
     * 首字母变小写
     */
    private String firstCharToLowerCase(String str) {
        Character firstChar = str.charAt(0);
        String tail = str.substring(1);
        str = Character.toLowerCase(firstChar) + tail;
        return str;
    }

    /**
     * 判断查询结果集中是否存在某列
     */
    private boolean isExistColumn(ResultSet rs, String columnName) {
        try {
            if (rs.findColumn(columnName) > 0) return true;
        } catch (SQLException e) {
            return false;
        }
        return false;
    }
}

测试Demo:

package com.servlet.demo.test;

import java.math.BigDecimal;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.servlet.demo.framework.db.JdbcTemplate;
import com.servlet.demo.framework.utill.JsonUtil;

/**
 * @author yuanqy
 * @time 2017年11月23日 下午2:58:51
 */
public class JdbcTest {
    // 测试表与数据
//    CREATE TABLE `demo` (
//        `id` bigint(11) NOT NULL AUTO_INCREMENT,
//        `name` varchar(25) DEFAULT NULL,
//        `age` int(3) DEFAULT NULL,
//        `sex` varchar(15) DEFAULT NULL,
//        `money` decimal(10,2) DEFAULT NULL,
//        `birthday` datetime DEFAULT NULL,
//        `married` tinyint(1) DEFAULT '1',
//        `score` double(5,2) DEFAULT NULL,
//        PRIMARY KEY (`id`)
//      );
//      insert  into `demo`(`id`,`name`,`age`,`sex`,`money`,`birthday`,`married`,`score`) values (1001,'马云',55,'man','35000.00','1991-10-02 19:50:42',1,59.50);
    public static void main(String[] args) {
        try {
            JdbcTemplate jdbc = new JdbcTemplate("jdbc:mysql://127.0.0.1:3306/test", "root", "password");
            //
            Entity en = jdbc.queryBean("select *,now() as nowTime from demo where id=?", JdbcTest.Entity.class, 1001);
            System.err.println(JsonUtil.toJson(en));
            //
            List<Long> ids = jdbc.queryListBean("select id from demo", Long.class);
            System.err.println(JsonUtil.toJson(ids));
            //
            Map<String, Object> map = jdbc.query("select *,now() as nowTime from demo where id=?", 1001);
            System.err.println(JsonUtil.toJson(map));

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public enum Sex {
        man, women, center, ohter
    }
    public static class Entity {
        long id;
        String name;
        int age;
        Sex sex;
        BigDecimal money;
        java.sql.Timestamp nowTime;
        java.util.Date birthday;
        boolean married;
        double score;

        public void setId(long id) {
            this.id = id;
        }

        public void setName(String name) {
            this.name = name;
        }

        public void setAge(int age) {
            this.age = age;
        }

        public void setSex(String sex) {
            this.sex = Sex.valueOf(sex);
        }

        public void setMoney(BigDecimal money) {
            this.money = money;
        }

        public void setMarried(boolean married) {
            this.married = married;
        }

        public void setScore(double score) {
            this.score = score;
        }
//===========================

        public double getScore() {
            return score;
        }

        public java.sql.Timestamp getNowTime() {
            return nowTime;
        }

        public void setNowTime(java.sql.Timestamp nowTime) {
            this.nowTime = nowTime;
        }

        public java.util.Date getBirthday() {
            return birthday;
        }

        public void setBirthday(java.util.Date birthday) {
            this.birthday = birthday;
        }

        public long getId() {
            return id;
        }

        public String getName() {
            return name;
        }

        public int getAge() {
            return age;
        }

        public Sex getSex() {
            return sex;
        }

        public BigDecimal getMoney() {
            return money;
        }

        public boolean isMarried() {
            return married;
        }

    }
}

 

展开阅读全文
加载中

作者的其它热门文章

打赏
0
0 收藏
分享
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部