用Junit测试数据库

package com.test.db;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertNull;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import org.junit.BeforeClass;
import org.junit.Test;

import com.test.bean.Person;

public class PersonDBTest
{
	private static PersonDB personDB;

	@BeforeClass
	public static void init()
	{
		personDB = new PersonDB();
	}

	@Test
	public void testInsert()
	{
		Person person = new Person();

		person.setUsername("lisi");
		person.setPassword("654321");
		person.setAge(20);

		personDB.insert(person);

		Person person2 = this.getPersonByMaxId();

		this.comparePersons(person, person2);

		personDB.removeById(person2.getId());

	}

	@Test
	public void testUpdate()
	{
		Person person = new Person();

		person.setUsername("zhangsan");
		person.setPassword("langsin");
		person.setAge(40);

		// 1. insert
		personDB.insert(person);

		// 2. get
		Person person2 = this.getPersonByMaxId();

		this.comparePersons(person, person2);

		person2.setUsername("wangwu");
		person2.setPassword("abcdefg");
		person2.setAge(90);

		// 3. update. what we want to test
		personDB.update(person2);

		// 4. get
		Person person3 = this.getPersonByMaxId();

		this.comparePersons(person2, person3);

		personDB.removeById(person3.getId());
	}

	@Test
	public void testGetById()
	{
		Person person = new Person();

		person.setUsername("zhangsan");
		person.setPassword("123456");
		person.setAge(40);

		personDB.insert(person);

		int maxId = this.getMaxId();

		Person person2 = personDB.getById(maxId);

		this.comparePersons(person, person2);
		
		personDB.removeById(maxId);
	}
	
	@Test
	public void testRemoveById()
	{
		Person person = new Person();
		
		person.setUsername("zhangsan");
		person.setPassword("123456");
		person.setAge(40);
		
		personDB.insert(person);
		
		int maxId = this.getMaxId();
		
		personDB.removeById(maxId);
		
		Person person2 = personDB.getById(maxId);
		
		assertNull(person2);
		
	}

	private int getMaxId()
	{
		Connection conn = null;

		int maxId = 0;

		try
		{
			conn = Conn.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
		{
			try
			{
				if (null != conn)
				{
					conn.close();
				}

			}
			catch (Exception ex)
			{
				ex.printStackTrace();
			}
		}

		return maxId;
	}

	private Person getPersonByMaxId()
	{
		Connection conn = null;

		Person person = null;

		try
		{
			conn = Conn.getConnection();

			String sql = "select max(id) as maxId from person";

			PreparedStatement ps = conn.prepareStatement(sql);

			ResultSet rs = ps.executeQuery();

			int maxId = 0;

			if (rs.next())
			{
				maxId = rs.getInt("maxId");
			}

			String sql2 = "select * from person where id = " + maxId;

			ps = conn.prepareStatement(sql2);

			rs = ps.executeQuery();

			if (rs.next())
			{
				person = new Person();

				person.setId(maxId);
				person.setUsername(rs.getString("username"));
				person.setPassword(rs.getString("password"));
				person.setAge(rs.getInt("age"));
			}

		}
		catch (Exception ex)
		{
			ex.printStackTrace();
		}
		finally
		{
			try
			{
				if (null != conn)
				{
					conn.close();
				}

			}
			catch (Exception ex)
			{
				ex.printStackTrace();
			}
		}

		return person;
	}

	// helper method
	private void comparePersons(Person person1, Person person2)
	{
		assertEquals(person1.getUsername(), person2.getUsername());
		assertEquals(person1.getPassword(), person2.getPassword());
		assertEquals(person1.getAge(), person2.getAge());
	}

}