Java实现从数据库导出大量数据记录并保存到文件的方法
程序员文章站
2024-03-07 17:17:27
本文实例讲述了java实现从数据库导出大量数据记录并保存到文件的方法。分享给大家供大家参考,具体如下:
数据库脚本:
-- table "t_test" ddl...
本文实例讲述了java实现从数据库导出大量数据记录并保存到文件的方法。分享给大家供大家参考,具体如下:
数据库脚本:
-- 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 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程序设计有所帮助。