package dao; import model.C2S; import utils.Dbtuil; import java.sql.*; import java.util.ArrayList; import java.util.List; /** * Created by yinus * Date:2016/4/6 * Time:18:44 */ public class C2SDao { public void add(C2S cs) throws SQLException { Connection connection = Dbtuil.getConnection(); String sql = "INSERT INTO db_c2s (stu_id, cou_id, c2s_credit, c2s_create_time, c2s_update_time)" + " VALUES (?,?,?,?,?)"; PreparedStatement ps = connection.prepareStatement(sql); ps.setInt(1,cs.getStu_id()); ps.setInt(2,cs.getCou_id()); ps.setDouble(3,cs.getCredit()); ps.setDate(4,new Date(cs.getCreate_time().getTime())); ps.setDate(5,new Date(cs.getUpdate_time().getTime())); ps.execute(); ps.close(); } public void update(C2S cs) throws SQLException { Connection connection = Dbtuil.getConnection(); String sql = "UPDATE db_c2s" + " SET c2s_credit=? , c2s_update_time=GETDATE()" + " WHERE stu_id= ? AND cou_id= ?"; PreparedStatement ps = connection.prepareStatement(sql); ps.setDouble(1,cs.getCredit()); ps.setInt(2,cs.getStu_id()); ps.setInt(3,cs.getCou_id()); ps.execute(); ps.close(); } public List<C2S> queryById(int id,boolean isStudentId) throws SQLException { List<C2S> css = new ArrayList<>(); C2S c = null; String sql=null; Connection connection = Dbtuil.getConnection(); if (isStudentId) { sql = "SELECT * FROM db_c2s" + " WHERE stu_id = ?"; } else { sql = "SELECT * FROM db_c2s" + " WHERE cou_id = ?"; } PreparedStatement ps = connection.prepareStatement(sql); ps.setInt(1,id); ResultSet rs = ps.executeQuery(); while (rs.next()) { c = new C2S(rs.getInt("stu_id"),rs.getInt("cou_id"),rs.getDouble("c2s_credit")); c.setCreate_time(rs.getDate("c2s_create_time")); c.setUpdate_time(rs.getDate("c2s_update_time")); css.add(c); } rs.close(); ps.close(); return css; } public C2S queryByStudentIdAndCourseId(int stu_id,int cou_id) throws SQLException { Connection connection = Dbtuil.getConnection(); String sql = "SELECT * FROM db_c2s WHERE stu_id=? AND cou_id=?"; PreparedStatement ps = connection.prepareStatement(sql); ps.setInt(1,stu_id); ps.setInt(2,cou_id); ResultSet rs = ps.executeQuery(); C2S cs = null; while (rs.next()){ cs = new C2S(); cs.setStu_id(rs.getInt("stu_id")); cs.setCou_id(rs.getInt("cou_id")); cs.setCredit(rs.getDouble("c2s_credit")); cs.setCreate_time(rs.getDate("c2s_create_time")); cs.setUpdate_time(rs.getDate("c2s_update_time")); } return cs; } public void delete(int id,boolean isStudentId) throws SQLException { Connection connection = Dbtuil.getConnection(); String sql=null; if (isStudentId) { sql = "DELETE FROM db_c2s WHERE stu_id= ?"; } else { sql = "DELETE FROM db_c2s WHERE cou_id =?"; } PreparedStatement ps = connection.prepareStatement(sql); ps.setInt(1,id); ps.execute(); ps.close(); } public void delete(int sId,int cId) throws SQLException { Connection connection = Dbtuil.getConnection(); String sql="DELETE FROM db_c2s WHERE stu_id=? AND cou_id=?"; PreparedStatement ps = connection.prepareStatement(sql); ps.setInt(1,sId); ps.setInt(2,cId); ps.execute(); ps.close(); } }
package dao; import utils.Dbtuil; import model.Course; import java.sql.*; import java.util.ArrayList; import java.util.List; /** * Created by yinus * Date:2016/4/6 * Time:18:44 */ public class CourseDao { public void addCourse(Course course) throws SQLException { Connection connection= Dbtuil.getConnection(); String sql = "INSERT INTO db_courses" + " (cou_id, cou_title, cou_create_time, cou_update_time)" + " VALUES (?,?,?,?)"; PreparedStatement ps = connection.prepareStatement(sql); ps.setInt(1,course.getId()); ps.setString(2,course.getTitle()); ps.setDate(3,new Date(course.getCreateTime().getTime())); ps.setDate(4,new Date(course.getUpdateTime().getTime())); ps.execute(); ps.close(); } public void delCourse(int id) throws SQLException { Connection connection=Dbtuil.getConnection(); String sql = " DELETE FROM db_courses" + " WHERE cou_id=?" ; PreparedStatement ps = connection.prepareStatement(sql); ps.setInt(1,id); ps.execute(); ps.close(); } public void updateCourse(Course course) throws SQLException { Connection connection= Dbtuil.getConnection(); String sql = "UPDATE db_courses" + " SET cou_title=?, cou_update_time = GETDATE()" + " WHERE cou_id=?"; PreparedStatement ps = connection.prepareStatement(sql); ps.setString(1,course.getTitle()); ps.setInt(2,course.getId()); ps.execute(); ps.close(); } public List<Course> queryCourses(String title) throws SQLException { Course course =null; List<Course> courses = new ArrayList<>(); Connection connection = Dbtuil.getConnection(); String sql = " SELECT * FROM db_courses" + " WHERE courses.cou_title LIKE ?" ; PreparedStatement ps = connection.prepareStatement(sql); ps.setString(1,"%"+title+"%"); ResultSet resultSet=ps.executeQuery(); while (resultSet.next()){ course = new Course(); course.setId(resultSet.getInt("cou_id")); course.setTitle(resultSet.getString("cou_title")); course.setCreateTime(resultSet.getDate("cou_create_time")); course.setUpdateTime(resultSet.getDate("cou_update_time")); courses.add(course); } resultSet.close(); ps.close(); return courses; } public Course querySingleCourse(int id) throws SQLException { Course course = null; Connection connection=Dbtuil.getConnection(); String sql = " SELECT * FROM db_courses" + " WHERE cou_id = ?" ; PreparedStatement ps = connection.prepareStatement(sql); ps.setInt(1,id); ResultSet rs=ps.executeQuery(); while (rs.next()) { course=new Course(); course.setId(rs.getInt("cou_id")); course.setTitle(rs.getString("cou_title")); course.setCreateTime(rs.getDate("cou_create_time")); course.setUpdateTime(rs.getDate("cou_update_time")); } rs.close(); ps.close(); return course; } }
package dao; import utils.Dbtuil; import model.Student; import java.sql.*; import java.util.ArrayList; import java.util.List; /** * Created by yinus * Date:2016/4/6 * Time:18:44 */ public class StudentDao { public void addStudent(Student student) throws SQLException { Connection connection=Dbtuil.getConnection(); String sql = "insert into db_students (stu_id,stu_name,stu_create_time,stu_update_time) values (?,?,?,?)"; PreparedStatement ps = connection.prepareStatement(sql); ps.setInt(1,student.getId()); ps.setString(2,student.getName()); ps.setDate(3,new Date(student.getCreateTime().getTime())); ps.setDate(4,new Date(student.getUpdateTime().getTime())); ps.execute(); ps.close(); } public void delStudent(int id) throws SQLException { Connection connection=Dbtuil.getConnection(); String sql = " DELETE FROM db_students WHERE stu_id=?" ; PreparedStatement ps = connection.prepareStatement(sql); ps.setInt(1,id); ps.execute(); ps.close(); } public void updateStudent(Student student) throws SQLException { Connection connection=Dbtuil.getConnection(); String sql = " UPDATE db_students SET stu_name=?,stu_create_time=?,stu_update_time=GETDATE() WHERE stu_id=?"; PreparedStatement ps = connection.prepareStatement(sql); ps.setString(1,student.getName()); ps.setDate(2,new Date(student.getCreateTime().getTime())); ps.setInt(3,student.getId()); ps.execute(); ps.close(); } public List<Student> queryStudents(String name) throws SQLException { Student student=null; List<Student> students = new ArrayList<>(); Connection connection=Dbtuil.getConnection(); String sql = " SELECT * FROM db_students WHERE stu_name LIKE ?" ; PreparedStatement ps = connection.prepareStatement(sql); ps.setString(1,"%"+name+"%"); ResultSet resultSet=ps.executeQuery(); while(resultSet.next()){ student = new Student(); student.setId(resultSet.getInt("stu_id")); student.setName(resultSet.getString("stu_name")); student.setCreateTime(resultSet.getDate("stu_create_time")); student.setUpdateTime(resultSet.getDate("stu_update_time")); students.add(student); } resultSet.close(); ps.close(); return students; } public Student querySingleStudent(int id) throws SQLException { Student student=null; Connection connection=Dbtuil.getConnection(); String sql = " SELECT * FROM db_students WHERE stu_id=?" ; PreparedStatement ps = connection.prepareStatement(sql); ps.setInt(1,id); ResultSet resultSet=ps.executeQuery(); while (resultSet.next()){ student=new Student(); student.setId(resultSet.getInt("stu_id")); student.setName(resultSet.getString("stu_name")); student.setCreateTime(resultSet.getDate("stu_create_time")); student.setUpdateTime(resultSet.getDate("stu_update_time")); } resultSet.close(); ps.close(); return student; } }