MyBatis多参数处理,动态SQL

MyBatis 代理生成Dao实现类

导jar包写mybatis.cfg.xml配置文件这些之前已经说过,不在赘述

1. 首先需要写一个Dao接口

一般来说要有增删改和各种查的方法

public interface UsersDao {
  //五常
  public void save(Users u) throws RuntimeException;

  public void update(Users u) throws RuntimeException;

  public void delete(String uid) throws RuntimeException;

  public List<Users> findAll()throws RuntimeException;

  public Users findById()throws RuntimeException;
}

2. 编写对应的映射文件

1. 头文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

2. mapper的名称空间必须是持久层接口的全名

<!-- 必须是 持久层接口(以前的Manager)的名字 !!! -->
<mapper namespace="com.senchen.dao.UsersDao">

3. 实现接口的方法

3.1 保存用户的方法用insert标签

id对应接口中方法的名字 parameterType对应参数类型

在标签中写sql语句 #{}中的参数对应get 方法首字母小写

<!-- 接口中的 方法名，返回类型，参数类型保持一致   -->
<insert id="save" parameterType="uuu">
  insert into users values(
  #{userId},
  #{uname},
  #{upwd},
  #{uhead},
  #{uphone},
  #{uemail},
  100,
  0
  )
</insert>

测试代码

UsersDao udao = session.getMapper( UsersDao.class);
Users u = new Users();
u.setUemail("a@b.c");
u.setUhead("u.png");
u.setUname("张大三");
u.setUphone("131");
u.setUpwd("000");
u.setUserId("AAA2");
udao.save(u);
session.commit();

成功


3.2 查询所有的用户用select标签

resultType返回的结果类型为Users不是List

<select id="findAll" resultType="uuu">
  select * from users
</select>

测试

List<Users> ulist = udao.findAll();
for (Users s : ulist) {
  System.out.println(s.getUserId()+"\t"+s.getUname());
}

结果


3.3 返回类型是Map的查询所有(两个表联查)

//详细
public List<Map<String,Object>> findInfo( ) throws RuntimeException;

resultType="java.util.Map"

<select id="findInfo" resultType="java.util.Map">
  select 
  * 
  from 
  users u left join usersinfo f on u.userid = f.uid
</select>

map中的键即为列名

List<Map<String,Object>> mlist = udao.findInfo();
for (Map<String, Object> s : mlist) {
  System.out.println( s.get("userId") + "\t" + s.get("uphone"));
}

3.4 多个条件来查询用户方案(1)

public Users findNameAndPwd_Map(Map<String,Object> mp)throws RuntimeException;

通过在Map集合中封装条件作为参数来去查询

<select id="findNameAndPwd_Map" resultType="uuu" parameterType="java.util.Map">
  select 
  * 
  from 
  users
  where
  uname=#{pn}
  and upwd=#{pd}    
</select>

上面的pn,pd为map的键,对应值为条件

Map<String,Object> m1 = new HashMap<String,Object>();
m1.put("pn", "张三");
m1.put("pd", "00");
Users tu = udao.findNameAndPwd_Map( m1 );
System.out.println("多个参数：Map\t"+tu);

3.5 多个条件来查询用户方案(2)

public Users findNameAndPwd_Users(Users mp)throws RuntimeException;

通过在一个Users对象中封装条件来查询

{}中的参数即为对应的get方法 首字母小写

<select id="findNameAndPwd_Users" resultType="uuu" parameterType="uuu">
  select 
  * 
  from 
  users
  where
  uname=#{uname}
  and upwd=#{upwd}  
</select>

3.5 多个条件来查询用户方案(3)

通过调用方法中参数的序列号来给定参数

public Users findNameAndPwd_param(String name,String pwd)throws RuntimeException;

{}中为方法参数的下标

<select id="findNameAndPwd_param" resultType="uuu">
  select 
  * 
  from 
  users
  where
  uname=#{0}
  and upwd=#{1} 
</select>

测试

tu = udao.findNameAndPwd_param( "张三","00" );
System.out.println("多个参数：序列\t"+tu);

3.6多个条件来查询用户方案(4)

通过接口中给参数定义注解

public Users findNameAndPwd_anno(@Param("a3cx") String name, @Param("bjyu") String pwd)throws RuntimeException;

{}中即为注解的名称

<select id="findNameAndPwd_anno" resultType="uuu">
  select 
  * 
  from 
  users
  where
  uname=#{a3cx}
  and upwd=#{bjyu}
</select>

测试

tu = udao.findNameAndPwd_anno( "张三","000" );
System.out.println("多个参数：注解\t"+tu);

3. 测试

InputStream is = TestDao.class.getResourceAsStream("/mybatis.cfg.xml");
//构建sqlSession的工厂
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);
//获取一个sqlSession
SqlSession session = sessionFactory.openSession();

4. MyBatis动态SQL

这次加上日志文件来测试

导入log4j.jar 在log4j.properties中配置

log4j.logger.org.apache.ibatis=debug

在mybatis.cfg.xml中引入log4j和类映射文件

<settings>
  <setting name="logImpl" value="log4j"/>
</settings>

<mappers>
  <mapper resource="com/senchen/mapper/UsersMapper.xml" />
</mappers>

4.1 公共字段include

如果sql语句中要查询的字段多且有多个sql语句重复使用,

可以用sql标签来封装这些字段,并配置id属性来方便使用

<sql id="xx001">
  userid,uname,uphone,utype
</sql>

使用的时候直接在sql语句中加入include,refid设置为sql标签的id值

<select id="findAll" resultType="uuu">
  select <include refid="xx001"/> from users
</select>
<select id="findInfo" resultType="java.util.Map">
  select 
  <include refid="xx001"/>,uemail,uhead 
  from 
  users u left join usersinfo f on u.userid = f.uid
</select>

4.2 动态SQL

public List<Users> search(Users u)throws RuntimeException;

用<where>标签来处理相当于sql中where 1=1

if来做判断字段是否为空来去添加条件

<select id="search" resultType="uuu" parameterType="uuu">
  select 
  <include refid="xx001"/>,uemail,uhead 
  from 
  users u left join usersinfo f on u.userid = f.uid
  <where> 
    <if test=" uname != null and uname !=''">
      and uname = #{uname}
    </if>
    <if test=" uphone != null and uphone !='' ">
      and uphone = #{uphone}
    </if>
  </where>
  <!-- 
    where 1=1   
    <if test=" uname != null and uname !=''">
     and uname = #{uname}
    </if>

    <if test=" uphone != null and uphone !='' ">
     and uphone = #{uphone}
    </if>
  -->       
</select>

测试

Users u1 = new Users();
u1.setUname("张三");
List<Users> ulist = udao.search(u1);
log.debug("第一次查询 \t" + ulist.size() );
log.debug("------------------第二次查询 只查 phone");
u1 = new Users();
u1.setUphone("131");
ulist = udao.search(u1);
log.debug("第二次查询 \t" + ulist.size() );
log.debug("------------------第三次查询 都查");
u1 = new Users();
u1.setUname("张三");
u1.setUphone("131");
ulist = udao.search(u1);
log.debug("第三次查询 \t" + ulist.size() );

4.3 修改用户

public void update(Users u) throws RuntimeException;

用update标签做修改,<set>标签来设置新的值<if>来判断参数是否为空

<!-- 修改时 set + if  -->
<update id="update" parameterType="uuu">
  update 
  users
  <set>
    <if test=" uname != null and uname !=''">
      uname=#{uname},
    </if>
    <if test=" upwd != null and upwd !=''">
      upwd=#{upwd},
    </if>
    <if test=" uhead != null and uhead !=''">
      uhead=#{uhead},
    </if>
    <if test=" uphone != null and uphone !=''">
      uphone=#{uphone},
    </if>
    <if test=" uemail != null and uemail !=''">
      uemail=#{uemail},
    </if>
    <if test=" utype != null and utype != 0">
      utype=#{utype},
    </if>
    <if test=" ustate != null and ustate != 0">
      ustate=#{ustate},
    </if>
  </set>    
  where
  userid=#{userId}   
</update>

测试修改

u1 = new Users();
u1.setUserId("AAA2");
u1.setUname("张三333");
u1.setUpwd("000");
udao.update( u1 );
session.commit();

4.4 用Map存一个数组形成条件去查

public List<Users> findByTypes(Map<String,Object> mp)throws RuntimeException;

通过<foreach>标签来遍历数组的值

<!-- for -->
<select id="findByTypes" resultType="uuu" parameterType="java.util.Map">
  select 
  * 
  from
  users
  where 1=1 <!--  and utype in(0, 50 ,100) -->
  <foreach collection="myTypes" item="xyz" index="index" open=" and utype in(" separator=","  close=")">
    #{xyz}
  </foreach>
</select>

测试查询

Map<String,Object> mp = new HashMap<String,Object>();
mp.put("myTypes", new String[]{"0,50,100"});
ulist = udao.findByTypes( mp );
©著作权归作者所有,转载或内容合作请联系作者
【社区内容提示】社区部分内容疑似由AI辅助生成，浏览时请结合常识与多方信息审慎甄别。
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

相关阅读更多精彩内容

友情链接更多精彩内容