1. 本章任务
通过JDBC操作数据库流程基本都是一样的：
打开数据库连接
执行查询或者更新操作
释放连接
由于每次操作都需要打开、关闭连接，所以封装一个操作类。
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. 总结
当前的数据访问类只是封装了基础的增删改查操作，如果需要的话还可以继续定制其他方法。