最近刚刚做完个项目，觉得分库这里可以归纳一下，给未来的自己备个份^_^
技术目标：实现自动备份库、自动建库，动态创建数据源、动态切换数据源
需求：给甲方A开发个项目，A有着很大的用户群，涉及A公司、B公司、c、d......（动态变化的：即可能继续延伸、也可能摸某个公司会终止业务），故A要求针对不同的业务客户有自己的独立库，这里强调一下：虽然是不同的库，A对每个公司的具体业务表示一样的。
目标需求：由于我不愿意为了写这个博客去剥离纯粹的逻辑代码，故这里只提供关键的实现点，具体的jsp调用逻辑等细节不再关注。
环境：win7，eclipse kepler，jdk7 ，tomcat7，jQuery v1.11.1，mysql5.6(mysql的命令必须配到环境变量里，建库时要用)，springmvc4 , hibernate4（可以不用hibernate）
代码地址：http://download.csdn.net/detail/rainyspring4540/9568014
实现逻辑：
第一步：项目启动时有个默认的基础库和对应的数据源（就是有个能访问的库作为默认库），以后统称为主库
第二步：我们会在主库的界面下进行创建新库（这里涉及先备份基础库表、视图，然后创建新库）并为新库创建数据源使之能被访问，以后统称新库为从库
第三步：由于有些业务可能涉及主库和从库、从库和从库的同步数据问题，故要再具体的某个库的数据源操作下动态切换到别的库，去更改其他需要同步的数据
我先贴出所有代码，大家完全可以自己边运行边分析，我花了一天从项目中抽离的的demo很简单的，没必要听我唠叨，故先贴代码：
代码目录：
其中temp里有个maindb.sql，是mysql的库文件，主库的名字固定是maindb
maindb.sql内容
/*
Navicat MySQL Data Transfer
Source Server : local
Source Server Version : 50517
Source Host : localhost:3306
Source Database : maindb
Target Server Type : MYSQL
Target Server Version : 50517
File Encoding : 65001
Date: 2016-07-05 17:23:21
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `msg`
-- ----------------------------
DROP TABLE IF EXISTS `msg`;
CREATE TABLE `msg` (
`name` varchar(255) NOT NULL DEFAULT '',
`note` varchar(255) DEFAULT NULL,
PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of msg
-- ----------------------------
-- ----------------------------
-- Table structure for `project`
-- ----------------------------
DROP TABLE IF EXISTS `project`;
CREATE TABLE `project` (
`PROJECTID` varchar(50) NOT NULL,
`PROJECTNAME` varchar(200) DEFAULT NULL,
`userId` varchar(255) DEFAULT NULL,
`pwd` varchar(255) DEFAULT NULL,
PRIMARY KEY (`PROJECTID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of project
-- ----------------------------
-- ----------------------------
-- Table structure for `sys_db`
-- ----------------------------
DROP TABLE IF EXISTS `sys_db`;
CREATE TABLE `sys_db` (
`id` varchar(255) NOT NULL DEFAULT '',
`projectId` varchar(255) DEFAULT '',
`dbname` varchar(255) DEFAULT NULL,
`urlPrefix` varchar(255) DEFAULT NULL,
`host` varchar(255) DEFAULT NULL,
`port` varchar(255) DEFAULT NULL,
`urlSuffix` varchar(255) DEFAULT NULL,
`driverClassNameValue` varchar(255) DEFAULT NULL,
`dataSourceId` varchar(255) DEFAULT NULL,
`username` varchar(255) DEFAULT NULL,
`pwd` varchar(255) DEFAULT NULL,
`createdate` varchar(255) DEFAULT '',
`status` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of sys_db
-- ----------------------------
然后是src文件，我逐个包贴出来
config包是配置文件，其中project-config.xml是springmvc的配置文件；dataSourceCfgTemplate.vm是动态数据源创建的配置文件，里面的参数都是替换符，因为是动态数据源嘛，参数是变化的。
而util.db是真正核心创建库和创建数据源包，且我个人认为代码完全独立
其他包就见明知义，controller是我demo的一些控制类:包含登录、初始化默认数据源。。。；dto就是展示层用于封装实体到jsp的数据传输；entity是实体
看demo的时候从login.jsp开始，一点点看就ok了。
提示自己：
1 。切换数据源的方式是当时找到的比较通用的，看完代码后可以网上看看原理，
2 。创建数据源虽然在四个测试人员下依然稳定，但个人总觉得不一定能撑住大数据并发，
3。如果你采用了这种方式，切库一定要小心！一定要小心！一定要小心！可能会出现卡死，幽灵数据、切换错误现象，那一定坚信是自己使用不当：
原因一 ：如果你用了hibernate，session的不正常关闭很恶心；如果因为异常不关闭session，导致池中的链接马上耗完，如果涉及同步，不正常关闭上一个session也会连累下边的不正常执行，建议不用hibernate，我的逻辑仔细看会发现 只有具体业务才用了hibernate，建库、创建数据源并没有用hibernate，而切换数据源可以直接采用如下代码即可：
contextHolder.set("defaultDataSource");
hibernateTemplate.find(sql);//采用hibernatetemplate，是spring封装了hibernate，无需考虑session关闭4.不要多个数据源的session嵌套进行CRUD，你会死的很惨，必须分开，比如同步操作，且对每个数据数据源的操作加Try-catch，然后进行下一个数据源的操作，这是测试的结果
5 建议不要将上一个数据源的session获取的实体（仅仅是实体，其他的没事，比如具体的数据列）用于下一个数据源的CRUD，仅仅是建议，我都是尽量避开可能的问题，当时项目太忙，没时间测试，先记下
6 以上都是针对使用了hibernate的session后的一些警告，还是那句话，如果能说服领导，别用hibernate，至少在多数据源上问题很多。
下面贴贴代码：
config.propties
##主库配置
main.driver=com.mysql.jdbc.Driver
main.url=jdbc:mysql://localhost:3306/maindb?useUnicode=true&characterEncoding=utf8
main.username=root
main.password=root
##默认的备份库地址
dataSourceRecord.urlPrefix=jdbc:mysql://
dataSourceRecord.urlSuffix=
backup.host=localhost
backup.port=3306
backup.dbName=maindb
backup.username=root
backup.password=root
backup.exportPath=d:/database.sql
##导入地址
importDB.host=localhost
importDB.port=3306
importDB.dbName=ggg
importDB.username=root
importDB.password=root
importDB.importPath=d:/database.sql
# hibernate.X
hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
hibernate.show_sql=true
hibernate.hbm2ddl.auto=none
dataSourceCfgTemplate.vm
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:aop="http://www.springframework.org/schema/aop" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.2.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-3.2.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-3.2.xsd">
<bean id="${dataSourceId}" class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close">
<property name="driverClassName" value="${driverClassName}" />
<property name="url" value="${url}" />
<property name="username" value="${username}" />
<property name="password" value="${password}" />
</bean>
</beans>project-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:aop="http://www.springframework.org/schema/aop" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.2.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-3.2.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-3.2.xsd">
<context:component-scan base-package="controller"/>
<bean id ="dynamicLoadBean" class="util.db.DynamicLoadBean" ></bean>
<bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<!-- 单独文件 -->
<!-- <property name="location" value="/WEB-INF/config.properties" /> -->
<!-- 多个文件 -->
<property name="locations">
<list>
<!-- method1 标准配置 -->
<value>classpath*:/config/config.properties</value>
<!-- method2 -->
<!-- <value>/WEB-INF/config.properties</value> -->
<!--method3 服务器生产环境配置 -->
<!-- <value>file:/var/myapp/application.server.properties</value> -->
</list>
</property>
</bean>
<!-- 连接mysql -->
<bean id="defaultDataSource" class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close">
<property name="driverClassName" value="${main.driver}" />
<property name="url" value="${main.url}" />
<property name="username" value="${main.username}" />
<property name="password" value="${main.password}" />
</bean>
<bean id="dynamicDataSource" class="util.db.DynamicDataSource">
<property name="targetDataSources">
<map key-type="java.lang.String">
<entry key="defaultDataSource" value-ref="defaultDataSource" />
</map>
</property>
<property name="defaultTargetDataSource" ref="defaultDataSource" />
</bean>
<!-- 类型2：DB2、SQL Server、MySQL 等非 Oracle 的其它数据库，则只要简单配置一个 DefaultLobHandler -->
<bean id="defaultLobHandler" class="org.springframework.jdbc.support.lob.DefaultLobHandler"
lazy-init="true" />
<!--Hibernate4 sessionFactory -->
<bean id="sessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
<property name="dataSource" ref="dynamicDataSource" />
<property name="packagesToScan">
<list>
<!-- 可以加多个包 -->
<value>entity</value>
</list>
</property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.hbm2ddl.auto" >${hibernate.hbm2ddl.auto}</prop>
<prop key="hibernate.dialect">${hibernate.dialect}</prop>
<prop key="hibernate.show_sql">${hibernate.show_sql}</prop>
<!-- hibernate.jdbc.fetch_size 对mysql无效 -->
<!-- <prop key="hibernate.jdbc.fetch_size">50</prop>
<prop key="hibernate.jdbc.batch_size">20</prop>
-->
<!-- 配置二级缓存 -->
<!-- <prop key="hibernate.cache.use_second_level_cache">true</prop>
<prop key="hibernate.cache.region.factory_class">org.hibernate.cache.ehcache.EhCacheRegionFactory</prop>
-->
<!-- 开启查询缓存 -->
<!-- <prop key="hibernate.cache.use_query_cache">true</prop> -->
<!-- <prop key="hibernate.current_session_context_class">thread</prop> -->
</props>
</property>
</bean>
<bean id="hibernateTemplate" class="org.springframework.orm.hibernate4.HibernateTemplate">
<property name="sessionFactory" ref="sessionFactory"></property>
</bean>
<!-- 配置Hibernate事务管理器 -->
<bean id="transactionManager"
class="org.springframework.orm.hibernate4.HibernateTransactionManager">
<property name="sessionFactory" ref="sessionFactory" />
</bean>
<!-- 配置事务异常封装 -->
<bean id="persistenceExceptionTranslationPostProcessor"
class="org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor" />
<!-- jdbc事务管理器 -->
<!-- <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource">
<ref local="dataSource_mysql" />
</property>
</bean> -->
<!-- 启用注解 -->
<context:annotation-config />
<!-- 注解事务驱动 -->
<tx:annotation-driven transaction-manager="transactionManager" />
<!-- ViewResolver -->
<bean id="internalResourceViewResolver"
class="org.springframework.web.servlet.view.InternalResourceViewResolver">
<property name="viewClass"
value="org.springframework.web.servlet.view.JstlView" />
<!-- <property name="prefix" value="/justwin/"/> -->
<property name="suffix" value=".jsp" />
<!-- 设置解析器的优先级 InternalResourceViewResolver必须总是赋予最低的优先级（最大的order值）， 因为不管返回什么视图名称，它都将解析视图。如果它的优先级高于其它解析器的优先级的话，
它将使得其它具有较低优先级的解析器没有机会解析视图 -->
<property name="order" value="0" />
</bean>
</beans>LoginController.java
package controller;
import java.io.IOException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.hibernate.Session;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.hibernate4.HibernateTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import util.db.DataSourceContextHolder;
/**
*
* @author jx
* @createDate 2016-06-24
*/
@Controller
public class LoginController {
@Autowired
private HibernateTemplate hibernateTemplate;
/**
* 管理员登录
* @param request
* @param response
* @throws IOException
*/
@RequestMapping("/systemLogin.do")
public String systemLogin(HttpServletRequest request,
HttpServletResponse response) throws IOException {
// 业务代码--------------------奇妙的分割线-------------------------
String userId = request.getParameter("userId");
String pwd = request.getParameter("pwd");
if("admin".equals(userId)&&"admin".equals(pwd)){
return "adminIndex";
}else{
return "login";//返回登录页面
}
}
@RequestMapping("/projectLogin.do")
public String projectLogin(HttpServletRequest request,
HttpServletResponse response) throws IOException {
Session session = null;
try {
session = DataSourceContextHolder.getSystemDB(hibernateTemplate);
// 业务代码--------------------奇妙的分割线-------------------------
String userId = request.getParameter("userId");
String pwd = request.getParameter("pwd");
Long count = (Long) session.createQuery("select count(*) from Project where userId=:userId and pwd=:pwd").setString("userId", userId).setString("pwd", pwd).uniqueResult();
if(count==null||count<=0){
return "login";//返回登录页面
}else{
return "projectIndex";
}
// 业务代码--------------------奇妙的分割线-------------------------
} catch (Exception e) {
return "login";//返回登录页面
} finally {
if (session != null) {
try{
session.flush();
session.close();
}catch(Exception e){
}
}
}
}
}
ProjectController.java
package controller;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang.StringUtils;
import org.hibernate.Session;
import org.springframework.beans.BeansException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.orm.hibernate4.HibernateTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import util.base.PrintHelper;
import util.db.DataSourceContextHolder;
import util.db.MyDataSource;
import util.db.MyDatabase;
import dto.DatagridMsg;
import dto.DatagridProject;
import entity.DatabaseBean;
import entity.Msg;
import entity.Project;
/**
*
* @author jx
* @createDate 2016-06-24
*/
@Controller
@RequestMapping("/project/")
public class ProjectController implements ApplicationContextAware {
private ApplicationContext ac;
@Autowired
private HibernateTemplate hibernateTemplate;
@RequestMapping("/listProjects.do")
public void listProjects(HttpServletRequest request,
HttpServletResponse response) throws IOException {
Session session = null;
try {
session = DataSourceContextHolder.getSystemDB(hibernateTemplate);
// 业务代码--------------------奇妙的分割线-------------------------
List<Project> list = session.createQuery("from Project").list();
List<DatagridProject> olist = this.changeDTO(list);
PrintHelper.sendJsonMap(response, olist,olist.size());
// 业务代码--------------------奇妙的分割线-------------------------
} catch (Exception e) {
} finally {
if (session != null) {
try{
session.flush();
session.close();
}catch(Exception e){
}
}
}
}
@RequestMapping("/listProjectsByUserIdAndPwd.do")
public void listProjectsByUserIdAndPwd(HttpServletRequest request,
HttpServletResponse response) throws IOException {
Session session = null;
try {
session = DataSourceContextHolder.getSystemDB(hibernateTemplate);
// 业务代码--------------------奇妙的分割线-------------------------
String userId = request.getParameter("userId");
String pwd = request.getParameter("pwd");
List<Project> list = session.createQuery("from Project where userId=:userId and pwd=:pwd").setString("userId", userId).setString("pwd", pwd).list();
List<DatagridProject> olist = this.changeDTO(list);
PrintHelper.sendJsonMap(response, olist,olist.size());
// 业务代码--------------------奇妙的分割线-------------------------
} catch (Exception e) {
} finally {
if (session != null) {
try{
session.flush();
session.close();
}catch(Exception e){
}
}
}
}
//
@RequestMapping("/getProject.do")
public void getProject(HttpServletRequest request,
HttpServletResponse response) throws IOException {
Session session = null;
try {
session = DataSourceContextHolder.getSystemDB(hibernateTemplate);
// 业务代码--------------------奇妙的分割线-------------------------
String projectId = request.getParameter("projectId");
Project p = (Project) session.get(Project.class, projectId);
if(p!=null){
DatagridProject o = this.changeDTO(p);
PrintHelper.sendJsonObject(response, o);
}else{
PrintHelper.sendJsonString(response, "");
}
// 业务代码--------------------奇妙的分割线-------------------------
} catch (Exception e) {
} finally {
if (session != null) {
try{
session.flush();
session.close();
}catch(Exception e){
}
}
}
}
private DatagridProject changeDTO(Project p) {
DatagridProject o = new DatagridProject();
o.setProjectId(p.getProjectId());
o.setProjectName(p.getProjectName());
o.setUserId(p.getUserId());
o.setPwd(p.getPwd());
return o;
}
/**
* 转换结构
* @param list
* @return
*/
private List<DatagridProject> changeDTO(List<Project> list) {
List<DatagridProject> olist = new ArrayList<DatagridProject>();
if(list==null||list.size()<=0){
return olist;
}
for(Project p :list){
olist.add(this.changeDTO(p));
}
return olist;
}
/**
* @updateDate 2016-06-12
* @author
* @throws IOException
* @description 新增项目
*/
@RequestMapping("/addProject")
public void addProject(HttpServletRequest request,
HttpServletResponse response) throws IOException {
Session session = null;
String projectId = null;
boolean flag = false;
try {
session = DataSourceContextHolder.getSystemDB(hibernateTemplate);
session.beginTransaction();
// 业务代码--------------------奇妙的分割线-------------------------
projectId = request.getParameter("projectId");
String projectName = request.getParameter("projectName");
String userId = request.getParameter("userId");
String pwd = request.getParameter("pwd");
Project p = (Project) session.get(Project.class, projectId);
if(p!=null){
PrintHelper.sendJsonString(response, "isUsed");//反馈
}else{
// 项目表中生成记录
Project p1 = new Project();
p1.setProjectId(projectId);
p1.setProjectName(projectName);
p1.setUserId(userId);
p1.setPwd(pwd);
session.save(p1);
flag = true;
}
session.getTransaction().commit();
// 业务代码--------------------奇妙的分割线-------------------------
} catch (Exception e) {
session.getTransaction().rollback();
} finally {
if (session != null) {
try{
session.flush();
session.close();
}catch(Exception e){
}
}
}
// 建立项目库
try {
if (flag) {
//创建项目
DatabaseBean db = new MyDatabase(hibernateTemplate, projectId)
.copyMaindbByDBName(null);
// 将数据源添加到server
new MyDataSource(db, ac).create();
PrintHelper.sendJsonString(response, "success");
} else {
PrintHelper.sendJsonString(response, "failure");
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
PrintHelper.sendJsonString(response, "failure");
}
}
@RequestMapping("/listMsgByProjectId.do")
public void listMsgByProjectId(HttpServletRequest request,
HttpServletResponse response) throws IOException {
Session session = null;
try {
String projectId = request.getParameter("projectId");
if(StringUtils.isBlank(projectId)){
PrintHelper.sendJsonString(response, "");//反馈
return;
}
session = DataSourceContextHolder.getProjectDB(hibernateTemplate, projectId);
// 业务代码--------------------奇妙的分割线-------------------------
List<Msg> list = session.createQuery("from Msg").list();
List<DatagridMsg> olist = new ArrayList<DatagridMsg>();
if(list!=null){
for(Msg m:list){
DatagridMsg o = new DatagridMsg();
o.setName(m.getName());
o.setNote(m.getNote());
olist.add(o);
}
}
PrintHelper.sendJsonMap(response, olist,olist.size());
// 业务代码--------------------奇妙的分割线-------------------------
} catch (Exception e) {
} finally {
if (session != null) {
try{
session.flush();
session.close();
}catch(Exception e){
}
}
}
}
/**
* @updateDate 2016-06-12
* @author
* @throws IOException
* @description 新增栏目
*/
@RequestMapping("/addMsg")
public void addMsg(HttpServletRequest request,
HttpServletResponse response) throws IOException {
Session session = null;
try {
String projectId = request.getParameter("projectId");
String name = request.getParameter("name");
String note = request.getParameter("note");
if(StringUtils.isBlank(projectId)){
PrintHelper.sendJsonString(response, "failure");//反馈
return;
}
session = DataSourceContextHolder.getProjectDB(hibernateTemplate, projectId);
session.beginTransaction();
Msg m = (Msg) session.get(Msg.class, name);
if(m!=null){
PrintHelper.sendJsonString(response, "isUsed");//反馈
}else{
// 项目表中生成记录
Msg m1 = new Msg();
m1.setName(name);
m1.setNote(note);
session.save(m1);
}
session.getTransaction().commit();
PrintHelper.sendJsonString(response, "success");
// 业务代码--------------------奇妙的分割线-------------------------
} catch (Exception e) {
session.getTransaction().rollback();
} finally {
if (session != null) {
try{
session.flush();
session.close();
}catch(Exception e){
}
}
}
}
/**
* @updateDate 2016-06-12
* @author
* @throws IOException
* @description 删除栏目
*/
@RequestMapping("/delMsg")
public void delMsg(HttpServletRequest request,
HttpServletResponse response) throws IOException {
Session session = null;
try {
String projectId = request.getParameter("projectId");
String name = request.getParameter("name");
if(StringUtils.isBlank(projectId)){
PrintHelper.sendJsonString(response, "failure");//反馈
return;
}
session = DataSourceContextHolder.getProjectDB(hibernateTemplate, projectId);
session.beginTransaction();
Msg m = (Msg) session.get(Msg.class, name);
if(m!=null){
session.delete(m);
}
session.getTransaction().commit();
PrintHelper.sendJsonString(response, "success");
// 业务代码--------------------奇妙的分割线-------------------------
} catch (Exception e) {
session.getTransaction().rollback();
} finally {
if (session != null) {
try{
session.flush();
session.close();
}catch(Exception e){
}
}
}
}
/**
* @updateDate 2016-06-12
* @author
* @description 删除项目
*/
@RequestMapping("/delProject")
public synchronized void delProject(HttpServletRequest request,
HttpServletResponse response) throws IOException {
Session session = null;
try {
session = DataSourceContextHolder.getSystemDB(hibernateTemplate);
String key = request.getParameter("projectId");
if (!StringUtils.isBlank(key)) {
session.beginTransaction();
// 删除主库的项目表记录
session.createQuery("delete from Project where projectId=:projectId")
.setString("projectId", key).executeUpdate();
// 删除db链接
session.createQuery(
"delete from DatabaseBean where projectId=:projectId")
.setString("projectId", key).executeUpdate();
session.getTransaction().commit();
// 删除项目库(采用占位符?)
session.createSQLQuery("drop database ds_" + key)
.executeUpdate();// 暂时采用这种语法
}
PrintHelper.sendJsonString(response, "success");
} catch (Exception e) {
session.getTransaction().rollback();
} finally {
if (session != null) {
try{
session.flush();
session.close();
}catch(Exception e){
}
}
}
}
@Override
public void setApplicationContext(ApplicationContext ac)
throws BeansException {
this.ac = ac;
}
}
SysInitData.java
package controller;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;
import javax.servlet.ServletContext;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationListener;
import org.springframework.context.event.ContextRefreshedEvent;
import org.springframework.orm.hibernate4.HibernateTemplate;
import org.springframework.stereotype.Component;
import org.springframework.web.context.ServletContextAware;
import util.base.DBHelper;
import util.db.DataSourceManager;
import util.db.MyDatabase;
/**
* 项目启动后--初始化权限类
* @author jx
* @date 2015-08-25
*/
@Component
public class SysInitData implements ApplicationListener<ContextRefreshedEvent>,ServletContextAware{
private Log log = LogFactory.getLog(SysInitData.class);
private ApplicationContext ac ;
private ServletContext sc;
@Override
public void onApplicationEvent(ContextRefreshedEvent event) {
this.ac = event.getApplicationContext();
//初始化应用级参数
//数据源加载中
log.info("####-----准备数据源加载");
DataSourceManager.init(ac,sc);
log.info("####-----完成数据源加载");
log.info("####-----准备导入存储过程");
try{
DBHelper.initAllProcedures((HibernateTemplate) ac.getBean("hibernateTemplate"), sc);
log.info("####-----完成导入存储过程");
}catch(Throwable e){
log.info("####-----导入存储过程失败");
}
//数据库连接类型
InputStream is = MyDatabase.class.getClassLoader().getResourceAsStream(
MyDatabase.configFile);
Properties pro = new Properties();
try {
pro.load(is);
String dialect = (String) pro.get("hibernate.dialect");
if(dialect!=null){
if(dialect.toLowerCase().contains("mysql")){
sc.setAttribute("dbtype", "mysql");
}
}
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
@Override
public void setServletContext(ServletContext servletContext) {
this.sc = servletContext;
}
}
dto层DatagridMsg.java
package dto;
public class DatagridMsg {
public String name;
public String note;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getNote() {
return note;
}
public void setNote(String note) {
this.note = note;
}
}
package dto;
public class DatagridProject {
public String projectId;
public String ProjectName;
public String userId;
public String pwd;
public String getProjectId() {
return projectId;
}
public void setProjectId(String projectId) {
this.projectId = projectId;
}
public String getProjectName() {
return ProjectName;
}
public void setProjectName(String projectName) {
ProjectName = projectName;
}
public String getUserId() {
return userId;
}
public void setUserId(String userId) {
this.userId = userId;
}
public String getPwd() {
return pwd;
}
public void setPwd(String pwd) {
this.pwd = pwd;
}
}
entity层DatabaseBean.java
package entity;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
/**
* 数据源
*
* @author jx
*
*/
@Entity
@Table(name = "sys_db")
public class DatabaseBean {
/**
* 当出现项目相同的多条记录时， 通过max函数取最大值作为以projectId为条件
* 获取数据源的返回记录（保证一个projectId下有且仅有一条有效数据源记录）
*/
@Id
private String id;
@Column
private String projectId;
@Column
private String dbName;
@Column
private String urlPrefix;
@Column
private String host;
@Column
private String port;
@Column
private String urlSuffix;
@Column
private String driverClassNameValue;
@Column
private String dataSourceId;
@Column
private String username;
@Column
private String pwd;
@Column
private String createdate;
@Column
private String status;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getProjectId() {
return projectId;
}
public void setProjectId(String projectId) {
this.projectId = projectId;
}
public String getDbName() {
return dbName;
}
public void setDbName(String dbName) {
this.dbName = dbName;
}
public String getUrlPrefix() {
return urlPrefix;
}
public void setUrlPrefix(String urlPrefix) {
this.urlPrefix = urlPrefix;
}
public String getHost() {
return host;
}
public void setHost(String host) {
this.host = host;
}
public String getPort() {
return port;
}
public void setPort(String port) {
this.port = port;
}
public String getUrlSuffix() {
return urlSuffix;
}
public void setUrlSuffix(String urlSuffix) {
this.urlSuffix = urlSuffix;
}
public String getDriverClassNameValue() {
return driverClassNameValue;
}
public void setDriverClassNameValue(String driverClassNameValue) {
this.driverClassNameValue = driverClassNameValue;
}
public String getDataSourceId() {
return dataSourceId;
}
public void setDataSourceId(String dataSourceId) {
this.dataSourceId = dataSourceId;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPwd() {
return pwd;
}
public void setPwd(String pwd) {
this.pwd = pwd;
}
public String getCreatedate() {
return createdate;
}
public void setCreatedate(String createdate) {
this.createdate = createdate;
}
public String getStatus() {
return status;
}
public void setStatus(String status) {
this.status = status;
}
}
Msg.java
package entity;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
/**
* @author jx
* @date 2015-11-16
* @description 栏目表
*/
@Entity
@Table(name = "msg")
public class Msg {
@Id
public String name;
@Column
public String note;
public String getNote() {
return note;
}
public void setNote(String note) {
this.note = note;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
Project.java
package entity;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
/**
* @author jx
* @date 2015-11-16
* @description 项目表
*/
@Entity
@Table(name = "project")
public class Project {
@Id
private String projectId;//项目编号
@Column
private String projectName;//项目名称
@Column
private String userId;
@Column
private String pwd;
public String getProjectName() {
return projectName;
}
public void setProjectName(String projectName) {
this.projectName = projectName;
}
public String getProjectId() {
return projectId;
}
public void setProjectId(String projectId) {
this.projectId = projectId;
}
public String getUserId() {
return userId;
}
public void setUserId(String userId) {
this.userId = userId;
}
public String getPwd() {
return pwd;
}
public void setPwd(String pwd) {
this.pwd = pwd;
}
}
util.base包DataHelper.java
package util.base;
import java.io.File;
import javax.servlet.ServletContext;
import javax.servlet.http.HttpServletRequest;
/*
* 这个帮助类专门处理 数据格式上的转换问题--多以json和其他格式互转
*
* @author yhr
*
*/
/**
* @author jx
* @date 2015-08-25
*/
public final class DataHelper {
/**
* 获取保存服务器保存文件的基础路径（目录末包含分隔符）
* @param sc
* @return
*/
public static String getSavePath(ServletContext sc){
if(sc==null) return null;
return sc.getRealPath("")+ File.separator + "temp" + File.separator;
}
/**
* 获取保存服务器保存文件的基础路径（目录末包含分隔符）
* @param sc
* @return
*/
public static String getSavePath4Base(ServletContext sc){
if(sc==null) return null;
return sc.getRealPath("")+ File.separator + "temp" + File.separator+"base"+ File.separator;
}
/**
* 获取服务器文件访问路径（目录末包含分隔符）
* @param request
* @return
*/
public static String getServerPath(HttpServletRequest request){
if(request==null) return null;
return request.getScheme() + "://"+ request.getServerName() + ":" + request.getServerPort()
+ request.getContextPath() + "/temp/";
}
}
package util.base;
import java.io.File;
import java.util.List;
import javax.servlet.ServletContext;
import org.apache.commons.lang.StringUtils;
import org.hibernate.Session;
import org.springframework.orm.hibernate4.HibernateTemplate;
import util.db.DBProperty;
import util.db.DataSourceContextHolder;
import util.db.MySqlImportAndExport;
import entity.DatabaseBean;
/**
* 具体sql的生成工具类
*
* @author
*
*/
/**
* @author jx
* @date 2015-08-25
*/
public final class DBHelper {
/**
* 在创建项目的时候目前无法将存储过程导入，故单独插入存储过程
* @param session 必须是系统库的session
* @param projectId 插入的项目，在主库的sys_db中存在记录
* @param request
*/
public static void initProceduresAfterCreatingProject(HibernateTemplate hibernateTemplate,String projectId,ServletContext sc){
Session session = null;
try{
if(StringUtils.isBlank(projectId)) {
System.err.println("在创建项目时插入存储过程projectId不能为空");
return;
}
//保存当前数据源名称
String currentDataSource = DataSourceContextHolder.getDataSourceName();
//切换到主库
DataSourceContextHolder.setDataSourceType("defaultDataSource");
session = hibernateTemplate.getSessionFactory().openSession();
List<DatabaseBean> db_list = session.createQuery("from DatabaseBean where id='" + projectId + "'").list();
if(db_list==null||db_list.size()!=1) {
System.err.println("在创建项目时插入存储过程 数据库实体DatabaseBean为空或大于一个");
return;
}
DatabaseBean mydb = db_list.get(0);
// ServletContext sc = request.getSession().getServletContext();
String fileName = DataHelper.getSavePath4Base(sc)+"package_procedures.sql";
if(!new File(fileName).exists()) return;
DBProperty pro = new DBProperty(mydb.getHost(), mydb.getDbName(), fileName);
long start = System.currentTimeMillis();
int exitVal = 0;
try{
exitVal = MySqlImportAndExport.importSqlBySource4NoCreateDB(pro);
}catch(Throwable e){
exitVal = -1;
}
long end = System.currentTimeMillis();
System.out.println("####-------向数据库"+mydb.getDbName()+" 导入存储过程【运行代码："+exitVal+" ; 耗时："+(end-start)/(1000*60)+"m】");
session.close();
//切回原来的数据源
DataSourceContextHolder.setDataSourceType(currentDataSource);
}catch(Throwable e){
if(session!=null){
session.close();
}
System.err.println("！！！！！！在创建项目过程中导入存储过程异常！！！！！！！！");
}
}
/**
* 目前的项目创建后无法导入存储过程，
* 所以要手动向各个项目导入
*/
public static void initAllProcedures(HibernateTemplate hibernateTemplate,ServletContext sc) {
Session session = null;
try{
String fileName = DataHelper.getSavePath4Base(sc)+"package_procedures.sql";
if(!new File(fileName).exists()) {
System.err.println("系统存储过程文件丢失");
return;
}
//从系统表中找到所有的现存的库
String currentDataSource = DataSourceContextHolder.getDataSourceName();
//HibernateTemplate hibernateTemplate = (HibernateTemplate) ac.getBean("hibernateTemplate");
DataSourceContextHolder.setDataSourceType("defaultDataSource");
session = hibernateTemplate.getSessionFactory().openSession();
List<Object[]> list_db = session.createSQLQuery("select id,projectId,dbname,host,username,pwd from sys_db").list();
System.out.println("需要导入的数据库个数："+list_db.size());
if(list_db!=null&&list_db.size()>0){
for(Object[] db:list_db){
System.out.println(db[0]+"--"+db[1]+"--"+db[2]+"--"+db[3]+"--"+db[4]+"--"+db[5]);
if(db.length<6) continue;
//System.out.println(db[2]+"--"+db[3]+"--"+db[4]+"--"+db[5]);
Object projectId = db[1];
Object dbname = db[2];
Object host = db[3];
Object username = db[4];
Object pwd = db[5];
if(projectId==null||dbname==null||host==null||username==null){
continue;
}
DBProperty pro = new DBProperty(host.toString(), dbname.toString(), fileName);
long start = System.currentTimeMillis();
int exitVal = 0;
try{
exitVal = MySqlImportAndExport.importSqlBySource4NoCreateDB(pro);
}catch(Throwable e){
exitVal = -1;
}
long end = System.currentTimeMillis();
System.out.println("####-------向数据库"+dbname+" 导入存储过程【运行代码："+exitVal+" ; 耗时："+(end-start)/(1000*60)+"m】");
}
session.flush();
session.close();
//切回原来的数据源
DataSourceContextHolder.setDataSourceType(currentDataSource);
}
}catch(Throwable e){
if(session!=null){
session.close();
}
}
}
}
I.java
package util.base;
/**
* @author jx
* @date 2015-08-25
*/
public final class I {
public static final String sys_existentProjectsWithDataSource= "existentProjectsWithDataSource";
}package util.base;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
/**
* 专门处理--返回给前台控件格式化的数据，多以json为主
* @author
*
*/
/**
* @author jx
* @date 2015-08-25
*/
public class PrintHelper {
Log log = LogFactory.getLog(PrintHelper.class);
/**
* @return object json:{}
* @param response
* @param olist
* @param total
* @throws IOException
*/
public static <T> void sendJsonMap(HttpServletResponse response,T olist,int total) throws IOException {
Map<String,Object> map=new HashMap<String, Object>();
map.put("total", total);
map.put("rows",olist);
String jsonstr= JSONObject.fromObject(map).toString();
System.out.println("(***)"+jsonstr);
response.setContentType("text/plain");
response.setCharacterEncoding("utf-8");
PrintWriter out=response.getWriter();
out.print(jsonstr);
out.close();
out.flush();
}
/**
* @return object json:{}
* @param response
* @param map
* @throws IOException
*/
public static <T> void sendJsonObject(HttpServletResponse response,T o) throws IOException {
String jsonstr= JSONObject.fromObject(o).toString();
response.setContentType("text/plain");
response.setCharacterEncoding("utf-8");
PrintWriter out=response.getWriter();
out.print(jsonstr);
out.close();
out.flush();
}
/**
* @return array json:[]
* @param response
* @param t
* @throws IOException
*/
public static void sendJsonArray(HttpServletResponse response,List t) throws IOException{
/*Map<String,Object> map=new HashMap<String, Object>();
map.put("total", total);
map.put("rows",olist);*/
String jsonstr=JSONArray.fromObject(t).toString();
response.setContentType("text/plain");
response.setCharacterEncoding("utf-8");
PrintWriter out=response.getWriter();
out.print(jsonstr);
out.close();
out.flush();
}
public static <T> void sendJsonString(HttpServletResponse response,T str) throws IOException{
//response.setContentType("text/html;charset=gbk");
response.setContentType("text/plain");
response.setCharacterEncoding("utf-8");
PrintWriter out=response.getWriter();
out.print(str);
out.close();
out.flush();
}
public static <T> void sendJsonHtm(HttpServletResponse response,T htm) throws IOException{
//response.setContentType("text/html;charset=gbk");
response.setContentType("text/html;charset=utf-8");
response.setCharacterEncoding("utf-8");
PrintWriter out=response.getWriter();
out.print(htm);
out.close();
out.flush();
}
/**
* 统一下载任意类型文件（浏览器自动识别文件类型）
* @param response
* @param file
* @param customFileName
* @throws UnsupportedEncodingException
*/
public static void downloadFile4AnyFile(HttpServletResponse response,File file,String customFileName) throws UnsupportedEncodingException{
if(!file.exists()){
return;
}
if(StringUtils.isBlank(customFileName)){
customFileName = file.getName();
}
//1.设置文件ContentType类型，这样设置，会自动判断下载文件类型
response.setContentType("multipart/form-data");
//2.设置文件头：最后一个参数是设置下载文件名(假如我们叫a.pdf)
response.setHeader("Content-Disposition", "attachment;fileName="+URLEncoder.encode(customFileName,"UTF-8"));
ServletOutputStream out = null;
FileInputStream inputStream = null;
try {
inputStream = new FileInputStream(file);
//3.通过response获取ServletOutputStream对象(out)
out = response.getOutputStream();
int b = 0;
byte[] buffer = new byte[1024];
while (b != -1){
b = inputStream.read(buffer);
//4.写到输出流(out)中
if(b!=-1){
out.write(buffer,0,b);
}
}
inputStream.close();
out.flush();
out.close();
} catch (IOException e) {
try {
if(inputStream!=null){
inputStream.close();
}
if(out!=null){
out.flush();
out.close();
}
} catch (IOException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
e.printStackTrace();
}
}
/**
* 统一下载Excel类型文件
* @param response
* @param file
* @param customFileName
* @throws UnsupportedEncodingException
*/
public static void downloadFile4Excel(HttpServletResponse response,File file,String customFileName) throws UnsupportedEncodingException{
if(!file.exists()){
return;
}
if(StringUtils.isBlank(customFileName)){
customFileName = file.getName();
}
if(!customFileName.endsWith(".xlsx")&&!customFileName.endsWith(".xls")){
customFileName +=".xlsx";
}
response.setCharacterEncoding("utf-8");
response.setContentType("application/vnd.ms-excel");
//response.setContentType("multipart/form-data");
/**解决下载的中文名无法显示的问题
* response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(filename, "UTF-8"));
*
* fileName = fileName.Replace("+", " ");
*/
response.setHeader("Content-Disposition", "attachment;fileName="
+ URLEncoder.encode(customFileName,"UTF-8").replace("+", " "));
InputStream inputStream = null;
OutputStream os = null;
try {
inputStream = new FileInputStream(file);
os = response.getOutputStream();
byte[] b = new byte[2048];
int length;
while ((length = inputStream.read(b)) > 0) {
os.write(b, 0, length);
}
// 这里主要关闭。
os.close();
inputStream.close();
} catch (Exception e) {
try {
if(os!=null){
os.close();
}
if(inputStream!=null){
inputStream.close();
}
} catch (IOException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
e.printStackTrace();
}
// 返回值要注意，要不然就出现下面这句错误！
//java+getOutputStream() has already been called for this response
}
}
下面是util.db包
package util.db;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
/**
* @author
* @version
*/
public class CallCmd {
public static void main(String[] args) {
Runtime rt = Runtime.getRuntime();
// 执行CMD命令，能够云行本地程序
Process p = null;
// String path =
// "F:\\Program Files (x86)\\Tencent\\QQ\\QQProtect\\Bin\\QQProtect.exe";
//
// try {
// p = rt.exec(path);
// } catch (IOException e) {
// // TODO Auto-generated catch block
// e.printStackTrace();
// }
// 调用浏览器，可以打开指定的网站
// String
// path="C:\\Users\\jrs521wlh\\AppData\\Local\\Google\\Chrome\\Application\\chrome.exe";
// String chrome ="www.google.com";
// String[] cmd={path,chrome};
// try {
// p = rt.getRuntime().exec(cmd);
// } catch (IOException e) {
// // TODO Auto-generated catch block
// e.printStackTrace();
// }
printProcess();
System.out.println("开始查找进程");
if (findProcess("QQ.exe")) {
System.out.println("查找进程存在");
} else {
System.out.println("不存在进程");
}
}
// 打印所有的进程信息
public static void printProcess() {
BufferedReader buff = null;
Process p = null;
try {
// 打印所有进程的信息
p = Runtime.getRuntime().exec("tasklist");
// 用流读出来
buff = new BufferedReader(new InputStreamReader(p.getInputStream()));
System.out.println("打印进程系信息");
String temp = null;
// 遍历
while ((temp = buff.readLine()) != null) {
System.out.println(buff.readLine());
}
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
if (buff != null) {
try {
buff.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
/**
* 查看进程是否运行
*
* @Title:
* @data:
* @author:
*
* @return
*/
public static boolean findProcess(String processName) {
BufferedReader buff = null;
try {
// 下面这句是列出含有processName的所有进程图形名字
Process p = Runtime.getRuntime().exec("tasklist");
buff = new BufferedReader(new InputStreamReader(p.getInputStream()));
String temp = null;
while ((temp = buff.readLine()) != null) {
System.out.println(temp);
if (temp.contains(processName)) {
return true;
}
}
return false;
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return false;
} finally {
if (buff != null) {
try {
buff.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
}
package util.db;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.io.UnsupportedEncodingException;
import entity.DatabaseBean;
/**
* 创建数据源配置文件程
*
* @author jx
*
*/
public class ConfigFileCreator {
/**
* 数据源模板xml的占位符
*/
private static final String placeholder_dataSourceId = "${dataSourceId}";
private static final String placeholder_driverClassName = "${driverClassName}";
private static final String placeholder_url = "${url}";
private static final String placeholder_username = "${username}";
private static final String placeholder_password = "${password}";
public static final String classPathCfgCatalog = "config";
private static final String dataSourceCfgTemplatePath = classPathCfgCatalog+"/dataSourceCfgTemplate.vm";
// private static final String defaultConfigFile
// =classPathCfgCatalog+"/db.properties";
private DatabaseBean database;
public ConfigFileCreator(DatabaseBean database) {
this.database = database;
}
public String create() {
String cfgFileStr = this.getTemplateString();
String fileName = createCfgFile(this.database, cfgFileStr);
return fileName;
}
public static String getNameByClassPath(String fileName){
return "classpath:" + ConfigFileCreator.classPathCfgCatalog + "/" + fileName;
}
/**
* 读取配置文件,并替换产生数据源配置文件
*
* @param dataSourceName
* @param url
* @param username
* @param password
*/
private String createCfgFile(DatabaseBean database, String cfgFileStr) {
String cfgFile = cfgFileStr;
String url = database.getUrlPrefix() + database.getHost() + ":"
+ database.getPort() + "/" + database.getDbName()
+ database.getUrlSuffix();
// 替换
cfgFile = cfgFile
.replace(placeholder_dataSourceId,
database.getDataSourceId())
.replace(placeholder_url, url)
.replace(placeholder_driverClassName,
database.getDriverClassNameValue())
.replace(placeholder_username, database.getUsername())
.replace(placeholder_password, database.getPwd());
String cfgStorePath = ConfigFileCreator.class.getClassLoader()
.getResource(classPathCfgCatalog).getPath();
String fileName = database.getDataSourceId() + ".xml";
String path = cfgStorePath+ File.separator+fileName;
System.out.println("path:" + path);
// 将替换好的串生成xml文件
try {
FileOutputStream outputStream = new FileOutputStream(path);
OutputStreamWriter outputStreamWriter = new OutputStreamWriter(
outputStream, "utf-8");
BufferedWriter bufferedWriter = new BufferedWriter(
outputStreamWriter);
bufferedWriter.write(cfgFile);
bufferedWriter.flush();
bufferedWriter.close();
return fileName;
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
private String getTemplateString() {
InputStream in = ConfigFileCreator.class.getClassLoader()
.getResourceAsStream(dataSourceCfgTemplatePath);
InputStreamReader reader = null;
try {
reader = new InputStreamReader(in, "utf-8");
BufferedReader bufferedReader = new BufferedReader(reader);
String line = null;
// 行分隔符
String separator = System.getProperty("line.separator");
StringBuffer sb = new StringBuffer();
while ((line = bufferedReader.readLine()) != null) {
sb.append(line + separator);
}
bufferedReader.close();
return sb.toString();
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
}
package util.db;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;
/**
* 读取配置文件config/db.properties
* @author
*
*/
public class ConfigFileReader {
private String targetConfigFile ;
public ConfigFileReader(String targetConfigFile) {
this.targetConfigFile = targetConfigFile;
}
public Properties reader() {
InputStream in = ConfigFileReader.class.getClassLoader().getResourceAsStream(targetConfigFile);
Properties p = new Properties();
try {
p.load(in);
} catch (IOException e1) {
e1.printStackTrace();
}
return p;
}
}
package util.db;
import org.apache.commons.lang.StringUtils;
import org.hibernate.Session;
import org.springframework.orm.hibernate4.HibernateTemplate;
public class DataSourceContextHolder {
private static final ThreadLocal contextHolder = new ThreadLocal();
public static void setDataSourceType(String dataSourceName) {
contextHolder.set(dataSourceName);
}
public static String getDataSourceName() {
return (String) contextHolder.get();
}
/**
* 主库
* @param hibernateTemplate
* @return
*/
public synchronized static Session getSystemDB(HibernateTemplate hibernateTemplate) {
contextHolder.set("defaultDataSource");
return hibernateTemplate.getSessionFactory().openSession();
}
/**
* 从库
* @param hibernateTemplate
* @param projectId
* @return
*/
public synchronized static Session getProjectDB( HibernateTemplate hibernateTemplate,String projectId) {
if(!StringUtils.isBlank(projectId)){
contextHolder.set("ds_" + projectId);
return hibernateTemplate.getSessionFactory().openSession();
}else{
return null;
}
}
}
package util.db;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.ServletContext;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.context.ApplicationContext;
import org.springframework.orm.hibernate4.HibernateTemplate;
import util.base.I;
import entity.DatabaseBean;
/**
* 系统启动后，自动加载从数据库
* @author jx
*
*/
public final class DataSourceManager {
static Log log = LogFactory.getLog(DataSourceManager.class);
public static final String mainProjectId="defaultDataSource";
public static final String mainDataSourceId="defaultDataSource";
private static final Map<String,String> existentProjectsWithDataSource = new HashMap<String,String>();
/**
* 项目启动后，数据源第一次初始化工作
* @param sc
*/
public static void init(ApplicationContext ac,ServletContext sc) {
//项目启动后，只有主库是默认连接好的数据源
HibernateTemplate hibernateTemplate = ac.getBean(HibernateTemplate.class);
List<DatabaseBean> list = (List<DatabaseBean>) hibernateTemplate.find(" from DatabaseBean");
//key:projectId ;value:datasourceId
//首先把系统的datasource放入
existentProjectsWithDataSource.put(DataSourceManager.mainProjectId, DataSourceManager.mainDataSourceId);
if(list!=null){
for(DatabaseBean db:list){
System.out.println(db);
if(!"off".equals(db.getStatus())){
log.info("准备为projectId:"+db.getProjectId()+" 创建dataSource："+db.getDataSourceId());
new MyDataSource(db,ac).create();
existentProjectsWithDataSource.put(db.getProjectId(), db.getDataSourceId());
log.info("创建dataSource："+db.getDataSourceId()+" 完毕");
}else{
log.info("忽略projectId:"+db.getProjectId()+" , status=off.");
}
}
}
sc.setAttribute(I.sys_existentProjectsWithDataSource, existentProjectsWithDataSource);
}
/**
* sys_db中存在记录但是没有数据源，就创建
* @param ac
* @param db
*/
public static void createNewDataSource4existentDB(ApplicationContext ac,DatabaseBean db){
if(existentProjectsWithDataSource.get(db.getProjectId())==null){
//初始化数据源到spring中
new MyDataSource(db,ac).create();
//放入map映射中
existentProjectsWithDataSource.put(db.getProjectId(), db.getDataSourceId());
log.info("新增数据源完成");
}
}
}
package util.db;
public class DBProperty{
public String username;//用户名
public String password ;//密码
public String host ;//导入的目标数据库所在的主机
public String port ;//使用的端口号
public String importDatabaseName ;//导入的目标数据库的名称
//应该存在服务器
public String importPath;//导入的目标文件所在的位置
public DBProperty(String host,String port, String username, String password,
String importDatabaseName, String importPath) {
this.username = username;
this.password = password;
this.host = host;
this.port = port;
this.importDatabaseName = importDatabaseName;
this.importPath = importPath;
}
public DBProperty(String host,String username, String password,
String importDatabaseName, String importPath) {
this.username = username;
this.password = password;
this.host = host;
this.port = "3306";
this.importDatabaseName = importDatabaseName;
this.importPath = importPath;
}
public DBProperty(String host,String importDatabaseName, String importPath) {
this.username = "root";
this.password = "root";
this.host = host;
this.port = "3306";
this.importDatabaseName = importDatabaseName;
this.importPath = importPath;
}
}
package util.db;
import java.util.Map;
import org.springframework.jdbc.datasource.lookup.DataSourceLookup;
public class DynamicDataSource extends MyAbstractRoutingDataSource {
/*
*
*/
@Override
public Object determineCurrentLookupKey() {
return DataSourceContextHolder.getDataSourceName();
}
@Override
public void setDataSourceLookup(DataSourceLookup dataSourceLookup) {
super.setDataSourceLookup(dataSourceLookup);
}
@Override
public void setDefaultTargetDataSource(Object defaultTargetDataSource) {
super.setDefaultTargetDataSource(defaultTargetDataSource);
}
@Override
public void setTargetDataSources(Map targetDataSources) {
super.setTargetDataSources(targetDataSources);
super.afterPropertiesSet();
}
}package util.db;
import java.io.IOException;
import org.springframework.beans.BeansException;
import org.springframework.beans.factory.config.BeanDefinition;
import org.springframework.beans.factory.support.BeanDefinitionRegistry;
import org.springframework.beans.factory.support.ChildBeanDefinition;
import org.springframework.beans.factory.support.DefaultListableBeanFactory;
import org.springframework.beans.factory.xml.ResourceEntityResolver;
import org.springframework.beans.factory.xml.XmlBeanDefinitionReader;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.context.ConfigurableApplicationContext;
/**
* 动态加载数据源
*
* @author jx
*
*/
public class DynamicLoadBean implements ApplicationContextAware {
private ConfigurableApplicationContext applicationContext = null;
public void setApplicationContext(ApplicationContext applicationContext)
throws BeansException {
this.applicationContext = (ConfigurableApplicationContext) applicationContext;
}
public ConfigurableApplicationContext getApplicationContext() {
return applicationContext;
}
/**
* 1.配置文件的位置固定 2.配置文件中bean的名字已确定
*
* @param configLocationString
*/
public void loadBean(String fileName) {
XmlBeanDefinitionReader beanDefinitionReader = new XmlBeanDefinitionReader(
(BeanDefinitionRegistry) getApplicationContext()
.getBeanFactory());
beanDefinitionReader.setResourceLoader(getApplicationContext());
beanDefinitionReader.setEntityResolver(new ResourceEntityResolver(
getApplicationContext()));
try {
beanDefinitionReader.loadBeanDefinitions(getApplicationContext()
.getResources(fileName));
} catch (BeansException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 未知方法
* @param beanName
* @param parentName
*/
@Deprecated
public void registBean(String beanName, String parentName) {
DefaultListableBeanFactory fcy = (DefaultListableBeanFactory) applicationContext
.getAutowireCapableBeanFactory();
BeanDefinition beanDefinition = new ChildBeanDefinition(parentName);
fcy.registerBeanDefinition(beanName, beanDefinition);
}
}
package util.db;
/**
* 该类完全复制org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource抽象类，
* 只是增加了 addDataSource(Object,Object) 方法，不然，就要改spring的jar包了
*/
/*
* Copyright 2002-2010 the original author or authors.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
import java.sql.Connection;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import javax.sql.DataSource;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.jdbc.datasource.AbstractDataSource;
import org.springframework.jdbc.datasource.lookup.DataSourceLookup;
import org.springframework.jdbc.datasource.lookup.JndiDataSourceLookup;
import org.springframework.util.Assert;
/**
* Abstract {@link javax.sql.DataSource} implementation that routes {@link #getConnection()}
* calls to one of various target DataSources based on a lookup key. The latter is usually
* (but not necessarily) determined through some thread-bound transaction context.
*
* @author Juergen Hoeller
* @since 2.0.1
* @see #setTargetDataSources
* @see #setDefaultTargetDataSource
* @see #determineCurrentLookupKey()
*/
public abstract class MyAbstractRoutingDataSource extends AbstractDataSource implements InitializingBean {
private Map<Object, Object> targetDataSources;
private Object defaultTargetDataSource;
private boolean lenientFallback = true;
private DataSourceLookup dataSourceLookup = new JndiDataSourceLookup();
private Map<Object, DataSource> resolvedDataSources;
private DataSource resolvedDefaultDataSource;
/**
* Specify the map of target DataSources, with the lookup key as key.
* The mapped value can either be a corresponding {@link javax.sql.DataSource}
* instance or a data source name String (to be resolved via a
* {@link #setDataSourceLookup DataSourceLookup}).
* <p>The key can be of arbitrary type; this class implements the
* generic lookup process only. The concrete key representation will
* be handled by {@link #resolveSpecifiedLookupKey(Object)} and
* {@link #determineCurrentLookupKey()}.
*/
public void setTargetDataSources(Map<Object, Object> targetDataSources) {
this.targetDataSources = targetDataSources;
}
/**@author jiangxu**/
public void addDataSource(Object key,Object dataSource){
this.targetDataSources.put(key, dataSource);
setTargetDataSources(this.targetDataSources);
}
/**
* Specify the default target DataSource, if any.
* <p>The mapped value can either be a corresponding {@link javax.sql.DataSource}
* instance or a data source name String (to be resolved via a
* {@link #setDataSourceLookup DataSourceLookup}).
* <p>This DataSource will be used as target if none of the keyed
* {@link #setTargetDataSources targetDataSources} match the
* {@link #determineCurrentLookupKey()} current lookup key.
*/
public void setDefaultTargetDataSource(Object defaultTargetDataSource) {
this.defaultTargetDataSource = defaultTargetDataSource;
}
/**
* Specify whether to apply a lenient fallback to the default DataSource
* if no specific DataSource could be found for the current lookup key.
* <p>Default is "true", accepting lookup keys without a corresponding entry
* in the target DataSource map - simply falling back to the default DataSource
* in that case.
* <p>Switch this flag to "false" if you would prefer the fallback to only apply
* if the lookup key was <code>null</code>. Lookup keys without a DataSource
* entry will then lead to an IllegalStateException.
* @see #setTargetDataSources
* @see #setDefaultTargetDataSource
* @see #determineCurrentLookupKey()
*/
public void setLenientFallback(boolean lenientFallback) {
this.lenientFallback = lenientFallback;
}
/**
* Set the DataSourceLookup implementation to use for resolving data source
* name Strings in the {@link #setTargetDataSources targetDataSources} map.
* <p>Default is a {@link JndiDataSourceLookup}, allowing the JNDI names
* of application server DataSources to be specified directly.
*/
public void setDataSourceLookup(DataSourceLookup dataSourceLookup) {
this.dataSourceLookup = (dataSourceLookup != null ? dataSourceLookup : new JndiDataSourceLookup());
}
public void afterPropertiesSet() {
if (this.targetDataSources == null) {
throw new IllegalArgumentException("Property 'targetDataSources' is required");
}
this.resolvedDataSources = new HashMap<Object, DataSource>(this.targetDataSources.size());
for (Map.Entry entry : this.targetDataSources.entrySet()) {
Object lookupKey = resolveSpecifiedLookupKey(entry.getKey());
DataSource dataSource = resolveSpecifiedDataSource(entry.getValue());
this.resolvedDataSources.put(lookupKey, dataSource);
}
if (this.defaultTargetDataSource != null) {
this.resolvedDefaultDataSource = resolveSpecifiedDataSource(this.defaultTargetDataSource);
}
}
/**
* Resolve the specified data source object into a DataSource instance.
* <p>The default implementation handles DataSource instances and data source
* names (to be resolved via a {@link #setDataSourceLookup DataSourceLookup}).
* @param dataSource the data source value object as specified in the
* {@link #setTargetDataSources targetDataSources} map
* @return the resolved DataSource (never <code>null</code>)
* @throws IllegalArgumentException in case of an unsupported value type
*/
protected DataSource resolveSpecifiedDataSource(Object dataSource) throws IllegalArgumentException {
if (dataSource instanceof DataSource) {
return (DataSource) dataSource;
}
else if (dataSource instanceof String) {
return this.dataSourceLookup.getDataSource((String) dataSource);
}
else {
throw new IllegalArgumentException(
"Illegal data source value - only [javax.sql.DataSource] and String supported: " + dataSource);
}
}
public Connection getConnection() throws SQLException {
return determineTargetDataSource().getConnection();
}
public Connection getConnection(String username, String password) throws SQLException {
return determineTargetDataSource().getConnection(username, password);
}
/**
* Retrieve the current target DataSource. Determines the
* {@link #determineCurrentLookupKey() current lookup key}, performs
* a lookup in the {@link #setTargetDataSources targetDataSources} map,
* falls back to the specified
* {@link #setDefaultTargetDataSource default target DataSource} if necessary.
* @see #determineCurrentLookupKey()
*/
protected DataSource determineTargetDataSource() {
Assert.notNull(this.resolvedDataSources, "DataSource router not initialized");
Object lookupKey = determineCurrentLookupKey();
DataSource dataSource = this.resolvedDataSources.get(lookupKey);
if (dataSource == null && (this.lenientFallback || lookupKey == null)) {
dataSource = this.resolvedDefaultDataSource;
}
if (dataSource == null) {
throw new IllegalStateException("Cannot determine target DataSource for lookup key [" + lookupKey + "]");
}
return dataSource;
}
/**
* Resolve the given lookup key object, as specified in the
* {@link #setTargetDataSources targetDataSources} map, into
* the actual lookup key to be used for matching with the
* {@link #determineCurrentLookupKey() current lookup key}.
* <p>The default implementation simply returns the given key as-is.
* @param lookupKey the lookup key object as specified by the user
* @return the lookup key as needed for matching
*/
protected Object resolveSpecifiedLookupKey(Object lookupKey) {
return lookupKey;
}
/**
* Determine the current lookup key. This will typically be
* implemented to check a thread-bound transaction context.
* <p>Allows for arbitrary keys. The returned key needs
* to match the stored lookup key type, as resolved by the
* {@link #resolveSpecifiedLookupKey} method.
*/
protected abstract Object determineCurrentLookupKey();
}
package util.db;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Properties;
import org.apache.commons.lang.StringUtils;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.orm.hibernate4.HibernateTemplate;
import entity.DatabaseBean;
/**
* 创建、备份数据库
*
* @author jx
*
*/
public class MyDatabase {
public static final String configFile = "config/config.properties";
private String projectId;
private String defaultDBName;
private HibernateTemplate hibernateTemplate;
/*public MyDatabase(){
}*/
public MyDatabase(HibernateTemplate hibernateTemplate, String projectId) {
this.hibernateTemplate = hibernateTemplate;
this.projectId = projectId;
this.defaultDBName = "ds_"+this.projectId;
}
public DatabaseBean copyMaindbByDBName(String dbName) throws Exception {
InputStream is = MyDatabase.class.getClassLoader().getResourceAsStream(
configFile);
Properties pro = new Properties();
pro.load(is);
// 备份主库
int status_backup = MySqlImportAndExport.export_high(pro);
if(status_backup!=0){
throw new Exception("创建项目过程中，备份主库失败！！");
}
// 将新库默认库名改一下
if(!StringUtils.isBlank(dbName)){
pro.put("importDB.dbName", this.defaultDBName);
}
DBProperty properties = new DBProperty(pro.getProperty("importDB.host"), this.defaultDBName, pro.getProperty("importDB.importPath"));
// 建库
MySqlImportAndExport.importSqlBySource4CreateDB(properties);
//导入数据
MySqlImportAndExport.importSqlBySource4NoCreateDB(properties);
//删除数据库文件
File dbFile = new File(pro.get("backup.exportPath").toString());
if(dbFile.exists()){
dbFile.delete();
}
// 向主库插入数据源记录
return this.insertDataSource4mainDB(pro);
// return null;
}
public DatabaseBean insertDataSource4mainDB(Properties pro) {
// 创建数据源记录
// 获取建库的默认信息
DatabaseBean db = new DatabaseBean();
SimpleDateFormat sf = new SimpleDateFormat("yyyyMMddHHmmss");
String id = sf.format(new Date());
db.setId(this.projectId);
db.setProjectId(this.projectId);
db.setDbName(this.defaultDBName);
db.setDataSourceId(this.defaultDBName);
db.setDriverClassNameValue(pro.getProperty("main.driver"));
db.setUrlPrefix(pro.getProperty("dataSourceRecord.urlPrefix"));
db.setHost(pro.getProperty("importDB.host"));
db.setPort(pro.getProperty("importDB.port"));
db.setUrlSuffix(pro.getProperty("dataSourceRecord.urlSuffix"));
db.setUsername(pro.getProperty("importDB.username"));
db.setPwd(pro.getProperty("importDB.password"));
db.setCreatedate(id);
db.setStatus("on");
//保存当前数据源
String currentDataSourceId = DataSourceContextHolder.getDataSourceName();
//切换到主库中
DataSourceContextHolder.setDataSourceType(DataSourceManager.mainDataSourceId);
SessionFactory factory = hibernateTemplate.getSessionFactory();
Session session = factory.openSession();
session.beginTransaction();
session.save(db);
session.getTransaction().commit();
session.close();
//切换到初始状态
DataSourceContextHolder.setDataSourceType(currentDataSourceId);
return db;
}
public static void main(String[] args) throws IOException, InterruptedException {
DBProperty pro = new DBProperty("192.168.0.202", "b", "d:/a.sql");
// 建库,导入数据
long start = System.currentTimeMillis();
int exitVal = MySqlImportAndExport.importSqlBySource4CreateDB(pro);
long end = System.currentTimeMillis();
System.out.println("耗时："+(end-start)/(1000*60)+"m");
}
}
package util.db;
import java.io.File;
import org.apache.commons.dbcp.BasicDataSource;
import org.springframework.context.ApplicationContext;
import entity.DatabaseBean;
public class MyDataSource {
private DatabaseBean database;
private ApplicationContext ac;
public MyDataSource(DatabaseBean db, ApplicationContext ac) {
this.database = db;
this.ac = ac;
}
public MyDataSource create() {
String dataSourceId = this.database.getDataSourceId();
// 动态创建数据源配置文件
// ConfigFileCreator.createConfigFile(dataSourceName, url);
String fileName = new ConfigFileCreator(this.database).create();
// 加载刚刚生成的数据源配置文件
DynamicLoadBean dynamicBeanLoad = (DynamicLoadBean) ac.getBean("dynamicLoadBean");
dynamicBeanLoad.loadBean(ConfigFileCreator.getNameByClassPath(fileName));
BasicDataSource dataSource = (BasicDataSource) ac.getBean(dataSourceId);
//获取动态数据源
DynamicDataSource dynamicDataSource = (DynamicDataSource) ac.getBean("dynamicDataSource");
//将数据源放入DynamicDataSource中，统一动态切分
dynamicDataSource.addDataSource(dataSourceId, dataSource);
DataSourceContextHolder.setDataSourceType(dataSourceId);
//删除配置文件
String path = MyDataSource.class.getResource("/config").getPath();
File file = new File(path+File.separator+fileName);
if(file.exists()){
file.delete();
}
return this;
}
public static void main(String[] args) {
String fileName = MyDataSource.class.getResource("/config").getPath();
// File file = new File(fileName);
System.out.println(fileName);
/*if(!file.exists()){
try {
file.createNewFile();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}else{
file.delete();
}*/
}
}
package util.db;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.util.Properties;
import org.apache.commons.lang.StringUtils;
/**
* 在进行导出的时候，需要注意命令语句的运行环境，如果已经将mysql安装路径下的bin加入到
* 系统的path变量中，那么在导出的时候可以直接使用命令语句，否则，就需要在执行命令语句的
* 时候加上命令所在位置的路径，即mysql安装路径想的bin下的mysqldump命令
*
* 目前只支持最多连续2条命令
* @author jx
*
*/
public class MySqlImportAndExport {
// 查询数据库信息
// SELECT * FROM information_schema.SCHEMATA where SCHEMA_NAME='aa';
// mysqldump -uusername -ppassword -hhost -Pport exportDatabaseName >
// exportPath
/**
* 根据属性文件的配置导出指定位置的指定数据库到指定位置
*
* 这个方法在命令执行过程中无法接受返回值，故任务完成的时间无法确定， 必须通过Thread.sleep(N秒)的方式不确定睡眠的等待db备份结束
*
* @param properties
* @throws IOException
*/
/*
* @Deprecated public static void export(Properties properties) throws
* IOException { Runtime runtime = Runtime.getRuntime(); String command =
* getExportCommand(properties); String ss = ""; try {
* runtime.exec(command);// 这里简单一点异常我就直接往上抛 //睡眠3秒,等待备份文件生成完毕 //
* Thread.sleep(3000); } catch (Exception e) { ss = e.toString(); } }
*/
/**
* 根据属性文件的配置导出指定位置的指定数据库到指定位置 // mysqldump -uusername -ppassword -hhost
* -Pport exportDatabaseName > exportPath export()的升级版，改良了无需采用不确定睡眠的等待
*
* @param properties
* @throws IOException
* @throws InterruptedException
* @return int [0正常，非0不正常]
*/
public static int export_high(Properties properties) throws IOException,
InterruptedException {
Runtime runtime = Runtime.getRuntime();
String command = getExportCommand(properties);
Process proc = runtime.exec(command);// 这里简单一点异常我就直接往上抛
InputStream stderr = proc.getInputStream();
InputStreamReader isr = new InputStreamReader(stderr);
BufferedReader br = new BufferedReader(isr);
String line = null;
System.out.println("<msg>----------------------------");
while ((line = br.readLine()) != null) {
System.out.println(line);
}
System.out.println("");
int exitVal = proc.waitFor();
/**
* exitVal==0正常，非0不正常
*/
System.out.println("Process exitValue[0正常，非0不正常]: " + exitVal);
br.close();
isr.close();
stderr.close();
System.out.println("----------------------------</msg>");
return exitVal;
}
/**
* 单独执行一条命令
* @param command
* @return
* @throws IOException
* @throws InterruptedException
*/
public static int execOneCommand(String command) throws IOException,
InterruptedException {
if (StringUtils.isBlank(command)) {
return 0;
}
Runtime runtime = Runtime.getRuntime();
Process proc = runtime.exec(command);// 这里简单一点异常我就直接往上抛
InputStream stderr = proc.getInputStream();
InputStreamReader isr = new InputStreamReader(stderr);
BufferedReader br = new BufferedReader(isr);
String line = null;
System.out.println("<msg>----------------------------");
while ((line = br.readLine()) != null) {
System.out.println(line);
}
int exitVal = proc.waitFor();
/**
* exitVal==0正常，非0不正常
*/
System.out.println("Process exitValue[0正常，非0不正常]: " + exitVal);
br.close();
isr.close();
stderr.close();
System.out.println("----------------------------</msg>");
return exitVal;
}
/**
* 利用属性文件提供的配置来拼装命令语句 在拼装命令语句的时候有一点是需要注意的：一般我们在命令窗口直接使用命令来
* 进行导出的时候可以简单使用“>”来表示导出到什么地方，即mysqldump -uusername -ppassword databaseName
* > exportPath， 但在Java中这样写是不行的，它需要你用-r明确的指出导出到什么地方，如： mysqldump -uusername
* -ppassword databaseName -r exportPath。
*
* @param properties
* @return
*/
private static String getExportCommand(Properties properties) {
StringBuffer command = new StringBuffer();
String username = properties.getProperty("backup.username");// 用户名
String password = properties.getProperty("backup.password");// 用户密码
String exportDatabaseName = properties.getProperty("backup.dbName");
String host = properties.getProperty("backup.host");// 从哪个主机导出数据库，如果没有指定这个值，则默认取localhost
String port = properties.getProperty("backup.port");// 使用的端口号
// 应该存在服务器
String exportPath = properties.getProperty("backup.exportPath");// 导出路径
// 注意哪些地方要空格，哪些不要空格
command.append("mysqldump -u").append(username)
.append(" -p")
.append(password)
// 密码是用的小p，而端口是用的大P。
.append(" -h").append(host).append(" -P").append(port)
.append(" ").append(exportDatabaseName).append(" -r ")
.append(exportPath);
return command.toString();
}
/**
* 将sql文件导入到存在的DB中 exitVal==0正常，非0不正常
*
* @param properties
* @throws IOException
* @throws InterruptedException
*/
public static int importSqlBySource4CreateDB(DBProperty properties)
throws IOException, InterruptedException {
Runtime runtime = Runtime.getRuntime();
// 因为在命令窗口进行mysql数据库的导入一般分三步走，所以所执行的命令将以字符串数组的形式出现
String cmdarray[] = getCommond4CreateDB(properties);// 根据属性文件的配置获取数据库导入所需的命令，组成一个数组
// runtime.exec(cmdarray);//这里也是简单的直接抛出异常
Process process = runtime.exec(cmdarray[0]);
// 执行了第一条命令以后已经登录到mysql了，所以之后就是利用mysql的命令窗口
// 进程执行后面的代码
OutputStream os = process.getOutputStream();
OutputStreamWriter writer = new OutputStreamWriter(os);
// 命令2和命令1的交互
writer.write(cmdarray[1] + "\r\n");
writer.flush();
writer.close();
os.close();
InputStream stderr = process.getErrorStream();
InputStreamReader isr = new InputStreamReader(stderr);
BufferedReader br = new BufferedReader(isr);
String line = null;
System.out.println("<msg>-------importData-----start----------------");
while ((line = br.readLine()) != null) {
System.out.println(line);
}
int exitVal = process.waitFor();
/**
* exitVal==0正常，非0不正常
*/
System.out.println("Process exitValue[0正常，非0不正常]: " + exitVal);
br.close();
isr.close();
stderr.close();
// long end = System.currentTimeMillis();
System.out.println("-------importData------end---------------</msg>");
return exitVal;
}
/**
* 建库命令
* @param properties
* @return
*/
private static String[] getCommond4CreateDB(DBProperty pro) {
String username = pro.username;// 用户名
String password = pro.password;// 密码
String host = pro.host;// 导入的目标数据库所在的主机
String port = pro.port;// 使用的端口号
String importDatabaseName = pro.importDatabaseName;// 导入的目标数据库的名称
// 应该存在服务器
// String importPath = pro.importPath;// 导入的目标文件所在的位置
// 第一步，获取登录命令语句
String loginCommand = new StringBuffer().append("mysql -u")
.append(username).append(" -p").append(password).append(" -h")
.append(host).append(" -P").append(port).toString();
// 第二步 建库
String sql_createDB = "create database " + importDatabaseName + ";";
// 需要返回的命令语句数组
String[] commands = new String[] { loginCommand, sql_createDB};
return commands;
}
/**
* 将sql文件导入到存在的DB中 exitVal==0正常，非0不正常
*
* @param properties
* @throws IOException
* @throws InterruptedException
*/
public static int importSqlBySource4NoCreateDB(DBProperty properties)
throws IOException, InterruptedException {
Runtime runtime = Runtime.getRuntime();
// 因为在命令窗口进行mysql数据库的导入一般分三步走，所以所执行的命令将以字符串数组的形式出现
String cmdarray[] = getCommandByImportSqlFile(properties);// 根据属性文件的配置获取数据库导入所需的命令，组成一个数组
// runtime.exec(cmdarray);//这里也是简单的直接抛出异常
Process process = runtime.exec(cmdarray[0]);
// 执行了第一条命令以后已经登录到mysql了，所以之后就是利用mysql的命令窗口
// 进程执行后面的代码
OutputStream os = process.getOutputStream();
OutputStreamWriter writer = new OutputStreamWriter(os);
// 命令2和命令1的交互
writer.write(cmdarray[1] + "\r\n");
writer.flush();
writer.close();
os.close();
InputStream stderr = process.getErrorStream();
InputStreamReader isr = new InputStreamReader(stderr);
BufferedReader br = new BufferedReader(isr);
String line = null;
System.out.println("<msg>-------importData-----start----------------");
while ((line = br.readLine()) != null) {
System.out.println(line);
}
int exitVal = process.waitFor();
/**
* exitVal==0正常，非0不正常
*/
System.out.println("Process exitValue[0正常，非0不正常]: " + exitVal);
br.close();
isr.close();
stderr.close();
// long end = System.currentTimeMillis();
System.out.println("-------importData------end---------------</msg>");
return exitVal;
}
/**
* 与getImportCommandBySource(Properties properties) 功能相同
* 暂时不支持中文字符（待调研source命令）
*
* @param map
* @return
*/
private static String[] getCommandByImportSqlFile(DBProperty pro) {
String username = pro.username;// 用户名
String password = pro.password;// 密码
String host = pro.host;// 导入的目标数据库所在的主机
String port = pro.port;// 使用的端口号
String importDatabaseName = pro.importDatabaseName;// 导入的目标数据库的名称
// 应该存在服务器
String importPath = pro.importPath;// 导入的目标文件所在的位置
// 第一步，获取登录命令语句
String loginCommand = new StringBuffer().append("mysql -u")
.append(username).append(" -p").append(password).append(" -h")
.append(host).append(" -P").append(port)
.append(" --default-character-set=gbk -D")
.append(importDatabaseName).toString();
// 第二步，获取导入的命令语句 (由于mysql 中source命令的规则，
// \后边不能是. ；所以如果未见名带有点就麻烦了)，故将\改为/
String importCommand = "source " + importPath.replace("\\", "/") + ";";
// 需要返回的命令语句数组
String[] commands = new String[] { loginCommand, importCommand };
return commands;
}
}
jsp和js
project.js
/**
* 显示项目列表
*/
function initProjectList(){
//获取项目列表
$.post('/multiDbDemo/project/listProjects.do',{},function(data){
if(data&&data.total>0){
var rows = data.rows;
var htm = '';
for(var i in rows){
var projectId = rows[i].projectId;
var projectName = rows[i].projectName;
var userId = rows[i].userId;
var pwd = rows[i].pwd;
htm +='<tr id="id_'+i+'"><td>项目编号：'+projectId+'</td><td>项目名称：'+projectName+'</td><td>登录用户：'+userId+'</td><td>密码：'+pwd+'</td><td><a href="#" οnclick="deleteProject(\''+projectId+'\')">删除</a></td></tr>';
}
$('#id_adminIndex_projectList').append(htm);
}
},'json');
}
/**
* 删除项目
* @param projectId
*/
function deleteProject(projectId){
$.post('/multiDbDemo/project/delProject.do',{projectId:projectId},function(data){
if('success'==data){
window.location.href='/multiDbDemo/adminIndex.jsp'
}else{
alert('系统异常');
}
});
}
/**
* 跳转到新增项目页面
*/
function jump2Page(location,projectId){
var page = '/multiDbDemo/login.jsp';
if('addProject'==location){//新增项目也
page='/multiDbDemo/addProject.jsp';
}else if('login'==location){//退回登录页
page='/multiDbDemo/login.jsp';
}else if('addMsg'==location){//退回登录页
page='/multiDbDemo/addMsg.jsp';
if(projectId){
page+='?projectId='+projectId;
}
}else if('home'==location){//退回登录页
page='/multiDbDemo/home.jsp';
if(projectId){
page+='?projectId='+projectId;
}
}
window.location.href=page;
}
/**
* 新增项目
*/
function addProject(){
var projectId = $('#id_addProject_projectId').val();
var projectName = $('#id_addProject_projectName').val();
var userId = $('#id_addProject_userId').val();
var pwd = $('#id_addProject_pwd').val();
if(!projectId||!projectName){
alert("项目代码或项目名称不同为空");
}
$.post('/multiDbDemo/project/addProject.do',{projectId:projectId,projectName:projectName,userId:userId,pwd:pwd},function(data){
if('success'==data){
window.location.href='/multiDbDemo/adminIndex.jsp'
}else if('isUsed'==data){
alert('项目代码已经存在');
}else{
alert('系统异常');
}
});
}
/**
* 初始化该（用户，密码）的项目列表
* @param userId
* @param pwd
*/
function initProjectListByUserIdAndPwd(userId,pwd){
$.post('/multiDbDemo/project/listProjectsByUserIdAndPwd.do',{userId:userId,pwd:pwd},function(data){
if(data&&data.total>0){
var rows = data.rows;
var htm = '';
for(var i in rows){
var projectId = rows[i].projectId;
var projectName = rows[i].projectName;
var userId = rows[i].userId;
var pwd = rows[i].pwd;
htm +='<tr><td>项目编号：'+projectId+'</td><td>项目名称：'+projectName+'</td><td>登录用户：'+userId+'</td><td>密码：'+pwd+'</td><td><a href="#" οnclick="comeInProject(\''+projectId+'\',\''+userId+'\')">进入</a></td></tr>';
}
$('#id_projectIndex_projectList').append(htm);
}
},'json');
}
/**
* 进入项目
* @param projectId
*/
function comeInProject(projectId,userId){
window.location.href='/multiDbDemo/home.jsp?projectId='+projectId+'&userId='+userId;
}
/**
* 初始化业务信息
* @param projectId
*/
function initMsg(projectId){
$.post('/multiDbDemo/project/getProject.do',{projectId:projectId},function(data){
if(data&&data.userId){
$('#id_home_userId').html(data.userId);
}
},'json');
$.post('/multiDbDemo/project/listMsgByProjectId.do',{projectId:projectId},function(data){
if(data&&data.total>0){
var rows = data.rows;
var htm = '';
for(var i in rows){
var name = rows[i].name;
var note = rows[i].note;
htm +='<tr><td>栏目：'+name+'</td><td>内容：'+note+'</td><td><a href="#" οnclick="delMsg(\''+projectId+'\',\''+name+'\')">删除</a></td></tr>';
}
$('#id_home_msgList').append(htm);
}
},'json');
}
/**
* 删除具体某个项目库（从库）的栏目
* @param projectId
* @param name
*/
function delMsg(projectId,name){
$.post('/multiDbDemo/project/delMsg.do',{projectId:projectId,name:name},function(data){
if('success'==data){
jump2Page('home', projectId);
}else{
alert('系统异常');
}
});
}
function addMsg(){
var projectId = $('#id_addMsg_projectId').val();
var name = $('#id_addMsg_name').val();
var note = $('#id_addMsg_note').val();
if(!projectId||!name){
alert("栏目名称或所在项目不同为空");
}
$.post('/multiDbDemo/project/addMsg.do',{projectId:projectId,name:name,note:note},function(data){
if('success'==data){
jump2Page('home', projectId);
}else if('isUsed'==data){
alert('栏目名称已经存在');
}else{
alert('系统异常');
}
});
}
jQuery v1.11.1自己去下吧
jsp
addMsg.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<script type="text/javascript" src="js/jquery.min.js"></script>
<script type="text/javascript" src="js/project.js"></script>
<title>Insert title here</title>
<%
String projectId = request.getParameter("projectId");
System.out.println(projectId);
%>
</head>
<body>
新增栏目<br /><br />
<div id="id_addMsg">
项目名称：<input type="text" id="id_addMsg_projectId" value="<%=projectId %>" readonly="readonly"><br />
栏目名称：<input type="text" id="id_addMsg_name" ><br />
栏目内容：<input type="text" id="id_addMsg_note" /><br />
<input type="button" value="submit" οnclick="addMsg()"/>
</div>
<br />
<br />
</body>
</html>addProject.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<script type="text/javascript" src="js/jquery.min.js"></script>
<script type="text/javascript" src="js/project.js"></script>
<title>Insert title here</title>
</head>
<body>
新增项目<br /><br />
<div id="id_form_addProject">
项目代码：<input type="text" id="id_addProject_projectId" ><br />
项目名称：<input type="text" id="id_addProject_projectName" /><br />
登录用户：<input type="text" id="id_addProject_userId" /><br />
密 码：<input type="text" id="id_addProject_pwd" /><br />
<input type="button" value="submit" οnclick="addProject()"/>
</div>
<br />
<br />
</body>
</html>adminIndex.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<script type="text/javascript" src="js/jquery.min.js"></script>
<script type="text/javascript" src="js/project.js"></script>
<title>Insert title here</title>
</head>
<body>
项目管理<br /><br />
<a href="#" οnclick="jump2Page('addProject')">新增</a>|<a href="#" οnclick="jump2Page('login')">退出</a><br />
<table id="id_adminIndex_projectList"></table>
<br />
----------------------------------------------------
<br />
<script type="text/javascript">
initProjectList();
</script>
</body>
</html>home.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<script type="text/javascript" src="js/jquery.min.js"></script>
<script type="text/javascript" src="js/project.js"></script>
<title>Insert title here</title>
<%
String projectId = request.getParameter("projectId");
System.out.println(projectId);
%>
</head>
<body>
hello<br />
<div>
项目：<a id="id_home_projectId"><%=projectId %></a>
登录用户：<a id="id_home_userId"></a>
</div>
<br />
----------------------------------------------------
<br />
<br />
<a href="#" οnclick="jump2Page('addMsg','<%=projectId %>')">新增栏目</a>|<a href="#" οnclick="jump2Page('login')">退回登录页</a><br />
<table id="id_home_msgList"></table>
<br />
<script type="text/javascript">
initMsg('<%=projectId %>');
</script>
</body>
</html>login.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
系统登录<br />
<form action="/multiDbDemo/systemLogin.do" method="post">
管理员：<input type="text" name="userId" value="admin"/><br />
密 码：<input type="text" name="pwd" value="admin"/>
<input type="submit" value="submit"/>
</form>
<br />
----------------------------------------------------
<br />
项目登录<br />
<form action="/multiDbDemo/projectLogin.do" method="post">
登录用户：<input type="text" name="userId"/><br />
密 码：<input type="text" name="pwd"/>
<input type="submit" value="submit"/>
</form>
<br />
</body>
</html>projectIndex.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<script type="text/javascript" src="js/jquery.min.js"></script>
<script type="text/javascript" src="js/project.js"></script>
<title>Insert title here</title>
<%
String userId = request.getParameter("userId");
String pwd = request.getParameter("pwd");
System.out.println(userId);
System.out.println(pwd);
%>
</head>
<body>
welcome<br />
<div>
登录用户：<a id="id_projectIndex_userId"><%=userId %></a>
密码：<a id="id_projectIndex_pwd"><%=pwd %></a>
</div>
<br />
----------------------------------------------------
<br />
该登录用户的项目列表<br />
<table id="id_projectIndex_projectList"></table>
<br />
<script type="text/javascript">
initProjectListByUserIdAndPwd('<%=userId %>','<%=pwd %>');
</script>
</body>
</html>累死了，希望能帮助未来的自己^_^
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
