Spring, PostgreSQL 연동 with MyBatis

Spring, PostgreSQL 연동 

작성일시 : 2018년 06월 03일

목표 : Spring과 PostgreSQL을 연동하여 DB의 데이터를 서버사이드로 로드

환경 : Spring 3.1.1, PostgreSQL 9.6.14, MyBatis 3.4.1

1. DB 설정

DB에서 로드할 임시 데이터 생성

1.1 테스트 테이블 생성

CREATE TABLE account ( account_idx INTEGER PRIMARY KEY, id character(8) )

1.2 테스트 데이터 생성

2. Spring 설정

수정, 추가할 파일

2.1 pom.xml

필요 라이브러리 추가

...  <dependencies>     ...       <!-- PostgreSQL 9.4 -->     <dependency>         <groupId>org.postgresql</groupId>         <artifactId>postgresql</artifactId>         <version>9.4.1209.jre6</version>     </dependency>     <!-- MyBatis 3.4 -->     <dependency>         <groupId>org.mybatis</groupId>         <artifactId>mybatis</artifactId>         <version>3.4.1</version>     </dependency>     <!-- MyBatis-Spring 1.3-->     <dependency>         <groupId>org.mybatis</groupId>         <artifactId>mybatis-spring</artifactId>         <version>1.3.0</version>     </dependency>     <!-- Spring-JDBC -->     <dependency>         <groupId>org.springframework</groupId>         <artifactId>spring-jdbc</artifactId>         <version>${org.springframework-version}</version>     </dependency>           ... </dependencies> ...

2.2 jdbc.properties

%Project_Home%\src\main\resources 위치에 jdbc.properties 생성 후 DB 정보 입력(URL, ID, Password는 환경에 맞게 수정 필요)

jdbc.driverClassName=org.postgresql.Driver # 고정 jdbc.url=jdbc:postgresql://localhost:5432/postgres # domain 및 db명 환경에 맞게 수정 필요,  # 'postgres'는 postgreSQL 기본 DB로 디폴트로 존재 # PostgreSQL은 디폴트로 5432 Port 사용 jdbc.username=postgres jdbc.password=123qwe # ID, PW

2.3 root-context.xml

%Project_Home%\src\main\webapp\WEB-INF\spring\root-context.xml에 아래와 같이 DB 연동 정보 및 SQL 설정 추가

<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd"> <!-- Root Context: defines shared resources visible to all other web components --> <!-- properties -->     <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">         <property name="locations" value="classpath:/jdbc.properties" />         <property name="fileEncoding" value="UTF-8" />     </bean>          <!-- JDBC-PostgreSQL -->     <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">         <property name="driverClassName" value="${jdbc.driverClassName}" />         <property name="url" value="${jdbc.url}" />         <property name="username" value="${jdbc.username}" />         <property name="password" value="${jdbc.password}" />     </bean>          <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">      <property name="mapperLocations" value="classpath*:sql/**/*.xml"/>       <property name="dataSource" ref="dataSource" />      </bean>           <bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate">      <constructor-arg index="0" ref="sqlSessionFactory" />      </bean> </beans>

2.4 sql.xml

%Project_Home%\src\main\resources밑에 sql 패키지 생성 후 sql.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="sql"> <select id="sel" resultType="com.company.test.HomeDto">     <!-- resultType 본인 환경에 맞게 수정 필요 --> SELECT * FROM account </select> </mapper>

기본 제공 HomeController 수정 및 같은 위치에 아래 파일 생성

2.5 HomeController.java

package com.company.test; import java.util.List; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Controller; import org.springframework.ui.Model; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; /**  * Handles requests for the application home page.  */ @Controller public class HomeController { @Autowired HomeDao homeDao; private static final Logger logger = LoggerFactory.getLogger(HomeController.class); @RequestMapping(value = "/", method = RequestMethod.GET) public String home(Model model) { List<HomeDto> list = homeDao.sel(); for(int i=0; i<list.size(); i++){ logger.info(list.get(i).getAccount_idx()); logger.info(list.get(i).getId()); model.addAttribute("ID", list.get(0).getId() ); } return "home"; } }

2.6 HomeDao.java

package com.company.test; import java.util.List; import org.mybatis.spring.SqlSessionTemplate; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Repository; @Repository public class HomeDao { @Autowired private SqlSessionTemplate sqlSession; public List<HomeDto> sel(){ return sqlSession.selectList("sql.sel"); } }

2.7 HomeDto.java

package com.company.test; public class HomeDto { private String account_idx; private String id; public String getAccount_idx(){ return account_idx; } public void setAccount_idx(String account_idx){ this.account_idx=account_idx; } public String getId(){ return id; } public void setId(String id){ this.id=id; } }

2.8 home.jsp

%Project_Home%\src\main\webapp\WEB-INF\views\home.jsp 수정

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %> <%@ page session="false" %> <html> <head> <title>Home</title> </head> <body> <h1> Hello world!   </h1> <P>  ID is ${ID}. </P> </body> </html>

3. 확인

3.1 브라우저 접근

3.2 콘솔 로그

DB에 있는 account_idx = 1, id=test의 데이터를 서버사이드로 로드

---

그대로 복붙 시 공백 주의