java与数据库oracle连接学习之jdbc（6）改进读取信息的方法

公共类

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);
    }
}

改进读取的公共类

package com.librarySystem;

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

/**
 * 通用的DAO，可以对任意表做增删改
 */
public     class CommonDao<T> extends JDBCUTILS {
    /**
     * 对任意表做增删改,根据增删改的字符串或数字不同操作
     * @return
     */

    public static int executeUpdate(String sql,Object ...params){
        Connection conn =null;
        PreparedStatement preparedStatement =null;
        int i=0;
        try {
            //获取连接
            conn = getConnection();
            //创建语句对象
            preparedStatement = conn.prepareStatement(sql);
            if(params!=null && params.length>0){//判断需要占位符
                for(int j=0;j<params.length;j++){
                    //设置占位符
                    preparedStatement.setObject(j+1,params[j]);
                }
            }
             i = preparedStatement.executeUpdate();//成功为1
        }catch(SQLException e){
            e.printStackTrace();
        }finally {//释放资源
            close(preparedStatement,conn);
        }
        return i;
    }

    public List<Map<String,Object>> queryBeenList(String sql, Object ...paramas){
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        List<Map<String,Object>>lst =new ArrayList<>();
        Map<String,Object>map =null;
        try{
            //获取连接
            connection = getConnection();
            //创建语句对象
            preparedStatement = connection.prepareStatement(sql);
            if(paramas!=null&&paramas.length>0){//需要设置占位符
                for(int i=0;i<paramas.length;i++){
                    preparedStatement.setObject(i+1,paramas[i]);
                }
            }
            //执行SQL语句
            resultSet = preparedStatement.executeQuery();
            //处理结果集
            //通过metaData获取元数据个数
            ResultSetMetaData resultSetMetaData =resultSet.getMetaData();
            int columnCont = resultSetMetaData.getColumnCount();
            String[]columnNames =new String[columnCont];//元数据名称数组
            for(int i=0;i<columnCont;i++){
                columnNames[i]=resultSetMetaData.getColumnName(i+1);
            }
            while(resultSet.next()){
                map =new HashMap<>();
                for(int i=0;i<columnCont;i++){
                    String columnName = columnNames[i];
                    map.put(columnNames[i],resultSet.getObject(columnName));
                }
                lst.add(map);
            }
        }
        catch(SQLException e){
            e.printStackTrace();
        }
        finally {
           close(resultSet,preparedStatement,connection);
        }
        return lst;
    }

    public Map<String,Object> queryBeen(String sql,Object ...params){
       List< Map<String,Object>>lst= queryBeenList(sql,params);
       if(lst.size()>0){
           return lst.get(0);
       }
       return null;
    }

}

实验类

//学习链接： https://www.bilibili.com/video/BV14E411Q7wJ?p=2
package com.librarySystem;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

class Reader {
    private String rno;
    private String rname;
    private String rsex;
    private int rage;
    private String rboss;
    private String raddress;

    public String getRno() {
        return rno;
    }

    public String getRname() {
        return rname;
    }

    public String getRsex() {
        return rsex;
    }

    public int getRage() {
        return rage;
    }

    public String getRaddress() {
        return raddress;
    }

    public String getRboss() {
        return rboss;
    }

    public void setRno(String rno) {
        this.rno = rno;
    }

    public void setRname(String rname) {
        this.rname = rname;
    }

    public void setRsex(String rsex) {
        this.rsex = rsex;
    }

    public void setRage(int rage) {
        this.rage = rage;
    }

    public void setRboss(String rboss) {
        this.rboss = rboss;
    }

    public void setRaddress(String raddress) {
        this.raddress = raddress;
    }

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

public class ConnnectDatabase extends CommonDao<Reader> {
    /**
     * 查询数据库中资源对象
     *
     * @return 对象集合
     */

    public static int doInsert(Reader reader) {
        String sql = "Insert into reader values('R011','George','男',?，null,?)";
        return executeUpdate(sql, reader.getRage(), reader.getRaddress());
    }

    public static int doDelete(String rno) {
        String sql = "Delete from reader where rno=?";
        return executeUpdate(sql, rno);
    }

    public static int doUpdate(Reader reader) {
        String sql = "Update reader set rname=?,rage=? where rno=?";
        return executeUpdate(sql, reader.getRname(), reader.getRage(), reader.getRno());
    }

    public static void main(String args[])
    {
        Map<String,Object>map = new ConnnectDatabase().queryByRno("R001");
        System.out.println(map);
    }


    public List<Map<String,Object>> queryAll() {
        String sql = "Select * from reader";
        return queryBeenList(sql);
    }

    public Map<String,Object>queryByRno(String rno) {
        String sql = "Select * from reader where rno=?";
        return queryBeen(sql, rno);
    }
}



/*
遍历列表
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);});
 */