JDBC封装管理

• 数据库配置文件的编写

driver = com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3306/jdbc?serverTimezone=Asia/Shanghai&?useUnicode=true&characterEncoding=utf8&useSSL=false
username = root
password = 1234

• 编写公共类

package com.jdbc;

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

public class BaseDao {
    private static String driver;
    private static String url;
    private static String username;
    private static String password;

    // 静态代码块，类加载的时候就初始化
    static {
        Properties properties = new Properties();
        // 通过类加载器读取对应的资源
        InputStream resources = BaseDao.class.getClassLoader().getResourceAsStream("db.properties");
        System.out.println(BaseDao.class.getClassLoader());
        try {
            properties.load(resources);
        } catch (IOException e) {
            e.printStackTrace();
        }
        driver = properties.getProperty("driver");
        url = properties.getProperty("url");
        username = properties.getProperty("username");
        password = properties.getProperty("password");
        System.out.println(driver);
        System.out.println(url);
        System.out.println(username);
        System.out.println(password);
    }

    // 获取数据库的连接
    public static Connection getConnection(){
        Connection connection = null;
        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return connection;
    }
    
    //编写查询公共方法
    public static ResultSet executeFind(Connection connection ,String sql ,Object[] params,ResultSet resultSet ,PreparedStatement preparedStatement) throws SQLException {
        preparedStatement = connection.prepareStatement(sql);
        for (int i = 0; i < params.length; i++) {
            // setObject占位符，从1开始
            preparedStatement.setObject(i+1,params[i]);
        }
        resultSet = preparedStatement.executeQuery();
        return resultSet;
    }
    //编写增删改的公共方法
    public static int executeCRUD(Connection connection ,String sql ,Object[] params,PreparedStatement preparedStatement ) throws SQLException {
        preparedStatement = connection.prepareStatement(sql);
        for (int i = 0; i < params.length; i++) {
            // setObject占位符，从1开始
            preparedStatement.setObject(i+1,params[i]);
        }
        int updateRows = preparedStatement.executeUpdate();
        return updateRows;
    }

    // 资源释放方法
    public static boolean closeResource(Connection connection,PreparedStatement preparedStatement,ResultSet resultSet ){
        boolean flag = true;
        if(resultSet != null){
            try {
                resultSet.close();
                // GC回收
                resultSet = null;
            } catch (SQLException e) {
                flag = false;
                e.printStackTrace();
            }
        }
        if(preparedStatement != null){
            try {
                preparedStatement.close();
                // GC回收
                preparedStatement = null;
            } catch (SQLException e) {
                flag = false;
                e.printStackTrace();
            }
        }
        if(connection != null){
            try {
                connection.close();
                // GC回收
                connection = null;
            } catch (SQLException e) {
                flag = false;
                e.printStackTrace();
            }
        }
        return flag;
    }
}

• 公共类的使用

import com.pojo.User;

import java.sql.Connection;

/**
 * 定义查询接口
 */
public interface UserDao {

    // 得到要登录的用户
    public User getLoginUser(Connection connection , String userCode);
}

package com.dao.user;

import com.dao.BaseDao;
import com.pojo.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
 * 调用公用类
 */
public class UserDaoImpl implements UserDao{
    @Override
    public User getLoginUser(Connection connection, String userCode) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        User user = null;
        if(connection != null){
            String sql = "select * from users where user_code = ? ";
            Object[] params = { userCode };
            try {
                resultSet = BaseDao.executeFind(connection, preparedStatement, sql, params);
                if(resultSet.next()){
                    user = new User();
                    user.setId(resultSet.getInt("id"));
                    user.setName(resultSet.getString("name"));
                    user.setUserCode(resultSet.getString("user_code"));
                }
                boolean resource = BaseDao.closeResource(connection, preparedStatement, resultSet);
                System.out.println(resource);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return user;
    }

    public static void main(String[] args) {
        UserDaoImpl userDao = new UserDaoImpl();
        Connection connection = BaseDao.getConnection();
        User user = userDao.getLoginUser(connection, "ad");
        System.out.println(user);
    }
}