【Oracle/jdbc/file】将oracle某表数个字段读出成csv文件及读取csv文件写入目标表的两段程序

注意：此文为在下工作记录，未必对您有用。

 

【读表写入csv文件程序】

复制代码
package com.hy.datamasting;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.io.FileNotFoundException;
import java.io.PrintWriter;

public class JdbcExp {
    //-- 以下为连接Oracle数据库的四大参数
    private static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
    private static final String URL = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
    private static final String USER = "luna";
    private static final String PSWD = "1234";

    public void export(String filename,String table,String[] fields){
        List<String> datas=new ArrayList<>();
        String fieldLine=String.join(",",fields);

        Connection conn = null;
        Statement stmt = null;

        try{
            Class.forName(DRIVER);
            conn = DriverManager.getConnection(URL, USER, PSWD);


            String sql=String.format("select %s from %s order by id",fieldLine,table);

            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);

            while (rs.next()) {
                List<String> ls=new ArrayList<>();

                for(String fd:fields){
                    String segment = rs.getString(fd);
                    ls.add(segment);
                }

                String line=String.join(",",ls);
                datas.add(line);
            }
            rs.close();
        } catch (Exception e) {
            System.out.print(e.getMessage());
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                System.out.print("Can't close stmt/conn because of " + e.getMessage());
            }
        }

        write2File(filename,table,fieldLine,datas);
    }

    private void write2File(String filename,String table,String fieldLine,List<String> datas){
        PrintWriter out;
        try {
            out = new PrintWriter(filename);

            // 输出文本
            out.println(table);
            out.println(fieldLine);
            for(String line:datas){
                out.println(line);
            }
            out.close();

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args){
        long startMs=System.currentTimeMillis();

        JdbcExp exp=new JdbcExp();
        exp.export("c:\\temp\\exp.csv","emp_from",new String[]{"id","name","age","salary"});

        long endMs=System.currentTimeMillis();
        System.out.println("Time elapsed:"+TimeUtil.ms2DHMS(startMs,endMs));
    }
}
复制代码

【CSV文件部分节选】

复制代码
emp_from
id,name,age,salary
1,XBCAVEHPSR,63,42590
2,BHNQVZAYKJ,62,25960
3,DBBLWOJNGU,18,1410
4,SBGBGGOMBR,42,37364
5,ILGLYGCWPD,26,5092
6,FQJBJFSBBH,27,48174
7,ZEKXNJXDZI,55,17478
8,QEGUHLSQIY,51,37556
9,OAQHHGPJDQ,30,25996
10,DKANHZFQZZ,41,38846
11,MXNSWZXERE,36,29380
12,YYXDLKEHZA,46,27148
13,SSFJXDFUZM,34,4012
14,YRRTBYMFCR,19,9691
15,MUMAMFLCIV,32,44891
16,IBZATVVCDG,28,23771
...
复制代码

【读取csv写入emp_to表程序】

复制代码
package com.hy.datamasting;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class JdbcImp {
    //-- 以下为连接Oracle数据库的四大参数
    private static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
    private static final String URL = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
    private static final String USER = "luna";
    private static final String PSWD = "1234";

    public void readFromFile(String filename){
        String table="";
        String fieldLine="";
        List<String> datas=new ArrayList<>();

        try {
            BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(filename), "UTF-8"));
            String line = null;
            int idx=0;
            while( ( line = br.readLine() ) != null ) {
                idx++;

                if(idx==1){
                    table=line;
                }else if(idx==2){
                    fieldLine=line;
                }else{
                    datas.add(line);
                }
            }
            br.close();
        } catch (FileNotFoundException ex) {
            ex.printStackTrace();
        } catch (IOException ex) {
            ex.printStackTrace();
        }

        /*System.out.println(table);
        System.out.println(fieldLine);
        for(String line:datas){
            System.out.println(line);
        }*/

        insertDatas("emp_to",fieldLine,datas);
    }

    private void insertDatas(String tableName,String fieldLine,List<String> datas){
        Connection conn = null;
        Statement stmt = null;

        try{
            Class.forName(DRIVER);
            conn = DriverManager.getConnection(URL, USER, PSWD);
            conn.setAutoCommit(false);
            stmt = conn.createStatement();

            for(String dt:datas){
                String[] arr=dt.split(",");
                String values=String.format("'%s'",String.join("','",arr));

                String sql=String.format("insert into %s(%s) values(%s)",tableName,fieldLine,values);
                stmt.execute(sql);
            }
            conn.commit();
        } catch (Exception e) {
            System.out.print(e.getMessage());
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                System.out.print("Can't close stmt/conn because of " + e.getMessage());
            }
        }
    }

    public static void main(String[] args){
        long startMs=System.currentTimeMillis();

        JdbcImp imp=new JdbcImp();
        imp.readFromFile("c:\\temp\\exp.csv");

        long endMs=System.currentTimeMillis();
        System.out.println("Time elapsed:"+TimeUtil.ms2DHMS(startMs,endMs));
    }
}
复制代码

 【读取csv批量插入emp_to表程序】

复制代码
package com.hy.datamasting;

import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class JdbcBatchImp {
    //-- 以下为连接Oracle数据库的四大参数
    private static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
    private static final String URL = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
    private static final String USER = "luna";
    private static final String PSWD = "1234";

    public void readFromFile(String filename){
        String table="";
        String fieldLine="";
        List<String> datas=new ArrayList<>();

        try {
            BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(filename), "UTF-8"));
            String line = null;
            int idx=0;
            while( ( line = br.readLine() ) != null ) {
                idx++;

                if(idx==1){
                    table=line;
                }else if(idx==2){
                    fieldLine=line;
                }else{
                    datas.add(line);
                }
            }
            br.close();
        } catch (FileNotFoundException ex) {
            ex.printStackTrace();
        } catch (IOException ex) {
            ex.printStackTrace();
        }

        /*System.out.println(table);
        System.out.println(fieldLine);
        for(String line:datas){
            System.out.println(line);
        }*/

        insertDatas("emp_to",fieldLine,datas);
    }

    private void insertDatas(String tableName,String fieldLine,List<String> datas){
        Connection conn = null;
        Statement stmt = null;

        try{
            Class.forName(DRIVER);
            conn = DriverManager.getConnection(URL, USER, PSWD);

            stmt = conn.createStatement();

            int start=0;
            int end=0;
            final int SIZE=200;
            int n=datas.size();

            do{
                end=start+SIZE;
                if(end>n){
                    end=n;
                }

                List<String> partDatas=datas.subList(start,end);
                //System.out.print("Start="+start+"  "+end+"\n");

                StringBuilder sb=new StringBuilder();
                sb.append("INSERT ALL ");
                for(String dt:partDatas){
                    String[] arr=dt.split(",");
                    String values=String.format("'%s'",String.join("','",arr));

                    String partSql=String.format(" into %s(%s) values(%s)",tableName,fieldLine,values);
                    sb.append(partSql);

                }
                sb.append("select * from dual");

                conn.setAutoCommit(false);
                stmt.execute(sb.toString());
                conn.commit();

                start+=SIZE;
            }while(start<n);

        } catch (Exception e) {
            System.out.println(e.getMessage());
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                System.out.println("Can't close stmt/conn because of " + e.getMessage());
            }
        }
    }

    public static void main(String[] args){
        long startMs=System.currentTimeMillis();

        JdbcBatchImp imp=new JdbcBatchImp();
        imp.readFromFile("c:\\temp\\exp.csv");

        long endMs=System.currentTimeMillis();
        System.out.println("Time elapsed:"+TimeUtil.ms2DHMS(startMs,endMs));
    }
}
复制代码

【实验结果】

在T14上，读取万行用时3秒；

写入万行用时9秒。

批量写入万行用时5.5秒。

END

posted @   逆火狂飙  阅读(125)  评论(0)  编辑  收藏  举报
相关博文：
· 【Oracle/jdbc/file】将oracle某表数个字段读出成csv文件及读取csv文件写入目标表的两段程序（改进版）
· 【java/oracle】从blob字段读取数据到文件例子
· 【Python】读取Oracle连接表生成CSV文件二
· Java读取excel文件（.xlsx/.xls）和.csv文件存入MySQL数据库
· java操作Oracle 方式一 ( 连接-》操作-》断开连接 )
阅读排行：
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
· C#/.NET/.NET Core优秀项目和框架2025年2月简报
· Manus爆火，是硬核还是营销？
· 终于写完轮子一部分：tcp代理 了，记录一下
· 【杭电多校比赛记录】2025“钉耙编程”中国大学生算法设计春季联赛（1）
历史上的今天：
2019-03-09 【Canvas与游戏】Bombman v1.04
2019-03-09 【Canvas与艺术】模拟八一电影制片厂电影片头效果
2019-03-09 【Canvas技法】蓝布底金字北岛诗节选（背景图片、文字阴影示例）
2017-03-09 “阿基里斯与乌龟”的终结性思考
2017-03-09 生活本来的样子
生当作人杰 死亦为鬼雄 至今思项羽 不肯过江东
点击右上角即可分享
微信分享提示