目录
1.基础使用
1.1文件目录
1.2User实体类
import java.util.Date;
public class User {
private Integer id;
private String username;
private Date birthday;
private String sex;
private String address;
//必须保留一个无参的构造器
public User() {
}
public User(String username, Date birthday, String sex, String address) {
this.username = username;
this.birthday = birthday;
this.sex = sex;
this.address = address;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "user{" + "id=" + id + ", username='" + username + '\'' + ", birthday=" + birthday + ", sex='" + sex + '\'' + ", address='" + address + '\'' + '}';
}
}
1.3UserMap接口
import java.io.IOException;
import java.util.List;
import java.util.Map;
import com.weng.bean.User;
//接口和xml文件的名字要保持一致
public interface UserMapper {
public abstract List<User> findList() throws IOException;
public abstract int addUser(User u);
public abstract User selectById(int id);
public abstract int updateById(User user);
public abstract int deleteById(int id);
public abstract List<User> selectLike(String username);
public abstract List<User> selectLike2(String username);
public abstract List<User> findUsersByBirthday(Map<String,Object> map);
public abstract Map<String,Object> getResult();
public abstract Map<String,Object> getResult2();
}1.4UserMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!--上述的话必须放在所有xml文件中的第一行，且不能有空格-->
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--接口和xml文件的名字要保持一致
namespace要是接口的全路径-->
<mapper namespace="com.weng.mapper.UserMapper">
<!--查询所有用户 id="方法名" 不用加; 返回类型resultType,查询必须写上
id必须和接口的方法名一致-->
<select id="findList" resultType="User">
select * from user
</select>
<!--新增用户
parameterType 参数类型，可以不写 mybatis自动推断
占位符是#{}
新增，更新，删除不需要写返回值类型，默认是int
配置自增长主键
useGeneratedKeys="true" 开启自增长的映射
keyProperty="id" 那一个属性是主键
-->
<insert id="addUser" parameterType="User">
<!--获取自增长主键-->
<selectKey keyProperty="id" resultType="int">
select last_insert_id()<!--获取最后一次新增成功的id值-->
</selectKey>
insert into user values (null,#{username},#{birthday},#{sex},#{address})
</insert>
<!--根据id查询-->
<select id="selectById" resultType="User">
select * from user where id = #{id}
</select>
<!--根据id更新-->
<update id="updateById" parameterType="User">
update user set username=#{username},birthday=#{birthday},sex=#{sex},address=#{address} where id=#{id}
</update>
<!--根据id删除-->
<delete id="deleteById" parameterType="int">
delete from user where id=#{id}
</delete>
<!--模糊查询方法1-->
<select id="selectLike" resultType="User">
select * from user where username like #{username}
</select>
<!--模糊查询方法2
%${value}%或者用@param注解，在接口的参数上写，如下
public abstract List<User> selectLike2(@Param("userName") String name,@Param("password") String pwd);
xml文件里：select * from user where user_name = #{userName} and user_password=#{password}
-->
<select id="selectLike2" resultType="User" parameterType="String">
select * from user where username like '%${value}%'
</select>
<!--#{}和$的区别：
#{}是sql语句的预处理函数，之后执行sql时用？代替，不需要关注数据类型，myBatis自动实现数据类型，可防止sql注入
$用于sql的直接拼接，需要自己判断数据类型，不能防止sql注入
-->
<!--传入参数多个，用map（键值对）传入
不用map的话,用parm0,parm1,parm2或者arg0,arg1,arg2也可以
或者注解@Param(xxxx)写在接口传参里-->
<select id="findUsersByBirthday" resultType="user">
select * from user where birthday between #{xxxx} and #{oooo}
</select>
<!--查询生日的最大最小，用map返回，map键值对，键就是min(birthday)和老大-->
<select id="getResult" resultType="map">
select max(birthday) as '老大',min(birthday) from user
</select>
<!--方法2
自己创建一个resultmap-->
<select id="getResult2" resultMap="my_map">
select max(birthday),min(birthday) from user
</select>
<resultMap id="my_map" type="map">
<result property="老大" column="max(birthday)"/>
<result property="老小" column="min(birthday)"/>
</resultMap>
<!--程序代码中实体类中属性和数据库不对应解决方法：
1：写sql语句的时候取别名as
2：不在sql中取别名，就需要自己写个resultMap取别名，保持一致
id字段用于表示主键字段，普通字段用result-->
</mapper>1.5MyBatisUtils工具类
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 {
//获取session
public static SqlSession getSession(){
SqlSession session = null;
//加载配置文件，得到一个输入流
InputStream inputStream = null;
try {
//配置文件的路径
String resource = "mybatis-config.xml";
inputStream = Resources.getResourceAsStream(resource);
//获取myBatis的session工厂
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//通过session工厂获取一个session(此session非servlet中session
// 这个session表示myBatis框架和数据库的会话信息)
//获取到session就表示myBatis连接到数据库了，类似JDBC中connection对象
session = sqlSessionFactory.openSession();
} catch (IOException e) {
e.printStackTrace();
}
return session;
}
//关闭session
public static void closeSession(SqlSession session){
if(session!=null){
session.close();
}
}
}
1.6mybatis-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>
<!--实体类取别名,可用在UserMapper.xml中
方法一：一个一个取
<typeAliases>
<typeAlias type="com.weng.bean.User" alias="hhUser"/>
</typeAliases>
方法二：给包下的所有实体类取别名,默认的实体类别名就是实体类名字（且不区分大小写）
-->
<typeAliases>
<package name="com.weng.bean"/>
</typeAliases>
<!--开发环境 -->
<environments default="development">
<!--目前是开发环境-->
<environment id="development">
<!--使用jdbc管理事务-->
<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>
<!--mapper有三种写法-->
<mappers>
<!--方法1：xml的路径
<mapper resource="com/weng/mapper/UserMapper.xml"></mapper>
-->
<!--方法2：类的路径，要求接口文件名和xml文件名一致
<mapper class="com.weng.mapper.UserMapper"/>
-->
<!--方法3：扫描整个包下的mapper，和取别名的方法2类似-->
<package name="com.weng.mapper"/>
</mappers>
</configuration>1.7testMybatis
import com.weng.bean.User;
import com.weng.mapper.UserMapper;
import com.weng.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class testMybatis {
SqlSession session = null;
UserMapper um = null;
//@Before表示每次调用测试方法之前先调用@Before注解的方法init
@Before
public void init(){
//UserMapperImpl um = new UserMapperImpl();
//myBatis在底层使用动态代理（反射）自动生成Mapper实现类，不需要人工写
session = MyBatisUtils.getSession();
um = session.getMapper(UserMapper.class);
//um就是自动生成的实现类
}
@Test
public void testQuery() throws IOException {
List<User> list = um.findList();
for (User user:list) {
System.out.println(user);
}
}
//myBatis中新增删除更新需要手动提交事务,或者在工具类中
// session = sqlSessionFactory.openSession(true);传入参数true，表示自动提交
@Test
public void testInsert(){
User u = new User("哈哈",new Date(),"男","齐齐哈尔");
try {
int count = um.addUser(u);
System.out.println(count>0?"新增成功":"新增失败");
} catch (Exception e) {
e.printStackTrace();
}
}
//根据id查询
@Test
public void testSelectById(){
System.out.println(um.selectById(1));
}
//根据id更新
@Test
public void testUpdateById(){
//先从数据库中拿出一个对象，再放回去
User user = um.selectById(1);
user.setUsername("哈1");
user.setBirthday(new Date());
user.setSex("无");
user.setAddress("大桥下");
int count = um.updateById(user);
System.out.println(count>0?"更新成功":"更新失败");
session.commit();
}
//根据id删除
@Test
public void testDeleteById(){
int count = um.deleteById(1);
System.out.println(count>0?"删除成功":"删除失败");
session.commit();
}
//测试模糊查询
//方法1：%%写在传入的参数里
@Test
public void testSelectLike(){
String keys = "王";//输入框，用户只会输入这个，%%需要后台拼接
List<User> users = um.selectLike("%"+keys+"%");
for (User user:users) {
System.out.println(user);
}
}
//方法2
@Test
public void testSelectLike2(){
List<User> users = um.selectLike2("张");
for (User user:users) {
System.out.println(user);
}
}
//测试传入参数是多个，根据生日区间查询
@Test
public void testFindUsersByBirthday(){
Map<String,Object> map = new HashMap<String, Object>();
map.put("xxxx","2014-10-10");
map.put("oooo","2020-10-10");
List<User> users = um.findUsersByBirthday(map);
for (User user : users) {
System.out.println(user);
}
}
//测试返回类型是map，求生日的最大最小
@Test
public void testGetResult(){
Map<String,Object> map = new HashMap<String, Object>();
map = um.getResult();
System.out.println(map);
}
//方法2
@Test
public void testGetResult2(){
Map<String,Object> map = new HashMap<String, Object>();
map = um.getResult2();
System.out.println(map);
}
//@After表示每次调用测试方法之后都会调用@After注解的方法destory
@After
public void destory(){
MyBatisUtils.closeSession(session);
}
}
1.8pom.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>com.weng.mybatis</groupId>
<artifactId>mybatis-20220811</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.3</version>
</dependency>
<!--其他的基本配置，日志、单元测试、jdbc——jar包-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.11</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>1.7.12</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>1.7.12</version>
</dependency>
<!--日志工具-->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.14</version>
</dependency>
</dependencies>
<!--如果是web项目（自带build标签），不需要自己创建build标签
下面代码的作用是编译的时候将xml文件一同编译-->
<build>
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
</resource>
</resources>
</build>
</project>2动态sql
2.1User
import java.util.Date;
public class User {
private Integer id;
private String username;
private Date birthday;
private String sex;
private String address;
//必须保留一个无参的构造器
public User() {
}
public User(String username, Date birthday, String sex, String address) {
this.username = username;
this.birthday = birthday;
this.sex = sex;
this.address = address;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "user{" + "id=" + id + ", username='" + username + '\'' + ", birthday=" + birthday + ", sex='" + sex + '\'' + ", address='" + address + '\'' + '}';
}
}
2.2Usermapper
package com.weng.mapper;
import java.io.IOException;
import java.util.List;
import java.util.Map;
import com.weng.bean.User;
//接口和xml文件的名字要保持一致
public interface UserMapper {
public abstract List<User> selectUserByUsernameAndSex(User user);
public abstract List<User> selectUserByUsernameAndSex2(User user);
public abstract List<User> selectUserByUsernameAndSex3(User user);
public abstract int updateById(User user);
public abstract int updateById2(User user);
public abstract int deleteSome(List<Integer> ids);
public abstract int deleteSome2(Map<String,Object> map);
}
2.4UserMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!--上述的话必须放在所有xml文件中的第一行，且不能有空格-->
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--接口和xml文件的名字要保持一致
namespace要是接口的全路径-->
<mapper namespace="com.weng.mapper.UserMapper">
<!--sql片段：共有的代码抽出来-->
<sql id="query">
<if test="username!= null and username!=''">
and username like #{username}
</if>
<if test="sex!=null and sex!=''">
and sex=#{sex}
</if>
</sql>
<sql id="update">
<if test="username != null and username != ''">
username = #{username},
</if>
<!--日期类型不会为空字符串-->
<if test="birthday != null">
birthday = #{birthday},
</if>
<if test="sex != null and sex != ''">
sex = #{sex},
</if>
<if test="address != null and address != ''">
address = #{address},
</if>
</sql>
<!--测试if和where-->
<select id="selectUserByUsernameAndSex" resultType="user">
select * from user where 1=1
<include refid="query"/>
</select>
<!--where标签-->
<select id="selectUserByUsernameAndSex2" resultType="user">
select * from user
<!--自动去掉多余的and和or-->
<where>
<include refid="query"/>
</where>
</select>
<!--trim标签用法1-->
<select id="selectUserByUsernameAndSex3" resultType="user">
select * from user
<!--trim标签的意识就是去掉前缀为where后多余的and和or-->
<trim prefix="where" prefixOverrides="and|or">
<include refid="query"/>
</trim>
</select>
<!--利用更新测试set标签-->
<update id="updateById" parameterType="int">
update user
<!--set标签自动去除多余,-->
<set>
<include refid="update"/>
</set>
</update>
<!--trim标签用法2-->
<update id="updateById2" parameterType="int">
update user
<!--前缀为set，自动去掉后缀多余的,-->
<trim prefix="set" suffixOverrides=",">
<include refid="update"/>
</trim>
</update>
<!--foreach标签，批量删除-->
<delete id="deleteSome">
delete from user where id in
<foreach collection="list" item="xx" open="(" separator="," close=")">
<!--delete from user where id in (10,4,29,30)-->
#{xx}
</foreach>
</delete>
<!--传入map,多条件删除-->
<delete id="deleteSome2">
delete from user where id in
<foreach collection="ids" item="xx" open="(" separator="," close=")">
<!--delete from user where id in (10,4,29,30)-->
#{xx}
</foreach>
or username like #{username}
</delete>
</mapper>2.5TestMybatis
package com.weng.test;
import com.weng.bean.User;
import com.weng.mapper.UserMapper;
import com.weng.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.omg.CORBA.PUBLIC_MEMBER;
import java.io.IOException;
import java.util.*;
public class testMybatis {
SqlSession session = null;
UserMapper um = null;
//@Before表示每次调用测试方法之前先调用@Before注解的方法init
@Before
public void init(){
//UserMapperImpl um = new UserMapperImpl();
//myBatis在底层使用动态代理（反射）自动生成Mapper实现类，不需要人工写
session = MyBatisUtils.getSession();
um = session.getMapper(UserMapper.class);
//um就是自动生成的实现类
}
@Test
public void testSelectUserBUsernameAndSex(){
User user = new User();
user.setUsername("haha");
user.setSex("女");
List<User> users = um.selectUserByUsernameAndSex(user);
for (User u : users) {
System.out.println(u);
}
}
@Test
public void testSelectUserByUsernameAndSex2(){
User user = new User();
user.setUsername("haha");
user.setSex("女");
List<User> users = um.selectUserByUsernameAndSex2(user);
for (User u : users) {
System.out.println(u);
}
}
@Test
public void testSelectUserByUsernameAndSex3(){
User user = new User();
user.setUsername("haha");
user.setSex("女");
List<User> users = um.selectUserByUsernameAndSex3(user);
for (User u : users) {
System.out.println(u);
}
}
@Test
public void testUpdateByid(){
User user = new User();
user.setUsername("haha2");
user.setSex("妖");
int i = um.updateById(user);
System.out.println(i>0?"成功":"失败");
}
@Test
public void testUpdateByid2(){
User user = new User();
user.setUsername("haha3");
user.setSex("妖");
int i = um.updateById2(user);
System.out.println(i>0?"成功":"失败");
}
@Test
public void testDeleteSome(){
List<Integer> ids = new ArrayList<Integer>();
ids.add(10);
ids.add(20);
ids.add(21);
ids.add(25);
int count = um.deleteSome(ids);
System.out.println(count);
}
@Test
public void testDeleteSome2(){
int[] ids = {22,24,26,28};
Map<String,Object> map = new HashMap<String, Object>();
map.put("ids",ids);
map.put("username","%jiu%");
int count = um.deleteSome2(map);
System.out.println(count);
}
//@After表示每次调用测试方法之后都会调用@After注解的方法destory
@After
public void destory(){
session.commit();
MyBatisUtils.closeSession(session);
}
}
3关联映射
3.1类要加属性
3.2自己写resultmap
collection标签：集合 ofType
association标签：单个 JavaType
4mybatis性能优化
4.1延迟加载
resultMap中association和collection具有延迟加载功能
延迟加载：关联查询时，先加载主信息，需要关联信息的时候再去加载关联信息。因为查一个表速度快，查多表速度慢。
mybatis默认不开启延迟加载，需要手动开启
通过对全局参数：lazyLoadingEnabled进行设置，默认就是false。 进行设置修改延时加载状态
<settings>
<!-- 延迟加载总开关 -->
<setting name="lazyLoadingEnabled" value="true"/>
<!-- 侵入式延迟加载开关 -->
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
- 侵入式延迟：执行对主加载对象的查询时，不会执行对关联对象的查询。但当要访问主加载对象的某个属性（该属性不是关联对象的属性）时，就会马上执行关联对象的select查询。
- 深度延迟：执行对主加载对象的查询时，不会执行对关联对象的查询。访问主加载对象的详情时也不会执行关联对象的select查询。只有当真正访问关联对象的详情时，才会执行对关联对象的select查询。
4.2缓存
第一次查询的时候会在缓存中存放数据，后面查询时先查询缓存，缓存中没有再去查询数据库。
一级缓存：就是指sqlsession，其中有个map结构的数据区域，key是由sql语句，条件，statement等信息组成的唯一值，value就是查询出的结果对象。默认开启
二级缓存：就是同一个namespace下的mapper，也是map。需要手动开启，二级缓存范围更大。公用一个session工厂，可以夸session。
二级缓存开关：开启所有的二级缓存
<setting name="cacheEnabled" value="true"/>二级缓存小开关：单独关一个select
<select ..... useCache="false"/>刷新二级缓存：flushCache
select默认是false，但如果是insert,update,delete建议设置为true
<insert ...... flushCache="true"/>清除缓存：session.clearCache，提交事务也会自动清空缓存（增删改的时候数据库会发生改变）
4076
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
