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

JDBC 项目结构：
项目结构
整体结构
整体结构
代码示例：

  • 配置数据库加载文件
  • 同时在项目工程下创建 bin 目录来存在第三方 jar 包
  • 导入第三方 jar 包【连接数据库】
    第三方jar包
1
2
3
4
user=root
password=【这里填写自己的数据库连接密码】
url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
driveClass=com.mysql.jdbc.Driver
  • 【JDBCUtils】操作数据库的工具类
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
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】
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
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】
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
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】
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
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】
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
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】

  • 测试数据库连接
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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);
        }
    }

}

  • 测试数据库增删改查连接
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
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);
        }
    }
}