MyBatis联合查询和修改例子
copy http://blog.csdn.net/caodegao/article/details/6735049
企业级项目实战(带源码)地址：http://zz563143188.iteye.com/blog/1825168
MyBatis现在还挺少人用的,刚玩的时候在网站查资料都没有很多贴研究它.走了很多弯路;
在此做了一个小例子,跟大家分享一下;
如果能对一些刚玩MyBatis的朋友一些帮助就再好不过了.
首先给大家配置MyBatis的前奏,毕竟什么框架都是配置出来的,大家得下载MyBatis的文档,上面有很详细的配置前奏.
我就不给大家贴出来了.我是用Spring和Struts2集成的.别怪我太自私啊!下次慢慢在贴出集成的例子,
先给大家sql的配置吧
我有一个总文件mybatis-config.xml,在总文件加载子文件,这样至少分工还算明细些.
[html] view plaincopy
<?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>
<typeAlias alias="Account" type="com.cookiejoo.beans.Account" />
<typeAlias alias="Weibo" type="com.cookiejoo.beans.Weibo" />
</typeAliases>
<mappers>
<mapper resource="mybatisConfig/account-mapper.xml" />
<mapper resource="mybatisConfig/weibo-mapper.xml" />
</mappers>
</configuration>
重点在这个weibo-mapper.xml里面,这个也一样自己写他的sql映射account-mapper.xml
由于就演示一个表weibo关联表account,关联都在weibo-mapper.xml里面写.account-mapper.xml就不给大家了,没什么大碍的.
配置文件的详细我都写了,大家靠自己的悟性吧.
[html] view plaincopy
<?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">
<!-- 命名空间都以pojo类的包命名,很长,但是也好区分,文档上说这是MyBatis现在的规定了 -->
<mapper namespace="com.cookiejoo.beans.Weibo">
<!-- 定义一张表,并且来个关联表 -->
<resultMap type="Weibo" id="weiboJoinAccountResultMap">
<id property="wId" column="w_id" />
<result property="wContext" column="w_context" />
<result property="wCreateTime" column="w_create_time" />
<!-- 关联account表 -->
<association property="account" column="w_acc_id"
javaType="Account" resultMap="joinAccountResult" />
</resultMap>
<!-- 关联表需要的列 -->
<resultMap type="Account" id="joinAccountResult">
<id property="aId" column="a_id" />
<result property="aUsername" column="a_username" />
<result property="aHeadImage" column="a_head_image" />
</resultMap>
<!-- 定义没有关联的单表查询返回结果 -->
<resultMap type="Weibo" id="weiboResultMap">
<id property="wId" column="w_id" />
<result property="wContext" column="w_context" />
<result property="wCreateTime" column="w_create_time" />
<!-- 关联account表 -->
<association property="account" column="w_acc_id"
javaType="Account" resultMap="joinAccountResult" />
</resultMap>
<!-- 根据创建时间查询 -->
<select id="findWeiboJoinAccount"
resultMap="weiboJoinAccountResultMap" parameterType="Weibo">
select w.w_id,w.w_context,w.w_create_time, a.a_id as
w_acc_id,a.a_username,a.a_head_image from weibo w left outer
join account a on w.w_acc_id = a.a_id where w.w_create_time >
#{wCreateTime} order by w.w_create_time desc
</select>
<!-- resultMap 返回 上面的结果 -->
<select id="findAllWeibo" resultMap="weiboResultMap">
select w.w_id,w.w_context,w.w_create_time, a.a_id
from weibo w left join account a on w.w_acc_id = a.a_id
</select>
<!-- 查询一条记录 -->
<!-- 注意:此处的关联,看pojo类是怎么写的,如果查询出现什么错误,得看这里了 -->
<select id="findAllWeiboById" parameterType="int"
resultMap="weiboResultMap">
select w.w_id,w.w_context,w.w_create_time, w.w_acc_id
from weibo w
where w.w_id = #{wId}
</select>
<!--
首先，如果你的数据库支持自动生成主键的字段（比如MySQL和SQL Server），
那么你可以设置useGeneratedKeys=”true”，
而且设置keyProperty到你已经做好的目标属性上。
例如，如果上面的Author表已经对id使用了自动生成的列类型，那么语句可以修改为
-->
<insert id="addWeibo" parameterType="Weibo" useGeneratedKeys="true"
keyProperty="wId">
insert into weibo(w_acc_id,w_context,w_create_time)
values(#{account.aId},#{wContext},#{wCreateTime})
</insert>
<!-- 修改 -->
<update id="updateWeibo" parameterType="Weibo">
update weibo set w_acc_id = #{account.aId},w_context = #{wContext},w_create_time = #{wCreateTime}
where w_id = #{wId}
</update>
<!-- 删除 -->
<delete id="deleteWeibo" parameterType="int">
delete from weibo where w_id = #{wId}
</delete>
</mapper>
pojo类:这里你要注意了, 怎么给数据做搜集,
[java] view plaincopy
package com.cookiejoo.beans;
import java.util.Date;
public class Weibo {
private Integer wId;
private Account account;
private String wContext;
private Date wCreateTime;
public Weibo() {
}
public Integer getWId() {
return wId;
}
public void setWId(Integer wId) {
this.wId = wId;
}
public Account getAccount() {
return account;
}
public void setAccount(Account account) {
this.account = account;
}
public String getWContext() {
return wContext;
}
public void setWContext(String context) {
wContext = context;
}
public Date getWCreateTime() {
return wCreateTime;
}
public void setWCreateTime(Date createTime) {
wCreateTime = createTime;
}
}
两张表结构很简单
id都是自动增长的;
Table weibo
===========
w_id, w_acc_id, w_context, w_create_time
-----------
w_id int(11) PK
w_acc_id int(11)
w_context varchar(2000)
w_create_time datetime
Table account
=============
a_id, a_username, a_password, a_sex, a_phone, a_brithday, a_create_time, a_head_image
-------------
a_id int(11) PK
a_username varchar(45)
a_password varchar(45)
a_sex int(2)
a_phone varchar(15)
a_brithday datetime
a_create_time datetime
a_head_image varchar(45)
这个是java调用的例子,一个接口一个实现类,我用了Spring集成了,所以和单独的MyBatis例子有点出入,大家对着MyBatis文档做时就是获取getSqlSession这个不一样而已.
[java] view plaincopy
package com.cookiejoo.iservice.impl;
import java.util.Date;
import java.util.List;
import org.mybatis.spring.support.SqlSessionDaoSupport;
import com.cookiejoo.beans.Weibo;
import com.cookiejoo.iservice.IWeiboService;
public class WeiboServiceImpl extends SqlSessionDaoSupport implements
IWeiboService {
@SuppressWarnings("unchecked")
public List<Weibo> findWeiboJoinAccount(Weibo w) {
return getSqlSession().selectList(
"com.cookiejoo.beans.Weibo.findWeiboJoinAccount", w);
}
@SuppressWarnings("unchecked")
public List<Weibo> findAllWeibo() {
return getSqlSession().selectList(
"com.cookiejoo.beans.Weibo.findAllWeibo");
}
public void addWeibo(Weibo w) {
w.setWCreateTime(new Date());
getSqlSession().insert("com.cookiejoo.beans.Weibo.addWeibo", w);
}
public void updateWeibo(Weibo w) {
getSqlSession().update("com.cookiejoo.beans.Weibo.updateWeibo", w);
}
public void deleteWeibo(Weibo w) {
getSqlSession().delete("com.cookiejoo.beans.Weibo.deleteWeibo", w);
}
public Weibo findAllWeiboById(Integer wId) {
return (Weibo) getSqlSession().selectOne(
"com.cookiejoo.beans.Weibo.findAllWeiboById", wId);
}
}
接着页面展示,我用jsp写的,用struts2做跳转... myJsp.jsp
[html] view plaincopy
<%@ page language="java" pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib prefix="s" uri="/struts-tags"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8" />
<title>Condition by Free CSS Templates</title>
<meta name="keywords" content="" />
<meta name="description" content="" />
<style type='text/css'>
#mytable {
padding: 0;
margin: 0;
}
th {
color: #4f6b72;
border-left: 1px solid #C1DAD7;
border-right: 1px solid #C1DAD7;
border-bottom: 1px solid #C1DAD7;
border-top: 1px solid #C1DAD7;
letter-spacing: 2px;
text-transform: uppercase;
text-align: left;
padding: 6px 6px 6px 12px;
background: #CAE8EA no-repeat;
}
td {
border-left: 1px solid #C1DAD7;
border-right: 1px solid #C1DAD7;
border-bottom: 1px solid #C1DAD7;
background: #fff;
padding: 6px 6px 6px 12px;
color: #4f6b72;
}
</style>
</head>
<body>
-------------------------------------------------------------------------------------------
<form action="findAllWeibo.action"><input value="findAllWeibo" type="submit"/>
<input name="weibo.wCreateTime" type="text" value="2011-01-01"/>
</form>
-------------------------------------------------------------------------------------------
<form action="addWeibo.action">
<input name="weibo.wContext" type="text" value="2011-01-01"/><br>
<input name="weibo.account.aId" type="text" value="1"/><br>
<input name="weibo.wCreateTime" type="text" value="2011-01-01"/>
<input value="addWeibo" type="submit"/>
</form>
-------------------------------------------------------------------------------------------
<form action="updateWeibo.action">
<input name="weibo.wContext" type="text" value="2011-02-01"/><br>
<input name="weibo.account.aId" type="text" value="2"/><br>
<input name="weibo.wCreateTime" type="text" value="2011-02-01"/>
<input name="weibo.wId" type="text" value="54"/>
<input value="updateWeibo" type="submit"/>
</form>
<a href="findWeiboByBean.action">findWeiboByBean</a>
<h3></h3>
<br>
<table id='mytable' cellspacing='0' width='100%'>
<tr>
<th>id</th><th>context</th><th>createTime</th><th>aid</th><th>operator</th></tr>
<c:forEach items="${weibos}" var="weibo">
<tr><td>${weibo.WId }</td><td>${weibo.WContext }</td><td>${weibo.WCreateTime }</td><td>${weibo.account.AId }</td><td><a href="deleteWeibo.action?weibo.wId=${weibo.WId }">delete</a></td></tr>
</c:forEach>
</table>
</body>
</html>