Last Modified 2022.9.28

간단한 Spring MyBatis 예제

C:\Users\john>sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on Sun SEP 25 11:53:25 2022

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter user-name: scott
Enter password:

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL>

create table music (
no number,
content varchar2(4000),
constraint PK_MTV PRIMARY KEY(no)
);

create sequence SEQ_MUSIC
increment by 1
start with 1;

mybatismusic
├── pom.xml
├── src
│   └── main
│       ├── java
│       │   └── net
│       │       └── java_school
│       │           ├── mybatis
│       │           │   └── MusicVideoMapper.java
│       │           └── mybatismusic
│       │               ├── HomeController.java
│       │               ├── MusicVideo.java
│       │               ├── MusicVideoService.java
│       │               └── MusicVideoServiceImpl.java
│       ├── resources
│       │   ├── commons-logging.properties
│       │   ├── log4j2.xml
│       │   └── net
│       │       └── java_school
│       │           └── mybatis
│       │               ├── Configuration.xml
│       │               └── MusicVideoMapper.xml
│       └── webapp
│           ├── WEB-INF
│           │   ├── applicationContext.xml
│           │   ├── mybatismusic-servlet.xml
│           │   ├── views
│           │   │   └── index.jsp
│           │   └── web.xml
│           └── resources
│               └── js
│                   └── jquery-3.6.1.min.js
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 
	http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>

<groupId>net.java_school</groupId>
<artifactId>mybatismusic</artifactId>
<version>1.0-SNAPSHOT</version>
<packaging>war</packaging>

<name>mybatismusic Maven Webapp</name>
<url>http://localhost:8080/mybatismusic</url>

<properties>
	<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
	<jdk.version>11</jdk.version>
	<spring.version>5.3.33</spring.version>
</properties>

<dependencies>
	<dependency>
		<groupId>junit</groupId>
		<artifactId>junit</artifactId>
		<version>4.11</version>
		<scope>test</scope>
	</dependency>
	<dependency>
		<groupId>javax.servlet</groupId>
		<artifactId>javax.servlet-api</artifactId>
		<version>4.0.1</version>
		<scope>provided</scope>
	</dependency>
	<dependency>
		<groupId>jstl</groupId>
		<artifactId>jstl</artifactId>
		<version>1.2</version>
	</dependency>
	<dependency>
		<groupId>org.springframework</groupId>
		<artifactId>spring-context</artifactId>
		<version>${spring.version}</version>
	</dependency>
	<dependency>
		<groupId>org.springframework</groupId>
		<artifactId>spring-webmvc</artifactId>
		<version>${spring.version}</version>
	</dependency>
	<dependency>
		<groupId>org.springframework</groupId>
		<artifactId>spring-jdbc</artifactId>
		<version>${spring.version}</version>
	</dependency>
	<dependency>
		<groupId>com.oracle</groupId>
		<artifactId>ojdbc6</artifactId>
		<version>11.2.0.2.0</version>
	</dependency>
	<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis-spring -->
	<dependency>
		<groupId>org.mybatis</groupId>
		<artifactId>mybatis-spring</artifactId>
		<version>2.0.7</version>
	</dependency>
	<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
	<dependency>
		<groupId>org.mybatis</groupId>
		<artifactId>mybatis</artifactId>
		<version>3.5.10</version>
	</dependency>
	<!-- https://mvnrepository.com/artifact/commons-dbcp/commons-dbcp -->
	<dependency>
		<groupId>commons-dbcp</groupId>
		<artifactId>commons-dbcp</artifactId>
		<version>1.4</version>
	</dependency>
	<!-- https://mvnrepository.com/artifact/org.apache.logging.log4j/log4j-api -->
	<dependency>
		<groupId>org.apache.logging.log4j</groupId>
		<artifactId>log4j-api</artifactId>
		<version>2.18.0</version>
	</dependency>
	<!-- https://mvnrepository.com/artifact/org.apache.logging.log4j/log4j-core -->
	<dependency>
		<groupId>org.apache.logging.log4j</groupId>
		<artifactId>log4j-core</artifactId>
		<version>2.18.0</version>
	</dependency>
	<!-- https://mvnrepository.com/artifact/org.apache.logging.log4j/log4j-jcl -->
	<dependency>
		<groupId>org.apache.logging.log4j</groupId>
		<artifactId>log4j-jcl</artifactId>
		<version>2.18.0</version>
	</dependency>
</dependencies>

<build>
	<finalName>mybatismusic</finalName>
	<pluginManagement>
		<plugins>
			<plugin>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>3.10.1</version>
				<configuration>
					<source>${jdk.version}</source>
					<target>${jdk.version}</target>
					<compilerArgument></compilerArgument>
					<encoding>UTF-8</encoding>
				</configuration>
			</plugin>
			<plugin>
				<artifactId>maven-clean-plugin</artifactId>
				<version>3.2.0</version>
				<configuration>
					<filesets>
						<fileset>
							<directory>src/main/webapp/WEB-INF/classes</directory>
						</fileset>
						<fileset>
							<directory>src/main/webapp/WEB-INF/lib</directory>
						</fileset>
					</filesets>
				</configuration>
			</plugin>
		</plugins>
	</pluginManagement>
</build>
</project>

src/main/resources

commons-logging.properties
org.apache.commons.logging.Log = org.apache.commons.logging.impl.Log4JLogger
log4j2.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration>
<Configuration>
    <Appenders>
        <File name="MyBatisMusic" fileName="/var/log/tomcat9/MyBatisMusic.log" append="false">
            <PatternLayout pattern="%t %-5p %c{2} - %m%n" />
        </File>
        <Console name="STDOUT" target="SYSTEM_OUT">
            <PatternLayout pattern="%d %-5p [%t] %C{2} (%F:%L) - %m%n" />
        </Console>
    </Appenders>
    <Loggers>
        <Logger name="net.java_school" level="DEBUG">
            <AppenderRef ref="MyBatisMusic" />
        </Logger>
        <Root level="INFO">
            <AppenderRef ref="STDOUT" />
        </Root>
    </Loggers>
</Configuration>

src/main/resources/net/java_school/mybatis

Configuration.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>
	<settings>
		<setting name="logImpl" value="LOG4J2"/>
	</settings>

	<typeAliases>
		<typeAlias type="net.java_school.mybatismusic.MusicVideo" alias="MusicVideo" />
	</typeAliases>

</configuration>
MusicVideoMapper.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="net.java_school.mybatis.MusicVideoMapper">

    <select id="selectCountOfVideos" resultType="int">
        SELECT count(*) FROM music 
    </select>

    <select id="selectVideos" parameterType="hashmap" resultType="MusicVideo">
        SELECT content 
        FROM (
            SELECT rownum R,A.* 
            FROM (
                SELECT 
                    content
                FROM 
                    music
                ORDER BY no DESC
                 ) A
             )
        WHERE R BETWEEN #{start} AND #{end}
    </select>	

    <insert id="insert">
	    insert into music values (seq_music.nextval, #{content})
    </insert>
	
</mapper>

src/main/java/net/java_school/mybatis

MusicVideoMapper.java
package net.java_school.mybatis;

import java.util.HashMap;
import java.util.List;

import net.java_school.mybatismusic.MusicVideo;

import org.apache.ibatis.annotations.Param;

public interface MusicVideoMapper {

	public int selectCountOfVideos();

	public List<MusicVideo> selectVideos(HashMap<String, String> hashmap);

	public void insert(@Param("content") String content);

}

src/main/java/net/java_school/mybatismusic

MusicVideo.java
package net.java_school.mybatismusic;

public class MusicVideo {
	private int no;
	private String content;

	public MusicVideo() {}

	public MusicVideo(int no, String content) {
		this.no = no;
		this.content = content;
	}

	public int getNo() {
	       return no;
	}

	public void setNo(int no) {
		this.no = no;
	}

	public String getContent() {
		return content;
	}

	public void setContent(String content) {
		this.content = content;
	}
}
MusicVideoService.java
package net.java_school.mybatismusic;

import java.util.List;

public interface MusicVideoService {
	
	public int getTotalRecords();

	public List<MusicVideo> getVideos(Integer startRecord, Integer endRecord);

	public void add(String content);
}
MusicVideoServiceImpl.java
package net.java_school.mybatismusic;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.HashMap;

import net.java_school.mybatis.MusicVideoMapper;

@Service
public class MusicVideoServiceImpl implements MusicVideoService {

	@Autowired
	private MusicVideoMapper musicVideoMapper;

	@Override
	public int getTotalRecords() {
		return musicVideoMapper.selectCountOfVideos();
	}

	@Override
	public List<MusicVideo> getVideos(Integer startRecord, Integer endRecord) {
		HashMap<String, String> hashmap = new HashMap<String, String>();
		hashmap.put("start", startRecord.toString());
		hashmap.put("end", endRecord.toString());

		return musicVideoMapper.selectVideos(hashmap);

	}

	@Override
	public void add(String content) {
		musicVideoMapper.insert(content);
	}

}
HomeController.java
package net.java_school.mybatismusic;

import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.ResponseBody;

import java.util.List;
import java.util.Map;
import java.util.HashMap;

@Controller
public class HomeController {

	@Autowired
	private MusicVideoService musicVideoService;

	private Map<String, Integer> getNumbersForPaging(int totalRecord, 
			int page, int numPerPage, int pagePerBlock) {

		Map<String, Integer> map = new HashMap<String, Integer>();

		int totalPage = totalRecord / numPerPage;
		if (totalRecord % numPerPage != 0) totalPage++;

		int totalBlock = totalPage / pagePerBlock;
		if (totalPage % pagePerBlock != 0) totalBlock++;

		int block = page / pagePerBlock;
		if (page % pagePerBlock != 0) block++;

		int firstPage = (block - 1) * pagePerBlock + 1;
		int lastPage = block * pagePerBlock;

		int prevPage = 0;
		if (block > 1) prevPage = firstPage - 1;

		int nextPage = 0;
		if (block < totalBlock) nextPage = lastPage + 1;
		if (block >= totalBlock) lastPage = totalPage;
		
		int listItemNo = totalRecord - (page - 1) * numPerPage;
		int startRecord = (page - 1) * numPerPage + 1;
		int endRecord = page * numPerPage;

		map.put("totalPage", totalPage);
		map.put("firstPage", firstPage);
		map.put("lastPage", lastPage);
		map.put("prevPage", prevPage);
		map.put("nextPage", nextPage);
		map.put("startRecord", startRecord);
		map.put("endRecord", endRecord);

		return map;
	}

	@GetMapping("/")
	public String index(Integer page, Model model) {
		if (page == null) return "redirect:/?page=1";

		int numPerPage = 4;
		int pagePerBlock = 50;

		int totalRecord = musicVideoService.getTotalRecords();

		Map<String, Integer> map = getNumbersForPaging(totalRecord, page, numPerPage, pagePerBlock);
		Integer startRecord = map.get("startRecord");
		Integer endRecord = map.get("endRecord");

		List<MusicVideo> list = musicVideoService.getVideos(startRecord, endRecord);

		Integer prevPage = map.get("prevPage");
		Integer nextPage = map.get("nextPage");
		Integer firstPage = map.get("firstPage");
		Integer lastPage = map.get("lastPage");
		Integer totalPage = map.get("totalPage");

		model.addAttribute("list", list);
		model.addAttribute("prevPage", prevPage);
		model.addAttribute("nextPage", nextPage);
		model.addAttribute("firstPage", firstPage);
		model.addAttribute("lastPage", lastPage);
		model.addAttribute("totalPage", totalPage);

		return "index";
	}

	@PostMapping("/")
	@ResponseBody
	public void add(String content) {
		musicVideoService.add(content);	
	}
}
web.xml
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee
                      http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd"
  version="4.0">

<display-name>MyBatis Music</display-name>

<context-param>
	<param-name>contextConfigLocation</param-name>
	<param-value>/WEB-INF/applicationContext.xml</param-value>
</context-param>

<listener>
	<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
</listener>

<filter>
	<filter-name>encodingFilter</filter-name>
	<filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
	<init-param>
		<param-name>encoding</param-name>
		<param-value>UTF-8</param-value>
	</init-param>
	<init-param>
		<param-name>forceEncoding</param-name>
		<param-value>true</param-value>
	</init-param>
</filter>

<filter-mapping>
	<filter-name>encodingFilter</filter-name>
	<url-pattern>/*</url-pattern>
</filter-mapping>

<servlet>
	<servlet-name>mybatismusic</servlet-name>
	<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
	<load-on-startup>1</load-on-startup>
</servlet>

<servlet-mapping>
	<servlet-name>mybatismusic</servlet-name>
	<url-pattern>/</url-pattern>
</servlet-mapping>

</web-app>
mybatismusic-servlet.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:context="http://www.springframework.org/schema/context"
	xmlns:mvc="http://www.springframework.org/schema/mvc"
	xmlns:mybatis="http://mybatis.org/schema/mybatis-spring"
	xsi:schemaLocation="http://www.springframework.org/schema/beans 
	http://www.springframework.org/schema/beans/spring-beans.xsd
	http://www.springframework.org/schema/context
	http://www.springframework.org/schema/context/spring-context.xsd
	http://www.springframework.org/schema/mvc 
	http://www.springframework.org/schema/mvc/spring-mvc.xsd
	http://mybatis.org/schema/mybatis-spring 
	http://mybatis.org/schema/mybatis-spring.xsd">

<mvc:resources location="/resources/" mapping="/resources/**" />

<mvc:annotation-driven />

<context:component-scan base-package="net.java_school.mybatismusic" />

<mybatis:scan base-package="net.java_school.mybatis" />

<bean id="internalResourceViewResolver" class="org.springframework.web.servlet.view.InternalResourceViewResolver">
	<property name="viewClass" value="org.springframework.web.servlet.view.JstlView" />
	<property name="prefix" value="/WEB-INF/views/" />
	<property name="suffix" value=".jsp" />
</bean>

</beans>
applicationContext.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:p="http://www.springframework.org/schema/p"
	xsi:schemaLocation="http://www.springframework.org/schema/beans 
	http://www.springframework.org/schema/beans/spring-beans.xsd">

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
	<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
	<property name="url" value="jdbc:oracle:thin:@localhost:1521:XE" />
	<property name="username" value="scott" />
	<property name="password" value="tiger" />
	<property name="maxActive" value="100" />
	<property name="maxWait" value="1000" />
	<property name="poolPreparedStatements" value="true" />
	<property name="defaultAutoCommit" value="true" />
	<property name="validationQuery" value=" SELECT 1 FROM DUAL" />
</bean>

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
	<property name="dataSource" ref="dataSource" />
	<property name="configLocation" value="classpath:net/java_school/mybatis/Configuration.xml" />
</bean>

</beans>

src/main/webapp/WEB-INF/views

index.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
         pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<title>MyBatis Music</title>
<meta name="viewport" content="width=device-width, initial-scale=1" />
<meta name="Keywords" content="MyBatis Music App Test" />
<meta name="Description" content="This is test web app for MyBatis Music Video" />
<style>
html, body {
	margin: 0;
	padding: 0;
	background-color: #FFF;
	font-family: "Liberation Sans", Helvetica, sans-serif;
}
#videos .iframe {
	float: left;
}
#paging {
	width: 640px;
	float: left;
	font-size: 1em;
}
form {
	display: flex;
}
textarea {
	display: inline-block;
	width: 85%;
	resize: none;
}
#submit {
	display: inline-block;
	width: 10%;
	margin-bottom: -10px;
}
</style>
<script src="resources/js/jquery-3.6.1.min.js"></script>
<script>
$(document).ready(function() {
	$("#addForm").submit(function (event) {
		event.preventDefault();
		var $form = $(this);
		var content = $('#addForm-ta').val();
		content = $.trim(content);
		if (content.length === 0) {
			$('#addForm-ta').val('');
			return false;
		}
		var dataToBeSent = $form.serialize();
		var url = $form.attr("action");
		var posting = $.post(url, dataToBeSent);
		posting.done(function () {
			$('#addForm-ta').val('');
		});
	});    

	var originWidth = $('#videos > iframe').width();
	var originHeight = $('#videos > iframe').height();

	var width = $('#paging').width();
	var height = originHeight * width / originWidth;

	$('#videos > iframe').attr('width', width);
	$('#videos > iframe').attr('height', height);

	$('#videos > iframe').attr('allowFullScreen', '');

});
</script>
</head>
<body>
	<div id="videos">
		<c:forEach var="video" items="${list }" varStatus="status">
		${video.content }
		</c:forEach>
	</div>

	<div id="paging">
		<c:if test="${prevPage > 0}">
		<a href="?page=1" title="1">[First]</a>
		<a href="?page=${prevPage }" title="${prevPage }">[Prev]</a>
		</c:if>

		<c:forEach var="i" begin="${firstPage }" end="${lastPage }" varStatus="status">
		<c:choose>
			<c:when test="${param.page == i}">
			<strong>${i }</strong>
			</c:when>
			<c:otherwise>
			<a href="?page=${i }" title="${i }">${i }</a>
			</c:otherwise>
		</c:choose>
		</c:forEach>

		<c:if test="${nextPage > 0 }">
		<a href="?page=${nextPage }" title="${nextPage }">[Next]</a>
		<a href="?page=${totalPage }" title="${totalPage }">[Last]</a>
		</c:if>
		<form id="addForm" action="./">
			<textarea id="addForm-ta" name="content" cols="30" rows="1"></textarea>
			<input id="submit" type="submit" value="Send" /> 
		</form>
	</div>
</body>
</html>

https://jquery.com/download/
src/main/webapp/resources/js/jquery-3.6.1.min.js 

sudo service tomcat9 stop

sudo nano /etc/tomcat9/Catalina/localhost/mybatismusic.xml

<?xml version="1.0" encoding="UTF-8"?>
<Context
  docBase="/home/john/mybatismusic/src/main/webapp"
  reloadable="true">
</Context>

mvn compile war:inplace

sudo service tomcat9 restart

최종 소스
https://github.com/kimjonghoon/mybatismusic

관련 글