MyBatis联合查询和修改例子

MyBatis现在还挺少人用的,刚玩的时候在网站查资料都没有很多贴研究它.走了很多弯路;

在此做了一个小例子,跟大家分享一下;

如果能对一些刚玩MyBatis的朋友一些帮助就再好不过了.

首先给大家配置MyBatis的前奏,毕竟什么框架都是配置出来的,大家得下载MyBatis的文档,上面有很详细的配置前奏.

我就不给大家贴出来了.我是用Spring和Struts2集成的.别怪我太自私啊!下次慢慢在贴出集成的例子,

 

先给大家sql的配置吧

我有一个总文件mybatis-config.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>
	<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就不给大家了,没什么大碍的.

配置文件的详细我都写了,大家靠自己的悟性吧.

<?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类:这里你要注意了, 怎么给数据做搜集,

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这个不一样而已.

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

<%@ 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>