package com.database_util;
|
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
|
import com.sql.MysqlConnPool;
|
import com.sql.Sql_Mysql;
|
|
public class DB_db_user {
|
|
|
public static void init(MysqlConnPool pool, boolean recreate) {
|
|
createDB_UserDB(pool); //´´½¨db_userÊý¾Ý¿â
|
|
createTb_user_infTable(pool, recreate); //´´½¨Óû§±í
|
|
}
|
|
/**
|
* ´´½¨ db_user Êý¾Ý¿â
|
* @param pool
|
*/
|
public static void createDB_UserDB(MysqlConnPool pool) {
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS " + Sql_Mysql.DB_USER);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ´´½¨Ïß³ÌÊÖ¶¯Æô¶¯¹¤¾ß±í
|
*/
|
public static void createTb_user_infTable(MysqlConnPool pool, boolean recreate) {
|
String sql_str01 = " DROP TABLE IF EXISTS "+Sql_Mysql.TB_UserInf;
|
String sql_str02 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.TB_UserInf + " (" +
|
" `uId` bigint(20) NOT NULL AUTO_INCREMENT," +
|
" `uSnId` varchar(50) NOT NULL DEFAULT '123456'," +
|
" `uName` varchar(50) NOT NULL DEFAULT ' '," +
|
" `uShenFenId` varchar(50) NOT NULL DEFAULT ' '," +
|
" `uEmployeeId` varchar(50) NOT NULL DEFAULT ' '," +
|
" `uTelephone` varchar(20) NOT NULL DEFAULT '0'," +
|
" `uMobilephone` varchar(20) NOT NULL DEFAULT '0'," +
|
" `uEmail` varchar(100) NOT NULL DEFAULT '0'," +
|
" `uAddr` varchar(200) NOT NULL DEFAULT ' '," +
|
" `uBirthDay` date NOT NULL DEFAULT '1980-01-01'," +
|
" `uAccessionDay` date NOT NULL DEFAULT '2001-01-01'," +
|
" `uSex` varchar(10) NOT NULL DEFAULT 'u'," +
|
" `uDepartment` varchar(50) NOT NULL DEFAULT ' '," +
|
" `uProTitle` varchar(50) NOT NULL DEFAULT ' '," +
|
" `uAuthority` varchar(50) NOT NULL DEFAULT ' '," +
|
" `uJobGroup` varchar(50) NOT NULL DEFAULT ' '," +
|
" `uDuties` varchar(50) NOT NULL DEFAULT ' '," +
|
" `uTasks` varchar(50) NOT NULL DEFAULT ' '," +
|
" `uBaojiUsr` tinyint(1) NOT NULL DEFAULT '1'," +
|
" `uNote` varchar(200) NOT NULL DEFAULT ' '," +
|
" `upassword` varchar(64) NOT NULL DEFAULT ''," +
|
" `uPubKeyX` varchar(200) NOT NULL DEFAULT ''," +
|
" `uPubKeyY` varchar(200) NOT NULL DEFAULT ''," +
|
" `uKey_ID` varchar(200) NOT NULL DEFAULT ''," +
|
" PRIMARY KEY (`uId`)," +
|
" UNIQUE KEY `user_id` (`uId`)" +
|
") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet rs = null;
|
try {
|
// if(true == recreate) {
|
// sql.sqlMysqlExecute(sql_str01);
|
// }
|
sql.sqlMysqlUseDB(Sql_Mysql.DB_USER);
|
sql.sqlMysqlExecute(sql_str02);
|
|
//Ìí¼Ó¼ÓÃܸ澯upassword
|
ResultSet res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_battinf'"
|
+ " AND table_name='tb_user_inf'"
|
+ " AND column_name='upassword'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.DeverrorRecord_Table
|
+ " ADD COLUMN `upassword` varchar(64) NOT NULL DEFAULT ''");
|
}
|
|
//Ìí¼Ó¹«Ô¿X
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_battinf'"
|
+ " AND table_name='tb_user_inf'"
|
+ " AND column_name='uPubKeyX'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.DeverrorRecord_Table
|
+ " ADD COLUMN `uPubKeyX` varchar(200) NOT NULL DEFAULT ''");
|
}
|
|
//Ìí¼Ó¹«Ô¿Y
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_battinf'"
|
+ " AND table_name='tb_user_inf'"
|
+ " AND column_name='uPubKeyY'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.DeverrorRecord_Table
|
+ " ADD COLUMN `uPubKeyY` varchar(200) NOT NULL DEFAULT ''");
|
}
|
|
//Ìí¼ÓuKey_ID
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_battinf'"
|
+ " AND table_name='tb_user_inf'"
|
+ " AND column_name='uKey_ID'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.DeverrorRecord_Table
|
+ " ADD COLUMN `uKey_ID` varchar(200) NOT NULL DEFAULT ''");
|
}
|
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
}
|
