Mybatis传递多个参数进行SQL查询的用法
PS:ibatis3如何传递多个参数有两个方法:一种是使用java.Map,另一种是使用JavaBean。
当只向xxxMapper.xml文件中传递一个参数时,可以简单的用“_parameter”来接收xxxMapper.java传递进来的参数,并代入查询,比如说这样:
(1)xxxMapper.java文件中这样定义:
List<String> selectAllAirportCode(Boolean mapping);
(2)这时在对应的xxxMapper.xml文件中可以使用“_parameter”来接收这个参数:
<select id="selectAllAirportCode" resultType="java.lang.String" parameterType="java.lang.Boolean"> select DEPARTURE_AIRPORT from USR_AIR_LINE union select ARRIVAL_AIRPORT from USR_AIR_LINE <if test="_parameter == true"> union select REL_DEPARTURE_AIRPORT from USR_AIR_LINE union select REL_ARRIVAL_AIRPORT from USR_AIR_LINE </if> </select>
但是,如果在xxxMapper.java文件中传递进来多个参数,就不能使用上面这种形式来接收参数,这时可以有两种方案来解决这个问题:
一 向xml文件中传递进去一个Map<String, Object>集合,然后xml文件中就可以正常使用Map集合中的各个参数了。
具体实例如下:
(1)xxxMapper.java文件中这样定义:
List<Airline> findAll(Map<String, Object> parms);
(2)在用到上面定义的具体实现类中给Map传参:
public List<Airline> findAll(PageInfo page,Airline airline) { HashMap<String,Object> params = new HashMap<String,Object>(); params.put("page", page); params.put("airline", airline); return airlineMapper.findAll(params); }
(3)此时对应的xxxMapper.xml文件使用“java.util.Map”来接收这个Map集合:
<sql id="sqlfileders"> <bind name="fileders" value="#{'id':'ID','departureAirport':'DEPARTURE_AIRPORT','relDepartureAirport':'REL_DEPARTURE_AIRPORT','arrivalAirport':'ARRIVAL_AIRPORT','relArrivalAirport':'REL_ARRIVAL_AIRPORT','popStatus':'POP_STATUS','status':'STATUS','creator':'CREATOR','createTime':'CREATE_TIME'}" /> <bind name="javapropertys" value="#{'ID':'id','DEPARTURE_AIRPORT':'departureAirport','REL_DEPARTURE_AIRPORT':'relDepartureAirport','ARRIVAL_AIRPORT':'arrivalAirport','REL_ARRIVAL_AIRPORT':'relArrivalAirport','POP_STATUS':'popStatus','STATUS':'status','CREATOR':'creator','CREATE_TIME':'createTime'}" /> </sql> <select id="findAll" resultMap="BaseResultMap" parameterType="java.util.Map"> <![CDATA[ select x.* from ( select z.*, rownum numbers from ( ]]> select <include refid="Base_Column_List" /> from USR_AIR_LINE <where> <if test="airline.departureAirport != null"> DEPARTURE_AIRPORT = #{airline.departureAirport} </if> <if test="airline.arrivalAirport != null"> and ARRIVAL_AIRPORT=#{airline.arrivalAirport} </if> <if test="airline.relDepartureAirport != null"> and REL_DEPARTURE_AIRPORT = #{airline.relDepartureAirport} </if> <if test="airline.relArrivalAirport != null"> and REL_ARRIVAL_AIRPORT = #{airline.relArrivalAirport} </if> <if test="airline.popStatus != null"> and POP_STATUS = #{airline.popStatus} </if> <if test="airline.status != null"> and STATUS = #{airline.status} </if> </where> <if test="page.sortName != null"> <include refid="sqlfileders" /> <bind name="orderfield" value="#this.fileders[page.sortName]" /> order by ${orderfield} ${page.sortOrder} </if> <![CDATA[ ) z where rownum < ]]> #{page.to} <![CDATA[ ) x where x.numbers >= ]]> #{page.from} </select>
注:上面的实例实现的是分页查询数据。我们可以发现使用Map来传递参数这种形式并不好,因为这样使得在接口中只有一个Map参数,其他人进行维护的时候并不清楚到底需要向这个Map里面传递什么参数进去
二 通过给参数添加@Param注解来解决问题:
(1)给xxxMapper.java文件的方法中的参数添加@Param注解,这个注解中的值对应xml文件中使用到的参数名称:
Airline selectEffectiveAirline( @Param("departureAirport") String departureAirport, @Param("arrivalAirport") String arrivalAirport, @Param("status") BigDecimal status);
(2)此时xxxMapper.xml文件中对应的地方就可以正常使用在@Param注解中对应的值了:
<select id="selectEffectiveAirline" resultMap="BaseResultMap" parameterType="java.util.Map"> select <include refid="Base_Column_List" /> from USR_AIR_LINE <where> <if test="departureAirport != null"> DEPARTURE_AIRPORT = #{departureAirport} </if> <if test="arrivalAirport != null"> and ARRIVAL_AIRPORT=#{arrivalAirport} </if> <if test="status != null"> and STATUS = #{status} </if> </where> </select>
注:需要注意的是if条件判断中的参数和在SQL语句中写法是不一样的,if判断中的变量是没有添加#{ }的
下面在单独给大家介绍下通过Mybatis传递多个参数的两个方法
Map传递多个参数
parameterType 可以是别名或完全限定名,map或者java.util.Map,这两个都是可以的
<select id="selectBlogByMap" parameterType="map" resultType="Blog"> select t.ID, t.title, t.content FROM blog t where t.title = #{h_title} and t.content =#{h_content} </select> public void testSelectByMap() { SqlSession session = sqlSessionFactory.openSession(); Map<String, Object> param=new HashMap<String, Object>(); param.put("h_title", "oracle"); param.put("h_content", "使用序列"); Blog blog = (Blog)session.selectOne("cn.enjoylife.BlogMapper.selectBlogByMap",param); session.close(); System.out.println("blog title:"+blog.getTitle()); }
通过JavaBean传递多个参数
<select id="selectBlogByBean" parameterType="Blog" resultType="Blog"> select t.ID, t.title, t.content from blog t wheret.title = #{title} and t.content =#{content} </select> public void testSelectByBean() { SqlSession session = sqlSessionFactory.openSession(); Blog blog=new Blog(); blog.setTitle("oracle"); blog.setContent("使用序列!"); Blog newBlog = (Blog)session.selectOne("cn.enjoylife.BlogMapper.selectBlogByBean",blog); session.close(); System.out.println("new Blog ID:"+newBlog.getId()); }