从数据库导出大量数据记录保存到文件的方法和实例

从数据库导出大量数据记录保存到文件的方法和实例

数据库脚本：

-- Table "t_test" DDL

CREATE TABLE `t_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT NULL,
  `createTime` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

 

代码:

 

package com.yanek.test;import java.io.BufferedReader;import java.io.File;import java.io.FileOutputStream;import java.io.FileReader;import java.io.IOException;import java.io.OutputStreamWriter;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class TestDB {public static void main(String[] args) {Test();  // 生成测试数据//Exp();//Exp(0);//System.out.println(readText("/opt/id.txt"));}/** * 导出数据 */ public static void Exp() { Connection Conn=null;try {Class.forName("com.mysql.jdbc.Driver").newInstance();String jdbcUrl = "jdbc:mysql://127.0.0.1:3306/testcms?characterEncoding=GBK";String jdbcUsername = "root";String jdbcPassword = "root";Conn = DriverManager.getConnection(jdbcUrl, jdbcUsername, jdbcPassword);System.out.println("conn"+Conn);Exp(Conn);} catch (SQLException e) {e.printStackTrace();}catch (InstantiationException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (IllegalAccessException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (ClassNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();}finally{try {Conn.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}   }  public static void Exp(int startid) { Connection Conn=null;try {Class.forName("com.mysql.jdbc.Driver").newInstance();String jdbcUrl = "jdbc:mysql://127.0.0.1:3306/testcms?characterEncoding=GBK";String jdbcUsername = "root";String jdbcPassword = "root";Conn = DriverManager.getConnection(jdbcUrl, jdbcUsername, jdbcPassword);System.out.println("conn"+Conn);Exp(Conn,startid);} catch (SQLException e) {e.printStackTrace();}catch (InstantiationException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (IllegalAccessException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (ClassNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();}finally{try {Conn.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}   }  /**  * 导出从startid开始的数据  * @param conn  * @param start_id  */ public static void Exp(Connection conn,int start_id) { int counter = 0;int startid=start_id;boolean flag = true;while (flag) {flag = false;String Sql = "SELECT * FROM t_test WHERE id>"+ startid + " order by id asc LIMIT 50";System.out.println("sql===" + Sql);try {Statement stmt = conn.createStatement();ResultSet rs = stmt.executeQuery(Sql);while (rs.next()) {flag = true;int id = rs.getInt("id");String title = rs.getString("title");startid = id ;counter++;writeContent(counter+"--id--"+id+"--title-"+title+"\r\n", "/opt/","log.txt",true); System.out.println("i="+counter+"--id--"+id+"--title-"+title);}rs.close();stmt.close();} catch (SQLException e) {e.printStackTrace();}}writeContent(""+startid, "/opt/","id.txt",false);  }   /**  * 导出一小时内的数据  * @param conn  */ public static void Exp(Connection conn) { int counter = 0;//一小时内的数据Long timestamp = System.currentTimeMillis() - (60 * 60 * 1000);boolean flag = true;while (flag) {flag = false;String Sql = "SELECT * FROM t_test WHERE createTime>"+ timestamp + " LIMIT 50";System.out.println("sql===" + Sql);try {Statement stmt = conn.createStatement();ResultSet rs = stmt.executeQuery(Sql);while (rs.next()) {flag = true;int id = rs.getInt("id");String title = rs.getString("title");Long lastmodifytime = rs.getLong("createTime");timestamp = lastmodifytime;counter++;System.out.println("i="+counter+"--id--"+id+"--title-"+title);}rs.close();stmt.close();} catch (SQLException e) {e.printStackTrace();}}     } public static void Test() {Connection Conn=null;try {Class.forName("com.mysql.jdbc.Driver").newInstance();String jdbcUrl = "jdbc:mysql://127.0.0.1:3306/testcms?characterEncoding=GBK";String jdbcUsername = "root";String jdbcPassword = "root";Conn = DriverManager.getConnection(jdbcUrl, jdbcUsername, jdbcPassword);System.out.println("conn"+Conn);for(int i=1;i<=10000;i++){add(Conn,"testTitle"+i+"-"+System.currentTimeMillis());}} catch (SQLException e) {e.printStackTrace();}catch (InstantiationException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (IllegalAccessException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (ClassNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();}finally{try {Conn.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}} public static void add(Connection conn,String title)  {    PreparedStatement pstmt = null;String insert_sql = "insert into t_test(title,createTime) values (?,?)";System.out.println("sql="+insert_sql);try {pstmt = conn.prepareStatement(insert_sql);pstmt.setString(1,title);pstmt.setLong(2,System.currentTimeMillis());int ret = pstmt.executeUpdate();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}finally{try {pstmt.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}    }  /** * 写入内容到文件 *  * @param number * @param filename * @return */public static boolean writeContent(String c, String dirname,String filename,boolean isAppend) {File f=new File(dirname);if (!f.exists()){f.mkdirs();}try {FileOutputStream fos = new FileOutputStream( dirname+File.separator+filename,isAppend);OutputStreamWriter writer = new OutputStreamWriter(fos);writer.write(c);writer.close();fos.close();} catch (IOException e) {e.printStackTrace();return false;}return true;} /** * 从文件读取内容 *  * @param filename * @return */public static String readText(String filename) {String content = "";try {File file = new File(filename);if (file.exists()) {FileReader fr = new FileReader(file);BufferedReader br = new BufferedReader(fr);String str = "";String newline = "";while ((str = br.readLine()) != null) {content += newline + str;newline = "\n";}br.close();fr.close();}} catch (IOException e) {e.printStackTrace();}return content;}}

 

基本内思想: 就是通过记录开始记录id,执行多次sql来处理. 由于大数据量所以不能使用一条sql语句来输出.否则会内存不足导致错误.

 

主要用途: 可以使用在做接口开发时,给第三方提供数据增量输出的场景使用.

 

有问题可以联系qq: 1046011462