[Java知识库]JDBC使用模板

1、前言

学习JDBC是一个技术迭代的过程，从无到有，现在已经集成好了轮子只要拿来使用就可以了，所以这个模板用来在开发中直接复制粘贴就可。

2、主要的包

bean 包：用于将数据库中的数据对应成类
DAO包：BaseDAO存放的是增删改的基本操作，剩下两个是对应到类的接口和接口实现类，juint用来测试接口实现类中的方法是否有错误
JDBCUtil：用于提供数据库连接和关闭数据可连接的操作

2.1、bean包

以Customer举例：属性、构造器、get、set方法和toString

//ORM(object relational mapping)编程思想:一个数据表对应一个java类
public class Customer {
    private int id;
    private String name;
    private String email;
    private Date birth;

    public Customer() {
    }

    public Customer(int id, String name, String email, Date birth) {
        this.id = id;
        this.name = name;
        this.email = email;
        this.birth = birth;
    }

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Date getBirth() {
        return birth;
    }

    public void setBirth(Date birth) {
        this.birth = birth;
    }

    @Override
    public String toString() {
        return "Customer{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", email='" + email + '\'' +
                ", birth=" + birth +
                '}';
    }
}

2.2、DAO包

1. BaseDAO

自己写的（不太健壮）

public abstract class BaseDAO<T> {

    private Class<T> clazz=null;
    {
        Type genericSuperclass = this.getClass().getGenericSuperclass();
        ParameterizedType parameterizedType = (ParameterizedType) genericSuperclass;
        Type[] actualTypeArguments = parameterizedType.getActualTypeArguments();
        clazz= (Class<T>) actualTypeArguments[0];
    }

    //通用增删改操作
    public static void update(Connection conn, String sql, Object ...args){//占位符的个数，取决于可变形参数组的长度
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            for(int i=0;i<args.length;++i){
                ps.setObject(i+1,args[i]);
            }
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

            try {
                conn.setAutoCommit(true);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
            }
        }
    }

    //获得一个查询的结果
    public  T getInstance(Connection conn, String sql, Object ...args){
        PreparedStatement ps=null;
        ResultSet resultSet=null;
        try{
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement(sql);
            for(int i=0;i<args.length;++i){
                ps.setObject(i+1,args[i]);
            }
            resultSet = ps.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            ArrayList<T> list = new ArrayList<>();

            if(resultSet.next()){
                T t = clazz.newInstance();
                for(int i=0;i<columnCount;++i){
                    Object columnValue = resultSet.getObject(i + 1);
                    String columnLabel = metaData.getColumnLabel(i+1);

                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t,columnValue);
                }
                return t;
            }
        }catch (Exception e){
            System.out.println(e.getMessage());
        }finally {
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }


    //查找的通用函数
    public List<T> getForList(Connection conn, String sql, Object ...args){
        PreparedStatement ps=null;
        ResultSet resultSet=null;
        try{
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement(sql);
            for(int i=0;i<args.length;++i){
                ps.setObject(i+1,args[i]);
            }
            resultSet = ps.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            ArrayList<T> list = new ArrayList<>();

            while(resultSet.next()){
                T t = clazz.newInstance();
                for(int i=0;i<columnCount;++i){
                    Object columnValue = resultSet.getObject(i + 1);
                    String columnLabel = metaData.getColumnLabel(i+1);

                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t,columnValue);
                }
                list.add(t);
            }
            return list;
        }catch (Exception e){
            System.out.println(e.getMessage());
        }finally {
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    //查询特殊值的函数
    public <E> E getValue(Connection conn,String sql,Object ...args){
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            ps = conn.prepareStatement(sql);
            for(int i=0;i<args.length;++i){
                ps.setObject(i+1,args[i]);
            }
            resultSet = ps.executeQuery();
            if(resultSet.next()){
                return (E) resultSet.getObject(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn,ps,resultSet);
        }
        return null;
    }
}

DbUtils包，Apache公司提供的包，很健壮，推荐使用

public abstract class BaseDAO<T> {

    private Class<T> clazz=null;
    {
        Type genericSuperclass = this.getClass().getGenericSuperclass();
        ParameterizedType parameterizedType = (ParameterizedType) genericSuperclass;
        Type[] actualTypeArguments = parameterizedType.getActualTypeArguments();
        clazz= (Class<T>) actualTypeArguments[0];
    }

    //通用增删改操作
    public static void update(Connection conn, String sql, Object ...args){//占位符的个数，取决于可变形参数组的长度
        try {
            QueryRunner runner = new QueryRunner();
            int update = runner.update(conn, sql, args);
            System.out.println("更改了"+update+"条数据");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn);
        }
    }

    //获得一个查询的结果
    public  T getInstance(Connection conn, String sql, Object ...args){
        try {
            QueryRunner runner = new QueryRunner();
            BeanHandler<T> handler = new BeanHandler<T>(clazz);
            return runner.query(conn, sql, handler,args);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn);
        }
        return null;
    }


    //查找的通用函数
    public List<T> getForList(Connection conn, String sql, Object ...args){
        try {
            QueryRunner runner = new QueryRunner();
            BeanListHandler<T> handler = new BeanListHandler<>(clazz);
            return runner.query(conn, sql, handler,args);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn);
        }
        return null;
    }

    //查询特殊值的函数
    public <E> E getValue(Connection conn,String sql,Object ...args){
        try {
            QueryRunner runner = new QueryRunner();
            ScalarHandler handler = new ScalarHandler();
            E query = (E)runner.query(conn, sql, handler);
            return query;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn);
        }
        return null;
    }
}

2. CustomerDao：作为一个例子，实际开发中要根据需求进行改写

public interface CustomerDao {
    //将Customer对象添加到数据库中
    void insert(Connection conn, Customer cust);

    //根据id删除表内记录
    void deleteById(Connection conn,int id);

    //将指定id对象修改成指定对象
    void update(Connection conn,Customer cust);

    //针对指定Id查询对应的Customer对象
    Customer getCustomerById(Connection conn,int id);

    //查询表中的所有记录构成的集合
    List<Customer> getAll(Connection conn);

    //返回数据表中数据数
    long getCount(Connection conn);

    //
    Date getMaxBirth(Connection conn);
}

3. CustomerDaoImpl：接口实现类

public class CustomerDaoImpl extends BaseDAO<Customer> implements CustomerDao{

    @Override
    public void insert(Connection conn, Customer cust) {
        String sql="insert into customers(name,email,birth)values(?,?,?)";
        update(conn,sql,cust.getName(),cust.getEmail(),cust.getBirth());
    }

    @Override
    public void deleteById(Connection conn, int id) {
        String sql="delete from customers where id=?";
        update(conn,sql,id);
    }

    @Override
    public void update(Connection conn,  Customer cust) {
        String sql="update customers set name=?,email=?,birth=? where id=?";
        update(conn,sql,cust.getName(),cust.getEmail(),cust.getBirth(),cust.getId());
    }

    @Override
    public Customer getCustomerById(Connection conn, int id) {
        String sql="select id,name,email,birth from customers where id=?";
        Customer instance = getInstance(conn, sql, id);
        return instance;
    }

    @Override
    public List<Customer> getAll(Connection conn) {
        String sql="select id,name,email,birth from customers";
        List<Customer> list = getForList(conn, sql);
        return list;
    }

    @Override
    public long getCount(Connection conn) {
        String sql="select count(*) from customers";
        long value = (long)getValue(conn, sql);
        return value;
    }

    @Override
    public Date getMaxBirth(Connection conn) {
        String sql="select max(birth) from customers";
        return getValue(conn, sql);
    }
}

4. Junit包：进行验证

创建方法如下，选择好需要验证的类，如下

public class CustomerDaoImplTest {

    private CustomerDaoImpl dao=new CustomerDaoImpl();

    @Test
    public void testinsert() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
            Date parse = sdf.parse("2000-09-16 18:56:10");
            Customer customer = new Customer(1, "董策典", "1355766867@qq.com", new java.sql.Date(parse.getTime()));
            dao.insert(conn,customer);
            System.out.println("添加成功");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Test
    public void deleteById() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            dao.deleteById(conn,23);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn);
        }
    }

    @Test
    public void update() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnectionDruid();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
            Date parse = sdf.parse("2001-06-13 5:13:50");
            Customer customer = new Customer(27, "李憨批", "250@qq.com", new java.sql.Date(parse.getTime()));
            dao.update(conn,customer);
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ParseException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn);
        }
    }

    @Test
    public void getCustomerById() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            Customer customer = dao.getCustomerById(conn, 27);
            System.out.println(customer);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn);
        }
    }

    @Test
    public void getAll() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnectionDruid();
            for (Customer customer : dao.getAll(conn)) {
                System.out.println(customer);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn);
        }
    }

    @Test
    public void getCountTest() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnectionDruid();
            long count = dao.getCount(conn);
            System.out.println(count);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn);
        }
    }

    @Test
    public void getMaxBirth() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            Date maxBirth = dao.getMaxBirth(conn);
            System.out.println(maxBirth);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn);
        }
    }
}

2.3、JDBCUtil包

public class JDBCUtils {
    /*
    *@description: 获取数据库连接
    *@author: dongcedian
    *@date:2021/9/27
    */
    public static Connection getConnection() throws Exception {
        InputStream resource = ClassLoader.getSystemClassLoader().getResourceAsStream("connectProperties.properties");
        Properties properties = new Properties();
        properties.load(resource);
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driver = properties.getProperty("driver");
        Class.forName(driver);
        Connection conn = DriverManager.getConnection(url, user, password);
        return conn;
    }

    /*
    *@description: 关闭操作
    *@author: dongcedian
    *@date:2021/9/27
    */
    public static void closeResource(Connection conn){
        DbUtils.closeQuietly(conn);
    }
    public static void closeResource(Connection conn, Statement pre){
        DbUtils.closeQuietly(conn);
        DbUtils.closeQuietly(pre);
    }

    public static void closeResource(Connection conn, Statement pre, ResultSet res){
        DbUtils.closeQuietly(conn);
        DbUtils.closeQuietly(pre);
        DbUtils.closeQuietly(res);
    }


    //使用Druid获取一个数据库连接池
    private static DataSource source;
    static {
        try {
            Properties pros = new Properties();
            InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druidProperties.properties");
            pros.load(is);
            source = DruidDataSourceFactory.createDataSource(pros);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnectionDruid() throws SQLException {
        return source.getConnection();
    }
}

其中涉及到两个配置文件的位置及内容

1. connectProperties.properties

user=root
password=root
url=jdbc:mysql://localhost:3306/test1?rewriteBatchedStatements=true
driver=com.mysql.cj.jdbc.Driver

2. druidProperties.properties

url=jdbc:mysql://localhost:3306/test1
username=root
password=root
driverClassName=com.mysql.cj.jdbc.Driver
initialSize=10
maxActive=10

3、dbUtils jar包

链接：https://pan.baidu.com/s/1GJzgUNAuiu9Pk0e_ldmK4g
提取码：hhxd

如果想了解如何迭代的请看我的博客
JDBC上
JDBC下