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("获取数据库默认路径失败!");
}
}
没有合适的资源?快使用搜索试试~ 我知道了~
批量执行sql脚本文件,支持oracle、sql server2005
共173个文件
java:32个
class:32个
jsp:25个
5星 · 超过95%的资源 需积分: 50 78 下载量 16 浏览量
2011-12-07
10:27:50
上传
评论 1
收藏 9.07MB ZIP 举报
温馨提示
批量执行sql脚本的web工程,通过myeclipse导入工程即可,经过测试可以批量导入oracle与sql server2005的脚本,而且是web页面的向导式执行*.sql脚本文件 支持事务,任何执行过程出现异常,均会提示并且回滚操作,页面利用jquery动态提示脚本的执行过程和进度
资源推荐
资源详情
资源评论
收起资源包目录
批量执行sql脚本文件,支持oracle、sql server2005 (173个子文件)
SystemInfoStep.class 7KB
InstallerServer.class 7KB
SQLServerAspect.class 6KB
SqlFileExecutor.class 6KB
OracleAspect.class 6KB
SysAdminUtil.class 6KB
Utils.class 5KB
DBThread.class 5KB
DatabaseStep.class 5KB
InstallerBean.class 4KB
DBFactory.class 3KB
LicenseStep.class 3KB
DataSourceBean.class 3KB
TestAll.class 3KB
Test.class 2KB
OracleDBConnection.class 2KB
CharacterEncodingFilter.class 2KB
DataSourceTemplet.class 2KB
DBEnvBean.class 2KB
Log4jInit.class 1KB
ExcuteStep.class 1KB
MD5Util.class 1KB
SchemeBean.class 1KB
ResultStep.class 1KB
FileUtil.class 1KB
EmailBean.class 1KB
AbstractStep.class 1KB
AspectFactory.class 924B
FileWrapper.class 901B
Aspect.class 615B
Step.class 603B
ParseSqlFile.class 568B
.classpath 2KB
org.eclipse.wst.common.component 477B
org.eclipse.wst.jsdt.ui.superType.container 49B
main.css 4KB
license.dat 635B
Thumbs.db 8KB
log4j.dtd 5KB
freemarker.ftl 409B
waiting16.gif 2KB
reg1.gif 951B
loading.gif 781B
reg4.gif 634B
reg2.gif 633B
reg3.gif 633B
bg_gradient.gif 265B
splitpane_handle-sn.gif 114B
splitpane_handle-ew.gif 114B
demo1.htm 12KB
badsource.html 741B
badpanel.html 740B
google.html 451B
meta.html 273B
freemarker.html 200B
velocity.html 196B
meta2.html 126B
FormBuilder.jar 4.73MB
ojdbc14.jar 1.47MB
freemarker.jar 683KB
velocity-dep-1.3.1.jar 498KB
dom4j-1.4.jar 475KB
log4j-1.2.8.jar 344KB
sqljdbc.jar 247KB
commons-beanutils.jar 184KB
sitemesh-2.3.jar 178KB
commons-lang-2.0.jar 166KB
commons-collections.jar 161KB
jdom.jar 149KB
oscore-2.2.5.jar 88KB
velocity-tools-view-1.1.jar 63KB
commons-logging.jar 31KB
jspSmartUpload.jar 14KB
SQLServerAspect.java 6KB
OracleAspect.java 5KB
InstallerServer.java 5KB
SystemInfoStep.java 5KB
SqlFileExecutor.java 5KB
SysAdminUtil.java 4KB
Utils.java 3KB
DBThread.java 3KB
DatabaseStep.java 3KB
InstallerBean.java 3KB
DataSourceBean.java 2KB
DBFactory.java 2KB
OracleDBConnection.java 2KB
TestAll.java 2KB
LicenseStep.java 2KB
DataSourceTemplet.java 2KB
Test.java 1KB
DBEnvBean.java 1KB
SchemeBean.java 994B
CharacterEncodingFilter.java 866B
ExcuteStep.java 855B
Aspect.java 854B
Step.java 850B
EmailBean.java 815B
Log4jInit.java 800B
FileUtil.java 800B
MD5Util.java 782B
共 173 条
- 1
- 2
ErekSZ
- 粉丝: 19
- 资源: 28
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功
- 1
- 2
- 3
前往页