myBatis
2018-01-22 本文已影响12人
liangxifeng833
一．整体使用流程
- maven的pom.xml中配置依赖，也就是加载mybatis的jar包
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.2.8</version>
</dependency>
-
练习代码结构
mybatis-1.jpg
-
mybatis主配置文件Configuration.xml配置
<environments default="development">
<environment id="development">
<transactionManager type="JDBC">
<property name="" value=""/>
</transactionManager>
<!-- mysql数据库连接配置 -->
<dataSource type="UNPOOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8"/>
<property name="username" value="common"/>
<property name="password" value="common"/>
</dataSource>
</environment>
</environments>
<!-- 加载实体数据表配置文件 -->
<mappers>
<mapper resource="com/mybatis/config/sql/xml/Message.xml"/>
<mapper resource="com/mybatis/config/sql/xml/Command.xml"/>
<mapper resource="com/mybatis/config/sql/xml/CommandContent.xml"/>
</mappers>
- log4j配置文件log4j.properties配置
log4j.rootLogger=DEBUG,Console
log4j.appender.Console=org.apache.log4j.ConsoleAppender
log4j.appender.Console.layout=org.apache.log4j.PatternLayout
log4j.appender.Console.layout.ConversionPattern=%d [%t] %-5p [%c] - %m%n
log4j.logger.org.apache=INFO
- 数据表配置文件配置
指令表Command.xml配置
<mapper namespace="Command">
<resultMap type="com.mybatis.bean.Command" id="CommandResult">
<!-- 注意：column 属性代表select 语句查询的别名，而不是真实的数据表列名 -->
<id column="c_id" jdbcType="INTEGER" property="id"/>
<result column="name" jdbcType="VARCHAR" property="name"/>
<result column="desc" jdbcType="VARCHAR" property="desc"/>
<collection property="contentList" resultMap="CommandContent.contentResult" />
</resultMap>
<!--根据条件查询 -->
<select id="queryCommandList" parameterType="com.mybatis.bean.Command" resultMap="CommandResult" >
SELECT <include refid="colums" />
FROM command a left join command_content b on a.id = b.command_id
<where>
1=1
<if test="name !=null and !"".equals(name.trim())" >
and name = #{name}
</if>
<if test="desc !=null and !"".equals(desc.trim())" >
and desc like '%' #{desc} '%'
</if>
</where>
</select>
<sql id="colums">a.id c_id, a.name,a.desc,b.id,b.content,b.command_id</sql>
</mapper>
指令内容表commandContent.xml配置
<mapper namespace="CommandContent">
<resultMap type="com.mybatis.bean.CommandContent" id="contentResult">
<id column="id" jdbcType="INTEGER" property="id"/>
<result column="content" jdbcType="VARCHAR" property="content"/>
<result column="command_id" jdbcType="INTEGER" property="command_id"/>
</resultMap>
</mapper>
message信息表message.xml配置
<mapper namespace="Message">
<resultMap type="com.mybatis.bean.Message" id="MessageResult">
<id column="id" jdbcType="INTEGER" property="id"/>
<result column="command" jdbcType="VARCHAR" property="command"/>
<result column="desc" jdbcType="VARCHAR" property="desc"/>
<result column="content" jdbcType="VARCHAR" property="content"/>
</resultMap>
<!--根据条件查询 -->
<select id="queryList" parameterType="com.mybatis.bean.Message" resultMap="MessageResult" >
SELECT <include refid="colums" />
FROM message
<where>
<if test="command !=null and !"".equals(command.trim())" >
and command = #{command}
</if>
<if test="desc !=null and !"".equals(desc.trim())" >
and desc like '%' #{desc} '%'
</if>
</where>
</select>
<sql id="colums">command,`desc`,content</sql>
<!-- 根据主键删除操作 -->
<update id="updateOne" parameterType="com.mybatis.bean.Message" >
update message
<set>
<if test="content !=null and !"".equals(content.trim())" >
content = #{content}
</if>
</set>
<where>
id=#{id}
</where>
limit 1
</update>
<!-- 新增数据 -->
<insert id="insertOne" parameterType="com.mybatis.bean.Message">
insert into message(
<include refid="colums" />
) values(#{command}, #{desc}, #{content})
</insert>
<!--根据主键删除 -->
<delete id="deleteOne" parameterType="int">
delete from message
<where>
id = #{_paramter}
</where>
limit 1
</delete>
<!-- 批量删除 -->
<delete id="deleteBatch" parameterType="java.util.List">
delete from message where id in (
<foreach collection="list" item="item" separator=","><!-- separator属性代表sql语句中in 中的连接符 -->
#{item}
</foreach>
)
</delete>
</mapper>
- DBAccess.java数据库连接配置
package com.mybatis.db;
import java.io.IOException;
import java.io.Reader;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
/**
* 数据库访问类
* @author lxf
*
*/
public class DBAccess {
public SqlSession getSqlSession () throws IOException
{
//通过配置文件爱你获取数据库连接信息
Reader reader = Resources.getResourceAsReader("com/mybatis/config/Configuration.xml");
//通过配置信息构建一个SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
//通过sqlSessionFactory打开一个数据库会话
SqlSession sqlSession = sqlSessionFactory.openSession();
return sqlSession;
}
}
- MessageDao.java单表操作
package com.mybatis.dao;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import com.mybatis.db.DBAccess;
import org.apache.ibatis.session.SqlSession;
import org.apache.log4j.Logger;
import com.mybatis.bean.Message;
public class MessageDao {
/**
* 根据条件查询多条
* @param command
* @param desc
* @return
*/
public List<Message> queryList(String command, String desc)
{
DBAccess dbAccess = new DBAccess();
SqlSession sqlSession = null;
try {
sqlSession = dbAccess.getSqlSession();
List<Message> msg = new ArrayList<Message>();
Message msgObj = new Message();
msgObj.setCommand(command);
msgObj.setDesc(desc);
msg = sqlSession.selectList("Message.queryList",msgObj);
sqlSession.commit();
for (Message message : msg) {
System.out.println(message.getCommand());
}
return msg;
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
if(sqlSession != null)
{
sqlSession.close();
}
}
return null;
}
/**
* 根据主键删除
*/
public void delOne(int id)
{
DBAccess dbAccess = new DBAccess();
SqlSession sqlSession = null;
try {
sqlSession = dbAccess.getSqlSession();
sqlSession.selectList("Message.deleteOne",id);
sqlSession.commit();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
if(sqlSession != null)
{
sqlSession.close();
}
}
}
/**
* 批量删除
* @param args
*/
public void delBatch(List<Integer> ids)
{
DBAccess dbAccess = new DBAccess();
SqlSession sqlSession = null;
try {
sqlSession = dbAccess.getSqlSession();
sqlSession.selectList("Message.deleteBatch",ids);
sqlSession.commit();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
if(sqlSession != null)
{
sqlSession.close();
}
}
}
/**
* 修改message信息
* @param args
*/
public void updateById(Message msg)
{
DBAccess dbAccess = new DBAccess();
SqlSession sqlSession = null;
try {
sqlSession = dbAccess.getSqlSession();
sqlSession.selectList("Message.updateOne",msg);
sqlSession.commit();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
if(sqlSession != null)
{
sqlSession.close();
}
}
}
/**
* 新增数据
* @param args
*/
public void add(Message msg)
{
DBAccess dbAccess = new DBAccess();
SqlSession sqlSession = null;
try {
sqlSession = dbAccess.getSqlSession();
sqlSession.selectList("Message.insertOne",msg);
sqlSession.commit();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
if(sqlSession != null)
{
sqlSession.close();
}
}
}
public static void main(String[] args){
MessageDao md = new MessageDao();
md.queryList("查看", "");
//测试删除
}
}
- CommandDao.java指令表对应指令内容表，一对多关联操作
public class CommandDao {
/**
* 根据条件查询多条指令列表
* @param command
* @param desc
* @return
*/
public List<Command> queryList(String command, String desc)
{
DBAccess dbAccess = new DBAccess();
SqlSession sqlSession = null;
try {
sqlSession = dbAccess.getSqlSession();
List<Command> commandList = new ArrayList<Command>();
Command comObj = new Command();
comObj.setName(command);
comObj.setDesc(desc);
commandList = sqlSession.selectList("Command.queryCommandList",comObj);
sqlSession.commit();
for (Command com : commandList) {
System.out.println("指令名＝"+com.getName()+", 描述：" + com.getDesc());
System.out.println("=======对应内容列表是：");
int i = 1;
for(CommandContent content : com.getContentList()){
System.out.println("内容:" + i + content.getContent() );
i++;
}
}
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
if(sqlSession != null)
{
sqlSession.close();
}
}
return null;
}
- 单元测试
package com.mybatis;
public class TestDB {
@Test
public void testGetDBsqlSession()
{
SqlSession sqlSession = null;
System.out.println("test mybatis GetDBsqlSessoin =======================");
try {
sqlSession = new DBAccess().getSqlSession();
Assert.assertNotNull(sqlSession);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(sqlSession != null)
{
sqlSession.close();
}
}
}
/**
* 单元测试新增操作
*/
@Test
public void testAdd()
{
MessageDao md = new MessageDao();
Message msg = new Message();
msg.setCommand("新增指令");
msg.setDesc("新增指令描述");
msg.setContent("新增指令内容");
md.add(msg);
}
/**
* 单元测试修改message表数据
*/
@Test
public void testUpdateOne()
{
MessageDao md = new MessageDao();
List<Message> msgList = new ArrayList<Message>();
msgList = md.queryList("查看", "");
msgList.get(0).setContent("测试修改查看指令的第一条数据内容");
md.updateById(msgList.get(0));
}
/**
* 单元测试查询多条记录
*/
@Test
public void testSelectList()
{
MessageDao md = new MessageDao();
md.queryList("查看", "");
}
/**
* 单元测试根据主键删除单条记录
*/
@Test
public void testDelOne()
{
MessageDao md = new MessageDao();
md.delOne(6);
}
/**
* 单元测试根据主键批量删除
*/
@Test
public void testDelBatch()
{
List<Integer> ids = new ArrayList<Integer>();
ids.add(Integer.valueOf("5"));
ids.add(Integer.valueOf("4"));
MessageDao md = new MessageDao();
md.delBatch(ids);
}
/**
* 单元测试输入一对多关联关系，指令表和内容表（列表）
*/
@Test
public void testGetCommandToContent()
{
Command command = new Command();
CommandDao cDao = new CommandDao();
List<Command> commandList = new ArrayList<Command>();
commandList = cDao.queryList("六一", "");
}
}
二． debug断点调试
Paste_Image.png
配置文件在myBatis原码中的位置，可以copy过来直接修改使用
Paste_Image.png
在mybatis中使用OGNL表达式
Paste_Image.png
Paste_Image.png
实体xml中的常用标签
Paste_Image.png
mybatis进阶
动态代理
Paste_Image.png
三．接口式编程
- 在dao层调用xml中的操作id的时候，是以字符串的形式标注的
msg = sqlSession.selectList("Message.queryList",msgObj);
- 字符串形式如果编写错误，编辑器编译阶段是不会报错的，所以为了减少错误的发生，以及更好的规范代码，我们可以使用接口
方法名映射为xml中的标签操作id,在dao中定义IMessage接口
package com.mybatis.dao;
import java.util.List;
import java.util.Map;
import com.mybatis.bean.Message;
/**
* 与Message.xml配置文件相对应的接口
* @author lxf
* @2017-06-08
*
*/
public interface IMessage {
/*
* 查询多条message信息表数据方法，方法名queryList=Message.xml中对应的查询id名
*/
public List<Message> queryList(Message msg);
/**
* 查询数量
*/
public int queryCount(Message msg);
/*
* 查询多条message信息表数据方法，带分页功能
*/
public List<Message> queryListWithPage(Map<String,Object> map);
}
- 对应message.xml中的命名空间应该使用该接口的全路径名
<mapper namespace="com.mybatis.dao.IMessage">
- 使用方式
Message msgObj = new Message();
msgObj.setCommand(command);
msgObj.setDesc(desc);
IMessage imsg = sqlSession.getMapper(IMessage.class);
msg = imsg.queryList(msgObj);
四．简单分页功能实现
- 在entity包下定义分页类Page
package com.lxf.entity;
/**
* 分页对应的实体类
* @author lxf
*
*/
public class Page {
/**
* 总条数
*/
private int totalNumber;
/**
* 当前第几页
*/
private int currentPage;
/**
* 总页数
*/
private int totalPage;
/**
* 每页显示条数
*/
private int pageNumber = 2;
/**
* 数据库中limit 参数，从第几条开始取
*/
private int dbIndex;
/**
* 数据库中limit 参数，一共取多少条
*/
private int dbNumber;
/**
*根据当前对象中属性值计算并设置相关属性值
*
*/
public void count()
{
//计算总页数
int totalPageTemp = this.totalNumber/this.pageNumber;
int plus =(this.totalNumber%this.pageNumber) ==0 ? 0 : 1;
totalPageTemp = totalPageTemp + plus;
if(totalPageTemp <= 0 )
{
totalPageTemp = 1;
}
this.totalPage = totalPageTemp;
/*
* 设置当前页
*/
//总页数小于当前页，应将当前页设置为总页数
if(this.totalPage < this.currentPage)
{
this.currentPage = this.totalPage;
}
//当前页＜１，设置为1
if(this.currentPage < 1)
{
this.currentPage = 1;
}
//设置limit
this.dbIndex = (this.currentPage-1) * this.pageNumber;
this.dbNumber = this.pageNumber;
}
public int getTotalNumber() {
return totalNumber;
}
public void setTotalNumber(int totalNumber) {
this.totalNumber = totalNumber;
count();
}
public int getCurrentPage() {
return currentPage;
}
public void setCurrentPage(int currentPage) {
this.currentPage = currentPage;
}
public int getTotalPage() {
return totalPage;
}
public void setTotalPage(int totalPage) {
this.totalPage = totalPage;
}
public int getPageNumber() {
return pageNumber;
}
public void setPageNumber(int pageNumber) {
this.pageNumber = pageNumber;
}
public int getDbIndex() {
return dbIndex;
}
public void setDbIndex(int dbIndex) {
this.dbIndex = dbIndex;
}
public int getDbNumber() {
return dbNumber;
}
public void setDbNumber(int dbNumber) {
this.dbNumber = dbNumber;
}
}
- 在xml中定义查询message表总记录数方法个,然后在dao中通过条件查询总记录数量，然后在实例化Page,设置总页数和当前页码
/**
* 根据条件查询多条,带分页功能
* @param command
* @param desc
* @param curPage 当前页码
* @return
*/
public List<Message> queryListWithPage(String command, String desc,int curPage)
{
DBAccess dbAccess = new DBAccess();
SqlSession sqlSession = null;
try {
sqlSession = dbAccess.getSqlSession();
List<Message> msg = new ArrayList<Message>();
Message msgObj = new Message();
msgObj.setCommand(command);
msgObj.setDesc(desc);
//获取接口式编程中，message.xml中的select.id对应的接口
IMessage imsg = sqlSession.getMapper(IMessage.class);
//获取记录总数
int count = imsg.queryCount(msgObj);
//实例化分页类
Page page = new Page();
//设置当前页码
page.setCurrentPage(curPage);
//设置记录总数
page.setTotalNumber(count);
//设置每页显示2条记录
page.setPageNumber(2);
Map<String,Object> mapParam = new HashMap<String, Object>();
mapParam.put("message", msgObj);
mapParam.put("page", page);
msg = imsg.queryListWithPage(mapParam);
sqlSession.commit();
for (Message message : msg) {
System.out.println(message.getCommand());
}
return msg;
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
if(sqlSession != null)
{
sqlSession.close();
}
}
return null;
}
Message.xml配置
<!--根据条件查询,带分页功能 -->
<select id="queryListWithPage" parameterType="java.util.Map" resultMap="MessageResult" >
SELECT <include refid="colums" />
FROM message
<where>
<if test="command !=null and !"".equals(command.trim())" >
and command = #{message.command}
</if>
<if test="desc !=null and !"".equals(desc.trim())" >
and desc like '%' #{message.desc} '%'
</if>
</where>
Limit #{page.dbIndex}, #{page.dbNumber}
</select>
五．拦截器分页
- 新建拦截器com.lxf.interceptor.PageInterceptor
package com.lxf.interceptor;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Map;
import java.util.Properties;
import org.apache.ibatis.executor.parameter.ParameterHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.apache.ibatis.reflection.wrapper.ObjectWrapperFactory;
import com.lxf.entity.Page;
/**
* 分页拦截器
* @author lxf
*
*/
//@Intercepts({@Signature(
// type= StatementHandler.class,
// method = "prepare",
// args = {Connection.class,Integer.class})})
@Intercepts({ @Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class }) })
public class PageInterceptor implements Interceptor {
/**
* 拦截器执行顺序3.拦截处理操作
*/
public Object intercept(Invocation invocation) throws Throwable {
//获取被拦截的对象
/*
StatementHandler statementHandler = (StatementHandler)invocation.getTarget();
//判断是否拦截该对象的方法
MetaObject metaObject = MetaObject.forObject(statementHandler, SystemMetaObject.DEFAULT_OBJECT_FACTORY, (ObjectWrapperFactory) SystemMetaObject.DEFAULT_OBJECT_FACTORY);
// TODO Auto-generated method stub
MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");
//获取xml配置文件中sql语句的id
String id = mappedStatement.getId();
if(id.matches(".+ByInterceptor$")){
BoundSql bondSql = statementHandler.getBoundSql();
//获取原始sql语句
String sql = bondSql.getSql();
//查询总条数的sql语句
String countSql = "select count(*) from ("+ sql +") a ";
Connection connection = (Connection)invocation.getArgs()[0];
PreparedStatement countStatement = connection.prepareStatement(countSql);
ParameterHandler pH = (ParameterHandler)metaObject.getValue("delegate.parameterHandler");
pH.setParameters(countStatement);
ResultSet rs = countStatement.executeQuery();
Map<String,Object> parameter = (Map<String,Object>)bondSql.getParameterObject();
Page page = (Page)parameter.get("page");
if(rs.next())
{
page.setTotalNumber(rs.getInt(1));
}
//改造后带分页查询的sql语句
String pageSql = sql + " limit" + page.getDbIndex() + "," + page.getDbNumber();
metaObject.setValue("delegate.boundSql.sql", pageSql);
}
*/
//让原本被拦截的方法继续执行
return invocation.proceed();
}
/**
* 拦截器执行顺序2.拦截器先将拦截的对象target，进行过滤
* 如果被拦截的对象有@Intercepts注解，并配置了拦截，那么使用动态代理机制生成代理类，
* 并调用intercept方法处理业务罗辑，否则放行，不经过intercept方法
*/
public Object plugin(Object target) {
// TODO Auto-generated method stub
return Plugin.wrap(target, this);
}
//拦截器执行顺序１．获取配置文件<plugin>中的<property>属性值
public void setProperties(Properties properties) {
// TODO Auto-generated method stub
properties.getProperty("testName");
}
}
- 在总配置文件中注册拦截器 Configuration.xml
<configuration>
<!-- 注册分页拦截器 -->
<plugins>
<plugin interceptor="com.lxf.interceptor.PageInterceptor">
<property name="testName" value="zhangsan"/>
</plugin>
</plugins>
<environments default="development">
...
</environments>
</configuration>
- 通过如上配置，在xml文件中就不需要自行组装sql的limit了，注册完成后，符合拦截器规则的程序会获取myBatis的sql，然后添加limit实现分页，最后将经过处理后的sql继续执行，就实现分页功能了．
- 拦截器我的理解就相当与ci的钩子函数，想在哪里下埋钩点，就在那拦截；
六．myBatis实现批量新增
- 在配置文件中配置如下
<!-- 批量新增 -->
<insert id="insertBatch" parameterType="java.util.List">
insert into message(
<include refid="colums" />
) values
<foreach collection="list" item="item" separator=",">
(#{item.command}, #{item.desc}, #{item.content})
</foreach>
</insert>
- 批量新增dao
/**
* 批量新增数据
* @param args
*/
public void addBatch(List<Message> msgList)
{
DBAccess dbAccess = new DBAccess();
SqlSession sqlSession = null;
try {
sqlSession = dbAccess.getSqlSession();
//获取Message.xml对应接口
IMessage imsg = sqlSession.getMapper(IMessage.class);
imsg.insertBatch(msgList);
sqlSession.commit();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
if(sqlSession != null)
{
sqlSession.close();
}
}
}
- 批量新增单元测试
/**
* 单元测试批量新增
*/
@Test
public void testAddBatch()
{
MessageDao md = new MessageDao();
Message msg = new Message();
List<Message> msgList = new ArrayList<Message>();
msg.setCommand("批量测试新增指令1");
msg.setContent("批量测试新增内容１");
msg.setDesc("批量测试新增描述1");
msgList.add(msg);
Message msg2 = new Message();
msg2.setCommand("批量测试新增指令2");
msg2.setContent("批量测试新增内容2");
msg2.setDesc("批量测试新增描述2");
msgList.add(msg2);
md.addBatch(msgList);
}
七．总结
- 总配置文件Configuration.xml配置拦截器，加载实体xml配置文件
- 在实体xml配置文件中通过mybatis特有的标签写sql语句
- 通过接口式变成定义每个实体xml对应的接口，接口中方法与实体xml中的标签id对应（原理：动态代理）
- 在dao层通过调用实体的方法操作数据库
- mybatis通过log4j配置在控制台打印sql语句，方便调试
