Java链接Oracle

下载驱动，mvn安装驱动

mvn install:install-file -DgroupId=com.oracle -DartifactId=ojdbc6 -Dversion=11.2.4 -Dpackaging=jar -Dfile=/home/zhangsail/.m2/repository/com/oracle/ojdbc6.jar

项目中pom文件引入驱动

        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.4</version>
        </dependency>

Oracle链接类

package com.hdyy.hdcp.drgsapp.service.impl;

import java.lang.reflect.Field;
import java.sql.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class OracleHelp {

    //数据库连接地址
    private static String url="jdbc:oracle:thin:@123.123.123.123:0000:hndicp";
    //用户名
    private static String username= "test";
    //密码
    private static String password ="st";
    //驱动名称
    private static String jdbcName = "oracle.jdbc.driver.OracleDriver";

    /*获取数据库连接 */
    private static Connection getCon(){
        try {
            Class.forName(jdbcName);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        Connection con = null;
        try {
            con = DriverManager.getConnection(url,username,password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return con;
    }

    /*关闭数据库连接*/
    private static void closeCon(Connection con) throws SQLException {
        if (con != null)
            con.close();
    }


//  public static void main(String[] args){
//      try {
//
//          Connection connection = OracleHelp.getCon();
//          PreparedStatement ps = connection.prepareStatement("SELECT * FROM BASE.DEPARTMENTTOBAO2");
//          ResultSet rs = ps.executeQuery();
//          while (rs.next()){
//              String id = rs.getString(1);
//              String name = rs.getString(2);
//              Time time=rs.getTime(7);
//              System.out.println("ID:"+id + " NAME:"+name+" time:"+time);
//          }
//          OracleHelp.closeCon(connection);//关闭数据库
//
//          System.out.println("数据库连接成功");
//      } catch (Exception e) {
//          e.printStackTrace();
//          System.out.println("数据库连接失败");
//      }
//  }
    /////////////////////////////
//
//  public static void main(String[] args){
//      try {
//
//          Connection connection = OracleHelp.getCon();
//          PreparedStatement ps = connection.prepareStatement("SELECT * FROM BASE.DEPARTMENTTOBAO2");
//          ResultSet rs = ps.executeQuery();
//
//          ResultSetMetaData md = rs.getMetaData();
//          int columnCount = md.getColumnCount();
//
//          List<Map<String,Object>> list = new ArrayList();
//          while (rs.next()){
//              Map<String,Object> rowData = new HashMap();
//              String id = rs.getString(1);
//              String name = rs.getString(2);
//              Time time=rs.getTime(7);
//              for (int i = 1; i <= columnCount; i++) {
//
//                  rowData.put(md.getColumnName(i), rs.getObject(i));
//
//              }
//              list.add(rowData);
//          }
//          OracleHelp.closeCon(connection);//关闭数据库
//
//          System.out.println("数据库连接成功"+list);
//      } catch (Exception e) {
//          e.printStackTrace();
//          System.out.println("数据库连接失败");
//      }
//  }

    //"SELECT * FROM BASE.DEPARTMENTTOBAO2"
    public static List<Map<String, Object>> OracleSelectList(String sql){
        List<Map<String,Object>> list = new ArrayList();
        try {

            Connection connection = OracleHelp.getCon();
            PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();

            ResultSetMetaData md = rs.getMetaData();
            int columnCount = md.getColumnCount();


            while (rs.next()){
                Map<String,Object> rowData = new HashMap();
                for (int i = 1; i <= columnCount; i++) {

                    rowData.put(md.getColumnName(i), rs.getObject(i));

                }
                list.add(rowData);
            }
            OracleHelp.closeCon(connection);//关闭数据库
            System.out.println("数据库连接成功");
            return list;
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("数据库连接失败");
            return list;
        }
    }
}

将ResultSet结果集转换为List

//借助bean的办法
//rs是一个查询语句所返回的结果集
ResultSet rs=new ResultSet();
rs=pstmt.executeQuery();
//将rs逐条取出，并将每一项放在一个bean对应的值里
               while(rs.next())
               { 
                 RecBean rec=new RecBean();
                 rec.setId(rs.getInt(1));
                 rec.setAgentNo(rs.getString(2));
                rec.setFileName(rs.getString(3));
                list.add(rec);
                }

//借助ResultSetMetaData 和Map
private static List convertList(ResultSet rs) throws SQLException {
        List list = new ArrayList();
        ResultSetMetaData md = rs.getMetaData();
        int columnCount = md.getColumnCount();
        while (rs.next()) {
            Map rowData = new HashMap();
            for (int i = 1; i <= columnCount; i++) {
                rowData.put(md.getColumnName(i), rs.getObject(i));
            }
            list.add(rowData);
        }
        return list;
}