package com.backup;
|
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.util.ArrayList;
|
import java.util.List;
|
import java.util.regex.Matcher;
|
import java.util.regex.Pattern;
|
|
import com.sql.MysqlConnPool;
|
import com.sql.Sql_Mysql;
|
|
public class DataBaseBackUpThread_SQL{
|
public static final String DataBase_Backup_Table = "tb_database_backup";
|
|
/**
|
* ´´½¨Êý¾Ý¿â±¸·ÝµÄ±í¸ñ
|
* @param conn_pool
|
*/
|
public static void createBackupTable(MysqlConnPool conn_pool){
|
String sql_str = "CREATE TABLE IF NOT EXISTS `" + DataBase_Backup_Table +"` ("
|
+ " `num` int(11) NOT NULL AUTO_INCREMENT,"
|
+ " `database_name` varchar(64) NOT NULL,"
|
+ " `table_name` varchar(64) NOT NULL,"
|
+ " `backup_en` int(11) NOT NULL DEFAULT '0',"
|
+ " `backup_flag` int(11) NOT NULL DEFAULT '0',"
|
+ " `note` varchar(64) NOT NULL DEFAULT '±£Áô',"
|
+ " PRIMARY KEY (`num`)"
|
+ ") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;";
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try {
|
sql.sqlMysqlUseDB(Sql_Mysql.WEB_Site);
|
sql.sqlMySqlExecute(sql_str); //´´½¨Êý¾Ý¿â±¸·Ý±í
|
} catch (Exception e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ÏòÊý¾Ý¿âÖÐÌí¼Ó±¸·ÝÊý¾Ý¿â¼°±íµÄ¼Ç¼
|
* @param conn_pool
|
*/
|
public static void inseartBackupTable(MysqlConnPool conn_pool,List<DatabaseTable> list){
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
ArrayList<String> al_sql_strs = new ArrayList<>();
|
for(int i=0;i<list.size();i++){
|
if(!checkTableIsExist(conn_pool,list.get(i))) {
|
al_sql_strs.add("INSERT INTO `tb_database_backup`(database_name,table_name,backup_en,backup_flag,note) "
|
+ "VALUES ('"+list.get(i).getDatabase_name()+"', '"+list.get(i).getTable_name()+"', '0', '0', '');");
|
}
|
}
|
try {
|
if(list.size()>0){
|
sql.sqlMysqlUseDB(Sql_Mysql.WEB_Site);
|
sql.makeManualCommit(al_sql_strs);
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ÅжÏÖ¸¶¨Êý¾Ý¿âÖбíµÄ¼Ç¼ÊÇ·ñ´æÔÚ
|
* @param pool
|
* @param table
|
* @return
|
*/
|
public static boolean checkTableIsExist(MysqlConnPool pool,DatabaseTable table) {
|
boolean isExist = false;
|
String sql_str = " SELECT num"
|
+ " FROM " + Sql_Mysql.Tb_DataBase_Backup
|
+ " WHERE database_name ='"+ table.getDatabase_name() + "' AND table_name='"+table.getTable_name()+"'";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet rs = null;
|
//System.out.println(sql_str);
|
rs = sql.sqlMysqlQuery(sql_str);
|
try {
|
if(rs.next()) {
|
isExist = true;
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
if(rs != null) {
|
try {
|
rs.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
return isExist;
|
}
|
|
/**
|
* ²éѯËùÓеÄÊý¾Ý¿âÒÔ¼°¶ÔÓ¦µÄ±íÃû³Æ
|
* @param conn_pool
|
* @return
|
*/
|
public static List<DatabaseTable> searchAllDataBaseAndTable(MysqlConnPool conn_pool){
|
List<DatabaseTable> list = new ArrayList<DatabaseTable>();
|
String sql_database_str = " SHOW DATABASES ";
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
List<DatabaseTable> dbs = new ArrayList<DatabaseTable>();
|
try {
|
ResultSet rs = sql.sqlMysqlQuery(sql_database_str);
|
while(rs.next()){
|
if(!rs.getString("database").equals("information_schema")
|
&&!rs.getString("database").equals("mysql")
|
&&!rs.getString("database").equals("performance_schema")
|
&&!rs.getString("database").equals("sys")
|
&&!isSpecialChar(rs.getString("database"))){ //ÌÞ³ýº¬ÓÐÌØÊâ×Ö·ûµÄÊý¾Ý¿â
|
DatabaseTable db = new DatabaseTable();
|
db.setDatabase_name(rs.getString("database"));
|
dbs.add(db);
|
}
|
}
|
|
String sql_table_str = "";
|
for(int i = 0;i<dbs.size();i++){
|
sql_table_str = "SELECT table_name FROM information_schema.tables "
|
+ "WHERE table_schema='"+dbs.get(i).getDatabase_name()+"'";
|
rs = sql.sqlMysqlQuery(sql_table_str);
|
while(rs.next()){
|
DatabaseTable obj = dbs.get(i).clone();
|
obj.setTable_name(rs.getString("table_name"));
|
if(!isSpecialChar(obj.getTable_name())) { //ÌÞ³ýº¬ÓÐÌØÊâ×Ö·ûµÄÊý¾Ý¿â±í
|
list.add(obj);
|
}
|
}
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}finally {
|
sql.close_con();
|
}
|
return list;
|
}
|
|
/**
|
* ²éѯÊý¾Ý¿âÖи÷ÖÖ±íµÄ״̬
|
* @param conn_pool
|
* @return
|
*/
|
public static List<DatabaseTable> searAllTableState(MysqlConnPool conn_pool){
|
List<DatabaseTable> list = new ArrayList<DatabaseTable>();
|
String sql_str = " SELECT num,database_name,table_name,backup_en,backup_flag,note "
|
+ " FROM tb_database_backup Where backup_en=1 ";
|
Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
|
try {
|
sql.sqlMysqlUseDB(Sql_Mysql.WEB_Site);
|
ResultSet rs = sql.sqlMysqlQuery(sql_str);
|
while(rs.next()){
|
DatabaseTable db = new DatabaseTable();
|
db.setNum(rs.getInt("num"));
|
db.setDatabase_name(rs.getString("database_name"));
|
db.setTable_name(rs.getString("table_name"));
|
db.setBack_en(rs.getInt("backup_en"));
|
db.setBackup_flag(rs.getInt("backup_flag"));
|
db.setNote(rs.getString("note"));
|
list.add(db);
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
return list;
|
}
|
|
/**
|
* ÉèÖÃÊý¾Ý¿âÏ̵߳İ汾ºÅºÍÏ̵߳ÄÆô¶¯Ê±¼ä
|
* @param pool
|
* @param mVersionnum
|
*/
|
public static void DataBaseBackUp_StartTime(MysqlConnPool pool, float mVersionnum) {
|
String sql_str = "";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlUseDB(Sql_Mysql.WEB_Site);
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ÐÞ¸ÄÖ¸¶¨Êý¾Ý¿âµÄÖ¸¶¨±íµÄ״̬
|
* @param pool
|
* @param obj
|
*/
|
public static void UpdateDataBaseState(MysqlConnPool pool,DatabaseTable obj){
|
String sql_str = " UPDATE " + Sql_Mysql.Tb_DataBase_Backup
|
+ " SET backup_en = " + obj.getBack_en()
|
+ " WHERE database_name='"+obj.getDatabase_name()+"' "
|
+ " AND table_name = '"+obj.getTable_name()+"'";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
//sql.sqlMysqlUseDB(Sql_Mysql.WEB_Site);
|
sql.sqlMysqlExecute(sql_str);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ÅжÏÊÇ·ñº¬ÓÐÌØÊâ×Ö·û
|
*
|
* @param str
|
* @return trueΪ°üº¬£¬falseΪ²»°üº¬
|
*/
|
public static boolean isSpecialChar(String str) {
|
String regEx = "[ \\/:*?\"<>|]";
|
Pattern p = Pattern.compile(regEx);
|
Matcher m = p.matcher(str);
|
return m.find();
|
}
|
|
|
public static void main(String[] args) {
|
MysqlConnPool pool = new MysqlConnPool("127.0.0.1",3360,5);
|
//System.out.println(searchAllDataBaseAndTable(pool));
|
createBackupTable(pool);
|
List<DatabaseTable> list = searchAllDataBaseAndTable(pool);
|
System.out.println(list);
|
inseartBackupTable(pool,list);
|
}
|
}
|
