JDBC工具类和通用的Dao父类

JDBC工具类（负责连接和释放资源）

package com.myutils;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

/**
 * @author 谢英亮
 * @date 2018年1月4日  下午8:14:34
 * @Description: 连接数据库
 */
public class DBUtil {
    private static String url;
    private static String user;
    private static String password;
    private static ThreadLocal<Connection> threadLocal;
    static {
        Properties prop = new Properties();
        threadLocal = new ThreadLocal<>();
        try {// 加载配置文件
            prop.load(new FileInputStream("src/com/myutils/db.properties"));
            //读取配置文件
            url=prop.getProperty("url");
            user=prop.getProperty("user");
            password=prop.getProperty("password");
            // 加载驱动
            Class.forName(prop.getProperty("driver"));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取连接
     * 
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        Connection conn = threadLocal.get();
        if (null != conn) {
            return conn;
        }
        conn = DriverManager.getConnection(url, user, password);
        threadLocal.set(conn);
        return conn;
    }

    /**
     * 释放资源
     * 
     * @throws SQLException
     */
    public static void close() throws SQLException {
        Connection conn = threadLocal.get();
        if (null != conn && !conn.isClosed()) {
            conn.close();
            threadLocal.remove();
        }
    }
}

通用的Dao类（更新和查询）

package com.myutils;

import java.sql.Connection;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.beanutils.BeanUtils;

/**
 * @author 谢英亮
 * @date 2018年1月4日 下午8:17:02
 * @Description: 通用的dao的父类
 */
public class BaseDao {
    /**
     * 执行增删改
     * 
     * @param sql
     *            sql语句
     * @param params
     *            替换占位符
     * @return 受影响的行数
     * @throws SQLException
     */
    public int executeUpdate(String sql, Object... params) throws SQLException {
        // 获取连接
        Connection conn = DBUtil.getConnection();
        // 获取PrepareStatement对象
        PreparedStatement state = conn.prepareStatement(sql);
        // 获取请求参数元数据
        ParameterMetaData metaData = state.getParameterMetaData();
        // 获取占位符的个数
        int count = metaData.getParameterCount();
        if (null != params) {
            if (count != params.length) {
                throw new RuntimeException("参数传递错误！");
            }
            // 替换占位符
            for (int i = 0; i < params.length; i++) {
                state.setObject(i + 1, params[i]);
            }
        }
        // 执行更新
        int affectRows = state.executeUpdate();
        // 释放资源
        DBUtil.close();
        return affectRows;
    }

    /**
     * 查询
     * 
     * @param sql
     * @param clazz
     * @param params
     * @return 对象的list集合
     * @throws Exception
     */
    public <T> List<T> queryList(String sql, Class clazz, Object... params) throws Exception {
        List<T> list = new ArrayList<>();
        // 获取连接
        Connection conn = DBUtil.getConnection();
        // 获取PrepareStatement对象
        PreparedStatement state = conn.prepareStatement(sql);
        // 获取请求参数元数据
        ParameterMetaData parameterMetaData = state.getParameterMetaData();
        int count = parameterMetaData.getParameterCount();
        if (null != params) {
            if (count != params.length) {
                throw new RuntimeException("参数传递错误！");
            }
            for (int i = 0; i < count; i++) {
                state.setObject(i + 1, params[i]);
            }
        }
        // 执行查询
        ResultSet rs = state.executeQuery();
        // 获取结果集元数据
        ResultSetMetaData metaData = state.getMetaData();
        // 获取结果集的总列数
        int colCount = metaData.getColumnCount();
        // 遍历结果集
        while (rs.next()) {
            T t = (T) clazz.newInstance();
            for (int i = 0; i < colCount; i++) {
                // 获取列名即值
                String colName = metaData.getColumnName(i + 1);
                Object colVal = rs.getObject(colName);
                // 为对象属性赋值
                BeanUtils.copyProperty(t, colName, colVal);
            }
            // 往list中添加元素
            list.add(t);
        }
        //释放资源
        DBUtil.close();
        return list.size() == 0 ? null : list;
    }
}

用户实体类（属性名需与数据库字段名一致）

package com.myutils;

/**
 * @author 谢英亮
 * @date 2018年1月4日  下午8:58:30
 * @Description: 用户实体类
 */
public class User {
    private int id;
    private String name;
    private int age;
    private String gendar;
    public User() {

    }
    public User(String name, int age, String gendar, int id) {
        super();
        this.name = name;
        this.age = age;
        this.gendar = gendar;
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }
    public String getGendar() {
        return gendar;
    }
    public void setGendar(String gendar) {
        this.gendar = gendar;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    @Override
    public String toString() {
        return "User [id=" + id + ", name=" + name + ", age=" + age + ", gendar=" + gendar + "]";
    }


}

UserDao类 继承与BaseDao

package com.myutils;

public class UserDao extends BaseDao {

}

测试类

package com.myutils;

import java.sql.SQLException;
import java.util.List;

import org.junit.Test;

public class Demo {
    @Test
    public void testBaseDao() throws SQLException {
        UserDao userDao = new UserDao();
        String sql = "insert into t_user(name,age,gendar) values (?,?,?)";
        // userDao.executeUpdate(sql, "哈哈",22,"男");
        String sql1 = "update t_user set name=? where id=?";
        // userDao.executeUpdate(sql1, "test",5);
        String sql2 = "delete from t_user where id=?";
        userDao.executeUpdate(sql2, 6);
    }
    @Test
    public void testQuery() throws Exception{
        UserDao userDao=new UserDao();
        String sql="select id,name,age,gendar from t_user";
        List<User> list = userDao.queryList(sql, User.class,null );
        System.out.println(list);
    }
}

配置文件

driver=com.mysql.jdbc.Driver
url=jdbc:mysql:///test
user=root
password=admin

sql脚本

CREATE DATABASE test DEFAULT CHARSET = utf8;
USE test;
CREATE TABLE t_user ( id INTEGER auto_increment, NAME VARCHAR ( 10 ), age INTEGER, gendar VARCHAR ( 2 ), PRIMARY KEY ( id ) )

注：在实现对象的属性映射时使用了Beanutils组件，直接给属性进行赋值。网上对jdbc工具类的封装有很多方法，都各有优缺点。如有疑问，请留言，欢迎指出。