set标签的使用
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>org.example</groupId>
<artifactId>mybatis02</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.6</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.28</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.24</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
</dependencies>
</project>
mybatis.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/mybatis?serverTimezone=Asia/Shanghai" />
<property name="username" value="root" />
<property name="password" value="**********" />
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="mapper/UserMapper.xml"/>
<mapper resource="mapper/StudentMapper.xml"/>
<mapper resource="mapper/AccountMapper.xml"/>
</mappers>
</configuration>
log4j.properties添加日志
log4j.rootLogger=DEBUG, Console
#Console
log4j.appender.Console=org.apache.log4j.ConsoleAppender
log4j.appender.Console.layout=org.apache.log4j.PatternLayout
log4j.appender.Console.layout.ConversionPattern=%d [%t] %-5p [%c] - %m%n
log4j.logger.java.sql.ResultSet=INFO
log4j.logger.org.apache=INFO
log4j.logger.java.sql.Connection=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG
Account
package com.zsb.entity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.Date;
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Account {
private Integer id;
private String name;
private Double money;
private Integer isdeleted;
private Date created;
private Date updated;
}
AccountDao
package com.zsb.dao;
import com.zsb.entity.Account;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface AccountDao {
public List<Account> findByCondition(@Param("name")String name,@Param("money")Double money);
public List<Account> findByCondition02(@Param("name")String name,@Param("money")Double money);
public void update(Account account);
}
AccountMapper
<?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.zsb.dao.AccountDao">
<update id="update">
update account
<set>
<if test="name!=null and name!=''">
name =#{name},
</if>
<if test="money!=null">
money=#{money},
</if>
<if test="isdeleted!=null">
isdeleted=#{isdeleted},
</if>
<if test="created!=null">
created=#{created},
</if>
<if test="updated!=null">
updated=#{updated}
</if>
</set>
where id=#{id}
</update>
<!-- <select id="findByCondition" resultType="com.zsb.entity.Account">-->
<!-- select * from account where name =#{name}-->
<!-- </select>-->
<select id="findByCondition" resultType="com.zsb.entity.Account">
select * from account where 1=1
<if test="name!=null and name!=''">
and name=#{name}
</if>
<if test="money!=null">
and money=#{money}
</if>
</select>
<!-- <select id="findByCondition" resultType="com.zsb.entity.Account">-->
<!-- select * from account-->
<!-- <where>-->
<!-- <if test="name!=null and name!=''">-->
<!-- and name=#{name}-->
<!-- </if>-->
<!-- <if test="money!=null">-->
<!-- and money=#{money}-->
<!-- </if>-->
<!-- </where>-->
<!-- </select>-->
<select id="findByCondition02" resultType="com.zsb.entity.Account">
<choose>
<when test="name!=null and name!=''">
and name=#{name}
</when>
<when test="money!=null">
and money=#{money}
</when>
<otherwise>
and isdeleted=0
</otherwise>
</choose>
</select>
</mapper>
Test测试
package com.zsb.test;
import com.zsb.dao.AccountDao;
import com.zsb.dao.StudentDao;
import com.zsb.entity.Account;
import com.zsb.entity.Student;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import java.io.Reader;
import java.util.Date;
import java.util.List;
public class Test04 {
@Test
public void testUpdate() throws Exception{
Reader reader = Resources.getResourceAsReader("mybatis.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
SqlSession session = factory.openSession();
AccountDao accountDao = session.getMapper(AccountDao.class);
Account account = new Account();
account.setId(1);
account.setCreated(new Date());
accountDao.update(account);
session.commit();
session.close();
}
@Test
public void testFindOne02() throws Exception{
Reader reader = Resources.getResourceAsReader("mybatis.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
SqlSession session = factory.openSession();
//获取接口的映射类
AccountDao accountDao = session.getMapper(AccountDao.class);
List<Account> abc = accountDao.findByCondition("",null);
session.commit();
session.close();
}
@Test
public void testFindOne() throws Exception{
Reader reader = Resources.getResourceAsReader("mybatis.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
SqlSession session = factory.openSession();
//获取接口的映射类
AccountDao accountDao = session.getMapper(AccountDao.class);
List<Account> abc = accountDao.findByCondition(null,null);
session.commit();
session.close();
}
}
foreach标签
AccountDao
package com.zsb.dao;
import com.zsb.entity.Account;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface AccountDao {
public List<Account> findByIds(Integer[] ids);
}
AccountMypper
<?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.zsb.dao.AccountDao">
<select id="findByIds" resultType="com.zsb.entity.Account">
select * from account where id in
<foreach collection="array" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</select>
</mapper>
Test
@Test
public void testFindByids() throws Exception{
Reader reader = Resources.getResourceAsReader("mybatis.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
SqlSession session = factory.openSession();
AccountDao accountDao = session.getMapper(AccountDao.class);
Integer[] ids={3,5,6,8,9};
List<Account> byIds = accountDao.findByIds(ids);
System.out.println(byIds);
session.commit();
session.close();
}
sql片段
<sql id="baseColumn">
id,name,money,isdeleted,created,updated
</sql>
<select id="findByCondition" resultType="com.zsb.entity.Account">
select
<include refid="baseColumn"/>
from account
<where>
<if test="name!=null and name!=''">
and name =#{name}
</if>
<if test="money!=null">
and money=#{money}
</if>
</where>
</select>
mybatis映射文件处理特殊字符
第一种:转义标签 <
<select id="findByMaxmin" resultType="com.zsb.entity.Account">
select * from account where id >#{min} and id <#{max}
</select>
第二种: <![CDATA[sql]]>
<select id="findByMaxmin" resultType="com.zsb.entity.Account">
<![CDATA[select * from account where id >#{min} and id <#{max}]]>
</select>
mybatis完成模糊查询
AccountDao
package com.zsb.dao;
import com.zsb.entity.Account;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface AccountDao {
public List<Account> findByLike(String name);
}
1)使用字符串函数完成拼接concat
<select id="findByLike" resultType="com.zsb.entity.Account">
select * from account where name like concat('%',#{name},'%')
</select>
2)使用${}
<select id="findByLike" resultType="com.zsb.entity.Account">
select * from account where name like '%{name}%'
</select>
Test测试
@Test
public void testLike() throws Exception{
Reader reader = Resources.getResourceAsReader("mybatis.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
SqlSession session = factory.openSession();
AccountDao accountDao = session.getMapper(AccountDao.class);
List<Account> byMaxMin = accountDao.findByLike("s");
System.out.println(byMaxMin);
session.commit();
session.close();
}
使用${}实际上是字符串拼接,它不能防止sql注入, 而#{}它是预编译,它可以防止sql注入问题,#{}实际使用的PreparedStatement.
MyBatis中#{}和${}的区别是什么?
\#{}是预编译处理,${}是字符替换
在使用 #{}时, MyBatis会将 SQL中的#{}替换成"?”,配合 PreparedStatement 的set方法赋值,这样可以有效的防止SQL注入,保证程序的运行安全。