本文共 19365 字，大约阅读时间需要 64 分钟。

为了保存数据库的备份信息，需要再新建一个数据库。

create database bookstore_back;use bookstore_back;

接下来，还要创建一张表，存储数据库备份的详细信息，建表SQL语句如下：

create table dbback(	id varchar(40) primary key,	filename varchar(255) not null,	backtime datetime not null,	description varchar(255));

开发domain层

在cn.liayun.domain包下创建一个DbBack类，该类的具体代码如下：

package cn.liayun.domain;import java.util.Date;//类的每一个对象代表数据库中的一条备份信息public class DbBack {
   	private String id;	private String filename;//备份文件的名称	private Date backtime;	private String description;		public String getId() {
   		return id;	}	public void setId(String id) {
   		this.id = id;	}	public String getFilename() {
   		return filename;	}	public void setFilename(String filename) {
   		this.filename = filename;	}	public Date getBacktime() {
   		return backtime;	}	public void setBacktime(Date backtime) {
   		this.backtime = backtime;	}	public String getDescription() {
   		return description;	}	public void setDescription(String description) {
   		this.description = description;	}}

开发数据访问层(dao、dao.impl)

为了连接bookstore_back数据库，所以应在类目录下的C3P0配置文件（c3p0-config.xml）中加入name为back的配置信息，以此来创建数据源。

   	
    	
    		
     
      com.mysql.jdbc.Driver
     		
     
      jdbc:mysql://localhost:3306/bookstore
     		
     
      root
     		
     
      liayun
     				
     
      10
     		
     
      30
     
     		
     
      20
     		
     
      5
     		
     
      200
     	
    	
    	
    		
     
      com.mysql.jdbc.Driver
     		
     
      jdbc:mysql://localhost:3306/bookstore_back
     		
     
      root
     		
     
      liayun
     				
     
      1
     		
     
      1
     
     		
     
      1
     		
     
      1
     		
     
      1
     	
    
   

这样，我们还要修改cn.liayun.utils包下的JdbcUtils工具类，用于读取name为back的配置信息创建数据源。

package cn.liayun.utils;import java.sql.Connection;import javax.sql.DataSource;import com.mchange.v2.c3p0.ComboPooledDataSource;public class JdbcUtils {
   		private static ThreadLocal
   
     tl = new ThreadLocal
    
     ();		private static DataSource ds;	private static DataSource back_ds;		static {
   		ds = new ComboPooledDataSource();		back_ds = new ComboPooledDataSource("back");	}		public static DataSource getDataSource() {
   		return ds;	}		public static DataSource getDataSource_back() {
   		return back_ds;	}		public static Connection getConnection() {
   		try {
   			//首先得到当前线程上绑定的连接			Connection conn = tl.get();			if (conn == null) {
   				conn = ds.getConnection();//如果当前线程上没有绑定一个连接，则从数据库连接池拿一个连接				conn.setAutoCommit(false);//开启事务			}			tl.set(conn);//把连接绑定到当前线程上去			return conn;		} catch (Exception e) {
   			throw new RuntimeException(e);		}	}		public static void startTransaction() {
   		try {
   			Connection conn = getConnection();			conn.setAutoCommit(false);		} catch (Exception e) {
   			throw new RuntimeException(e);		}	}		public static void commitTransaction() {
   		try {
   			Connection conn = getConnection();			if (conn != null) {
   				conn.commit();			}		} catch (Exception e) {
   			throw new RuntimeException(e);		}	}		public static void closeConn() {
   		Connection conn = null;		try {
   			conn = getConnection();			if (conn != null) {
   				conn.close();			}		} catch (Exception e) {
   			throw new RuntimeException(e);		} finally {
   			/*			if (conn != null) {				tl.remove();			}			*/			tl.remove();//千万要注意，解除当前线程上绑定的连接（从ThreadLocal容器中移除掉对应当前线程上的连接）		}	}	}
    
   

准备好以上这些工作之后，我们正式步入开发数据库访问层的阶段。

package cn.liayun.dao;import java.util.List;import cn.liayun.domain.DbBack;public interface DbBackDao {
   	void add(DbBack back);	List
   
     getAll();	DbBack find(String id);}
   

接下来，我们就要编写该接口的具体实现类了，在cn.liayun.dao.impl包下创建DbBackDao接口的实现类——DbBackDaoImpl.java。

package cn.liayun.dao.impl;import java.util.List;import org.apache.commons.dbutils.QueryRunner;import org.apache.commons.dbutils.handlers.BeanHandler;import org.apache.commons.dbutils.handlers.BeanListHandler;import cn.liayun.dao.DbBackDao;import cn.liayun.domain.DbBack;import cn.liayun.utils.JdbcUtils;public class DbBackDaoImpl implements DbBackDao {
   	@Override	public void add(DbBack back) {
   		try {
   			QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource_back());			String sql = "insert into dbback(id,filename,backtime,description) values(?,?,?,?)";			Object[] params = {
   back.getId(), back.getFilename(), back.getBacktime(), back.getDescription()};			runner.update(sql, params);		} catch (Exception e) {
   			throw new RuntimeException(e);		}	}		@Override	public List
   
     getAll() {
   		try {
   			QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource_back());			String sql = "select * from dbback order by backtime desc";			return runner.query(sql, new BeanListHandler
    
     (DbBack.class));		} catch (Exception e) {
   			throw new RuntimeException(e);		}	}		@Override	public DbBack find(String id) {
   		try {
   			QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource_back());			String sql = "select * from dbback where id=?";			return runner.query(sql, id, new BeanHandler
     
      (DbBack.class));		} catch (Exception e) {
   			throw new RuntimeException(e);		}	}}
     
    
   

照理说，我们每写好一层的代码，就应该去测试的，这才是一个合格的程序员嘛！但这一步，我略过了。

开发service层(service层对web层提供所有的业务服务)

在cn.liayun.service包下的BusinessService接口中添加与数据库管理模块相关的服务。

package cn.liayun.service;import java.util.List;import cn.liayun.domain.Book;import cn.liayun.domain.Cart;import cn.liayun.domain.Category;import cn.liayun.domain.DbBack;import cn.liayun.domain.Order;import cn.liayun.domain.PageBean;import cn.liayun.domain.QueryInfo;import cn.liayun.domain.User;public interface BusinessService {
   	/********************************************	 * 分类相关的服务	 ********************************************/	void addCategory(Category c);	Category findCategory(String id);	List
   
     getAllCategory();	/********************************************	 * 图书相关的服务	 ********************************************/	void addBook(Book book);	Book findBook(String id);	PageBean bookPageQuery(QueryInfo info);		public List
    
      getAllBook();	/********************************************	 * 用户相关的服务	 ********************************************/	void addUser(User user);	User findUser(String username, String password);	User findUser(String id);	/********************************************	 * 订单相关的服务	 ********************************************/	//使用用户的购物车来生成一个订单，然后存入数据库中	void saveOrder(Cart cart, User user);	Order findOrder(String id);	List
     
       getOrderByStatus(boolean status);		//更新订单状态	public void updatOrder(String id, boolean status);		/********************************************	 * 数据库相关的服务	 ********************************************/	public void addDbBack(DbBack back);		public List
      
        getAllDbBack();		public DbBack findDbBack(String id);}
      
     
    
   

接着，在BusinessService接口的具体实现类（BusinessServiceImpl.java）中实现以上与数据库管理模块相关的服务。

package cn.liayun.service.impl;import java.util.Date;import java.util.HashSet;import java.util.List;import java.util.Map;import java.util.Set;import java.util.UUID;import cn.liayun.dao.BookDao;import cn.liayun.dao.CategoryDao;import cn.liayun.dao.DbBackDao;import cn.liayun.dao.OrderDao;import cn.liayun.dao.UserDao;import cn.liayun.domain.Book;import cn.liayun.domain.Cart;import cn.liayun.domain.CartItem;import cn.liayun.domain.Category;import cn.liayun.domain.DbBack;import cn.liayun.domain.Order;import cn.liayun.domain.OrderItem;import cn.liayun.domain.PageBean;import cn.liayun.domain.QueryInfo;import cn.liayun.domain.QueryResult;import cn.liayun.domain.User;import cn.liayun.factory.DaoFactory;import cn.liayun.service.BusinessService;public class BusinessServiceImpl implements BusinessService {
   	private CategoryDao cDao = DaoFactory.getInstance().createDao(CategoryDao.class);	private BookDao bDao = DaoFactory.getInstance().createDao(BookDao.class);	private UserDao uDao = DaoFactory.getInstance().createDao(UserDao.class);	private OrderDao oDao = DaoFactory.getInstance().createDao(OrderDao.class);	private DbBackDao dDao = DaoFactory.getInstance().createDao(DbBackDao.class);		/********************************************	 * 分类相关的服务	 ********************************************/	@Override	public void addCategory(Category c) {
   		cDao.add(c);	}		@Override	public Category findCategory(String id) {
   		return cDao.find(id);	}		@Override	public List
   
     getAllCategory() {
   		return cDao.getAll();	}		/********************************************	 * 图书相关的服务	 ********************************************/	@Override	public void addBook(Book book) {
   		bDao.add(book);	}		@Override	public Book findBook(String id) {
   		return bDao.find(id);	}		@Override	public PageBean bookPageQuery(QueryInfo info) {
   		QueryResult result = bDao.pageQuery(info.getStartindex(), info.getPagesize(), info.getWhere(), info.getQueryvalue());		PageBean bean = new PageBean();		bean.setCurrentpage(info.getCurrentpage());		bean.setList(result.getList());		bean.setPagesize(info.getPagesize());		bean.setTotalrecord(result.getTotalrecord());		return bean;	}		public List
    
      getAllBook() {
   		return bDao.getAll();	}	/********************************************	 * 用户相关的服务	 ********************************************/	@Override	public void addUser(User user) {
   		uDao.add(user);	}		@Override	public User findUser(String username, String password) {
   		return uDao.find(username, password);	}		@Override	public User findUser(String id) {
   		return uDao.find(id);	}		/********************************************	 * 订单相关的服务	 ********************************************/	//使用用户的购物车来生成一个订单，然后存入数据库中	@Override	public void saveOrder(Cart cart, User user) {
   		Order order = new Order();		order.setId(UUID.randomUUID().toString());		order.setOrdertime(new Date());		order.setPrice(cart.getPrice());		order.setStatus(false);		order.setUser(user);				//定义一个集合，用于保存所有订单项		Set
     
       oitems = new HashSet
      
       ();				//用购物车中的购物项生成订单项		Set
       
        
         > set = cart.getMap().entrySet(); for (Map.Entry
         
           entry : set) { //得到每一个购物项 CartItem citem = entry.getValue(); OrderItem oitem = new OrderItem(); //用购物车中的购物项生成订单项 oitem.setBook(citem.getBook()); oitem.setId(UUID.randomUUID().toString()); oitem.setPrice(citem.getPrice()); oitem.setQuantity(citem.getQuantity()); oitems.add(oitem); } order.setOrderitems(oitems); oDao.add(order); } @Override public Order findOrder(String id) { return oDao.find(id); } @Override public List
          
            getOrderByStatus(boolean status) { return oDao.getAll(status); } //更新订单状态 public void updatOrder(String id, boolean status) { oDao.update(id, status); } /******************************************** * 数据库相关的服务 ********************************************/ public void addDbBack(DbBack back) { dDao.add(back); } public List
           
             getAllDbBack() { return dDao.getAll(); } public DbBack findDbBack(String id) { return dDao.find(id); }}
           
          
         
        
       
      
     
    
   

开发web层

备份数据库

首先，找到后台左侧导航页面——left.jsp，在数据库备份的超链接上绑定显示数据库备份页面。

<%@ page language="java" contentType="text/html; charset=UTF-8"    pageEncoding="UTF-8"%>
   	
   
    		请描述备份的原因：
    
		
    		
    	
   

填写完数据库备份的原因后，要将请求提交给DbServlet。那么备份的SQL脚本文件存放在哪儿呢？我们可在WebRoot根目录下新建一个backup目录，它就用于保存备份的SQL脚本文件。

package cn.liayun.web.manager;import java.io.IOException;import java.util.Date;import java.util.UUID;import javax.servlet.ServletException;import javax.servlet.annotation.WebServlet;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import cn.liayun.domain.DbBack;import cn.liayun.service.BusinessService;import cn.liayun.service.impl.BusinessServiceImpl;@WebServlet("/manager/DbServlet")public class DbServlet extends HttpServlet {
   		private BusinessService service = new BusinessServiceImpl();		protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
   		String method = request.getParameter("method");		if ("backup".equals(method)) {
   			backup(request, response);		}	}	//备份当前数据库	private void backup(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
   		try {
   			String backpath = this.getServletContext().getRealPath("/backup");			String filename = System.currentTimeMillis() + ".sql";			//1.做备份操作			String command = "cmd /c mysqldump -uroot -pliayun mybookstore>" + backpath + "\\" + filename;//cmd /c意思是启动一个命令行窗口，然后执行一条Windows命令（这是一条Windows命令）			Runtime.getRuntime().exec(command);//Java中的Runtime类可以执行一个任意的Windows命令						//2.把备份信息封装到一个JavaBean中，并把JavaBean保存到数据库当中			DbBack back = new DbBack();			back.setBacktime(new Date());			back.setDescription(request.getParameter("description"));			back.setFilename(backpath + "\\" + filename);			back.setId(UUID.randomUUID().toString());						service.addDbBack(back);						request.setAttribute("message", "备份成功！！");		} catch (IOException e) {
   			e.printStackTrace();			request.setAttribute("message", "备份失败！！");		}				request.getRequestDispatcher("/message.jsp").forward(request, response);	}	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
   		doGet(request, response);	}}

温馨提示：cmd /c的意思是启动一个命令行窗口，然后执行一条Windows命令（这是一条Windows命令）。

恢复数据库

找到后台左侧导航页面——left.jsp，在数据库恢复的超链接上绑定一个查询所有备份信息的DbServlet。

package cn.liayun.web.manager;import java.io.IOException;import java.util.Date;import java.util.List;import java.util.UUID;import javax.servlet.ServletException;import javax.servlet.annotation.WebServlet;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import cn.liayun.domain.DbBack;import cn.liayun.service.BusinessService;import cn.liayun.service.impl.BusinessServiceImpl;@WebServlet("/manager/DbServlet")public class DbServlet extends HttpServlet {
   		private BusinessService service = new BusinessServiceImpl();		protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
   		String method = request.getParameter("method");		if ("backup".equals(method)) {
   			backup(request, response);		}		if ("list".equals(method)) {
   			list(request, response);		}	}	//列出所有的备份信息	private void list(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
   		List
   
     list = service.getAllDbBack();		request.setAttribute("list", list);		request.getRequestDispatcher("/manager/listdbback.jsp").forward(request, response);	}		//备份当前数据库	private void backup(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
   		try {
   			String backpath = this.getServletContext().getRealPath("/backup");			String filename = System.currentTimeMillis() + ".sql";			//1.做备份操作			String command = "cmd /c mysqldump -uroot -pliayun mybookstore>" + backpath + "\\" + filename;//cmd /c意思是启动一个命令行窗口，然后执行一条Windows命令（这是一条Windows命令）			Runtime.getRuntime().exec(command);//Java中的Runtime类可以执行一个任意的Windows命令						//2.把备份信息封装到一个JavaBean中，并把JavaBean保存到数据库当中			DbBack back = new DbBack();			back.setBacktime(new Date());			back.setDescription(request.getParameter("description"));			back.setFilename(backpath + "\\" + filename);			back.setId(UUID.randomUUID().toString());						service.addDbBack(back);						request.setAttribute("message", "备份成功！！");		} catch (IOException e) {
   			e.printStackTrace();			request.setAttribute("message", "备份失败！！");		}				request.getRequestDispatcher("/message.jsp").forward(request, response);	}	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
   		doGet(request, response);	}}
   

接着，在manager目录中创建出数据库备份信息展示列表页面（listdbback.jsp）。

<%@ page language="java" contentType="text/html; charset=UTF-8"    pageEncoding="UTF-8"%><%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
   	
   			
   
   
		    
     
			      
备份的文件名
			      
备份的时间
			      
备份的原因
			      
操作
		     
		     
				      
${back.filename }
				      
${back.backtime }
				      
${back.description }
				      
					恢复				
			     
			    
   

当点击恢复超链接时，可以恢复为之前某个时刻的mybookstore数据库。于是，在DbServlet中添加恢复指定数据库的方法。

package cn.liayun.web.manager;import java.io.IOException;import java.util.Date;import java.util.List;import java.util.UUID;import javax.servlet.ServletException;import javax.servlet.annotation.WebServlet;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import cn.liayun.domain.DbBack;import cn.liayun.service.BusinessService;import cn.liayun.service.impl.BusinessServiceImpl;@WebServlet("/manager/DbServlet")public class DbServlet extends HttpServlet {
   		private BusinessService service = new BusinessServiceImpl();		protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
   		String method = request.getParameter("method");		if ("backup".equals(method)) {
   			backup(request, response);		}		if ("list".equals(method)) {
   			list(request, response);		}		if ("restore".equals(method)) {
   			restore(request, response);		}	}	//恢复指定的数据库	private void restore(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
   		try {
   			String id = request.getParameter("id");			DbBack back = service.findDbBack(id);			String filename = back.getFilename();			String command = "cmd /c mysql -uroot -pliayun mybookstore<" + filename;			Runtime.getRuntime().exec(command);						request.setAttribute("message", "恢复成功！！");		} catch (IOException e) {
   			e.printStackTrace();			request.setAttribute("message", "恢复失败！！");		}				request.getRequestDispatcher("/message.jsp").forward(request, response);	}	//列出所有的备份信息	private void list(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
   		List
   
     list = service.getAllDbBack();		request.setAttribute("list", list);		request.getRequestDispatcher("/manager/listdbback.jsp").forward(request, response);	}		//备份当前数据库	private void backup(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
   		try {
   			String backpath = this.getServletContext().getRealPath("/backup");			String filename = System.currentTimeMillis() + ".sql";			//1.做备份操作			String command = "cmd /c mysqldump -uroot -pliayun mybookstore>" + backpath + "\\" + filename;//cmd /c意思是启动一个命令行窗口，然后执行一条Windows命令（这是一条Windows命令）			Runtime.getRuntime().exec(command);//Java中的Runtime类可以执行一个任意的Windows命令						//2.把备份信息封装到一个JavaBean中，并把JavaBean保存到数据库当中			DbBack back = new DbBack();			back.setBacktime(new Date());			back.setDescription(request.getParameter("description"));			back.setFilename(backpath + "\\" + filename);			back.setId(UUID.randomUUID().toString());						service.addDbBack(back);						request.setAttribute("message", "备份成功！！");		} catch (IOException e) {
   			e.printStackTrace();			request.setAttribute("message", "备份失败！！");		}				request.getRequestDispatcher("/message.jsp").forward(request, response);	}	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
   		doGet(request, response);	}}
   

转载地址：https://liayun.blog.csdn.net/article/details/91422606 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！