第二次数据库作业--dao

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;
    }
}