14.通用Mapper中的方法详解

package cn.itcast.mybatis.mapper;

import static org.junit.Assert.*;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.persistence.Id;

import net.sf.jsqlparser.statement.update.Update;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.apache.log4j.lf5.util.Resource;
import org.junit.Before;
import org.junit.Test;

import com.github.abel533.entity.Example;

import cn.itcast.mybatis.pojo.User;

public class NewUserMapperTest {
    
    private NewUserMapper newUserMapper;

    @Before
    public void setUp() throws Exception {
//        完成mybatis的初始化
        InputStream inputStream=Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session=sqlSessionFactory.openSession(true);
        this.newUserMapper=session.getMapper(NewUserMapper.class);
    }

    @Test
    public void testSelectOne() {
//        根据用户名和密码查询一条数据
        User record=new User();
        record.setName("王五");
        record.setPassword("123456");
       User user = this.newUserMapper.selectOne(record);
       System.out.println(user);
       /**
        * Preparing: SELECT USER_NAME USERNAME,NAME,UPDATED,PASSWORD,ID,BIRTHDAY,SEX,AGE,CREATED FROM tb_user WHERE NAME = ? AND PASSWORD = ? 
        * Parameters: 王五(String), 123456(String)
        */
    }

    @Test
    public void testSelect() {
//       用于查询多条符合条件的数据,如果无查询条件就设置为null,表示查询所有的数据；
//        如果要设置条件，就实例化user，设置条件即可
        User record=new User();
        record.setSex(1);
        List<User> list=this.newUserMapper.select(record);
//        List<User> list = this.newUserMapper.select(null);
        for(User user:list){
            System.out.println(user);;
        }
        /**
         * Preparing: SELECT USER_NAME USERNAME,NAME,PASSWORD,BIRTHDAY,UPDATED,SEX,AGE,CREATED,ID FROM tb_user WHERE SEX = ?
         * Parameters: 1(Integer)
         */
    }

    @Test
    public void testSelectCount() {
//         查询数据总条数设置为null,若按条件查询总条数可以设置条件
//        比如，查询性别为男性1的数据总条数
        User record=new User();
        record.setSex(1);
        System.out.println(this.newUserMapper.selectCount(record));
        /**
         * Preparing: SELECT COUNT(*) FROM tb_user WHERE SEX = ? 
         */
//      System.out.println(this.newUserMapper.selectCount(null));
        /**
         *  Preparing: SELECT COUNT(*) FROM tb_user 
         */
    }

    @Test
    public void testSelectByPrimaryKey() {
//        按主键查询数据，注意是根据主键，而不是id
       User user = this.newUserMapper.selectByPrimaryKey(2L);
       System.out.println(user);
       /**
        * 例如pojo中@Id若放在name属性上
        * @Id
        * private String userName;
        * 那么主键就是userName，就按userName进行查询而不是id
        */
    }
//========================================================================================
    @Test
    public void testInsert() {
        User user=new User();
        user.setAge(22);
        user.setName("test_1");
        user.setSex(2);
        user.setPassword("222");
        user.setUserName("test_1_username");
        this.newUserMapper.insert(user);
        System.out.println(user.getId());
        /**
         * Preparing: INSERT INTO tb_user (USER_NAME,NAME,CREATED,UPDATED,PASSWORD,SEX,ID,AGE,BIRTHDAY) VALUES ( ?,?,?,?,?,?,?,?,? )
         * Parameters: test_1_username(String), test_1(String), null, null, 222(String), 2(Integer), null, 22(Integer), null 
         * 
         * Executing: SELECT LAST_INSERT_ID() 
         */
    }

    @Test
    public void testInsertSelective() {
        User user=new User();
        user.setUserName("test_3_username");
        this.newUserMapper.insertSelective(user);
        System.out.println(user.getId());
        /**
         * Preparing: INSERT INTO tb_user ( USER_NAME,ID ) VALUES ( ?,? ) 
         * Parameters: test_3_username(String), null
         */
    }
//=================================================================================
    /**
     * 总结insert()与InsertSelective()的区别：
     * insert是无论你的插入的属性字段是否为null都会作为一个字段去插入
     * insertSelective是当你插入的属性值不为null时才作为一个字段去插入
     * 详细对比两者的插入语句就可以发现。
     */
//=================================================================================

    @Test
    public void testDelete() {
//        可以根据属性去删除，也可以设置为null把所有数据都删除
        User user=new User();
        user.setName("test_1");
        this.newUserMapper.delete(user);
        /**
         * Preparing: DELETE FROM tb_user WHERE NAME = ? 
         * Parameters: test_1(String)
         * Updates: 1
         */
//      this.newUserMapper.delete(null);
    }

    @Test
    public void testDeleteByPrimaryKey() {
//        根据主键删除数据，只删除一条
        this.newUserMapper.deleteByPrimaryKey(12L);
        /**
         * Preparing: DELETE FROM tb_user WHERE (ID = ? ) 
         *  Parameters: 12(Long)
         *  Updates: 1
         * 
         */
    }

    @Test
    public void testUpdateByPrimaryKey() {
//        根据主键进行更新
        User user=new User();
        user.setId(13L);
        user.setName("13");
        user.setUserName("13");
        this.newUserMapper.updateByPrimaryKey(user);
        /**
         * Preparing: UPDATE tb_user SET USER_NAME = ?, NAME = ?, PASSWORD = ?, BIRTHDAY = ?, CREATED = ?, AGE = ?, UPDATED = ?, SEX = ? WHERE ID = ?
         * Parameters: 13(String), 13(String), null, null, null, null, null, null, 13(Long) 
         * Updates: 1
         */
    }

    @Test
    public void testUpdateByPrimaryKeySelective() {
//        根据主键更新不为null的字段
//        例如修改用户密码
        User user=new User();
        user.setId(2L);
        user.setPassword("123456789");
        user.setUpdated(new Date());
        this.newUserMapper.updateByPrimaryKeySelective(user);
        /**
         * Preparing: UPDATE tb_user SET UPDATED = ?, PASSWORD = ? WHERE ID = ? 
         * Parameters: 2017-11-17 13:27:04.334(Timestamp), 123456789(String), 2(Long)
         * Updates: 1
         */
        
    }
    
//    以下都是ByExample，比较复杂但是却很灵活，可以解决如下需求
    /**
     * 需求：
     * 1、查询用户列表并且按照更新时间倒序排序
     * 2、根据多个id查询用户数据
     * 3、批量删除数据
     */

    @Test
    public void testSelectCountByExample() {
        fail("Not yet implemented");
    }

    @Test
    public void testDeleteByExample() {
//        3、批量删除数据
        Example example =new Example(User.class);
        List<Object> list = new ArrayList<Object>();
        list.add(2);
        list.add(2);
        example.createCriteria().andIn("sex",list);
        this.newUserMapper.deleteByExample(example);
        /**
         * 只可以用于单表删除，如果你要删除的数据与另一张表有关联，那么无法删除
         */
    }

    @Test
    public void testSelectByExample() {
   //   1、查询用户列表并且按照更新时间倒序排序
        Example example=new Example(User.class);
        example.setOrderByClause("updated DESC");
        List<User> list = this.newUserMapper.selectByExample(example);
        for(User user:list){
            System.out.println(user);
        }
        /**
         * Preparing: SELECT USER_NAME USERNAME,BIRTHDAY,NAME,AGE,PASSWORD,CREATED,ID,SEX,UPDATED FROM tb_user order by updated DESC 
         * Total: 10
         * 
         * 
         * User [id=2, userName=lisi, password=123456789, name=李四, age=21, sex=2, birthday=Tue Jan 01 00:00:00 CST 1991, created=Fri Sep 19 16:56:04 CST 2014, updated=Fri Nov 17 13:27:04 CST 2017]
         * User [id=1, userName=null, password=abc123, name=null, age=null, sex=null, birthday=null, created=null, updated=Mon Jul 31 18:33:00 CST 2017]
         * User [id=6, userName=lilei, password=123456, name=李磊, age=23, sex=1, birthday=Mon Aug 08 00:00:00 CDT 1988, created=Sat Sep 20 11:41:15 CST 2014, updated=Sat Sep 20 11:41:15 CST 2014]
         * User [id=4, userName=zhangwei, password=123456, name=张伟, age=20, sex=1, birthday=Thu Sep 01 00:00:00 CDT 1988, created=Fri Sep 19 16:56:04 CST 2014, updated=Fri Sep 19 16:56:04 CST 2014]
         * User [id=5, userName=lina, password=123456, name=李娜, age=28, sex=1, birthday=Tue Jan 01 00:00:00 CST 1985, created=Fri Sep 19 16:56:04 CST 2014, updated=Fri Sep 19 16:56:04 CST 2014]
         * User [id=3, userName=wangwu, password=123456, name=王五, age=22, sex=2, birthday=Sun Jan 01 00:00:00 CST 1989, created=Fri Sep 19 16:56:04 CST 2014, updated=Fri Sep 19 16:56:04 CST 2014]
         * User [id=7, userName=null, password=null, name=sansan, age=null, sex=null, birthday=null, created=null, updated=null]
         * User [id=8, userName=null, password=null, name=test_insertSelective, age=null, sex=null, birthday=null, created=null, updated=null]
         * User [id=9, userName=null, password=null, name=sansan, age=null, sex=null, birthday=null, created=null, updated=null]
         * User [id=13, userName=13, password=null, name=13, age=null, sex=null, birthday=null, created=null, updated=null]
         */
    }
    @Test
    public void testSelectByExample2() {
//        2、根据多个id查询用户数据
        Example example =new Example(User.class);
//        创建查询条件
        List<Object> ids = new ArrayList<Object>();
        ids.add(1L);
        ids.add(2L);
        ids.add(13L);
        example.createCriteria().andIn("id", ids);
        List<User> list = this.newUserMapper.selectByExample(example);
        for(User user:list){
            System.out.println(user);
        }
        /**
         *Preparing: SELECT USER_NAME USERNAME,NAME,BIRTHDAY,PASSWORD,SEX,UPDATED,CREATED,ID,AGE FROM tb_user WHERE ( ID in(?,?,?) ) 
         * Parameters: 1(Long), 2(Long), 13(Long)
         * Total: 3
         * 
         * User [id=1, userName=null, password=abc123, name=null, age=null, sex=null, birthday=null, created=null, updated=Mon Jul 31 18:33:00 CST 2017]
         * User [id=2, userName=lisi, password=123456789, name=李四, age=21, sex=2, birthday=Tue Jan 01 00:00:00 CST 1991, created=Fri Sep 19 16:56:04 CST 2014, updated=Fri Nov 17 13:27:04 CST 2017]
         * User [id=13, userName=13, password=null, name=13, age=null, sex=null, birthday=null, created=null, updated=null]
         */
        
    }

    @Test
    public void testUpdateByExampleSelective() {
        fail("Not yet implemented");
    }

    @Test
    public void testUpdateByExample() {
        fail("Not yet implemented");
    }

}