java与数据库oracle连接学习之jdbc（4)做一个工具类，减少代码复杂度

工具类

package com.librarySystem;

import java.sql.*;

/**
 * JDBC的工具类：
 * 加载驱动：只需要加载一次
 * 建立连接：
 * 释放资源：
 */

public class JDBCUTILS {
    private static final String DRIVER = "oracle.jdbc.OracleDriver";
    // 主机地址 连接本机 localhost  或者127.0.0.1
    // 端口号 Oracle 数据库默认端口号 1521
    // 实例名 安装全的是orcl，没有安装全的是XE
    private static String URL = "jdbc:oracle:thin:@localhost:1521:orcl";   // jdbc:oracle:thin: @主机地址 :  端口号 : 实例名
    private static String USER = "cc";
    private static String PASSWORD = "ccpassword";
    static {//因为驱动只需要加载一次，所以在静态语句中进行
        try {
            Class.forName(DRIVER);
        }catch(Exception e){
            e.printStackTrace();
        }
    }

    /**
     *建立与数据库的连接
     * @return 连接好的连接
     */
    public static Connection getConnection(){
        try {
            Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
           return  connection;
        }catch(Exception ex){
            ex.printStackTrace();
        }
        return null;
    }

    /**
     * 释放资源
     * @param resultSet 结果集
     * @param statement 语句对象
     * @param connection 连接
     */
    public static void close(ResultSet resultSet, Statement statement ,Connection connection){//查询时
        try
        {
            if (resultSet != null && !resultSet.isClosed())
                resultSet.close();
        }
        catch(SQLException ex)
        {
            ex.printStackTrace();
        }
        finally
        {
            try
            {
                if (statement != null && !statement.isClosed())
                    statement.close();
            }
            catch (SQLException ex)
            {
                ex.printStackTrace();
            }
            finally
            {
                try
                {
                    if (connection != null && !connection.isClosed())
                        connection.close();
                }
                catch(SQLException ex)
                {
                    ex.printStackTrace();;
                }
            }
        }
    }

    /**
     * 释放资源
     * @param statement 语句对象
     * @param connection 连接
     */
    public static void close(Statement statement ,Connection connection){//增删改
        close(null,statement,connection);
    }
}

主类

//学习链接： https://www.bilibili.com/video/BV14E411Q7wJ?p=2
package com.librarySystem;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

class Reader{
    public String rno;
    public String rname;
    public String rsex;
    public int rage;
    public String rboss;
    public String raddress;
    public String toString(){
        return rno+" "+rname+" "+rsex+" "+rage+" "+rboss+" "+raddress;
    }
}

public class ConnnectDatabase {
    /**
     * 查询数据库中资源对象
     * @return 对象集合
     */
    public static List<Reader>  queryALL(){
        Connection conn=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet = null;
        List<Reader>readers = new ArrayList<>();
        try{
            conn = JDBCUTILS.getConnection();
            String sql ="SELECT * FROM Reader";
            preparedStatement = conn.prepareStatement(sql);
            resultSet  = preparedStatement.executeQuery();
            while(resultSet.next()){
                Reader reader= new Reader();
                reader.rno = resultSet.getString("RNO");
                reader.rname = resultSet.getString("RNAME");
                reader.rsex = resultSet.getString("RSEX");
                reader.rage = resultSet.getInt("RAGE");
                reader.rboss = resultSet.getString("RBOSS");
                reader.raddress = resultSet.getString("RADDRESS");
                readers.add(reader);
            }
        }
        catch (Exception e){
            e.printStackTrace();;
        }
        finally {
            JDBCUTILS.close(resultSet,preparedStatement,conn);
        }
        return readers;
    }

    public static void add() {
        Connection conn=null;
        PreparedStatement preparedStatement=null;
        try{
            conn = JDBCUTILS.getConnection();
            //防止插入注入,使用占位符?
            String sql = "Insert into reader values('R011',?,?,?,?,?)";
            preparedStatement =conn.prepareStatement(sql);
            //根据位置以及类型设置占位符
            preparedStatement.setString(1,"王尼玛");
            preparedStatement.setString(2,"男");
            preparedStatement.setDouble(3,22);
            preparedStatement.setString(4,"李四");
            preparedStatement.setString(5,"404");
            preparedStatement.executeUpdate();
        }
        catch(Exception e){
            e.printStackTrace();
        }
        finally{
            JDBCUTILS.close(preparedStatement,conn);
        }
    }

    public static void del() {
        Connection conn=null;
        PreparedStatement preparedStatement=null;
        try{
            conn = JDBCUTILS.getConnection();
            //防止插入注入,使用占位符?
            String sql = "Delete from reader where reader.rname like?";
            preparedStatement =conn.prepareStatement(sql);
            preparedStatement.setString(1,"_尼玛");
            preparedStatement.executeUpdate();
        }catch(Exception e){
            e.printStackTrace();
        }
        finally{
           JDBCUTILS.close(preparedStatement,conn);
        }
    }

    public static void changeValue()  {
        Connection conn=null;
        PreparedStatement preparedStatement=null;
        try{
            conn = JDBCUTILS.getConnection();
            //防止插入注入,使用占位符?
            String sql = "Update  reader  set rname='吴尼玛' where reader.rname=?";
            preparedStatement =conn.prepareStatement(sql);
            preparedStatement.setString(1,"王尼玛");
            preparedStatement.executeUpdate();
        }catch(Exception e){
            e.printStackTrace();
        }finally{
          JDBCUTILS.close(preparedStatement,conn);
        }
    }

    public static Reader querySpecial(String rno) {
        Connection conn=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet = null;
        Reader reader=null;
        try{
            conn = JDBCUTILS.getConnection();
            String sql ="SELECT * FROM Reader where reader.rno=?";
            preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setString(1,rno);
            resultSet  = preparedStatement.executeQuery();
            if(resultSet.next()){
                reader= new Reader();
                reader.rno = resultSet.getString("RNO");
                reader.rname = resultSet.getString("RNAME");
                reader.rsex = resultSet.getString("RSEX");
                reader.rage = resultSet.getInt("RAGE");
                reader.rboss = resultSet.getString("RBOSS");
                reader.raddress = resultSet.getString("RADDRESS");
            }
        }
        catch (Exception e){
            e.printStackTrace();;
        }
        finally {
            JDBCUTILS.close(resultSet,preparedStatement,conn);
        }
        return reader;
    }

    public static void main(String args[]) {

    }
}



/*
遍历列表
List<Reader> lst = queryALL();
        Iterator<Reader>it = lst.iterator();
        String tmp =null;
        while(it.hasNext()){
            tmp = ((Reader)it.next()).rname;
            System.out.println(tmp);
        }

 queryALL().forEach(str->
                {System.out.println(str);});
 */