UBSデータベースの実際のデータのオンライン処理
-1.スクリプトを使用してkasaiデータベースを構築し、初期権限に関する情報とスーパーユーザー情報を挿入します.データベース・スクリプトは次のとおりです.
0.ライブラリ作成スクリプトを使用してターゲットマシンに作成し、tb_domain,tb_prefixテーブル、urlフィールドにインデックスを作成することに注意してください.後のスクリプトには含まれません.ライブラリ作成スクリプトは次のとおりです.
1.リアルデータdmpファイルを、ターゲットmysqlの異なるシナリオ(test)にインポートします.すなわち、バッファシナリオです.
2.insert select文により、ドメイン名-"接頭辞-"デッドロン-"の価値のない順序で、バッファスキームからsogou_にインポートblacklistスキームの2つの主要なテーブルにあります.構文は次のとおりです.
2.0自増フィールドは1からとし、ドメイン名テーブルデータの整理を容易にする
alter table sogou_blacklist.tb_domain AUTO_INCREMENT = 1;--------ドメイン名------2.1バッファリングスキーム【ドメイン名テーブル】のレコードをsogou_blacklistスキーム【ドメイン名テーブル】にインポートし、追加属性を【一般】insert into sogou_blacklist.tb_domain(url,tag_block,tag_reversible,reserve_id_str,extra_attr,`user`,sub_date)に設定するselect url,1-`type`,0,','普通',user,sub_date from test.domain;---------プレフィックス------2.2バッファスキーム【プレフィックステーブル】のレコードをsogou_blacklistスキーム【プレフィックステーブル】にインポートし、追加属性を【一般】insert into sogou_blacklist.tb_prefix(url,tag_selfinclude,tag_block,tag_reversible,reserve_id_str,extra_attr,`user`,sub_date)に設定するselect url,1-`type`,1-black,0,','普通',user,sub_date from test.url;--------デッドライン------2.3バッファリングスキーム【デッドラインテーブル】の【ドメイン名】レコードをsogou_blacklistスキーム【ドメイン名テーブル】にインポートし、追加属性を【デッドライン】insert into sogou_blacklist.tb_domain(url,tag_block,tag_reversible,reserve_id_str,extra_attr,`user`,sub_date)に設定するselect url,1,0,','デッドロック',user,sub_date from test.deadlink where type=1;2.4バッファ方式【デッドチェーンテーブル】の【接頭辞】レコードをsogou_blacklist方式【接頭辞テーブル】にインポートし、追加属性を【デッドチェーン】insert into sogou_blacklist.tb_prefix(url,tag_selfinclude,tag_block,tag_reversible,reserve_id_str,extra_attr,`user`,sub_date)に設定するselect url,1,1,0,','デッドロック',user,sub_date from test.deadlink where type=0;--------無価値------2.5バッファリングスキーム【無価値テーブル】の【無価値】レコードをsogou_blacklistスキーム【ドメイン名テーブル】にインポートし、追加属性を【無価値】insert into sogou_blacklist.tb_domain(url,tag_block,tag_reversible,reserve_id_str,extra_attr,`user`,sub_date)に設定するselect url,1,0,','無価値',user,sub_date from test.novalue where type=0;2.6バッファリングスキーム【無価値テーブル】のプレフィックス形式の【非中国語】レコードをdelete from test.novalue where url like'http://%';2.7バッファリングスキーム【無価値テーブル】の【非中国語】レコードを削除し、sogou_blacklistスキーム【ドメイン名テーブル】にインポートし、追加属性を【非中国語】insert into sogou_blacklist.tb_domain(url,tag_block,tag_reversible,reserve_id_str,extra_attr,`user`,sub_date)select url,1,0,','非中国語',user,sub_date from test.novalue where type=1;
2.8【ドメイン名テーブル】のsub_dateフィールドの0000-00-00 00 00:00を当日レコードに変換
update sogou_blacklist.tb_domain set sub_date=NOW() where sub_date like '%0000-00-00 00:00:00%'
2.9【接頭辞表】のsub_dateフィールドに0000を含むレコードを当日に変換する
update sogou_blacklist.tb_prefix set sub_date=NOW() where sub_date like '%0000%'3.実行順は
(2.6)オプションドメイン名表2.12.32.52.7(2.8)オプション接頭辞表2.22.4
2.9
4.関連するバックバンクJAVAプログラム
Dump.java
JDBCUtils.java
-- KASAI
-- for MySQL 4.x and above
CREATE TABLE kasai_users
(
id VARCHAR(50) NOT NULL,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(254),
password VARCHAR(254),
blocked INT,
description VARCHAR(254),
data TEXT,
super_user TINYINT default 0,
PRIMARY KEY (id)
) TYPE=InnoDB;
CREATE TABLE kasai_groups
(
id VARCHAR(50) NOT NULL,
description VARCHAR(254),
blocked INT,
system TINYINT default 0,
data TEXT,
PRIMARY KEY CLUSTERED (id)
) TYPE=InnoDB;
CREATE TABLE kasai_users_groups
(
id_user VARCHAR(50) NOT NULL,
id_group VARCHAR(50) NOT NULL,
PRIMARY KEY (id_user,id_group ),
INDEX IDX_kasai_users_groups_user (id_user),
INDEX IDX_kasai_users_groups_group (id_group),
CONSTRAINT FK_users_groups_users FOREIGN KEY( id_user )
REFERENCES kasai_users ( id )
ON UPDATE NO ACTION
ON DELETE CASCADE,
CONSTRAINT FK_users_groups_groups FOREIGN KEY( id_group )
REFERENCES kasai_groups ( id )
ON UPDATE NO ACTION
ON DELETE CASCADE
) TYPE=InnoDB;
CREATE TABLE kasai_operatives
(
id VARCHAR(254) NOT NULL,
sequence INT,
description VARCHAR(254),
PRIMARY KEY CLUSTERED (id )
) TYPE=InnoDB;
CREATE TABLE kasai_objects
(
id VARCHAR(254) NOT NULL,
PRIMARY KEY (id )
) TYPE=InnoDB;
CREATE TABLE kasai_roles
(
id int auto_increment NOT NULL,
name VARCHAR(50) NOT NULL,
description VARCHAR(254),
PRIMARY KEY (id )
) TYPE=InnoDB;
CREATE TABLE kasai_roles_operatives
(
id_role int NOT NULL,
id_operative VARCHAR(254) NOT NULL,
PRIMARY KEY (id_role,id_operative ),
INDEX IDX_kasai_roles_operatives_role (id_role),
INDEX IDX_kasai_roles_operatives_operative (id_operative),
CONSTRAINT FK_roles_operatives_operatives FOREIGN KEY( id_operative )
REFERENCES kasai_operatives ( id )
ON UPDATE NO ACTION
ON DELETE CASCADE,
CONSTRAINT FK_roles_operatives_roles FOREIGN KEY( id_role )
REFERENCES kasai_roles ( id )
ON UPDATE NO ACTION
ON DELETE CASCADE
) TYPE=InnoDB;
CREATE TABLE kasai_objects_users_roles(
id int auto_increment NOT NULL,
id_object VARCHAR(254) NOT NULL,
id_user VARCHAR(50) NOT NULL,
id_role int NOT NULL,
PRIMARY KEY (id ),
UNIQUE (id_object, id_user, id_role),
INDEX IDX_kasai_objects_users_roles_object (id_object),
INDEX IDX_kasai_objects_users_roles_user (id_user),
INDEX IDX_kasai_objects_users_roles_role (id_role),
CONSTRAINT FK_objects_users_roles_roles FOREIGN KEY( id_role )
REFERENCES kasai_roles ( id )
ON UPDATE NO ACTION
ON DELETE CASCADE,
CONSTRAINT FK_objects_users_roles_objects FOREIGN KEY( id_object )
REFERENCES kasai_objects ( id )
ON UPDATE NO ACTION
ON DELETE CASCADE,
CONSTRAINT FK_objects_users_roles_users FOREIGN KEY( id_user )
REFERENCES kasai_users ( id )
ON UPDATE NO ACTION
ON DELETE CASCADE
) TYPE=InnoDB;
CREATE TABLE kasai_objects_groups_roles(
id int auto_increment NOT NULL,
id_object VARCHAR(254) NOT NULL,
id_group VARCHAR(50) NOT NULL,
id_role int NOT NULL,
PRIMARY KEY (id ),
INDEX IDX_kasai_objects_groups_roles_object (id_object),
INDEX IDX_kasai_objects_groups_roles_group (id_group),
INDEX IDX_kasai_objects_groups_roles_role (id_role),
UNIQUE (id_object, id_group, id_role),
CONSTRAINT FK_objects_groups_roles_roles FOREIGN KEY( id_role )
REFERENCES kasai_roles ( id )
ON UPDATE NO ACTION
ON DELETE CASCADE,
CONSTRAINT FK_objects_groups_roles_objects FOREIGN KEY( id_object )
REFERENCES kasai_objects ( id )
ON UPDATE NO ACTION
ON DELETE CASCADE,
CONSTRAINT FK_objects_groups_roles_groups FOREIGN KEY( id_group )
REFERENCES kasai_groups ( id )
ON UPDATE NO ACTION
ON DELETE CASCADE
) TYPE=InnoDB;
CREATE TABLE kasai_audit(
audit_id INT AUTO_INCREMENT NOT NULL,
user_id VARCHAR(50) NOT NULL,
date_time DATETIME NOT NULL,
return_code INT NOT NULL,
error_description VARCHAR(254),
duration INT,
client_ip VARCHAR(15),
operation VARCHAR(254) NOT NULL,
object_id VARCHAR(254),
transaction_data TEXT,
PRIMARY KEY (audit_id)
) TYPE=InnoDB;
-- Operatives
INSERT INTO kasai_operatives (id,sequence,description) VALUES('kasai','1','All permissions over Kasai');
INSERT INTO kasai_operatives (id,sequence,description) VALUES('kasai.object.modifyaccess','2','Modify an object permissions');
INSERT INTO kasai_operatives (id,sequence,description) VALUES('kasai.user','5','All permissions involving users');
INSERT INTO kasai_operatives (id,sequence,description) VALUES('kasai.user.read','6','Read user information');
INSERT INTO kasai_operatives (id,sequence,description) VALUES('kasai.user.delete','7','Delete a user');
INSERT INTO kasai_operatives (id,sequence,description) VALUES('kasai.user.commit','8','Create and update a users information');
INSERT INTO kasai_operatives (id,sequence,description) VALUES('kasai.user.resetpassword','9','Reset a users password');
INSERT INTO kasai_operatives (id,sequence,description) VALUES('kasai.user.block','10','Block a user');
INSERT INTO kasai_operatives (id,sequence,description) VALUES('kasai.user.unblock','11','Unblock a user');
INSERT INTO kasai_operatives (id,sequence,description) VALUES('kasai.group','12','All permissions involving groups');
INSERT INTO kasai_operatives (id,sequence,description) VALUES('kasai.group.read','13','Read group information');
INSERT INTO kasai_operatives (id,sequence,description) VALUES('kasai.group.delete','14','Delete a group');
INSERT INTO kasai_operatives (id,sequence,description) VALUES('kasai.group.commit','15','Create and update a groups information');
INSERT INTO kasai_operatives (id,sequence,description) VALUES('kasai.group.block','16','Block a group');
INSERT INTO kasai_operatives (id,sequence,description) VALUES('kasai.group.unblock','17','Unblock a group');
INSERT INTO kasai_operatives (id,sequence,description) VALUES('kasai.group.user.delete','18','Remove users from a group');
INSERT INTO kasai_operatives (id,sequence,description) VALUES('kasai.group.user.add','19','Add users to a group');
INSERT INTO kasai_operatives (id,sequence,description) VALUES('kasai.role.commit','20','Create and update a role information');
INSERT INTO kasai_operatives (id,sequence,description) VALUES('kasai.role.read','21','Read role information');
INSERT INTO kasai_operatives (id,sequence,description) VALUES('kasai.role.delete','22','Delete a role');
INSERT INTO kasai_operatives (id,sequence,description) VALUES('blacklist.add',100,'add');
INSERT INTO kasai_operatives (id,sequence,description) VALUES('blacklist.delete',101,'delete');
INSERT INTO kasai_operatives (id,sequence,description) VALUES('blacklist.edit',102,'edit');
INSERT INTO kasai_operatives (id,sequence,description) VALUES('blacklist.query',103,'query');
INSERT INTO kasai_operatives (id,sequence,description) VALUES('blacklist.detail',104,'detail');
-- Groups
insert into kasai_groups (id,description,blocked,data) values ('Administrators','Administrators group',0,null);
insert into kasai_groups (id,description,blocked,system,data) values ('AllUsers','All system users',0,1,null);
-- Users
-- This two sample users have the password set to "password" using a SHA hashing algorithm
insert into kasai_users (id, password,blocked, super_user) values ('admin','5b-56-61-1c-37-47-3f-3f-6-7e-25-b-6c-8-33-1b-7e-1a-71-28',0,1);
insert into kasai_users (id, password,blocked, super_user) values ('guest','5b-56-61-1c-37-47-3f-3f-6-7e-25-b-6c-8-33-1b-7e-1a-71-28',0,0);
-- user/group
insert into kasai_users_groups (id_user,id_group) values ('admin','Administrators');
insert into kasai_users_groups (id_user,id_group) values ('admin','AllUsers');
insert into kasai_users_groups (id_user,id_group) values ('guest','AllUsers');
-- operatives/roles
insert into kasai_roles (id,name,description) values (1,'Administrator','Can perform any action');
insert into kasai_roles (id,name,description) values (2,'Guest','Can not perform any action by default');
insert into kasai_roles (id,name,description) values (3,'User','Can read users, roles and groups information');
----Administrator Role
insert into kasai_roles_operatives values (1,'kasai');
---User Role
insert into kasai_roles_operatives values (3,'kasai.group.read');
insert into kasai_roles_operatives values (3,'kasai.role.read');
insert into kasai_roles_operatives values (3,'kasai.user.read');
-- Entities
insert into kasai_objects values ('/kasai/');
insert into kasai_objects values ('/kasai/user/');
insert into kasai_objects values ('/kasai/group/');
insert into kasai_objects values ('/kasai/role/');
insert into kasai_objects values ('/kasai/user/admin');
insert into kasai_objects values ('/kasai/user/guest');
insert into kasai_objects values ('/kasai/group/Administrators');
insert into kasai_objects values ('/kasai/group/AllUsers');
insert into kasai_objects values ('/kasai/role/1');
insert into kasai_objects values ('/kasai/role/2');
insert into kasai_objects values ('/kasai/role/3');
-- Assign permissions on objects
insert into kasai_objects_groups_roles values (1,'/kasai/','Administrators',1);
-- ，
-- user
INSERT INTO kasai_users(id,first_name,last_name,email,password,blocked,super_user) VALUES('[email protected]','empty','empty','[email protected]','empty',0,0);
-- object
INSERT INTO kasai_objects(id) VALUES ('kasai/kasai_objects');
INSERT INTO kasai_objects(id) VALUES ('kasai/kasai_objects_users_roles');
INSERT INTO kasai_objects(id) VALUES ('kasai/kasai_operatives');
INSERT INTO kasai_objects(id) VALUES ('kasai/kasai_roles');
INSERT INTO kasai_objects(id) VALUES ('kasai/kasai_roles_operatives');
INSERT INTO kasai_objects(id) VALUES ('kasai/kasai_users');
INSERT INTO kasai_objects(id) VALUES ('test/tb_domain');
INSERT INTO kasai_objects(id) VALUES ('test/tb_prefix');
-- role
INSERT INTO kasai_roles(id,name,description) VALUES (9,'owner_test','Permission to add,delete,modify,list');
INSERT INTO kasai_roles(id,name,description) VALUES (10,'viewer_test','Permission to list');
INSERT INTO kasai_roles(id,name,description) VALUES (11,'guest_test','Permission to do nothing');
-- user-object-role
INSERT INTO kasai_objects_users_roles(id_object,id_user,id_role) VALUES ('kasai/kasai_objects','[email protected]',9);
INSERT INTO kasai_objects_users_roles(id_object,id_user,id_role) VALUES ('kasai/kasai_objects_users_roles','[email protected]',9);
INSERT INTO kasai_objects_users_roles(id_object,id_user,id_role) VALUES ('kasai/kasai_operatives','[email protected]',9);
INSERT INTO kasai_objects_users_roles(id_object,id_user,id_role) VALUES ('kasai/kasai_roles','[email protected]',9);
INSERT INTO kasai_objects_users_roles(id_object,id_user,id_role) VALUES ('kasai/kasai_roles_operatives','[email protected]',9);
INSERT INTO kasai_objects_users_roles(id_object,id_user,id_role) VALUES ('kasai/kasai_users','[email protected]',9);
INSERT INTO kasai_objects_users_roles(id_object,id_user,id_role) VALUES ('test/tb_domain','[email protected]',9);
INSERT INTO kasai_objects_users_roles(id_object,id_user,id_role) VALUES ('test/tb_prefix','[email protected]',9);
-- operative
INSERT INTO kasai_operatives(id,sequence,description) VALUES ('list_test',1000,'List');
INSERT INTO kasai_operatives(id,sequence,description) VALUES ('add_test',1001,'Add');
INSERT INTO kasai_operatives(id,sequence,description) VALUES ('delete_test',1002,'Delete');
INSERT INTO kasai_operatives(id,sequence,description) VALUES ('modify_test',1003,'Modify');
-- role-operative
INSERT INTO kasai_roles_operatives(id_role,id_operative) VALUES (9,'add_test');
INSERT INTO kasai_roles_operatives(id_role,id_operative) VALUES (9,'delete_test');
INSERT INTO kasai_roles_operatives(id_role,id_operative) VALUES (9,'list_test');
INSERT INTO kasai_roles_operatives(id_role,id_operative) VALUES (9,'modify_test');
INSERT INTO kasai_roles_operatives(id_role,id_operative) VALUES (10,'list_test');0.ライブラリ作成スクリプトを使用してターゲットマシンに作成し、tb_domain,tb_prefixテーブル、urlフィールドにインデックスを作成することに注意してください.後のスクリプトには含まれません.ライブラリ作成スクリプトは次のとおりです.
CREATE TABLE `tb_actlog` (
`id` int(10) NOT NULL auto_increment,
`db_name` varchar(255) NOT NULL,
`db_type` int(1) NOT NULL,
`db_url` varchar(255) NOT NULL,
`db_user` varchar(255) NOT NULL,
`act` int(1) NOT NULL,
`sub_date` TIMESTAMP(14),
`ip` varchar(255) NOT NULL,
`info` varchar(255) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=gbk;
DROP TABLE IF EXISTS `tb_domain`;
CREATE TABLE `tb_domain` (
`id` int(10) NOT NULL auto_increment,
`url` varchar(255) NOT NULL,
`tag_block` int(1) NOT NULL,
`tag_reversible` int(1) NOT NULL,
`reserve_id_str` varchar(255) NOT NULL,
`extra_attr` varchar(50) NOT NULL,
`user` varchar(255) NOT NULL,
`sub_date` TIMESTAMP(14),
`info` varchar(255) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=gbk;
DROP TABLE IF EXISTS `tb_prefix`;
CREATE TABLE `tb_prefix` (
`id` int(10) NOT NULL auto_increment,
`url` varchar(255) NOT NULL,
`tag_selfinclude` int(1) NOT NULL,
`tag_block` int(1) NOT NULL,
`tag_reversible` int(1) NOT NULL,
`reserve_id_str` varchar(255) NOT NULL,
`extra_attr` varchar(50) NOT NULL,
`user` varchar(255) NOT NULL,
`sub_date` TIMESTAMP(14),
`info` varchar(255) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=gbk;
DROP TABLE IF EXISTS `tb_protective_white`;
CREATE TABLE `tb_protective_white` (
`id` int(10) NOT NULL auto_increment,
`url` varchar(255) NOT NULL,
`type` int(1) NOT NULL,
`ip` varchar(255) NOT NULL,
`user` varchar(255) NOT NULL,
`sub_date` TIMESTAMP(14),
`info` varchar(255) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=gbk;
CREATE TABLE `dm_extra_attr` (
`id` int(10) NOT NULL auto_increment,
`code` int(3) NOT NULL,
`content` varchar(50) NOT NULL,
`remark` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=gbk;1.リアルデータdmpファイルを、ターゲットmysqlの異なるシナリオ(test)にインポートします.すなわち、バッファシナリオです.
2.insert select文により、ドメイン名-"接頭辞-"デッドロン-"の価値のない順序で、バッファスキームからsogou_にインポートblacklistスキームの2つの主要なテーブルにあります.構文は次のとおりです.
2.0自増フィールドは1からとし、ドメイン名テーブルデータの整理を容易にする
alter table sogou_blacklist.tb_domain AUTO_INCREMENT = 1;--------ドメイン名------2.1バッファリングスキーム【ドメイン名テーブル】のレコードをsogou_blacklistスキーム【ドメイン名テーブル】にインポートし、追加属性を【一般】insert into sogou_blacklist.tb_domain(url,tag_block,tag_reversible,reserve_id_str,extra_attr,`user`,sub_date)に設定するselect url,1-`type`,0,','普通',user,sub_date from test.domain;---------プレフィックス------2.2バッファスキーム【プレフィックステーブル】のレコードをsogou_blacklistスキーム【プレフィックステーブル】にインポートし、追加属性を【一般】insert into sogou_blacklist.tb_prefix(url,tag_selfinclude,tag_block,tag_reversible,reserve_id_str,extra_attr,`user`,sub_date)に設定するselect url,1-`type`,1-black,0,','普通',user,sub_date from test.url;--------デッドライン------2.3バッファリングスキーム【デッドラインテーブル】の【ドメイン名】レコードをsogou_blacklistスキーム【ドメイン名テーブル】にインポートし、追加属性を【デッドライン】insert into sogou_blacklist.tb_domain(url,tag_block,tag_reversible,reserve_id_str,extra_attr,`user`,sub_date)に設定するselect url,1,0,','デッドロック',user,sub_date from test.deadlink where type=1;2.4バッファ方式【デッドチェーンテーブル】の【接頭辞】レコードをsogou_blacklist方式【接頭辞テーブル】にインポートし、追加属性を【デッドチェーン】insert into sogou_blacklist.tb_prefix(url,tag_selfinclude,tag_block,tag_reversible,reserve_id_str,extra_attr,`user`,sub_date)に設定するselect url,1,1,0,','デッドロック',user,sub_date from test.deadlink where type=0;--------無価値------2.5バッファリングスキーム【無価値テーブル】の【無価値】レコードをsogou_blacklistスキーム【ドメイン名テーブル】にインポートし、追加属性を【無価値】insert into sogou_blacklist.tb_domain(url,tag_block,tag_reversible,reserve_id_str,extra_attr,`user`,sub_date)に設定するselect url,1,0,','無価値',user,sub_date from test.novalue where type=0;2.6バッファリングスキーム【無価値テーブル】のプレフィックス形式の【非中国語】レコードをdelete from test.novalue where url like'http://%';2.7バッファリングスキーム【無価値テーブル】の【非中国語】レコードを削除し、sogou_blacklistスキーム【ドメイン名テーブル】にインポートし、追加属性を【非中国語】insert into sogou_blacklist.tb_domain(url,tag_block,tag_reversible,reserve_id_str,extra_attr,`user`,sub_date)select url,1,0,','非中国語',user,sub_date from test.novalue where type=1;
2.8【ドメイン名テーブル】のsub_dateフィールドの0000-00-00 00 00:00を当日レコードに変換
update sogou_blacklist.tb_domain set sub_date=NOW() where sub_date like '%0000-00-00 00:00:00%'
2.9【接頭辞表】のsub_dateフィールドに0000を含むレコードを当日に変換する
update sogou_blacklist.tb_prefix set sub_date=NOW() where sub_date like '%0000%'3.実行順は
(2.6)オプションドメイン名表2.12.32.52.7(2.8)オプション接頭辞表2.22.4
2.9
4.関連するバックバンクJAVAプログラム
Dump.java
package dataDump;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.HashMap;
public class Dump {
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
try {
JDBCUtilsTest();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void JDBCUtilsTest() throws Exception {
double begin;
begin=System.currentTimeMillis();
Connection conn = null;
Statement st = null;
ResultSet rs = null;
String sql;
int num = 0;
try {
/**
* ： domain
*/
conn = JDBCUtils.getConnection();
st = conn.createStatement();
//2.0 1 ，
sql= "alter table sogou_blacklist.tb_domain AUTO_INCREMENT = 1";
st.executeUpdate(sql);
//2.1
sql= "insert into sogou_blacklist.tb_domain(url,tag_block,tag_reversible,reserve_id_str,extra_attr,`user`,sub_date) select url,1-`type`,0,'',' ',user,sub_date from test.domain";
num = st.executeUpdate(sql);
System.out.println("2.1 ："+num);
//2.3
sql= "insert into sogou_blacklist.tb_domain(url,tag_block,tag_reversible,reserve_id_str,extra_attr,`user`,sub_date) select url,1,0,'',' ',user,sub_date from test.deadlink where type=1";
num = st.executeUpdate(sql);
System.out.println("2.3 ："+num);
//2.5
sql= "insert into sogou_blacklist.tb_domain(url,tag_block,tag_reversible,reserve_id_str,extra_attr,`user`,sub_date) select url,1,0,'',' ',user,sub_date from test.novalue where type=0";
num = st.executeUpdate(sql);
System.out.println("2.5 ："+num);
//2.7
sql= "insert into sogou_blacklist.tb_domain(url,tag_block,tag_reversible,reserve_id_str,extra_attr,`user`,sub_date) select url,1,0,'',' ',user,sub_date from test.novalue where type=1";
num = st.executeUpdate(sql);
System.out.println("2.7 ："+num);
//2.8 sub_date 0000-00-00 00:00:00
sql= "update sogou_blacklist.tb_domain set sub_date=NOW() where sub_date like '%0000-00-00 00:00:00%'";
num = st.executeUpdate(sql);
System.out.println("2.8 ："+num);
/**
* ： prefix
*/
//2.2
sql= "insert into sogou_blacklist.tb_prefix(url,tag_selfinclude,tag_block,tag_reversible,reserve_id_str,extra_attr,`user`,sub_date) select url,1-`type`,1-black,0,'',' ',user,sub_date from test.url";
num = st.executeUpdate(sql);
System.out.println("2.2 ："+num);
//2.4
sql= "insert into sogou_blacklist.tb_prefix(url,tag_selfinclude,tag_block,tag_reversible,reserve_id_str,extra_attr,`user`,sub_date) select url,1,1,0,'',' ',user,sub_date from test.deadlink where type=0";
num = st.executeUpdate(sql);
System.out.println("2.4 ："+num);
//2.9 【 】 sub_date 0000
sql= "update sogou_blacklist.tb_prefix set sub_date=NOW() where sub_date like '%0000%'";
num = st.executeUpdate(sql);
System.out.println("2.9 ："+num);
} finally {
JDBCUtils.free( rs,st, conn);
}
System.out.println(" ："+(System.currentTimeMillis()-begin));
}
}
JDBCUtils.java
package dataDump;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public final class JDBCUtils {
private JDBCUtils() {
}
private static String url = "jdbc:mysql://10.11.89.108:3306/sogou_blacklist?useUnicode=true&characterEncoding=gbk";
private static String user = "sogou_blacklist";
private static String password = "m6i1m2a3";
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
throw new ExceptionInInitializerError(e);
}
}
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, user, password);
}
public static void free(ResultSet rs, Statement st, Connection conn) {
try {
if (rs != null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (st != null)
st.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
public static void free(Statement st, Connection conn) {
try {
if (st != null)
st.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}