mybatis的基本使用
源代码地址:https://github.com/Zou2021/mybatisDemo
1、准备数据库中的表及初始数据
CREATE DATABASE /*!32312 IF NOT EXISTS*/`test` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `test`;
/*Table structure for table `user` */
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
`age` int(8) DEFAULT NULL,
`sex` varchar(2) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
/*Data for the table `user` */
insert into `user`(`id`,`name`,`age`,`sex`) values (1,'zou',18,'男'),(2,'ming',20,'男'),(3,'Maria',21,'女');
2、新建一个maven工程
3、在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>mybatisDemo</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
</properties>
<dependencies>
<!--Junit单元测试依赖-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
<scope>test</scope>
</dependency>
<!--mysql驱动依赖-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>6.0.5</version>
</dependency>
<!--mybatis依赖-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.6</version>
</dependency>
<!--log4j日志依赖-->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<!--lombok依赖-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.20</version>
</dependency>
</dependencies>
<build>
<resources>
<!--编译mapper的xml文件-->
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
</resource>
</resources>
</build>
</project>
4、编写实体类
package com.zou.entity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
/**
* @author: 邹祥发
* @date: 2021/4/23 20:45
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class User {
private Integer id;
private String name;
private Integer age;
private String sex;
}
5、编写UserMapper接口类
package com.zou.mapper;
import com.zou.entity.User;
import java.util.List;
/**
* @author: 邹祥发
* @date: 2021/4/23 20:49
*/
public interface UserMapper {
List<User> selectList() throws Exception;
List<User> selectlike01() throws Exception;
List<User> selectlike02(String name) throws Exception;
List<User> selectlike03(User user) throws Exception;
void insert(User user) throws Exception;
List<User> sqlSelect01(User user) throws Exception;
List<User> sqlSelect02(User user) throws Exception;
List<User> sqlSelect03(User user) throws Exception;
}
6、编写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.zou.mapper.UserMapper">
<select id="selectList" resultType="com.zou.entity.User">
select *
from user
</select>
<select id="selectlike01" resultType="com.zou.entity.User">
select *
from user
where name like '%i%'
</select>
<select id="selectlike02" parameterType="java.lang.String" resultType="com.zou.entity.User">
select *
from user
where name like #{name}
</select>
<select id="selectlike03" parameterType="com.zou.entity.User" resultType="com.zou.entity.User">
select *
from user
where name like '%${name}%'
</select>
<insert id="insert" useGeneratedKeys="true" keyProperty="id"
parameterType="com.zou.entity.User">
insert into user(name, age, sex)
values (#{name}, #{age}, #{sex})
</insert>
<!--动态sql -->
<!-- 不使用动态sql -->
<select id="sqlSelect01" parameterType="com.zou.entity.User"
resultType="com.zou.entity.User">
select *
from user
where name = #{name}
or age = #{age}
</select>
<select id="sqlSelect02" parameterType="com.zou.entity.User"
resultType="com.zou.entity.User">
select *from user where
<if test="name!=null">
name=#{name}
</if>
<if test="sex!=null">
or sex=#{sex}
</if>
</select>
<select id="sqlSelect03" parameterType="com.zou.entity.User"
resultType="com.zou.entity.User">
select *from user
<where>
<if test="name!=null">
name=#{name}
</if>
<if test="sex!=null">
or sex=#{sex}
</if>
</where>
</select>
</mapper>
7、加入log4j日志输出文件及配置mybatis连接数据库
log4j.properties
# Global logging configuration
log4j.rootLogger=DEBUG, stdout
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
mybatis-config.xml
注:因为我用的是mysql6版本,有以下两个易错点
<?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/test?serverTimezone=UTC"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
<!-- 加入映射 -->
<mappers>
<mapper resource="com/zou/mapper/UserMapper.xml"/>
</mappers>
</configuration>
8、初始化sqlsession
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;
/**
* @author: 邹祥发
* @date: 2021/4/23 21:06
*/
public class InitSession {
public static SqlSession getSession(String config) throws IOException {
InputStream inputStream = Resources.getResourceAsStream(config);
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
return sessionFactory.openSession(true);
}
}
9、编写测试类
测试一:查询所有数据
import com.zou.entity.User;
import com.zou.mapper.UserMapper;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.io.IOException;
import java.util.List;
/**
* @author: 邹祥发
* @date: 2021/4/23 21:08
*/
public class MyTest {
private final SqlSession session = InitSession.getSession("mybatis-config.xml");
public MyTest() throws IOException {
}
/**
* 查询所有数据
*/
@Test
public void selectListTest() throws Exception {
UserMapper mapper = session.getMapper(UserMapper.class);
List<User> users = mapper.selectList();
for (User user : users){
System.out.println(user);
}
}
}
注:下列测试注意观察SQL语句及参数的值
测试二:模糊查询1
查询name中带有i的所有数据
/**
* 模糊查询1
*/
@Test
public void selectLikeTest01() throws Exception {
UserMapper mapper = session.getMapper(UserMapper.class);
List<User> users = mapper.selectlike01();
for (User user : users){
System.out.println(user);
}
}
测试三:模糊查询2
查询name中带有i的所有数据
/**
* 模糊查询2
*/
@Test
public void selectLikeTest02() throws Exception {
UserMapper mapper = session.getMapper(UserMapper.class);
List<User> users = mapper.selectlike02("%i%");
for (User user : users){
System.out.println(user);
}
}
测试四:模糊查询3
查询name中带有zou的所有数据
/**
* 模糊查询3
*/
@Test
public void selectLikeTest03() throws Exception {
UserMapper mapper = session.getMapper(UserMapper.class);
List<User> users = mapper.selectlike03(new User(1,"zou",18,"男"));
for (User user : users){
System.out.println(user);
}
}
测试五:插入并获取主键的值
/**
* 插入并获得主键id的值
*/
@Test
public void insertTest() throws Exception {
UserMapper mapper = session.getMapper(UserMapper.class);
User user = new User(11,"jack",25,"男");
mapper.insert(user);
System.out.println(user.getId());
}
测试六:关键词or的使用
查询name为zou 或者 age=21的所有数据
/**
* or 的使用
* 查询名字为zou 或者 年龄为21的数据
*/
@Test
public void orTest() throws Exception {
UserMapper mapper = session.getMapper(UserMapper.class);
List<User> users = mapper.sqlServlet01(new User(1, "zou", 21, "男"));
for (User user : users){
System.out.println(user);
}
}
测试七:mybatis中if标签的基本使用
对应的SQL语句配置文件:
<select id="sqlServlet02" parameterType="com.zou.entity.User"
resultType="com.zou.entity.User">
select *from user where
<if test="name!=null">
name=#{name}
</if>
<if test="sex!=null">
or sex=#{sex}
</if>
</select>
测试八:mybatis中if标签嵌套where标签的基本使用
对应的SQL语句配置文件:
<select id="sqlServlet03" parameterType="com.zou.entity.User"
resultType="com.zou.entity.User">
select *from user
<where>
<if test="name!=null">
name=#{name}
</if>
<if test="sex!=null">
or sex=#{sex}
</if>
</where>
</select>