本文實例講述了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程序設計有所幫助。