import java.io.*;
import java.sql.*;
public class DBConn {
private String ADDRESS ;
private int PORT ;
private String DBNAME ;
private String USERNAME ;
private String PASSWORD ;
private Connection conn ;
private PreparedStatement pstmt ;
private Statement stmt ;
private ResultSet rs ;
/***
* 连接MYSQL数据库
* @return
*/
public Connection getMySqlConn(){
try{
ADDRESS = "127.0.0.1";
PORT = 3306;
DBNAME = "test";
USERNAME = "root";
PASSWORD = "123456";
if(conn==null){
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://"+ADDRESS+":"+PORT+"?user="+USERNAME+"&password="+PASSWORD+"&useUnicode=true&characterEncoding=utf8");
}
}catch(Exception e){
e.printStackTrace();
}
return conn;
}
/***
* 连接MSSQL数据库
* @return
*/
public Connection getMSSqlConn(){
try{
ADDRESS = "127.0.0.1";
PORT = 1433;
DBNAME = "mytest";
USERNAME = "sa";
PASSWORD = "";
if(conn==null){
Class.forName("net.sourceforge.jtds.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:jtds:sqlserver://"+ADDRESS+":"+PORT,USERNAME,PASSWORD);
}
}catch(Exception e){
e.printStackTrace();
}
return conn;
}
/**
* 关闭数据库连接
* @return
*/
public void closeConn(){
try{
if(rs != null){
rs.close();
}
if(pstmt != null){
pstmt.close();
}
if(conn != null || !conn.isClosed()){
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}
}
/***
* 读取SQL文件创建数据库和表
* @return
*/
public boolean CreateDB(){
try{
//获取该类路径
String path = this.getClass().getCanonicalName();
//替换路径中的.
if(path.indexOf(".")!=-1){
path = path.replaceAll("//.", "/");
}
//指定路径到sql文件
path = path.substring(0,path.lastIndexOf("/")+1);
path += "mytest.sql";
File file = new File(path);
String sql = "";
String line = "";
BufferedReader br = new BufferedReader(new FileReader(file));
//读取sql语句
while((line = br.readLine())!=null){
//当前行不是注释
if(line.indexOf("--")==-1){
sql+=line;
}
}
//按照分号截取字符串
String [] sqls = sql.split(";");
//打开数据库连接
if(this.conn==null || this.conn.isClosed()){
this.getMySqlConn();
}
//执行sql语句
stmt = conn.createStatement();
for(String s : sqls){
stmt.addBatch(s);
}
//输出执行次数
System.out.println(stmt.executeBatch().length);
}catch(Exception ex){
ex.printStackTrace();
}finally{
//关闭数据库连接
this.closeConn();
}
return false;
}
public static void main(String [] args){
DBConn db = new DBConn();
db.CreateDB();
}
}
mytest.sql
DROP DATABASE IF EXISTS ABCCS;
CREATE DATABASE ABCCS;
/*
aaaaaaaaaaaaaaaaaaaaaaaaaasdfsdfsdfsdf
*/
----
----sdfsdfsdf
----
USE ABCCS;
DROP TABLE IF EXISTS userinfo;
CREATE TABLE `userinfo` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`username` varchar(50) DEFAULT NULL,
`password` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=latin1;
----
----sdfsdfsdf
----
DROP TABLE IF EXISTS userinfo2;
CREATE TABLE `userinfo2` (
`uid` int(4) NOT NULL,
`uname` varchar(50) DEFAULT NULL,
`upwd` varchar(50) DEFAULT NULL,
PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
----
----sdfsdfsdf
----
DROP TABLE IF EXISTS userinfo3;
CREATE TABLE `userinfo3` (
`uid` int(4) NOT NULL,
`uname` varchar(50) DEFAULT NULL,
`upwd` varchar(50) DEFAULT NULL,
PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `userinfo` VALUES ('10', '456', '456');
INSERT INTO `userinfo` VALUES ('11', '456', '456');
INSERT INTO `userinfo` VALUES ('12', '456', '456');
INSERT INTO `userinfo` VALUES ('13', '456', '456');
INSERT INTO `userinfo` VALUES ('14', '456', '456');
INSERT INTO `userinfo` VALUES ('15', '456', '456');
INSERT INTO `userinfo` VALUES ('16', '456', '456');
INSERT INTO `userinfo` VALUES ('17', '456', '456');
INSERT INTO `userinfo` VALUES ('18', '456', '456');
INSERT INTO `userinfo` VALUES ('19', '456', '456');
INSERT INTO `userinfo` VALUES ('20', '456', '456');
INSERT INTO `userinfo` VALUES ('21', '456', '456');