I use myBatis to map a simple database (as an example).
It consists of 4 models: User, Car, Tariff, Insurance.
User has private List carList and private Tariff tariff and some other fields with getters and setters.
Car has private Insurance insurance and some other fields with getters and setters.
So I can map only 1st nesting level. I mean i can map User and its fields - Tariff and a List of Cars. But I can't map Insurance field of Car. What should I do?
Here is my 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">
<mapper namespace = "UserNamespace">
<resultMap id="resultUser" type="User">
<id property="id" column="id"/>
<result property="name" column="name"/>
<association property="tariff" column="tariff" select="getTariff" javaType="Tariff"/>
<collection property="carList" column="id" select="getCars" javaType="ArrayList" ofType="Car">
<id property="id" column="id"/>
<result property="model" column="model"/>
<association property="insurance" column="insurance" select="getInsurance" javaType="Insurance"/>
</collection>
</resultMap>
<select id = "getAll" resultMap = "resultUser">
SELECT * FROM carwashservice.users
</select>
<select id = "getTariff" parameterType="int" resultType="Tariff">
SELECT tariffs.description FROM carwashservice.tariffs WHERE tariffs.id = #{id}
</select>
<select id = "getCars" parameterType="int" resultType="Car">
SELECT * FROM carwashservice.cars WHERE cars.user = #{id}
</select>
<select id = "getInsurance" parameterType="int" resultType="Insurance">
SELECT * FROM carwashservice.insurance WHERE insurance.id = #{insurance}
</select>
</mapper>
And my DB:
CREATE DATABASE IF NOT EXISTS `carwashservice` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `carwashservice`;
DROP TABLE IF EXISTS `cars`;
CREATE TABLE `cars` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`model` VARCHAR(45) NOT NULL,
`user` INT(11) DEFAULT NULL,
`insurance` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `user_idx` (`user`),
KEY `insurance_idx` (`insurance`),
CONSTRAINT `user` FOREIGN KEY (`user`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `insurance`;
CREATE TABLE `insurance` (
`id` INT(11) NOT NULL,
`cost` VARCHAR(45) NOT NULL,
`exp_date` VARCHAR(45) NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `id` FOREIGN KEY (`id`) REFERENCES `cars` (`insurance`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `tariffs`;
CREATE TABLE `tariffs` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`description` VARCHAR(45) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `id` (`id`,`description`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
`id` INT(11) NOT NULL,
`name` VARCHAR(45) NOT NULL,
`tariff` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `tariff_idx` (`tariff`),
KEY `id` (`id`,`name`),
CONSTRAINT `tariff` FOREIGN KEY (`tariff`) REFERENCES `tariffs` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
