mybatis parametertype 多个参数

MyBatis Spring整合配置映射接口类

applicationContext.xml

<!-- spring和MyBatis完美整合，不需要mybatis的配置映射文件 -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <!-- 配置mybatis配置文件的位置 -->
        <property name="configLocation" value="classpath:mybatis-config.xml" />
        <property name="typeAliasesPackage" value="com.thunisoft.shop.domain"/>
        <!-- 自动扫描mapping.xml文件，**表示迭代查找,代表所有目录下，也可在sqlMapConfig.xml中单独指定xml文件 -->
        <property name="mapperLocations" value="classpath:com/thunisoft/shop/mapper/*.xml" /> 
    </bean>
<!-- 扫描mybatis映射接口类-->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
        <!-- 映射接口类包路径 -->
        <property name="basePackage" value="com.thunisoft.shop.dao"/>
    </bean>

一、单个参数：

1.XML

ProductDaoImpl.java

public Product getById(Long id){
        return sqlSessionTemplate.selectOne(
                "com.thunisoft.shop.mapper.ProductMapper.getById",id);
    }

ProductMapper.xml

<select id="getById" resultMap="product" parameterType="long">
        select
        p.id,p.name,p.sn,p.costPrice,p.salePrice,
        p.place,p.model,p.num,p.viewTimes,p.recommended,
        p.inputTime
        from t_product p
        where p.id=#{id}
    </select>

2.注解（映射器接口类）

IUserDao.java

@Select("SELECT * FROM t_user WHERE id = #{id}")
public User getUserById(Long id);

UserDaoImpl.java

public User getUserById(Long id) {
        return sqlSessionTemplate.getMapper(IUserDao.class).getUserById(id);
    }

3.注解+XML

ProductDaoImpl.java

public Product getById(@Param("id")Long id){
//二选一，通过方法名getById匹配xml中id为getById的语句
//return sqlSessionTemplate.getMapper(IUserDao.class).getById(id);
return sqlSessionTemplate.selectOne(
                "com.thunisoft.shop.mapper.ProductMapper.getById",id);
    }

ProductMapper.xml

<select id="getById" resultMap="product" parameterType="long">
        select
        p.id,p.name,p.sn,p.costPrice,p.salePrice,
        p.place,p.model,p.num,p.viewTimes,p.recommended,
        p.inputTime
        from t_product p
        where p.id=#{id}
    </select>

其中ProductDaoImpl.java中方法名和ProductMapper.xml中id一致，#{}中的参数名与方法中的参数名一致，parameterType写不写都可以，这里采用的是@Param这个参数，实际上@Param这个最后会被Mabatis封装为map类型的。

二、多个参数：

1.封装到实体类（XML）

ProductDaoImpl.java

public List<Product> getRecommendedNewProductList(Date startDate,Date endDate) {
//查询实体类
ProductQuery productQuery = new ProductQuery(startDate,endDate);
return sqlSessionTemplate.selectList("com.thunisoft.shop.mapper.ProductMapper.getRecommendedNewProducts",productQuery);
    }

ProductMapper.xml

<select id="getRecommendedNewProducts" parameterType="productQuery" resultType="product" >
        select
        name,salePrice,marketPrice,smallPic,place,model
        from t_product
        where recommended = 1 and (inputTime between #{startDate} and #{endDate})
        order by inputTime desc
    </select>

2.封装到Map（XML）

ProductDaoImpl.java

public List<Product> getRecommendedNewProductList(Date startDate,Date endDate) {
Map<String,Date> paramsMap =  new HashMap<String,Date>();
paramsMap.put("startDate",startDate);
paramsMap.put("endDate",endDate);
return sqlSessionTemplate.selectList("com.thunisoft.shop.mapper.ProductMapper.getRecommendedNewProducts",paramsMap);
    }

ProductMapper.xml

<select id="getRecommendedNewProducts" parameterType="hashmap" resultType="product" >
        select
        name,salePrice,marketPrice,smallPic,place,model
        from t_product
        where recommended = 1 and (inputTime between #{startDate} and #{endDate})
        order by inputTime desc
    </select>

3.纯注解（映射器接口类）

IUserDao.java

@Select("SELECT * FROM t_user WHERE birthday between #{startDate} and #{endDate}")
public User getUserById(Date startDate,Date endDate);

UserDaoImpl.java

public User getUserById(Date startDate,Date endDate) {
        return sqlSessionTemplate.getMapper(IUserDao.class).getUserById(startDate,endDate);
    }

4.映射器接口类

IProductDao.java

public List<Product> getRecommendedNewProductList(Date startDate,Date endDate);

ProductMapper.xml

<select id="getRecommendedNewProducts" resultType="product" >
        select
        name,salePrice,marketPrice,smallPic,place,model
        from t_product
        where recommended = 1 and (inputTime between #{0} and #{1})
        order by inputTime desc
    </select>

由于是多参数那么就不能使用parameterType， 改用#｛index｝是第几个就用第几个的索引，索引从0开始

5.封装到Map/实体（映射器接口类）

IUserDao.java

public User getUserById(HashMap map);

UserMapper.xml

<select id="getUserById" parameterType="hashmap" resultType="product" >
        SELECT * FROM t_user WHERE birthday between #{startDate} and #{endDate}
    </select>

其中hashmap是mybatis自己配置好的直接使用就行。map中key的名字是那个就在#{}使用那个

6.List封装in （映射器接口类）

IUserDao.java

public List<XXXBean> getXXXBeanList(List<String> list);

UserMapper.xml

<select id="getXXXBeanList" resultType="XXBean">
　　select 字段... from XXX where id in
　　<foreach item="item" index="index" collection="list" open="(" separator="," close=")">  
　　　　#{item}  
　　</foreach>  
</select>  

foreach 最后的效果是select 字段... from XXX where id in ('1','2','3','4')

7.参数注解 （映射器接口类）

IProductDao.java

public Product getRecommendedNewProducts(@param("startDate")Date startDate,@param("endDate")Date endDate);

ProductMapper.xml

<select id="getRecommendedNewProducts" resultType="product" >
        select
        name,salePrice,marketPrice,smallPic,place,model
        from t_product
        where recommended = 1 and (inputTime between #{startDate} and #{endDate})
        order by inputTime desc
    </select>

由于是多参数那么就不能使用parameterType， 这里用@Param来指定哪一个

8.参数既要包含String类型，又要包含List类型时

List<String> list_3 = new ArrayList<String>();
Map<String, Object> map2 = new HashMap<String, Object>();

list.add("1");
list.add("2");

map2.put("list", list); //网址id
map2.put("siteTag", "0");//网址类型

ProductDaoImpl.java

public List<SysWeb> getSysInfo(Map<String, Object> map2) {
　　return getSqlSession().selectList("com.thunisoft.shop.mapper.ProductMapper..getSysInfo", map2);
}

ProductMapper.xml

<select id="getSysInfo" parameterType="java.util.Map" resultType="SysWeb">
　　select t.sysSiteId, t.siteName, t1.mzNum as siteTagNum, t1.mzName as siteTag, t.url, t.iconPath
   from TD_WEB_SYSSITE t
   left join TD_MZ_MZDY t1 on t1.mzNum = t.siteTag and t1.mzType = 10
   WHERE t.siteTag = #{siteTag } 
   and t.sysSiteId not in 
   <foreach collection="list" item="item" index="index" open="(" close=")" separator=",">
       #{item}
   </foreach>
 </select>