MyBatis重点是对sql的灵活解析和处理
MyBatis项目构建:https://blog.csdn.net/young_1004/article/details/81987259
1、if + where
(如果name为null的话按id==1去查询)
<select id="findUserByName" parameterType="String" resultType="user">
select * from user
<where>
<if test="name==null">
id=1
</if>
</where>
</select>
测试
@Test
/**
* 通过NAME查询用户
*/
public void findUserByName() throws IOException {
InputStream cof = Resources.getResourceAsStream("sqlMapConfig.xml");
//创建会话工厂
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(cof);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = mapper.findUserByName(null);
System.out.println(user);
sqlSession.close();
}
数据库
效果
2、foreach
foreach元素的属性主要有 item,index,collection,open,separator,close。
-
item表示集合中每一个元素进行迭代时的别名,
-
index指定一个名字,用于表示在迭代过程中,每次迭代到的位置,
-
open表示该语句以什么开始,
-
separator表示在每次进行迭代之间以什么符号作为分隔符,
-
close表示以什么结束
UserMapper.java
public List<User> findUsers(UserCustom userCustom);
定义集合
package com.ma.mybatis.pojo;
import java.util.List;
public class UserCustom extends User {
private List<Integer> ids;
public List<Integer> getIds() {
return ids;
}
public void setIds(List<Integer> ids) {
this.ids = ids;
}
@Override
public String toString() {
return "UserCustom{" +
"ids=" + ids +
'}';
}
}
配置文件
<select id="findUsers" parameterType="com.ma.mybatis.pojo.UserCustom" resultType="com.ma.mybatis.pojo.User">
select * from user
<where>
<foreach collection="ids" item="id" open="id in(" separator="," close=")">
#{id}
</foreach>
</where>
</select>
测试
@Test
/**
* 通过多个ID查询用户
*/
public void findUsers() throws IOException {
InputStream cof = Resources.getResourceAsStream("sqlMapConfig.xml");
//创建会话工厂
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(cof);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
UserCustom userCustom = new UserCustom();
userCustom.setIds(Arrays.asList(1,2,3));
List<User> users = mapper.findUsers(userCustom);
for (User user:users ){
System.out.println(user);
}
sqlSession.close();
}
数据库
效果
3、if + set + where语句
UserMapper.java
public void updateUserName(User user);
配置文件
<update id="updateUserName" parameterType="user">
update user
<set>
<if test="name != null">
name = #{name }
</if>
</set>
<where>
id=#{id}
</where>
</update>
测试
@Test
/**
* 更改用户名
*/
public void updateUserNmae() throws IOException {
InputStream cof = Resources.getResourceAsStream("sqlMapConfig.xml");
//创建会话工厂
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(cof);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setId(1);
user.setName("Jack");
mapper.updateUserName(user);
sqlSession.close();
}
数据库:
效果
4、choose+while+otherwise
<choose>
<when test="条件">
sql语句
</when>
<when test="条件">
sql语句
</when>
<otherwise>
sql语句
</otherwise>
</choose>