本文实例讲述了Java实现从数据库导出大量数据记录并保存到文件的方法。分享给大家供大家参考，具体如下：
数据库脚本：
-- Table "t_test" DDLCREATE 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 block e.printStackTrace(); } catch (IllegalAccessException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { try { Conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.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 block e.printStackTrace(); } catch (IllegalAccessException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { try { Conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.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 block e.printStackTrace(); } catch (IllegalAccessException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { try { Conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.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 block e.printStackTrace(); } finally{ try { pstmt.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.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语句来输出.否则会内存不足导致错误.
主要用途: 可以使用在做接口开发时,给第三方提供数据增量输出的场景使用.
希望本文所述对大家Java程序设计有所帮助。