JDBC 单例模式 封装

JDBC 单例模式 封装

分开方法执行和 sql语句的传入

封装一个工具类，将执行语句封装起来
在JDBCManager.class中，使用了单例模式

import java.sql.*;

/**
 * 设计一个数据库的访问工具类，采用单例模式
 */
public class JDBCManager {
    //数据库的用户名
    private final String USER ="root";
    //数据库密码
    private final String PASSWORD="19970909";
    //数据库的访问lianjie
    private static final String URL="jdbc:mysql://localhost:3306/mydb?characterEncoding=utf8&useSSL=false";
    //数据库的驱动
    private final String DRIVER="com.mysql.cj.jdbc.Driver";
    //获取JDBCManager 的对象
    private static  JDBCManager instance;
    //数据库的执行空间
    private Statement statement;
    //数据库的连接
    private Connection connection;

    /**
     * 构造方法私有化
     */
    private JDBCManager(){
        try {
            Class.forName(DRIVER);
            System.out.println("注册MySQL驱动成功");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

    }

    /**
     * 单例模式构建访问数据库的访问对象
     * @return
     */
    public static  JDBCManager getInstance(){
        if(instance == null){
            instance = new JDBCManager();
        }
        return instance;
    }

    /**
     * 获得数据库的连接
     * @return
     */
    public Connection getConnection()throws SQLException {
        connection=DriverManager.getConnection(URL,USER,PASSWORD);
        return connection;
    }

    /**
     * 执行静态的SQL语句
     * @param sql
     * @return 返回一个整型，整型值为数据库受影响的行数
     * @throws SQLException
     */
    public  int executeSQLByStatement(String sql) throws SQLException{
        statement=connection.createStatement();
        return  statement.executeUpdate(sql);
    }

    /**
     * 执行DML语句，例如删除，插入，修改
     * insert into student()
     * @param sql
     * @param columnName 列名称
     * @return
     * @throws SQLException
     */
    public int executeSQLBYStatement(String sql,String[] columnName)throws  SQLException{
        statement =connection.createStatement();
        return  statement.executeUpdate(sql,columnName);
    }

    /**
     * 查询返回的结果，可能包含一个或者多个结果
     *      返回值                     参数
     * 方法  int        executeUpdate​(String sql, int[] columnIndexes)
     * @param sql
     * @return
     * @throws SQLException
     */

    public ResultSet executeQuetyBYStatement(String sql)throws  SQLException{
        statement =connection.createStatement();
        return statement.executeQuery(sql);


    }

    /**
     *将关闭流放入方法中减少代码重复量
     */
    public void closeAll(){
        try {
            if(statement!=null) {
                statement.close();
            }
            if(connection!=null){
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

TestStudent.class类 用于测试封装的执行方法类JDBCManager.class
此类中具体写入sql语句
并且在main方法里将sql语句的value真正传入

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class TestStudent {

    private  JDBCManager manager;

    public TestStudent(){
        manager =JDBCManager.getInstance();
    }

    /**
     * 执行删除语句的方法
     */
    public void delete(){
        String sql ="delete from student where id =2";
        try {
            manager.getConnection();
            int row_result = manager.executeSQLByStatement(sql);
            //判断受影响的行数，若不等于0则删除成功
            if(row_result>0){
                System.out.println("删除学生记录成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            manager.closeAll();
        }
    }

    /**
     * 执行插入语句
     * @param val 传入的参数是一个Object类型的数组，在调用insert()方法的时候就需要把参数传入
     *            以便以执行SQL语句
     */
    public void insert(Object[] val){
        //需要外传值来补全SQL语句
        String sql = "insert into student(name,age)values('"+val[0]+"',"+val[1]+")";
        try {
            manager.getConnection();
            //这里的String 是上面SQL中的
            int row_count = manager.executeSQLBYStatement(sql,new String[]{"name","age"});
            if(row_count>0){
                System.out.println("添加学生成功");
            }
            System.out.println("--->"+sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * Statement 是静态的，为了实现能够将SQL语句写成可变的，传入Object[] 数组 次数在中写入 fielsName 对应的 value
     * @param val
     */
    public void update(Object[] val){
        String sql = "update student set name = '"+val[0]+"' ,age="+val[1]+" where id="+val[2]+" ";
        System.out.println("--->"+sql);
        try {
            manager.getConnection();
            int row_count = manager.executeSQLBYStatement(sql,new String[]{"name","age","id"});
            if(row_count>0){
                System.out.println("修改学生成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 查询语句
     */
    public void  querySingleResult(){
        String sql ="select name,age from student where id=5";
        System.out.println("--->"+sql);
        try {
            manager.getConnection();
            //返回查询的结果集
            ResultSet resultSet =manager.executeQuetyBYStatement(sql);
            while (resultSet.next()){
                System.out.println("-->"+resultSet.getString("name"));
                System.out.println("-->"+resultSet.getInt("age"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
    /**
     *  查询单个结果
     *  创建一个响应的类来接受查询到的返回值，并且输出这个类的引用
     */

    public Student queryOneResult(){
        Student stu = new Student();
        String sql ="select name,age from where id=5";
        System.out.println("--->"+sql);
        ResultSet resultSet=null;
        try {
            manager.getConnection();
            resultSet = manager.executeQuetyBYStatement(sql);
            if(resultSet.next()){
                stu.setName(resultSet.getString("naame"));
                stu.setAge(resultSet.getInt("age"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            manager.closeAll();
        }
       return stu;
    }

    /**
     * 查询多个结果
     * @return List<Student> list
     */

    public List<Student> queryMoreResult(){
        String sql = "select id, name ,age from student";
        List<Student> list = new ArrayList<>();
        ResultSet resultset =null;
        try {
            manager.getConnection();
            resultset =manager.executeQuetyBYStatement(sql);
            while(resultset.next()){
                Student student =new Student();
                student.setAge(resultset.getInt("age"));
                student.setId(resultset.getInt("id"));
                student.setName(resultset.getString("name"));
                list.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            manager.closeAll();
        }
   return list;
    }
    public static void main(String[] args) {
        //new TestStudent().insert(new Object[]{"zhangsan", 45});
        // new TestStudent().update(new Object[]{"lisi", 66, 5});
        //new TestStudent().querySingleResult();
        // Student student = new TestStudent().queryOneResult();
        // System.out.println(student);
        System.out.println(new TestStudent().queryMoreResult());

    }
}

Student类

public class Student {
    private String name;
    private int age;
    private  int id;

    public Student() {

    }

    public Student(String name, int age, int id) {
        this.name = name;
        this.age = age;
        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 int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    @Override
    public String toString() {
        return "Student{" +
                "name='" + name + '\'' +
                ", age=" + age +
                ", id=" + id +
                '}';
    }
}