Spring中的Jdbc

項目結構圖：

(1)創建java project
(2)創建數據庫(mysql)
create database mydb;
create table t_user(user_id int primary key auto_increment,username varchar(20),password varchar(20));
insert into t_user(username,password) values('java','1234');
insert into t_user(username,password) values('php','1234');

(3)添加spring類庫

commons-logging.jar
log4j-1.2.15.jar
spring.jar
aspectjrt.jar(可選)
aspectjweaver.jar(可選)
(4)在src下添加spring配置文件
applicationContext.xml
log4j.properties
 建議：
      在搭建框架使用debug模式，
      開發程序使用info模式
(5)實體類
   com.tarena.entity.User
(6)連接數據庫
   添加mysql驅動庫：
      mysql-connector-java-5.1.16.jar
   在spring配置文件中配置：

<!-- 連接數據庫 -->
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://127.0.0.1:3306/lirui"/>
        <property name="username" value="root"/>
        <property name="password" value="865631572"/>
    </bean>

(7)編寫DAO
(a)IUserDao接口類

public interface IUserDao {
    int saveUser(User user);
    int deleteUserById(User user);
    int updateUserById(User user);

    List<Map<String,Object>> findAllUser();
    int countUser();
    User findUserById(User user);
    List<User> findAll();
}

(b)UserDaoImpl實現類

public class UserDaoImpl extends JdbcDaoSupport implements IUserDao {

    @Override
    public int saveUser(User user) {
        String sql = "insert into t_user(username,password) values(?,?)";
        return this.getJdbcTemplate().
            update(sql, new Object[]{
                    user.getUsername(),user.getPassword()
            });
    }

    @Override
    public int deleteUserById(User user) {
        String sql = "delete from t_user where user_id=?";
        return this.getJdbcTemplate().update(sql, new Object[]{user.getUserId()});
    }

    @Override
    public int updateUserById(User user) {
        String sql = "update t_user set username=? where user_id=?";
        return this.getJdbcTemplate().update(sql, new Object[]{user.getUsername(),user.getUserId()});
    }

    @Override
    public int countUser() {
        String sql = "select count(*) from t_user";
        return this.getJdbcTemplate().queryForInt(sql);
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<User> findAll() {
        String sql = "select * from t_user";

        return this.getJdbcTemplate().query(sql, new RowMapper(){

            @Override
            public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                User user = new User();
                user.setUserId(rs.getInt("user_id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                return user;
            }});
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<Map<String, Object>> findAllUser() {
        String sql = "select * from t_user";
        return this.getJdbcTemplate().queryForList(sql);
    }

    @Override
    public User findUserById(User user) {
        String sql = "select * from t_user where user_id = ?";
        return (User)this.getJdbcTemplate().queryForObject(sql,new Object[]{user.getUserId()}, new RowMapper(){

            @Override
            public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                User user = new User();
                user.setUserId(rs.getInt("user_id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                return user;
            }});
    }

}

(c)在spring配置文件中配置UserDaoImpl

   <!-- dao -->
   <bean id="userDaoImpl" class="com.tarena.dao.impl.UserDaoImpl">
      <property name="dataSource" ref="dataSource"/>
   </bean>

其中name="dataSource"中不可改,因為UserDaoImpl繼承JdbcDaoSupport類,這個類中有一個setDataSource(DataSource datasource)方法,采用的是Spring set方式

(8)測試
靜態導包：
(1)jdk5.0以上
(2)方法必須是static修改方法
使用:
(1)import static 包名.靜態方法名   例:import static org.junit.Assert.assertEquals;
(2)靜態方法名(...)
   

//斷言：預計值與實際值比較
assertEquals(預計值,實際值);     
UserDaoImplTest測試類:

 public class UserDaoImplTest {
    private IUserDao iuserDao = null;
    @Before
    public void setUp(){
        ApplicationContext ac = new ClassPathXmlApplicationContext("applicationContext.xml");   
        iuserDao=(IUserDao)ac.getBean("userDaoImpl");
    }

    @Test
    @Ignore
    public void testSaveUser(){
        //IUserDao iuserDao = new UserDaoImpl();
        User user = new User();
        user.setUsername("凈凈");
        user.setPassword("1234");
        int row= iuserDao.saveUser(user);
        //斷言：預計值,實際值比較
        //assertEquals(預計值,實際值);
        assertEquals(1,row);
    }

    @Test
    @Ignore
    public void testDeleteUserById(){
        User user = new User();
        user.setUserId(3);
        int row = iuserDao.deleteUserById(user);
        assertEquals(1, row);
    }
    @Test
    @Ignore
    public void testUpdateUserById(){
        User user = new User();
        user.setUserId(5);
        user.setUsername("八戒");
        int row = iuserDao.updateUserById(user);
        assertEquals(1, row);
    }

    @Test
    @Ignore
    public void testFindAllUser(){
      List<Map<String,Object>> list=iuserDao.findAllUser(); 
      /*for (Map<String, Object> map : list) {
         System.out.println(map.get("user_id")+" "+
                            map.get("username")

         );
      }*/
      assertEquals(5, list.size());
    }
    @Test
    @Ignore
    public void testCountUser(){
        System.out.println(iuserDao.countUser());
    }

    @Test
    @Ignore
    public void testFindUserById(){
        User user = new User();
        user.setUserId(1);
        User u = iuserDao.findUserById(user);
        System.out.println(u.getUsername());
    }
    @Test
    public void testFindAll(){
        List<User> users = iuserDao.findAll();
        for (User user : users) {
            System.out.println(user.getUserId()+" "+
                               user.getUsername()+" "+
                               user.getPassword()
            );

        }
    }