myBatis 操作数据库增删改查
mybati-config配置文件
<?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/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/huang/dao/UserMapper.xml"/>
</mappers>
</configuration>
Utils工具类:
package com.huang.utils;
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 MybatisUtils {
private static SqlSessionFactory sqlSessionFactory;
static{
try {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
}
public static SqlSession getSqlSession(){
return sqlSessionFactory.openSession();
}
}
dao层
package com.huang.dao;
import com.huang.pojo.User;
public interface UserMapper {
//增加一行数据
int addUser(User user);
//删除一行数据
int deleteUser(int id);
//修改一行数据
int updateUser(User user);
//查询一行数据
User selectUser(int id);
}
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.huang.dao.UserMapper">
<insert id="addUser" parameterType="com.huang.pojo.User">
INSERT INTO `smbms`.`smbms_user` (`id`,`userCode`,`userName`,`userPassword`)
VALUES (#{id},#{userCode},#{userName},#{userPassword})
</insert>
<delete id="deleteUser" parameterType="int">
DELETE FROM `smbms`.`smbms_user` WHERE `id` = #{id}
</delete>
<update id="updateUser" parameterType="com.huang.pojo.User">
UPDATE `smbms`.`smbms_user` SET `userCode` = #{userCode} ,`userName` = #{userName}, `userPassword` = #{userPassword} WHERE `id` = #{id}
</update>
<select id="selectUser" parameterType="int" resultType="com.huang.pojo.User">
SELECT * FROM `smbms`.`smbms_user` WHERE `id` = #{id}
</select>
</mapper>
@text测试代码:
package com.huang.text;
import com.huang.dao.UserMapper;
import com.huang.pojo.User;
import com.huang.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class text {
@Test
public void addUser(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
int num = userMapper.addUser(new User(20 ,"hahahah" , "小明" , "55555555"));
if (num > 0){
System.out.println("增加成功!");
}
sqlSession.commit();
sqlSession.close();
}
@Test
public void deleteUser(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
int num = userMapper.deleteUser(20);
if (num > 0){
System.out.println("删除成功!");
}
sqlSession.commit();
sqlSession.close();
}
@Test
public void updateUser(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
int num = userMapper.updateUser(new User(20 , "hhhh","张三" , "66666666"));
if (num > 0){
System.out.println("更改成功!");
}
sqlSession.commit();
sqlSession.close();
}
@Test
public void selectUser(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = userMapper.selectUser(1);
System.out.println(user.getUserName());
sqlSession.close();
}
}
使用Map进行mybatis
接口编写
//分页
List<User> getUserByLimit(Map<String,Object> map);
配置分页
<select id="getUserByLimit" parameterType="map" resultType="user">
SELECT * FROM `smbms`.`smbms_user` Limit #{startIndex} , #{pageSize}
</select>
测试用例
@Test
public void getUserByLimit(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
Map<String , Object> map = new HashMap<>();
map.put("startIndex" , 2);
map.put("pageSize" , 2);
List<User> limit = userMapper.getUserByLimit(map);
for (User user : limit) {
System.out.println(user.getUserName());
}
sqlSession.commit();
sqlSession.close();
}
mybatis配置
<!--配置文件扫描-->
<properties resource="db.properties"/>
<!--配置日志-->
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<resultMap id="userMap" type="User">
<result column="id" property="id"/>
<result column="userCode" property="userCode"/>
<result column="userName" property="userName"/>
<result column="userPassword" property="userPassword"/>
</resultMap>
详见官网
注解操作增删改查
修改mybatis-config
<mappers>
<mapper class="com.huang.dao.UserMapper"/>
</mappers>
接口编写
package com.huang.dao;
import com.huang.pojo.User;
import org.apache.ibatis.annotations.*;
public interface UserMapper {
//增加一行数据
@Insert("INSERT INTO `smbms`.`smbms_user` (`id`,`userCode`,`userName`,`userPassword`) VALUES (#{id},#{userCode},#{userName},#{userPassword})")
int addUser(User user);
//删除一行数据
@Delete("DELETE FROM `smbms`.`smbms_user` WHERE `id` = #{id}")
int deleteUser(@Param("id") int id);
//修改一行数据
@Update("UPDATE `smbms`.`smbms_user` SET `userCode` = #{userCode} ,`userName` = #{userName}, `userPassword` = #{userPassword} WHERE `id` = #{id}")
int updateUser(User user);
//查询一行数据
@Select("SELECT * FROM `smbms`.`smbms_user` WHERE `id` = #{id}")
User selectUser(@Param("id") int id);
}
联表查询
多对一
package com.huang.dao;
import com.huang.pojo.Student;
import com.huang.pojo.Teacher;
import java.util.List;
public interface StudentMapper {
//查询学生 方式1
List<Student> getStudents();
//查询学生 方式2
List<Student> getStudents2(int id);
//测试查询两张表
List<Student> getST();
}
<?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.huang.dao.StudentMapper">
<select id="getStudents" resultMap="StudentTeacher">
SELECT s.id , s.name , t.name AS t_name
FROM school.student s
LEFT JOIN school.teacher t
ON s.tid = t.id
</select>
<resultMap id="StudentTeacher" type="Student">
<result property="id" column="id"/>
<result property="name" column="name"/>
<result property="teacher_name" column="t_name"/>
</resultMap>
<select id="getStudents2" resultMap="StudentTeacher2" parameterType="int">
SELECT s.id , s.name , s.tid FROM school.student s WHERE tid = #{id}
</select>
<resultMap id="StudentTeacher2" type="Student">
<id property="id" column="id"/>
<result property="name" column="name"/>
<association property="teacher" column="tid" select="getTeacher"/>
</resultMap>
<select id="getTeacher" resultType="Teacher">
SELECT t.id , t.name FROM school.teacher t WHERE id = #{tid}
</select>
<select id="getST" resultType="Student">
SELECT * FROM `school`.`student`,`school`.`teacher`
</select>
</mapper>
一对多
package com.huang.dao;
import com.huang.pojo.Teacher;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface TeacherMapper {
//查询所有的教师
List<Teacher> getTeacher();
//查询老师加学生
List<Teacher> getTeachers(@Param("tid") int id);
}
<?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.huang.dao.TeacherMapper">
<select id="getTeacher" resultType="Teacher">
SELECT * FROM `school`.`teacher`
</select>
<select id="getTeachers" resultMap="StudentTeacher">
SELECT t.id tid, t.name tname, s.name sname , s.id sid
FROM school.teacher t,school.student s
WHERE s.tid = t.id AND t.id = #{tid}
</select>
<resultMap id="StudentTeacher" type="Teacher">
<result property="id" column="tid"/>
<result property="name" column="tname"/>
<collection property="student" ofType="com.huang.pojo.Student" javaType="java.util.List">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<result property="tid" column="tid"/>
</collection>
</resultMap>
</mapper>
动态SQL
package com.huang.dao;
import com.huang.pojo.Blog;
import java.util.List;
import java.util.Map;
public interface BlogMapper {
//where if
List<Blog> queryBlogIF(Map map);
//choose when otherwise
List<Blog> queryBlogIF2(Map map);
//set
int updateBlog(Map map);
//foreach
List<Blog> queryBlogIF3(Map map);
}
<?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.huang.dao.BlogMapper">
<select id="queryBlogIF" resultType="blog" parameterType="map">
SELECT * FROM `school`.`blog`
<where>
<if test="title != null">
title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</where>
</select>
<select id="queryBlogIF2" parameterType="map" resultType="blog">
SELECT * FROM `school`.`blog`
<where>
<choose>
<when test="title != null">
title = #{title}
</when>
<when test="author != null">
author = #{author}
</when>
<otherwise>
</otherwise>
</choose>
</where>
</select>
<update id="updateBlog" parameterType="map">
UPDATE `school`.`blog`
<set>
<if test="title != null">
title = #{title},
</if>
<if test="author != null">
author = #{author}
</if>
</set>
WHERE id = #{id}
</update>
<select id="queryBlogIF3" parameterType="map" resultType="blog">
SELECT * FROM `school`.`blog`
<where>
<foreach collection="ids" item="id" open="(" close=")" separator="or">
id = #{id}
</foreach>
</where>
</select>
</mapper>
测试类
package com.huang.text;
import com.huang.dao.BlogMapper;
import com.huang.pojo.Blog;
import com.huang.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class text {
@Test
public void queryBlogIF(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
Map<String , Object> map = new HashMap<>();
//map.put("title" , "狂神说");
//map.put("author" , "小明");
List<Blog> blogs = blogMapper.queryBlogIF(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
@Test
public void queryBlogIF2(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
Map<String , Object> map = new HashMap<>();
//map.put("title" , "狂神说");
//map.put("author" , "小明");
List<Blog> blogs = blogMapper.queryBlogIF2(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
@Test
public void queryBlogIF3(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
Map<String , Object> map = new HashMap<>();
ArrayList<Integer> num = new ArrayList<>();
num.add(1);
num.add(2);
map.put("ids" , num);
List<Blog> blogs = blogMapper.queryBlogIF3(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
}
mybatis缓存
一级缓存默认开启 作用域为sqlsession开启到关闭
二级缓存手动开启 作用域为整个程序执行的过程中,当一级缓存关闭的时候自动保存在二级缓存中
参数配置
<setting name="cacheEnabled" value="true"/>
Mapper中配置
<cache eviction="FIFO"
flushInterval="60000"
size="512"
readOnly="true"/>