MyBatis提高

MyBatis提高

一、输入映射和输出映

1. parameterType(输入类型)
  • 传递简单类型
    在这里插入图片描述
  • 传递pojo对象
    在这里插入图片描述
  • 传递pojo包装类
    在这里插入图片描述
    • 包装类的编写
      package com.lld.MyBatis.Model;
      
      import java.io.Serializable;
      
      public class QueryVo implements Serializable{
      
      	/**
      	 * 
      	 */
      	private static final long serialVersionUID = 1L;
      
      	private User user;
      
      	public void setUser(User user) {
      		this.user = user;
      	}	
      }
      
    • 调用
      @Test
      	public void testMapperFindUserByName() throws Exception {
      		//加载核心配置文件
      		String resource = "sqlMapConfig.xml";
      		InputStream in = Resources.getResourceAsStream(resource);
      		//创建SQLsessionFactory
      		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
      		//创建SQLsession
      		SqlSession session = sqlSessionFactory.openSession();
      		//使用session获取UserMapper的实现类(传进去一个接口返回一个接口类型的,但是底层已经帮你创建好了实现类)
      		UserMapper userMapper = session.getMapper(UserMapper.class);
      		//调用方法
      		User user = new User();
      		user.setUsername("五");
      		QueryVo vo = new QueryVo();
      		vo.setUser(user);
      		List<User> list = userMapper.fingUserByUsername(vo);
      		//遍历获得数据
      		for (User u : list) {
      			System.out.println(u);
      			
      		}
      	}
      
2. resultType(输出类型)
  • 简单类型
    在这里插入图片描述
  • 输出pojo对象
    在这里插入图片描述
  • 输出pojo列表
    在这里插入图片描述
  • resultType
<mapper namespace="com.lld.MyBatis.Mapper.OrdersMapper">
	<!-- 
			resultMap里的id要和select里的resultMap相同
			在类型不匹配的时候使用resultMap
				resultMap里的result只需要添加名称不匹配的列即可
	 -->
	<resultMap type="Orders" id="orders">
		<!-- 
				column:数据库表中的值
				property:编写映射model中的值
		 -->
		<result column="user_id" property="userId"/>
	</resultMap>
	<select id="selectOrdersList" resultMap="orders">
		SELECT id, user_id, number, createtime, note FROM orders
	</select>
</mapper>

二、动态SQL

if和where
	<!-- 根据性别和名字查询用户 -->
	<select id="findUserBySexAndUsername" parameterType="User" resultType="User">
		select * from user
		<!-- where标签可以自动添加where,同时处理sql语句中第一个and关键字 -->
		<where>
			<!-- 判断是否为空和是否为空字符串 -->
			<if test="sex != null and sex != ''">
				and sex = #{sex}
			</if>
			<if test="username != null and username != ''">
				and username = #{username}
			</if>
		</where>
	</select>
sql片段

将一些公共的SQL语句提取成为一个SQL片段,在需要用的时候将其引入

  • 引用本mapper中的SQL片段
    在这里插入图片描述
  • 引用别的mapper中的SQL片段

在refid前面加上对应的Mapper.xml的namespace
在这里插入图片描述

foreach
  • UserMapper.java

public List findUserByIDs(QueryVo vo);

  • UserMapper.xm;
<!-- 根据多个id查询用户信息 -->
	<select id="findUserByIDs" parameterType="QueryVo" resultType="User">
		<include refid="userFields"></include>
		<where>
			id in
			<!-- 
				collection="listids"		:遍历QueryVo里面的listids
				item="id"					:遍历出来的值赋给id 
				open="(" 					:在开始便利时加(
				close=")" 					:在结束便利时加)
				separator=","				:在便利出一个值后加一个,
						最后出现的结果(1,2,3....)
			 -->
			<foreach collection="listids" item="id" separator="," open="(" close=")">
				#{id}
			</foreach>
		</where>
	</select>
  • Queryvo.java
    在这里插入图片描述
  • Junit.java
	@Test
	//根据多个id查询用户信息
	public void testMapperfindUserByIDs() throws Exception {
		//加载核心配置文件
		String resource = "sqlMapConfig.xml";
		InputStream in = Resources.getResourceAsStream(resource);
		//创建SQLsessionFactory
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
		//创建SQLsession
		SqlSession session = sqlSessionFactory.openSession();
		//使用session获取UserMapper的实现类(传进去一个接口返回一个接口类型的,但是底层已经帮你创建好了实现类)
		UserMapper userMapper = session.getMapper(UserMapper.class);
		QueryVo vo = new QueryVo();
		List<Integer> list = new ArrayList<Integer>();
		list.add(26);
		list.add(27);
		list.add(30);
		vo.setListids(list);
		List<User> users = userMapper.findUserByIDs(vo);
		for (User user : users) {
			System.out.println(user);
		}
	}
  • 注意
    在这里插入图片描述

三、关联查询

一对一关联查询
  • ordersMapper.xml文件
<!-- 
			SELECT
				o.id,
				o.user_id,
				o.number,
				o.createtime,
				o.note,
				u.username,
				u.address
			FROM orders o
			LEFT JOIN USER u 
			ON o.user_id = u.id
	 -->
	 <!-- 当配置一对一时,只要resultMap中出现association,则orders表里的所有的字段都要进行配置 -->
	 <resultMap type="Orders" id="order">
	 	<id column="id" property="id"/>
	 	<result column="user_id" property="userId"/>
	 	<result column="number" property="number"/>
	 	<result column="createtime" property="createtime"/>
	 	<result column="note" property="note"/>
	 	<!-- 
	 	
	 		配置一对一
	 			property:对应orders.java中的user
	 			javaType:对应User.java
	 	 -->
	 	<association property="user" javaType="User">
	 		<id column="user_id" property="id"/>
	 		<result column="username" property="username"/>
	 	</association>
	 </resultMap>
	 <select id="selectOrders" resultMap="order">
	 	SELECT
			o.id,
			o.user_id,
			o.number,
			o.createtime,
			o.note,
			u.username,
			u.address
		FROM orders o
		LEFT JOIN user u 
		ON o.user_id = u.id
	 </select>
  • OrdersMapper.java文件
	//查询所有订单信息,关联查询下单用户信息。
	public List<Orders> selectOrders();
  • Junit.java文件
@Test
	//查询所有订单信息,关联查询下单用户信息。
	public void testMapperselectOrders() throws Exception {
		//加载核心配置文件
		String resource = "sqlMapConfig.xml";
		InputStream in = Resources.getResourceAsStream(resource);
		//创建SQLsessionFactory
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
		//创建SQLsession
		SqlSession session = sqlSessionFactory.openSession();
		//使用session获取OrdersMapper的实现类(传进去一个接口返回一个接口类型的,但是底层已经帮你创建好了实现类)
		OrdersMapper ordersMapper = session.getMapper(OrdersMapper.class);
		List<Orders> orders = ordersMapper.selectOrders();
		for (Orders order : orders) {
			System.out.println(order);
		}
	}
  • 运行结果
    在这里插入图片描述
一对多关联查询
  • ordersMapper.xml
	 <!--  -->
	 <resultMap type="User" id="user">
	 	<id column="user_id" property="id"/>
	 	<result column="username" property="username"/>
	 	<!-- 
	 		配置一对多
	 			property:对应user.java中的user
	 			ofType:对应user.java里面的list集合里面的泛型
	 	 -->
	 	<collection property="ordersList" ofType="Orders">
	 		<id column="id" property="id"/>
	 		<result column="number" property="number"/>
	 		<result column="createtime" property="createtime"/>
	 	</collection>
	 </resultMap>
	 <select id="selectUsers" resultMap="user">
	 	SELECT
			o.id,
			o.user_id,
			o.number,
			o.createtime,
			o.note,
			u.username
		FROM user u 
		LEFT JOIN orders o
		ON o.user_id = u.id
	 </select>
  • ordersMapper.java

//查询所有用户信息及用户关联的订单信息。
public List selectUsers();

  • User.java
    在这里插入图片描述
  • Junit.java
	@Test
	//查询所有用户信息及用户关联的订单信息。
	public void testMapperselectUser() throws Exception {
		//加载核心配置文件
		String resource = "sqlMapConfig.xml";
		InputStream in = Resources.getResourceAsStream(resource);
		//创建SQLsessionFactory
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
		//创建SQLsession
		SqlSession session = sqlSessionFactory.openSession();
		//使用session获取OrdersMapper的实现类(传进去一个接口返回一个接口类型的,但是底层已经帮你创建好了实现类)
		OrdersMapper ordersMapper = session.getMapper(OrdersMapper.class);
		List<User> Users = ordersMapper.selectUsers();
		for (User user : Users) {
			System.out.println(user);
		}
	}

四、MyBatis整合Spring

传统方式整合
1.创建项目
2.导入jar包

在这里插入图片描述

3.创建sqlMapConfig.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>
	<!-- 引入配置文件 -->
	<mappers>
		<mapper resource="mybatis/user.xml"/>
	</mappers>
</configuration>

4.创建applicationContext.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:context="http://www.springframework.org/schema/context" xmlns:p="http://www.springframework.org/schema/p"
	xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
	http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd
	http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.0.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd
	http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.0.xsd">
	<!-- 加载配置文件 -->
   <context:property-placeholder location="classpath:jdbc.properties" />

	<!-- 数据库连接池 -->
	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
		destroy-method="close">
		<property name="driverClassName" value="${jdbc.driver}" />
		<property name="url" value="${jdbc.url}" />
		<property name="username" value="${jdbc.username}" />
		<property name="password" value="${jdbc.password}" />
		<!-- 连接池的最大数据库连接数 -->
		<property name="maxActive" value="10" />
		<!-- 最大空闲数 -->
		<property name="maxIdle" value="5" />
	</bean>

	<!-- 配置SqlSessionFactory -->
	<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
		<!-- 配置DataSource -->
		<property name="dataSource" ref="dataSource"/>
		<!-- 加载MyBatis核心配置文件 -->
		<property name="configLocation" value="classpath:sqlMapConfig.xml"/>
		<!-- 配置package包扫描 -->
		<property name="typeAliasesPackage" value="com.lld.model"/>
	</bean>
	
	<!-- 传统Dao配置 -->
	<bean class="com.lld.Dao.Impl.UserDaoImpl">
		<property name="sqlSessionFactory" ref="sqlSessionFactory"/>
	</bean>
</beans>
5. 创建model
package com.lld.model;

import java.io.Serializable;
import java.util.Date;

public class User implements Serializable {
	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	private Integer id;
	private String username;// 用户姓名
	private String sex;// 性别
	private Date birthday;// 生日
	private String 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 String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	public Date getBirthday() {
		return birthday;
	}
	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	@Override
	public String toString() {
		return "User [id=" + id + ", username=" + username + ", sex=" + sex
				+ ", birthday=" + birthday + ", address=" + address + "]";
	}

}

6. 创建user.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">
<!-- namespace:命名空间,用于隔离sql语句,后继有重要重要
	 #{}:点位符,相当于jdbc的?
	 ${}:字符串拼接指令,如果入参为普通数据类型{}内部只写value
-->
<mapper namespace="user">

	<!-- id:sql id,语句的唯一标识
		 parameterType:入参的数据类型
		 resultType:返回结果的数据类型
	-->
	<select id="getUserById" parameterType="Integer" resultType="User">
		SELECT
		  `id`,
		  `username`,
		  `birthday`,
		  `sex`,
		  `address`
		FROM `user`
		WHERE id = #{id2}
	</select>
</mapper>
7.创建dao以及daoimpl
  • dao
package com.lld.Dao;

import com.lld.model.User;

public interface UserDao {
	public User findUserById(Integer id);
}

  • daoimpl
package com.lld.Dao.Impl;

import org.apache.ibatis.session.SqlSession;

import org.mybatis.spring.support.SqlSessionDaoSupport;

import com.lld.Dao.UserDao;
import com.lld.model.User;

public class UserDaoImpl extends SqlSessionDaoSupport implements UserDao {

	@Override
	public User findUserById(Integer id) {
		//获得session
		SqlSession session = this.getSqlSession();
		User user = session.selectOne("user.getUserById", id);
		//将User返回
		return user;
	}

}
8.创建测试类
package com.lld.test;



import static org.junit.Assert.*;

import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.lld.Dao.UserDao;
import com.lld.model.User;

public class UserDaoTest {

	private ApplicationContext applicationContext;
	
	@Before
	public void init(){
		applicationContext = new ClassPathXmlApplicationContext("classpath:applicationContext.xml");
	}
	
	@Test
	public void testFindUserById() {
		UserDao userDao = applicationContext.getBean(UserDao.class);
		User user = userDao.findUserById(30);
		System.out.println(user);
	}

}

动态代理开发(使用mapper)
1.创建mapper
  • usermapper.java
package com.lld.mapper;

import com.lld.model.User;

public interface UserMapper {
	/*
	 * 使用动态代理开发mybatis要遵循四大原则
	 * 1. 方法名要与User.xml中的每个sql的id相同
	 * 2. 返回值类型要与调用sql的resultType相同
	 * 3. 输入类型要与调用sql的parameterType相同
	 * 4. mapper中的namespace要是mapper接口的全路径地址
	 */
	public User fingUserById(Integer id);
}
  • usermapper.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">
<!-- 
	namespace:在调用的时候使用(namespace.id)
 -->
<mapper namespace="com.lld.mapper.UserMapper">
	<!-- 
		配置select:通过id查询一个用户
		parameterType:输入参数类型
		resultType:返回值类型
	 -->
	<select id="fingUserById" parameterType="Integer" resultType="User">
		select * from user where id = #{v}
	</select>
</mapper>
2.在applicationContest.xml里面添加以下内容
<!-- 动态代理配置方式一 -->
	<!-- <bean id="baseMapper" class="org.mybatis.spring.mapper.MapperFactoryBean" abstract="true" lazy-init="true">
      <property name="sqlSessionFactory" ref="sqlSessionFactory" />
    </bean> -->
 	<!-- 配置一个包的接口 -->
   <!--  <bean parent="baseMapper">
      <property name="mapperInterface" value="com.lld.mapper.UserMapper" />
    </bean> -->
       
    <!-- 动态代理配置方式二(推荐) -->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
    	<property name="basePackage" value="com.lld.mapper"/>
    </bean>
3.创建测试类
package com.lld.test;

import static org.junit.Assert.*;

import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.lld.mapper.UserMapper;
import com.lld.model.User;

public class UserMapperTest {

	private ApplicationContext applicationContext;
	
	@Before
	public void init(){
		applicationContext = new ClassPathXmlApplicationContext("classpath:applicationContext.xml");
	}
	
	@Test
	public void testFingUserById() {
		UserMapper userMapper = applicationContext.getBean(UserMapper.class);
		User user = userMapper.fingUserById(30);
		System.out.println(user);
	}
}

五、MyBatis逆向工程

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值