ibatis 关联关系与缓存的配置

    昨天做了个ibatis( + spring)关联关系（一对多、一对一、多对多）和缓存配置的小例子，在这里做一下记录备忘。   

关联关系的配置：

    ibatis的关系映射不像hibernate那样是完全面向对象的，所以可以说ibatis的关系配置是手动建立的。

   

共有四个实体：用户、地址、身份证和公司。

用户->地址：一对多；

用户->身份证：一对一；

用户->公司：多对多

 

数据库我用的是mysql，应用层框架用spring整合的, 所以来看看applicationContext.xml吧。

 

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns:p="http://www.springframework.org/schema/p"
  xmlns:context="http://www.springframework.org/schema/context"
  xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
  http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-2.5.xsd" default-autowire="byName">
 <!-- 使用注解装配方式 (@Resource)-->
 <context:annotation-config /> 
 <!-- org.rocking.ibatis这个包中的注解的组件（@repository）都可以被识别 -->
 <context:component-scan base-package="org.rocking.ibatis"/>
 <!-- 加载jdbc属性文件    路径为类路径(src下) 根目录-->
 <context:property-placeholder location="classpath:jdbc.properties" />
 
 <!-- 配置数据源 --> 
 <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
  <property name="driverClassName" value="${driverClassName}" />
  <property name="url" value="${url}" />
  <property name="username" value="${username}" />
  <property name="password" value="${password}" />
 </bean>
 <!-- 配置SqlMapClient工厂 -->
 <bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean"> 
  <property name="configLocation" value="classpath:sqlMapConfig.xml" />
  <property name="dataSource" ref="dataSource" />
 </bean>
 <!-- 配置抽象父类,BaseDAO继承它 ，实际上不用你关心 ,你的dao继承BaseDAO即可-->
 <bean id="basedao" abstract="true">
  <property name="sqlMapClient" ref="sqlMapClient" />
 </bean>
 </beans>

 

 

再来看看sqlMapConfig.xml的配置吧

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMapConfig PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN"
    "http://www.ibatis.com/dtd/sql-map-config-2.dtd">
<sqlMapConfig>
 <!--
 cacheModelsEnabled="true"  开启缓存  false为关闭
 lazyLoadingEnabled="true"   开启延迟加载机制
 -->
 <settings
    cacheModelsEnabled="true"  
    enhancementEnabled="true"  
    lazyLoadingEnabled="true"/>
 
 <sqlMap resource="org/rocking/ibatis/domain/user-sql.xml" />
 
</sqlMapConfig>

 

 

 

 

建表

 

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for address
-- ----------------------------
DROP TABLE IF EXISTS `address`;
CREATE TABLE `address` (
  `address_id` int(11) NOT NULL auto_increment,
  `address_name` varchar(100) NOT NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY  (`address_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `address_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

 

-- ----------------------------
-- Table structure for company
-- ----------------------------
DROP TABLE IF EXISTS `company`;
CREATE TABLE `company` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

 

 

-- ----------------------------
-- Table structure for idcard
-- ----------------------------
DROP TABLE IF EXISTS `idcard`;
CREATE TABLE `idcard` (
  `id` int(11) NOT NULL auto_increment,
  `number` varchar(100) default NULL,
  `user_id` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `idcard_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

 

 

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL auto_increment,
  `username` varchar(100) NOT NULL,
  `password` varchar(100) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8;

 

-- ----------------------------
-- Table structure for rel_company_user
-- ----------------------------
DROP TABLE IF EXISTS `rel_company_user`;
CREATE TABLE `rel_company_user` (
  `user_id` int(11) NOT NULL default '0',
  `company_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`user_id`,`company_id`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `rel_company_user_ibfk_2` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`),
  CONSTRAINT `rel_company_user_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

 

 

上面的文件为sql文件，复制即可。rel_company_user为中间表，没有对应的实体。 因为user和company是多对多.

 

 

 

sql配置user-sql.xml

 

 

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap     
    PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"     
    "http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap>

 <!--
 配置缓存
 type ="OSCACHE"    使用oscache缓存
 id="user_cache"    缓存的 id
 readOnly="true"    只读缓存
 -->
 
 <cacheModel type ="OSCACHE"  id="user_cache" readOnly="true" serialize="true">
  <!-- 每24小时刷新一次缓存 -->
  <flushInterval hours="24"/>
  <!-- 当执行  "user_insert" 语句时刷新一次缓存-->
  <flushOnExecute statement="user_insert"/>
 </cacheModel>

 <!-- 为当前表对应的 bean 起一个化名 -->
 <typeAlias alias="user" type="org.rocking.ibatis.domain.User" />
 <typeAlias alias="address" type="org.rocking.ibatis.domain.Address" />
 <typeAlias alias="idcard" type="org.rocking.ibatis.domain.IdCard" />
 <typeAlias alias="company" type="org.rocking.ibatis.domain.Company"/>
 
 <!-- 配置一个result Map 关联查询时使用-->
 <resultMap class="user" id="userResult">
  <result property="id" column="id"/>
  <result property="username"/>
  <result property="password"/>
  <!--
  property="addresses" 表示复杂属性名
  column="id"    当执行"getAddresses"时  user的 id列作为参数    (外键)
  select="getAddresses" 当获取关联对象所执行的sql
  -->
  
  <!-- 一对多属性 -->
  <result property="addresses" column="id" select="getAddresses"/>
  <!-- 一对一属性 -->
  <result property="idCard" column="id" select="getIdCard"/>
  <!-- 多对多属性 -->
  <result property="companies" column="id" select="getCompanies"/>
  
 </resultMap>
 
 <resultMap class="address" id="addressResult">
  <result property="addressId"/>
  <result property="addressName"/>
 </resultMap>
 
 <resultMap class="idcard" id="idCardResult">
  <result property="id"/>
  <result property="number"/>
 </resultMap>
 
 <resultMap class="company" id="companyResult">
  <result property="id"/>
  <result property="name"/>
  <result property="users" column="id" select="getUsersByCompanyId"/>
 </resultMap>
 
 <select id="getUsersByCompanyId"  parameterClass="int" resultMap="userResult">
  SELECT * FROM company
   WHERE id in(SELECT user_id FROM rel_company_user
    WHERE company_id = #value#)
 </select>
 
 <select id="getAddresses" parameterClass="int" resultMap="addressResult">
  SELECT * FROM address WHERE user_id = #value#
 </select>
 
 <select id="getIdCard" parameterClass="int" resultMap="idCardResult">
  SELECT * FROM idcard WHERE user_id = #value#
 </select>
 
 <select id="getCompanies" parameterClass="int" resultMap="companyResult">
  SELECT * FROM company
   WHERE id in(SELECT company_id FROM rel_company_user
    WHERE user_id = #value#)
 </select>
 
 <insert id="user_insert" parameterClass="user">
  INSERT INTO user(username,password)
  VALUES(#username#,#password#)
 </insert>
 
 <select id="user_select" parameterClass="int" resultClass="user" resultMap="userResult">
  SELECT * FROM user
  WHERE id = #value#
 </select>
 
 <select id="user_list" resultClass="user" cacheModel="user_cache">
  SELECT * FROM user
 </select>
   
</sqlMap>

 

 

 

public class Address {

 private int addressId;
 private String addressName;

 ......getter && setter

}

 

 

 

public class IdCard {

 private int id;
 private String number;
 ......getter && setter

}

 

 

 

public class Company {

 private int id;
 private String name;

......getter && setter
 
}

 

 

创建User接口

 

public interface UserDAO {

   /**
    * 保存
    * */
   public void save(User user);
 
   /**
    * 通过id查
    * */
   public User get(Serializable id);

   /**
    * 查询所有
    * */
   public List<User> query();
}

 

实现User接口

@Repository(value="userDAO")//相当于在applicationContext.xml中配置一个id为"userDAO"的bean
public class UserDAOImpl extends BaseDAO implements UserDAO {

   @Override
   public User get(Serializable id) {
    return (User)super.queryForObject("user_select", id);
   }

   @Override
   public void save(User user) {
    insert("user_insert", user);
   }

   @SuppressWarnings("unchecked")
   @Override
   public List<User> query(){
    return super.queryForList("user_list");
   }

}

 

一切准备好了 可以测试了

 

public class UserDAOTest extends ParentTest {

 /**
  * @param args
  */
 public static void main(String[] args) {
  
  ApplicationContext ac = new ClassPathXmlApplicationContext("applicationContext.xml");

  UserDAO userDAO = (UserDAO)ac.getBean("userDAO");
  
  /*for(int i=0;i<100000;i++){
   User user = new User();
   user.setUsername("username" + (i+1));
   user.setPassword("password" + (i+1));
   userDAO.save(user);
  }*/
  /*long t1 = System.currentTimeMillis();
  userDAO.query();
  userDAO.query();
  userDAO.query();
  userDAO.query();
  userDAO.query();
  userDAO.query();
  userDAO.query();
  userDAO.query();
  userDAO.query();
  userDAO.query();
  long t2 = System.currentTimeMillis();
  System.out.println("执行查询使用了" + (t2-t1) + "ms");*/
  User user = userDAO.get(1);
  List<Company> companies = user.getCompanies();
  for(Company c: companies){
   System.out.println(c.getName());
  }
 }

}

 

 

建立pojo

 

public class User {

 private int id;
 private String username;
 private String password;
 
 private List<Address> addresses = new ArrayList<Address>();
 
 private IdCard idCard;
 
 private List<Company> companies = new ArrayList<Company>();
 
 ......getter && setter
 
}