JUnit4.x实现对数据库的测试

完整版见https://jadyer.github.io/

首先是数据库的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;