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());
}
}