DAO

DAO：Data Access Object 数据访问接口，是面向对象的数据接口

对数据库的访问操作一般会有：增加数据、删除数据、修改数据、查询数据等等

我们建立ORM（Object Relation Mapping），在数据库和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 StudentDaoImplimplements IStudentDao {

 

public static void main(String[] args) throws Exception {

IStudentDao dao = new StudentDaoImpl();

//for (int i = 0; i < 100; i++) {

//Studentstu = new Student();

//stu.setName("张三" + "-" + i);

//stu.setCode("zhangsan" + "-" + i);

//dao.addStudent(stu);

//}

//Studentstu = new Student(114,"李四","lisi","","");

//System.out.println(dao.updateStudent(stu));

//Studentstu = 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;

}

 

}