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語(yǔ)句完整 -->
<!-- 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中有語(yǔ)句滿(mǎn)足链瓦，那么會(huì)自動(dòng)加上where并且會(huì)自動(dòng)去掉第一個(gè)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>
<!-- 只要滿(mǎn)足一個(gè)when那么后面的就不會(huì)再判斷執(zhí)行了; and 1=2的作用是：當(dāng)上面的條件不滿(mǎn)足時(shí),不再查詢(xún)-->
<!-- 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專(zhuān)用于更新數(shù)據(jù);set標(biāo)簽下的條件判斷如果滿(mǎn)足，那么就會(huì)更新該字段數(shù)據(jù) -->
<!-- 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"表示來(lái)的參數(shù)為數(shù)組（有[]）;值為list,表示來(lái)的參數(shù)是個(gè)list集合類(lèi)型 -->
<!--
item:遍歷的每個(gè)成員名
separator:成員之間的分割
open：開(kāi)始符號(hào)
close：結(jié)束符號(hào)
如(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>
<!-- 會(huì)根據(jù)我們對(duì)象屬性的值來(lái)動(dòng)態(tài)創(chuàng)建sql -->
<!-- trim:
prefix:在sql語(yǔ)句首添加
prefixOverrides:在sql語(yǔ)句首刪除
suffix:在sql語(yǔ)句末添加
suffixOverrides:在sql語(yǔ)句末刪除
-->
</mapper>
類(lèi)
public class UserDao {
SqlSession session = null;
// 使用if動(dòng)態(tài)生成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動(dòng)態(tài)生成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動(dòng)態(tài)生成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動(dòng)態(tài)生成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動(dòng)態(tài)生成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動(dòng)態(tài)生成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("受影響行數(shù)："+i);
}
}
@Data
public class User {
private int userId;
private String userName;
private int userAge;
}
補(bǔ)充：輸出mybatis中的sql語(yǔ)句
1. 導(dǎo)入jar包
Paste_Image.png
2. 創(chuàng)建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
