JSP+Servlet培训班作业管理系统[9]–数据库操作类开发

1. 本章任务

通过JDBC操作数据库流程基本都是一样的:

  1. 打开数据库连接
  2. 执行查询或者更新操作
  3. 释放连接

由于每次操作都需要打开、关闭连接,所以封装一个操作类。

2. 数据库操作类封装

将加载驱动、打开、关闭连接封装到一个类:


/**
 * 数据库操作工具类
 */
public class DbUtils {

	// 连接所需的固定参数
	private static String driver = "com.mysql.jdbc.Driver";
	private static String url = "jdbc:mysql://127.0.0.1:3306/homework?useUnicode=true&characterEncoding=utf-8";
	private static String user = "root";
	private static String password = "Easy@0122";

	// 初始化的时候加载去的弄
	static {
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			throw new ExceptionInInitializerError(e);
		}
	}

	/**
	 * 获取连接
	 */
	public static Connection getConnection() throws SQLException {
		return DriverManager.getConnection(url, user, password);
	}

	/**
	 * 释放连接
	 */
	public static void releasonConnection(ResultSet rs, Statement st, Connection conn) {
		try {
			if (rs != null)
				rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (st != null)
					st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				if (conn != null)
					try {
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}`在这里插入代码片`
			}
		}
	}
}

3. 数据访问类开发

有类数据库操作类后,就可以针对各个数据实体开发相应的操作类了,按照国际管理,数据访问类命名为XxxDao。

3.1 用户访问类 UserDao


/**
 * 用户数据访问类
 */
public class UserDao {
	/**
	 * 新增
	 */
	public int add(User user) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = DbUtils.getConnection();
			// 拼装sql,?为预留占位符
			String sql = "insert into user(user_role,user_name,user_password)values(?,?,?)";
			ps = conn.prepareStatement(sql);
			// 将对象属性插入sql预留位置
			ps.setString(1, user.getUserRole());
			ps.setString(2, user.getUserName());
			ps.setString(3, user.getUserPassword());
			// 执行sql
			return ps.executeUpdate();
		} catch (SQLException e) {
			return 0;
		} finally {
			DbUtils.releaseConnection(null, ps, conn);
		}
	}

	/**
	 * 根据id删除
	 */
	public int deleteById(int userId) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = DbUtils.getConnection();
			String sql = "delete from user where user_id=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, userId);
			return ps.executeUpdate();
		} catch (SQLException e) {
			return 0;
		} finally {
			DbUtils.releaseConnection(null, ps, conn);
		}
	}

	/**
	 * 获取全部用户
	 */
	public List<User> getUsers() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<User> users = new ArrayList<User>();
		try {
			conn = DbUtils.getConnection();
			String sql = "select * from user";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				users.add(makeOneUser(rs));
			}
		} catch (SQLException e) {
		} finally {
			DbUtils.releaseConnection(rs, ps, conn);
		}
		return users;
	}

	/**
	 * 获取一个用户
	 */
	public User makeOneUser(ResultSet rs) throws SQLException {
		User user = new User();
		user.setUserId(rs.getInt("user_id"));
		user.setUserName(rs.getString("user_name"));
		user.setUserPassword(rs.getString("user_password"));
		user.setUserRole(rs.getString("user_role"));
		return user;
	}

	/**
	 * 根据id修改其他信息
	 */
	public int update(User user) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = DbUtils.getConnection();
			String sql = "update user set user_role=?,user_name=?, user_password=? where user_id=? ";
			ps = conn.prepareStatement(sql);
			ps.setString(1, user.getUserRole());
			ps.setString(2, user.getUserName());
			ps.setString(3, user.getUserPassword());
			ps.setInt(4, user.getUserId());
			return ps.executeUpdate();
		} catch (SQLException e) {
			return 0;
		} finally {
			DbUtils.releaseConnection(rs, ps, conn);
		}
	}
}

3.2 课程访问类 CourseDao


/**
 * 课程访问类
 */
public class CourseDao {
	/**
	 * 新增
	 */
	public int add(Course course) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = DbUtils.getConnection();
			// 拼装sql,?为预留占位符
			String sql = "insert into course(course_user,course_name)values(?,?)";
			ps = conn.prepareStatement(sql);
			// 将对象属性插入sql预留位置
			ps.setInt(1, course.getCourseUser());
			ps.setString(2, course.getCourseName());
			// 执行sql
			return ps.executeUpdate();
		} catch (SQLException e) {
			return 0;
		} finally {
			DbUtils.releaseConnection(null, ps, conn);
		}
	}

	/**
	 * 根据id删除
	 */
	public int deleteById(int courseId) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = DbUtils.getConnection();
			String sql = "delete from course where course_id=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, courseId);
			return ps.executeUpdate();
		} catch (SQLException e) {
			return 0;
		} finally {
			DbUtils.releaseConnection(null, ps, conn);
		}
	}

	/**
	 * 获取全部
	 */
	public List<Course> getCourses() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Course> courses = new ArrayList<Course>();
		try {
			conn = DbUtils.getConnection();
			String sql = "select * from course";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				courses.add(makeOneCourse(rs));
			}
		} catch (SQLException e) {
		} finally {
			DbUtils.releaseConnection(rs, ps, conn);
		}
		return courses;
	}

	/**
	 * 获取一个
	 */
	public Course makeOneCourse(ResultSet rs) throws SQLException {
		Course course = new Course();
		course.setCourseId(rs.getInt("course_id"));
		course.setCourseName(rs.getString("course_name"));
		course.setCourseUser(rs.getInt("course_user"));
		return course;
	}

	/**
	 * 根据id修改其他信息
	 */
	public int update(Course course) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = DbUtils.getConnection();
			String sql = "update course set course_user=?,course_name=? where course_id=? ";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, course.getCourseUser());
			ps.setString(2, course.getCourseName());
			ps.setInt(3, course.getCourseId());
			return ps.executeUpdate();
		} catch (SQLException e) {
			return 0;
		} finally {
			DbUtils.releaseConnection(rs, ps, conn);
		}
	}
}

3. 选课访问类 SelectionDao


/**
 * 选课访问类
 */
public class SelectionDao {
	/**
	 * 新增
	 */
	public int add(Selection selection) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = DbUtils.getConnection();
			String sql = "insert into selection(selection_user,selection_course)values(?,?)";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, selection.getSelectionUser());
			ps.setInt(2, selection.getSelectionCourse());
			return ps.executeUpdate();
		} catch (SQLException e) {
			return 0;
		} finally {
			DbUtils.releaseConnection(null, ps, conn);
		}
	}

	/**
	 * 根据id删除
	 */
	public int deleteById(int selectionId) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = DbUtils.getConnection();
			String sql = "delete from selection where selection_id=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, selectionId);
			return ps.executeUpdate();
		} catch (SQLException e) {
			return 0;
		} finally {
			DbUtils.releaseConnection(null, ps, conn);
		}
	}

	/**
	 * 获取全部
	 */
	public List<Selection> getSelections() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Selection> selections = new ArrayList<Selection>();
		try {
			conn = DbUtils.getConnection();
			String sql = "select * from selection";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				selections.add(makeOneSelection(rs));
			}
		} catch (SQLException e) {
		} finally {
			DbUtils.releaseConnection(rs, ps, conn);
		}
		return selections;
	}

	/**
	 * 获取一个
	 */
	public Selection makeOneSelection(ResultSet rs) throws SQLException {
		Selection selection = new Selection();
		selection.setSelectionId(rs.getInt("selection_id"));
		selection.setSelectionUser(rs.getInt("selection_user"));
		selection.setSelectionCourse(rs.getInt("selection_course"));
		return selection;
	}

	/**
	 * 根据id修改其他信息
	 */
	public int update(Selection selection) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = DbUtils.getConnection();
			String sql = "update selection set selection_user=?,selection_course=? where selection_id=? ";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, selection.getSelectionUser());
			ps.setInt(2, selection.getSelectionCourse());
			ps.setInt(3, selection.getSelectionId());
			return ps.executeUpdate();
		} catch (SQLException e) {
			return 0;
		} finally {
			DbUtils.releaseConnection(rs, ps, conn);
		}
	}
}

4. 作业题目访问类 TitleDao


/**
 * 作业题目访问类
 */
public class TitleDao {
	/**
	 * 新增
	 */
	public int add(Title title) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = DbUtils.getConnection();
			String sql = "insert into title(title_content,title_course,title_time)values(?,?,?)";
			ps = conn.prepareStatement(sql);
			ps.setString(1, title.getTitleContent());
			ps.setInt(2, title.getTitleCourse());
			ps.setString(1, title.getTitleTime());
			return ps.executeUpdate();
		} catch (SQLException e) {
			return 0;
		} finally {
			DbUtils.releaseConnection(null, ps, conn);
		}
	}

	/**
	 * 根据id删除
	 */
	public int deleteById(int titleId) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = DbUtils.getConnection();
			String sql = "delete from title where title_id=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, titleId);
			return ps.executeUpdate();
		} catch (SQLException e) {
			return 0;
		} finally {
			DbUtils.releaseConnection(null, ps, conn);
		}
	}

	/**
	 * 获取全部
	 */
	public List<Title> getTitles() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Title> titles = new ArrayList<Title>();
		try {
			conn = DbUtils.getConnection();
			String sql = "select * from title";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				titles.add(makeOneTitle(rs));
			}
		} catch (SQLException e) {
		} finally {
			DbUtils.releaseConnection(rs, ps, conn);
		}
		return titles;
	}

	/**
	 * 获取一个
	 */
	public Title makeOneTitle(ResultSet rs) throws SQLException {
		Title title = new Title();
		title.setTitleId(rs.getInt("title_id"));
		title.setTitleContent(rs.getString("title_content"));
		title.setTitleCourse(rs.getInt("title_course"));
		title.setTitleTime(rs.getString("title_time"));
		return title;
	}

	/**
	 * 根据id修改其他信息
	 */
	public int update(Title title) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = DbUtils.getConnection();
			String sql = "update title set title_content=?,title_course=?,title_time=? where title_id=? ";
			ps = conn.prepareStatement(sql);
			ps.setString(1, title.getTitleContent());
			ps.setInt(2, title.getTitleCourse());
			ps.setString(3, title.getTitleTime());
			ps.setInt(4, title.getTitleId());
			return ps.executeUpdate();
		} catch (SQLException e) {
			return 0;
		} finally {
			DbUtils.releaseConnection(rs, ps, conn);
		}
	}
}

5. 作业内容访问类 JobDao


/**
 * 作业内容访问类
 */
public class JobDao {
	/**
	 * 新增
	 */
	public int add(Job job) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = DbUtils.getConnection();
			String sql = "insert into job(job_title,job_user,job_time,job_content,job-score)values(?,?,?,?,?)";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, job.getJobTitle());
			ps.setInt(2, job.getJobUser());
			ps.setString(3, job.getJobTime());
			ps.setString(4, job.getJobContent());
			ps.setString(5, job.getJobScore());
			return ps.executeUpdate();
		} catch (SQLException e) {
			return 0;
		} finally {
			DbUtils.releaseConnection(null, ps, conn);
		}
	}

	/**
	 * 根据id删除
	 */
	public int deleteById(int jobId) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = DbUtils.getConnection();
			String sql = "delete from job where job_id=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, jobId);
			return ps.executeUpdate();
		} catch (SQLException e) {
			return 0;
		} finally {
			DbUtils.releaseConnection(null, ps, conn);
		}
	}

	/**
	 * 获取全部
	 */
	public List<Job> getJobs() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Job> jobs = new ArrayList<Job>();
		try {
			conn = DbUtils.getConnection();
			String sql = "select * from job";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				jobs.add(makeOneJob(rs));
			}
		} catch (SQLException e) {
		} finally {
			DbUtils.releaseConnection(rs, ps, conn);
		}
		return jobs;
	}

	/**
	 * 获取一个
	 */
	public Job makeOneJob(ResultSet rs) throws SQLException {
		Job job = new Job();
		job.setJobId(rs.getInt("job_id"));
		job.setJobTitle(rs.getInt("job_title"));
		job.setJobUser(rs.getInt("job_user"));
		job.setJobTime(rs.getString("job_time"));
		job.setJobContent(rs.getString("job_content"));
		job.setJobScore(rs.getString("job_score"));
		return job;
	}

	/**
	 * 根据id修改其他信息
	 */
	public int update(Job job) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = DbUtils.getConnection();
			String sql = "update job set job_title=?,job_user=?,job_time=?,job_content=?,job_score=? where job_id=? ";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, job.getJobTitle());
			ps.setInt(2, job.getJobUser());
			ps.setString(3, job.getJobTime());
			ps.setString(4, job.getJobContent());
			ps.setString(5, job.getJobScore());
			ps.setInt(6, job.getJobId());
			return ps.executeUpdate();
		} catch (SQLException e) {
			return 0;
		} finally {
			DbUtils.releaseConnection(rs, ps, conn);
		}
	}
}

4. 总结

当前的数据访问类只是封装了基础的增删改查操作,如果需要的话还可以继续定制其他方法。


如有问题请扫码联系我
在这里插入图片描述

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 代码科技 设计师:Amelia_0503 返回首页