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;
}
}