MyBatis联合查询和修改例子

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

企业级项目实战(带源码)地址：http://zz563143188.iteye.com/blog/1825168

收集五年的开发资料下载地址：  http://pan.baidu.com/share/home?uk=4076915866&view=share

演示项目源码请下载codeFactory20130321,lib20130321文件,

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> 

评论

1 楼 hy2012_campus 2014-09-28  

总结的很好