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

2024-07-13 09:55:56

字体：大中小

来源：转载

供稿：网友

这篇文章主要介绍了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 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;

}

}