Ibatis - 外键关联

最新推荐文章于 2023-10-31 19:04:28 发布

weixin_34337381

最新推荐文章于 2023-10-31 19:04:28 发布

阅读量71

点赞数

原文链接：http://blog.51cto.com/tianya23/532402

版权

一、查询复杂对象

对于有外键的主表映射，在查询返回结果时要使用resultmap进行映射，并在propety里使用select属性进行子查询。


public class PetitionLetter {
	private int id;                              
	private String identifier;                   
	private Reporter reporter;         //外键，数据库里是int类型          
	private LetterInformation letterInformation;   //外键，数据库里是int类型
	private Supervision supervision;     //外键，数据库里是int类型    
	private ProcessFlow processFlow;     //外键，数据库里是int类型
}

<sqlMap namespace="PetitionLetter">
  <typeAlias alias="PetitionLetter" type="com.cs.jfb.input.model.PetitionLetter"/>
  <typeAlias alias="ProcessFlow" type="com.cs.jfb.process.model.ProcessFlow"/>
  <typeAlias alias="Reporter" type="com.cs.jfb.input.model.Reporter"/>
  <typeAlias alias="LetterInformation" type="com.cs.jfb.input.model.LetterInformation"/>
  <typeAlias alias="Supervision" type="com.cs.jfb.supervision.model.Supervision"/>
 
  <resultMap id="PetitionLetterResult" class="PetitionLetter">
    <result property="id" column="id"/>
    <result property="identifier" column="identifier"/>
    <result property="reporter" column="reporter" select="selectReporter"/>
    <result property="letterInformation" column="letterInformation" select="selectLetterInformation"/>
    <result property="supervision" column="supervision" select="selectSupervision"/>
    <result property="processFlow" column="processFlow" select="selectProcessFlow"/>
  </resultMap>

  <!-- Select with no parameters using the result map for Account class. -->
  <select id="selectAllPetitionLetter" resultMap="PetitionLetterResult">
    select * from t_petitionletter
  </select>
  
  <select id="selectProcessFlow" resultClass="ProcessFlow" parameterClass="int">
  	select * from t_processflow where id = #id#
  </select>
  
  <select id="selectReporter" resultClass="Reporter" parameterClass="int">
  	select * from t_reporter where id = #id#
  </select>
  
  <select id="selectLetterInformation" resultClass="LetterInformation" parameterClass="int">
  	select * from t_letterinformation where id = #id#
  </select>
  
  <select id="selectSupervision" resultClass="Supervision" parameterClass="int">
  	select * from t_supervision where id = #id#
  </select>
  
  <select id="selectPetitonLetterById" parameterClass="int" resultMap="PetitionLetterResult">
  	select * from t_petitionletter where id = #id#
  </select>
</sqlMap>

二、复杂对象的插入情况

由于数据库中的外键关系只是对其他表内容的引用，所以在进行插入时，只需要插入引用的外键关联值即可，不会在外键表中该条内容不存在的情况下再去插入一条记录，这是不符合逻辑的。所以在插入的时候，只需要在插入的表中插入内容即可，外键引用的地方定位到具体的值即可，例如：appModel.identityId。

AppModelVersion类

import java.util.Date;
/**
* 应用模型版本
*/
public class AppModelVersion {
/**
* 关联的模型
*/
private AppModel appModel;
/**
* 创建此模型版本的用户
*/
private User createUser;
/**
* 系统标识
*/
private Integer identityId;
/**
* 版本名称。系统自动生成，规则是：“模型名称_YYYYMMDD_V流水号”，比如：en_10k_20101102_v1
*/
private String name;
/**
* 版本说明
*/
private String versionDescription;
/**
* 训练样本描述
*/
private String trainingDataDescription;
/**
* 备注
*/
private String remark;
/**
* 服务器工作目录
*/
private String serverWorkingDir;
/**
* 状态： prepared -- 准备 verify -- 验证中 success -- 验证成功 failure -- 验证失败
*/
private String status;
/**
* 创建时间
*/
private Date createTime;
/**
* 修改时间
*/
private Date modifiedTime;
public AppModel getAppModel() {
return appModel;
}
public void setAppModel(AppModel appModel) {
this.appModel = appModel;
}
public User getCreateUser() {
return createUser;
}
public void setCreateUser(User createUser) {
this.createUser = createUser;
}
public Integer getIdentityId() {
return this.identityId;
}
public void setIdentityId(Integer identityId) {
this.identityId = identityId;
}
public String getName() {
return this.name;
}
public void setName(String name) {
this.name = name;
}
public String getVersionDescription() {
return this.versionDescription;
}
public void setVersionDescription(String versionDescription) {
this.versionDescription = versionDescription;
}
public String getTrainingDataDescription() {
return this.trainingDataDescription;
}
public void setTrainingDataDescription(String trainingDataDescription) {
this.trainingDataDescription = trainingDataDescription;
}
public String getRemark() {
return this.remark;
}
public void setRemark(String remark) {
this.remark = remark;
}
public String getServerWorkingDir() {
return this.serverWorkingDir;
}
public void setServerWorkingDir(String serverWorkingDir) {
this.serverWorkingDir = serverWorkingDir;
}
public String getStatus() {
return this.status;
}
public void setStatus(String status) {
this.status = status;
}
public Date getCreateTime() {
return this.createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
public Date getModifiedTime() {
return this.modifiedTime;
}
public void setModifiedTime(Date modifiedTime) {
this.modifiedTime = modifiedTime;
}
}

AppModel类：

public class AppModel {
/**
* 模型的所有版本
*/
private Set<AppModelVersion> versions;
/**
* 系统标识
*/
private Integer identityId;
/**
* 模型名称，唯一性。用户输入，格式：网站名_应用名_模型名。比如：CBU_EMAIL_EDM。注意：1、
* 兼容老系统的模型名，比如：ipr，email_edm 2、是模型存放得根目录名 3、是应用方请求的appCode 4、
* 此属性是唯一得，一旦确定就不能更改
*/
private String name;
/**
* 网站
*/
private String website;
/**
* 应用名称
*/
private String application;
/**
* 模型用途
*/
private String useDescription;
/**
* 备注
*/
private String remark;
/**
* 是否激活，默认true。保留属性
*/
private boolean activated = true;
/**
* 创建时间
*/
private Date createTime;
/**
* 修改时间
*/
private Date modifiedTime;
public Set<AppModelVersion> getVersions() {
return versions;
}
public void setVersions(Set<AppModelVersion> versions) {
this.versions = versions;
}
public Integer getIdentityId() {
return this.identityId;
}
public void setIdentityId(Integer identityId) {
this.identityId = identityId;
}
public String getName() {
return this.name;
}
public void setName(String name) {
this.name = name;
}
public String getWebsite() {
return this.website;
}
public void setWebsite(String website) {
this.website = website;
}
public String getApplication() {
return this.application;
}
public void setApplication(String application) {
this.application = application;
}
public String getUseDescription() {
return this.useDescription;
}
public void setUseDescription(String useDescription) {
this.useDescription = useDescription;
}
public String getRemark() {
return this.remark;
}
public void setRemark(String remark) {
this.remark = remark;
}
public boolean isActivated() {
return this.activated;
}
public void setActivated(boolean activated) {
this.activated = activated;
}
public Date getCreateTime() {
return this.createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
public Date getModifiedTime() {
return this.modifiedTime;
}
public void setModifiedTime(Date modifiedTime) {
this.modifiedTime = modifiedTime;
}
}

User类：

public class User implements UserDetails {
/**
* 系统标识
*/
private Integer identityId;
/**
* 登录名
*/
private String loginName;
/**
* 称呼
*/
private String nickName;
/**
* 是否有效
*/
private boolean activated;
/**
* 创建时间
*/
private Date createTime;
/**
* 修改时间
*/
private Date modifiedTime;
/**
* 用户密码
*/
private String password;
/**
* 用户角色列表
*/
private Set<UserRole> roles = new HashSet<UserRole>();
public Integer getIdentityId() {
return identityId;
}
public void setIdentityId(Integer identityId) {
this.identityId = identityId;
}
public String getLoginName() {
return loginName;
}
public void setLoginName(String loginName) {
this.loginName = loginName;
}
public String getNickName() {
return nickName;
}
public void setNickName(String nickName) {
this.nickName = nickName;
}
public void setPassword(String password) {
this.password = password;
}
public boolean isActivated() {
return activated;
}
public void setActivated(boolean activated) {
this.activated = activated;
}
public Date getCreateTime() {
return this.createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
public Date getModifiedTime() {
return this.modifiedTime;
}
public void setModifiedTime(Date modifiedTime) {
this.modifiedTime = modifiedTime;
}
/**
* 实现UserDetails接口方法
*/
public GrantedAuthority[] getAuthorities() {
UserRole defaultRole = new UserRole("USER");
if (!roles.contains(defaultRole)) {
roles.add(defaultRole);
}
GrantedAuthority[] authorities = new GrantedAuthority[this.roles.size()];
return this.roles.toArray(authorities);
}
/**
* 实现UserDetails接口方法
*/
public String getPassword() {
return this.password;
}
/**
* 实现UserDetails接口方法
*/
public String getUsername() {
return this.loginName;
}
/**
* 实现UserDetails接口方法
*/
public boolean isAccountNonExpired() {
//帐号永不过期
return true;
}
/**
* 实现UserDetails接口方法
*/
public boolean isAccountNonLocked() {
//帐号不锁定
return true;
}
/**
* 实现UserDetails接口方法
*/
public boolean isCredentialsNonExpired() {
return true;
}
/**
* 实现UserDetails接口方法
*/
public boolean isEnabled() {
return this.activated;
}
public String getInfo() {
StringBuffer sb = new StringBuffer().append("name = " + loginName);
return sb.toString();
}
public boolean equals(Object obj) {
if (obj == null) {
return false;
}
if (obj == this) {
return true;
}
if (!(obj instanceof User)) {
return false;
}
User user = (User) obj;
return new EqualsBuilder().append(loginName, user.loginName).isEquals();
}
public int hashCode() {
return new HashCodeBuilder(17, 37).append(loginName).toHashCode();
}
}

<insert id="insert" parameterClass="AppModelVersion">
insert into app_model_version(user_login_name,app_model_id,name,version_description,training_data_description,remark,
server_working_dir,status,gmt_created,gmt_modified) values (#createUser.loginName:VARCHAR#,
#appModel.identityId:DECIMAL#,#name:VARCHAR#,#versionDescription:VARCHAR#,#trainingDataDescription:VARCHAR#,#remark:VARCHAR#,
#serverWorkingDir:VARCHAR#,#status:VARCHAR#,now(),now())
</insert>

3、工作中的实例

<sqlMap namespace="modules.company">
<typeAlias alias="address" type="com.alibaba.druid.domain.Address" />
<typeAlias alias="employee" type="com.alibaba.druid.domain.Employee" />
<typeAlias alias="company" type="com.alibaba.druid.domain.Company" />
<resultMap id="companyResult" class="company">
<result property="cid" column="cid" />
<result property="companyId" column="companyId" />
<result property="companyName" column="companyName" />
<result property="officeAddress" column="officeAddress" select="selectAddr" />
<result property="registryMoney" column="registryMoney" />
<result property="companyOwner" column="companyOwner" select="selectEmp" />
</resultMap>
<resultMap id="employeeResult" class="employee">
<result property="eid" column="eid" />
<result property="employeeId" column="employeeId" />
<result property="employeeName" column="employeeName" />
<result property="employeeType" column="employeeType" />
<result property="salary" column="salary" />
<result property="address" column="addressId" select="selectAddr" />
<result property="telePhone" column="telePhone" />
<result property="masterId" column="masterId" />
</resultMap>
<select id="selectEmp" parameterClass="int" resultMap="employeeResult">
select
eid, employeeId,employeeName,employeeType,salary,addressId,telePhone,
masterId from employee where
eid = #eid#
</select>
<select id="selectAddr" parameterClass="int" resultClass="address">
select
addressId, province,community,street,houseNumber from address where
addressId = #addressId#
</select>
<insert id="insertCompany" parameterClass="company">
insert into
Company(cid,companyId, companyName, officeAddress,
registryMoney,
companyOwner) values(#cid#,#companyId#, #companyName#,
#officeAddress.addressId#,
#registryMoney#, #companyOwner.eid#);
</insert>
</sqlMap>

【注意】

1、officeAddress.addressId中的officeAddress是指java程序中的属性，不是指表中的字段，addressId是指address类中的addressId属性，这个编号插入数据库对应的字段即可，该字段的外键为address表中的addressId。

2、companyOwner.eid和上面也是相似的情况。

3、在resultMap中需要定义清楚company表与address表和employee表之间的关系。通过select="selectAddr"属性来表示。selectAddr再通过select来定义清楚。

4、事务的回滚

支持增删改查表中的内容的回滚，不支持数据库的表的回滚操作，例如创建表的回滚。

<sqlMap namespace="modules.sql">
<statement id="createTableOfAllType" parameterClass="String">
CREATE TABLE $tableName$ (F1 FLOAT, F2 DOUBLE, F3 REAL, F4 DATE, F5 TIME, F6 SMALLINT, F7 SMALLINT,
F8 INTEGER, F9 BIGINT, F10 DECIMAL(9,2), F11 TIMESTAMP, F12 BLOB, F13 VARCHAR(256),
F14 VARCHAR(256), F15 VARCHAR(256), F16 VARCHAR(256), F17 SMALLINT, F18 BLOB)
</statement>
</sqlMap>

转载于:https://blog.51cto.com/tianya23/532402

weixin_34337381

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Ibatis - 外键关联

一、查询复杂对象对于有外键的主表映射，在查询返回结果时要使用resultmap进行映射，并在propety里使用select属性进行子查询。 public class PetitionLetter { private int id; private String identifier...
复制链接

扫一扫