Java实现从数据库导出大量数据记录并保存到文件的方法

Java实现从数据库导出大量数据记录并保存到文件的方法

本文实例讲述了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 http://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程序设计有所帮助。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。