DAO

DAO：Data AccessObject 数据访问接口，是面向对象的数据接口对数据库的访问操作一般会有：增加数据、删除数据、修改数据、查询数据等等。
我们建立ORM（Object RelationMapping），在数据库和DAO之间建立联系。

package com.ntqingniao.sm.dao.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.ntqingniao.sm.bean.Student;
import com.ntqingniao.sm.dao.IStudentDao;

public class StudentDaoImpl implements IStudentDao {

    public static void main(String[] args) throws Exception {
        IStudentDao dao = new StudentDaoImpl();
//      for (int i = 0; i < 100; i++) {
//          Student stu = new Student();
//          stu.setName("张三" + "-" + i);
//          stu.setCode("zhangsan" + "-" + i);
//          dao.addStudent(stu);
//      }

//      Student stu = new Student(114,"李四","lisi","","");
//      System.out.println(dao.updateStudent(stu));

//      Student stu = dao.findStudentById(118);
//      System.out.println(stu);

        List<Student> stus = dao.queryStudent("5", "zhangsan");
        for (Student stu : stus) {
            System.out.println(stu);
        }


    }

    @Override
    public Student addStudent(Student stu) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/nq_stu?characterEncoding=UTF-8",
                "root", "");
        PreparedStatement pst = conn.prepareStatement("insert into t_stu(name, code, idcard, email,state) values(?,?,?,?,?)",
                Statement.RETURN_GENERATED_KEYS);
        pst.setString(1, stu.getName());
        pst.setString(2, stu.getCode());
        pst.setString(3, stu.getIdcard());
        pst.setString(4, stu.getEmail());
        pst.setInt(5, stu.getState());

        pst.executeUpdate();

        int autoInckey = -1;
        ResultSet rs = pst.getGeneratedKeys(); // 获取结果
        if (rs.next()) {
            autoInckey = rs.getInt(1);// 取得ID
        } else {
        }
        stu.setId(autoInckey);

        pst.close();
        conn.close();
        return stu;
    }

    @Override
    public boolean updateStudent(Student stu) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/nq_stu?characterEncoding=UTF-8",
                "root", "");
        PreparedStatement pst = conn.prepareStatement("update t_stu set name=?,code=?,idcard=?,email=?,state=? where id=?");
        pst.setString(1, stu.getName());
        pst.setString(2, stu.getCode());
        pst.setString(3, stu.getIdcard());
        pst.setString(4, stu.getEmail());
        pst.setInt(5, stu.getState());
        pst.setInt(6, stu.getId());

        int i = pst.executeUpdate();
        pst.close();
        conn.close();
        return i == 1 ? true : false;
    }

    @Override
    public boolean delStudent(int id) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/nq_stu?characterEncoding=UTF-8",
                "root", "");
        PreparedStatement pst = conn.prepareStatement("delete from t_stu where id=?");
        pst.setInt(1, id);
        int i = pst.executeUpdate();
        pst.close();
        conn.close();
        return i == 1 ? true : false;
    }

    @Override
    public int batchDelStudents(int[] ids) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/nq_stu?characterEncoding=UTF-8",
                "root", "");

        String sids = "";
        for (int i = 0; i < ids.length; i++) {
            if (i == ids.length - 1) {
                sids += ids[i];
            } else {
                sids += ids[i] + ",";
            }

        }

        PreparedStatement pst = conn.prepareStatement("delete from t_stu where id in ("+sids+")");
        int i = pst.executeUpdate();
        pst.close();
        conn.close();
        return i;
    }

    @Override
    public Student findStudentById(int id) throws Exception {
        Student stu = null;
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/nq_stu?characterEncoding=UTF-8",
                "root", "");
        PreparedStatement pst = conn.prepareStatement("select id,name,code,idcard,email,state from t_stu where id=?");
        pst.setInt(1, id);
        ResultSet rs = pst.executeQuery();
        if(rs.next()) {
            String name = rs.getString("name");
            String code = rs.getString("code");
            String idcard = rs.getString("idcard");
            String email = rs.getString("email");
            Integer state = rs.getInt("state");
            stu = new Student(id, name, code, idcard, email,state);
        }
        return stu;
    }

    @Override
    public List<Student> queryStudent(String name, String code) throws Exception {
        List<Student> stus = new ArrayList<Student>();
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/nq_stu?characterEncoding=UTF-8",
                "root", "");
        PreparedStatement pst = conn.prepareStatement("select id,name,code,idcard,email,state from t_stu where name like ? and code like ?");
        pst.setString(1, "%"+name+"%");
        pst.setString(2, "%"+code+"%");
        ResultSet rs = pst.executeQuery();
        while(rs.next()) {
            Integer id = rs.getInt("id");
            String name1 = rs.getString("name");
            String code1 = rs.getString("code");
            String idcard = rs.getString("idcard");
            String email = rs.getString("email");
            Integer state = rs.getInt("state");
            Student stu = new Student(id, name1, code1, idcard, email,state);
            stus.add(stu);
        }
        return stus;
    }

}