数据库大量数据导出导入

1.jdbc批量导入数据 大约3秒导入32万条记录

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;


public class HttpXmlSender_export_data {

	public static void main(String[] args) throws Exception {
		List<String> list = IOTool.getContentFromText("D:\\tpdata\\twoElement\\test.txt", "UTF-8");
		long startTime = System.currentTimeMillis();
		insertBatch(list);
		long endTime = System.currentTimeMillis();
		System.out.println("耗时:"+(endTime-startTime));
	}
	
	/**
	 * 分割数组
	 * @param dataString
	 * @param regex
	 * @return
	 */
	public static String[] splitToData(String dataString,String regex) {
		dataString = dataString + regex+"tmpData";
		String[] arrs = dataString.split(regex);
		return arrs;
	}
	
	public static int insertBatch(List<String> list) {
		Connection conn = getConn();
		try {
			conn.setAutoCommit(false);
			String sql = "insert into TMP_20190730_X(name,phone) values(?,?)";
			PreparedStatement pstmt = (PreparedStatement) conn.prepareStatement(sql);
			for(int i=0;i<list.size();i++) {
				String[] data = splitToData(list.get(i),";");
				
				pstmt.setString(1, data[0]);
				pstmt.setString(2, data[1]);
				pstmt.addBatch();
				if(i%500==0||i==list.size()-1) {
					pstmt.executeBatch();
				}
			}
			conn.commit();
			pstmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		close(conn);
		
	    return 0;
	}
	
	public static void close(Connection conn) {
		if(conn!=null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	private static Connection getConn() {
		String driver = "oracle.jdbc.driver.OracleDriver";
		// 生产
		String url = "";
		Connection conn = null;
		try {
		    Class.forName(driver); //classLoader,加载对应驱动
		  //  conn = DriverManager.getConnection(url, "tpsc_test", "tpsc_test");
		    conn = DriverManager.getConnection(url, "", "");  //生产
		} catch (ClassNotFoundException e) {
		    e.printStackTrace();
		} catch (SQLException e) {
		    e.printStackTrace();
		}
		return conn;
	}
}

 

2.IOTool工具类

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.RandomAccessFile;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.alibaba.fastjson.JSON;


public class IOTool {

	//=============================================导入数据=============================================
	/**
	 * 读取text文件
	 * @param filePath
	 * @param charSet
	 * @return
	 * @throws Exception
	 */
	public static List<String> getContentFromText(String filePath,String charSet) throws Exception {
		List<String> list = new ArrayList<String>();
		BufferedReader reader = new BufferedReader(new InputStreamReader(new FileInputStream(filePath),charSet));
		String contentLine = "";
		while((contentLine = reader.readLine())!=null) {
			list.add(contentLine);
		}
		return list;
	}
	public static List<String> getContentFromCsv(String filePath) throws Exception {
		List<String> list = new ArrayList<String>();
		BufferedReader file = new BufferedReader(new InputStreamReader(new FileInputStream(filePath), "UTF-8"));
		String contentLine = "";
		// 遍历数据行并存储在名为records的ArrayList中，每一行records中存储的对象为一个String数组
		while ((contentLine = file.readLine()) != null) {
			list.add(contentLine);
		}
		return list;
	}
	/**
	 * 读取Excel数据
	 * @param filePath
	 * @param columns
	 * @return
	 * @throws Exception
	 */
	public static List<String> getContentFromExcle(String filePath,int columns) throws Exception {
		List<String> list = new ArrayList<String>();
		StringBuffer sb = new StringBuffer();
		Workbook wb = getWorkbook(filePath);
		int sheets = wb.getNumberOfSheets();
		for(int i=0;i<sheets;i++) {
			Sheet sheet = wb.getSheetAt(i);
			for(int j=0;j<=sheet.getLastRowNum();j++) {
				Row row = sheet.getRow(j);
				for(int k=0;k<columns;k++) {	
					Cell cell = row.getCell(k);
					String data = getStringCell(cell);
					sb.append(data+",");
				}
				String str = sb.toString();
				// 清空StringBuffer
				sb.setLength(0);
				str = str.substring(0,str.length()-1);
				list.add(str);
			}
		}
        return list;
	}
	
	public static String getStringCell(Cell cell) {
		String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
        // 判断数据的类型
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_NUMERIC: // 数字
            //short s = cell.getCellStyle().getDataFormat();
            SimpleDateFormat sdf = null;
            // 验证short值
            if (cell.getCellStyle().getDataFormat() == 14) {
                sdf = new SimpleDateFormat("yyyy/MM/dd");
                Date date = cell.getDateCellValue();
                cellValue = sdf.format(date);
             } else if (cell.getCellStyle().getDataFormat() == 21) {
                sdf = new SimpleDateFormat("HH:mm:ss");
                Date date = cell.getDateCellValue();
                cellValue = sdf.format(date);
             } else if (cell.getCellStyle().getDataFormat() == 22) {
                sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
                Date date = cell.getDateCellValue();
                cellValue = sdf.format(date);
             } else if (cell.getCellStyle().getDataFormat() == 0) {//处理数值格式
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cellValue = String.valueOf(cell.getRichStringCellValue().getString());
             }else {
                throw new RuntimeException("日期格式错误!!!");
             }
               
            break;
        case Cell.CELL_TYPE_STRING: // 字符串
            cellValue = String.valueOf(cell.getStringCellValue());
            break;
        case Cell.CELL_TYPE_BOOLEAN: // Boolean
            cellValue = String.valueOf(cell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_FORMULA: // 公式
            cellValue = String.valueOf(cell.getCellFormula());
            break;
        case Cell.CELL_TYPE_BLANK: // 空值
            cellValue = null;
            break;
        case Cell.CELL_TYPE_ERROR: // 故障
            cellValue = "非法字符";
            break;
        default:
            cellValue = "未知类型";
            break;
        }
        return cellValue;
	}
	
	public static void main(String[] args) throws Exception {
		String str = ",,aaa,,ddd,44";
		String[] arr = str.split(",");
		System.out.println(JSON.toJSONString(arr));
		
	//	List<String> list = getContentFromCsv("D:\\tpdata\\test.csv");
	//	for(String str :list) {
	//		System.out.println(str);
	//	}
		
		
	//	33,,,,33,
	//	,,aaa,,ddd,44
	}
	
	public static Workbook getWorkbook(String filePath) throws IOException{
        Workbook wb = null;
        FileInputStream in = new FileInputStream(filePath);
        if(filePath.endsWith("xls")){     //Excel&nbsp;2003
            wb = new HSSFWorkbook(in);
        }else if(filePath.endsWith("xlsx")){    // Excel 2007/2010
            wb = new XSSFWorkbook(in);
        }
        return wb;
        
    }
	
	//=============================================导出数据=================================
	
	/**
	 * 导出txt
	 */
	public static void exportDataToTxt(List<String> list) throws Exception {
		if(list==null || list.size()==0) {
			return ;
		}
		String dateString = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
		String path = "D:\\tpdata\\export\\"+ dateString+".txt";
		File file = new File(path);
		file.createNewFile();
		 
		RandomAccessFile raf=new RandomAccessFile(file, "rw");
		for(String str:list) {
			raf.seek(raf.length());
			raf.writeBytes(str+"\r\n");
		}
		raf.close();
		
	}
}

 3.导出数据，oracle采用sqluldr2，将三个文件放入pl/sql的bin目录下

 打开cmd,输入sqluldr2.exe USER=username/password@WX SQL=D:\tpdata\sql\sql_1.txt head=yes FILE=D:\tpdata\sql\export\test.txt

 

可以把sql语句放入sql_1.txt中，如果使用csv作为输出文件，会有行数限制，最多只有1048576条

 

 

4.mysql 数据导入导出

 show variables like '%secure%';  查看secure_file_priv路径

也可以修改路径，找到my.ini文件修改，修改后重启mysql服务

 

导入：

SELECT * FROM pay INTO OUTFILE 'D:\\tpdata\\mysql\\data2.txt' FIELDS TERMINATED BY ',' ;

导出

LOAD DATA INFILE 'D:\\tpdata\\mysql\\test2.txt' INTO TABLE pay FIELDS TERMINATED BY ',';

 

这种方式有点缺点，文件都会在mysql安装的服务器上。