Mybatis入门案例

Mybatis案例具体实施

文件结构分析

mapper : 接口和映射,包含了封装的SQL的语句

pojo : 实体类

vo : 封装相关信息的类

util : 用于创建单例的数据回话对象

test : 测试专用

config : 配置文件

需要的jar包 : log4j-1.2.17.jar 用于打印日志

mybatis-3.2.7.jar mybatis基础包

mysql-connector-java-5.1.47.jar 数据库连接包

数据库使用了两张表

Order 和 User表

DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL COMMENT '下单用户id',
  `number` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单号',
  `createtime` datetime NOT NULL COMMENT '创建订单时间',
  `note` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `FK_orders_1`(`user_id`) USING BTREE,
  CONSTRAINT `FK_orders_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of orders
-- ----------------------------
INSERT INTO `orders` VALUES (3, 1, '1000010', '2019-07-04 13:22:35', NULL);
INSERT INTO `orders` VALUES (4, 1, '1000011', '2019-07-03 13:22:41', NULL);
INSERT INTO `orders` VALUES (5, 10, '1000012', '2019-07-12 16:13:23', NULL);
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户名称',
  `birthday` date NULL DEFAULT NULL COMMENT '生日',
  `sex` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '性别',
  `address` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 27 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '谢佳辰', '1996-01-27', '1', '安徽马鞍山');
INSERT INTO `user` VALUES (10, '徐青', '1996-10-30', '1', '安徽马鞍山');
INSERT INTO `user` VALUES (16, '吴观钱', '1997-04-20', '1', '安徽池州');
INSERT INTO `user` VALUES (22, '林爱国', '1995-11-10', '1', '安徽滁州');
INSERT INTO `user` VALUES (24, '徐勇', '1996-02-20', '1', '安徽合肥');
INSERT INTO `user` VALUES (25, '陈硕', '1997-01-10', '1', '安徽阜阳');
SET FOREIGN_KEY_CHECKS = 1;

准备config配置文件夹

log4j.properties 打印日志

# Global logging configuration
log4j.rootLogger=DEBUG, stdout
#log4j.rootLogger=stdout
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
​

jdbc.properties 数据库连接

jdbc.url=jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf-8
jdbc.password=root
jdbc.username=root
jdbc.driverClassName=com.mysql.jdbc.Driver

编写实体类

Order 和 User

Order类

package com.xiejiachen.pojo;
import java.util.Date;
​
public class Order {
    private Integer id;
    private Integer userId;
    private String number;
    private Date createtime;
    private String note;
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public Integer getUserId() {
        return userId;
    }
    public void setUserId(Integer userId) {
        this.userId = userId;
    }
    public String getNumber() {
        return number;
    }
    public void setNumber(String number) {
        this.number = number;
    }
    public Date getCreatetime() {
        return createtime;
    }
    public void setCreatetime(Date createtime) {
        this.createtime = createtime;
    }
    public String getNote() {
        return note;
    }
    public void setNote(String note) {
        this.note = note;
    }
    public Order(Integer id, Integer userId, String number, Date createtime, String note) {
        super();
        this.id = id;
        this.userId = userId;
        this.number = number;
        this.createtime = createtime;
        this.note = note;
    }
    public Order() {
        super();
    }
    @Override
    public String toString() {
        return "Order [id=" + id + ", userId=" + userId + ", number=" + number + ", createtime=" + createtime
                + ", note=" + note + "]";
    }
}

User类

package com.xiejiachen.pojo;
​
import java.util.Date;
​
public class User {
    private Integer id;
    private String name;
    private Date birthday;
    private String sex;
    private String address;
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    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;
    }
    public User(Integer id, String name, Date birthday, String sex, String address) {
        super();
        this.id = id;
        this.name = name;
        this.birthday = birthday;
        this.sex = sex;
        this.address = address;
    }
    public User() {
        super();
    }
    @Override
    public String toString() {
        return "User [id=" + id + ", name=" + name + ", birthday=" + birthday + ", sex=" + sex + ", address=" + address
                + "]";
    }
}

编写vo封装类

OrderQueryVo类

package com.xiejiachen.vo;
/*
 * 查询条件封装
 */
​
import java.util.Date;
​
import com.xiejiachen.pojo.User;
​
/**
 * @author XIEJIACHEN
 *
 */
public class OrderQueryVo {
    private User user;      //用户信息查询
    private String orderNo; //编号查询
    private Date orderDate; //创建时间查询
    public User getUser() {
        return user;
    }
    public void setUser(User user) {
        this.user = user;
    }
    public String getOrderNo() {
        return orderNo;
    }
    public void setOrderNo(String orderNo) {
        this.orderNo = orderNo;
    }
    public Date getOrderDate() {
        return orderDate;
    }
    public void setOrderDate(Date orderDate) {
        this.orderDate = orderDate;
    }
}
​

UserQueryVo类

package com.xiejiachen.vo;
​
import java.util.List;
​
import com.xiejiachen.pojo.User;
​
/**
 * @author XIEJIACHEN
 *
 */
public class UserQueryVo {
    
    private User user;
    private List<Integer> ids; //把需要查询的id封装到List当中
    //...
    public User getUser() {
        return user;
    }
    public void setUser(User user) {
        this.user = user;
    }
    public List<Integer> getIds() {
        return ids;
    }
    public void setIds(List<Integer> ids) {
        this.ids = ids;
    }
}
​

编写mapper对象关系映射

OrderMapper类

package com.xiejiachen.mapper;
​
import java.util.List;
​
import com.xiejiachen.pojo.Order;
import com.xiejiachen.vo.OrderQueryVo;
​
public interface OrderMapper {
    //根据清单号进行查询
    List<Order> selectOrders(OrderQueryVo queryVo);
    
    //查询所有的清单数量  select count(*) from orders
    int countOrders();
    
    //查询所有清单
    List<Order> selectAllOrders();  
}

OrderMapper.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="com.xiejiachen.mapper.OrderMapper">

 <!-- <select id="selectOrders" parameterType="string" resultType="order">
         select * from orders where number = #{number}
      </select> 
  -->
 <!-- 根据清单号查询 -->
 <!--  <select id="selectOrders" parameterType="orderQueryVo" resultType="order">
    select * from orders where number = #{orderNo}
  </select> -->
  
  <!-- 根据用户id进行查询 -->
  
  <select id="selectOrders" parameterType="orderQueryVo" resultType="order">
    select * from orders where user_id = #{user.id}
  </select> 
  
  <!-- 查询清单数量 -->
  <select id="countOrders" resultType="int">
  	select count(*) from orders
  </select>
  
  <!-- resutMap:当查询字段名与对象的属性名不一致的时候用于指定字段名与属性名的映射关系
  id:resultMap的唯一标识符
  type:最终结果集需要映射的对象类型
   -->
  <resultMap type="order" id="orderMap">
  	<!-- 指定字段名与属性名的映射关系 -->
  	
  	<!-- id用于指定主键的映射关系
  	column:主键对应的字段   property:主键对应的实体的属性
  	 -->
  	<id column="id" property="id"/>
  	<!-- result用于指定非主键的映射关系
  	column:非主键对应的字段   property:非主键对应的实体的属性
  	 -->
  	<result column="user_id" property="userId"/>
  	<result column="number" property="number"/>
  	<result column="createtime" property="createtime"/>
  	<result column="note" property="note"/>
  </resultMap>

  <!-- 查询所有清单 -->
  <!-- 1.通过给查询字段设置别名解决查询字段名与对象的属性名不一致的问题 -->
  <!-- <select id="selectAllOrders" resultType="order">
  	select id,user_id userId,number,createtime,note from orders
  </select> -->
  <select id="selectAllOrders" resultMap="orderMap">
  	select id,user_id,number,createtime,note from orders
  </select> 
</mapper>

UserMapper类

package com.xiejiachen.mapper;

import java.util.List;

import com.xiejiachen.pojo.User;
import com.xiejiachen.vo.UserQueryVo;

public interface UserMapper {
	
	/*
	 * 参数user:封装的查询条件
	 */
	//List<User> selectUsers(User user);
	
	List<User> selectUsers(UserQueryVo queryVo);
	
}

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">
<mapper namespace="com.xiejiachen.mapper.UserMapper">
	<!-- foreach:
	collection:需要遍历的集合
	item:当前遍历的元素
	open:以什么开头
	separator:每个元素的分隔符
	close:以什么结尾
	 -->
	<select id="selectUsers" parameterType="userQueryVo" resultType="user">
		<!-- 从User表中选出id为1,10,16的用户的信息 -->
		<!-- select * from user where id in (1,10,16) -->
		select <include refid="user_info_detail"></include> from user where id in
		<foreach item="id" collection="ids" open="(" separator="," close=")">
        	#{id}
  		</foreach>
	</select>
	 <!-- SQL片段:封装公共的SQL片段/语句
	 	  id: SQL片段唯一标识符
	 	<include refid="user_base_column"></include>
	 		根据sql片段的id引用sql片段
	  -->		
	<sql id="user_info_detail">
		id,username,birthday,sex,address
	</sql>	
</mapper>

编写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>
	<!--属性（properties） 用于加载外部属性配置文件 -->
	<properties resource="jdbc.properties"></properties>
	
	<!--类型别名（typeAliases)-->
	<typeAliases>
		<package name="com.xiejiachen.pojo"/>
		<package name="com.xiejiachen.vo"/>
	</typeAliases>

	<!-- mybatis环境配置 default:当前默认环境 -->
	<environments default="development">
		<environment id="development">
			<!-- 配置事务管理器 -->
			<transactionManager type="JDBC" />
			<!-- 使用mybatis默认数据源 -->
			<dataSource type="POOLED">
				<property name="driver" value="${jdbc.driverClassName}" />
				<property name="url" value="${jdbc.url}" />
				<property name="username" value="${jdbc.username}" />
				<property name="password" value="${jdbc.password}" />
			</dataSource>
		</environment>
	</environments>

	<!-- 配置映射文件:加载映射文件-->
	<mappers>
		<mapper class="com.xiejiachen.mapper.OrderMapper"/>
		<mapper class="com.xiejiachen.mapper.UserMapper"/>
	</mappers>
</configuration>

编写工具类

SqlSessionUtil

package com.xiejiachen.util;

import java.io.IOException;
import java.io.InputStream;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

/**
 * @author XIEJIACHEN
 *
 */
public class SqlSessionUtil {
	
	//单例对象(不对外提供构造方法,为私有)
	private static SqlSessionFactory sqlSessionFactory = null;
	
	public synchronized static SqlSessionFactory getSqlSessionFactoryInstance() {
		if(sqlSessionFactory==null) {
			String resource = "sqlMapConfig.xml";
			InputStream inputStream = null;
			try {
				inputStream = Resources.getResourceAsStream(resource);
				sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}	
		}
		return sqlSessionFactory;
	}
}

我们开始测试

Test类

例如测试OrderTest

package com.xiejiachen.test;

import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import com.xiejiachen.mapper.OrderMapper;
import com.xiejiachen.pojo.Order;
import com.xiejiachen.pojo.User;
import com.xiejiachen.util.SqlSessionUtil;
import com.xiejiachen.vo.OrderQueryVo;

public class OrderTest {

	// 查询清单
	@Test
	public void selectOrdersTest() {
		// 创建会话对象
		SqlSession sqlSession = SqlSessionUtil.getSqlSessionFactoryInstance().openSession();
		// 获取mapper代理对象
		OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);

		// 创建查询条件
		OrderQueryVo queryVo = new OrderQueryVo();
		// 1:根据清单号查询
		// 2:根据用户id查询该用户的清单信息
		User user = new User();
		user.setId(1);
		queryVo.setUser(user);

		// 执行查询方法
		List<Order> orders = orderMapper.selectOrders(queryVo);
		System.out.println("orders=>" + orders);

		// 关闭资源
		sqlSession.close();
	}

	// 查询清单数量
	@Test
	public void selectOrderCounts() {
		// 创建会话对象
		SqlSession sqlSession = SqlSessionUtil.getSqlSessionFactoryInstance().openSession();
		// 获取mapper代理对象
		OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);

		// 执行查询方法
		int nums = orderMapper.countOrders();
		System.out.println("nums=>" + nums);

		// 关闭资源
		sqlSession.close();
	}

	// 查询清单
	@Test
	public void selectAllOrders() {
		// 创建会话对象
		SqlSession sqlSession = SqlSessionUtil.getSqlSessionFactoryInstance().openSession();
		// 获取mapper代理对象
		OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);

		// 执行查询方法
		List<Order> orders = orderMapper.selectAllOrders();
		for(Order order : orders) {
			System.out.println("order=>" + order);
		}

		// 关闭资源
		sqlSession.close();
	}
}

扩展

SSM整合Mybatis

在resource的文件配置要包括文件夹Mybatis下的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>

</configuration>

在spring文件夹下的applicationContext-dao.xml

 <!--加载外部属性配置文件-->
    <context:property-placeholder location="classpath:jdbc.properties"></context:property-placeholder>

<!--配置sqlSessionFactory-->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <!--配置mybatis配置文件-->
        <property name="configLocation" value="classpath:mybatis/sqlMapConfig.xml"></property>
        <!--配置数据源对象-->
        <property name="dataSource" ref="dataSource"></property>
    </bean>

底下还要配置mapper的代理对象,以及扫描mapper接口,可以设置批量

<!--配置mapper的代理对象
    this.sqlSessionFactory.openSession().getMapper(UserMapper.class)
    -->
    <!--<bean id="userMapper" class="org.mybatis.spring.mapper.MapperFactoryBean">
        &lt;!&ndash;配置sqlSessionFactroy对象&ndash;&gt;
        <property name="sqlSessionFactory" ref="sqlSessionFactory"></property>
        &lt;!&ndash;配置接口&ndash;&gt;
        <property name="mapperInterface" value="com.xiejiachen.ssm.mapper.UserMapper"></property>
    </bean>

逆向工程

在gengerationConfig.xml文件中配置数据库的连接信息

po类和mapper类的文件名和位置

指定的数据库表

延迟加载

延迟加载需要配置文件

原理就是不一次性全部加载,需要加载的时候再加载.得到资源的合理利用

mybatis默认没有开启延迟加载，需要在SqlMapConfig.xml中setting配置

<settings>		
<!-- 打开延迟加载的开关 -->		
<setting name="lazyLoadingEnabled" value="true" />		
<!-- 将积极加载改为消息加载即按需加载 -->		
<setting name="aggressiveLazyLoading" value="false"/>	
</settings>