Mybatis
的3.5以后的版本支持 Optional,注解不再支持通用的注解(容易引起歧义性),而是更有针对性
。
项目采用Maven实现,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>mybatis-001</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<!--mybatis核心jar-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.9</version>
</dependency>
<!--mysql数据库:注意这里使用的8.*版本
driver:com.mysql.cj.jdbc.Driver
-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.25</version>
<scope>provided</scope>
</dependency>
<!--日志 start-->
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-classic</artifactId>
<version>1.2.11</version>
</dependency>
<!--日志end-->
<!--junit单元测试-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<!--lombok-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.16</version>
<scope>provided</scope>
</dependency>
</dependencies>
<build>
<resources>
<resource>
<directory>${project.basedir}/src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
</resource>
<resource>
<directory>${project.basedir}/src/main/resources</directory>
</resource>
</resources>
</build>
</project>
SqlSessionFactory工具类:
package com.song.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 org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.IOException;
public class MyBatisUtil {
private MyBatisUtil() { }
private static SqlSession session;
private static SqlSessionFactory sessionFactory;
private static Logger logger= LoggerFactory.getLogger(MyBatisUtil .class);
static {
try {
sessionFactory = new SqlSessionFactoryBuilder()
.build(Resources.getResourceAsReader("mybatis-config.xml"));
} catch (IOException e) {
logger.debug("读取核心配置文件错误,请检查文件名或路径是否正确!");
}catch (Exception e){
logger.debug("读取sql映射文件错误,请检查文该文件!");
}
}
/**
* 获取SqlSessionFactory
* @return sqlSessionFactory
*/
public static SqlSessionFactory getFactory(){
return sessionFactory;
}
public static SqlSession getSession(){
if(session==null){
session=sessionFactory.openSession();
}
return session;
}
public static void closeSession(){
if(session!=null){
session.close();
}
}
}
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="jdbc.properties"></properties>
<settings>
<setting name="cacheEnabled" value="true"/>
</settings>
<typeAliases>
<package name="com.song.model"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<package name="com.song.mapper"/>
</mappers>
</configuration>
实体类:
package com.song.model;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;
import java.util.Date;
@Data
@AllArgsConstructor
@NoArgsConstructor
@Accessors(chain = true) //链式语法
public class User implements java.io.Serializable{
private Integer id;
private String username;
private String password;
private Date birthday;
private Integer sex;
private Integer age;
}
Mapper接口:
package com.song.mybatis.mapper;
import com.song.mybatis.entity.User;
import org.apache.ibatis.annotations.Param;
import java.util.List;
import java.util.Optional;
public interface UserMapper {
Optional<User> findById(Integer id);
List<User> findAll(@Param("username") String username,@Param("sex")Integer sex,
@Param("a1")Integer a1,@Param("a2")Integer a2);
int add(User user);
List<User> USERS();
int save(User user);
int delete(int id);
int update(User user);
}
Mapper映射文件:
<?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.song.mybatis.mapper.UserMapper">
<cache/>
<sql id="users">
select * from t_user
</sql>
<insert id="add" parameterType="User" flushCache="true">
insert into t_user(username, password, sex, birthday, age)
VALUES(#{username},#{password},#{sex},#{birthday},#{age})
</insert>
<insert id="save">
call add_user(#{username},#{password},#{sex},#{birthday},#{age})
</insert>
<update id="update">
call update_user(#{username},#{password},#{sex},#{birthday},#{age},${id})
</update>
<delete id="delete">
call del(#{id})
</delete>
<select id="findById" resultType="User" parameterType="int">
<include refid="users"/> where id=#{id}
</select>
<select id="findAll" resultType="User">
<include refid="users"/>
<where>
<if test="username!=null and username!=''">
username like concat('%',#{username},'%')
</if>
<if test="sex!=null">
and sex=#{sex}
</if>
<if test="a1!=null and a2!=null">
and age between #{a1} and #{a2}
</if>
</where>
</select>
<!--使用存储过程执行列表-->
<select id="USERS" resultType="User">
call list()
</select>
</mapper>
存储过程:
DROP PROCEDURE list;
#创建列表的存储过程,没有参数
CREATE PROCEDURE list()
SELECT * from t_user;
#调用
call list();
#创建新增的存储过程
CREATE PROCEDURE add_user(IN $username VARCHAR(20),IN $password VARCHAR(20),
IN $sex int,IN $birthday datetime,IN $age int)
INSERT INTO t_user(username,password,sex,birthday,age)
values($username,$password,$sex,$birthday,$age);
call add_user('abdc','1234',0,now(),33);
CREATE PROCEDURE del(IN $id int)
DELETE from t_user where id=$id
#修改的存储过程:
CREATE PROCEDURE update_user(IN $username VARCHAR(20),IN $password VARCHAR(20),
IN $sex int,IN $birthday datetime,IN $age int,IN $id int)
UPDATE t_user set username=$username, password=$password ,sex=$sex,
birthday=$birthday,age=$age where id=$id;
项目结构:
测试:
package com.song.test;
import com.song.mybatis.entity.User;
import com.song.mybatis.mapper.UserMapper;
import com.song.mybatis.util.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Assert;
import org.junit.Before;
import org.junit.Test;
import java.util.Date;
public class MyTest {
private SqlSession sqlSession;
private UserMapper userMapper;
@Before
public void before(){
this.sqlSession= MyBatisUtil.getSession();
this.userMapper = sqlSession.getMapper(UserMapper.class);
}
@Test
public void test(){
System.out.println("第一次查询");
System.out.println(this.userMapper.findById(3));
System.out.println("第二次查询");
System.out.println(this.userMapper.findById(4));
}
@Test
public void testSecond(){
System.out.println("第一次sqlsession查询");
SqlSession session1=MyBatisUtil.getFactory().openSession();
UserMapper userMapper1=session1.getMapper(UserMapper.class);
User user = userMapper1.findById(4).get();
System.out.println(user);
session1.close();
// session1.commit();
System.out.println("第二次sqlsession查询");
SqlSession session2=MyBatisUtil.getFactory().openSession();
UserMapper userMapper2=session2.getMapper(UserMapper.class);
User user1 = userMapper2.findById(4).get();
System.out.println(user1);
session2.close();
System.out.println("第三次sqlsession查询");
SqlSession session3=MyBatisUtil.getFactory().openSession();
UserMapper userMapper3=session3.getMapper(UserMapper.class);
User user2 = userMapper3.findById(4).get();
System.out.println(user2);
session3.close();
}
@Test
public void testProcedure(){
System.out.println(this.userMapper.USERS());
}
@Test
public void testAdd(){
User user=new User(null, "dddd", "333", new Date(), 1, 29);
System.out.println(this.userMapper.save(user));
}
@Test
public void testDelete(){
System.out.println(this.userMapper.delete(3));
}
@Test
public void testUpdate(){
User user=this.userMapper.findById(32).get();
System.out.println("修改前:"+user);
user.setUsername("悟空");
user.setAge(44);
System.out.println(this.userMapper.update(user));
System.out.println("修改后:"+user);
}
@After
public void after(){
this.sqlSession.commit();
}
}