ResultSet tabs = dbMetaData.getTables(null, null, null,types/*只要表就好了*/); /*记录集的结构如下: TABLE_CAT String => table catalog (may be null) TABLE_SCHEM String => table schema (may be null) TABLE_NAME String => table name TABLE_TYPE String => table type. REMARKS String => explanatory comment on the table TYPE_CAT String => the types catalog (may be null) TYPE_SCHEM String => the types schema (may be null) TYPE_NAME String => type name (may be null) SELF_REFERENCING_COL_NAME String => name of the designated "identifier" column of a typed table (may be null) REF_GENERATION String => specifies how values in SELF_REFERENCING_COL_NAME are created. Values are "SYSTEM", "USER", "DERIVED". (may be null) */ while(tabs.next()){ //只要表名这一列 tables.add(tabs.getObject("TABLE_NAME"));
或者
SQLServer代理中创建作业,调用存储过程
import java.sql.*;
import java.util.*;public class DBToFile{
private String dbName = "pubs"; //默认为pubs
private String directory = "pubs"; //文件夹名
String driver = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
String url = "jdbc:microsoft:sqlserver://localhost:1433;";
String user = "sa";
String pwd = "";
Connection cnn;
public DBToFile() throws Exception{
}
//
private void createDir() throws IOException{
File f = new File(directory);
if(f.exists()){
return;
}else{
f.mkdir();
}
}
//设置SQLServer中所要操作的数据库
public void setDBName(String name){
this.dbName = name;
}
public void setDirectory(String name){
this.directory = name;
}
//得到一个可用的数据库连接
public Connection getConnection(String driver, String url,
String user, String pwd) throws SQLException, ClassNotFoundException{
Class.forName(driver);
Connection cnn = DriverManager.getConnection(url, user, pwd);
cnn.setCatalog(dbName);
return cnn;
}
//得到当前数据库连接中的所有的表
private List getAllTableName() throws SQLException{
List tables = new ArrayList();
DatabaseMetaData dbMetaData = cnn.getMetaData();
//可为:"TABLE", "VIEW", "SYSTEM TABLE",
//"GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM"
String[] types = {"TABLE"};
ResultSet tabs = dbMetaData.getTables(null, null, null,types/*只要表就好了*/);
/*记录集的结构如下:
TABLE_CAT String => table catalog (may be null)
TABLE_SCHEM String => table schema (may be null)
TABLE_NAME String => table name
TABLE_TYPE String => table type.
REMARKS String => explanatory comment on the table
TYPE_CAT String => the types catalog (may be null)
TYPE_SCHEM String => the types schema (may be null)
TYPE_NAME String => type name (may be null)
SELF_REFERENCING_COL_NAME String => name of the designated "identifier" column of a typed table (may be null)
REF_GENERATION String => specifies how values in SELF_REFERENCING_COL_NAME are created. Values are "SYSTEM", "USER", "DERIVED". (may be null)
*/
while(tabs.next()){
//只要表名这一列
tables.add(tabs.getObject("TABLE_NAME"));
} return tables;
}
//
private void readDataAndWriteData() throws Exception{
//建文件夹
this.createDir();
//得到连接
cnn = this.getConnection(driver, url, user, pwd);
List tables = this.getAllTableName();
for(int i = 0; i < tables.size(); i++){
String tableName = tables.get(i).toString();
List content = this.readData(tableName);
this.writeFile(tableName, content);
}
cnn.close();
}
private List readData(String tableName) throws SQLException{
if(tableName == null || "".equals(tableName)){
return null;
}
List rows = new ArrayList();
//防止表名有空格
String sql = "select * from [" + tableName + "]";
Statement st = cnn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
;
ResultSet rs = st.executeQuery(sql);
//得到一个表的总列数
int columnCount = rs.getMetaData().getColumnCount();
while(rs.next()){
String line = "";
//表索引从1开始
for(int i = 1; i <= columnCount; i++){
Object temp = rs.getObject(i);
String cell = temp == null ? "NULL":temp.toString();
line = line + cell + "\t";
}
//把每一行放到一个List中
rows.add(line);
}
st.close(); return rows;
}
//写文件
private void writeFile(String fileName, List content) throws IOException{
PrintWriter out = new PrintWriter(
new FileWriter("./" + this.directory + "/" + fileName + ".txt"), true/* auto flush*/);
for(int i = 0; i < content.size(); i++){
out.println(content.get(i).toString());
}
out.close();
}
public static void main(String[] args){
try{
DBToFile test = new DBToFile(); test.readDataAndWriteData();
}catch(Exception e){
e.printStackTrace();
}
}
}