package com.bao.dbunit.dao;

import com.bao.dbunit.entity.User;

public  interface UserDao {
   public User getUserByNick(String nick);
  
   public  void save(User user);
  
   public  void update(User user);
  
   public  void remove(String nick);
}

package com.bao.dbunit.entity;

public  class User {
   private String nick;
   private String password;

   public String getNick() {
     return nick;
  }

   public  void setNick(String nick) {
     this.nick = nick;
  }

   public String getPassword() {
     return password;
  }

   public  void setPassword(String password) {
     this.password = password;
  }

}

package com.bao.dbunit.dao.impl;

import org.springframework.jdbc.core.support.JdbcDaoSupport;

public  class BaseDao  extends JdbcDaoSupport {

}

package com.bao.dbunit.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;
import com.bao.dbunit.dao.UserDao;
import com.bao.dbunit.entity.User;

public  class DefaultUserDao  extends BaseDao  implements UserDao {

   private  static String QUERY_BY_NICK =  "select * from user where user.nick = ?";
  
   private  static String REMOVE_USER =  "delete from user where user.nick = ?";
  
   private  static String INSERT_USER =  "insert into user(nick,password) values(?, ?)";
  
   private  static String UPDATE_USER =  "update user set user.password = ? where user.nick = ?";
  @Override
   public User getUserByNick(String nick) {
     return (User) getJdbcTemplate().queryForObject(QUERY_BY_NICK, new Object[]{nick},  new RowMapper(){
      @Override
       public Object mapRow(ResultSet rs,  int index)  throws SQLException {
        User user =  new User();
        user.setNick(rs.getString( "nick"));
        user.setPassword(rs.getString( "password"));
         return user;
      }
    });
  }

  @Override
   public  void remove(String nick) {
    getJdbcTemplate().update(REMOVE_USER,  new Object[]{nick});
  }

  @Override
   public  void save(User user) {
    getJdbcTemplate().update(INSERT_USER,  new Object[]{user.getNick(), user.getPassword()});
  }

  @Override
   public  void update(User user) {
    getJdbcTemplate().update(UPDATE_USER,  new Object[]{user.getPassword(), user.getNick()});
  }

}

<?xml version= "1.0" encoding= "UTF-8"?>
<beans xmlns= "http://www.springframework.org/schema/beans"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns:aop="http://www.springframework.org/schema/aop"
  xmlns:tx="http://www.springframework.org/schema/tx"
  xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
    http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd
    http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd">

  <bean name="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
    <property name="url" value="jdbc:mysql://localhost/dbunit"></property>
    <property name="password" value="root" />
    <property name="username" value="root" />
  </bean>

  <bean name="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> 
    <property name="dataSource" ref="dataSource" />
  </bean>
  
  <bean name="baseDao" abstract="true" class="com.bao.dbunit.dao.impl.BaseDao">
    <property name="jdbcTemplate"    ref="jdbcTemplate"/>
  </bean>
  
  <bean name="userDao" parent="baseDao" class="com.bao.dbunit.dao.impl.DefaultUserDao" />
  
</beans>

create  table  user(
  nick  varchar(12)  not  null,
  password  varchar(12)  not  null,
   primary  key(nick)
);

package com.bao.dbunit.dao;

import javax.sql.DataSource;

import org.dbunit.Assertion;
import org.dbunit. database.DatabaseConnection;
import org.dbunit. database.IDatabaseConnection;
import org.dbunit. database.QueryDataSet;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.xml.FlatXmlDataSet;
import org.dbunit.operation.DatabaseOperation;
import org.junit.Assert;
import org.junit.Before;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.AbstractTransactionalJUnit4SpringContextTests;
import org.springframework.test.context. transaction.TransactionConfiguration;

import com.bao.dbunit.dao.UserDao;
import com.bao.dbunit.entity. User;


@ContextConfiguration(locations = { "classpath:testApplicationContext.xml" })
@TransactionConfiguration(defaultRollback = true)
public class UserDaoTest extends AbstractTransactionalJUnit4SpringContextTests {
  @Autowired
  private UserDao userDao;

  @Autowired
  private DataSource dataSource;

  private IDatabaseConnection conn;

  @Before
   public void initDbunit() throws Exception {
    conn = new DatabaseConnection(DataSourceUtils.getConnection(dataSource));
  }

  @Test
   public void saveUser() throws Exception {
     User  user = new  User();
     user.setNick("user001");
     user.setPassword("password001");
    userDao. save( user);

    QueryDataSet actual = new QueryDataSet(conn);
    actual.addTable(" user",
        " select *  from  user  where  user.nick =  'user001'");

    IDataSet expected = new FlatXmlDataSet(new ClassPathResource(
        "com/bao/dbunit/dao/user001.xml").getFile());

    Assertion.assertEquals(expected, actual);
  }

  @Test
   public void updateUser() throws Exception {

    IDataSet origen = new FlatXmlDataSet(new ClassPathResource(
        "com/bao/dbunit/dao/user001.xml").getFile());

    DatabaseOperation. INSERT. execute(conn, origen);

     User  user = new  User();
     user.setNick("user001");
     user.setPassword("password002");
    userDao. update( user);

    QueryDataSet actual = new QueryDataSet(conn);
    actual.addTable(" user",
        " select *  from  user  where  user.nick =  'user001'");

    IDataSet expected = new FlatXmlDataSet(new ClassPathResource(
        "com/bao/dbunit/dao/user001_updated.xml").getFile());

    Assertion.assertEquals(expected, actual);
  }

  @Test
   public void removeUser() throws Exception {
    IDataSet origen = new FlatXmlDataSet(new ClassPathResource(
        "com/bao/dbunit/dao/user001.xml").getFile());
    DatabaseOperation. INSERT. execute(conn, origen);

    userDao.remove("user001");

    QueryDataSet actual = new QueryDataSet(conn);
    actual.addTable(" user", " select *  from  user  where nick =  'user001'");

    Assert.assertEquals(0, actual.getTable(" user").getRowCount());
    
  }
  
  @Test
   public void findUser() throws Exception {
    IDataSet data = new FlatXmlDataSet(new ClassPathResource(
        "com/bao/dbunit/dao/user001.xml").getFile());
    DatabaseOperation. INSERT. execute(conn, data);

    
     User  user = userDao.getUserByNick("user001");

    Assert.assertEquals("password001",  user.getPassword());
  }

}

<? xml  version ="1.0"  encoding ="UTF-8" ?>
< dataset >
   < user  nick ="user001"  password ="password002"  />
</ dataset >

<? xml  version ="1.0"  encoding ="UTF-8" ?>
< dataset >
   < user  nick ="user001"  password ="password001"  />
</ dataset >

<? xml  version ="1.0"  encoding ="UTF-8" ?>
< beans  xmlns ="http://www.springframework.org/schema/beans"
   xmlns:xsi ="http://www.w3.org/2001/XMLSchema-instance"
   xmlns:aop ="http://www.springframework.org/schema/aop"
   xmlns:tx ="http://www.springframework.org/schema/tx"
  xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
    http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd
    http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd" >

   < bean  name ="dataSource"  class ="org.springframework.jdbc.datasource.DriverManagerDataSource" >
     < property  name ="driverClassName"  value ="com.mysql.jdbc.Driver" > </ property >
     < property  name ="url"  value ="jdbc:mysql://localhost/dbunit" > </ property >
     < property  name ="password"  value ="root"  />
     < property  name ="username"  value ="root"  />
   </ bean >

   < bean  name ="transactionManager"  class ="org.springframework.jdbc.datasource.DataSourceTransactionManager" >
     < property  name ="dataSource"  ref ="dataSource"  />
   </ bean >
  
   < tx:annotation-driven  transaction-manager ="transactionManager" />
  
   < bean  name ="jdbcTemplate"  class ="org.springframework.jdbc.core.JdbcTemplate" > 
     < property  name ="dataSource"  ref ="dataSource"  />
   </ bean >
  
   < bean  name ="baseDao"  abstract ="true"  class ="com.bao.dbunit.dao.impl.BaseDao" >
     < property  name ="jdbcTemplate"     ref ="jdbcTemplate" />
   </ bean >
  
   < bean  name ="userDao"  parent ="baseDao"  class ="com.bao.dbunit.dao.impl.DefaultUserDao"  />
  
</ beans >

   < dependencies >
     < dependency >
       < groupId >org.springframework </ groupId >
       < artifactId >spring </ artifactId >
       < version >2.5.6 </ version >
     </ dependency >
     < dependency >
       < groupId >mysql </ groupId >
       < artifactId >mysql-connector-java </ artifactId >
       < version >5.1.6 </ version >
     </ dependency >
     < dependency >
       < groupId >commons-logging </ groupId >
       < artifactId >commons-logging </ artifactId >
       < version >1.1.1 </ version >
     </ dependency >

     < dependency >
       < groupId >org.springframework </ groupId >
       < artifactId >spring-test </ artifactId >
       < version >2.5.6 </ version >
       < scope >test </ scope >
     </ dependency >
     < dependency >
       < groupId >junit </ groupId >
       < artifactId >junit </ artifactId >
       < version >4.4 </ version >
       < scope >test </ scope >
     </ dependency >
     < dependency >
       < groupId >org.easymock </ groupId >
       < artifactId >easymock </ artifactId >
       < version >2.4 </ version >
       < scope >test </ scope >
     </ dependency >
     < dependency >
       < groupId >org.dbunit </ groupId >
       < artifactId >dbunit </ artifactId >
       < version >2.4.6 </ version >
       < scope >test </ scope >
     </ dependency >
     < dependency >
       < groupId >org.slf4j </ groupId >
       < artifactId >slf4j-nop </ artifactId >
       < version >1.5.6 </ version >
       < scope >test </ scope >
     </ dependency >
   </ dependencies >
   < build >
     < plugins >
       < plugin >
         < artifactId >maven-compiler-plugin </ artifactId >
         < configuration >
           < target >1.6 </ target >
           < source >1.6 </ source >
           < encoding >UTF-8 </ encoding >
         </ configuration >
       </ plugin >
     </ plugins >
   </ build >