package com.as.upgrade;
import java.io.FileInputStream;
import java.io.InputStream;
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 Tools {
private static Connection conn = null;
public static PreparedStatement ps = null;
private static ResultSet rs = null;
/*
* 读取本地txt文件
* sqlFile 文件路径
*/
public static List<String> loadFile(String sqlFile) throws Exception{
// 创建list集合
List<String> sqlList = new ArrayList<String>();
// 读取文件的内容并写道StringBuffer中去
InputStream sqlFileIn = new FileInputStream(sqlFile);
// 创建StringBuffer
StringBuffer sqlSb = new StringBuffer();
// 创建byte数组
byte[] buff = new byte[sqlFileIn.available()];
int byteRead = 0;
while ((byteRead = sqlFileIn.read(buff)) != -1) {
sqlSb.append(new String(buff, 0, byteRead));
}
//去除字符
String sqlArr[] = sqlSb.toString().split("(;\\s*\\rr\\n)|(;\\s*\\n)");
for (int i = 0; i < sqlArr.length; i++) {
String sql = sqlArr[i].replaceAll(" --.* ", "").trim();
if (!"".equals(sql)) {
sqlList.add(sql);
}
}
return sqlList;
}
/*
* 查询siterule表取id
* 放入list集合
*/
public static List<String> getSiterulebyId() throws Exception{
//创建list集合
List<String> list = new ArrayList<String>();
//获取数据库连接
conn = jdbcuitl.getConnection();
//sql语句
String sql = "select id from siterule";
try {
//创建prepareStatement
ps = conn.prepareStatement(sql);
//执行
rs = ps.executeQuery();
//循环打印
while(rs.next()){
//取id放入list集合
list.add(rs.getString(1));
}
return list;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return null;
}finally{
//释放连接
if (rs != null)
rs.close();
if (ps != null)
ps.close();
if (conn != null)
conn.close();
}
}
/*
* 查询subject表取id
* 放入list集合
*/
public static List<String> getSubjectByid() throws Exception{
//创建list集合
List<String> list = new ArrayList<String>();
//获取数据库连接
conn = jdbcuitl.getConnection();
//sql语句
String sql = "select id from subject";
try {
//创建prepareStatement
ps = conn.prepareStatement(sql);
//执行
rs = ps.executeQuery();
//循环打印
while(rs.next()){
//取id放入list集合
list.add(rs.getString(1));
}
return list;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return null;
}finally{
//释放连接
if (rs != null)
rs.close();
if (ps != null)
ps.close();
if (conn != null)
conn.close();
}
}
}
package com.as.upgrade;
import java.sql.DriverManager;
import java.sql.Connection;
public class jdbcuitl {
private static Connection connection = null;
private static final String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8";
private static final String Driver = "com.mysql.jdbc.Driver";
private static final String username = "root";
private static final String password = "123456";
public static Connection getConnection(){
try {
//加载驱动
Class.forName(Driver);
connection = DriverManager.getConnection(url,username,password);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return connection;
}
}
package com.as.upgrade;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class SiteruleDatabase {
private static Connection conn = null;
private static Statement stmt = null;
/*
* siterule
* 读取文件 插入数据库
* sqlFile 文件路径
*/
public void executeInsert(String sqlFile) throws Exception{
//读取文件
List<String> loadFile = Tools.loadFile(sqlFile);
//创建map集合
Map<String, String> map = new HashMap<String, String>();
//迭代取ID放入map集合
for (String string : loadFile) {
//判断ID是否为空
if(!"".equals(string.substring(string.indexOf("('")+2, string.indexOf("',")))){
//截取ID存放Key,value存放sql语句
map.put(string.substring(string.indexOf("('")+2, string.indexOf("',")), string);
}else{
continue;
}
}
//调用Tools的getSiterulebyId()
List<String> siterulebyId = Tools.getSiterulebyId();
//遍历去除相同ID的sql
for (String string : siterulebyId) {
//删除相同ID
map.remove(string);
}
try {
//获取连接数据库
conn = jdbcuitl.getConnection();
conn.setAutoCommit(false);
stmt = conn.createStatement();
//遍历map集合
for (String string : map.keySet()) {
System.out.println(map.get(string));
//批量添加
stmt.addBatch(map.get(string));
}
//执行
stmt.executeBatch();
conn.commit();
System.out.println("数据跟新成功");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
conn.rollback();
} finally{
//释放连接
if(conn!=null){conn.close();}
if(stmt!=null){stmt.close();}
}
}
//测试
public static void main(String[] args) throws Exception {
SiteruleDatabase insertDatabase = new SiteruleDatabase();
insertDatabase.executeInsert("file/siterule.txt");
}
}
package com.as.upgrade;
import java.sql.Connection;
import java.sql.Statement;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class SubjectDatabase {
private static Connection conn = null;
private static Statement stmt = null;
/*
* Subject
* 读取文件 插入数据库
* sqlFile 文件路径
*/
public void executeInsert(String sqlFile) throws Exception{
//读取文件
List<String> loadFile = Tools.loadFile(sqlFile);
//创建map集合
Map<String, String> map = new HashMap<String, String>();
//迭代取ID放入map集合
for (String string : loadFile) {
//截取ID存放Key,value存放sql语句
map.put(string.substring(string.indexOf("('")+2, string.indexOf("',")), string);
}
//调用Tools的getSubjectByid()
List<String> subjectByid = Tools.getSubjectByid();
//遍历去重复
for (String string : subjectByid) {
//删除ID相同的key
map.remove(string);
}
try {
//获取连接数据库
conn = jdbcuitl.getConnection();
conn.setAutoCommit(false);
stmt = conn.createStatement();
//遍历map
for (String string : map.keySet()) {
//批量添加
stmt.addBatch(map.get(string));
System.out.println(map.get(string));
}
//执行
stmt.executeBatch();
conn.commit();
System.out.println("数据添加成功!");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
conn.rollback();
}finally{
//释放连接
if(conn!=null){conn.close();}
if(stmt!=null){stmt.close();}
}
}
//测试
public static void main(String[] args) throws Exception {
SubjectDatabase subjectDatabase = new SubjectDatabase();
subjectDatabase.executeInsert("file/subject.txt");
}
}