JDBC 操作

JDBC操作

这里讲一个JDBC 基本操作流程,数据库为MySQL5.0,对CRUD操作采用在main方法中实现,也有在Junit4中的实现

搭建好环境后，创建数据库junittest,这里也一并创建一个表Person

表结构:

Id          int(10)              auto_increment not null

name    varchar(45)

age       int(3)

主要是两个有关数据库的类,另附带一个Junit4测试类

Ec.java:

public class Ec{
        public static Connection conn(){
                Connection conn = null;
               try{
               Class.forName(com.mysql.jdbc.Driver);
               conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/junittest","root","root");
              }catch(SQLException e){
                   e.printStackTrace();
                 }catch(Exception ex){
                  ex.printStackTrace();
                  }
             return conn;
      }
}<span style="font-family: Arial, Helvetica, sans-serif; background-color: rgb(255, 255, 255);"> </span>

PersonDB.java:

public class   PersonDB{
 
//增加数据
 public void insert(Person person){
  Connection conn = Ec.getConn();
  try{
  String sql = "insert into person(name,age) values(?,?)";
  PreparedStatement ps = conn.prepareStatement(sql);
  ps.setString(1, person.getName());
  ps.setInt(2, person.getAge());
  ps.executeUpdate();
  
  }catch(Exception e){
   e.printStackTrace();
  }finally{
   if(conn != null)
    try {
     conn.close();
    } catch (SQLException e) {
     e.printStackTrace();
    }
  }
 } 
 
 //修改
 public void update(Person person){
  Connection conn = Ec.getConn();
  try{
   String sql = "update person set name = ? ,age = ? where id = ?";
   PreparedStatement ps = conn.prepareStatement(sql);
   ps.setString(1, person.getName());
   ps.setInt(2, person.getAge());
   ps.setInt(3, person.getId());
   ps.executeUpdate();
   
  }catch(Exception e)
  {
   e.printStackTrace();
  }finally{
   if(conn != null){
    try {
     conn.close();
    } catch (SQLException e) {
     e.printStackTrace();
    }
   }
   
  }
 }

 //根据Id 查询
 public Person getById(int id){
  Person person = null;
  Connection conn = Ec.getConn();
  try{
   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.setName(rs.getString("name"));
    person.setAge(rs.getInt("age"));
   }
   rs.close();
  }catch(Exception e){
   e.printStackTrace();
  }finally{
   if(conn != null){
    try {
     conn.close();
    } catch (SQLException e) {
     e.printStackTrace();
    }
   }
  }
  return person;
 }

 //删除
 public void delete(Person person){
  Connection conn = Ec.getConn();
  try{
   String sql = "delete from person where id = ?";
   PreparedStatement ps = conn.prepareCall(sql);
   ps.setInt(1, person.getId());
   ps.executeUpdate();
   
  }catch(Exception e){
   e.printStackTrace();
  }finally{
   if(conn != null){
    try {
     conn.close();
    } catch (SQLException e) {
     e.printStackTrace();
    } 
   } 
  }
 }
}<span style="font-family: Arial, Helvetica, sans-serif; background-color: rgb(255, 255, 255);"> </span>

PersonDBTest.java

public class PersonDBTest {
 private static PersonDB personDB;
 
 @BeforeClass
 public static void init()
 {
  personDB = new PersonDB();
 }
 
 //测试增加
 @Test
 //@Ignore("This case is ignored") 
 public void testInsert(){
  Person person = new Person();
  
  person.setUsername("lisi");
  person.setPassword("654321");
  person.setAge(20);
  
  personDB.insert(person);
  
  /*如何知道增加了以上数据，一个通用的方法是将对象插入到数据库后，再将对象选择出来
  *将选择出来的对象(相当于一个临时对象temp object)的属性跟插入的对象的属性进行比较,如果相同，说明插入成功
  */
  
  //这里根据最大Id选择出来，一般最大Id表示刚插入的记录
  Person person2 = this.getPersonByMaxId();
  //比较两个对象
  this.comparePersons(person, person2); 
 }
 
 //测试更新
 @Test
 //@Ignore("This case is ignored") 
 public void testUpdate()
 {
  Person person = new Person();
  
  /**
   * 更新数据的步骤是:首先把对象插入到数据库，接下来去更新它，接下来把它取出来进行比较
   * 这个与在页面中的更新过程:先取出来，再更新 是不同的
   * 
   */
  person.setUsername("zhangshan");
  person.setPassword("111");
  person.setAge(25);
  
  //增加数据
  personDB.insert(person);
  Person person2 = this.getPersonByMaxId(); //取得一个临时的对象,用于比较
  this.comparePersons(person, person2);
  
  person2.setUsername("wangwu");
  person2.setPassword("abc");
  person2.setAge(30);
  
  //更新
  personDB.update(person2);
  
  //取出刚刚更新过的对象
  Person person3 = this.getPersonByMaxId();
  this.comparePersons(person2, person3);
  //删除垃圾数据
  personDB.removeById(person3.getId());
  
 }
 
 //测试查询
 @Test
 //@Ignore("This case is ignored") 
 public void testGetById()
 {
  /**
   * 查询数据的步骤是:首先把对象插入到数据库，再把它取出来进行比较
   */
  Person person = new Person();
  person.setUsername("zhangsan");
  person.setPassword("123");
  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()
 {
  /**
   * 删除数据的步骤：首先同样是插入数据，此时将产生个最大Id,然后删除，
   * 此时可以根据最大Id获取一个对象,并断言为空
   */
  Person person = new Person();
  
  person.setUsername("zhangsan");
  person.setPassword("123");
  person.setAge(40);
  
  personDB.insert(person);
  int maxId = this.getMaxId();
  personDB.removeById(maxId);
  Person person2 = personDB.getById(maxId);
  
  //断言(Assert类的一个静态方法)
  assertNull(person2);
 }
 
 //获取表中最大Id的对象
 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;
   //"select *  from person where id = max(id)"
   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;
 }
 
 //获取最大Id,一般最大Id表示刚插入的对象的Id
 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 void comparePersons(Person person1,Person person2)
 {
  assertEquals(person1.getUsername(),person2.getUsername());
  assertEquals(person1.getPassword(),person2.getPassword());
  assertEquals(person1.getAge(),person2.getAge());
 }
}