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