首先是数据库的PERSON表的实体类
package com.jadyer.junit4.db;
public class Person {
private Integer id;
private String username;
private Integer age;
/* 三个属性的setter和getter略 */
public Person(){}
public Person(String username, Integer age){
this.username = username;
this.age = age;
}
}
然后是用于获取数据库连接的类
package com.jadyer.junit4.db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/**
* 获得数据库连接
*/
public class DBConn {
private static Connection conn;
private static String className = "oracle.jdbc.OracleDriver";
private static String url = "jdbc:oracle:thin:@127.0.0.1:1521:jadyer";
private static String username = "scott";
private static String password = "jadyer";
public static Connection getConnection(){
try {
Class.forName(className);
conn = DriverManager.getConnection(url, username, password);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
public static void close(){
if(null != conn){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
然后是对PERSON表的操作类
package com.jadyer.junit4.db;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class PersonDao {
public void insert(Person person){
Connection conn = null;
try{
conn = DBConn.getConnection();
String sql = "insert into person values(person_sequence.nextval, ?, ?)";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, person.getUsername());
ps.setInt(2, person.getAge());
ps.executeUpdate();
}catch(Exception ex){
ex.printStackTrace();
}finally{
DBConn.close();
}
}
public void update(Person person){
Connection conn = null;
try{
conn = DBConn.getConnection();
String sql = "update person set username=?, age=? where id=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, person.getUsername());
ps.setInt(2, person.getAge());
ps.setInt(3, person.getId());
ps.executeUpdate();
}catch(Exception ex){
ex.printStackTrace();
}finally{
DBConn.close();
}
}
public Person getPersonById(int id){
Connection conn = null;
Person person = null;
try{
conn = DBConn.getConnection();
String sql = "select * from person where id=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, id);
ResultSet rs = ps.executeQuery();
if(rs.next()){
person = new Person();
person.setId(rs.getInt("id"));
person.setUsername(rs.getString("username"));
person.setAge(rs.getInt("age"));
}
rs.close();
}catch(Exception ex){
ex.printStackTrace();
}finally{
DBConn.close();
}
return person;
}
public void removePersonById(int id){
Connection conn = null;
try{
conn = DBConn.getConnection();
String sql = "delete from person where id=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, id);
ps.executeUpdate();
}catch(Exception ex){
ex.printStackTrace();
}finally{
DBConn.close();
}
}
/**
* 获取数据表中的最大ID
*/
public int getMaxId(){
Connection conn = null;
int maxId = 0;
try{
conn = DBConn.getConnection();
String sql = "select max(id) as maxId from person";
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
if(rs.next()){
maxId = rs.getInt("maxId");
}
}catch(Exception ex){
ex.printStackTrace();
}finally{
DBConn.close();
}
return maxId;
}
/**
* 获取数据表中最大ID所对应的记录
*/
public Person getPersonByMaxId(){
Connection conn = null;
Person person = null;
try{
conn = DBConn.getConnection();
String sql22 = "select * from person where id=" + this.getMaxId();
PreparedStatement ps = conn.prepareStatement(sql22);
ResultSet rs = ps.executeQuery();
if(rs.next()){
person = new Person();
person.setId(rs.getInt("id"));
person.setUsername(rs.getString("username"));
person.setAge(rs.getInt("age"));
}
}catch(Exception ex){
ex.printStackTrace();
}finally{
DBConn.close();
}
return person;
}
}
下面是使用JUnit4.x测试数据库连接的单元测试类
package com.jadyer.junit4.db;
import static org.junit.Assert.*;
import java.sql.Connection;
import org.junit.Test;
import com.jadyer.junit4.db.DBConn;
public class ConnTest {
@Test
public void testGetConnection() {
Connection conn = DBConn.getConnection();
assertNotNull(conn); //只要conn不为空,说明数据库连接成功
}
}
下面是使用JUnit4.x测试数据库操作的单元测试类
package com.jadyer.junit4.db;
import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertNull;
import org.junit.BeforeClass;
import org.junit.Test;
import com.jadyer.junit4.db.Person;
import com.jadyer.junit4.db.PersonDao;
/**
* 通过使用JUnit对数据库进行测试的例子
* 我们发现这里很多地方都是重复的繁琐的,而且会互相调用API
* 因此就出现了很多数据库测试的框架,其中比较有名的就是DBUnit
* 详见http://blog.csdn.net/jadyer/article/details/9280911
*/
public class personDaoTest {
private static PersonDao personDao;
//PersonDao类中关于CRUD的四个方法都是互不相关的
//所以我们这里只需要构造一个PersonDao对象就可以了
@BeforeClass
public static void init(){
personDao = new PersonDao();
}
/**
* 判断两个对象,所对应的记录,是否相同
*/
private void comparePersons(Person person11, Person person22){
assertEquals(person11.getUsername(), person22.getUsername());
assertEquals(person11.getAge(), person22.getAge());
}
/**
* 验证原则:插入的记录与表中最大ID所对应的记录进行对比,判断两条记录是否相同
* @see 数据库单元测试时,不要产生垃圾数据,即最后应该清空测试时向数据库插入的数据
*/
@Test
public void testInsert(){
Person person11 = new Person("李寻欢", 26);
personDao.insert(person11);
Person person22 = personDao.getPersonByMaxId();
this.comparePersons(person11, person22);
personDao.removePersonById(person22.getId());
}
/**
* 验证原则:先插入数据,再取出该数据,再更新该数据,最后再比较该数据
* @see 注:不要直接取数据库中的数据进行测试,应该自己构造数据来测试
* @see 注:因为我们无法保证下一次数据库中是否仍存在该数据
*/
@Test
public void testUpdate(){
Person person11 = new Person("张起灵", 46);
personDao.insert(person11); //插入数据
Person person22 = personDao.getPersonByMaxId(); //取出数据
this.comparePersons(person11, person22); //比较数据
person22.setUsername("吴三省");
person22.setAge(36);
personDao.update(person22); //更新数据
Person person33 = personDao.getPersonByMaxId(); //再取数据
this.comparePersons(person22, person33); //再比较
personDao.removePersonById(person33.getId());
}
@Test
public void testGetPersonById(){
Person person11 = new Person("陈文锦", 49);
personDao.insert(person11);
int maxId = personDao.getMaxId();
Person person22 = personDao.getPersonById(maxId);
this.comparePersons(person11, person22);
personDao.removePersonById(maxId);
}
@Test
public void testRemovePersonById(){
Person person = new Person("齐羽", 22);
personDao.insert(person);
int maxId = personDao.getMaxId();
personDao.removePersonById(maxId);
Person person2 = personDao.getPersonById(maxId);
assertNull(person2);
}
}
最后是用到的数据库脚本文件
--Oracle 11g
--创建表格
create table person(
id number(2) primary key,
username varchar(8),
age number(2)
)
--创建序列
create sequence person_sequence increment by 1 start with 1 nomaxvalue nocycle;