JDBC 逻辑整理初步之通用增删改查

JDBC 逻辑整理初步之通用增删改查

 

 DBUtil

public class DBUtil {
    private String driver="com.mysql.cj.jdbc.Driver";
    private String url="jdbc:mysql://localhost:3306/student";
    private String username="root";
    private String password="15870796485hxf";

    public static PreparedStatement pstmt = null;
    public static Connection con = null;
    public static ResultSet rs = null;

    public DBUtil(String driver, String url, String username, String password) {
        this.driver = driver;
        this.url = url;
        this.username = username;
        this.password = password;
    }
    public DBUtil(){

    }

    public Connection getConnection() throws ClassNotFoundException, SQLException {
        Class.forName(driver);
        return DriverManager.getConnection(url,username,password);
    }

    /**
     *  Object[] obs = { name,age ,...,x} ;
     *               String sql = "delete from xxx where Name = ? or id = ?  " ;
     *               pstmt.setInt(1,sno );
     *             setXxx()方法的个数 依赖于 ?的个数， 而?的个数 又和 数组params的个数一致
     *             setXxx()方法的个数 ->数组params的个数一致
     */
    public PreparedStatement createPreParedStatement(String sql,Object[] params) throws SQLException, ClassNotFoundException {
        System.out.println(url+"+"+password);
        pstmt = getConnection().prepareStatement(sql);
        if (params!=null){
            for (int i = 0; i < params.length; i++){
                pstmt.setObject(i+1,params[i]);
            }
        }
        return pstmt;
    }
    /**例如：
     Object[] obj = new Object[]{stu.getId(),stu.getName(),stu.getPwd()};
     boolean a = util.executeUpdate("insert into student values(?,?,?)",obj);
     */
    //通用的查  :通用 表示  适合与 任何查询
    public ResultSet executeQuery( String sql ,Object[] params) {
//        Student student = null;
//        List<String > strings = new ArrayList<>();
        //String sql = "select * from student" ;//select enmae ,job from xxxx where...id>3
        try {
            pstmt = createPreParedStatement(sql,params);
            rs =  pstmt.executeQuery() ;
            return rs ;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            return null;
        }finally {
            closeAll(rs,pstmt,con);
        }
    }

    public Student queryCollect(String sql ,Object[] params){
        Student student = null;

        try {
            pstmt = createPreParedStatement(sql,params);
            rs =  pstmt.executeQuery() ;

            if (rs !=null && rs.next()){
                System.out.println(student+"-------------");
                int a = rs.getInt("id");
                String b = rs.getString("name");
                String c = rs.getString("pwd");
                System.out.println(a+","+b+","+c);
                //Student  set不了值进去？？？？？？？？？？？？？？？
//                student.setId(rs.getInt("id"));
//                student.setName(rs.getString("name"));
//                student.setPwd(rs.getString("pwd"));
            }

            return student;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            return null;
        } finally {
            closeAll(rs,pstmt,con);
        }

    }


    //通用的增删改
    public boolean executeUpdate(String sql,Object[] params){
        try {
            System.out.println(params);
            pstmt = createPreParedStatement(sql,params);
            int count = pstmt.executeUpdate();
            if (count > 0)
                return true;
            else
                return false;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            return false;
        }finally {
            closeAll(rs,pstmt,con);
        }
    }



    public static void closeAll(ResultSet rs,PreparedStatement pstmt,Connection con)
    {
        try {
            if(rs!=null)rs.close();
            if(pstmt!=null)pstmt.close();
            if(con!=null)con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

 

StudentDao

public class StudentDao {
    DBUtil util = new DBUtil();

    public List<Student> queryStudentAll(){
        Object[] obj = new Object[]{};
        Student student = null;
        List<Student> students = null;
        try {
            ResultSet rs = util.executeQuery("select * from student",obj);
            if (rs.next()){
                student.setId(rs.getInt("id"));
                student.setName(rs.getString("name"));
                student.setPwd(rs.getString("pwd"));
                students.add(student);
            }
            return students;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public Student queryStudentId(Student stu){
        Student student = null;
        Object[] obj = new Object[]{stu.getId()};
        System.out.println(stu.getId());
         return util.queryCollect("select * from student where id = ?",obj);


    }

    public Student queryStudentNameAndPwd(Student stu){
        Student student = null;
        Object[] obj = new Object[]{stu.getName(),stu.getPwd()};

        try {
            ResultSet rs = util.executeQuery("select * from student where name = ? and pwd = ?",obj);
            System.out.println(rs);
            if (rs.next()){
                student.setId(rs.getInt("id"));
                student.setName(rs.getString("name"));
                student.setPwd(rs.getString("pwd"));
            }
            return student;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

 

StudentService-IStudentService

public interface IstudentService {
    boolean register(Student student);
    boolean updateStudent(Student stu);
    public List<Student> findAll();
//    Student queryuser(Student student);
    Student queryId(Student student) throws SQLException;
}

@Service
public class studentService implements IstudentService{
    @Autowired
    private StudentDao studentDao;
    public void setStudentDao(StudentDao studentDao) {
    }

    /**
     * 用户登录业务
     */
//    public Student login(Student student){
//        return queryuser(student);
//    }

    /**
     * 查询用户
     */
    public Student queryuser(Student student){
//        if (student.getName()==null || "".equals(student.getName())){
//            return null;
//        }
//        if (student.getPwd()==null || "".equals(student.getPwd())){
//            return null;
//        }

        //进行验证
        Student stu = studentDao.queryStudentNameAndPwd(student);
        //没有查询到id为null
//        if (stu == null){
//            return null;
//        }
        //查询到该用户
//        return stu;


            return stu;

    }

    /**
     * 修改密码
     */
    public Student ChangPassword(Student student,String newPw){
        //1.通过原来账号密码查找用户
        student = queryuser(student);
        if (student == null){
            System.out.println("账号或密码错误");
        }
        //2.修改原密码
        boolean a = studentDao.updateStudentPwd(student,newPw);
        if (a==false){
            return null;
        }
        //3.修改student对象数据
        student.setPwd(newPw);
        return student;
    }
    /**
     * 根据id查询
     */
    public Student queryId(Student student) {
        return studentDao.queryStudentId(student);
    }
    /**
     * 用户注册add
     */
    public boolean register(Student student){

        return studentDao.addStudent(student);

    }

    @Override
    public boolean updateStudent(Student stu) {
        return studentDao.updateStudent(stu);
    }

//    @Override
//    public List<Student> findAll() {
//        return null;
//    }

    /**
     * 修改账号
     */
    public int update(Student student){
        boolean a = studentDao.updateStudent(student);
        int i = 0;
        if (a == true) i++;
        return i;
    }

/**
 * 查询alluser
 */
    public List<Student> findAll(){
        List<Student> students = studentDao.queryStudentAll();
        return students;
    }


}

 

Student

public class Student {
    private int Id;
    private String Name;
    private String Pwd;
    public Student() {
    }
    public Student(int id, String name, String pwd) {
        Id = id;
        Name = name;
        Pwd = pwd;
    }

    public int getId() {
        return Id;
    }

    public void setId(int id) {
        Id = id;
    }

    public String getName() {
        return Name;
    }

    public void setName(String name) {
        Name = name;
    }

    public String getPwd() {
        return Pwd;
    }

    public void setPwd(String pwd) {
        Pwd = pwd;
    }

    @Override
    public String toString() {
        return "Student{" +
                "Id=" + Id +
                ", Name='" + Name + '\'' +
                ", Pwd='" + Pwd + '\'' +
                '}';
    }
}