动态SQL语句
我们一般实际应用的时候,sql语句都不会是简简单单的一个查询条件,都会是很多and、set、,之类的链接,然而很多时候并不知道数量,不知道边界值,这个时候就用到了Mybatis的技术了
Select、Update例子
User1Dao
import java.util.List;
import java.util.Map;
import domain.User;
import domain.UserVO;
public interface User1Dao {
public User findByIf(User user);
public User findByChoose(User user);
public User findByWhere(User user);
public User findByTrim(User user);
public List<User> findAll(List list);
public void updateUser(User user);
//public List<User> findLikeAll(String str);
public List<User> findLikeAll(Map<String, String> map);
}
Text03
public class Text03 {
public static void main(String[] args) {
// TODO 自动生成的方法存根
String resource="MybatisMainCof.xml";
InputStream is=Text01.class.getClassLoader().getResourceAsStream(resource);
SqlSessionFactory sessionFactory=new SqlSessionFactoryBuilder().build(is); //通过配置文件获取链接工厂
SqlSession sqlSession=sessionFactory.openSession(); //获取链接
/*User1Dao userDao=sqlSession.getMapper(User1Dao.class);
User user=new User();
user.setId("6");
User user1=userDao.findByIf(user);
System.out.println(user1);*/
/*User1Dao userDao=sqlSession.getMapper(User1Dao.class);
User user=new User();
// user.setId("7");
User user1=userDao.findByChoose(user);
System.out.println(user1);*/
/*User1Dao userDao=sqlSession.getMapper(User1Dao.class);
User user=new User();
user.setId("2");
User user1=userDao.findByWhere(user);
System.out.println(user1);*/
/*User1Dao userDao=sqlSession.getMapper(User1Dao.class);
User user=new User();
user.setId("7");
User user1=userDao.findByTrim(user);
System.out.println(user1);*/
/*User1Dao userDao=sqlSession.getMapper(User1Dao.class);
List list= new ArrayList<String>();
list.add("123");
list.add("456");
List<User> user1=userDao.findAll(list);
System.out.println(user1);*/
/*User1Dao userDao=sqlSession.getMapper(User1Dao.class);
User user=new User();
user.setId("1");
user.setUsername("qwer");
user.setPassword("789");
userDao.updateUser(user);
sqlSession.commit();
sqlSession.close();*/
User1Dao userDao=sqlSession.getMapper(User1Dao.class);
Map<String,String> map=new HashMap<String, String>();
String searchText = new StringBuilder("%").append("帅").append("%").toString();
map.put("name", searchText);
List<User> user1=userDao.findLikeAll(map);
System.out.println(user1);
}
}
JoinMapper.xml
<?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">
<mapper namespace="dao.User1Dao">
<select id="findByIf" parameterType="domain.User" resultType="domain.User">
select * from tb_user where 1=1
<if test="id!=null">
and password ="159" and id=#{id}
</if>
</select>
<select id="findByChoose" parameterType="domain.User" resultType="domain.User">
select * from tb_user where 1=1
<choose>
<when test="id!=null">
and password="123" and id=#{id}
</when>
<otherwise>
and id="1"
</otherwise>
</choose>
</select>
<select id="findByWhere" parameterType="domain.User" resultType="domain.User">
select * from tb_user
<where>
<if test="id!=null">
password ="456" and id=#{id}
</if>
</where>
</select>
<select id="findByTrim" parameterType="domain.User" resultType="domain.User">
select * from tb_user
<trim prefix="where" prefixOverrides="and|or">
<if test="id!=null">
and password ="123" and id=#{id}
</if>
</trim>
</select>
<select id="findAll" resultType="domain.User">
select * from tb_user where password in
<foreach collection="list" index="index" item="item" open="(" separator="," close=")">
<!-- collection可以填写list、map、array,如果是map的话 index是键,item是值 -->
#{item}
</foreach>
</select>
<!-- <update id="updateUser" parameterType="domain.User">
update tb_user
<trim prefix="set" suffixOverrides=",">
<if test="username!=null">username=#{username},</if>
<if test="password!=null">password=#{password},</if>
</trim>
where id=#{id}
</update>
-->
<update id="updateUser" parameterType="domain.User">
update tb_user
<set>
<if test="id!=null">password=#{password},</if>
<if test="id!=null">username=#{username},</if>
</set>
where id=#{id}
</update>
<select id="findLikeAll" resultType="domain.User" parameterType="java.util.Map">
<!-- select * from tb_user where username like " % " #{name} " %"
select * from tb_user where username like CONCAT(CONCAT('%',#{name},'%')) -->
select * from tb_user where username like #{name}
</select>
</mapper>
- SELECT 我们有IF、Choose、Where、Trim下面我分别介绍
SELECT
IF
<select id="findByIf" parameterType="domain.User" resultType="domain.User">
select * from tb_user where 1=1
<if test="id!=null">
and password ="159" and id=#{id}
</if>
</select>
- if很简单的一个用法,和我们JSTL的用法差不多,满足text的条件就加上,不满足就不管
Choose
<select id="findByChoose" parameterType="domain.User" resultType="domain.User">
select * from tb_user where 1=1
<choose>
<when test="id!=null">
and password="123" and id=#{id}
</when>
<otherwise>
and id="1"
</otherwise>
</choose>
</select>
- 这里我们看到了when以及otherwise
- 当满足when的时候就直接加上,否则就运行otherwise
Where
<select id="findByWhere" parameterType="domain.User" resultType="domain.User">
select * from tb_user
<where>
<if test="id!=null">
password ="456" and id=#{id}
</if>
</where>
</select>
- where的用法也很简单,但是我们需要注意的是,我们不需要另外添加where了,Mybatis自动帮我们添加好了
- 这里面一样和if什么的配合使用都是没问题的
Trim
<select id="findByTrim" parameterType="domain.User" resultType="domain.User">
select * from tb_user
<trim prefix="where" prefixOverrides="and|or">
<if test="id!=null">
and password ="123" and id=#{id}
</if>
</trim>
</select>
- trim是个很强大的标签,不仅仅可以适用于select,也适用于update以及其他
- 这里我们看到prefix (前缀)就是自动帮我们添加where
- prefixOverrides,这个就是自动帮我们忽略第一个and或者or,也就是你最前面写不写都不会出错
selectAll
<select id="findAll" resultType="domain.User">
select * from tb_user where password in
<foreach collection="list" index="index" item="item" open="(" separator="," close=")">
<!-- collection可以填写list、map、array,如果是map的话 index是键,item是值 -->
#{item}
</foreach>
</select>
- 代码中有注解,就不过多解释了
小结
- 这里的小结很简单,就是我们看到前面都有一个 where 1=1 这个的作用就是忽略第一个条件,无论第一个是什么 这个都是为真,所以后面我们只需要拼接and即可
UPDATE
set
<update id="updateUser" parameterType="domain.User">
update tb_user
<set>
<if test="id!=null">password=#{password},</if>
<if test="id!=null">username=#{username},</if>
</set>
where id=#{id}
</update>
- 这里也不过多解释,和前面的where差不多的用法
trim
<update id="updateUser" parameterType="domain.User">
update tb_user
<trim prefix="set" suffixOverrides=",">
<if test="username!=null">username=#{username},</if>
<if test="password!=null">password=#{password},</if>
</trim>
where id=#{id}
</update>
- 这里一样和前面的用法差不多
小结
- 这里注意,使用update一定要提交事务,不然数据库不会更新
- 这个代码自己多打几次就会了,很简单