mybatis官方文档:
csdn里的mybatis中文文档:http://t.csdnimg.cn/pmobD
包结构:
基于xml实现mybatis:
一、导入mybatis坐标
<!-- mybatis依赖-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.13</version>
</dependency>
二、创建数据库表和实体类
根据下方实体类创间数据库表
三、创建Mapper层
package com.demo.mapper;
import com.demo.domain.TUser;
import java.util.List;
/**
* @Entity com.demo.domain.TUser
*/
public interface TUserMapper {
int deleteByPrimaryKey(Long id);
int insert(TUser record);
int insertSelective(TUser record);
TUser selectByPrimaryKey(Long id);
int updateByPrimaryKeySelective(TUser record);
int updateByPrimaryKey(TUser record);
List<TUser> selectAll();
List<TUser> selectIf(String name);
List<TUser> selectCWO(TUser tUser);
List<TUser> selectWhere(TUser tUser);
int updateSet(TUser tUser);
}
四、编写mapper映射文件(sql)
<?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.demo.mapper.TUserMapper">
<resultMap id="BaseResultMap" type="com.demo.domain.TUser">
<id property="id" column="id" jdbcType="INTEGER"/>
<result property="name" column="name" jdbcType="VARCHAR"/>
<result property="money" column="money" jdbcType="DOUBLE"/>
</resultMap>
<sql id="Base_Column_List">
id,name,money
</sql>
<select id="selectByPrimaryKey" parameterType="java.lang.Long" resultType="com.demo.domain.TUser">
select
<include refid="Base_Column_List"/>
from t_user
where id = #{id,jdbcType=INTEGER}
</select>
<select id="selectAll" resultType="com.demo.domain.TUser">
select *
from t_user
</select>
<!-- <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap">-->
<!-- select-->
<!-- <include refid="Base_Column_List"/>-->
<!-- from t_user-->
<!-- where id = #{id,jdbcType=INTEGER}-->
<!-- </select>-->
<delete id="deleteByPrimaryKey" parameterType="java.lang.Long">
delete from t_user
where id = #{id,jdbcType=INTEGER}
</delete>
<insert id="insert" keyColumn="id" keyProperty="id" parameterType="com.demo.domain.TUser" useGeneratedKeys="true">
insert into t_user
( name,money
)
values (#{name,jdbcType=VARCHAR},#{money,jdbcType=DOUBLE}
)
</insert>
<!--动态sql-->
<!-- if: 查询money>1000 且名字模糊查询-->
<select id="selectIf" resultType="com.demo.domain.TUser" parameterType="java.lang.String">
SELECT *
FROM t_user
WHERE money >1000
<if test="name != null">
AND name like CONCAT('%', #{name}, '%')
</if>
</select>
<!-- choose、when、otherwise -->
<select id="selectCWO" resultType="com.demo.domain.TUser" parameterType="com.demo.domain.TUser">
SELECT * FROM t_user
<choose>
<when test="name!=null">
where name = #{name}
</when>
<when test="money!=null">
where money > #{money}
</when>
</choose>
</select>
<!-- trim、where、set-->
<!-- where-->
<select id="selectWhere" resultType="com.demo.domain.TUser" parameterType="com.demo.domain.TUser">
SELECT * FROM t_user
<where>
<if test="name!=null">
name = #{name}
</if>
<if test="money!=null">
money > #{name}
</if>
</where>
</select>
<!--set-->
<update id="updateSet" parameterType="com.demo.domain.TUser">
update t_user
<set>
<if test="name != null">name =#{name},</if>
<if test="money != null">money=#{money}</if>
</set>
where id = #{id}
</update>
<insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="com.demo.domain.TUser"
useGeneratedKeys="true">
insert into t_user
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="id != null">id,</if>
<if test="name != null">name,</if>
<if test="money != null">money,</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="id != null">#{id,jdbcType=INTEGER},</if>
<if test="name != null">#{name,jdbcType=VARCHAR},</if>
<if test="money != null">#{money,jdbcType=DOUBLE},</if>
</trim>
</insert>
<update id="updateByPrimaryKeySelective" parameterType="com.demo.domain.TUser">
update t_user
<set>
<if test="name != null">
name = #{name,jdbcType=VARCHAR},
</if>
<if test="money != null">
money = #{money,jdbcType=DOUBLE},
</if>
</set>
where id = #{id,jdbcType=INTEGER}
</update>
<update id="updateByPrimaryKey" parameterType="com.demo.domain.TUser">
update t_user
set
name = #{name,jdbcType=VARCHAR},
money = #{money,jdbcType=DOUBLE}
where id = #{id,jdbcType=INTEGER}
</update>
</mapper>
映射TUserMapper接口
方法id与mapper层的方法一一对应
parameterType="java.lang.Long",调用该方法时的参数类型
resultType="com.demo.domain.TUser" ,查询数据库后返回的数据类型
五、编写properties文件
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8
jdbc.username=root
jdbc.password=wsy
六、编写mybatis全局配置文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<properties resource="jdbc.properties"></properties>
<settings>
<setting value="log4j" name="logImpl"/>
</settings>
<!--起别名-->
<typeAliases>
<!-- <typeAlias type="org.example.one.bean.Student" alias="student"/>-->
<package name="com.demo"/>
</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>
<mapper resource="mapper/TUserMapper.xml"/>
</mappers>
</configuration>
映射器:TUserMapper.xml 映射文件,(加载mybatis时从哪找sql语句)
测试:
import com.demo.domain.TUser;
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 javax.annotation.Resource;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class MybatisTest {
public static void main(String[] args) throws IOException {
//加载核心配置文件
InputStream resourceAsStream = Resources.getResourceAsStream("mybatisConfig.xml");
//获取Sqlsession工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
//通过工厂获取SqlSession对象
SqlSession sqlSession=sqlSessionFactory.openSession();
//执行映射文件中的sql语句,并接收结果
// 查询操作
TUser tUser=sqlSession.selectOne("com.demo.mapper.TUserMapper.selectByPrimaryKey",1l);
System.out.println(tUser);
}
}
插件的使用:
MybatisX
1.idea提前连接数据库
2.右击相应的表,构造初始mybatis文件(pojo类,mapper接口,mapper.xml)
base packge 是你要将创建的文件放在哪个包下面
常用动态sql:
优先加载全局配置文件,生成sqlsession
//加载核心配置文件
InputStream resourceAsStream = Resources.getResourceAsStream("mybatisConfig.xml");
//获取Sqlsession工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
//通过工厂获取SqlSession对象
SqlSession sqlSession=sqlSessionFactory.openSession();
if
MybatisTest:
//查询money>1000 且名字模糊查询
List<TUser> users = sqlSession.selectList("com.demo.mapper.TUserMapper.selectIf","w");
for (TUser user:users
) {
System.out.println(user);
}
sqlSession.close();
TUserMapper:
<!-- if: 查询money>1000 且名字模糊查询-->
<select id="selectIf" resultType="com.demo.domain.TUser" parameterType="java.lang.String">
SELECT *
FROM t_user
WHERE money >1000
<if test="name != null">
AND name like CONCAT('%', #{name}, '%')
</if>
</select>
如果传入数据没有name,则AND的SQl语句不执行,反之AND执行
choose、when、otherwise
MybatisTest:
TUser tUser = new TUser();
//tUser.setName("wsy2");
tUser.setMoney(2000d);
List<TUser> users = sqlSession.selectList("com.demo.mapper.TUserMapper.selectCWO",tUser);
for (TUser user: users
) {
System.out.println(user);
}
sqlSession.close();
TUserMapper:
<select id="selectCWO" resultType="com.demo.domain.TUser" parameterType="com.demo.domain.TUser">
SELECT * FROM t_user
<choose>
<when test="name!=null">
where name = #{name}
</when>
<when test="money!=null">
where money > #{money}
</when>
<otherwise>
AND featured = 1
</otherwise>
</choose>
</select>
类似java的switch语句,如果传入name数据,就按name属性去查找,如果传入money属性,就按照muney属性去查找,如果两个属性都没有传入,若两者都没有传入,就返回标记为 featured 的 BLOG
where、trim
MybatisTest:
//where
TUser tUser = new TUser();
tUser.setName("张三");
tUser.setMoney(10000d);
List<TUser> tUsers = sqlSession.selectList("com.demo.mapper.TUserMapper.selectWhere", tUser);
for (TUser user:tUsers
) {
System.out.println(user);
}
sqlSession.close();
TUserMapper:
<!-- where-->
<select id="selectWhere" resultType="com.demo.domain.TUser" parameterType="com.demo.domain.TUser">
SELECT * FROM t_user
<where>
<if test="name!=null">
name = #{name}
</if>
<if test="money!=null">
AND money > #{money}
</if>
</where>
</select>
where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且,若子句的开头为 “AND” 或 “OR”,where 元素也会将它们去除。
如果 where 元素与你期望的不太一样,你也可以通过自定义 trim 元素来定制 where 元素的功能。比如,和 where 元素等价的自定义 trim 元素为:
<trim prefix="WHERE" prefixOverrides="AND |OR ">
...
</trim>
set、trim
MybatisTest:
TUser tUser = new TUser();
tUser.setName("张三");
tUser.setId(3l);
int update = sqlSession.update("com.demo.mapper.TUserMapper.updateSet", tUser);
System.out.println(update);
sqlSession.commit();
sqlSession.close()
TUserMapper:
<!--set-->
<update id="updateSet" parameterType="com.demo.domain.TUser">
update t_user
<set>
<if test="name != null">name =#{name},</if>
<if test="money != null">money=#{money}</if>
</set>
where id = #{id}
</update>
set 元素会动态地在行首插入 SET 关键字,并会删掉额外的逗号,
假设只传入name,数据也会更行,只是更新name
可以通过使用trim元素来达到同样的效果:
<trim prefix="SET" suffixOverrides=",">
...
</trim>
spring整合mybatis
1.导入mybatis和spring集成依赖,和mybatis依赖
<!-- mybatis依赖-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.13</version>
</dependency>
<!-- mybatis和spring集成依赖 -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.3.1</version>
</dependency>
2、如xml实现mybatis创建出基本的包结构,并添加一个service层
包结构
编写service接口及其实现类
package com.demo.service.impl;
import com.demo.domain.TUser;
import com.demo.mapper.TUserMapper;
import com.demo.service.TUserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service("userService")
public class TUserServiceImpl implements TUserService {
@Autowired
private TUserMapper tUserMapper;
@Override
public int deleteByPrimaryKey(Long id) {
return tUserMapper.deleteByPrimaryKey(id);
}
@Override
public int insert(TUser record) {
return tUserMapper.insert(record);
}
@Override
public int insertSelective(TUser record) {
return tUserMapper.insertSelective(record);
}
@Override
public TUser selectByPrimaryKey(Long id) {
return tUserMapper.selectByPrimaryKey(id);
}
@Override
public int updateByPrimaryKeySelective(TUser record) {
return tUserMapper.updateByPrimaryKeySelective(record);
}
@Override
public int updateByPrimaryKey(TUser record) {
return tUserMapper.updateByPrimaryKey(record);
}
@Override
public List<TUser> selectAll() {
return tUserMapper.selectAll();
}
@Override
public List<TUser> selectIf(String name) {
return tUserMapper.selectIf(name);
}
@Override
public List<TUser> selectCWO(TUser tUser) {
return tUserMapper.selectCWO(tUser);
}
@Override
public List<TUser> selectWhere(TUser tUser) {
return tUserMapper.selectWhere(tUser);
}
@Override
public int updateSet(TUser tUser) {
return tUserMapper.updateSet(tUser);
}
}
3、编写spring配置类
applicationContext.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd
">
<!-- 注解包扫描-->
<context:component-scan base-package="com.demo"></context:component-scan>
<!-- 加载外部属性配置文件 -->
<context:property-placeholder location="classpath:jdbc.properties"/>
<!-- 数据源-->
<!-- 声明数据源DataSource -->
<bean id="myDataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</bean>
<!-- 声明SqlSessionFactoryBean,在这个类的内部,创建SqlSessionFactory对象,之后就可以获取SqlSession对象 -->
<bean id="factory" class="org.mybatis.spring.SqlSessionFactoryBean">
<!-- 指定数据源 -->
<property name="dataSource" ref="myDataSource"/>
<!-- 指定mybatis主配置文件 -->
<property name="configLocation" value="classpath:mybatisConfig.xml"/>
</bean>
<!-- MapperScannerConfigurer 作用扫描指定的包,产生mapper对象存储到spring容器-->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="com.demo.mapper"></property>
</bean>
</beans>
4、测试
import com.demo.domain.TUser;
import com.demo.service.TUserService;
import org.junit.jupiter.api.Test;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class MybatisTest3 {
@Test
public void Test1(){
ClassPathXmlApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
TUserService userService = (TUserService) applicationContext.getBean("userService");
TUser tUser = new TUser();
tUser.setName("迪迦");
tUser.setMoney(3000d);
int insert = userService.insert(tUser);
System.out.println(insert);
}
}