Mybatis使用
1.常用小计
1.常用注解
@Param(value) -> 参数不一致使用
2.${}和#{}区别
1.${}是字符串拼接默认不会有'',#{}为占位符,默认带''
2.#{}安全性比较高
3.在批量删除时候,模糊查询时候等时候${}比较方便
3.一级缓存和二级缓存
一级缓存:是SqlSession级别 默认开启
失效情况:1.同一个SqlSession查询条件不同
2.在两次查询中间穿插了任何一次增删改操作
3.手动清除缓存
二级缓存:是nameSpace级别 也为SqlSessionFactiory级别
开启条件 添加 <cache/>标签,必须SqlSession提交了有效
失效情况:1.二次查询时候执行了增删改操作
缓存机种条件:二级没命中,找一级,以及没有查数据库
2.idea搭建一个mybatis
1.创建一个Maven工程
2.引入常用pom依赖
< packaging> jar</ packaging>
< dependencies>
< dependency>
< groupId> org.mybatis</ groupId>
< artifactId> mybatis</ artifactId>
< version> 3.5.7</ version>
</ dependency>
< dependency>
< groupId> junit</ groupId>
< artifactId> junit</ artifactId>
< version> 4.12</ version>
< scope> test</ scope>
</ dependency>
< dependency>
< groupId> mysql</ groupId>
< artifactId> mysql-connector-java</ artifactId>
< version> 8.0.16</ version>
</ dependency>
< dependency>
< groupId> log4j</ groupId>
< artifactId> log4j</ artifactId>
< version> 1.2.17</ version>
</ dependency>
</ dependencies>
3.创建接口(mybatis是一个面向接口变成语言)
public interface UserMapper {
int insertUser ( ) ;
}
4.创建对用pojo
5.配置文件
1.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" />
< typeAliases>
< package name = " com.atguigu.mybatis.pojo" />
</ 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.atguigu.mybatis.mapper" />
</ mappers>
</ configuration>
2.jdbc.properties
jdbc.driver = com.mysql.cj.jdbc.Driver
jdbc.url = jdbc:mysql://localhost:3306/ssm?serverTimezone=UTC
jdbc.username = root
jdbc.password = root
3.log4j.xml and logback.xml
<?xml version="1.0" encoding="UTF-8" ?>
<! DOCTYPE log4j:configuration SYSTEM "log4j.dtd" >
< log4j: configuration xmlns: log4j= " http://jakarta.apache.org/log4j/" >
< appender name = " STDOUT" class = " org.apache.log4j.ConsoleAppender" >
< param name = " Encoding" value = " UTF-8" />
< layout class = " org.apache.log4j.PatternLayout" >
< param name = " ConversionPattern" value = " %-5p %d{MM-dd HH:mm:ss,SSS}
%m (%F:%L) \n" />
</ layout>
</ appender>
< logger name = " java.sql" >
< level value = " debug" />
</ logger>
< logger name = " org.apache.ibatis" >
< level value = " info" />
</ logger>
< root>
< level value = " debug" />
< appender-ref ref = " STDOUT" />
</ root>
</ log4j: configuration>
<?xml version="1.0" encoding="UTF-8"?>
< configuration debug = " true" >
< appender name = " STDOUT"
class = " ch.qos.logback.core.ConsoleAppender" >
< encoder>
< pattern> [%d{HH:mm:ss.SSS}] [%-5level] [%thread] [%logger] [%msg]%n</ pattern>
< charset> UTF-8</ charset>
</ encoder>
</ appender>
< root level = " DEBUG" >
< appender-ref ref = " STDOUT" />
</ root>
< logger name = " com.atguigu.mybatis" level = " DEBUG" />
</ configuration>
4.对应接口的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.atguigu.mybatis.mapper.UserMapper" >
< insert id = " insertUser" >
insert into t_user value(NULL ,'郝雪龙','123456',22,'男','2234276777@qq.com')
</ insert>
< update id = " updateUser" >
update t_user set username = 'root' where id = 3
</ update>
< select id = " getAllUser" resultType = " User" >
select * from t_user;
</ select>
</ mapper>
6使用mybatis对数据库操作
public class MyBatatisTest {
@Test
public void testInset ( ) throws IOException {
InputStream is = Resources . getResourceAsStream ( "mybatis-config.xml" ) ;
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder ( ) ;
SqlSessionFactory build = sqlSessionFactoryBuilder. build ( is) ;
SqlSession sqlSession = build. openSession ( ) ;
UserMapper mapper = sqlSession. getMapper ( UserMapper . class ) ;
int result = mapper. insertUser ( ) ;
System . out. println ( "result = " + result) ;
sqlSession. close ( ) ;
}
}
3.查询
1.返回实体对象
resultType="User"
2.返回Map集合
resultType=“map”
3.返回List集合
resultType="list"
注意:mybatis自动帮我们内置了一些别名
4.参数为map
占位符${key}
5.参数为list
占位符${list[index]}
6.参数为Set
占位符${collection}
7.参数实体类
占位符${属性}
小计:可以使用@Param(value)注解来改变参数名
4.模糊查询
1. 方法1 :
select * from t_user where username like concat( '%' ,
2. 方法2 :
select * from t_user where username like '%${参数}%'
3. 方法3 :
select * from t_user where username like "%"
注意:由于
5.批量删除
delect from t_user where id in ( ${参数}) ;
6.动态设置表明
select * from ${参数}
7.获得自增主键
userGeneratedKeys="true" keyProperty="id"
8.使用数据库字段与属性不一致
1.方法:
1.全局配置:
< settings>
< setting name = " mapUnderscoreToCameCase" value = " true" />
</ settings>
2.使用resultMap自定义映射
< resultMap id = " empResultMap" type = " Emp" >
< id column = " emp_id" property = " empId" />
< result column = " emp_name" property = " empName" >
</ resultMap>
9.逆向工程
1.导入pom依赖
< dependencies>
< dependency>
< groupId> org.mybatis</ groupId>
< artifactId> mybatis</ artifactId>
< version> 3.5.7</ version>
</ dependency>
< dependency>
< groupId> junit</ groupId>
< artifactId> junit</ artifactId>
< version> 4.12</ version>
< scope> test</ scope>
</ dependency>
< dependency>
< groupId> log4j</ groupId>
< artifactId> log4j</ artifactId>
< version> 1.2.17</ version>
</ dependency>
< dependency>
< groupId> mysql</ groupId>
< artifactId> mysql-connector-java</ artifactId>
< version> 8.0.16</ version>
</ dependency>
</ dependencies>
< build>
< plugins>
< plugin>
< groupId> org.mybatis.generator</ groupId>
< artifactId> mybatis-generator-maven-plugin</ artifactId>
< version> 1.3.0</ version>
< dependencies>
< dependency>
< groupId> org.mybatis.generator</ groupId>
< artifactId> mybatis-generator-core</ artifactId>
< version> 1.3.2</ version>
</ dependency>
< dependency>
< groupId> mysql</ groupId>
< artifactId> mysql-connector-java</ artifactId>
< version> 8.0.16</ version>
</ dependency>
</ dependencies>
</ plugin>
</ plugins>
</ build>
2.引入generatorConfig.xml文件
<?xml version="1.0" encoding="UTF-8"?>
<! DOCTYPE generatorConfiguration
PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
"http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd" >
< generatorConfiguration>
< context id = " DB2Tables" targetRuntime = " MyBatis3" >
< jdbcConnection driverClass = " com.mysql.cj.jdbc.Driver"
connectionURL = " jdbc:mysql://localhost:3306/mybatisdemo?
serverTimezone=UTC"
userId = " root"
password = " root" >
</ jdbcConnection>
< javaModelGenerator targetPackage = " com.atguigu.mybatis.pojo"
targetProject = " .\src\main\java" >
< property name = " enableSubPackages" value = " true" />
< property name = " trimStrings" value = " true" />
</ javaModelGenerator>
< sqlMapGenerator targetPackage = " com.atguigu.mybatis.mapper"
targetProject = " .\src\main\resources" >
< property name = " enableSubPackages" value = " true" />
</ sqlMapGenerator>
< javaClientGenerator type = " XMLMAPPER"
targetPackage = " com.atguigu.mybatis.mapper" targetProject = " .\src\main\java" >
< property name = " enableSubPackages" value = " true" />
</ javaClientGenerator>
< table tableName = " car" domainObjectName = " Car" />
< table tableName = " wheel" domainObjectName = " Wheel" />
< table tableName = " adminuser" domainObjectName = " User" />
</ context>
</ generatorConfiguration>
3.maven-plugins
10.动态Sql
1.Where if
select * from adminuser
< where>
< if test = " username != null and username != '' " >
and username = #{username}
</ if>
< if test = " gender != null and gender != '' " >
and gender = #{gender}
</ if>
</ where>
2.Set if
update adminuser
< set>
< if test = " username !=null and username != ''" >
username = #{username},
</ if>
< if test = " password != null and password != '' " >
password = #{password}
</ if>
</ set>
where uid = #{uid}
11.一对多 || 多对一
1.提示
外键存在多的一方
查询一 ,使用级联方式
< resultMap id = " findCarWithWheelByIdResultMap" type = " Car" >
< id column = " id" property = " id" />
< result column = " brand" property = " brand" />
< association property = " wheel" javaType = " Wheel" >
< id column = " wheel_id" property = " wheelId" />
< result column = " wheel_brand" property = " wheelBrand" />
</ association>
</ resultMap>
< select id = " findCarWithWheelById" resultMap = " findCarWithWheelByIdResultMap" >
SELECT id,brand,wheel_brand,wheel.wheel_id
FROM car LEFT JOIN wheel
ON car.`wheel_id` = wheel.`wheel_id` WHERE car.`id` = #{cid}
</ select>
查询多,使用collection
< resultMap id = " findWheelWithCarResultMap" type = " Wheel" >
< id column = " wheel_id" property = " wheelId" />
< result column = " wheel_brand" property = " wheelBrand" />
< collection property = " carList" ofType = " Car" >
< id column = " id" property = " id" />
< result column = " brand" property = " brand" />
</ collection>
</ resultMap>
< select id = " findWheelWithCar" resultMap = " findWheelWithCarResultMap" >
SELECT w.wheel_id,wheel_brand,c.id,c.brand
FROM wheel w LEFT JOIN car c
ON w.`wheel_id` = c.`wheel_id` WHERE w.wheel_id = #{wid}
</ select>