myBatis-conf.xml
Paste_Image.png
userMapper.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="com.xxjqr.dyn_sql.userMapper">
<resultMap type="User" id="userResultMap">
<id column="user_id" property="userId"/>
<result column="user_name" property="userName"/>
<result column="user_age" property="userAge"/>
</resultMap>
<!-- if -->
<select id="selectListUseIf" parameterType="User" resultMap="userResultMap" >
select user_id,user_name,user_age from user_t where 1=1
<if test="userId!=null">and user_id = #{userId}</if>
<if test="userName!=null">and user_name = #{userName}</if>
<if test="userAge!=0">and user_age = #{userAge}</if>
</select>
<!-- 1=1是为了让sql语句完整 -->
<!-- where if -->
<select id="selectListUseWhere" parameterType="User" resultMap="userResultMap" >
select user_id,user_name,user_age from user_t
<where>
<if test="userId!=null">and user_id = #{userId}</if>
<if test="userName!=null">and user_name = #{userName}</if>
<if test="userAge!=0">and user_age = #{userAge}</if>
</where>
</select>
<!-- 如果if中有语句满足，那么会自动加上where并且会自动去掉第一个and -->
<!-- where choose when otherwise -->
<select id="selectListUseChoose" parameterType="User" resultMap="userResultMap" >
select user_id,user_name,user_age from user_t
<where>
<choose>
<when test="userId!=null">and user_id = #{userId}</when>
<when test="userName!=null">and user_name = #{userName}</when>
<when test="userAge!=0">and user_age = #{userAge}</when>
<otherwise>and 1=2</otherwise>
</choose>
</where>
</select>
<!-- 只要满足一个when那么后面的就不会再判断执行了; and 1=2的作用是：当上面的条件不满足时,不再查询-->
<!-- set -->
<update id="updateUserUseSet" parameterType="User" >
update user_t
<set>
<if test="userName!=null">user_name = #{userName},</if>
<if test="userAge!=null">user_age=#{userAge},</if>
</set>
where user_id = #{userId}
</update>
<!-- set专用于更新数据;set标签下的条件判断如果满足，那么就会更新该字段数据 -->
<!-- foreach -->
<select id="selectListUseForeach" resultMap="userResultMap">
select * from user_t where user_id in
<foreach collection="list" item="userId" separator="," open="(" close=")">
#{userId} <!-- 这里要等于item的值 -->
</foreach>
</select>
<!--collection的值为"array"表示来的参数为数组（有[]）;值为list,表示来的参数是个list集合类型 -->
<!--
item:遍历的每个成员名
separator:成员之间的分割
open：开始符号
close：结束符号
如(1,2,3)
-->
<!-- include -->
<insert id="insertUserUseInclude" parameterType="User">
insert into user_t
<include refid="key"></include>
values
<include refid="value"></include>
</insert>
<sql id="key">
<trim suffixOverrides="," prefix="(" suffix=")">
<if test="userName!=null">user_name,</if>
<if test="userAge!=0">user_age,</if>
</trim>
</sql>
<sql id="value">
<trim suffixOverrides="," prefix="(" suffix=")"><!-- 先删除,再加) -->
<if test="userName!=null">#{userName},</if>
<if test="userAge!=0">#{userAge},</if>
</trim>
</sql>
<!-- 会根据我们对象属性的值来动态创建sql -->
<!-- trim:
prefix:在sql语句首添加
prefixOverrides:在sql语句首删除
suffix:在sql语句末添加
suffixOverrides:在sql语句末删除
-->
</mapper>
类
public class UserDao {
SqlSession session = null;
// 使用if动态生成sql
public List<User> selectListUseIf(User user) {
List<User> users = null;
try {
session = MybatisSessionFactory.getSession();
users = session.selectList(
"com.xxjqr.dyn_sql.userMapper.selectListUseIf", user);
session.commit();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
MybatisSessionFactory.closeSession();
} catch (Exception e) {
e.printStackTrace();
}
}
return users;
}
// 使用where动态生成sql
public List<User> selectListUseWhere(User user) {
List<User> users = null;
try {
session = MybatisSessionFactory.getSession();
users = session.selectList(
"com.xxjqr.dyn_sql.userMapper.selectListUseWhere", user);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
MybatisSessionFactory.closeSession();
} catch (Exception e) {
e.printStackTrace();
}
}
return users;
}
// 使用choose动态生成sql
public List<User> selectListUseChoose(User user) {
List<User> users = null;
try {
session = MybatisSessionFactory.getSession();
users = session.selectList(
"com.xxjqr.dyn_sql.userMapper.selectListUseChoose", user);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
MybatisSessionFactory.closeSession();
} catch (Exception e) {
e.printStackTrace();
}
}
return users;
}
// 使用Set动态生成sql
public int updateUserUseChoose(User user) {
int i =0;
try {
session = MybatisSessionFactory.getSession();
i = session.update("com.xxjqr.dyn_sql.userMapper.updateUserUseSet", user);
session.commit();
} catch (Exception e) {
e.printStackTrace();
session.rollback();
} finally {
try {
MybatisSessionFactory.closeSession();
session.rollback();
} catch (Exception e) {
e.printStackTrace();
}
}
return i;
}
// 使用foreach动态生成sql
public List<User> selectListUseForeach(List<Integer> userIds) {
List<User> users = null;
try {
session = MybatisSessionFactory.getSession();
users = session.selectList(
"com.xxjqr.dyn_sql.userMapper.selectListUseForeach", userIds);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
MybatisSessionFactory.closeSession();
} catch (Exception e) {
e.printStackTrace();
}
}
return users;
}
// 使用include动态生成sql
public int insertUserUseInclude(User user) {
int i =0;
try {
session = MybatisSessionFactory.getSession();
i = session.insert("com.xxjqr.dyn_sql.userMapper.insertUserUseInclude", user);
session.commit();
} catch (Exception e) {
e.printStackTrace();
session.rollback();
} finally {
try {
MybatisSessionFactory.closeSession();
} catch (Exception e) {
e.printStackTrace();
}
}
return i;
}
}
public class TestUserDao {
private UserDao userDao = new UserDao();
@Test
public void test() {
User user = new User();
int i = 0;
List<User> list = null;
// user.setUserId(1);
// list= userDao.selectListUseIf(user);
// list = userDao.selectListUseWhere(user);
// list = userDao.selectListUseChoose(user);
// List<Integer> queryList = new ArrayList<Integer>();
// queryList.add(1);
// queryList.add(15);
// queryList.add(16);
// list = userDao.selectListUseForeach(queryList);
// System.out.println(list);
// user.setUserAge(121);
// user.setUserName("丁老头");
// i = userDao.updateUserUseChoose(user);
user.setUserAge(232);
user.setUserName("张三丰");
i = userDao.insertUserUseInclude(user);
System.out.println("受影响行数："+i);
}
}
@Data
public class User {
private int userId;
private String userName;
private int userAge;
}
补充：输出mybatis中的sql语句
1. 导入jar包
Paste_Image.png
2. 创建log4j.properties文件放在src下
# Rules reminder:
# DEBUG < INFO < WARN < ERROR < FATAL
# Global logging configuration
log4j.rootLogger=debug,stdout
# My logging configuration...
log4j.logger.cn.jbit.mybatisdemo=DEBUG
## Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p %d %C: %m%n
log4j.logger.org.apache.ibatis=DEBUG
## log4j.logger.org.apache.jdbc.SimpleDataSource=DEBUG
log4j.logger.org.apache.ibatis.jdbc.ScriptRunner=DEBUG
## log4j.logger.com.ibatis.sqlmap.engine.impl.SqlMapclientDelegate=DEBUG
log4j.logger.java.sql.Connection=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG
