oracle、sql server 批量执行sql脚本

package com.szkingdom.installer.database.aspect; import com.szkingdom.installer.common.Utils; import com.szkingdom.installer.database.DBFactory; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.regex.Matcher; import java.util.regex.Pattern; public class SQLServerAspect implements Aspect { private Connection conn = null; private String databaseName = ""; public boolean CreateDatabase(String dbPath, String dabaseName) throws Exception { try { StringBuffer sql = new StringBuffer(); sql.append("CREATE DATABASE " + dabaseName + " ON (NAME = N'" + dabaseName + "_Data',"); sql.append("FILENAME = N'" + dbPath + "\\" + dabaseName + "_Data.MDF' ,"); sql.append("SIZE = " + Utils.getValue("kjdp.dbSize") + ", FILEGROWTH = 10%) LOG "); sql.append("ON (NAME = N'" + dabaseName + "_Log',"); sql.append("FILENAME = N'" + dbPath + "\\" + dabaseName + "_Log.LDF' ,"); sql.append("SIZE = " + Utils.getValue("kjdp.dbSize") + ", FILEGROWTH = 10%)"); sql.append("COLLATE Chinese_PRC_CI_AS;"); Statement stmt = this.conn.createStatement(); stmt.execute(sql.toString()); this.conn.commit(); Utils.info("创建用户" + dabaseName + ":" + sql, null); return true; } catch (Exception e) { Utils.error("创建数据库" + dabaseName + "失败！", e); throw e; } } public boolean CreateUser(String username, String passwd) throws Exception { String dabaseName = getDatabaseName(); try { StringBuffer sql = new StringBuffer(); sql.append("exec sp_addlogin '" + username + "','" + passwd + "','" + dabaseName + "';"); sql.append("exec sp_grantdbaccess N'" + username + "';"); sql.append("USE " + dabaseName + ";"); sql.append("EXEC sp_changedbowner '" + username + "';"); Statement stmt = this.conn.createStatement(); stmt.execute(sql.toString()); this.conn.commit(); Utils.info("创建用户" + username + ":" + sql, null); return true; } catch (Exception e) { Utils.error("创建用户" + username + "失败！", e); throw e; } } public String GetSysURL(String url) throws SQLException { String regEx = "databaseName=[^;]*"; Pattern p = Pattern.compile(regEx, 2); Matcher m = p.matcher(url); while (m.find()) { this.databaseName = m.group(0); } String s = m.replaceFirst(""); return s; } public String getDatabaseName() { if (!"".equals(this.databaseName)) { return this.databaseName.substring(13); } return this.databaseName; } public String getSysUser(String username) { return username; } public static void main(String[] args) { try { Connection conn = DBFactory.getConn("sa", "000000", "jdbc:sqlserver://localhost:1433;", "com.microsoft.sqlserver.jdbc.SQLServerDriver"); conn.setCatalog("master"); Aspect sql = new SQLServerAspect(); sql.GetSysURL("jdbc:sqlserver://localhost:1433;databaseName=test103"); sql.setConn(conn); sql.CreateDatabase(sql.GetSysDBPath(), "test103"); sql.CreateUser("test103", "000000"); Connection conn2 = DBFactory.getConn("sa", "000000", "jdbc:sqlserver://localhost:1433;", "com.microsoft.sqlserver.jdbc.SQLServerDriver"); sql.setConn(conn2); sql.CancelDatabase("test103"); sql.CancelUser("test103"); conn.close(); } catch (Exception e) { e.printStackTrace(); } } public void setConn(Connection conn) { this.conn = conn; } public boolean CancelDatabase(String dabaseName) throws Exception { try { StringBuffer sql = new StringBuffer(); sql.append("USE [master];"); sql.append("ALTER DATABASE [" + dabaseName + "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;"); sql.append("ALTER DATABASE [" + dabaseName + "] SET SINGLE_USER;"); sql.append("USE [master];"); sql.append("DROP database " + dabaseName); Statement stmt = this.conn.createStatement(); stmt.execute(sql.toString()); this.conn.commit(); Utils.info("删除数据库" + dabaseName + ":" + sql, null); return true; } catch (Exception e) { Utils.error("删除数据库" + dabaseName + "失败！", e); throw e; } } public boolean CancelUser(String username) throws Exception { try { StringBuffer sql = new StringBuffer(); sql.append("EXEC sp_droplogin '" + username + "';"); sql.append("EXEC sp_dropuser '" + username + "';"); Statement stmt = this.conn.createStatement(); stmt.execute(sql.toString()); this.conn.commit(); Utils.info("删除用户" + username + ":" + sql, null); return true; } catch (Exception e) { Utils.error("删除用户" + username + "失败！", e); throw e; } } public String GetSysDBPath() throws Exception { try { StringBuffer sql = new StringBuffer(); sql.append("select filename as value from master..sysfiles;"); Statement stmt = this.conn.createStatement(); ResultSet rs = stmt.executeQuery(sql.toString()); String path = ""; while (rs.next()) { path = rs.getString("value"); } if (!"".equals(path)) path = path.substring(0, path.lastIndexOf("\\")); return path; } catch (Exception e) { e.printStackTrace(); }throw new Exception("获取数据库默认路径失败！"); } }