JDBC模板（二）

这篇没有采用数据库连接池，如需要数据库连接池，可以参考我上一篇 JDBC 模板

JDBC 项目结构：

整体结构

代码示例：

• 配置数据库加载文件
• 同时在项目工程下创建 bin 目录来存在第三方 jar 包
  
• 导入第三方 jar 包【连接数据库】
  

user=root
password=【这里填写自己的数据库连接密码】
url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
driveClass=com.mysql.jdbc.Driver

• 【JDBCUtils】操作数据库的工具类

package com.oy.online.JDBC1.util;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

/**
*@Description 操作数据库的工具类
*@Author OY
*@Date 2020/5/30
*@Time 17:08
*/
public class JDBCUtils {
    /**
    *@Description 获取数据库连接
    *@Param
    *@Return
    *@Author OY
    *@Date 2020/5/30
    *@Time 17:09
    */
    public static Connection getConnection() throws Exception {
        //1.读取配置文件中的4个基本信息
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");

        Properties pros = new Properties();
        pros.load(is);

        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String url = pros.getProperty("url");
        String driveClass = pros.getProperty("driveClass");

        //加载驱动
        Class.forName(driveClass);

        // 3.获取连接
        Connection conn = DriverManager.getConnection(url, user, password);
        return conn;
    }
    /**
    *@Description 关闭资源操作
    *@Param [conn, ps]
    *@Return void
    *@Author OY
    *@Date 2020/5/30
    *@Time 17:21
    */
    public static void closeResource(Connection conn, Statement ps){
        try {
            if(ps != null){
                ps.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(conn != null){
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void closeResource(Connection conn,Statement ps,ResultSet rs){
        try {
            if(ps != null){
                ps.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(conn != null){
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(rs != null){
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

• 【Customers】

package com.oy.online.JDBC1.bean;

import java.sql.Date;

/**
*@Description
*@Author OY
*@Date 2020/5/30
*@Time 17:23
*/
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 getEamil() {
        return email;
    }

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

    public Date getBirth() {
        return birth;
    }

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

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

• 【BaseDAO】

ackage com.oy.online.JDBC1.DAO;


import com.oy.online.JDBC1.util.JDBCUtils;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @Description 封装针对于数据表的通用的操作
 * @Author OY
 * @Date 2020/5/30
 * @Time 17:26
 */
public abstract class BaseDAO {
    /**
     * @Description 通用的增删改
     * @Param [conn, sql, args]
     * @Return int
     * @Author OY
     * @Date 2020/5/30
     * @Time 17:31
     */
    public int update(Connection conn, String sql, Object... args) {
        PreparedStatement ps = null;
        try {
            // 1.预编译sql语句，返回preparedStatement的实例
            ps = conn.prepareStatement(sql);
            // 2.填充占位符
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            //3.执行
            return ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.closeResource(null, ps);
        }
        return 0;
    }

    /**
     * @Description 查询数据表中的一条记录
     * @Param [conn, clazz, sql, args]
     * @Return T
     * @Author OY
     * @Date 2020/5/30
     * @Time 17:44
     */
    public <T> T getInstance(Connection conn, Class<T> clazz, String sql, Object... args) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            //2.填充占位符
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            // 3.执行
            rs = ps.executeQuery();
            // 获取结果集的元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            // 获取结果集中的列数
            int columnCount = rsmd.getColumnCount();
            if (rs.next()) {
                T t = clazz.newInstance();
                for (int i = 0; i < columnCount; i++) {
                    // 获取列值
                    Object colunmnValue = rs.getObject(i + 1);
                    // 获取每个列的列名
                    String columnLabel = rsmd.getColumnLabel(i + 1);

                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t,colunmnValue);
                }
                return t;
            }
        } catch (Exception throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.closeResource(null, ps, rs);
        }
        return null;
    }

    /**
     * @Description 查询数据表中的多条数据
     * @Param [conn, clazz, sql, args]
     * @Return java.util.List<T>
     * @Author OY
     * @Date 2020/5/30
     * @Time 17:46
     */
    public <T> List<T> getForList(Connection conn, Class<T> clazz, String sql, Object...args) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }

            rs = ps.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();

            // 创建集合对象
            ArrayList<T> list = new ArrayList<>();
            while (rs.next()) {
                T t = clazz.newInstance();
                for (int i = 0; i < columnCount; i++) {
                    Object columValue = rs.getObject(i + 1);
                    String columnLabel = rsmd.getColumnLabel(i + 1);

                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, columValue);
                }
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(null, ps, rs);
        }
        return null;
    }
    /**
    *@Description 用于查询特殊值
    *@Param [conn, sql, args]
    *@Return E
    *@Author OY
    *@Date 2020/5/30
    *@Time 18:02
    */
    public <E> E getValue(Connection conn, String sql, Object... args) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            if (rs.next()) {
                return (E) rs.getObject(1);
            }
        } catch (Exception throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.closeResource(null, ps, rs);
        }
        return null;
    }
}

• 【CustomerDAO.java】

package com.oy.online.JDBC1.DAO;

import com.oy.online.JDBC1.bean.Customer;

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

/**
*@Description
*@Author OY
*@Date 2020/5/30
*@Time 18:03
*/
public interface CustomerDAO {
    /**
    *@Description 将cust对象添加到数据库中
    *@Param [conn, cust]
    *@Return void
    *@Author OY
    *@Date 2020/5/30
    *@Time 18:05
    */
    void insert(Connection conn, Customer cust);

    /**
    *@Description 针对指定的id，删除表中的一条记录
    *@Param [conn, id]
    *@Return void
    *@Author OY
    *@Date 2020/5/30
    *@Time 18:05
    */
    void deletById(Connection conn, int id);
    /**
    *@Description 针对内存中的cust对象，去修改数据表中指定的记录
    *@Param [conn, cust]
    *@Return void
    *@Author OY
    *@Date 2020/5/30
    *@Time 18:07
    */
    void update(Connection conn, Customer cust);

    /**
    *@Description 指定的id查询得到指定的customer对象
    *@Param [conn, id]
    *@Return com.oy.online.JDBC1.bean.Customer
    *@Author OY
    *@Date 2020/5/30
    *@Time 18:11
    */
    Customer getCustomerById(Connection conn, int id);

    /**
    *@Description 查询表中的所有数据构成的集合
    *@Param [conn]
    *@Return java.util.List<com.oy.online.JDBC1.bean.Customer>
    *@Author OY
    *@Date 2020/5/30
    *@Time 18:12
    */
    List<Customer> getAll(Connection conn);

    /**
    *@Description 返回数据表中的数据的条目数
    *@Param [conn]
    *@Return java.lang.Long
    *@Author OY
    *@Date 2020/5/30
    *@Time 18:14
    */
    Long getCount(Connection conn);

    /**
    *@Description 返回数据表中最大的生日
    *@Param [conn]
    *@Return java.sql.Date
    *@Author OY
    *@Date 2020/5/30
    *@Time 18:15
    */
    Date getMaxBirth(Connection conn);
}

• 【CustomerDAOImpI.java】

package com.oy.online.JDBC1.DAO;

import com.oy.online.JDBC1.bean.Customer;

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

public class CustomerDAOImpI extends BaseDAO 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.getEamil(),cust.getBirth());
    }

    @Override
    public void deletById(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.getEamil(),cust.getBirth(),cust.getId());
    }

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

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

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

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

测试【text】

• 测试数据库连接

package com.oy.online.JDBC1.text;

import com.oy.online.JDBC1.util.JDBCUtils;
import org.junit.Test;

import java.sql.Connection;

/**
*@Description 测试数据库是否连接成功
*@Author OY
*@Date 2020/5/31
*@Time 14:46
*/
public class JDBCUtilTest {
    @Test
    public void test() throws Exception {
        for(int i = 0; i < 100; i++){
            Connection conn = JDBCUtils.getConnection();
            System.out.println(conn);
            JDBCUtils.closeResource(conn,null,null);
        }
    }

}

• 测试数据库增删改查连接

package com.oy.online.JDBC1.text;

import com.oy.online.JDBC1.DAO.CustomerDAOImpI;
import com.oy.online.JDBC1.bean.Customer;
import com.oy.online.JDBC1.util.JDBCUtils;
import org.junit.Test;

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


public class CustomerDAOTest {
    private CustomerDAOImpI custs = new CustomerDAOImpI();

    @Test
    public void insert() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            Date date = sdf.parse("2001-8-9");
            Customer cust = new Customer(1, "小明", "xiaoming@123.com", new java.sql.Date(date.getTime()));
            custs.insert(conn,cust);
            System.out.println("添加成功");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
        }
        JDBCUtils.closeResource(conn,null,null);
    }

    @Test
    public void deletById() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            custs.deletById(conn,30);
            System.out.println("删除成功");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null,null);
        }
    }

    @Test
    public void update() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            Date date = sdf.parse("2000-9-12");
            Customer cust = new Customer(31, "小白", "xiaobai@126.com",new java.sql.Date(date.getTime()));
            custs.update(conn, cust);
            System.out.println("修改成功");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn,null,null);
        }
    }

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

    @Test
    public void getAll() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            List<Customer> list = custs.getAll(conn);
            list.forEach(System.out::println);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn,null,null);
        }
    }

    @Test
    public void getCount() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            Long count = custs.getCount(conn);
            System.out.println(count);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null, null);
        }
    }

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