Ian's Archive 🏃🏻

Ian

Ian's Archive

Developer / React, SpringBoot ...

📍 Korea

Github Profile →

Spring에서 MyBatis사용하기

Spring

2024.12.09.

1. MyBatis란?

Mybatis는 질의 후 수행해야 하는 객체 매핑을 대신 수행해줌으로써 프로그래머가 SQL작성에만 신경 쓸 수 있게하는 Persistance Framework이다.

SQL을 XML에 편리하게 작성 가능
동적 쿼리를 매우 편리하게 작성

2. MyBatis 설치 및 설정

pom.xml설정

복사

<dependencies>
    ...

    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>${org.springframework-version}</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-tx</artifactId>
        <version>${org.springframework-version}</version>
    </dependency>
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>${org.mybatis-version}</version>
    </dependency>
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis-spring</artifactId>
        <version>${org.mybatis-spring-version}</version>
    </dependency>
    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <version>42.6.0</version>
    </dependency>
    <dependency>
        <groupId>com.zaxxer</groupId>
        <artifactId>HikariCP</artifactId>
        <version>5.0.1</version>
    </dependency>
    ...
</dependencies>

root-context.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:mybatis-spring="http://mybatis.org/schema/mybatis-spring"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="
        http://mybatis.org/schema/mybatis-spring http://mybatis.org/schema/mybatis-spring-1.2.xsd
        http://www.springframework.org/schema/beans https://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd">

    <!-- HikariCP Configuration -->
    <bean id="hikariConfig" class="com.zaxxer.hikari.HikariConfig">
        <property name="driverClassName" value="org.postgresql.Driver"/>
        <property name="jdbcUrl" value="jdbc:postgresql://localhost:5433/postgres"/>
        <property name="username" value="postgres"/>
        <property name="password" value="egis"/>
        <property name="maximumPoolSize" value="10"/>
        <property name="minimumIdle" value="2"/>
        <property name="idleTimeout" value="30000"/>
        <property name="connectionTimeout" value="20000"/>
    </bean>

    <!-- HikariCP DataSource -->
    <bean id="datasource" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
        <constructor-arg ref="hikariConfig"/>
    </bean>

    <!-- MyBatis SqlSessionFactory -->
    <!--  Mapper위치 매핑  -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
	    <property name="dataSource" ref="datasource"/>
	    <property name="mapperLocations" value="classpath*:com/egis/prj/mapper/*.xml"/>
	    <property name="typeAliasesPackage" value="com.egis.prj.model"/>
	</bean>

	<!-- SqlSessionTemplate 설정 -->
	<bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate">
	    <constructor-arg ref="sqlSessionFactory" />
	</bean>

</beans>

Mapper작성

패키지 경로 동일하게 설정

src/main/resources/com/egis/prj/mapper/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="PolygonMapper">
    <insert id="insert">
        INSERT INTO polygon (id, geom)
        VALUES (
        #{id},
        ST_SetSRID(
        ST_MakePolygon(
        ST_MakeLine(ARRAY[
        <foreach collection="coordinates" item="coordinate" separator=",">
            ST_MakePoint(#{coordinate.longitude}, #{coordinate.latitude}, #{coordinate.altitude})
        </foreach>
        ])
        ),
        4326
        )
        )
    </insert>
    <delete id="deleteOne">
        DELETE
        FROM polygon
        WHERE id = #{id}
    </delete>
    <delete id="deleteAll">
        DELETE
        FROM polygon
    </delete>
    <!-- selectAll 쿼리에서 PolygonVO와 PointVO 매핑 -->
    <resultMap id="lineWithCoordinates" type="com.egis.prj.model.PolygonVO">
        <id property="id" column="id"/>
        <collection property="coordinates" ofType="com.egis.prj.model.PointVO">
            <!-- geom에서 X와 Y 좌표를 추출 -->
            <result property="longitude" column="longitude"/>
            <result property="latitude" column="latitude"/>
            <result property="altitude" column="altitude"/>
        </collection>
    </resultMap>

    <select id="selectAll" resultMap="lineWithCoordinates">
        SELECT id,
               ST_X(pt.geom) AS longitude,
               ST_Y(pt.geom) AS latitude,
               ST_Z(pt.geom) AS altitude
        FROM polygon, LATERAL ST_DumpPoints(geom) AS pt;
    </select>
</mapper>

DAO작성, DTO작성

복사

package com.egis.prj.dao;

import com.egis.prj.model.PolygonVO;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class PolygonDao {

    @Autowired
    private SqlSessionTemplate sqlSessionTemplate;

    public void insert(PolygonVO polygon) {
        sqlSessionTemplate.insert("PolygonMapper.insert", polygon);
    }

    public void deleteOne(PolygonVO polygon) {
        sqlSessionTemplate.delete("PolygonMapper.deleteOne", polygon.getId());
    }

    public void deleteAll() {
        sqlSessionTemplate.delete("PolygonMapper.deleteAll");
    }

    public List<PolygonVO> selectAll() {
        return sqlSessionTemplate.selectList("PolygonMapper.selectAll");
    }
}

복사

@Getter @Setter
@NoArgsConstructor
@AllArgsConstructor
public class LineVO {
    private int id;
    private List<PointVO> coordinates; // 좌표 리스트
}

@Getter @Setter
@NoArgsConstructor
@AllArgsConstructor
public class PointVO {
    private double longitude;
    private double latitude;
    private double altitude;
}

controller, service 작성

POIController

복사

@Controller
public class LineController {

    @Autowired
    private LineService lineService;

    @GetMapping("/")
    public String home() {
        return "home";
    }

    //
    @PostMapping("/createLine")
    public ResponseEntity<String> createLine(@RequestBody LineVO lineVO) {
        lineService.insertLineData(lineVO);
        return new ResponseEntity<>("scuess", HttpStatus.OK);
    }

    @PostMapping("/deleteLine")
    public ResponseEntity<String> deleteLine(@RequestBody LineVO lineVO) {
        lineService.deleteLineData(lineVO);
        return new ResponseEntity<>("scuess", HttpStatus.OK);
    }

    @PostMapping("/deleteAllLine")
    public ResponseEntity<String> deleteAllLine() {
        lineService.allDeleteLineData();
        return new ResponseEntity<>("scuess", HttpStatus.OK);
    }
}

POIService

복사

import com.egis.prj.model.LineVO;
import com.egis.prj.model.PointVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.egis.prj.dao.LineDao;
import com.egis.prj.service.LineService;

import java.util.List;

@Service
public class LineServiceImpl implements LineService {

	@Autowired
    private LineDao lineDao;

    @Override
    public void insertLineData(LineVO lineVO) {
        lineDao.insert(lineVO);
    }

    @Override
    public void deleteLineData(LineVO lineVO) {
        lineDao.deleteOne(lineVO);
    }

    @Override
    public void allDeleteLineData() {
        lineDao.deleteAll();
    }

    private String createGeometryText(List<PointVO> coordinates) {
        StringBuilder sb = new StringBuilder("LINESTRINGZ(");
        for (int i = 0; i < coordinates.size(); i++) {
            PointVO point = coordinates.get(i);
            sb.append(point.getLongitude())
                    .append(" ")
                    .append(point.getLatitude())
                    .append(" ")
                    .append(point.getAltitude());

            if (i < coordinates.size() - 1) {
                sb.append(", ");
            }
        }
        sb.append(")");
        return sb.toString();
    }
}

위 나열한 소스처럼 설정을 하나씩 따라하고, Dao, Dto, Controller, Service 코드를 작성하면 된다.

mapper에서 조금 어려웠던 부분을 조금 자세히 정리하고 넘어간다.

위 사진처럼 공간데이터 중 line정보를 저장해야 했다.

복사

    <insert id="insertLine">
        INSERT INTO lines (id, geom)
        VALUES (
        #{id},
        ST_SetSRID(
        ST_MakeLine(
        ARRAY[
        <foreach collection="coordinates" item="coordinate" separator=",">
            ST_MakePoint(#{coordinate.longitude}, #{coordinate.latitude}, #{coordinate.altitude})
        </foreach>
        ]
        ),
        4326
        )
        )
    </insert>

복사

INSERT INTO lines (id, geom)
VALUES ( 22532724,
    ST_SetSRID(
        ST_MakeLine(
            ARRAY[
                ST_MakePoint(127.06115384536608, 37.508410135476886, 17.757772529497743) ,
                ST_MakePoint(127.06308502872506, 37.50886850393438, 17.786800945177674) ,
                ST_MakePoint(127.06268970863653, 37.50988179984271, 17.746766459196806)
            ]
        ),
        4326
    )
);

포인트 몇가지를 정리해보자

를 사용해 coordinates라는 리스트에서 각 좌표를 반복적으로 처리하며 배열(ARRAY)로 만든다.
ST_MakePoint로 개별 좌표(longitude, latitude, altitude)를 3D 포인트로 만든다.
ARRAY는 PostgreSQL에서 여러 값을 하나의 배열로 묶는 SQL기능
여기서 ARRAY[...]는 여러 개의 ST_MakePoint호출을 배열로 묶어 ST_MakeLine함수에 전달
ST_MakeLine으로 배열에 포함된 포인트들을 이어 LINESTRING 객체를 생성 (하나의 선으로 만든다.)
이 선을 ST_SetSRID로 SRID 4326(표준 좌표계) 으로 설정

Reference

1. MyBatis란?
2. MyBatis 설치 및 설정
Reference

Modern Java In Action 1장 정리

MyBatis log찍기