项目结构
$ tree . ├── README.md ├── pom.xml └── src ├── main │ ├── java │ │ └── com │ │ └── mouday │ │ ├── mapper │ │ │ ├── PersonMapper.java │ │ │ └── PersonMapper.xml │ │ ├── pojo │ │ │ └── Person.java │ │ └── util │ │ └── MyBatisUtil.java │ └── resources │ ├── db.properties │ ├── mybatis-config.xml │ └── sql │ └── person.sql └── test └── java └── com └── mouday └── PersonTest.java
依赖 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>com.mouday</groupId> <artifactId>mybatis-demo</artifactId> <version>1.0-SNAPSHOT</version> <build> <resources> <!--编译src/main/java目录下的xml文件--> <resource> <directory>src/main/java</directory> <includes> <include>**/*.xml</include> </includes> <filtering>true</filtering> </resource> </resources> </build> <dependencies> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.5.5</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.20</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.12</version> <scope>provided</scope> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.11</version> <scope>test</scope> </dependency> </dependencies> </project>
数据库配置
db.properties
driver=com.mysql.cj.jdbc.Driver url=jdbc:mysql://localhost:3306/data username=root password=123456
MyBatis配置 mybatis-config.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> <!-- 加载数据库配置 --> <properties resource="db.properties" /> <!-- 打印sql日志 --> <settings> <setting name="logImpl" value="STDOUT_LOGGING"/> </settings> <!-- 指定整个包下的类都是别名 --> <typeAliases> <package name="com.mouday.pojo"/> </typeAliases> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="${driver}"/> <property name="url" value="${url}"/> <property name="username" value="${username}"/> <property name="password" value="${password}"/> </dataSource> </environment> </environments> <mappers> <mapper class="com.mouday.mapper.PersonMapper"/> </mappers> </configuration>
建表插入数据 person.sql
-- 建表 create table person( id int not null primary key auto_increment, name varchar(20), age int ); -- 插入数据 insert into person(name, age) values('刘备', 25); insert into person(name, age) values('关羽', 24); insert into person(name, age) values('张飞', 23); insert into person(name, age) values('曹操', 25); insert into person(name, age) values('许褚', 24); insert into person(name, age) values('孙权', 25); insert into person(name, age) values('周瑜', 24);
工具类 MyBatisUtil.java
package com.mouday.util; 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 java.io.IOException; import java.io.InputStream; public class MyBatisUtil { private static SqlSessionFactory factory = null; // 使用static静态代码块，随着类的加载而加载，只执行一次 static { try { String resource = "mybatis-config.xml"; // 加载MyBatis的主配置文件 InputStream inputStream = Resources.getResourceAsStream(resource); // 通过构建器（SqlSessionFactoryBuilder）构建一个SqlSessionFactory工厂对象 factory = new SqlSessionFactoryBuilder().build(inputStream); } catch (Exception e) { e.printStackTrace(); } } public static SqlSession getSqlSession() throws IOException { return factory.openSession(); } }
实体Person.java
package com.mouday.pojo; import lombok.Getter; import lombok.Setter; @Setter @Getter public class Person { private Integer id; private String name; private Integer age; }
mapper接口 PersonMapper.java
package com.mouday.mapper; import com.mouday.pojo.Person; import java.util.List; public interface PersonMapper { List<Person> selectAll(); /** * 根据输入的信息进行条件检索 * 1. 当只输入用户名时， 使用用户名进行 【模糊检索】 * 2. 当只输入年龄时， 使用性别进行 【完全匹配】 * 3. 当用户名和年龄都存在时， 用这两个条件进行查询匹配的用 */ List<Person> selectByPersonSelective(Person person); /** * 更新非空属性 */ int updateByPrimaryKeySelective(Person person); /** * 插入非空字段 */ int insertSelective(Person person); /** * 当 name 没有值时， 使用 name 进行查询 * 否则使用 id 进行查询 */ List<Person> selectByNameOrId(Person person); }
mapper映射文件 PersonMapper.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.mouday.mapper.PersonMapper"> <sql id="Base_Column_List"> id, name, age </sql> <select id="selectAll" resultType="Person"> select <include refid="Base_Column_List"/> from person </select> <select id="selectByPersonSelective" resultType="Person" parameterType="Person"> select <include refid="Base_Column_List" /> from person <where> <if test="name != null and name !=''"> and name like concat('%', #{name}, '%') </if> <if test="age != null"> and age=#{age} </if> </where> </select> <update id="updateByPrimaryKeySelective" parameterType="Person"> update person <set> <if test="name != null"> `name` = #{name,jdbcType=VARCHAR}, </if> <if test="age != null"> `age` = #{age,jdbcType=INTEGER}, </if> </set> where id = #{id,jdbcType=INTEGER} </update> <insert id="insertSelective" parameterType="Person"> insert into person <trim prefix="(" suffix=")" suffixOverrides=","> <if test="id != null"> id, </if> <if test="name != null"> `name`, </if> <if test="age != null"> age, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="id != null"> #{id,jdbcType=INTEGER}, </if> <if test="name != null"> #{name,jdbcType=VARCHAR}, </if> <if test="age != null"> #{age,jdbcType=INTEGER}, </if> </trim> </insert> <select id="selectByNameOrId" resultType="Person" parameterType="Person"> select <include refid="Base_Column_List" /> from person where 1=1 <choose> <when test="id != null"> and id=#{id} </when> <otherwise> and name=#{name} </otherwise> </choose> </select> </mapper>
测试文件PersonTest.java
package com.mouday; import com.mouday.mapper.PersonMapper; import com.mouday.pojo.Person; import com.mouday.util.MyBatisUtil; import org.apache.ibatis.session.SqlSession; import org.junit.After; import org.junit.Before; import org.junit.Test; import java.io.IOException; public class PersonTest { private SqlSession session; private PersonMapper mapper; @Before public void init() throws IOException { this.session = MyBatisUtil.getSqlSession(); this.mapper = this.session.getMapper(PersonMapper.class); } @After public void destroy() { this.session.close(); } @Test public void testSelect() { System.out.println(mapper.selectAll()); } /** * 选择数据 */ @Test public void testSelectByStudentSelective() { Person person = new Person(); System.out.println(mapper.selectByPersonSelective(person)); // select id, name, age from person person.setName("操"); System.out.println(mapper.selectByPersonSelective(person)); // select id, name, age from person WHERE name like concat('%', ?, '%') person.setAge(25); System.out.println(mapper.selectByPersonSelective(person)); // select id, name, age from person WHERE name like concat('%', ?, '%') and age=? } /** * 更新数据 */ @Test public void testUpdateByPrimaryKeySelective() { Person person = new Person(); person.setId(1); person.setAge(26); mapper.updateByPrimaryKeySelective(person); // update person SET `age` = ? where id = ? session.commit(); person.setName("刘禅"); mapper.updateByPrimaryKeySelective(person); session.commit(); // update person SET `name` = ?, `age` = ? where id = ? } /** * 插入数据 */ @Test public void testInsertSelective() { Person person = new Person(); person.setName("司马懿"); mapper.insertSelective(person); // insert into person ( `name` ) values ( ? ) session.commit(); person.setAge(26); mapper.insertSelective(person); // insert into person ( `name`, age ) values ( ?, ? ) session.commit(); } /** * 选择查询 */ @Test public void testSelectByNameOrId() { Person person = new Person(); person.setName("司马懿"); mapper.selectByNameOrId(person); // select id, name, age from person where 1=1 and name=? person.setId(1); mapper.selectByNameOrId(person); // select id, name, age from person where 1=1 and id=? } }
Where
set 和 where 其实都是 trim 标签的一种类型
where 等价于
<trim prefix="where" prefixOverrides="AND |OR"> </trim>
表示当 trim 中含有内容时， 添加 where， 且第一个为 and 或 or 时， 会将其去掉。
而如果没有内容， 则不添加 where。
set 等价于
<trim prefix="SET" suffixOverrides=","> ... </trim>
表示当 trim 中含有内容时， 添加 set， 且最后的内容为 , 时， 会将其去掉。而没有内容， 不添加 set
trim 的几个属性
prefix: 当 trim 元素包含有内容时， 增加 prefix 所指定的前缀 prefixOverrides: 当 trim 元素包含有内容时， 去除 prefixOverrides 指定的 前缀 suffix: 当 trim 元素包含有内容时， 增加 suffix 所指定的后缀 suffixOverrides：当 trim 元素包含有内容时， 去除 suffixOverrides 指定的后缀
参考
