도도의 초록누리

[실험 결과]

1. [Spring-Framework] Spring Framework 5.4, MyBatis 3.5 연동 방법 - 실험 결과, 2020-10-02

   - https://yyman.tistory.com/1437

[이전 게시글]

1. [Spring-F.] 28. MyBatis 3.5, Spring Framework 5.4, Oracle 19g - 방법4(SqlMap Spring전용)

   - https://yyman.tistory.com/1441

<?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_3_1.xsd"

version="3.1">

<!-- The definition of the Root Spring Container shared by all Servlets and Filters -->

<context-param>

<param-name>contextConfigLocation</param-name>

<param-value>

/WEB-INF/spring/root-context.xml

</param-value>

</context-param>

<!-- Creates the Spring Container shared by all Servlets and Filters -->

<listener>

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

</listener>

<!-- Processes application requests -->

<servlet>

<servlet-name>appServlet</servlet-name>

<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>

<init-param>

<param-name>contextConfigLocation</param-name>

<param-value>

/WEB-INF/spring/appServlet/servlet-context.xml

</param-value>

</init-param>

<load-on-startup>1</load-on-startup>

</servlet>

<servlet-mapping>

<servlet-name>appServlet</servlet-name>

<url-pattern>/</url-pattern>

</servlet-mapping>

</web-app>

package com.example.spbatis.db;

import java.io.IOException;

import java.io.InputStream;

import java.sql.SQLException;

import javax.sql.DataSource;

import org.apache.ibatis.io.Resources;

import org.apache.ibatis.mapping.Environment;

import org.apache.ibatis.session.Configuration;

import org.apache.ibatis.session.SqlSessionFactory;

import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import org.apache.ibatis.transaction.TransactionFactory;

import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;

import org.mybatis.spring.SqlSessionFactoryBean;

import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import com.example.spbatis.model.CompUsers;

import oracle.jdbc.pool.OracleDataSource;

public class SqlMapSessionFactory {

private static SqlMapSessionFactory factory = new SqlMapSessionFactory();

private final static String driverName = "oracle.jdbc.driver.OracleDriver";

private final static String dbUrl = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";

private final static String userName = "사용자계정명";

private final static String userPassword = "비밀번호";

public static SqlSessionFactory ssf;

public static SqlMapSessionFactory getInstance() {

return factory;

}

private SqlMapSessionFactory () {    }

static {

/* 방법1

DataSource dataSource = getOracleDataSource();

TransactionFactory transactionFactory = new JdbcTransactionFactory();

Environment environment = new Environment("development", transactionFactory, dataSource);

Configuration configuration = new Configuration(environment);

configuration.addMapper(CompUsersMapper.class);

ssf = new SqlSessionFactoryBuilder().build(configuration);

*/

}

    public static SqlSessionFactory getSqlSessionFactory(){

/* 방법2: 공식 메뉴얼 참고 */

DataSource dataSource = getOracleDataSource();

SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean(); // Spring Framework만 지원

factoryBean.setDataSource(dataSource);

try {

//쿼리가 정의된 xml 파일들의 위치 지정

        factoryBean.setMapperLocations(

                new PathMatchingResourcePatternResolver().getResources("com/example/spbatis/mapper/*.xml")

        );

ssf = factoryBean.getObject();

// 클래스 등록

// ssf.getConfiguration().addMapper(CompUsersMapper.class);

} catch (Exception e) {

e.printStackTrace();

}

    // 방법 1, 방법 2 공통

        return ssf;

    }

    /*

* Description: 순정 오라클 데이터소스

*/

    private static DataSource getOracleDataSource(){

    OracleDataSource oracleDS = null;

    try {

            oracleDS = new OracleDataSource();

            oracleDS.setURL(dbUrl);

            oracleDS.setUser(userName);

            oracleDS.setPassword(userPassword);

        } catch (SQLException e) {

            e.printStackTrace();

        }

        return oracleDS;

    }

}

<?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.example.spbatis.mapper">

<select id="allCompUsers" resultType="com.example.spbatis.model.CompUsers">

select * from comp_users

</select>

<!-- 

<select id="allAddress" resultType="com.edu.db.AddressDto">

select * from addressbook

</select>

<select id="selectAddress" parameterType="Integer" resultType="com.edu.db.AddressDto">

select NUM, NAME, ADDRESS, BIRTHDATE

from addressbook

  where num=#{num}

</select>

<insert id="insertAddress" parameterType="com.edu.db.AddressDto">

insert into

addressbook(NAME, ADDRESS, BIRTHDATE)

values

(#{name},#{address},#{birthdate})

</insert>

<delete id="deleteAddress" parameterType="Integer">

DELETE FROM AddressBook

WHERE NUM = #{num}

</delete>

<update id="updateAddress" parameterType="com.edu.db.AddressDto" >

update addressbook

set birthdate = #{birthdate}, name = #{name}, address =#{address}

where num = #{num}

</update>

 -->

</mapper>

package com.example.spbatis.dao;

import java.util.List;

import org.apache.ibatis.session.SqlSession;

import org.apache.ibatis.session.SqlSessionFactory;

import com.example.spbatis.db.SqlMapSessionFactory;

import com.example.spbatis.model.CompUsers;

public class CompUsersDao {

    SqlSessionFactory factory = SqlMapSessionFactory.getSqlSessionFactory();

    public CompUsersDao() {

    }

    // SQL 세션 열기

    public List<CompUsers> selectAddress() {

    List<CompUsers> user = null;

try (SqlSession session = factory.openSession()) {

user = session.selectList("com.example.spbatis.mapper.allCompUsers");

session.close();

// System.out.println("계정명:" + user.getUsername());

}

return user;

    }

}

package com.example.spbatis.service;

import java.util.List;

import com.example.spbatis.dao.CompUsersDao;

import com.example.spbatis.model.CompUsers;

public class CompUsersService {

private CompUsersDao dao = null;

public CompUsersService() {

dao = new CompUsersDao();

}

public List<CompUsers> getAllCompUsers() {

return dao.selectAddress();

}

}

package com.example.spbatis;

import java.text.DateFormat;

import java.util.Date;

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.example.spbatis.service.CompUsersService;

/**

 * Handles requests for the application home page.

 */

@Controller

public class HomeController {

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

private CompUsersService compUserService;

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

public String home(Locale locale, Model model) {

logger.info("Welcome home5 - Java(XML Mappers)! The client locale is {}.", locale);

Date date = new Date();

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

String formattedDate = dateFormat.format(date);

compUserService = new CompUsersService();

model.addAttribute("serverTime", formattedDate );

model.addAttribute("list", compUserService.getAllCompUsers()) ;

return "home";

}

}

<%@ page language="java" contentType="text/html; charset=UTF-8"

    pageEncoding="UTF-8"%>

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

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

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

<%@ page session="false" %>

<html>

<head>

<title>Home</title>

</head>

<body>

<h1>

Hello world!  

</h1>

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

<h3>SqlMapSessionFactory 방식(Java - XML Mapper - Spring 전용 버전)</h3>

<c:forEach items="${list }" var="val" >

    ${val.username} / 

    ${val.password} /

    ${val.enabled }<br>

</c:forEach>

<c:if test="${empty list }">

    ${"데이터가 존재하지않아요."}

</c:if>

</body>

</html>

[실험 결과]

1. [Spring-Framework] Spring Framework 5.4, MyBatis 3.5 연동 방법 - 실험 결과, 2020-10-02

   - https://yyman.tistory.com/1437

[이전 게시글]

1. [Spring-F.] 27. MyBatis 3.5, Spring Framework 5.4, Oracle 19g - 방법3(SqlMap-XML-Class)     // 결과: 지원, 2020-10-02

    - https://yyman.tistory.com/1440

<?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_3_1.xsd"

version="3.1">

<!-- The definition of the Root Spring Container shared by all Servlets and Filters -->

<context-param>

<param-name>contextConfigLocation</param-name>

<param-value>

/WEB-INF/spring/root-context.xml

</param-value>

</context-param>

<!-- Creates the Spring Container shared by all Servlets and Filters -->

<listener>

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

</listener>

<!-- Processes application requests -->

<servlet>

<servlet-name>appServlet</servlet-name>

<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>

<init-param>

<param-name>contextConfigLocation</param-name>

<param-value>

/WEB-INF/spring/appServlet/servlet-context.xml

</param-value>

</init-param>

<load-on-startup>1</load-on-startup>

</servlet>

<servlet-mapping>

<servlet-name>appServlet</servlet-name>

<url-pattern>/</url-pattern>

</servlet-mapping>

</web-app>

package com.example.spbatis.db;

import java.io.IOException;

import java.io.InputStream;

import java.sql.SQLException;

import javax.sql.DataSource;

import org.apache.ibatis.io.Resources;

import org.apache.ibatis.mapping.Environment;

import org.apache.ibatis.session.Configuration;

import org.apache.ibatis.session.SqlSessionFactory;

import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import org.apache.ibatis.transaction.TransactionFactory;

import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;

import org.mybatis.spring.SqlSessionFactoryBean;

import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import com.example.spbatis.mapper.CompUsersMapper;

import com.example.spbatis.model.CompUsers;

import oracle.jdbc.pool.OracleDataSource;

public class SqlMapSessionFactory {

private static SqlMapSessionFactory factory = new SqlMapSessionFactory();

private final static String driverName = "oracle.jdbc.driver.OracleDriver";

private final static String dbUrl = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";

private final static String userName = "사용자계정";

private final static String userPassword = "비밀번호";

public static SqlSessionFactory ssf;

public static SqlMapSessionFactory getInstance() {

return factory;

}

private SqlMapSessionFactory () {    }

static {

/* 방법1

DataSource dataSource = getOracleDataSource();

TransactionFactory transactionFactory = new JdbcTransactionFactory();

Environment environment = new Environment("development", transactionFactory, dataSource);

Configuration configuration = new Configuration(environment);

configuration.addMapper(CompUsersMapper.class);

ssf = new SqlSessionFactoryBuilder().build(configuration);

*/

}

    public static SqlSessionFactory getSqlSessionFactory(){

/* 방법2: 공식 메뉴얼 참고 */

DataSource dataSource = getOracleDataSource();

SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean(); // Spring Framework만 지원

factoryBean.setDataSource(dataSource);

try {

//쿼리가 정의된 xml 파일들의 위치 지정

/*

        factoryBean.setMapperLocations(

                new PathMatchingResourcePatternResolver().getResources("com/example/spbatis/mapper/*.xml")

        );

        */

ssf = factoryBean.getObject();

// 클래스 등록

ssf.getConfiguration().addMapper(CompUsersMapper.class);

} catch (Exception e) {

e.printStackTrace();

}

    // 방법 1, 방법 2 공통

        return ssf;

    }

    /*

* Description: 순정 오라클 데이터소스

*/

    private static DataSource getOracleDataSource(){

    OracleDataSource oracleDS = null;

    try {

            oracleDS = new OracleDataSource();

            oracleDS.setURL(dbUrl);

            oracleDS.setUser(userName);

            oracleDS.setPassword(userPassword);

        } catch (SQLException e) {

            e.printStackTrace();

        }

        return oracleDS;

    }

}

package com.example.spbatis.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Mapper;

import org.apache.ibatis.annotations.Select;

import com.example.spbatis.model.CompUsers;

@Mapper

public interface CompUsersMapper {

@Select("SELECT * FROM comp_users")

List<CompUsers> selectAll();

}

package com.example.spbatis.dao;

import java.util.List;

import org.apache.ibatis.session.SqlSession;

import org.apache.ibatis.session.SqlSessionFactory;

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

import com.example.spbatis.db.SqlMapSessionFactory;

import com.example.spbatis.mapper.CompUsersMapper;

import com.example.spbatis.model.CompUsers;

public class CompUsersDao {

    SqlSessionFactory factory = SqlMapSessionFactory.getSqlSessionFactory();

    public CompUsersDao() {

    }

    // SQL 세션 열기

    public List<CompUsers> selectAddress() {

    List<CompUsers> user = null;

try (SqlSession session = factory.openSession()) {

CompUsersMapper mapper = session.getMapper(CompUsersMapper.class);

user = mapper.selectAll();

// System.out.println("계정명:" + user.getUsername());

}

return user;

    }

}

package com.example.spbatis.service;

import java.util.List;

import com.example.spbatis.dao.CompUsersDao;

import com.example.spbatis.model.CompUsers;

public class CompUsersService {

private CompUsersDao dao = null;

public CompUsersService() {

dao = new CompUsersDao();

}

public List<CompUsers> getAllCompUsers() {

return dao.selectAddress();

}

}

package com.example.spbatis;

import java.text.DateFormat;

import java.util.Date;

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.example.spbatis.service.CompUsersService;

/**

 * Handles requests for the application home page.

 */

@Controller

public class HomeController {

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

private CompUsersService compUserService;

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

public String home(Locale locale, Model model) {

logger.info("Welcome home4(Class Mappers) - Java! The client locale is {}.", locale);

Date date = new Date();

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

String formattedDate = dateFormat.format(date);

compUserService = new CompUsersService();

model.addAttribute("serverTime", formattedDate );

model.addAttribute("list", compUserService.getAllCompUsers()) ;

return "home";

}

}

<%@ page language="java" contentType="text/html; charset=UTF-8"

    pageEncoding="UTF-8"%>

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

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

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

<%@ page session="false" %>

<html>

<head>

<title>Home</title>

</head>

<body>

<h1>

Hello world!  

</h1>

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

<h3>SqlMapSessionFactory 방식(Java - Mapper - Spring 전용 버전)</h3>

<c:forEach items="${list }" var="val" >

    ${val.username} / 

    ${val.password} /

    ${val.enabled }<br>

</c:forEach>

<c:if test="${empty list }">

    ${"데이터가 존재하지않아요."}

</c:if>

</body>

</html>

1. [Spring-F.] 29. MyBatis 3.5, Spring Framework 5.4, Oracle 19g - 방법5(SqlMap Spring전용)  // 결과: 지원, 2020-10-02

    - https://yyman.tistory.com/1442

[실험 결과]

1. [Spring-Framework] Spring Framework 5.4, MyBatis 3.5 연동 방법 - 실험 결과, 2020-10-02

   - https://yyman.tistory.com/1437

[이전 게시글]

1. [Spring-F.] 26. MyBatis 3.5, Spring Framework 5.4, Oracle 19g - 방법2(SqlMap-XML-XML 맵핑)     // 결과: 지원, 2020-10-02

    - https://yyman.tistory.com/1439

<?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_3_1.xsd"

version="3.1">

<!-- The definition of the Root Spring Container shared by all Servlets and Filters -->

<context-param>

<param-name>contextConfigLocation</param-name>

<param-value>

/WEB-INF/spring/root-context.xml

</param-value>

</context-param>

<!-- Creates the Spring Container shared by all Servlets and Filters -->

<listener>

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

</listener>

<!-- Processes application requests -->

<servlet>

<servlet-name>appServlet</servlet-name>

<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>

<init-param>

<param-name>contextConfigLocation</param-name>

<param-value>

/WEB-INF/spring/appServlet/servlet-context.xml

</param-value>

</init-param>

<load-on-startup>1</load-on-startup>

</servlet>

<servlet-mapping>

<servlet-name>appServlet</servlet-name>

<url-pattern>/</url-pattern>

</servlet-mapping>

</web-app>

<%@ page language="java" contentType="text/html; charset=UTF-8"

    pageEncoding="UTF-8"%>

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

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

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

<%@ page session="false" %>

<html>

<head>

<title>Home</title>

</head>

<body>

<h1>

Hello world!  

</h1>

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

<h3>SqlMapSessionFactory 방식(XML - 환경설정 - 클래스-Mappers)</h3>

<c:forEach items="${list }" var="val" >

    ${val.username} / 

    ${val.password} /

    ${val.enabled }<br>

</c:forEach>

<c:if test="${empty list }">

    ${"데이터가 존재하지않아요."}

</c:if>

<h3>XML - web.xml 설정방식</h3>

<c:forEach items="${list2 }" var="val" >

    ${val.username} / 

    ${val.password} /

    ${val.enabled }<br>

</c:forEach>

<c:if test="${empty list2 }">

    ${"데이터가 존재하지않아요."}

</c:if>

</body>

</html>

package com.example.spbatis.db;

import java.io.IOException;

import java.io.InputStream;

import org.apache.ibatis.io.Resources;

import org.apache.ibatis.session.SqlSessionFactory;

import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import com.example.spbatis.mapper.CompUsersMapper;

public class SqlMapSessionFactory {

public SqlSessionFactory ssf;

public SqlMapSessionFactory () {

        String resource = "com/example/spbatis/mapper/mybatis-config.xml";

        InputStream is = null;

        try {

            is = Resources.getResourceAsStream(resource);

        } catch (IOException e) {

            e.printStackTrace();

        }

        ssf = new SqlSessionFactoryBuilder().build(is);

        ssf.getConfiguration().addMapper(CompUsersMapper.class);

    }

    public SqlSessionFactory getSqlSessionFactory(){

        return ssf;

    }

}

<?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>

<environments default="development">

    <environment id="development">

      <transactionManager type="JDBC"/>

      <!-- 커넥션 풀(MyBatis) -->

      <dataSource type="POOLED">

        <property name="driver" value="oracle.jdbc.driver.OracleDriver" />

        <property name="url" value="jdbc:oracle:thin:@127.0.0.1:1521:orcl"/>

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

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

        <property name="poolMaximumActiveConnections" value="20"/>

        <property name="poolMaximumIdleConnections" value="20"/>

        <property name="poolMaximumCheckoutTime" value="20000"/>

        <property name="poolPingEnabled" value="true"/>

        <property name="poolPingQuery" value="select 1"/>

        <property name="poolPingConnectionsNotUsedFor" value="10000"/>

        <property name="poolTimeToWait" value="15000"/>

      </dataSource>

    </environment>

</environments>

</configuration>

package com.example.spbatis.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Mapper;

import org.apache.ibatis.annotations.Select;

import com.example.spbatis.model.CompUsers;

@Mapper

public interface CompUsersMapper {

@Select("SELECT * FROM comp_users")

List<CompUsers> selectAll();

}

package com.example.spbatis.dao;

import java.util.List;

import org.apache.ibatis.session.SqlSession;

import org.apache.ibatis.session.SqlSessionFactory;

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

import com.example.spbatis.db.SqlMapSessionFactory;

import com.example.spbatis.mapper.CompUsersMapper;

import com.example.spbatis.model.CompUsers;

public class CompUsersDao {

    private SqlMapSessionFactory ssfc;

    private SqlSessionFactory factory;

    public CompUsersDao() {

        ssfc = new SqlMapSessionFactory();

        factory = ssfc.getSqlSessionFactory();

    }

    // SQL 세션 열기

    public List<CompUsers> selectAddress() {

    List<CompUsers> user = null;

try (SqlSession session = factory.openSession()) {

CompUsersMapper mapper = session.getMapper(CompUsersMapper.class);

user = mapper.selectAll();

// System.out.println("계정명:" + user.getUsername());

}

return user;

    }

}

package com.example.spbatis.service;

import java.util.List;

import com.example.spbatis.dao.CompUsersDao;

import com.example.spbatis.model.CompUsers;

public class CompUsersService {

private CompUsersDao dao = null;

public CompUsersService() {

dao = new CompUsersDao();

}

public List<CompUsers> getAllCompUsers() {

return dao.selectAddress();

}

}

package com.example.spbatis;

import java.text.DateFormat;

import java.util.Date;

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.example.spbatis.service.CompUsersService;

/**

 * Handles requests for the application home page.

 */

@Controller

public class HomeController {

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

private CompUsersService compUserService;

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

public String home(Locale locale, Model model) {

logger.info("Welcome home3! The client locale is {}.", locale);

Date date = new Date();

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

String formattedDate = dateFormat.format(date);

compUserService = new CompUsersService();

model.addAttribute("serverTime", formattedDate );

model.addAttribute("list", compUserService.getAllCompUsers()) ;

return "home";

}

}

1. [Spring-F.] 28. MyBatis 3.5, Spring Framework 5.4, Oracle 19g - 방법4(SqlMap Spring전용)   // 결과: 지원, 2020-10-02

- https://yyman.tistory.com/1441

[실험 결과]

1. [Spring-Framework] Spring Framework 5.4, MyBatis 3.5 연동 방법 - 실험 결과, 2020-10-02

- https://yyman.tistory.com/1437

[이전 게시글]

1. [Spring-F.] 25. MyBatis 3.5, Spring Framework 5.4, Oracle 19g - 방법1(web.xml-Beans), 2020-10-02

- https://yyman.tistory.com/1438

<?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_3_1.xsd"

version="3.1">

<!-- The definition of the Root Spring Container shared by all Servlets and Filters -->

<context-param>

<param-name>contextConfigLocation</param-name>

<param-value>

/WEB-INF/spring/root-context.xml

</param-value>

</context-param>

<!-- Creates the Spring Container shared by all Servlets and Filters -->

<listener>

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

</listener>

<!-- Processes application requests -->

<servlet>

<servlet-name>appServlet</servlet-name>

<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>

<init-param>

<param-name>contextConfigLocation</param-name>

<param-value>

/WEB-INF/spring/appServlet/servlet-context.xml

</param-value>

</init-param>

<load-on-startup>1</load-on-startup>

</servlet>

<servlet-mapping>

<servlet-name>appServlet</servlet-name>

<url-pattern>/</url-pattern>

</servlet-mapping>

</web-app>

<%@ page language="java" contentType="text/html; charset=UTF-8"

    pageEncoding="UTF-8"%>

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

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

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

<%@ page session="false" %>

<html>

<head>

<title>Home</title>

</head>

<body>

<h1>

Hello world!  

</h1>

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

<h3>SqlMapSessionFactory 방식(XML - 환경설정 - XML-Mappers)</h3>

<c:forEach items="${list }" var="val" >

    ${val.username} / 

    ${val.password} /

    ${val.enabled }<br>

</c:forEach>

<c:if test="${empty list }">

    ${"데이터가 존재하지않아요."}

</c:if>

<h3>XML - web.xml 설정방식</h3>

<c:forEach items="${list2 }" var="val" >

    ${val.username} / 

    ${val.password} /

    ${val.enabled }<br>

</c:forEach>

<c:if test="${empty list2 }">

    ${"데이터가 존재하지않아요."}

</c:if>

</body>

</html>

Spring Boot가 아닌 이상, 참고로 해당 위치에 xml파일을 넣어줘야 한다.
예를 들면, the.getClass().getResources() 이런 함수를 활용해서 /src/main/resources 폴더를 생성하고 작업을 시도하려고 한다면, 

실패할 것이다. 운이 좋아서 경로를 찾아서 빌드했을 때 동작이 되었다고 해도, 톰캣 서버에 올려버리면 경로를 못 찾아버리는 현상이 발생한다.

충분히 시간을 가지고, 태스트를 거친 후에 작성한 글이다.

<?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>

<environments default="development">

    <environment id="development">

      <transactionManager type="JDBC"/>

      <!-- 커넥션 풀(MyBatis) -->

      <dataSource type="POOLED">

        <property name="driver" value="oracle.jdbc.driver.OracleDriver" />

        <property name="url" value="jdbc:oracle:thin:@127.0.0.1:1521:orcl"/>

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

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

        <property name="poolMaximumActiveConnections" value="20"/>

        <property name="poolMaximumIdleConnections" value="20"/>

        <property name="poolMaximumCheckoutTime" value="20000"/>

        <property name="poolPingEnabled" value="true"/>

        <property name="poolPingQuery" value="select 1"/>

        <property name="poolPingConnectionsNotUsedFor" value="10000"/>

        <property name="poolTimeToWait" value="15000"/>

      </dataSource>

    </environment>

</environments>

<mappers>

  <mapper resource="com/example/spbatis/mapper/CompUsersMapper.xml"/>

</mappers>

</configuration>

<?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.example.spbatis.mapper">

<select id="allCompUsers" resultType="com.example.spbatis.model.CompUsers">

select * from comp_users

</select>

<!-- 

<select id="allAddress" resultType="com.edu.db.AddressDto">

select * from addressbook

</select>

<select id="selectAddress" parameterType="Integer" resultType="com.edu.db.AddressDto">

select NUM, NAME, ADDRESS, BIRTHDATE

from addressbook

  where num=#{num}

</select>

<insert id="insertAddress" parameterType="com.edu.db.AddressDto">

insert into

addressbook(NAME, ADDRESS, BIRTHDATE)

values

(#{name},#{address},#{birthdate})

</insert>

<delete id="deleteAddress" parameterType="Integer">

DELETE FROM AddressBook

WHERE NUM = #{num}

</delete>

<update id="updateAddress" parameterType="com.edu.db.AddressDto" >

update addressbook

set birthdate = #{birthdate}, name = #{name}, address =#{address}

where num = #{num}

</update>

 -->

</mapper>

package com.example.spbatis.db;

import java.io.IOException;

import java.io.InputStream;

import org.apache.ibatis.io.Resources;

import org.apache.ibatis.session.SqlSessionFactory;

import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class SqlMapSessionFactory {

public SqlSessionFactory ssf;

public SqlMapSessionFactory () {

        String resource = "com/example/spbatis/mapper/mybatis-config.xml";

        InputStream is = null;

        try {

            is = Resources.getResourceAsStream(resource);

        } catch (IOException e) {

            e.printStackTrace();

        }

        ssf = new SqlSessionFactoryBuilder().build(is);

    }

    public SqlSessionFactory getSqlSessionFactory(){

        return ssf;

    }

}

package com.example.spbatis.dao;

import java.util.List;

import org.apache.ibatis.session.SqlSession;

import org.apache.ibatis.session.SqlSessionFactory;

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

import com.example.spbatis.db.SqlMapSessionFactory;

import com.example.spbatis.model.CompUsers;

public class CompUsersDao {

    private SqlMapSessionFactory ssfc;

    private SqlSessionFactory factory;

    public CompUsersDao() {

        ssfc = new SqlMapSessionFactory();

        factory = ssfc.getSqlSessionFactory();

    }

    // SQL 세션 열기

    public List<CompUsers> selectAddress() {

        SqlSession session = factory.openSession();

        List<CompUsers> compDTO = session.selectList("com.example.spbatis.mapper.allCompUsers");

        session.close();

        return compDTO;

    }

}

package com.example.spbatis.service;

import java.util.List;

import com.example.spbatis.dao.CompUsersDao;

import com.example.spbatis.model.CompUsers;

public class CompUsersService {

private CompUsersDao dao = null;

public CompUsersService() {

dao = new CompUsersDao();

}

public List<CompUsers> getAllCompUsers() {

return dao.selectAddress();

}

}

package com.example.spbatis;

import java.text.DateFormat;

import java.util.Date;

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.example.spbatis.service.CompUsersService;

/**

 * Handles requests for the application home page.

 */

@Controller

public class HomeController {

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

private CompUsersService compUserService;

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

public String home(Locale locale, Model model) {

logger.info("Welcome home2! The client locale is {}.", locale);

Date date = new Date();

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

String formattedDate = dateFormat.format(date);

compUserService = new CompUsersService();

model.addAttribute("serverTime", formattedDate );

model.addAttribute("list", compUserService.getAllCompUsers()) ;

return "home";

}

}

3. [Spring-F.] 27. MyBatis 3.5, Spring Framework 5.4, Oracle 19g - 방법3(SqlMap-XML-Class)

- https://yyman.tistory.com/1440

1. [Spring-Framework] Spring Framework 5.4, MyBatis 3.5 연동 방법 - 실험 결과, 2020-10-02

https://yyman.tistory.com/1437

동작되지 않은 소스이지만, 분석해보고 싶은 사람들을 위해서 소스코드를 공개하도록 하겠다.

<?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_3_1.xsd"

version="3.1">

<!-- The definition of the Root Spring Container shared by all Servlets and Filters -->

<context-param>

<param-name>contextConfigLocation</param-name>

<param-value>

/WEB-INF/spring/root-context.xml

/WEB-INF/spring/mybatis-context.xml

</param-value>

</context-param>

<!-- Creates the Spring Container shared by all Servlets and Filters -->

<listener>

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

</listener>

<!-- Processes application requests -->

<servlet>

<servlet-name>appServlet</servlet-name>

<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>

<init-param>

<param-name>contextConfigLocation</param-name>

<param-value>

/WEB-INF/spring/appServlet/servlet-context.xml

</param-value>

</init-param>

<load-on-startup>1</load-on-startup>

</servlet>

<servlet-mapping>

<servlet-name>appServlet</servlet-name>

<url-pattern>/</url-pattern>

</servlet-mapping>

</web-app>

<beans xmlns="http://www.springframework.org/schema/beans"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xmlns:aop="http://www.springframework.org/schema/aop"

xmlns:tx="http://www.springframework.org/schema/tx"

xmlns:context="http://www.springframework.org/schema/context"

xmlns:p="http://www.springframework.org/schema/p"

xmlns:jdbc="http://www.springframework.org/schema/jdbc"

xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd

http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd

http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd

http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">

<!-- 현재는 빈(Bean) 등록 방식 미지원함(2020-10-02 / Spring Framework 5 이상) -->

<!-- org.apache.ibatis.datasource.pooled.PooledDataSource -->

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

<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>

<property name="url" value="jdbc:oracle:thin:@127.0.0.1:1521:orcl"/>

<property name="username" value="사용자계정"/>

<property name="password" value="비밀번호"/>

</bean>

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

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

<property name="configLocation" value="/WEB-INF/mybatis-config.xml"/>

<property name="mapperLocations" value="classpath:com/example/spbatis/mapper/*.xml" />

    </bean>

<!-- 

<property name="mapperLocations" value="classpath:com/think/mapper/**/*.xml" />

        <property name="mapperLocations" value="/WEB-INF/mybatis/mapper/*.xml" />

-->

<bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">

<constructor-arg index="0" ref="sqlSessionFactory"></constructor-arg>

</bean>

</beans> 

<?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="cacheEnabled" value="false" />

<setting name="useGeneratedKeys" value="true" />

<setting name="defaultExecutorType" value="REUSE" />

</settings>

<!-- 별명 -->

    <typeAliases>

         <typeAlias alias="hashMap" type="java.util.HashMap" />

         <typeAlias alias="map" type="java.util.Map" />

    </typeAliases>

<!--

<mappers>

<mapper resource="mybatis/mapper/tempMapper.xml" />

</mappers>

<mappers>

<mapper resource="mybatis/mapper/bbsMapper.xml" />

</mappers>

-->

</configuration>

경로: /src/main/webapp/WEB-INF/views/home.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"

    pageEncoding="UTF-8"%>

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

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

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

<%@ page session="false" %>

<html>

<head>

<title>Home</title>

</head>

<body>

<h1>

Hello world!  

</h1>

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

<h3>Beans 방식(XML)</h3>

<c:forEach items="${list }" var="val" >

    ${val.username} / 

    ${val.password} /

    ${val.enabled }<br>

</c:forEach>

<c:if test="${empty list }">

    ${"데이터가 존재하지않아요."}

</c:if>

<h3>XML - web.xml 설정방식</h3>

<c:forEach items="${list2 }" var="val" >

    ${val.username} / 

    ${val.password} /

    ${val.enabled }<br>

</c:forEach>

<c:if test="${empty list2 }">

    ${"데이터가 존재하지않아요."}

</c:if>

</body>

</html>

패키지 경로: com.example.spbatis;

package com.example.spbatis;

import java.text.DateFormat;

import java.util.Date;

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.example.spbatis.service.CompUsersService;

/**

 * Handles requests for the application home page.

 */

@Controller

public class HomeController {

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

private CompUsersService compUserService;

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

public String home(Locale locale, Model model) {

logger.info("Welcome home! The client locale is {}.", locale);

Date date = new Date();

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

String formattedDate = dateFormat.format(date);

compUserService = new CompUsersService();

model.addAttribute("serverTime", formattedDate );

model.addAttribute("list2", compUserService.getAllCompUsers()) ;

return "home";

}

}

패키지 경로: com.example.spbatis.service

package com.example.spbatis.service;

import java.util.List;

import com.example.spbatis.dao.CompUsersDao;

import com.example.spbatis.model.CompUsers;

public class CompUsersService {

CompUsersDao dao = new CompUsersDao();

public List<CompUsers> getAllCompUsers() {

return dao.selectAddress();

}

}

패키지 경로: com.example.spbatis.dao

package com.example.spbatis.dao;

import java.util.List;

import org.apache.ibatis.session.SqlSession;

import org.apache.ibatis.session.SqlSessionFactory;

import org.mybatis.spring.SqlSessionTemplate;

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

import org.springframework.stereotype.Component;

import com.example.spbatis.model.CompUsers;

@Component

public class CompUsersDao {

/* 동작 안함1

    @Autowired

    private SqlSessionFactory sqlSessionFactory;

*/

/* 동작 안함2 */

@Autowired

private SqlSession sqlSession;

/* */

    public CompUsersDao() {

    }

    // SQL 세션 열기

    public List<CompUsers> selectAddress() {

    /* 동작 안함2 */

    return sqlSession.selectList("com.example.spbatis.mapper.allCompUsers");

    /* 동작 안함1

    SqlSession session = sqlSessionFactory.openSession();

    try 

    {

    return session.selectList("com.example.spbatis.mapper.allCompUsers");

    }

    finally

    {

        session.close();

    }

    */

    // return null;

    }

}

태스트 실험을 시도한 코드를 주석으로 구분하여 표기해두었다.

<?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.example.spbatis.mapper">

<select id="allCompUsers" resultType="com.example.spbatis.model.CompUsers">

select * from comp_users

</select>

<!-- 

<select id="allAddress" resultType="com.edu.db.AddressDto">

select * from addressbook

</select>

<select id="selectAddress" parameterType="Integer" resultType="com.edu.db.AddressDto">

select NUM, NAME, ADDRESS, BIRTHDATE

from addressbook

  where num=#{num}

</select>

<insert id="insertAddress" parameterType="com.edu.db.AddressDto">

insert into

addressbook(NAME, ADDRESS, BIRTHDATE)

values

(#{name},#{address},#{birthdate})

</insert>

<delete id="deleteAddress" parameterType="Integer">

DELETE FROM AddressBook

WHERE NUM = #{num}

</delete>

<update id="updateAddress" parameterType="com.edu.db.AddressDto" >

update addressbook

set birthdate = #{birthdate}, name = #{name}, address =#{address}

where num = #{num}

</update>

 -->

</mapper>

2. [Spring-F.] 26. MyBatis 3.5, Spring Framework 5.4, Oracle 19g - 방법2(SqlMap-XML-XML 맵핑)     // 결과: 지원, 2020-10-02

   - https://yyman.tistory.com/1439

1. [JSP] 18. JSP/Servlet MVC2 - 페이징네이션과 검색 그리고 오라클 프로젝트 (1)

https://yyman.tistory.com/1428

실제경로: /target/generated-sources/annotations/com/smile/web/model

패키지명: com.smile.web.logic

파일명: Paging.java

package com.smile.web.logic;

public class Paging {

    private long pageSize; // 게시 글 수

    private long firstPageNo; // 첫 번째 페이지 번호

    private long prevPageNo; // 이전 페이지 번호

    private long startPageNo; // 시작 페이지 (페이징 네비 기준)

    private long pageNo; // 페이지 번호

    private long endPageNo; // 끝 페이지 (페이징 네비 기준)

    private long nextPageNo; // 다음 페이지 번호

    private long finalPageNo; // 마지막 페이지 번호

    private long totalCount; // 게시 글 전체 수

    private long dbStartNum; // db 조회 (시작번호)

    private long dbEndNum; // db 조회 (종료번호)

    /**

     * @return the pageSize

     */

    public long getPageSize() {

        return pageSize;

    }

    /**

     * @param pageSize the pageSize to set

     */

    public void setPageSize(long pageSize) {

        this.pageSize = pageSize;

    }

    /**

     * @return the firstPageNo

     */

    public long getFirstPageNo() {

        return firstPageNo;

    }

    /**

     * @param firstPageNo the firstPageNo to set

     */

    public void setFirstPageNo(long firstPageNo) {

        this.firstPageNo = firstPageNo;

    }

    /**

     * @return the prevPageNo

     */

    public long getPrevPageNo() {

        return prevPageNo;

    }

    /**

     * @param prevPageNo the prevPageNo to set

     */

    public void setPrevPageNo(long prevPageNo) {

        this.prevPageNo = prevPageNo;

    }

    /**

     * @return the startPageNo

     */

    public long getStartPageNo() {

        return startPageNo;

    }

    /**

     * @param startPageNo the startPageNo to set

     */

    public void setStartPageNo(long startPageNo) {

        this.startPageNo = startPageNo;

    }

    /**

     * @return the pageNo

     */

    public long getPageNo() {

        return pageNo;

    }

    /**

     * @param pageNo the pageNo to set

     */

    public void setPageNo(long pageNo) {

        this.pageNo = pageNo;

    }

    /**

     * @return the endPageNo

     */

    public long getEndPageNo() {

        return endPageNo;

    }

    /**

     * @param endPageNo the endPageNo to set

     */

    public void setEndPageNo(long endPageNo) {

        this.endPageNo = endPageNo;

    }

    /**

     * @return the nextPageNo

     */

    public long getNextPageNo() {

        return nextPageNo;

    }

    /**

     * @param nextPageNo the nextPageNo to set

     */

    public void setNextPageNo(long nextPageNo) {

        this.nextPageNo = nextPageNo;

    }

    /**

     * @return the finalPageNo

     */

    public long getFinalPageNo() {

        return finalPageNo;

    }

    /**

     * @param finalPageNo the finalPageNo to set

     */

    public void setFinalPageNo(long finalPageNo) {

        this.finalPageNo = finalPageNo;

    }

    /**

     * @return the totalCount

     */

    public long getTotalCount() {

        return totalCount;

    }

    /**

     * @param totalCount the totalCount to set

     */

    public void setTotalCount(long totalCount) {

        this.totalCount = totalCount;

        this.makePaging();

        this.makeDbPaging();

    }

    /**

     * 페이징 생성 (setTotalCount에서 호출함)

     */

    private void makePaging() {

        if (this.totalCount == 0) 

        return; 

        // 게시 글 전체 수가 없는 경우

        if (this.pageNo == 0) 

        this.setPageNo(1); // 기본 값 설정

        if (this.pageSize == 0) 

        this.setPageSize(10); // 기본 값 설정

        long finalPage = (totalCount + (pageSize - 1)) / pageSize; // 마지막 페이지

        if (this.pageNo > finalPage) 

        this.setPageNo(finalPage); // 기본 값 설정

        if (this.pageNo < 0 || this.pageNo > finalPage) 

        this.pageNo = 1; // 현재 페이지 유효성 체크

        boolean isNowFirst = pageNo == 1 ? true : false; // 시작 페이지 (전체)

        boolean isNowFinal = pageNo == finalPage ? true : false; // 마지막 페이지 (전체)

        long startPage = ((pageNo - 1) / 10) * 10 + 1; // 시작 페이지 (페이징 네비 기준)

        long endPage = startPage + 10 - 1; // 끝 페이지 (페이징 네비 기준)

        if (endPage > finalPage) { // [마지막 페이지 (페이징 네비 기준) > 마지막 페이지] 보다 큰 경우

            endPage = finalPage;

        }

        this.setFirstPageNo(1); // 첫 번째 페이지 번호

        if (isNowFirst) {

            this.setPrevPageNo(1);    // 이전 페이지 번호

        } else {

            this.setPrevPageNo(((pageNo - 1) < 1 ? 1 : (pageNo - 1))); // 이전 페이지 번호

        }

        this.setStartPageNo(startPage); // 시작 페이지 (페이징 네비 기준)

        this.setEndPageNo(endPage); // 끝 페이지 (페이징 네비 기준)

        if (isNowFinal) {

            this.setNextPageNo(finalPage); // 다음 페이지 번호

        } else {

            this.setNextPageNo(((pageNo + 1) > finalPage ? finalPage : (pageNo + 1))); // 다음 페이지 번호

        }

        this.setFinalPageNo(finalPage); // 마지막 페이지 번호

    }

    private void makeDbPaging() {

    long current = this.getPageNo();

    long weight = this.getPageSize();

    long endNum = 0;

    this.setDbEndNum( current * weight );

    endNum = this.getDbEndNum();

    this.setDbStartNum( (endNum - weight) + 1 );

    }

    /**

     * @return the dbStartNum

     */

public long getDbStartNum() {

return dbStartNum;

}

    /**

     * @param dbStartNum the dbStartNum to set

     */

public void setDbStartNum(long dbStartNum) {

this.dbStartNum = dbStartNum;

}

    /**

     * @return dbEndNum

     */

public long getDbEndNum() {

return dbEndNum;

}

    /**

     * @param dbEndNum the dbEndNum to set

     */

public void setDbEndNum(long dbEndNum) {

this.dbEndNum = dbEndNum;

}

}

실제경로: /target/generated-sources/annotations/com/smile/web/model

패키지명: com.smile.web.model

파일명: Board.java

package com.smile.web.model;

import java.sql.Timestamp;

public class Board {

private long id;

private String name;

private String subject;

private String memo;

private long count;

private Timestamp regidate;

public long getId() {

return id;

}

public void setId(long id) {

this.id = id;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public String getSubject() {

return subject;

}

public void setSubject(String subject) {

this.subject = subject;

}

public String getMemo() {

return memo;

}

public void setMemo(String memo) {

this.memo = memo;

}

public long getCount() {

return count;

}

public void setCount(long count) {

this.count = count;

}

public Timestamp getRegidate() {

return regidate;

}

public void setRegidate(Timestamp regidate) {

this.regidate = regidate;

}

}

실제경로: /target/generated-sources/annotations/com/smile/web/service

패키지명: com.smile.web.service

파일명: BoardService.java

package com.smile.web.service;

import java.util.List;

import com.smile.web.model.Board;

public class BoardService {

private static BoardService service = null;

private static BoardServiceImpl dao = null;

private BoardService() {}

public static BoardService getInstance() {

        if(service == null){

        service = new BoardService();

    dao = BoardServiceImpl.getInstatnce();

        }

        return service;

}

public List<Board> getBoardList(long startNum, long endNum){

return dao.getBoardList(startNum, endNum);

}

public long getTotalCount() {

return dao.getTotalCount();

}

public List<Board> getBoardKeywordList(String keyword, long startNum, long endNum){

return dao.getBoardKeywordList(keyword, startNum, endNum);

}

public long getTotalKeywordCount(String keyword) {

return dao.getTotalKeywordCount(keyword);

}

}

실제경로: /target/generated-sources/annotations/com/smile/web/service

패키지명: com.smile.web.service

파일명: BoardServiceImpl.java

package com.smile.web.service;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

import com.smile.web.db.DBFactory;

import com.smile.web.model.Board;

public class BoardServiceImpl {

private static BoardServiceImpl boardDAO;

private static DBFactory dbPool;

private BoardServiceImpl() {}

public static BoardServiceImpl getInstatnce() {

if ( boardDAO == null ) {

boardDAO = new BoardServiceImpl();

dbPool = new DBFactory();

}

return boardDAO;

}

public List<Board> getBoardList(long startNum, long endNum){

Connection conn = null;

    PreparedStatement pstmt = null;

    ResultSet rs = null;

    Board node = null;

    List<Board> boardList = new ArrayList<Board>();

    String sql = "select * from (select /*+INDEX_DESC(tbl_board pk_board) */ rownum rn, A.*" + 

    " from board A order by id desc) " + 

    "where rn >= ? and rn <= ?";

    //System.out.println(sql);

    //System.out.println(startNum + "/" + endNum);

    try {

    conn = dbPool.getConnection();

    pstmt = conn.prepareStatement(sql);

    pstmt.setLong(1, startNum);

    pstmt.setLong(2, endNum);

    rs = pstmt.executeQuery();

    while ( rs.next() ) {

    // 데이터가 존재할 때, 노드 생성

    node = new Board();

    node.setId(rs.getLong(2));

    node.setSubject(rs.getNString(3));

    node.setMemo(rs.getNString(4));

    node.setName(rs.getNString(5));

    boardList.add(node);

    System.out.println("rs:" + rs.getLong(1));

    }

    }catch(Exception ex) {

    System.out.println("오류 발생: " + ex);

    }

    finally {

    dbPool.close(conn, pstmt, rs);

    }

return boardList;

}

public List<Board> getBoardKeywordList(String keyword, long startNum, long endNum){

Connection conn = null;

    PreparedStatement pstmt = null;

    ResultSet rs = null;

    Board node = null;

    String allKeyword = "%" + keyword + "%";

    List<Board> boardList = new ArrayList<Board>();

    String sql = "select * " + 

    "from (select /*+INDEX_DESC(tbl_board pk_board) */ rownum rn, A.* " + 

    "from board A where subject like ? order by id desc) " + 

    "where rn >= ? and rn <= ?";

    //System.out.println(sql);

    try {

    conn = dbPool.getConnection();

    pstmt = conn.prepareStatement(sql);

    pstmt.setNString(1, allKeyword);

    pstmt.setLong(2, startNum);

    pstmt.setLong(3, endNum);

    rs = pstmt.executeQuery();

    while ( rs.next() ) {

    // 데이터가 존재할 때, 노드 생성

    node = new Board();

    node.setId(rs.getLong(2));

    node.setSubject(rs.getNString(3));

    node.setMemo(rs.getNString(4));

    node.setName(rs.getNString(5));

    boardList.add(node);

    //System.out.println("rs:" + rs.getLong(1));

    }

    }catch(Exception ex) {

    System.out.println("오류 발생: " + ex);

    }

    finally {

    dbPool.close(conn, pstmt, rs);

    }

return boardList;

}

public long getTotalCount() {

Connection conn = null;

    PreparedStatement pstmt = null;

    ResultSet rs = null;

    Board node = null;

    long cnt = 0;

    String sql = "select count(*) from board";

    System.out.println("sql:" + sql);

    try {

    conn = dbPool.getConnection();

    //System.out.println( conn.getSchema() );

    pstmt = conn.prepareStatement(sql);

    rs = pstmt.executeQuery();

    //System.out.println( rs.getFetchSize() );

    if ( rs.next() ) {

    cnt = rs.getLong(1);

        System.out.println("전체갯수:" + cnt + "/" + rs.getLong(1));

    }

    }catch(Exception ex) {

    System.out.println("오류 발생: " + ex);

    }

    finally {

    dbPool.close(conn, pstmt, rs);

    }

return cnt;

}

public long getTotalKeywordCount(String keyword) {

Connection conn = null;

    PreparedStatement pstmt = null;

    ResultSet rs = null;

    Board node = null;

    String allKeyword = "%" + keyword + "%";

    long cnt = 0;

    String sql = "select count(*) from board where subject like ?";

    //System.out.println("sql:" + sql + "/키:" + keyword);

    try {

    conn = dbPool.getConnection();

    /// System.out.println( conn.getSchema() );

    pstmt = conn.prepareStatement(sql);

    pstmt.setNString(1, allKeyword);

    rs = pstmt.executeQuery();

    /// System.out.println( rs.getFetchSize() );

    if ( rs.next() ) {

    cnt = rs.getLong(1);

        System.out.println("특정 갯수:" + cnt + "/" + rs.getLong(1));

    }

    }catch(Exception ex) {

    System.out.println("오류 발생: " + ex);

    }

    finally {

    dbPool.close(conn, pstmt, rs);

    }

return cnt;

}

}

[개선 후 SQL 파일 내용]

-- Oracle 11 - 자동번호 생성 테이블 정의
-- Table 생성 (BOARD)
-- NEW.ID (Table의 id를 가리킴)
CREATE TABLE board
(
    id NUMBER PRIMARY KEY,
    name VARCHAR2(30),
    subject VARCHAR2(30),
    memo NCLOB,
    count NUMBER,
    regidate DATE
);

-- Sequence 정의
CREATE SEQUENCE board_sequence
START WITH 1
INCREMENT BY 1;

-- Trigger 생성
-- BEFORE INSERT on '테이블명'
CREATE OR REPLACE TRIGGER board_trigger
BEFORE INSERT
    ON board
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT board_sequence.nextval INTO :NEW.ID FROM dual;
END;

/* 데이터 추가 */
INSERT INTO board (name, subject, memo, count, regidate) VALUES ('홍길동', '안녕하세요.', '메모메모', '0', '2020-09-29 11:11:00');

/* 데이터 등록 후 커밋할 것(대량 정보 처리 후) */
COMMIT;

-- 싱글 쿼리 (페이징)
SELECT * FROM (
SELECT /*+ INDEX_DESC(Z OP_SAMPLE_PK) */ ROWNUM AS RNUM, Z.* FROM (
SELECT * from board order by id desc
) Z WHERE ROWNUM <= 10
) WHERE RNUM >= 1

-- 특정 싱글 쿼리 SQL
SELECT * FROM (
SELECT /*+ INDEX_DESC(Z OP_SAMPLE_PK) */ ROWNUM AS RNUM, Z.* FROM (
SELECT * from board where subject like '%야해해%' order by id desc
) Z WHERE ROWNUM <= 10
) WHERE RNUM >= 1

페이징 로직 설계가 되었다면, 다음 계획해야 할 작업이 실질적으로는 이 작업이다.

의외로 작업을 하다보면, 골치가 아픈 문제들이 많이 생긴다.

[Oracle Databases 코너에 쿼리 관련 작업에 대해서 추가로 보충하여 소개하도록 하겠다.]

- 단일 테이블 게시물 쿼리 방법, 뷰 테이블 쿼리 방법, 테이블 조인(2개 이상) 사용 방법

-> 태스트 결과로는 "Java 페이징 로직"은 크게 문제가 없다. 
    다만, 나머지 부분은 쿼리라고 본다.

생성할 폴더: 

/src/main/webapp/board

/src/main/webapp/board/css

생성할 파일

/src/main/web/app/board/css/style.css

@charset "utf-8";

/* 1. 제목 */

h3{

font-size:20px;

font-family:'Nanum Gothic';

text-align:center;

}

/* 2. 게시판 */

/* 게시판 목록 출력 */

.board_list{

border-top:1px solid #e2e2e2;

border-bottom:1px solid #e2e2e2;

font-size:12px;

font-family:'Nanum Gothic';

width:900px;

margin:auto;

text-align:center;

}

/* 제목 */

.board_list th{

border-right:1px solid #666;

border-bottom:1px solid #666;

font-size:15px;

font-family:'Nanum Gothic';

height:20px;

text-align:center;

background-color:#eeeeee;

}

/* 내용 */

.board_list td{

border-right:1px solid #e2e2e2;

font-size:15px;

font-family:'Nanum Gothic';

height:20px;

text-align:center;

}

/* 3. 페이징 네이션 */

.paginate{

font-size:15px;

font-family:'Nanum Gothic';

font-color:#666;

text-align: center;

}

.paginate .first{

font-size:15px;

font-family:'Nanum Gothic';

font-color:#666;

text-align: center;

margin-right:10px;

}

.paginate .prev{

font-size:15px;

font-family:'Nanum Gothic';

font-color:#666;

text-align: center;

margin-right:10px;

}

.paginate .next{

font-size:15px;

font-family:'Nanum Gothic';

font-color:#666;

text-align: center;

margin-right:10px;

}

.paginate .last{

font-size:15px;

font-family:'Nanum Gothic';

font-color:#666;

text-align: center;

margin-right:10px;

}

.paginate .choice{

font-size:20px;

font-family:'Nanum Gothic';

font-color:#666;

text-align: center;

margin-right:10px;

}

/* 4. 링크 */

a{

text-decoration:none;

color:#666;

}

/* 5. 검색 영역 */

.searchArea{

font-size:15px;

font-family:'Nanum Gothic';

font-color:#666;

text-align: center;

}

생성할 폴더: 

/src/main/webapp/WEB-INF/views/

/src/main/webapp/WEB-INF/views/board

생성할 파일

/src/main/webapp/WEB-INF/views/board/list.jsp

/src/main/webapp/WEB-INF/views/board/search.jsp

/src/main/webapp/WEB-INF/views/board/paging.jsp

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

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

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

<%@ page import = "com.smile.web.model.*" %>

<%@ page session="false" %>

<!DOCTYPE html>

<html>

<head>

<meta charset="UTF-8">

<title>게시물 목록</title>

<style>

@import url('https://fonts.googleapis.com/css?family=Nanum+Gothic:400,700,800');

</style>

<link href="css/style.css" rel="stylesheet" type="text/css" />

</head>

<body>

<%

List<Board> boardList = (List<Board>)request.getAttribute("list");

%>

<h3>게시물 목록</h3>

<!-- 목록 출력 -->

<table class="board_list">

<tr>

<th style="width:15%;">

번호(Num)

</th>

<th>

제목(Subject)