Spring Boot, MyBatis One-to-Many and One-to-One Selects - XML Mapping Example

In this section, we will learn how to handle One-to-Many and One-to-One selects in Spring Boot, MyBatis application (XML Mapping Example).


Technologies used:

  • Spring Boot 3.0.2
  • MyBatis 3
  • Java 17
  • Maven 
  • H2 Database

Creating a simple spring boot web application:

First, open the Spring initializr https://start.spring.io/ 

Then, Provide the Group and Artifact name. We have provided Group name com.knf.dev.demo and Artifact spring-boot-mybatis-xml-one-to-many-one-to-one. Here I selected the Maven project - language Java 17 - Spring Boot 3.0.2 and add Spring web dependencyMyBatis Framework and H2 Database.


Then, click on the Generate button. When we click on the Generate button, it starts packing the project in a .zip(spring-boot-mybatis-xml-one-to-many-one-to-one) file and downloads the project. Then, Extract the Zip file. 
Then, import the project on your favourite IDE.

Final Project directory:


pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0
https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>3.0.2</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.knf.dev.demo</groupId>
<artifactId>spring-boot-mybatis-xml-one-to-many-one-to-one</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>spring-boot-mybatis-xml-one-to-many-one-to-one</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>17</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>3.0.0</version>
</dependency>

<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>

<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>

</project>

Create tables and load initial data.

schema.sql

CREATE TABLE IF NOT EXISTS users
(
email CHARACTER VARYING(30) NOT NULL,
name CHARACTER VARYING(30),
PRIMARY KEY (email)
);
CREATE TABLE IF NOT EXISTS cards
(

PRIMARY KEY (id),
id INTEGER NOT NULL,
cardnumber CHARACTER VARYING(30),
cardtype CHARACTER VARYING(50),
email CHARACTER VARYING(50),
CONSTRAINT fk_group
FOREIGN KEY(email)
REFERENCES users(email)
);

data.sql

insert into users (email, name) values ('alpha@gmail.com', 'Alpha');
insert into users (email, name) values ('beta@gmail.com', 'Beta');
insert into users (email, name) values ('gama@gmail.com', 'Gama');

insert into cards (id, cardnumber, cardtype, email)
values (1, '11111111', 'Mastercard', 'gama@gmail.com');
insert into cards (id, cardnumber, cardtype, email)
values (2, '22222222', 'Visa', 'gama@gmail.com');
insert into cards (id, cardnumber, cardtype, email)
values (3, '33333333', 'PayPal', 'gama@gmail.com');
insert into cards (id, cardnumber, cardtype, email)
values (4, '4444444', 'Visa', 'beta@gmail.com');
insert into cards (id, cardnumber, cardtype, email)
values (5, '5555555', 'PayPal', 'beta@gmail.com');
insert into cards (id, cardnumber, cardtype, email)
values (6, '6666666', 'Mastercard', 'alpha@gmail.com');
insert into cards (id, cardnumber, cardtype, email)
values (7, '7777777', 'Visa', 'alpha@gmail.com');

application.properties

mybatis.mapper-locations is a key configuration. It specifies the path to load the xml file for the mapper interface (wildcards can be used). 
logging.level.com.knf.dev.demo=TRACE

#Configure the xml Mapping path
mybatis.mapper-locations=classpath:mapper/*.xml

Create User Bean

package com.knf.dev.demo.model;

import java.util.List;

public class User {

private String email;
private String name;
private List<Card> cards;

public String getEmail() {
return email;
}

public void setEmail(String email) {
this.email = email;
}

public String getName() {
return name;
}

public void setName(String name) {
this.name = name;
}

public List<Card> getCards() {
return cards;
}

public void setCards(List<Card> cards) {
this.cards = cards;
}
}

Create Card Bean

package com.knf.dev.demo.model;

public class Card {

private Integer id;
private String cardNumber;
private String cardType;
private String email;
private User user;

public String getEmail() {
return email;
}

public void setEmail(String email) {
this.email = email;
}

public Integer getId() {
return id;
}

public void setId(Integer id) {
this.id = id;
}

public String getCardNumber() {
return cardNumber;
}

public void setCardNumber(String cardNumber) {
this.cardNumber = cardNumber;
}

public String getCardType() {
return cardType;
}

public void setCardType(String cardType) {
this.cardType = cardType;
}

public User getUser() {
return user;
}

public void setUser(User user) {
this.user = user;
}
}

Create User Repository

package com.knf.dev.demo.repository;

import com.knf.dev.demo.model.User;

public interface UserRepository {
User selectUserById(String email);

}

Create Card Repository

package com.knf.dev.demo.repository;

import com.knf.dev.demo.model.Card;

public interface CardRepository {
Card selectCardById(Integer id);

}

Create UserMapper.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="com.knf.dev.demo.repository.UserRepository" >
<!-- result maps -->
<resultMap id="UserResultMap" type="com.knf.dev.demo.model.User" >
<id column="email" property="email" />
<result column="name" property="name" />
<!-- collections of Cards -->
<collection property="cards" ofType="com.knf.dev.demo.model.Card"
column="email" select="selectCardFromUser" />
</resultMap>

<resultMap id = "CardResultMap"
type = "com.knf.dev.demo.model.Card">
<id property = "id" column = "id"/>
<result property = "cardNumber" column = "cardnumber"/>
<result property = "cardType" column = "cardtype"/>
<result property = "email" column = "email"/>
</resultMap>

<!-- one to many select -->
<select id="selectUserById"
resultMap="UserResultMap"
parameterType="java.lang.String" >
SELECT
users.name,
users.email
FROM
users
WHERE users.email = #{email}
</select>

<select id="selectCardFromUser"
parameterType="java.lang.String"
resultType="com.knf.dev.demo.model.Card"
resultMap="CardResultMap">
SELECT
cards.id,
cards.email,
cards.cardnumber,
cards.cardtype
FROM
cards
WHERE
email = #{email}
</select>
</mapper>

Create CardMapper.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="com.knf.dev.demo.repository.CardRepository" >
<!-- result maps -->
<resultMap id="CardResultMap" type="com.knf.dev.demo.model.Card" >
<id property = "id" column = "id"/>
<result property = "cardNumber" column = "cardnumber"/>
<result property = "cardType" column = "cardtype"/>
<result property = "email" column = "email"/>

<!-- one to one -->
<association property="user" column="email"
javaType="com.knf.dev.demo.model.User"
select="selectUserFromCard"/>
</resultMap>

<!-- one to one select -->
<select id="selectCardById"
resultMap="CardResultMap"
parameterType="java.lang.Integer" >
SELECT
cards.id,
cards.email,
cards.cardnumber,
cards.cardtype
FROM
cards
WHERE
id = #{id,jdbcType=INTEGER}
</select>

<select id="selectUserFromCard"
parameterType="java.lang.String"
resultType="com.knf.dev.demo.model.User">
SELECT
users.name,
users.email
FROM
users
WHERE
email = #{email}
</select>
</mapper>

Create User Controller

package com.knf.dev.demo.controller;

import com.knf.dev.demo.model.Card;
import com.knf.dev.demo.model.User;
import com.knf.dev.demo.repository.CardRepository;
import com.knf.dev.demo.repository.UserRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping("/api/v1/")
public class UserController {

@Autowired
UserRepository userRepository;
@Autowired
CardRepository cardRepository;

@GetMapping("users/{email}")
public User getUserById(@PathVariable String email)
{
return userRepository.selectUserById(email);
}

@GetMapping("cards/{id}")
public Card getCardById(@PathVariable Integer id)
{
return cardRepository.selectCardById(id);
}
}

Run the application - Application.java

Set the package where the mapper interface resides (there can be more than one) via the @MapperScan annotation. 
package com.knf.dev.demo;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("com.knf.dev.demo.repository")
public class Application {

public static void main(String[] args) {
SpringApplication.run(Application.class, args);
}

}
Application is the entry point that sets up the Spring Boot application. The @SpringBootApplication annotation enables auto-configuration and component scanning. 

Step1: Download or clone the source code from GitHub to a local machine - Click here!


Step 2: mvn clean install


Step 3: Run the Spring Boot application - mvn spring-boot:run


OR 


Run this Spring boot application from 

  • IntelliJ IDEA IDE by right click - Run 'Application.main()' 
  • Eclipse/STS - You can right click the project or the Application.java file and run as java application or Spring boot application.


Verify APIs using Postman:

1. Get User by ID (email): localhost:8080/api/v1/users/beta@gmail.com


2. Get Card by ID: localhost:8080/api/v1/cards/4

Popular posts from this blog

Learn Java 8 streams with an example - print odd/even numbers from Array and List

Java Stream API - How to convert List of objects to another List of objects using Java streams?

Registration and Login with Spring Boot + Spring Security + Thymeleaf

Java, Spring Boot Mini Project - Library Management System - Download

ReactJS, Spring Boot JWT Authentication Example

Top 5 Java ORM tools - 2024

Java - Blowfish Encryption and decryption Example

Spring boot video streaming example-HTML5

Google Cloud Storage + Spring Boot - File Upload, Download, and Delete