JDBC脚本批处理

//db1.properties数据库配置
driver = com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3306/zj
root = root
password = 123
-----------------------------------------------
package com.engaworld.test.util;

import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;

public class GetPropertiesConfig {
private  static Properties prop = new Properties();
private static GetPropertiesConfig  propertiesconfig=new GetPropertiesConfig();
private  GetPropertiesConfig(){
InputStream in = this.getClass().getResourceAsStream("db1.properties");
try {
        prop.load(in);
       } catch (IOException e) {
           // TODO Auto-generated catch block
            e.printStackTrace();
       }
}
public String getPropertiesItem(String key){

return prop.getProperty(key);
   
}
public static GetPropertiesConfig getPropertiesconfig() {
return propertiesconfig;
}

}

----------------------------------------------
package com.engaworld.test.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import org.apache.commons.dbcp.BasicDataSource;


public class MySQLConnectionBean {
private String driver;
private String url;
private String root;
private String password;
private Connection connection;
private BasicDataSource bas;
public MySQLConnectionBean(){
driver=GetPropertiesConfig.getPropertiesconfig().getPropertiesItem("driver");
url=GetPropertiesConfig.getPropertiesconfig().getPropertiesItem("url");
root=GetPropertiesConfig.getPropertiesconfig().getPropertiesItem("root");
password=GetPropertiesConfig.getPropertiesconfig().getPropertiesItem("password");
bas =new BasicDataSource();

bas.setDriverClassName(driver);
bas.setUrl(url);
bas.setUsername(root);
bas.setPassword(password);

try {
Class.forName(driver);

connection=DriverManager.getConnection(url,root,password);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public Connection getConnection(){
try {
connection = bas.getConnection();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

return   connection;
}
public void closeDBConnection(){

if(null!=connection){
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}

-----------------------------------------------
package com.engaworld.test.util;
/*
* 脚本批处理
*/
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class TestDBConnection {
private Connection con;
private PreparedStatement pst;
private ResultSet rs;

public List<List<String>> theSelect(List<String> list) {
List<List<String>> listt = new ArrayList<List<String>>();
List<String> lists = new ArrayList<String>();
MySQLConnectionBean  mysqlCon=new MySQLConnectionBean();
con=mysqlCon.getConnection();
try {
con.setAutoCommit(false);
int n = 0;
for (String string : list) {
n++;
System.out.println("第"+n+"条SQl");
pst=con.prepareStatement(string);
rs = pst.executeQuery();
int count = rs.getMetaData().getColumnCount();
int rows = 0;
for (int i = 1; i <= count; i++) {
lists.add(rs.getMetaData().getColumnName(i));
}
while(rs.next()){
int i = 1;
while(i<=count){
try {
lists.add(rs.getString(i));
i++;
} catch (Exception e) {
// TODO Auto-generated catch block
i = 50;
}
}
lists.add("\n");
rows++;
}
}
listt.add(lists);
con.commit();
} catch (SQLException e) {
e.printStackTrace();
}
mysqlCon.closeDBConnection();
return listt;
}
public void action(){
PrintWriter pw = null;
try {
pw = new PrintWriter(new BufferedWriter(new FileWriter(new File("D:/test/11.txt"))));
} catch (IOException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
List<String> list = new ArrayList<String>();
try {
BufferedReader in = new BufferedReader(new FileReader(new File("D:/Workspaces/MyEclipse 8.6/Test2012-04-28/thesql.sql")));
String s1;
while((s1=in.readLine())!=null){
// s1.replace("[date]", GetSystemDate.getDate().toString());//替换日期参数为系统当前日期
list.add(s1);
}
in.close();
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
List<List<String>> listt = this.theSelect(list);
for (List<String> list2 : listt) {
int a = 0;
for (String string : list2) {
a++;
if(string.equals("\n")){
a = 0;
}
if(a != 0){
string += ",";
}
System.out.print(string);
    pw.print(string);
}
}
pw.close();
}
public static void main(String[] args) {
TestDBConnection tdb = new TestDBConnection();
tdb.action();
}
}



-----------------------------------------完