Mybatis 简述
Mybatis 是一款数据库映射框架,就是把数据库中的一行数据映射为java/kotlin 中的一个实体对象.用于简化jdbc操作,让程序猿们只关心sql的编写,不用再去关注Connection、ResultSet...
1 maven 依赖引入
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.8</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.27</version>
</dependency>
2 添加Mybatis全局配置文件
1 在src下创建resources文件夹,单击右键Mark Directory as Resources Root 资源根目录(如果没有这一步,编译的target/classes根目录下不会有mybatis.xml文件,导致文件找不到异常) 如图:
2 在创建Mybatis全局配置文件mybatis.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>
<property name="username" value="root"/>
<property name="password" value="你的数据库密码"/>
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/你的数据库名称"/>
</properties>
<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 resource="zengqiang/dao/StudentDao.xml"/>
</mappers>
</configuration>
3 创建dao ,dao是一个接口,并且每个dao有一个mapper.xml与之对应。
example:
StudentDao.kt
package zengqiang.dao
import zengqiang.entity.Student
interface StudentDao {
fun selectStudents()
fun insertStudent(student: Student)
}
StudentDao.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="zengqiang.dao.StudentDao">
<select id="selectStudents" resultType="zengqiang.entity.Student">
select id,name,email from student
</select>
<insert id="insertStudent">
insert into student values(#{id},#{name},#{email})
</insert>
</mapper>
4 使用mybatis查询数据
package zengqiang
import org.apache.ibatis.io.Resources
import org.apache.ibatis.session.SqlSessionFactoryBuilder
import zengqiang.entity.Student
import kotlin.Exception
fun main() {
try {
val `is` = Resources.getResourceAsStream("mybatis.xml")
val sessionFactoryBuilder = SqlSessionFactoryBuilder()
val sessionFactory = sessionFactoryBuilder.build(`is`)
val sqlSession = sessionFactory.openSession()
val sqlId = "zengqiang.dao.StudentDao.selectStudents"
val studentList = sqlSession.selectList<Student>(sqlId)
print(studentList.toString())
sqlSession.close()
} catch (e: Exception) {
e.printStackTrace()
}
}
5 获取sqlSession扩展(还需要优化,因为Kotlin扩展函数中每次获取都会new,极其浪费资源,需要将重量级对象使用单例获取)
package zengqiang.ext
import org.apache.ibatis.io.Resources
import org.apache.ibatis.session.SqlSession
import org.apache.ibatis.session.SqlSessionFactoryBuilder
fun String.getSqlSession(autoCommit: Boolean = false): SqlSession {
return SqlSessionFactoryBuilder()
.build(Resources.getResourceAsStream(this))
.openSession(autoCommit)
}
6 使用mybatis动态代理获取Dao
sqlSession.getMapper(Dao接口::class.java)
example:
package zengqiang
import org.junit.Test
import zengqiang.dao.StudentDao
/**
* 使用 mybatis的动态代理获取StudentDao的Impl
*/
class AppProxyDaoTest {
@Test
fun selectStudents() {
val sqlSession = "mybatis.xml".getSqlSession()
val studentDao = sqlSession.getMapper(StudentDao::class.java)
println(studentDao::class.java.simpleName)
val studentList = studentDao.selectStudents()
print(studentList)
}
}
7 开启mybatis日志
<configuration>
<settings>
<!-- mybatis开启日志-->
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
</configuration>
8 Mapper 中的parameterType
<!-- parameterType dao接口中定义的参数类型 它的值为java的类型全限定名或者mabatis中定义的别名
例如: parameterType="java.lang.Integer"
parameterType="int"
注意 parameterType 不是强制的,mybatis通过反射机制能够发现接口中的参数类型。所以可以没有,一般我们不写-->
<select id="selectStudentById" parameterType="int" resultType="zengqiang.entity.Student">
select id,name,email from student where id=#{id}
</select>
9 使用@Param命名多个参数
Dao方法定义
/**
* 通过@Param 命名参数的方式指定多个参数
*/
fun selectStudentByIdOrName(@Param("name") name: String,
@Param("id") id: Int): List<Student>
对应mapper定义
<select id="selectStudentByIdOrName" resultType="zengqiang.entity.Student">
select id,name,email from student where id=#{id} or name=#{name}
</select>
10 位置参数
Dao方法定义
fun selectStudentByPos(name: String, id: Int): List<Student>
对应mapper定义
<!-- 位置参数的使用 语法:#{arg0} #{arg1}-->
<select id="selectStudentByPos" resultType="zengqiang.entity.Student">
select id,name,email from student where name=#{arg0} or id=#{arg1}
</select>
11 对象参数
Dao 方法定义
fun selectStudentByObject(student: Student): List<Student>
mapper定义
<insert id="insertStudent">
insert into student values(#{id},#{name},#{email})
</insert>
12 Map 传递参数
Dao 方法定义
fun selectStudentByMap(map: Map<String, Any>): List<Student>
mapper定义
<!-- 使用Map传参-->
<select id="selectStudentByMap" parameterType="java.util.Map" resultType="zengqiang.entity.Student">
select * from student where name=#{name} or id=#{id}
</select>
12 resultMap
表字段定义
CREATE TABLE `student` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '用户id',
`name` varchar(100) DEFAULT NULL,
`email` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
entity定义
data class Stu(val sid: Int, val sname: String, val semail: String)
dao定义
/**
* 使用 resultMap映射表数据到实体(如果表数据和实体字段不一致时使用非常方便)
*/
fun selectStudentByResultMap(): List<Stu>
mapper定义
<!-- 使用resultMap 映射查询结果-->
<resultMap id="studentResultMap" type="zengqiang.entity.Stu">
<result column="id" property="sid"/>
<result column="name" property="sname"/>
<result column="email" property="semail"/>
</resultMap>
<select id="selectStudentByResultMap" resultMap="studentResultMap">
select id,name,email from student
</select>