[Spring] spring mybatis postgresql 연동

Spring Tool Suite(STS)는 Maven을 통해 필요한 .jar 파일들을 받아올 수 있다. 이는 프로젝트의 환경을 셋팅하는데 매우 유용하다. 

필요한 파일은 http://mvnrepository.com/에서 검색하면 쉽게 찾을 수 있다. 해당 내용을 프로젝트의 porm.xml에 입력하면 된다.

<!-- mybatis 3.1.1 -->

<dependency>

<groupId>org.mybatis</groupId>

<artifactId>mybatis</artifactId>

<version>3.1.1</version>

</dependency>

<!-- mybatis-spring 1.1.1 -->

<dependency>

<groupId>org.mybatis</groupId>

<artifactId>mybatis-spring</artifactId>

<version>1.1.1</version>

</dependency>

<!-- PostgreSQL 9.1-901 -->

<dependency>

<groupId>postgresql</groupId>

<artifactId>postgresql</artifactId>

<version>9.1-901.jdbc4</version>

</dependency>

STS는 친절하게도 web.xml, root-context.xml, servlet-context.xml으로 분리해 놓고 있다. 뭐... 이건 단순한 프로젝트를 더 복잡하게 만들 수도... 복잡한 프로젝트를 잘 구분해 나누어 놓을 수도... 뭐... 일단 좋다.

먼저 root-context.xml에 postgresql jdbc driver와 mybatis를 셋팅

<!-- PostgreSQL JDBC setting -->

<bean id="dataSource" class="org.springframework.jdbc.datasource.SimpleDriverDataSource" >

<property name="driverClass" value="org.postgresql.Driver" />

<property name="url" value="$jdbc:postgresql://192.168.0.1:5432/TestDB" />

<property name="username" value="postgres" />

<property name="password" value="postgres" />

</bean>

<!-- mybatis setting -->

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean" >

<property name="dataSource" ref="dataSource" />

<property name="configLocation"

                              value="classpath:com/myproject/myapp/mybatisConfig/mybatis-config.xml"/>

</bean>

sqlSessionFactory의 property 중 configLocation의 경로에 xml파일을 만든다.

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "HTTP://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>

<mappers>

<mapper resource="com/myproject/myapp/sqlMap/testSqlMap.xml" />

</mappers>

</configuration>

마찬가지로 testSqlMap.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="testSqlMap" >

<!-- select Query -->

<select id="selectTestTable" resultType="java.util.HashMap">

SELECT * FROM "TableList"

</select>

</mapper>

간단한 테스트용 프로젝트이지만 controller, service, dao로 구분하여 구현을 해보자.

STS가 프로젝트 생성시 자동으로 만드는 HomeController.java를 그대로 Controller로 사용하고,

인터페이스 iMyappDao.java, iMyappService.java

클래스 MyappDaoImpl.java, MyappServiceImpl.java. 를 생성한다.

iMyappDao.java

package com.myproject.myapp.dao;

import java.util.HashMap;

import java.util.List;

public interface iMyappDao {

public List<HashMap<String, Object>> selectTestTable();

}

MyappDaoImpl.java

package com.myproject.myapp.daoImpl;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import org.mybatis.spring.support.SqlSessionDaoSupport;

import com.myproject.myapp.dao.iMyappDao;

public class MyappDaoImpl extends SqlSessionDaoSupport implements iMyappDao{

public List<HashMap<String, Object>> selectTestTable() {

// TODO Auto-generated method stub

List<HashMap<String, Object>> testTableList = new ArrayList<HashMap<String, Object>>();

testTableList = getSqlSession().selectList("testSqlMap.selectTestTable");

return testTableList;

}

}

iMyappService.java

package com.myproject.myapp.service;

import java.util.HashMap;

import java.util.List;

public interface iMyappService {

public List<HashMap<String, Object>> selectTestTable();

}

MyappServiceImpl.java

package com.myproject.myapp.serviceImpl;

import java.util.HashMap;

import java.util.List;

import com.myproject.myapp.dao.iMyappDao;

import com.myproject.myapp.service.iMyappService;

public class MyappServiceImpl implements iMyappService {

private iMyappDao myappDao;

public void setMyappDao(iMyappDao myappDao){

this.myappDao = myappDao;

}

public List<HashMap<String,Object>> selectTestTable() {

// TODO Auto-generated method stub

return myappDao.selectTestTable();

}

}

HomeController.java

package com.myproject.myapp;

import java.text.DateFormat;

import java.util.ArrayList;

import java.util.Date;

import java.util.HashMap;

import java.util.List;

import java.util.Locale;

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;

import com.myproject.myapp.service.iMyappService;

/**

 * Handles requests for the application home page.

 */

@Controller

public class HomeController {

private static final Logger logger = LoggerFactory.getLogger(HomeController.class);

@Autowired

private iMyappService myappService;

/**

* Simply selects the home view to render by returning its name.

*/

@RequestMapping(value = "/", method = RequestMethod.GET)

public String home(Locale locale, Model model) {

logger.info("Welcome home! the client locale is "+ locale.toString());

Date date = new Date();

DateFormat dateFormat = DateFormat.getDateTimeInstance(DateFormat.LONG, DateFormat.LONG, locale);

String formattedDate = dateFormat.format(date);

model.addAttribute("serverTime", formattedDate );

List<HashMap<String, Object>> testList = new ArrayList<HashMap<String, Object>>();

testList = myappService.selectTestTable();

model.addAttribute("selectTableList", testList);

return "home";

}

}

소스파일을 만들었으니 spring bean을 만들어 준다.

root-context.xml에 bean 추가.

<bean id="myappService" class="com.myproject.myapp.serviceImpl.MyappServiceImpl">

<property name="myappDao" ref="myappDao"/>

</bean>

<bean id="myappDao" class="com.myproject.myapp.daoImpl.MyappDaoImpl">

<property name="sqlSessionFactory" ref="sqlSessionFactory" />

</bean>

화면을 보여줄 home.jsp를 수정한다.

결과만 확인할 수 있게 간단히 수정.

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>

<%@ page session="false" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>

<%@ page import="java.util.*" %>

<html>

<head>

<title>Home</title>

</head>

<body>

<h1>

Hello world!  

</h1>

<P>  The time on the server is ${serverTime}. </P>

<%

    int listCount;

List<HashMap<String, Object>> resultTableList = new ArrayList<HashMap<String, Object>>();

resultTableList = (ArrayList<HashMap<String, Object>>)request.getAttribute("selectTableList");

listCount = resultTableList.size();

%>

<p></p>

<p></p>

<form action="">

<div id="testTable">

<table border="1" bordercolor="1313EE" >

<tbody>

<tr>

<td width="33%" align="center">

<label>tbCode</label>

</td>

<td width="33%" align="center">

<label>tableName</label>

</td>

<td width="33%" align="center">

<label>tableContent</label>

</td>

</tr>

<%

if(listCount > 0){

for(int i=0; i<listCount; i++){

HashMap<String, Object> tableListMap = resultTableList.get(i);

%>

<tr>

<td width="33%" align="center">

<label><%= tableListMap.get("tbCode").toString() %></label>

</td>

<td width="33%" align="center">

<label><%= tableListMap.get("tableName").toString() %></label>

</td>

<td width="33%" align="center">

<label><%= tableListMap.get("tableContent").toString() %></label>

</td>

</tr>

<%

}

}

%>

</tbody>

</table>

</div>

</form>

</body>

</html>

postgreSQL table

-- Table: "TableList"

-- DROP TABLE "TableList";

CREATE TABLE "TableList"

(

  "tbCode" character varying(13) NOT NULL,

  "tableName" character varying(40),

  "tableContent" character varying(60),

  CONSTRAINT "pk_tbCode" PRIMARY KEY ("tbCode")

)

WITH (

  OIDS=FALSE

);

ALTER TABLE "TableList"

  OWNER TO postgres;

테이블 컬럼의 데이터는 대충 입력하고.... 

결과