Mybatis初识
1.首个mybatis项目
依赖sql:
CREATE TABLE `edu_chapter` (
`id` char(19) NOT NULL COMMENT '章节ID',
`course_id` char(19) NOT NULL COMMENT '课程ID',
`title` varchar(50) NOT NULL COMMENT '章节名称',
`sort` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '显示排序',
`gmt_create` datetime NOT NULL COMMENT '创建时间',
`gmt_modified` datetime NOT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
KEY `idx_course_id` (`course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT='课程';
1.导入依赖pom(mybatis 和mysql驱动)
<dependencies>
<!-- junit test -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
<!-- mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.5</version>
</dependency>
<!-- mysql -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
</dependencies>
<!-- 将项目中的配置文件识别,构建 -->
<build>
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
<include>**/*.properties</include>
</includes>
</resource>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/*.xml</include>
<include>**/*.properties</include>
</includes>
</resource>
</resources>
</build>
2.mybatis的xml配置(目的在于链接数据源的配置和注册其他的mapper)
<?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>
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/michael_edu?characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai"/>
<property name="username" value="root"/>
<property name="password" value="Haishao123"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/michael/dao/mapper/ChapterMapper.xml"/>
</mappers>
</configuration>
3.提取工具类(封装再utis中,实现数据库连接session创建;工场模式+构造者模式)
package com.michael.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();
}
}
4.编写数据库映射pojo类
package com.michael.pojo;
import java.util.Date;
import java.io.Serializable;
public class Chapter implements Serializable {
private static final long serialVersionUID = 1L;
private String id;
private String courseId;
private String title;
private Integer sort;
private Date gmtCreate;
private Date gmtModified;
public Chapter() {
}
public Chapter(String id, String courseId, String title, Integer sort, Date gmtCreate, Date gmtModified) {
this.id = id;
this.courseId = courseId;
this.title = title;
this.sort = sort;
this.gmtCreate = gmtCreate;
this.gmtModified = gmtModified;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getCourseId() {
return courseId;
}
public void setCourseId(String courseId) {
this.courseId = courseId;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public Integer getSort() {
return sort;
}
public void setSort(Integer sort) {
this.sort = sort;
}
public Date getGmtCreate() {
return gmtCreate;
}
public void setGmtCreate(Date gmtCreate) {
this.gmtCreate = gmtCreate;
}
public Date getGmtModified() {
return gmtModified;
}
public void setGmtModified(Date gmtModified) {
this.gmtModified = gmtModified;
}
@Override
public String toString() {
return "Chapter{" +
"id='" + id + '\'' +
", courseId='" + courseId + '\'' +
", title='" + title + '\'' +
", sort=" + sort +
", gmtCreate=" + gmtCreate +
", gmtModified=" + gmtModified +
'}';
}
}
5.编写需要实现的接口
package com.michael.dao;
import com.michael.pojo.Chapter;
import java.util.List;
public interface ChapterMapper {
List<Chapter> queryChapterLists();
}
6.编写mapper.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.michael.dao.ChapterMapper">
<select id="queryChapterLists" resultType="com.michael.pojo.Chapter">
select * from edu_chapter
</select>
</mapper>
7.测试使用
package com.michael;
import com.michael.pojo.Chapter;
import com.michael.dao.ChapterMapper;
import com.michael.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class MybatisTest {
@Test
public void queryLists(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
ChapterMapper mapper = sqlSession.getMapper(ChapterMapper.class);
List<Chapter> chapters = mapper.queryChapterLists();
for (Chapter chapter : chapters) {
System.out.println(chapter);
}
sqlSession.close();
}
}
2.mybatis增删改查实现(增删改需要提交事务)
CRUD实现实例
1).定义接口
package com.michael.dao;
import com.michael.pojo.Chapter;
import java.util.List;
public interface ChapterMapper {
// query all the lists of chapter
List<Chapter> queryChapterLists();
// query an appointed chapter
Chapter queryOneChapter(String id);
// update the appointed chapter
int updateChapter(Chapter chapter);
// delete the appointed chapter
int deleteChapter(String id);
// add a new chapter
int addChapter(Chapter chapter);
}
2).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.michael.dao.ChapterMapper">
<select id="queryChapterLists" resultType="com.michael.pojo.Chapter">
select * from edu_chapter
</select>
<select id="queryOneChapter" resultType="com.michael.pojo.Chapter">
select * from edu_chapter where id=#{id}
</select>
<update id="updateChapter" parameterType="com.michael.pojo.Chapter">
update edu_chapter set title=#{title},gmt_modified=#{gmtModified} where id=#{id}
</update>
<delete id="deleteChapter" parameterType="String">
delete from edu_chapter where id=#{id}
</delete>
<insert id="addChapter" parameterType="com.michael.pojo.Chapter">
insert into edu_chapter values (#{id},#{courseId},#{title},#{sort},#{gmtCreate},#{gmtModified})
</insert>
</mapper>
3).测试代码
package com.michael;
import com.michael.dao.ChapterMapper;
import com.michael.pojo.Chapter;
import com.michael.util.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.Date;
import java.util.List;
public class MybatisTest {
@Test
public void queryLists(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
ChapterMapper mapper = sqlSession.getMapper(ChapterMapper.class);
List<Chapter> chapters = mapper.queryChapterLists();
for (Chapter chapter : chapters) {
System.out.println(chapter);
}
sqlSession.close();
}
@Test
public void queryList(){
SqlSession session = MybatisUtils.getSqlSession();
ChapterMapper mapper = session.getMapper(ChapterMapper.class);
Chapter chapter = mapper.queryOneChapter("1");
System.out.println(chapter);
session.close();
}
@Test
public void updateChapter(){
SqlSession session = MybatisUtils.getSqlSession();
ChapterMapper mapper = session.getMapper(ChapterMapper.class);
Chapter chapter = new Chapter();
chapter.setId("44");
chapter.setTitle("第三章:控制流updated");
chapter.setGmtModified(new Date());
int i = mapper.updateChapter(chapter);
System.out.println(i);
session.commit();
session.close();
}
@Test
public void deleteChapter(){
SqlSession session = MybatisUtils.getSqlSession();
ChapterMapper mapper = session.getMapper(ChapterMapper.class);
int i = mapper.deleteChapter("1");
System.out.println(i);
session.commit();
session.close();
}
@Test
public void addChapter(){
SqlSession session = MybatisUtils.getSqlSession();
ChapterMapper mapper = session.getMapper(ChapterMapper.class);
Chapter chapter = new Chapter();
chapter.setId("1");
chapter.setCourseId("11");
chapter.setTitle("第一章:Java入门added");
chapter.setSort(1);
chapter.setGmtCreate(new Date());
chapter.setGmtModified(new Date());
mapper.addChapter(chapter);
session.commit();
session.close();
}
}
3.使用map实现CRUD功能(灵活,可扩展)
1)定义接口
List<Chapter> queryAppointedChapter(Map<String,Object> map);
2)实现接口
<!-- Implement the CRUD by Map -->
<select id="queryAppointedChapter" parameterType="map" resultType="com.michael.pojo.Chapter">
select * from edu_chapter where id=#{id} and course_id=#{courseId}
</select>
3)测试使用
package com.michael;
import com.michael.dao.ChapterMapper;
import com.michael.pojo.Chapter;
import com.michael.util.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.HashMap;
import java.util.List;
public class MybatisTest02 {
@Test
public void queryTest(){
SqlSession session = MybatisUtils.getSqlSession();
ChapterMapper mapper = session.getMapper(ChapterMapper.class);
HashMap<String, Object> map = new HashMap<>();
map.put("id","1");
map.put("courseId", "18");
List<Chapter> chapters = mapper.queryAppointedChapter(map);
if(!chapters.isEmpty()){
for (Chapter chapter: chapters
) {
System.out.println(chapter);
}
}
else {
System.out.println("the list of target chapters is empty!!!");
}
session.close();
}
}
3.模糊查询实现
1)定义接口
// query the target list by like
List<Chapter> queryChapterByLike(@Param("titleValue") String titleValue);
// query the target list by like
List<Chapter> queryChapterByLike02(@Param("titleValue") String titleValue);
2)实现接口
<select id="queryChapterByLike" resultType="com.michael.pojo.Chapter">
select * from edu_chapter where title like #{titleValue}
</select>
<!-- using a wildcard in the SQL statement can cause SQL injection -->
<select id="queryChapterByLike02" resultType="com.michael.pojo.Chapter">
select * from edu_chapter where title like "%"#{titleValue}"%"
</select>
3)测试使用
@Test
public void likeTest(){
SqlSession session = MybatisUtils.getSqlSession();
ChapterMapper mapper = session.getMapper(ChapterMapper.class);
String likeVaule = "%第一%";
List<Chapter> chapters = mapper.queryChapterByLike(likeVaule);
if(!chapters.isEmpty()){
for (Chapter chapter: chapters
) {
System.out.println(chapter);
}
}
else {
System.out.println("the list of target chapters is empty!!!");
}
System.out.println("============ Parting line ===============");
// 在sql语句中拼接通配符,会引起sql注入
String likeVaule2 = "第一";
List<Chapter> chapters2 = mapper.queryChapterByLike02(likeVaule2);
if(!chapters2.isEmpty()){
for (Chapter chapter: chapters2
) {
System.out.println(chapter);
}
}
else {
System.out.println("the list of target chapters is empty!!!");
}
session.close();
}
小结:
-
所有的增删改操作都需要提交事务!
-
接口所有的普通参数,尽量都写上@Param参数,尤其是多个参数时,必须写上!
-
有时候根据业务的需求,可以考虑使用map传递参数!
-
为了规范操作,在SQL的配置文件中,我们尽量将Parameter参数和resultType都写上!