`
specialbrian
  • 浏览: 60933 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

UBS数据库的真实数据上线过程

SQLMySQL脚本JDBC 
阅读更多

-1.使用脚本建立kasai数据库，并插入初始权限相关信息，及超级用户信息。数据库脚本如下：

 

-- 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('wangpan@sohu-rd.com','empty','empty','empty@empty.com','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','wangpan@sohu-rd.com',9);
INSERT INTO kasai_objects_users_roles(id_object,id_user,id_role) VALUES ('kasai/kasai_objects_users_roles','wangpan@sohu-rd.com',9);
INSERT INTO kasai_objects_users_roles(id_object,id_user,id_role) VALUES ('kasai/kasai_operatives','wangpan@sohu-rd.com',9);
INSERT INTO kasai_objects_users_roles(id_object,id_user,id_role) VALUES ('kasai/kasai_roles','wangpan@sohu-rd.com',9);
INSERT INTO kasai_objects_users_roles(id_object,id_user,id_role) VALUES ('kasai/kasai_roles_operatives','wangpan@sohu-rd.com',9);
INSERT INTO kasai_objects_users_roles(id_object,id_user,id_role) VALUES ('kasai/kasai_users','wangpan@sohu-rd.com',9);

INSERT INTO kasai_objects_users_roles(id_object,id_user,id_role) VALUES ('test/tb_domain','wangpan@sohu-rd.com',9);
INSERT INTO kasai_objects_users_roles(id_object,id_user,id_role) VALUES ('test/tb_prefix','wangpan@sohu-rd.com',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.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.1
2.3
2.5
2.7
(2.8)可选
前缀表
2.2
2.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();
				}
			}
	}
}

 

 

分享到：
| JAVA编译运行命令格式
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    UBS查询工具UBS查询工具UBS查询工具

    2. 实时数据更新：为了保持数据的最新状态，工具可能连接到UBS的实时数据库，提供最新的市场数据和账户状况，帮助用户做出及时的决策。 3. 数据分析：工具可能包含数据分析模块，支持对大量数据进行统计分析，比如...

    UBS高速数据采集系统论文.doc

    UBS高速数据采集系统论文.doc

    UBS Logical

    这类测试在求职过程中，尤其是像UBS这样的大型国际金融公司，是评估应聘者逻辑思维和问题解决能力的一种方式。 描述中提到的“UBS Logical Test answer Hope everyone finds it useful.”表明内容可能是关于UBS逻辑...

    UBS检测工具

    总的来说，UBS检测工具是计算机用户尤其是IT技术人员的得力助手，它简化了USB设备管理和故障排查的过程，提高了工作效率，并增强了对USB设备安全性的把控。无论是个人用户还是企业用户，都能从中受益。

    UBS笔试Logic题库

    根据提供的标题、描述以及部分可见的内容（虽然这里并未给出具体的图片内容，但我们可以根据上下文推测这些“[pic]”代表的是逻辑图形推理题目），我们可以分析出以下与UBS笔试逻辑图形推理题相关的知识点。...

    ubs试题————三大经典问题，网上整理版

    UBS（瑞士联合银行集团）作为全球领先的金融服务机构之一，在招聘过程中往往会提出一些经典的面试问题来考察应聘者的综合能力与职业素养。本文将对UBS招聘过程中常见的三个经典问题进行详细解读，并提供可能的回答...

    UBS在線測試題

    UBS在線測試題

    UBS笔试测试题逻辑部分

    不过，基于标题和描述，我们可以推测出文件的知识点是与UBS公司笔试测试题中的逻辑部分相关。 UBS笔试测试题逻辑部分的知识点主要包括以下内容： 1. 测试形式：通常，笔试测试题逻辑部分以题库的形式出现，每个...

    UBS保险科技亚洲前瞻.pdf

    2017年UBS发布的报告《保险科技亚洲前瞻》指出，亚洲作为全球保险渗透度最低的市场之一，拥有巨大的潜力等待挖掘。保险科技（InsurTech）的崛起，借助于联网设备、高级数据分析、人工智能（AI）和数字化分销渠道，...

    ubs hisoft frs

    【标题】"ubs hisoft frs" 涉及到的是一个特定的IT解决方案，它很可能是由Hisoft公司为UBS（瑞士联合银行）开发的一款财务报告系统（Financial Reporting System，FRS）。这个系统可能用于帮助UBS这样的金融机构进行...

    03.承銷公告UBS AG,London Branch美元債.pdf

    03.承銷公告UBS AG,London Branch美元債.pdf

    老电脑ubs补丁

    标题中的“老电脑ubs补丁”实际上是指针对老旧计算机的USB启动相关补丁，这里的"ubs"可能是"USB"的误拼。这个补丁是为了帮助那些年代较久的电脑能够支持从USB设备启动，比如U盘，进行系统安装或者修复。在很多情况下...

    ubs求职大礼包

    ubs求职大礼包,包括瑞银求职资料，面经，申请感谢。

    usb_data.rar_Ubs代码10_usb_usb 开发

    4. **USB枚举过程**：当设备插入时，主机会通过枚举过程识别设备，分配地址，加载适当的驱动程序，并建立数据传输路径。 5. **USB驱动程序**：驱动程序是操作系统与USB设备之间的桥梁，负责解释设备的特性和处理...

    ubs hisoft gtrs

    【ubs hisoft gtrs】是UBS（瑞士联合银行）采用的一种特定的软件系统，它由Hisoft公司开发，主要用于企业级的财务管理和报告服务。GTRS，全称为Global Tax Reporting System，是一个全球税务报告解决方案，旨在帮助...

    UBS报告：中国A 股量化研究-风格因子月报（29页）.zip

    《UBS报告：中国A股量化研究-风格因子月报》是瑞士银行(UBS)发布的一份深度研究报告，旨在探讨中国A股市场中的量化投资策略及其相关风格因子的表现。这份29页的报告深入剖析了中国股市的量化投资领域，为投资者提供...

    UBS-MobilePass1011.apk

    UBS-MobilePass1011.apk

    ubs3.0协议规格书

    同时，协议中包含了对设备的热插拔和即插即用的支持，让用户在使用过程中更加便捷。 在“USB 3.0 (11132008)-final.pdf”文档中，读者可以深入了解到USB 3.0的详细技术规格，包括电气特性、物理层、传输层、协议层...

    CyAPI USB頭文件 (CyAPI.h Cyioctl.h devioctl.h CyAPI.lib)

    CyAPI USB頭文件 (CyAPI.h Cyioctl.h devioctl.h CyAPI.lib) 含 CyAPI.pdf Programmer's Reference

Magicbox
Global site tag (gtag.js) - Google Analytics