2020.12.8收获

dao->CourseDao.java

package dao;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import entity.Course;
import util.DBUtil;

/**
 * 课程Dao
 * Dao层操作数据
 * @author Hu
 *
 */
public class CourseDao {

    /**
     * 添加
     * @param course
     * @return
     */
    public boolean add(Course course) {
        String sql = "insert into curd(name, sex, minzu,time,age,mianmao,fuwu) values('" + course.getName() + "','" + course.getSex() + "','" + course.getMinzu() +"','" + course.getTime() + "','" + course.getAge()  + "','" + course.getMianmao() + "','" + course.getFuwu() + "')";
        Connection conn = DBUtil.getConn();
        Statement state = null;
        boolean f = false;
        int a = 0;
        try {
            state = conn.createStatement();
            a=state.executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(state, conn);
        }        
        if (a > 0) {
            f = true;
        }
        return f;
    }
    /**
     * 删除
     * 
     * @param id
     * @return
     */
    public boolean delete (int id) {
        boolean f = false;
        String sql = "delete from curd where id='" + id + "'";
        Connection conn = DBUtil.getConn();
        Statement state = null;
        int a = 0;
        
        try {
            state = conn.createStatement();
            a = state.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(state, conn);
        }    
        if (a > 0) {
            f = true;
        }
        return f;
    }

    /**
     * 修改
     * @param name
     * @param pass
     */
    public boolean update(Course course) {
        String sql = "update curd set name='" + course.getName() + "', sex='" + course.getSex() + "', minzu='" + course.getMinzu()+ "', time='" + course.getTime()+ "', age='" + course.getAge()+ "', mianmao='" + course.getMianmao()+ "', fuwu='" + course.getFuwu()
            + "' where id='" + course.getId() + "'";
        Connection conn = DBUtil.getConn();
        Statement state = null;
        boolean f = false;
        int a = 0;
        try {
            state = conn.createStatement();
            a = state.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(state, conn);
        }    
        if (a > 0) {
            f = true;
        }
        return f;
    }
    
    /**
     * 验证名称是否唯一
     * true --- 不唯一
     * @param name
     * @return
     */
    public boolean name(String name) {
        boolean flag = false;
        String sql = "select name from curd where name = '" + name + "'";
        Connection conn = DBUtil.getConn();
        Statement state = null;
        ResultSet rs = null;    
        try {
            state = conn.createStatement();
            rs = state.executeQuery(sql);
            while (rs.next()) {
                flag = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, state, conn);
        }
        return flag;
    }
    
    /**
     * 通过ID得到类
     * @param id
     * @return
     */
    public Course getCourseById(int id) {
        String sql = "select * from curd where id ='" + id + "'";
        Connection conn = DBUtil.getConn();
        Statement state = null;
        ResultSet rs = null;
        Course course = null;    
        try {
            state = conn.createStatement();
            rs = state.executeQuery(sql);
            while (rs.next()) {
                String name = rs.getString("name");
                String sex = rs.getString("sex");
                String minzu = rs.getString("minzu");
                String time = rs.getString("time");
                String age = rs.getString("age");
                String mianmao = rs.getString("mianmao");
                String fuwu = rs.getString("fuwu");            
                course = new Course(id, name, sex, minzu, time, age, mianmao, fuwu);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, state, conn);
        }        
        return course;
    }
    
    /**
     * 通过name得到Course
     * @param name
     * @return
     */
    public Course getCourseByName(String name) {
        String sql = "select * from curd where name ='" + name + "'";
        Connection conn = DBUtil.getConn();
        Statement state = null;
        ResultSet rs = null;
        Course course = null;
        
        try {
            state = conn.createStatement();
            rs = state.executeQuery(sql);
            while (rs.next()) {
                int id = rs.getInt("id");
                String sex = rs.getString("sex");
                String minzu = rs.getString("minzu");
                String time = rs.getString("time");
                String age = rs.getString("age");
                String mianmao = rs.getString("mianmao");
                String fuwu = rs.getString("fuwu");
            course = new Course(id, name, sex, minzu, time, age, mianmao, fuwu);
            
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, state, conn);
        }        
        return course;
    }
    
    /**
     * 查找
     * @param name
     * @param teacher
     * @param minzu
     * @return
     */
    public List<Course> search(String name, String sex, String minzu,String time,String age,String mianmao,String fuwu) {
        String sql = "select * from curd where ";
        if (name != "") {
            sql += "name like '%" + name + "%'";
        }
        if (sex != "") {
            sql += "sex like '%" + sex + "%'";
        }
        if (minzu != "") {
            sql += "minzu like '%" + minzu + "%'";
        }
        if (time!= "") {
            sql += "time like '%" + time + "%'";
        }
        if (age!= "") {
            sql += "age like '%" + age + "%'";
        }
        if (mianmao!= "") {
            sql += "mianmao like '%" + mianmao + "%'";
        }
        if (fuwu!= "") {
            sql += "fuwu like '%" + fuwu + "%'";
        }
        List<Course> list = new ArrayList<>();
        Connection conn = DBUtil.getConn();
        Statement state = null;
        ResultSet rs = null;
        try {
            state = conn.createStatement();
            rs = state.executeQuery(sql);
            Course bean = null;
            while (rs.next()) {
                int id = rs.getInt("id");
                String name2 = rs.getString("name");
                String sex2 = rs.getString("sex");
                String minzu2 = rs.getString("minzu");
                String time2 = rs.getString("time");
                String age2 = rs.getString("age");
                String mianmao2 = rs.getString("mianmao");
                String fuwu2 = rs.getString("fuwu");
                bean = new Course(id, name2, sex2,minzu2, time2, age2, mianmao2, fuwu2);
                list.add(bean);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, state, conn);
        }    
        return list;
    }
    
    /**
     * 全部数据
     * @param name
     * @param teacher
     * @param minzu
     * @return
     */
    public List<Course> list() {
        String sql = "select * from curd";
        List<Course> list = new ArrayList<>();
        Connection conn = DBUtil.getConn();
        Statement state = null;
        ResultSet rs = null;
        try {
            state = conn.createStatement();
            rs = state.executeQuery(sql);
            Course bean = null;
            while (rs.next()) {
                int id = rs.getInt("id");
                String name2 = rs.getString("name");
                String sex2 = rs.getString("sex");
                String minzu2 = rs.getString("minzu");
                String time2 = rs.getString("time");
                String age2 = rs.getString("age");
                String mianmao2 = rs.getString("mianmao");
                String fuwu2 = rs.getString("fuwu");
                bean = new Course(id, name2, sex2, minzu2, time2, age2, mianmao2, fuwu2);
                list.add(bean);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, state, conn);
        }    
        return list;
    }
}