基于ssh框架的教务管理系统

package com.su02.teacheapp.impls; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.HashSet; import java.util.Iterator; import java.util.List; import java.util.Map; import java.util.Set; import com.su02.teacheapp.beans.Course; import com.su02.teacheapp.services.CourseService; import com.su02.teacheapp.utils.ChineseSimpleTime; import com.su02.teacheapp.utils.DbUtil; import com.su02.teacheapp.utils.PrimaryKeyGenerator; public class CourseServiceImpl extends BaseImpl implements CourseService { /** * 创建一笔资料保存到数据库 */ public int create(Course course) { int state = 0; if (course != null) { String sql = "INSERT INTO T_DEPARTMENT(id,cname,details,teacher,createdtime,remark)VALUES(?,?,?,?,?,?)"; Connection conn = this.getConn(); PreparedStatement preparedStatement=null; try { preparedStatement = conn.prepareStatement(sql); preparedStatement.setString(1, PrimaryKeyGenerator.getPk()); preparedStatement.setString(2, course.getCname()); preparedStatement.setString(3, course.getDetails()); preparedStatement.setString(4, course.getTeacher()); preparedStatement.setString(5, ChineseSimpleTime.getChTime()); preparedStatement.setString(6, course.getRemarks()); state = preparedStatement.executeUpdate(); conn.commit(); } catch (SQLException e) { try { conn.rollback(); } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } e.printStackTrace(); } finally { try { if (conn != null) { conn.close(); } if (preparedStatement != null) { preparedStatement.close(); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } return state; } /** * */ public int delete(String id) { int state = 0; String sql = "DELETE FROM T_COURSE WHERE id=?"; String batch_sql="DELETE FROM T_STU_COURSE WHERE CID=?"; Connection conn = this.getConn(); PreparedStatement preparedStatement=null; PreparedStatement pstmt=null; try { preparedStatement = conn.prepareStatement(sql); pstmt=conn.prepareStatement(batch_sql); pstmt.setString(1, id); preparedStatement.setString(1, id); pstmt.executeUpdate(); state=preparedStatement.executeUpdate(); conn.commit(); } catch (SQLException e) { e.printStackTrace(); try { conn.rollback(); } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } } finally { try { if (preparedStatement != null) { preparedStatement.close(); } if (conn != null) { conn.close(); } } catch (SQLException se) { se.printStackTrace(); } } return state; } /** * */ public List<Course> findByKeywords(Map<String, Object> keywords, int startrow, int endrow) { Set<String> strs_set = new HashSet<String>(); DbUtil dbUtil =this.getDbUtil(); Connection conn = this.getConn(); PreparedStatement preparedStatement=null; ResultSet rs=null; List<Course> courses = new ArrayList<Course>(); Course course = null; String sql = "SELECT id,cname,details,teacher,createdtime,remark FROM T_COURSE WHERE 1=1"; String row_sql="SELECT count(*) FROM T_COURSE WHERE 1=1"; //构造查询条件 if(keywords!=null){ strs_set = keywords.keySet(); Iterator keys = strs_set.iterator(); while (keys.hasNext()) { String key = (String) keys.next(); Object _o = keywords.get(key); sql += " AND " + key + " = '" + _o + "'"; row_sql += " AND " + key + " = '" + _o + "'"; } } int maxRowCount=dbUtil.getRowCount(row_sql); if(endrow>maxRowCount){ endrow=maxRowCount; } if(endrow==0){ endrow=10; } if(startrow<=0){ startrow=1; } try { conn = dbUtil.openConnection(); preparedStatement = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY); rs = preparedStatement.executeQuery(); rs.absolute(startrow); for(;startrow<=endrow;rs.next(),startrow++){ course = new Course(); course.setId(rs.getString("id")); course.setCname(rs.getString("cname")); course.setDetails(rs.getString("details")); course.setRemarks(rs.getString("remark")); course.setTeacher(rs.getString("teacher")); courses.add(course); } } catch (SQLException e) { e.printStackTrace(); } finally { try { if(rs!=null){ rs.close(); } if (preparedStatement != null) { preparedStatement.close(); } if (conn != null) { conn.close(); } } catch (SQLException se) { se.printStackTrace(); } } return courses; } /** * */ public Course findByPirmaryKey(String id) { String sql = "SELECT id,cname,details,teacher,createdtime,remark FROM T_COURSE WHERE id=?"; Connection conn = this.getConn(); PreparedStatement preparedStatement=null; ResultSet rs=null; Course course=null; try { preparedStatement = conn.prepareStatement(sql); preparedStatement.setString(1, id); rs = preparedStatement.executeQuery(); if (rs.next()) { course = new Course(); course.setId(rs.getString("id")); course.setCname(rs.getString("cname")); course.setDetails(rs.getString("details")); course.setTeacher(rs.getString("teacher")); course.setRemarks(rs.getString("remark")); } } catch (SQLException e) { e.printStackTrace(); } finally { try { if (preparedStatement != null) { preparedStatement.close(); } if (conn != null) { conn.close(); } } catch (SQLException se) { se.printStackTrace(); } } return course; } /** * 根据学生的id来查询此学生所选的全部课程 */ public List<Course> findByStudentId(String studentId,int startrow, int endrow) { String sql = "SELECT cid,cname,details,teacher FROM VI_COURSE_STUDETN WHERE sid='"+studentId+"'"; String row_sql="SELECT COUNT(*) WHERE sid='"+studentId+"'"; List<Course> courses = new ArrayList<Course>(); DbUtil dbUtil=this.getDbUtil(); Connection conn = this.getConn(); PreparedStatement preparedStatement=null; ResultSet rs=null; int maxRowCount=dbUtil.getRowCount(row_sql); if(endrow>maxRowCount){ endrow=maxRowCount; } if(startrow<=0){ startrow=1; } try { preparedStatement = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY); //preparedStatement.setString(1, studentId); rs = preparedStatement.executeQuery(); Course course = null; while (rs.next()) { courses.add(course); } } catch (SQLException e) { e.printStackTrace(); } finally { try { if (preparedStatement != null) { preparedStatement.close(); } if (conn != null) { conn.close(); } } catch (SQLException se) { se.printStackTrace(); } } return courses; } /** * */ public int update(Course course) { int state = 0; if (course != null) { String cname = course.getCname(); String details = course.getDetails(); String teacher = course.getTeacher(); String remark = course.getRemarks(); String id = course.getId(); String sql = "UPDATE T_COURSE SET cname=?,details=?,teacher=?,remark=? WHERE id=?"; Connection conn = this.getConn(); PreparedStatement preparedStatement=null; try { preparedStatement = conn.prepareStatement(sql); preparedStatement.setString(1, cname); preparedStatement.setString(2, details); preparedStatement.setString(3, teacher); preparedStatement.setString(4, remark); preparedStatement.setString(5, id); state = prepare