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

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