您的位置: 首页  >  文章  >  man-study-03 用maven搭建一个数据库实验项目-后台实例

man-study-03 用maven搭建一个数据库实验项目-后台实例

分类: 文章 • 2023-03-11 13:05:55

创建一个maven项目，这里只有后台部分，前台页面部分，需要实践时再补充。
可以从servlet处入手，添加urlPattern后，然后和页面对接起来用。
项目结构如下：
man-study-03 用maven搭建一个数据库实验项目-后台实例
测试数据库如下：

/*
Navicat MySQL Data Transfer

Source Server         : MySQL57
Source Server Version : 50718
Source Host           : localhost:3306
Source Database       : mvn_db

Target Server Type    : MYSQL
Target Server Version : 50718
File Encoding         : 65001

Date: 2019-03-07 13:52:54
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for mvn_user
-- ----------------------------
DROP TABLE IF EXISTS `mvn_user`;
CREATE TABLE `mvn_user` (
  `ur_id` int(11) NOT NULL AUTO_INCREMENT,
  `ur_user_name` varchar(255) DEFAULT NULL,
  `ur_password` varchar(255) DEFAULT NULL,
  `ur_age` int(11) DEFAULT NULL,
  `ur_status` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ur_id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of mvn_user
-- ----------------------------
INSERT INTO `mvn_user` VALUES ('15', 'zhangsan', '123', '11', 'Active');
INSERT INTO `mvn_user` VALUES ('16', 'lisi', '123', '13', 'Inactive');
INSERT INTO `mvn_user` VALUES ('17', 'wangwu', '123', '13', 'Active');
INSERT INTO `mvn_user` VALUES ('18', 'fhzheng', '123456', '38', 'Active');
INSERT INTO `mvn_user` VALUES ('19', 'jtzheng', '12345679', '35', 'Active');
INSERT INTO `mvn_user` VALUES ('20', 'sjzheng', '12345679', '35', null);
INSERT INTO `mvn_user` VALUES ('21', 'ssjzheng', '12345679', '35', null);
INSERT INTO `mvn_user` VALUES ('22', 'zzjzheng', '12345679', '35', null);
INSERT INTO `mvn_user` VALUES ('23', 'bbjzheng', '12345679', '25', 'Inactive');

man-study-03 用maven搭建一个数据库实验项目-后台实例
约定数据库访问：rootcuit:rootcuit的用户名和密码，数据库名为：mvn_db
则db.properties文件内容如下

userName=rootcuit
password=rootcuit
url=jdbc:mysql://localhost:3306/mvn_db
driverName=com.mysql.jdbc.Driver

自底自向做开发：
实体层：
MvnUser.java

package com.fhzheng.demo.tp04.entity;

public class MvnUser {
	private int urId;
	private String urUserName;
	private String urPassword;
	private int urAge;
	private String urStatus;

	public int getUrId() {
		return urId;
	}

	public void setUrId(int urId) {
		this.urId = urId;
	}

	public String getUrUserName() {
		return urUserName;
	}

	public void setUrUserName(String urUserName) {
		this.urUserName = urUserName;
	}

	public String getUrPassword() {
		return urPassword;
	}

	public void setUrPassword(String urPassword) {
		this.urPassword = urPassword;
	}

	public int getUrAge() {
		return urAge;
	}

	public void setUrAge(int urAge) {
		this.urAge = urAge;
	}

	public String getUrStatus() {
		return urStatus;
	}

	public void setUrStatus(String urStatus) {
		this.urStatus = urStatus;
	}

}

库连接工具类 DBConnection.java

package com.fhzheng.demo.tp04.db;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

/**
 * 获取连接的一个工具类，继承Properties，实现如下封装：<br/>
 * 1 读取db.properties文件<br/>
 * 2 获取Connection连接的方法<br/>
 * 3 关闭资源的方法
 * 
 * @author fhzheng
 * @version 1.0
 * */
public class DBConnection extends Properties {
	private static DBConnection DB_CONN = null;
    /**
     * 构造器方法，被私有化，封装读取db.properties逻辑
     * 
     * */
	private DBConnection() throws Exception {
		InputStream in = DBConnection.class.getClassLoader()
				.getResourceAsStream("db.properties");
		this.load(in);
		// 加载驱动类
		Class.forName(this.getProperty("driverName"));
	}

	/**
	 * 单例模式实现，获取DBConnection实例的静态方法
	 * 
	 * @return DBConnection DBConnection实例
	 * @throws Exception 初始化db.properties出现问题时，会抛异常
	 * */
	public static DBConnection getInstance() throws Exception {
		if (DB_CONN == null) {
			DB_CONN = new DBConnection();
		}
		return DB_CONN;
	}

	/**
	 * 基于驱动和db.properties中配置的连接数据库的信息，创建一个新连接返回
	 * 
	 * @return Connection 创建的新连接对象
	 * 
	 * */
	public Connection getConnection() {
		Connection conn = null;
		String url = this.getProperty("url");
		String userName = this.getProperty("userName");
		String password = this.getProperty("password");
		//
		try {
			conn = DriverManager.getConnection(url, userName, password);
		} catch (Exception e) {
			throw new RuntimeException("数据库连接错误，请与管理员联系");
		}
		return conn;
	}

	/**
	 * 关闭操作数据库后的资源
	 * 
	 * @param conn Connection对象
	 * @param stmt Statement或Statement的子类对象
	 * @param rs ResultSet对象
	 * */
	public void close(Connection conn, Statement stmt, ResultSet rs) {
		try {
			if (rs != null)
				rs.close();
			if (stmt != null)
				stmt.close();
			if (conn != null)
				conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}

数据访问层DAO MvnUserDAO .java

package com.fhzheng.demo.tp04.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.fhzheng.demo.tp04.db.DBConnection;
import com.fhzheng.demo.tp04.entity.MvnUser;
/**
 * MvnUser实体对象的持久层代码，封装了对MvnUser实体对象的CRUD方法
 * 
 * @author fhzheng
 * @version 1.0
 * */
public class MvnUserDAO {
	/**
	 * 在数据库中，添加一个新的MvnUser对象
	 * 
	 * @param user 需要添加的用户实体对象，该对象需要有用户名、密码、年龄和状态属性
	 * 
	 * @return void
	 * @throws RuntimeException 添加失败或出现其它意外
	 * */
	public void addUser(MvnUser user) {
		DBConnection dbConn = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			// 获取DBConnection实例
			dbConn = DBConnection.getInstance();
			String sql = "insert into mvn_user (ur_user_name,ur_password,ur_age,ur_status) values(?,?,?,?)";
			// 获取连接对象
			conn = dbConn.getConnection();
			// 基于连接和sql，获取一个预处理Statement对象
			pstmt = conn.prepareStatement(sql);
			// 设置sql中占位符的值
			pstmt.setString(1, user.getUrUserName());
			pstmt.setString(2, user.getUrPassword());
			pstmt.setInt(3, user.getUrAge());
			pstmt.setString(4, user.getUrStatus());
            // 执行预处理
			pstmt.executeUpdate();
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			// 关闭资源
			if (dbConn != null)
				dbConn.close(conn, pstmt, null);
		}
	}

	/**
	 * 更新MvnUser对象。该对象中需要设置年龄、状态和id属性，属性和状态是要更新的新值，id为条件
	 * 
	 * @param user 需要更新的MvnUser对象
	 * 
	 * @return void
	 * @throws RuntimeException 更新失败或出现其它意外
	 * */
	public void update(MvnUser user) {
		DBConnection dbConn = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			dbConn = DBConnection.getInstance();
			String sql = "update mvn_user set ur_age=?,ur_status=? where ur_id=?";
			conn = dbConn.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, user.getUrAge());
			pstmt.setString(2, user.getUrStatus());
			pstmt.setInt(3, user.getUrId());

			pstmt.executeUpdate();
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			if (dbConn != null)
				dbConn.close(conn, pstmt, null);
		}
	}
    /**
     * 删除MvnUser对象，该对象中需要有要删除对象的id属性，id属性为删除条件
     * 
     * @param user 要删除的MvnUser对象
     * 
     * @return void
     * @throws RuntimeException 删除失败或出现其它意外
     * */
	public void deleteUser(MvnUser user) {
		DBConnection dbConn = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			dbConn = DBConnection.getInstance();
			String sql = "delete from mvn_user where ur_id=?";
			conn = dbConn.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, user.getUrId());

			pstmt.executeUpdate();
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			if (dbConn != null)
				dbConn.close(conn, pstmt, null);
		}
	}

	/**
	 * 根据id查询对应的MvnUser对象
	 * 
	 * @param id 要查询的MvnUser对象的id
	 * @return MvnUser id对应的MvnUser对象，如果没有对象，返回null
	 * @throws RuntimeException 出现意外情况
	 * */
	public MvnUser findUserById(int id) {
		MvnUser user = null;
		DBConnection dbConn = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			dbConn = DBConnection.getInstance();
			String sql = "select * from mvn_user where ur_id=?";
			conn = dbConn.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);

			rs = pstmt.executeQuery();
			if (rs.next()) {
				user = new MvnUser();
				user.setUrAge(rs.getInt("ur_age"));
				user.setUrId(rs.getInt("ur_id"));
				user.setUrPassword(rs.getString("ur_password"));
				user.setUrStatus(rs.getString("ur_status"));
				user.setUrUserName(rs.getString("ur_user_name"));
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			if (dbConn != null)
				dbConn.close(conn, pstmt, rs);
		}
		return user;
	}
	/**
	 * 根据用户名查询对应的MvnUser对象
	 * 
	 * @param userName 要查询的MvnUser对象的用户名
	 * @return MvnUser 用户对应的MvnUser对象，如果没有对象，返回null
	 * @throws RuntimeException 出现意外情况
	 * */
	public MvnUser findUserByUserName(String userName) {
		MvnUser user = null;
		DBConnection dbConn = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			dbConn = DBConnection.getInstance();
			String sql = "select * from mvn_user where ur_user_name=?";
			conn = dbConn.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, userName);

			rs = pstmt.executeQuery();
			if (rs.next()) {
				user = new MvnUser();
				user.setUrAge(rs.getInt("ur_age"));
				user.setUrId(rs.getInt("ur_id"));
				user.setUrPassword(rs.getString("ur_password"));
				user.setUrStatus(rs.getString("ur_status"));
				user.setUrUserName(rs.getString("ur_user_name"));
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			if (dbConn != null)
				dbConn.close(conn, pstmt, rs);
		}
		return user;
	}

	/**
	 * 查找数据库中所有的用户对象，以List集合的形式返回
	 * 
	 * @return List<MvnUser> 所有用户对象的集合
	 * @throws RuntimeException 出现意外情况
	 * */
	public List<MvnUser> findUsers() {
		List<MvnUser> userList = null;
		DBConnection dbConn = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			dbConn = DBConnection.getInstance();
			String sql = "select * from mvn_user order by ur_id";
			conn = dbConn.getConnection();
			pstmt = conn.prepareStatement(sql);

			rs = pstmt.executeQuery();
			if (rs != null) {
				userList = new ArrayList<MvnUser>();
				MvnUser user = null;
				while (rs.next()) {
					user = new MvnUser();
					user.setUrAge(rs.getInt("ur_age"));
					user.setUrId(rs.getInt("ur_id"));
					user.setUrPassword(rs.getString("ur_password"));
					user.setUrStatus(rs.getString("ur_status"));
					user.setUrUserName(rs.getString("ur_user_name"));

					userList.add(user);
				}
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			if (dbConn != null)
				dbConn.close(conn, pstmt, rs);
		}
		return userList;
	}
}

业务逻辑层 UserService.java

package com.fhzheng.demo.tp04.service;
import java.util.List;

import com.fhzheng.demo.tp04.dao.MvnUserDAO;
import com.fhzheng.demo.tp04.entity.MvnUser;
/**
 * 对用CRUD操作的服务层，封装了CRUD在持久化前的必要业务逻辑
 * 
 * @author fhzheng
 * @version 1.0
 * */
public class UserService {
	private MvnUserDAO userDAO = new MvnUserDAO();

	/**
	 * 创建新的用户
	 * @param user 要创建的用户对象
	 * @return void 
	 * @throws RuntimeException 当用户信息不全或用户名已经存在的时候，都会抛出异常
	 * */
	public void createUser(MvnUser user) {
		// 验证基本的用户信息
		if (user == null || user.getUrUserName() == null || user.getUrPassword() == null) {
			throw new RuntimeException("用户信息不合法");
		}
		// 根据用户名查询用户对象
		MvnUser u = userDAO.findUserByUserName(user.getUrUserName());
		// 如果能查询到用户对象，说明用户已经存在，抛异常
		if (u != null) {
			throw new RuntimeException(user.getUrUserName() + " 用户已存在");
		}
		// 调用dao代码，添加一个新用户
		userDAO.addUser(user);
	}

	/**
	 * 更新id对应用户的年龄和状态信息
	 * 
	 * @param age 要更新用户的新年龄
	 * @param status 要更新用户的新状态
	 * @param id 要更新用户的id，这是更新的条件
	 * 
	 * @return void
	 * */
	public void editUser(int age, String status, int id) {
		MvnUser user = this.userDAO.findUserById(id);
		user.setUrAge(age);
		user.setUrStatus(status);
		this.userDAO.update(user);
	}

	public void deleteUser(int id) {
		MvnUser user = this.userDAO.findUserById(id);
		this.userDAO.deleteUser(user);
	}

	public MvnUser searchUser(int id) {
		MvnUser user = this.userDAO.findUserById(id);
		return user;
	}
	public MvnUser searchUser(String userName) {
		MvnUser user = this.userDAO.findUserByUserName(userName);
		return user;
	}

	public List<MvnUser> searchUsers() {
		List<MvnUser> userList = this.userDAO.findUsers();
		return userList;
	}
}

最后就是控制层，即servlet
完成做CRUD的库操作
AddUserServlet.java
DeleteUserServlet.java
EditUserServlet.java
SearchUserServlet.java
SearchUsersServlet.java

以下是具体的参考代码

package com.fhzheng.demo.tp04.servlet;
import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.fhzheng.demo.tp04.entity.MvnUser;
import com.fhzheng.demo.tp04.service.UserService;
import net.sf.json.JSONObject;

/**
 * Servlet implementation class SearchUserServlet
 */
public class SearchUserServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;

	protected void service(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		// 获取查询的方式（根据id查询还是根据用户名查询）
		String type = request.getParameter("type");
		UserService userService = new UserService();
		MvnUser user = null;
		// 根据id查询
		if ("byId".equals(type)) {
			// 获取id
			String idStr = request.getParameter("id");
			int id = 0;
			try {
				id = Integer.parseInt(idStr);
			} catch (Exception e) {
			}
			user = userService.searchUser(id);
		} else {
			// 根据用户名查询
			String userName = request.getParameter("userName");
			user = userService.searchUser(userName);
		}
		// 设置返回的响应为json响应
		response.setContentType("text/json;charset=UTF-8");
		PrintWriter out = response.getWriter();
		// 将查询的用户对象，转变成json格式的字符串，写入响应返回
		out.print(JSONObject.fromObject(user));
		out.flush();
		out.close();
	}

}



package com.fhzheng.demo.tp04.servlet;
import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.fhzheng.demo.tp04.entity.MvnUser;
import com.fhzheng.demo.tp04.service.UserService;

/**
 * Servlet implementation class AddUserServlet
 */
public class AddUserServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;

	/**
	 * @see HttpServlet#service(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void service(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		request.setCharacterEncoding("utf-8");
		// 获取要添加用户的信息
		String userName = request.getParameter("userName");
		String password = request.getParameter("password");
		String ageStr = request.getParameter("age");
		String status = request.getParameter("status");
		int age = 0;
		try {
			// 将年龄字符串，转变成数字（数据库中需要数字类型）
			age = Integer.parseInt(ageStr);
		} catch (Exception e) {
		}
		// 封装成MvnUser对象
		MvnUser user = new MvnUser();
		user.setUrAge(age);
		user.setUrPassword(password);
		user.setUrStatus(status);
		user.setUrUserName(userName);

		UserService userService = new UserService();
		String msg = "添加成功";
		try {
			// 调用service，创建用户
			userService.createUser(user);
		} catch (Exception e) {
			e.printStackTrace();
			msg = "添加失败：" + e.getMessage();
		}
		// 返回添加后的结果提示信息
		response.setContentType("text/html;charset=UTF-8");
		PrintWriter out = response.getWriter();
		out.print(msg);
	}

}



package com.fhzheng.demo.tp04.servlet;
import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.fhzheng.demo.tp04.service.UserService;

/**
 * Servlet implementation class DeleteUserServlet
 */
public class DeleteUserServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;

	/**
	 * @see HttpServlet#service(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void service(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		String idStr = request.getParameter("id");
		int id = 0;
		try {
			id = Integer.parseInt(idStr);
		} catch (Exception e) {
		}
		UserService userService = new UserService();
		String msg = "删除成功";
		try {
			userService.deleteUser(id);
		} catch (Exception e) {
			e.printStackTrace();
			msg = "删除失败:" + e.getMessage();
		}
		response.setContentType("text/html;charset=UTF-8");
		PrintWriter out = response.getWriter();
		out.print(msg);
		out.flush();
		out.close();
	}

}



package com.fhzheng.demo.tp04.servlet;
import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.fhzheng.demo.tp04.service.UserService;

/**
 * Servlet implementation class EditUserServlet
 */
public class EditUserServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
   
	protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		String idStr = request.getParameter("id");
		String status = request.getParameter("status");
		String ageStr = request.getParameter("age");
		
		int id=0,age=0;
		//分开进行try catch转换，以方便定位出错信息
		try{
			id = Integer.parseInt(idStr);
		}catch(Exception e){}
		try{
			age = Integer.parseInt(ageStr);
		}catch(Exception e){}
		//调用完成业务
		UserService userService = new UserService();
		String msg = "修改成功";
		try{
			userService.editUser(age, status, id);
		}catch(Exception e){
			e.printStackTrace();
			msg = "修改失败:"+e.getMessage();
		}
		//输出
		response.setContentType("text/html;charset=UTF-8");
		PrintWriter out = response.getWriter();
		out.print(msg);
		out.flush();
		out.close();
	}

}

相关推荐