JDBC封装工具类

JDBC封装工具类

1.封装类

package com.tsccg.util;

import java.sql.*;
import java.util.ResourceBundle;

/**
 * @Author: TSCCG
 * @Date: 2021/08/28 19:42
 */
public class JdbcUtil {
    private static Connection conn = null;
    private static PreparedStatement ps = null;
    private static Statement stmt = null;

    private static String url = null;
    private static String user = null;
    private static String password = null;

    /**
     * 静态代码块，用于注册驱动及初始化各种参数
     */
    static {
        try {
            ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
            String driverName = bundle.getString("driverName");
            url = bundle.getString("url");
            user = bundle.getString("user");
            password = bundle.getString("password");
            //注册驱动
            Class.forName(driverName);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取连接
     * @return 返回Connection对象
     */
    public static Connection getConn() {
        try {
            conn = DriverManager.getConnection(url,user,password);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return conn;
    }

    /**
     * 获取预编译的数据库操作对象
     * @param sql 需要执行的sql语句
     * @return 返回PreparedStatement对象
     */
    public static PreparedStatement getPs(String sql) {
        try {
            ps = getConn().prepareStatement(sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return ps;
    }

    /**
     * 获取数据库操作对象
     * @return 返回Statement对象
     */
    public static Statement getStmt() {
        try {
            stmt = getConn().createStatement();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return stmt;
    }

    /**
     * 关闭所有
     * @param rs 查询结果集对象
     */
    public static void closeAll(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (stmt != null) {
            try {
                stmt.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();
            }
        }
    }

}

2.配置文件

jdbc.properties

driverName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/db_ajax
user=root
password=123456

3.测试

package com.tsccg.util;

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

/**
 * @Author: TSCCG
 * @Date: 2021/08/28 21:24
 */
public class Test {
    public static void main(String[] args) {
        ResultSet rs = null;
        String sql = "select * from city where provinceid = ?";
        PreparedStatement ps = JdbcUtil.getPs(sql);
        try {
            ps.setInt(1,1);
            rs = ps.executeQuery();
            while (rs.next()) {
                Integer id = rs.getInt("id");
                String name = rs.getString("name");
                int provinceid = rs.getInt("provinceid");
                System.out.println(id + " " + name + " " + provinceid);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtil.closeAll(rs);
        }
    }
}

结果：

1 石家庄市 1
2 秦皇岛 1
3 保定市 1
4 张家口 1