第二次数据库作业--dao

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

 

第二次数据库作业--dao

标签：