mysql java导出表数据_java从mysql导出数据例子

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

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/test?characterEncoding=GBK";

//String jdbcUsername = "root";

//String jdbcPassword = "mysql";

Conn = DriverManager.getConnection(jdbcUrl, "root", "mysql");

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/test?characterEncoding=GBK";

String jdbcUsername = "root";

String jdbcPassword = "mysql";

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", "D:\\","log.txt",true);

System.out.println("i="+counter+"--id--"+id+"--title-"+title);

}

rs.close();

stmt.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

writeContent(""+startid, "D:\\","id.txt",false);

}

/**

* 导出一小时内的数据

* @param conn

*/

public static void Exp(Connection conn) {

int counter = 0;

//一小时内的数据

Long timestamp = System.currentTimeMillis() - (600 * 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/test?characterEncoding=GBK";

String jdbcUsername = "root";

String jdbcPassword = "mysql";

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;

}

}

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值