This article describes the Java implementation method of exporting large amounts of data records from a database and saving them to a file. Share it for your reference, as follows:
Database script:
-- Table "t_test" DDLCREATE TABLE `t_test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(255) DEFAULT NULL, `createTime` bigint(20) DEFAULT NULL, P RIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Code:
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; i mport java.sql.Statement; public class TestDB { public static void main( String[] args) { Test(); // Generate test data //Exp(); //Exp(0); //System.out.println(readText("/opt/id.txt")); } /** * Export data*/ public static void Exp() { Connection Conn=null; try { Class.forName("com.mysql.jdbc.Driver").newInstance(); String jdbcUrl = "jdb c:mysql:/ /127.0.0.1:3306/testcms?characterEncoding=GBK"; String jdbcUsername = "root"; String jdbcPassword = "root"; Conn = DriverManager.getConnection(j dbcUrl, jdbcUsername, jdbcPassword); System.out.println("conn" +Conn); Exp(Conn); } catch (SQLException e) { e.printStackTrace(); } catch (InstantiationException e) { // TODO Auto-generated catch block e.printStackTr ace(); } 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 jdbcPassw ord = "root"; Conn = DriverManager.getConnection(jdbcUrl, jdbcUsername, jdbcPassword); System.out.println("conn"+Conn); Exp(Conn,startid); } catch (SQLException e) { e.printStackTrace(); } catch (Instan tiationException e) { // TODO Auto- generated catch block e.printStackTrace(); } catch (IllegalAccessException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (ClassNo tFoundException e) { // TODO Auto-generated catch block e.printStackTrace() ; } finally { try { Conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } /** * Export data starting from 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 = s tmt.executeQuery( Sql); while (rs.next()) { flag = true; int id = rs.getInt("id"); String title = rs.getString("title"); startid = id ; counter++; writeContent(c ounter+" --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); } /** * Export data within one hour* @param conn */ public static void Exp(Connection conn) { int counter = 0; //Data within one hour 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(S ql); while (rs.next ()) { flag = true; int id = rs.getInt("id"); String title = rs.getString("title"); Long lastmodifytime = rs.getLong("createTime"); timestamp = la stmodifytime; 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(); Str ing jdbcUrl = "jdbc:mysql ://127.0.0.1:3306/testcms?characterEncoding=GBK"; String jdbcUsername = "root"; String jdbcPassword = "root"; Conn = DriverManager.getConnecti on(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) { // TOD O 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) value s (?,?)"; 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 bloc k e.printStackTrace(); } } } /** * Write content to file* * @param number * @param filename * @return */ public static boolean writeContent(String c, String dirname,String filename,boolean i sAppend) { File f=new File(dirname ); if (!f.exists()) { f.mkdirs(); } try { FileOutputStream fos = new FileOutputStream( dirname+File.separator+filename,isAppend); OutputStreamWr iter writer = new OutputStreamWriter(fos); writer.write (c); writer.close(); fos.close(); } catch (IOException e) { e.printStackTrace(); return false; } return true; } /** * Read content from file* * @param : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : filename * @return */ public static String readText(String filename) { String content = ""; try { File file = new File(filename); if (file.exists()) { Fi leReader fr = new FileReader(file); BufferedReader br = new BufferedReader(fr); String str = ""; String newline = ""; while ((str = br.readLine()) != null) { content += newline + str; new line = "/n" ; } br.close(); fr.close(); } } catch (IOException e) { e.printStackTrace(); } return content; } }Basic idea: It is to start recording the id and execute SQL multiple times to process it. Due to the large amount of data, it cannot be used to output it. Otherwise, there will be insufficient memory and errors.
Main uses: It can be used in scenarios where data incremental output is provided to third parties during interface development.
I hope this article will be helpful to everyone's Java programming.