MyBatis联合查询和修改例子_JAVA_编程开发_程序员俱乐部

copy http://blog.csdn.net/caodegao/article/details/6735049
?

class="Apple-converted-space">企业级项目实战(带源码)地址：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>?