MyBatis(二)
一、输入输出映射
1、输入类型parameterType
1.1 传递基本类型
1.2 传递POJO对象
使用ONGL表达式解析对象字段的值,#{}或者${}中的值为POJO属性的名称 user.username
1.3 传递POJO包装对象QueryVo
查询条件是多个查询条件的综合
1.3.1 QueryVo
public class QueryVo { private User user; public User getUser() { returnuser; } publicvoid setUser(User user) { this.user = user; } }
1.3.2 UserMapper.xml
<!-- 使用包装类型查询用户 使用ognl从对象中取属性值,如果是包装对象可以使用.操作符来取内容部的属性 --> <select id="findUserByQueryVo" parameterType="com.guojie.pojo.QueryVo" resultType="user"> SELECT * FROM user where username like '%${user.username}%' and sex=#{user.sex} // 查询条件为多个时,入参类型需要为POJO
parameterType
</select>
1.3.3 UserMapper接口中
public List<User> findUserByQueryVo(QueryVo vo);
1.3.4 测试
@Test public void testFindUserByQueryVo() throws Exception { SqlSession sqlSession = sessionFactory.openSession(); //获得mapper的代理对象,通过getMapper方法实例化接口 UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
//创建QueryVo对象 QueryVo queryVo = new QueryVo(); //创建user对象 User user = new User(); user.setUsername("刘"); user.setSex("1"); queryVo.setUser(user);
//根据queryvo查询用户 List<User> list = userMapper.findUserByQueryVo(queryVo); System.out.println(list); sqlSession.close();
}
2、输出类型resultType
2.1 输出基本类型
2.1.1 UserMapper.xml
<!-- 获取用户列表总数 --> <select id="findUserCount" resultType="int"> select count(*) from user </select>
2.1.2 UserMapper接口
public int findUserCount() throws Exception;
2.1.3 测试
Public void testFindUserCount() throws Exception{ //获取session SqlSession session = sqlSessionFactory.openSession(); //获取mapper接口实例 UserMapper userMapper = session.getMapper(UserMapper.class); int count = userMapper.findUserCount(); //关闭session session.close(); }
2.2 输出POJO对象
2.3 输出POJO列表
3、resultMap(用List可取代)
resultType需要POJO属性和sql查询的列名一致才能将查询结果映射为POJO,自动映射
resultMap在POJO属性和sql查询的列名不一致时也行,可用于映射复杂类型的POJO,需要手动配置
<select id="selectOrderList" resultMap="orders"> select user_id, user_name from user </select>
<resultMap type="Orders" id="orders"> <result column="user_id" property="userId"> </resultMap>
id:结果集的唯一标识
property:POJO类的属性
column:sql字段
二、动态sql
通过MyBatis提供的各种标签方法实现动态拼接sql
1、<if>&<where>
<select id="findUserByNameAndSex" parameterType="cn.guojie.pojo.User" resultType="cn.guojie.pojo.User"> <where> <if test="username!=null and username!=' ' "> and username like '%${username}%' </if> <if test="sex!=null and sex!=' ' "> and sex=#{sex} </if> <where> </select>
where作用:添加where关键字,去掉第一个条件的and关键字
条件中username为POJO的属性
2、sql片段<sql>
提取重复的sql条件封装重用
<sql id="userFileds"> 重复代码 </sql>
<select> <!--调用sql条件 --> <include refid="userField" /> </select>
3、<foreach>
向sql传数组或list
<select id="findUserByIds" parameterType="cn.guojie.pojo.QueryVo" resultType="com.guojie.pojo.User"> <where> <!-- ids为QueryVo中的List集合属性 --> <if test="ids"!=null> <foreach collection="ids" item="id" open="id in (" close=")“ seperator="," ”> #{id} </foreach> </if> </where> </select>
等同于select * from user whereid in (1, 2, 3)
collection:传入的集合变量名
item:循环的数据存入变量
open:开始拼接的字符串
close:结束拼接的字符串
seperator:分隔符
sql中不要有or,会造成性能下降,用in
@Test public void testFindUserByIds() throws Execption { SqlSession openSession = factory.openSession(); // 通过getMapper方法实例化接口 UserMapper mapper = openSession.getMapper(UserMapper.class); QueryVo vo = new QueryVo(); List<Integer> ids = new ArrayList<Integer>(); ids.add(1); ids.add(2); ids.add(3); vo.setIds(ids); List<User> list = mapper.findUserByIds(vo); }
三、关联查询
多中有一的属性引用,一中有多的集合引用
1、一对一查询
以订单为中心,单个订单只能属于一个用户,相对用户一对一,订单表以用户表的id为外键
互联网一般单表查询多,关联查询较少
1.1 方式一:自动映射(常用) resultType
根据表的字段名和POJO类中的属性名对应
select a.*, b.id uid, username, birthday, sex, address
from orders a, user b
where a.userId = b.id
1.1.1 自定义OrdersCustom
包含用户和订单中所有属性或者继承Orders类,再加上User类的属性
public class OrdersCustom extends Orders { private String username;// 用户名称 private String address;// 用户地址 setter&getter; }
<!-- 查询所有订单信息 --> <select id="findOrdersList" resultType="cn.guojie.mybatis.pojo.OrdersCustom"> select a.*, b.id uid, username, birthday, sex, address from orders a, user b where a.user_id = b.id </select>
public void testfindOrdersList() throws Exception{ //获取session SqlSession session = sqlSessionFactory.openSession(); //获取mapper接口实例 UserMapper userMapper = session.getMapper(UserMapper.class); //查询订单信息 List<OrdersCustom> list = userMapper.findOrdersList(); System.out.println(list); //关闭session session.close(); }
1.2 方式二:手动映射(MyBatis官方) resultMap
指定表中字段名和POJO类中的属性名的对应关系
用association完成关联查询
1.2.1 在Orders类中加入User对象
private User user;
1.2.2 OrderMapper.xml中
<!-- 查询订单关联用户信息使用resultmap --> <resultMap type="cn.guojie.pojo.Orders" id="orderUserResultMap"> <!-- id为主键字段,column为表字段,property为POJO属性,result为非主键字段 --> <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"/> <!-- 一对一关联映射 --> <!-- 为Order中增加的user对象设置对应关系 property : Orders对象的user属性 javaType:user属性对应的类型 --> <association property="user" javaType="cn.guojie.pojo.User"> <!-- column:user表的主键对应的列 property:user对象中id属性--> <id column="user_id" property="id"/> <result column="username" property="username"/> <result column="address" property="address"/> </association> </resultMap>
<select id="findOrdersList" resultMap="orderUserResultMap"> select a.*, b.id uid, username, birthday, sex, address from orders a, user b where a.user_id = b.id </select>
1.2.3 OrderMapper接口中
public List<Orders> findOrdersList();
1.2.4 测试
public void testfindOrdersListResultMap() throws Exception{ //获取session SqlSession session = sqlSessionFactory.openSession(); //获取mapper接口实例 UserMapper userMapper = session.getMapper(UserMapper.class); //查询订单信息 List<Orders> list = userMapper.findOrdersList(); System.out.println(list); //关闭session session.close(); }
2、一对多查询
以用户为中心,一个用户对应多个订单
只能手动映射,在User类中加入Order的List<Orders>集合
private List<Orders> orderList;
<resultMap type="cn.guojie.pojo.user" id="userOrderResultMap"> <!-- 用户信息映射 --> <id column="id property="id" "/> <result column="username property="username" "/> <result column="birthday property="birthday" "/> <result column="sex property="sex" "/> <result column="address property="address""/> <!-- 一对多关联映射 集合对象关系映射 property:将数据放入User对象中的orderList属性中 ofType:指定orderList属性的泛型类型 --> <collection property="orders" ofType="cn.guojie.pojo.Orders"> <id column="oid property="id" "/> <!--用户id已经在user对象中存在,此处可以不设置--> <!-- <result column="id property="userId""/> --> <result column="number property="number" "/> <result column="createtime property="createtime" "/> <result column="note property="note""/> </collection> </resultMap>
<select id="finUserAndOrders" resultMap="userOrderResultMap"> select a.*, b.id oid, number,createtime from user a, order b where a.id = b.user_id </select>
public List<User> findUserAndOrders();
@Test public void testFindUserAndOrders() { SqlSession session = sqlSessionFactory.openSession(); UserMapper userMapper = session.getMapper(UserMapper.class); List<User>result = userMapper.getUserOrderList(); for (User user : result) { System.out.println(user); } session.close(); }
四、Spring整合MyBatis
1、思路
(1)数据库连接池及其事务管理应交给Spring容器管理
(2)SqlSessionFactory工厂对象应该放到Spring容器中作为单例对象
(3)传统DAO开发中,应从Spring容器中获得sqlSession对象;Mapper动态代理形式中,应从Spring容器中获得mapper的接口代理对象
2、整合
2.1 导包
Spring 、MyBatis、Spring+MyBatis整合包、MySQL数据库驱动包、数据库连接池DBCP(比C3P0效率低)
2.2 Spring核心配置文件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:db.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> <!-- mapper配置 --> <!-- 让spring管理sqlsessionfactory 使用mybatis和spring整合包中的 --> <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <!-- 数据库连接池 --> <property name="dataSource" ref="dataSource"/> <!-- 加载mybatis的全局配置文件 --> <property name="configLocation" value="classpath:mybatis/SqlMapConfig.xml"/> </bean> </beans>
Spring容器配置文件需要加classpath
db.properties
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf-8
jdbc.username=root
jdbc.password=root
MyBatis核心配置文件
<?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> <typeAliases> <package name="cn.guojie.mybatis.pojo"/> </typeAliases> <mappers> <mapperresource="sqlmap/User.xml"/> </mappers> </configuration>
3、原生DAO
接口+实现类,实现类继承自SqlSessionSupport类,提供getSqlSession()来获取SqlSession
需要在Spring配置文件中指定DAO类
DAO实现类需要继承SqlSessionDaoSupport超类
在DAO实现类中不要手动关闭会话,不要提交事务
applicationContext.xml中
<!-- 配置UserDao实现类 --> <bean id="userDao" class="cn.guojie.dao.UserDaoImpl"> <property name="sqlSessionFactory" ref="sqlSessionFactory"/> </bean>
DAO实现类
public class UserDaoImpl extends SqlSessionDaoSupport implements UserDao { @Override public User findUserById(int id) throws Exception { SqlSession session = this.getSqlSession(); User user = session.selectOne("test.findUserById", id); //不能关闭SqlSession,让spring容器来完成 //session.close(); return user; } @Override public void insertUser(User user) throws Exception { SqlSession session = this.getSqlSession(); session.insert("test.insertUser", user); session.commit(); //session.close(); } }
测试
public class UserDaoTest { private ApplicationContext applicationContext; @Before public void setUp() throws Exception{ String configLocation = "classpath:spring/ApplicationContext.xml"; //初始化spring运行环境 applicationContext = new ClassPathXmlApplicationContext(configLocation); } @Test public void testFindUserById() throws Exception { UserDao userDao = (UserDao) applicationContext.getBean("userDao"); User user = userDao.findUserById(1); System.out.println(user); } }
4、Mapper接口动态代理
只需要接口,由MyBatis生成其实现类
applicationContext.xml中
方式一
<!-- 配置mapper代理对象 --> <bean id=userMapper class="org.mybatis.spring.mapper.MapperFactoryBean"> <!-- 配置mapper接口全路径名--> <property name="mapperInterface" value="cn.guojie.mybatis.mapper.UserMapper"/> <propertyname="sqlSessionFactory"ref="sqlSessionFactory"></property> </bean>
public class UserMapperTest { private ApplicationContext applicationContext; @Before publicvoid setUp() throws Exception { applicationContext = new ClassPathXmlApplicationContext("classpath:spring/applicationContext.xml"); } @Test public void testGetUserById() { UserMapper userMapper = applicationContext.getBean(UserMapper.class); User user = userMapper.getUserById(1); System.out.println(user); } }
方式二:包扫描批量引入Mapper(常用)
<!-- 使用扫描包的形式来创建mapper代理对象 --> <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer"> <property name="basePackage" value="cn.guojie.mybatis.mapper"></property> </bean>
每个mapper代理对象的id就是类名,扫描后引用时首字母小写
SqlMapConfig.xml中<package name="cn.guojie.mapper"/>包扫描批量引入接口可以不要了,重复引用
五、MyBatis逆向工程
1、导包
MyBatis核心包
数据库驱动
逆向工程包
日志包log4j-1.2.16.jar
2、generator.xml 工程目录下
(1)POJO所在的包路径注意修改
(2)添加要生成的数据库表
(3)mapper文件和接口所在的包路径要一致
<?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="testTables" targetRuntime="MyBatis3"> <commentGenerator> <!-- 是否去除自动生成的注释 true:是 : false:否 --> <property name="suppressAllComments" value="true" /> </commentGenerator> <!--数据库连接的信息:驱动类、连接地址、用户名、密码 --> <jdbcConnection driverClass="com.mysql.jdbc.Driver" connectionURL="jdbc:mysql://localhost:3306/mybatis" userId="root" password="admin"> </jdbcConnection> <!-- <jdbcConnection driverClass="oracle.jdbc.OracleDriver" connectionURL="jdbc:oracle:thin:@127.0.0.1:1521:yycg" userId="yycg" password="yycg"> </jdbcConnection> --> <!-- 默认false,把JDBC DECIMAL 和 NUMERIC 类型解析为 Integer,为 true时把JDBC DECIMAL 和 NUMERIC 类型解析为java.math.BigDecimal --> <javaTypeResolver> <property name="forceBigDecimals" value="false" /> </javaTypeResolver> <!-- targetProject:生成POJO类的位置 --> <javaModelGenerator targetPackage="cn.guojie.pojo" targetProject=".\src"> <!-- enableSubPackages:是否让schema作为包的后缀 --> <property name="enableSubPackages" value="false" /> <!-- 从数据库返回的值被清理前后的空格 --> <property name="trimStrings" value="true" /> </javaModelGenerator> <!-- targetProject:mapper映射文件生成的位置,和接口放在同一目录 --> <sqlMapGenerator targetPackage="cn.guojie.mapper" targetProject=".\src"> <!-- enableSubPackages:是否让schema作为包的后缀 --> <property name="enableSubPackages" value="false" /> </sqlMapGenerator> <!-- targetPackage:mapper接口生成的位置 和映射文件放在同一目录 --> <javaClientGenerator type="XMLMAPPER" targetPackage="cn.guojie.mapper" targetProject=".\src"> <!-- enableSubPackages:是否让schema作为包的后缀 --> <property name="enableSubPackages" value="false" /> </javaClientGenerator> <!-- 指定数据库表 --> <!-- <table tableName="items"></table> --> <table tableName="orders"></table> <!-- <table tableName="orderdetail"></table> --> <table tableName="user"></table> <!-- <table schema="" tableName="sys_user"></table> <table schema="" tableName="sys_role"></table> <table schema="" tableName="sys_permission"></table> <table schema="" tableName="sys_user_role"></table> <table schema="" tableName="sys_role_permission"></table> --> <!-- 有些表的字段需要指定java类型 <table schema="" tableName=""> <columnOverride column="" javaType="" /> </table> --> </context> </generatorConfiguration>
3、使用Java类生成mapper文件(mapper接口+mapper.xml+POJO类)
public class StartServer { public void generator() throws Exception{ List<String> warnings = new ArrayList<String>(); boolean overwrite = true; File configFile = new File("genarator.xml"); ConfigurationParser cp = new ConfigurationParser(warnings); Configuration config = cp.parseConfiguration(configFile); DefaultShellCallback callback = new DefaultShellCallback(overwrite); MyBatisGenerator myBatisGenerator = new MyBatisGenerator(config, callback, warnings); myBatisGenerator.generate(null); } public static void main(String[] args) throws Exception { try { StartServer startServer = new StartServer(); startServer.generator(); } catch (Exception e) { e.printStackTrace(); } } }
生成文件的方式是追加而不是覆盖,运行第二次会再生成一遍,要删除原来的再生成,不然会报错
4、拷贝生成的文件
mapper xml文件和mapper.java文件在一个目录内且文件名相同
5、测试
public class UserMapperTest { private ApplicationContext applicatonContext; @Before public void setUp() throws Exception{ String configLocation = "classpath:ApplicationContext.xml"; applicatonContext = new ClassPathXmlApplicationContext(configLocation); } @Test public void testFindUserById() throws Exception{ UserMapper userMapper = (UserMapper)applicatonContext.getBean("userMapper"); User user = userMapper.selectByPrimaryKey(1); System.out.println(user); } @Test public void testFindUserAndSex() throws Exception{ UserMapper userMapper = (UserMapper)applicatonContext.getBean("userMapper"); //创建UserExample对象 UserExample userExample = new UserExample(); //通过UserExample对象创建查询条件封装对象(Criteria中是封装的查询条件) Criteria createCriteria = userExample.createCriteria(); //加入查询条件 createCriteria.andUsernameLike("%王%"); createCriteria.andSexEqualTo("1"); List<User> list = userMapper.selectByExample(userExample); System.out.println(list); } }
like模糊查询时要加%
POJO:不按MVC分层,只有JavaBean的一些属性和setter&getter
Domain:不按MVC分层,只有JavaBean的一些属性和setter&getter
PO:用于持久层,在增加或者修改的时候,从页面直接传入action中,其中JavaBean类名等于表名,属性名等于字段名,还有对应的setter&getter
VO:View Object表现层对象,主要用于在高级查询中从页面接收传过来的各种条件参数,好处是扩展性强
BO:在Service,现在基本不用