时间：2014-8-11 作者：admin 分类： 摘自网络
导出路径最好写绝对路径
<%@ page language="java" import="java.util.*,java.sql.*,java.io.*"
pageEncoding="utf-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme() + "://"
+ request.getServerName() + ":" + request.getServerPort()
+ path + "/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Jsp + MySQL</title>
</head>
<body>
<%!public static class Tool {
public static String strTab(String str, int num) {//补齐24位
int j = str.length();
if (str.length() < num) {
for (int x = j; x < num; x++, j++) {
str = str + " ";
}
}
return str;
}
}
public class Column {
private String name;
private String data;
private String type;
public Column() {
}
public Column(String name, String type) {
this.name = name;
this.type = type;
}
public void setName(String name) {
this.name = name;
}
public void setType(String type) {
this.type = type;
}
public void setDate(String date) {
this.data = date;
}
public String getName() {
return this.name;
}
public String getType() {
return this.type;
}
public String getDate() {
return this.data;
}
}
public class Table {
private String name;
private List<Column> column = new ArrayList<Column>();
private String count;
public void setCount(String count) {
this.count = count;
}
public String getCount() {
return this.count;
}
public Table() {
}
public Table(String name, String count) {
this.name = name;
this.count = count;
}
public void setName(String name) {
this.name = name;
}
public String getName() {
return this.name;
}
public void setColumn(List<Column> column) {
this.column = column;
}
public List<Column> getColumn() {
return this.column;
}
}
/*
数据库连接类
*/
public class DatabaseConnection {
private Connection connection;
private Statement statement;
private ResultSet res;
public DatabaseConnection(String url, String username, String password) {
this.getConnection(url, username, password);
}
public Connection getConnection(String url, String username,
String password) {
try {
Class.forName("org.gjt.mm.mysql.Driver");
this.connection = DriverManager.getConnection(url, username,
password);
} catch (Exception e) {
e.printStackTrace();
}
return this.connection;
}
public ResultSet execut(String sql) throws Exception {
this.statement = this.connection.createStatement();
this.res = this.statement.executeQuery(sql);
return this.res;
}
public void close() {
if (this.connection != null) {
try {
this.connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}%>
<%
String submit = request.getParameter("submit");
String b = request.getParameter("b"); //标志位,是否是导出数据
if (submit == null || "".equals(submit)) {
%>
<form method="post" action="#">
database:
<input type="text" value="" name="db" />
<br />
username:
<input type="text" value="" name="username" />
<br />
password:
<input type="text" value="" name="password" />
<br />
port:
<input type="text" value="3306" name="port" />
<br />
<input type="submit" value="submit" name="submit" />
<br />
</form>
<%
} else if (b == null || "".equals(b)) {
String db = request.getParameter("db");
String username = request.getParameter("username");
String password = request.getParameter("password");
String port = request.getParameter("port");
String url = "jdbc:mysql://localhost:" + port + "/" + db; //连接字符串
request.getSession().setAttribute("username", username);
request.getSession().setAttribute("password", password);
request.getSession().setAttribute("url", url);
String sql = "select table_name from information_schema.TABLES where table_schema=(select DATABASE());"; //查出当前库所有表
DatabaseConnection dbc = new DatabaseConnection(url, username,
password);
ResultSet res = dbc.execut(sql);
List<Table> table = new ArrayList<Table>(); //存放所有的表
while (res.next()) {
String tablename = res.getString(1);
String sqlcount = "select count(*) from " + tablename;
ResultSet temp = dbc.execut(sqlcount);
if (temp.next()) {
sqlcount = temp.getString(1);
}
table.add(new Table(res.getString(1), sqlcount));
temp.close();
}
res.close(); //关闭资源
for (Table t : table) {
String tsql = "select COLUMN_NAME ,DATA_TYPE from information_schema.COLUMNS where TABLE_NAME='"
+ t.getName() + "'"; // 查询表结构
ResultSet tres = dbc.execut(tsql);
while (tres.next()) {
t.getColumn()
.add(
new Column(tres.getString(1), tres
.getString(2)));
}
}
request.setAttribute("table", table);
res.close();
dbc.close();
for (Table t : table) {
out.print("<div style=\"float:left;padding-left:30px\">");
out.print("+------------------------+<br/>");
out.print("|"
+ Tool.strTab(
t.getName() + " ---> " + t.getCount(), 24)
+ "|<br/>");
out.print("+------------------------+<br/>");
out.print("| " + Tool.strTab("Column", 10) + "| "
+ Tool.strTab("Type", 11) + "|<br/>");
out.print("+-----------+------------+<br/>");
for (Column c : t.column) {
out.print("| " + Tool.strTab(c.getName(), 10) + "| "
+ Tool.strTab(c.getType(), 11) + "|<br/>");
out.print("+-----------+------------+<br/>");
}
out.print("</div>");
}
%>
<!-- 开始进行数据导出的HTML数据 -->
<form method="post" action="#">
<br style="clear: left" />
<hr style="float: none" />
表名：
<input type="text" name="table" />
<br />
列名：
<input type="text" value="*" name="colums" />
以逗号隔开(英文)，默认为*
<br />
分行：
<input type="text" value="5000" name="row" />
每次查询多少条？默认每次5000条
<br />
<input type="hidden" name="b" value="b" />
保存路径：
<input type="text" value="" name="path" />
<br />
<input type="submit" value="开始导出" name="submit" />
</form>
<%
} else {
String table = request.getParameter("table").trim();
String colums = request.getParameter("colums").trim();
String row = request.getParameter("row").trim();
String filePath = request.getParameter("path").trim();
String sql = "select " + colums + " from " + table;
int index = 0;
if (row == null || "".equals(row)) {
index = 10000; //默认10000条每次
} else {
index = Integer.parseInt(row);
}
String url = (String) request.getSession().getAttribute("url");
String username = (String) request.getSession().getAttribute(
"username");
String password = (String) request.getSession().getAttribute(
"password");
DatabaseConnection dbc = new DatabaseConnection(url, username,
password);
String sqlcount = "select count(*) from " + table;
ResultSet res = dbc.execut(sqlcount);
int count = 0;
if (res.next()) {
count = res.getInt(1);
}
res.close();
//开始执行SQL语句
//1、先进行判断是否是* ,如果是的话就进行查询表结构..
if (colums.trim().length() == 1) {
String temp = "select COLUMN_NAME from information_schema.COLUMNS where TABLE_NAME='"
+ table + "'";
ResultSet tempr = dbc.execut(temp);
colums ="";
while (tempr.next()) {
colums += tempr.getString(1) + ",";
}
tempr.close();
colums = colums.substring(0, colums.length() - 1);
}
String c[] = colums.split(","); //或去所有列
if(new File(filePath).exists()){
new File(filePath).delete(); //如果文件存在就删除..
}
FileWriter fw = new FileWriter(filePath,true);
for(String t:c){
fw.write(t+"\t\t");
}
fw.write("\r\n");
fw.flush();
out.print("开始导出数据：共"+count+"条<br/>");
int j=0;
for (int x = 0; x < count; x += index) {
String rsql = "select " + colums + " from " + table + " limit "
+ x + "," + index;
ResultSet rs = dbc.execut(rsql);
while (rs.next()) {
String tempdate = "";
for(String it : c){
// tempdate += rs.getString(it)+"\t"; //得到每条数据
try{
tempdate +=rs.getString(it)+"\t\t";
}catch(Exception e){
}
}
fw.write(tempdate+"\r\n");
j++;
}
if(j%10000==0){
out.print("正在导出 ："+j+"条<br/>");
out.flush();
}
fw.flush();
}
out.print("已导出 ："+j+"条<br/>");
out.print("导出完毕...Good !!");
fw.close();
dbc.close();
}
%>
</body>
</html>