欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

MyBatis联合查询和修改例子

程序员文章站 2022-04-25 10:03:56
...

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


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>