JDBC模板

最近刚学完 JDBC，通博客来总结一下。这里采用的是 Druid 连接池，以及调用了第三方的 jar 包。

• 调用的 jar 包：
  

• JDBC 项目结构
  

• 整体结构
  

• 代码如下

• 配置数据库加载文件（放在 src 目录下）

• 【Druid.properties】

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/userinfo?useUnicode=true&characterEncoding=utf8
username=root
password=(填写自己的数据库密码)
#初始化连接
initialSize=10
#最大连接数量
maxActive=30
#最小空闲连接
minIdle=5
#超时等待时间以毫秒为单位
maxWait=5000

• 【JDBCutil.java】配置数据库连接池

package com.OY.online.jdbc2.utilTest;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.apache.commons.dbutils.DbUtils;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * 连接Druid数据连接池
*@Author OY
*@Date 2020/5/19
*@Time 11:03
*@Since version-1.8
*/
public class JDBCutil {
    private static DataSource source;
    static{
        try {
            Properties prop = new Properties();
            InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("Druid.properties");
            prop.load(is);
            source = DruidDataSourceFactory.createDataSource(prop);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static Connection getConection() throws SQLException {

        Connection conn = source.getConnection();
        return conn;
    }
    //关闭数据库连接
    public static void closeConection(Connection conn, Statement ps, ResultSet rs){
        DbUtils.closeQuietly(conn);
        DbUtils.closeQuietly(ps);
        DbUtils.closeQuietly(rs);
    }
}

• 在 Bean 目录下创建【Customers.java】

package com.OY.online.jdbc.bean;

import java.sql.Date;

/**
*@Author OY
*@Date 2020/5/19
*@Time 10:11
*@Since version-1.8
*/
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 +
                '}';
    }
}

• 【BaseDAO.java】

package com.OY.online.jdbc.DAO;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

/**
*@Author OY
*@Date 2020/5/19
*@Time 10:14
*@Since version-1.8
*/
public abstract class BaseDAO<T> {
   private QueryRunner runner = new QueryRunner();
   private Class<T> clazz = null;
    {
        Type genericSuperclass = this.getClass().getGenericSuperclass();
        ParameterizedType param = (ParameterizedType) genericSuperclass;
        Type[] typeArguments = param.getActualTypeArguments();
        clazz =(Class<T>) typeArguments[0];
    }
    /**
     *通用的增删改
     */
    public int Update(Connection conn, String sql , Object...args){
        int update = 0;
        try {
            update = runner.update(conn, sql, args);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return update;
    }
    /**
     * 获取一条数据
     */
    public T getConnection(Connection conn, String sql , Object...args){
        T query = null;
        try {
            query = runner.query(conn, sql, new BeanHandler<T>(clazz),args);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return query;
    }
    /**
     * 获取多条数据
     */
    public List<T> getConnectionList(Connection conn, String sql , Object...args){
        List<T> list = null;
        try {
            list = runner.query(conn, sql, new BeanListHandler<T>(clazz), args);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }
    /**
     * 获取数据库特殊值：例如count(*)、Max()
     */
    public Object getValue(Connection conn, String sql, Object...args){
        Object value = null;
        try {
            value = runner.query(conn, sql, new ScalarHandler(), args);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return value;
    }
}

*【CustomerDAO.java】

package com.OY.online.jdbc.DAO;

import com.OY.online.jdbc.bean.Customer;

import java.sql.Connection;
import java.util.List;

/**
*@Author OY
*@Date 2020/5/19
*@Time 10:31
*@Since version-1.8
*/
public interface CustomerDAO {
    /**
     * 将cust对象添加到数据库中
     */
    void insert(Connection conn, Customer cust);

    /**
     * 针对指定的id，删除表中的一条记录
     */
    void deleteByID(Connection conn, int id);
    /**
     * 针对内存中的cust对象，去修改数据表中指定的记录
     */
    void update(Connection conn, Customer cust);
    /**
     * 针对指定的id查询得到对应的Customer对象
     */
    Customer getConnectionById(Connection conn, int id);
    /**
     * 查询表中的所有记录构成的集合
     */
    List<Customer> getAll(Connection conn);
    /**
     *  返回数据表中的数据的条目数
     */
    Long getCount(Connection conn);
}

• 【CustomerDAOImpI.java】

package com.OY.online.jdbc.DAO;

import com.OY.online.jdbc.bean.Customer;

import java.sql.Connection;
import java.util.List;

public class CustomerDAOImpI 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 getConnectionById(Connection conn, int id) {
       String sql = "select id, name, email, birth from customers where id = ?";
//        String sql = "select id,name,email,birth from customers where id = ?";
        Customer cust = getConnection(conn, sql, id);
        return cust;
    }

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

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

• 测试【CustomerDAOImpITest.java】

package com.OY.online.jdbc.util;

import com.OY.online.jdbc.DAO.CustomerDAOImpI;
import com.OY.online.jdbc.bean.Customer;
import com.OY.online.jdbc2.utilTest.JDBCutil;
import org.junit.Test;

import java.sql.Connection;
import java.sql.Date;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.List;

import static org.junit.Assert.*;

public class CustomerDAOImpITest {
   private CustomerDAOImpI custs = new CustomerDAOImpI();
    @Test
    public void insert() {
        Connection conn = null;
        try {
            conn = JDBCutil.getConection();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            java.util.Date date = sdf.parse("2001-7-8");
            Customer cust = new Customer(1, "莫林", "molin@126.com", new Date(date.getTime()));
            custs.insert(conn, cust);
            System.out.println("添加成功");
        } catch (Exception throwables) {
            throwables.printStackTrace();
        }  finally {
            JDBCutil.closeConection(conn,null,null);
        }
    }

    @Test
    public void deleteByID() {
        Connection conn = null;
        try {
            conn = JDBCutil.getConection();
            custs.deleteByID(conn,26);
            System.out.println("删除成功");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCutil.closeConection(conn, null,null);
        }
    }

    @Test
    public void update() {
        Connection conn = null;
        try {
            conn = JDBCutil.getConection();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCutil.closeConection(conn, null,null);
        }
    }

    @Test
    public void getConnectionById() {
        Connection conn = null;
        try {
            conn = JDBCutil.getConection();
            Customer cust = custs.getConnectionById(conn, 27);
            System.out.println(cust);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCutil.closeConection(conn, null,null);
        }
    }

    @Test
    public void getAll() {
        Connection conn = null;
        try {
            conn = JDBCutil.getConection();
            List<Customer> list = custs.getAll(conn);
            list.forEach(System.out::println);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCutil.closeConection(conn, null,null);
        }
    }

    @Test
    public void getCount() {
        Connection conn = null;
        try {
            conn = JDBCutil.getConection();
            Long count = custs.getCount(conn);
            System.out.println("数据库条目数为"+count);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCutil.closeConection(conn, null,null);
        }
    }
}