【Java】JDBC 封装

说明

对 JDBC 进行简单的封装，方便使用

完整代码：https://pan.bigdataboy.cn/#/s/nMC0

数据库数据

创表

DROP TABLE IF EXISTS user;

CREATE TABLE user
(
    id BIGINT(20) NOT NULL auto_increment COMMENT '主键ID',
    name VARCHAR(30) NULL DEFAULT NULL COMMENT '姓名',
    age INT(11) NULL DEFAULT NULL COMMENT '年龄',
    email VARCHAR(50) NULL DEFAULT NULL COMMENT '邮箱',
    PRIMARY KEY (id)
);

加入数据

INSERT INTO user (id, name, age, email) VALUES
(1, 'Jone', 18, 'test1@bigdataboy.com'),
(2, 'Jack', 20, 'test2@bigdataboy.com'),
(3, 'Tom', 28, 'test3@bigdataboy.com'),
(4, 'Sandy', 21, 'test4@bigdataboy.com'),
(5, 'Billie', 24, 'test5@bigdataboy.com');

构建目录

工具类

连接工具类，封装连接对象，方便获取连接

package cn.bigdataboy.util;

import java.sql.PreparedStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DBConnectionUtil {
    // 创建连接参数
    private static String driver = "com.mysql.jdbc.Driver";
    private static String url = "jdbc:mysql://localhost:3306/jdbctest?characterEncoding=utf8";
    private static String username = "";
    private static String password = "";

    // 获取连接对象
    public static Connection getConnection(){
        Connection  conn = null;

        // 获取连接
        try {
            // 加载驱动
            Class.forName(driver);
            conn = DriverManager.getConnection(url, username, password);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return conn;
    }

    // 关闭全部
    public static void closeAll(ResultSet rs, PreparedStatement ps, Connection conn){

        if (rs != null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        };

        if (ps != null){
            try {
                ps.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        };

        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

}

测试连接工具类

public static void main(String[] args) {
        Connection connection = getConnection();
        System.out.println(connection);
    }

用户实体类

用户实体类的字段需要与数据表字段对应

package cn.bigdataboy.entity;

public class User {
        private int id;
        private String name;
        private int age;
        private String email;

    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 int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getEmail() {
        return email;
    }

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

定义 dao 层接口

dao 层主要功能是提供对数据库数据的操作类

package cn.bigdataboy.dao;

import cn.bigdataboy.entity.User;

public interface UserDao {
    // 通过 id 查询用户
    User getUserById(int id);
}

实现 UserDao 接口

package cn.bigdataboy.dao.impl;

import cn.bigdataboy.dao.UserDao;
import cn.bigdataboy.entity.User;
import cn.bigdataboy.util.DBConnectionUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class UserDaoImpl implements UserDao {

    // 通过 id 查询用户
    public User getUserById(int id) {
        User user = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            // 通过连接工具类 获取连接对象
            conn = DBConnectionUtil.getConnection();
            String sql = "select * from user where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id); // 设置第一个问号的值
            rs = ps.executeQuery(); // 执行SQL语句
            // 判断是否返回到值，多个值需要循环使用 next() 获取
            if (rs.next()){
                // 设置 user 值
                user = new User();
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("name"));
                user.setAge(rs.getInt("age"));
                user.setEmail(rs.getString("email"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 关闭全部
            DBConnectionUtil.closeAll(rs, ps, conn);
        }

        return user;
    }
}

测试实现的 getUserById() 方法

package cn.bigdataboy.dao;

import cn.bigdataboy.dao.impl.UserDaoImpl;
import cn.bigdataboy.entity.User;
import org.junit.Test;

public class TestUserDaoImpl {

    @Test
    public void TestGetUserById(){
        int id = 1;
        UserDaoImpl userDao = new UserDaoImpl();
        User user = userDao.getUserById(id);
        System.out.println(user.getId());
        System.out.println(user.getName());
        System.out.println(user.getAge());
        System.out.println(user.getEmail());
        System.out.println(user.getClass());
    }
}

其他的 修改、添加、删除 数据 都是一样的，完整代码里有