先做一个BaseDAO做一个基本实现:
package cn.zsp.dao;
import cn.zsp.uil.JDBCUtils;
import org.junit.Test;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public abstract class BaseDAO {
public <E> E getValue(Connection conn,String sql,Object ...args) {
PreparedStatement ps =null;
ResultSet rs=null;
try {
ps = conn.prepareStatement(sql);
for (int i = 0; i <args.length ; i++) {
ps.setObject(i+1,args[i]);
}
rs = ps.executeQuery();
if (rs.next())
{
return (E) rs.getObject(1);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.closeResource(null,ps,rs);
}
return null;
}
/***********************考虑事务的查询操作************************/
//考虑事务的查询操作 version 2.0 返回1个对象
public <T> T getInstance(Connection conn,Class<T> clazz,String sql,
Object ...args) {
//获取预编译对象
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement(sql);
for (int i = 0; i <args.length ; i++) {
ps.setObject(i+1,args[i]);
}
//获得结果集
rs = ps.executeQuery();
//获取结果集元数据
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
while(rs.next())
{
//空参构造器
T t = clazz.newInstance();
for (int i = 0; i <columnCount ; i++) {
String columnLabel = rsmd.getColumnLabel(i + 1);
//从结果集里面取数据
Object columnValue = rs.getObject(i + 1);
//反射存数据
//要数据的应该是一个类,我们只是取到这个类,然后给使用这个类变量的对象赋值
Field field =clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t,columnValue);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
}
finally {
JDBCUtils.closeResource(null,ps,rs);
}
return null;
}
/***********************考虑事务的查询操作************************/
//考虑事务的查询操作 version 2.0 返回多个对象
public <T> List<T> getForList(Connection conn, Class<T> clazz,
String sql,
Object ...args) {
PreparedStatement ps =null;
ResultSet rs=null;
try {
conn = JDBCUtils.getConnection();
ps = conn.prepareStatement(sql);
//获取
for (int i = 0; i <args.length ; i++) {
ps.setObject(i+1,args[i]);
}
//获得结果集
rs = ps.executeQuery();
//获取结果集元数据
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
ArrayList<T> arrayList = new ArrayList<T>();
while(rs.next())
{
//空参构造器
T t = clazz.newInstance();
for (int i = 0; i <columnCount ; i++) {
String columnLabel = rsmd.getColumnLabel(i + 1);
//从结果集里面取数据
Object columnValue = rs.getObject(i + 1);
//反射存数据
//要数据的应该是一个类,我们只是取到这个类,然后给使用这个类变量的对象赋值
Field field =clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t,columnValue);
}
arrayList.add(t);
}
return arrayList;
} catch (Exception e) {
e.printStackTrace();
}
finally {
JDBCUtils.closeResource(null,ps,rs);
}
//获得预编译的SQL语句的对象
return null;
}
/***********************考虑事务的增删改操作************************/
//考虑事务的增删改操作 version 2.0
public int update(Connection conn,String sql,Object...args) {
//这里用Object是因为我不知道到底是什么数据类型会传过来
PreparedStatement ps=null;
try {
conn= JDBCUtils.getConnection();
ps = conn.prepareStatement(sql);
for (int i = 0; i <args.length ; i++) {
ps.setObject(i+1,args[i]);
}
return ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}
JDBCUtils.closeResource(null,ps);
return 0;
}
}
然后做一个实现类的DAO接口:
package cn.zsp.dao;
import cn.zsp.bean.Students;
import java.sql.Connection;
import java.util.List;
//此接口用于规范针对于students表的常用操作
public interface StudentsDao {
/**
* 添加学生
* @param conn
* @param students
*/
void insert(Connection conn, Students students);
/**
* 根据id删除学生
* @param conn
* @param uid
*/
void deleteById(Connection conn, int uid);
/**
* 根据传过来的类对表进行更新
* @param conn
* @param students
*/
void update(Connection conn, Students students);
/**
* 根据id查询学生
* @param conn
* @param id
* @return
*/
Students getStudentsById(Connection conn, int id);
/**
* 传个连接得到表里面的数据
* @return
*/
List<Students> getAll(Connection conn);
Long getCount(Connection conn);
}
最后实现接口继承DAO接口:
package cn.zsp.dao;
import cn.zsp.bean.Students;
import java.sql.Connection;
import java.util.List;
public class StudentsDAOImpl extends BaseDAO implements StudentsDao {
@Override
public void insert(Connection conn, Students students) {
String sql ="insert into students(uid,uname,uhobby)values(?,?,?)";
update(conn,sql,students.getUid(),students.getUname(),students.getUhobby());
}
@Override
public void deleteById(Connection conn, int uid) {
String sql ="delete from students where uid =?";
update(conn,sql,uid);
}
@Override
public void update(Connection conn, Students students) {
String sql ="update students set uname=?,uhobby=? where " +
"uid=?";
update(conn,sql,students.getUname(),students.getUhobby(),
students.getUid());
}
@Override
public Students getStudentsById(Connection conn, int uid) {
String sql ="select uid,uname,uhobby from students where uid =?";
Students student = getInstance(conn, Students.class, sql, uid);
return student;
}
@Override
public List<Students> getAll(Connection conn) {
String sql="select * from students";
List<Students> list = getForList(conn, Students.class, sql);
return list;
}
@Override
public Long getCount(Connection conn) {
String sql ="select count(*) from students";
return getValue(conn, sql);
}
}
底下是测试案例:
package cn.zsp.dao.junit;
import cn.zsp.bean.Students;
import cn.zsp.dao.StudentsDAOImpl;
import cn.zsp.uil.JDBCUtils;
import org.junit.Test;
import java.sql.Connection;
import java.util.List;
import static org.junit.Assert.*;
public class StudentsDAOImplTest {
private StudentsDAOImpl dao =new StudentsDAOImpl();
@Test
public void insert() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
} catch (Exception e) {
e.printStackTrace();
}
dao.insert(conn,new Students(5,"连宜辉","睡觉"));
JDBCUtils.closeResource(conn,null);
}
@Test
public void deleteById() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
} catch (Exception e) {
e.printStackTrace();
}
dao.deleteById(conn,5);
JDBCUtils.closeResource(conn,null);
}
@Test
public void update() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
} catch (Exception e) {
e.printStackTrace();
}
dao.update(conn,new Students(1,"朱少鹏","java"));
JDBCUtils.closeResource(conn,null);
}
@Test
public void getStudentsById() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
} catch (Exception e) {
e.printStackTrace();
}
Students student = dao.getStudentsById(conn, 1);
System.out.println(student);
JDBCUtils.closeResource(conn,null);
}
@Test
public void getAll() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
} catch (Exception e) {
e.printStackTrace();
}
List<Students> all = dao.getAll(conn);
for (Students students : all) {
System.out.println(students);
}
JDBCUtils.closeResource(conn,null);
}
@Test
public void getCount() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
} catch (Exception e) {
e.printStackTrace();
}
Long count = dao.getCount(conn);
System.out.println(count);
JDBCUtils.closeResource(conn,null);
}
}