도도의 초록누리

[작업 환경]

* 웹 서버: Apache-tomcat-9.0.37-windows-x64

* IDE: Spring-Tool-Suite 4-4.7.2. Releases.

* Eclipse Marketplace: STS 검색 후 Spring Tools 3 Add-On for Spring Tools 4 3.9.14.Release 설치할 것

* POM 라이브러리: 

  - Apache Commons IO 2.8.0 (2020. 09)

  - Apache Commons FileUpload (2018. 12)

* C태그(JSTL)

(중략)

<!-- Servlet -->

<!-- https://mvnrepository.com/artifact/javax.servlet/javax.servlet-api -->

<dependency>

    <groupId>javax.servlet</groupId>

    <artifactId>javax.servlet-api</artifactId>

    <version>4.0.1</version>

    <scope>provided</scope>

</dependency>

<dependency>

<groupId>javax.servlet.jsp</groupId>

<artifactId>jsp-api</artifactId>

<version>2.1</version>

<scope>provided</scope>

</dependency>

<dependency>

<groupId>javax.servlet</groupId>

<artifactId>jstl</artifactId>

<version>1.2</version>

</dependency>

<!-- https://mvnrepository.com/artifact/commons-io/commons-io -->

<dependency>

    <groupId>commons-io</groupId>

    <artifactId>commons-io</artifactId>

    <version>2.8.0</version>

</dependency>

<!-- https://mvnrepository.com/artifact/commons-fileupload/commons-fileupload -->

<dependency>

    <groupId>commons-fileupload</groupId>

    <artifactId>commons-fileupload</artifactId>

    <version>1.4</version>

</dependency>

(중략)

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

(중략)

경로: /src/main/webapp/WEB-INF/spring/appServlet/

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

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

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

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

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

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

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

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

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

http://www.springframework.org/schema/beans/spring-beans-3.0.xsd 

http://www.springframework.org/schema/context 

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

http://www.springframework.org/schema/mvc 

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

http://www.springframework.org/schema/cache 

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

<!-- MultipartResolver 설정 --> 

<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver"> 

<property name="maxUploadSize" value="100000000" /> 

<property name="maxInMemorySize" value="100000000" /> 

</bean>

</beans>

[비고]

파일 크기의 제한을 두기 위한 코드이다.

예: 최대 업로드 파일 크기는 20MB, 메모리에 최대로 저장할 수 있는 파일 크기는 10MB로 제한함.

1MB = 1,024KB

10MB = 10,485,760KB(1,024*1,024*10)

20MB = 20,971,520KB(1,024*1,024*20)

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

/WEB-INF/spring/appServlet/context-common.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>

<!-- UTF-8 -->

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

</web-app>

* 패키지 경로: com.example.postget.vo

package com.example.postget.vo;

public class MemberVO {

private int num;

private String id;

private String passwd;

private String address;

public int getNum() {

return num;

}

public void setNum(int num) {

this.num = num;

}

public String getId() {

return id;

}

public void setId(String id) {

this.id = id;

}

public String getPasswd() {

return passwd;

}

public void setPasswd(String passwd) {

this.passwd = passwd;

}

public String getAddress() {

return address;

}

public void setAddress(String address) {

this.address = address;

}

}

* 패키지 경로: com.example.postget.controller

POST 방식으로 업로드 처리에 관한 것이다.

(중략)

@RequestMapping(value = "uploadResult", method = RequestMethod.POST)

public String uploadResult(Model model, MemberVO memberVO, MultipartHttpServletRequest request) {

     // 루트 경로

           String rootUploadDir = "C:"+File.separator+"Upload"; // C:/Upload

           File dir = new File(rootUploadDir + File.separator + "testfile"); 

           if(!dir.exists()) { //업로드 디렉토리가 존재하지 않으면 생성

                dir.mkdirs();

           }

     Iterator<String> iterator = request.getFileNames(); // 다중 업로드 지원

           ArrayList<String> list = new ArrayList<String>();

           int fileLoop = 0;

           String uploadFileName;

           MultipartFile mFile = null;

           String orgFileName = ""; // 진짜 파일명

           String sysFileName = ""; // 변환된 파일명

            list.add("루트경로:" + dir + File.separator );

            while(iterator.hasNext()) {

                 fileLoop++;

                 uploadFileName = iterator.next();

                 mFile = request.getFile(uploadFileName);

                 orgFileName = mFile.getOriginalFilename();

                 logger.info(orgFileName);

                 if(orgFileName != null && orgFileName.length() != 0) { //sysFileName 생성

                   SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyyMMDDHHmmss-" + fileLoop);

                   Calendar calendar = Calendar.getInstance();

                   sysFileName = simpleDateFormat.format(calendar.getTime()); //sysFileName: 날짜-fileLoop번호

                    try {

                        logger.info("try 진입");

                        mFile.transferTo(new File(dir + File.separator + sysFileName)); // C:/Upload/testfile/sysFileName

                        list.add("원본파일명: " + orgFileName + ", 시스템파일명: " + sysFileName);

                        list.add("파일크기: " + mFile.getSize() + ", 파일형식: " + mFile.getContentType() );

                    }catch(Exception e){

                         list.add("오류: 파일 업로드 중 에러발생!!!");

                    }

                }//if

            }//while

            model.addAttribute("list", list);

return "/board/uploadResult";

}

(중략)

<!-- 파일 업로드 영역 -->

<h3>파일 업로드</h3>

<form method="POST" action="uploadResult" enctype="multipart/form-data">

<table style="width:900px; height:70px;">

<tr>

<td style="width:10%">아이디(UserID)</td>

<td style="width:90%">

<input type="text" name="id" class="login_id">

</td>

</tr>

<tr>

<td>

비밀번호(Password)

</td>

<td>

<input type="password" name="passwd" class="login_pwd">

</td>

</tr>

<tr>

<td colspan="2">

<input type="file" name="uploadFile" multiple>

</td>

</tr>

<tr>

<td colspan="2">

<input type="submit" value="로그인">

</td>

</tr>

</table>

</form>

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

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

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

    pageEncoding="UTF-8"%>

<!DOCTYPE html>

<html>

<head>

<meta charset="UTF-8">

<title>업로드 - 결과</title>

</head>

<body>

<!-- 업로드 결과 (출력) -->

<c:set var="i" value="1" />

<c:set var="num" value="1" />

<table style="width:700px; border:1px;">

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

<c:choose>

<c:when test="${i == 1}">

<tr>

<td colspan="2" style="background-color:#e2e2e2;">

<c:out value="${num}" />

</td>

</tr>

</c:when>

</c:choose>

<c:choose>

<c:when test="${i <= 3}">

<tr>

<td><c:out value="${i}" /></td>

<td>${list}</td>

<c:set var="i" value="${i + 1}" />

</tr>

</c:when>

<c:otherwise>

<c:set var="i" value="1" />

<c:set var="num" value="${num + 1}" />

</c:otherwise>

</c:choose>

</c:forEach>

</table>

</body>

</html>

[1부] [Spring-Framework] 10. Spring MVC - HikariCP 3.4.2, Oracle 19g 연동하기(Properties, Java) - (1), 2020-09-23 14:20

https://yyman.tistory.com/1410

패키지: com.springMVC.web.persistance

package com.springMVC.web.persistance;

import java.io.IOException;

import java.io.InputStream;

import java.io.Reader;

import java.util.Properties;

import java.sql.Connection;

import java.sql.SQLException;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

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

import com.zaxxer.hikari.HikariConfig;

import com.zaxxer.hikari.HikariDataSource;

public class DataSource {

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

    private static String CLASSNAME;

    private static String JDBC_URL;

    private static String USERNAME;

    private static String PASSWORD;

    private static String CACHE_PREP_STMTS;

    private static HikariDataSource ds;

/*

(자바 방식)

    private static HikariConfig config = new HikariConfig();

    static {

    config.setDriverClassName("oracle.jdbc.OracleDriver");

        config.setJdbcUrl( "jdbc:oracle:thin:@localhost:1521:orcl" );

        config.setUsername( "사용자계정명" );

        config.setPassword( "비밀번호" );

        config.addDataSourceProperty( "cachePrepStmts" , "true" );

        config.addDataSourceProperty( "prepStmtCacheSize" , "250" );

        config.addDataSourceProperty( "prepStmtCacheSqlLimit" , "2048" );

        ds = new HikariDataSource( config );

    }

    */

    private HikariConfig config;

    public DataSource() {

    InputStream inputStream;

    config = new HikariConfig();

    String resource = "db.properties";

        Properties properties = new Properties();

        try {

        inputStream = getClass().getClassLoader().getResourceAsStream(resource);

            properties.load(inputStream);

            System.out.println(properties.getProperty("jdbcUrl"));

            System.out.println(properties.getProperty("dataSourceClassName"));

            CLASSNAME = properties.getProperty("dataSourceClassName");

            JDBC_URL = properties.getProperty("jdbcUrl");

            USERNAME = properties.getProperty("dataSource.user");

            PASSWORD = properties.getProperty("dataSource.password");

            CACHE_PREP_STMTS = properties.getProperty("cachePrepStmts");

            config.setDriverClassName(CLASSNAME);

            config.setJdbcUrl( JDBC_URL );

            config.setUsername( USERNAME );

            config.setPassword( PASSWORD );

            config.addDataSourceProperty( "cachePrepStmts" , CACHE_PREP_STMTS );

            config.addDataSourceProperty( "prepStmtCacheSize" , "250" );

            config.addDataSourceProperty( "prepStmtCacheSqlLimit" , "2048" );

            ds = new HikariDataSource( config );

        } catch (IOException e) {

            e.printStackTrace();

        }

    }

    public Connection getConnection() throws SQLException {

        return ds.getConnection();

    }

}

비고: 클래스명에 대해서 "DataSource"로 해도 되나요?

       javax.sql.DataSource가 존재한다. 예제의 DataSource 클래스명은 좋은 명칭은 아니다. 

       (동작은 될 수 있겠으나 javax.sql.DataSource를 사용할 때는 명칭에 대해서 다시 생각을 해봐야 할 것이다.)

package com.springMVC.web.controller;

import java.text.DateFormat;

import java.util.Date;

import java.util.Locale;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

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.springMVC.web.dao.AddressBookDao;

import com.springMVC.web.persistance.DataSource;

/**

 * Handles requests for the application home page.

 */

@Controller

public class HomeController {

/*

// Controller 안에서만 사용가능.

@Value("#{props['dataSourceClassName']}")

private String CLASSNAME;

@Value("#{props['jdbcUrl']}")

private String JDBC_URL;

@Value("#{props['dataSource.user']}")

private String USERNAME;

@Value("#{props['dataSource.password']}")

private String PASSWORD;

*/

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

// @Value("#{props['jdbcUrl']}")

// private String url;

/**

* 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);

Date date = new Date();

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

String formattedDate = dateFormat.format(date);

try {

DataSource ds = new DataSource();

//ds.getConnection();

AddressBookDao dao = new AddressBookDao();

dao.allSelect();

//logger.info("URL:{}", JDBC_URL);

//dao.allSelect();

}

catch(Exception e) {

logger.info("msg: {}", e.getMessage());

}

model.addAttribute("serverTime", formattedDate );

return "home";

}

}

package com.springMVC.web.dao;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import com.springMVC.web.persistance.DataSource;

public class AddressBookDao {

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

public void allSelect() {

try {

DataSource ds = new DataSource();

Connection conn = ds.getConnection();

String query = "select * from addressbook";

PreparedStatement pstmt = conn.prepareStatement(query);

ResultSet rs = pstmt.executeQuery();

while(rs.next()) {

String msg = "번호:" + rs.getString("num") + "/이름:" + rs.getString("name") + "/주소:" + rs.getString("address");

logger.info("Msg: {}.", msg);

}

rs.close();

conn.close();

} catch (SQLException e) {

logger.info("msg:{}",  e.getMessage());

}

}

}

package com.springMVC.web.persistance;

import java.io.IOException;

import java.io.InputStream;

import java.io.Reader;

import java.util.Properties;

import java.sql.Connection;

import java.sql.SQLException;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

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

import com.zaxxer.hikari.HikariConfig;

import com.zaxxer.hikari.HikariDataSource;

public class DataSource {

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

    private static String CLASSNAME;

    private static String JDBC_URL;

    private static String USERNAME;

    private static String PASSWORD;

    private static String CACHE_PREP_STMTS;

    private static HikariDataSource ds;

    private static HikariConfig config = new HikariConfig();

    static {

    config.setDriverClassName("oracle.jdbc.OracleDriver");

        config.setJdbcUrl( "jdbc:oracle:thin:@localhost:1521:orcl" );

        config.setUsername( "사용자계정명" );

        config.setPassword( "비밀번호" );

        config.addDataSourceProperty( "cachePrepStmts" , "true" );

        config.addDataSourceProperty( "prepStmtCacheSize" , "250" );

        config.addDataSourceProperty( "prepStmtCacheSqlLimit" , "2048" );

        ds = new HikariDataSource( config );

    }

    /*

    private HikariConfig config;

    public DataSource() {

    InputStream inputStream;

    config = new HikariConfig();

    String resource = "db.properties";

        Properties properties = new Properties();

        try {

        inputStream = getClass().getClassLoader().getResourceAsStream(resource);

            properties.load(inputStream);

            System.out.println(properties.getProperty("jdbcUrl"));

            System.out.println(properties.getProperty("dataSourceClassName"));

            CLASSNAME = properties.getProperty("dataSourceClassName");

            JDBC_URL = properties.getProperty("jdbcUrl");

            USERNAME = properties.getProperty("dataSource.user");

            PASSWORD = properties.getProperty("dataSource.password");

            CACHE_PREP_STMTS = properties.getProperty("cachePrepStmts");

            config.setDriverClassName(CLASSNAME);

            config.setJdbcUrl( JDBC_URL );

            config.setUsername( USERNAME );

            config.setPassword( PASSWORD );

            config.addDataSourceProperty( "cachePrepStmts" , CACHE_PREP_STMTS );

            config.addDataSourceProperty( "prepStmtCacheSize" , "250" );

            config.addDataSourceProperty( "prepStmtCacheSqlLimit" , "2048" );

            ds = new HikariDataSource( config );

        } catch (IOException e) {

            e.printStackTrace();

        }

    }

    */

    public static Connection getConnection() throws SQLException {

        return ds.getConnection();

    }

}

package com.springMVC.web.controller;

import java.text.DateFormat;

import java.util.Date;

import java.util.Locale;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

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.springMVC.web.dao.AddressBookDao;

import com.springMVC.web.persistance.DataSource;

/**

 * Handles requests for the application home page.

 */

@Controller

public class HomeController {

/*

// Controller 안에서만 사용가능.

@Value("#{props['dataSourceClassName']}")

private String CLASSNAME;

@Value("#{props['jdbcUrl']}")

private String JDBC_URL;

@Value("#{props['dataSource.user']}")

private String USERNAME;

@Value("#{props['dataSource.password']}")

private String PASSWORD;

*/

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

// @Value("#{props['jdbcUrl']}")

// private String url;

/**

* 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);

Date date = new Date();

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

String formattedDate = dateFormat.format(date);

try {

//ds.getConnection();

AddressBookDao dao = new AddressBookDao();

dao.allSelect();

//logger.info("URL:{}", JDBC_URL);

//dao.allSelect();

}

catch(Exception e) {

logger.info("msg: {}", e.getMessage());

}

model.addAttribute("serverTime", formattedDate );

return "home";

}

}

package com.springMVC.web.dao;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import com.springMVC.web.persistance.DataSource;

public class AddressBookDao {

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

public void allSelect() {

try {

Connection conn = DataSource.getConnection();

String query = "select * from addressbook";

PreparedStatement pstmt = conn.prepareStatement(query);

ResultSet rs = pstmt.executeQuery();

while(rs.next()) {

String msg = "번호:" + rs.getString("num") + "/이름:" + rs.getString("name") + "/주소:" + rs.getString("address");

logger.info("Msg: {}.", msg);

}

rs.close();

conn.close();

} catch (SQLException e) {

logger.info("msg:{}",  e.getMessage());

}

}

}

공식 사이트: https://github.com/brettwooldridge/HikariCP

-> 공식 사이트의 메뉴얼이 다소 부족한 면이 있다.

* IDE: Spring Tool-Suite 4-4.7.2 Releases(Eclipse)

* Web Server(웹서버):

Apache Tomcat 9

* 데이터베이스(Databases)

Oracle Databases 19

MySQL 8.0.21

* 프레임워크

Spring Framework 3.1.1 Releases

* 라이브러리

POM - Oracle JDBC 19

POM - MySQL 8.0.21

Maven Project - 3.6.3/1.16.0.20200610-1735

특징: MyBatis 제거 등 영속 프레임워크 미적용

[JAVA JDK 버전 오류]

오류 : Resource specification not allowed here for source level below 1.7

      <properties>

<java-version>14</java-version>

<org.springframework-version>3.1.1.RELEASE</org.springframework-version>

<org.aspectj-version>1.6.10</org.aspectj-version>

<org.slf4j-version>1.6.6</org.slf4j-version>

</properties>

(중략)

<!-- spring-test -->

<dependency>

<groupId>org.springframework</groupId>

<artifactId>spring-test</artifactId>

<version>${org.springframework-version}</version>

</dependency>

(중략)

<!-- https://mvnrepository.com/artifact/com.zaxxer/HikariCP -->

<dependency>

    <groupId>com.zaxxer</groupId>

    <artifactId>HikariCP</artifactId>

    <version>3.4.2</version>

</dependency>

<dependency>

<groupId>com.oracle.database.jdbc</groupId>

<artifactId>ojdbc8</artifactId>

<version>19.3.0.0</version>

</dependency>    

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->

<dependency>

    <groupId>mysql</groupId>

    <artifactId>mysql-connector-java</artifactId>

    <version>8.0.21</version>

</dependency>

[JAVA JDK 버전 오류]

오류 : Resource specification not allowed here for source level below 1.7

-> src/main/webapp/spring/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"

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

<!-- Root Context: defines shared resources visible to all other web components -->

<bean id="hikariConfig" class="com.zaxxer.hikari.HikariConfig">

<!-- Oracle 9 이상으로 작성. -->

<!-- 오라클 9 이후에 oracle.jdbc.driver.OracleDriver는 중단되었음.  -->

<!-- oracle.jdbc.pool.OracleDataSource -->

<!-- jdbc:oracle:thin:@localhost:1521:orcl -->

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

<property name="jdbcUrl" value="jdbc:oracle:thin:@localhost:1521:orcl"></property> 

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

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

</bean>

<bean id="datasource" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">

<constructor-arg ref="hikariConfig"></constructor-arg>

</bean>

</beans>

<?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 https://www.springframework.org/schema/beans/spring-beans.xsd">

<!-- Root Context: defines shared resources visible to all other web components -->

<bean id="hikariConfig" class="com.zaxxer.hikari.HikariConfig">

  <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"></property>

<property name="jdbcUrl" value="jdbc:mysql://localhost:{포트번호}/{DB명}?serverTimezone=UTC"></property> 

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

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

</bean>

<bean id="datasource" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">

<constructor-arg ref="hikariConfig"></constructor-arg>

</bean>

</beans>

* 정정: jdbc:mysql://localhost:3306/web?serverTimezone=UTC&characterEncoding=utf8

파일 경로: /src/main/webapp/WEB-INF/spring/appServlet

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

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

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

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

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

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

xsi:schemaLocation="http://www.springframework.org/schema/mvc https://www.springframework.org/schema/mvc/spring-mvc.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

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

<!-- DispatcherServlet Context: defines this servlet's request-processing infrastructure -->

<!-- Enables the Spring MVC @Controller programming model -->

<annotation-driven />

<!-- Handles HTTP GET requests for /resources/** by efficiently serving up static resources in the ${webappRoot}/resources directory -->

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

<!-- Resolves views selected for rendering by @Controllers to .jsp resources in the /WEB-INF/views directory -->

<beans:bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">

<beans:property name="prefix" value="/WEB-INF/views/" />

<beans:property name="suffix" value=".jsp" />

</beans:bean>

<!-- 추가(Hikara4 -->

<!-- <context:property-placeholder location="classpath:/db.properties" />  -->

<util:properties id="props" location="classpath:/db.properties" />     

<context:component-scan base-package="com.springMVC.web.controller" />

</beans:beans>

jdbcUrl=jdbc:oracle:thin:@localhost:1521:orcl

dataSourceClassName=oracle.jdbc.OracleDriver

dataSource.user={username}

dataSource.password={password}

cachePrepStmts=true

prepStmtCacheSize=250

prepStmtCacheSqlLimit=2048

jdbcUrl=jdbc:mysql://localhost:3306/web?serverTimezone=UTC

dataSourceClassName=com.mysql.cj.jdbc.Driver

dataSource.user={UserName}

dataSource.password={Password}

cachePrepStmts=true

prepStmtCacheSize=250

prepStmtCacheSqlLimit=2048

[Spring-Framework] 11. Spring MVC - HikariCP 3.4.2, Oracle 19g 연동하기(Properties, Java) - (2), 2020-09-23 14:28

https://yyman.tistory.com/1411

일반 범용 "게시판"에서 사용될 수 있는 것 위주로 작성하였다.

* IDE: Spring Tool-Suite 4(Eclipse)

-> Spring Legacy Project의 Spring MVC Project를 기반으로 작성함.

이 프로젝트의 특징은 추후에 MyBatis 등의 라이브러리까지 고민하여 작성하였다.

그림 4. 프로젝트 구성(Controller와 VO)

그림 5. 프로젝트 구성(view영역의 jsp 파일)

package com.springMVC1.web.vo;

public class MemberVO {

private String id;

private String passwd;

public String getId() {

return id;

}

public void setId(String id) {

this.id = id;

}

public String getPasswd() {

return passwd;

}

public void setPasswd(String passwd) {

this.passwd = passwd;

}

}

package com.springMVC1.web.controller;

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 java.util.Map;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

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.springMVC1.web.vo.MemberVO;

@Controller

@RequestMapping(value="/board")

public class BoardController {

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

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

public String list(Locale locale, Model model) {

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

Date date = new Date();

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

String formattedDate = dateFormat.format(date);

model.addAttribute("serverTime", formattedDate );

return "/board/list";

}

// GET, POST 방식 모두 가능

@RequestMapping(value = "insert")

public String insert(Locale locale, Model model) {

return "/board/insert";

}

// POST방식만 사용

@RequestMapping(value = "insertResult", method=RequestMethod.POST)

public String insertResult(Model model, MemberVO memberVO) {

model.addAttribute("serverTime", "1군");

logger.info("로거 사용"); // 로거

HashMap<String, Object> map = new HashMap<String, Object>();

MemberVO node1 = new MemberVO();

// 1번 회원

node1.setId("user1");

node1.setPasswd("1234");

map.put("1", node1);

// 2번 회원

node1 = new MemberVO();

node1.setId("user2");

node1.setPasswd("9876");

map.put("2", node1);

// model 속성으로 등록

model.addAttribute("map", map);

return "/board/insert_result_post";

}

// GET방식만 사용 - 편법(매개변수 하나 더 추가) 

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

public String insertResult(Model model, MemberVO memberVO, String s) {

model.addAttribute("serverTime", "2군");

logger.info("로거 사용"); // 로거

// 다중 회원 목록 출력 실험

List<MemberVO> member = new ArrayList<MemberVO>();

MemberVO node1 = new MemberVO();

// 1번 회원

node1.setId("user1");

node1.setPasswd("1234");

member.add(node1);

// 2번 회원

node1 = new MemberVO();

node1.setId("user2");

node1.setPasswd("9876");

member.add(node1);

model.addAttribute("list", member);

return "/board/insert_result_get";

}

}

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

    pageEncoding="UTF-8"%>

<!DOCTYPE html>

<html>

<head>

<meta charset="UTF-8">

<title>글쓰기 - 양식</title>

</head>

<body>

<%

String context = request.getContextPath();

%>

<h3>POST 방식</h3>

<form action="<%=context%>/board/insertResult" method="POST">

ID: <input type="text" name="id"><br/>

비밀번호: <input type="password" name="passwd"><br/>

<input type="submit" value="전송">

</form>

<br />

<h3>GET 방식</h3>

<form action="<%=context%>/board/insertResult" method="GET">

ID: <input type="text" name="id"><br/>

비밀번호: <input type="password" name="passwd"><br/>

<input type="submit" value="전송">

</form>

</body>

</html>

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

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

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

    pageEncoding="UTF-8"%>

<!DOCTYPE html>

<html>

<head>

<meta charset="UTF-8">

<title>등록 결과 - GET방식(실험)</title>

</head>

<body>

<!-- 결과 -->

<h3>GET 방식 - 실험</h3>

<h5>GET으로 전송받은 데이터</h5>

<table border="1" style="width:500px">

<tr>

<td>서버시간</td>

<td>${serverTime }</td>

</tr>

<tr>

<td>ID</td>

<td>${memberVO.id}</td>

</tr>

<tr>

<td>비밀번호</td>

<td>${memberVO.passwd }</td>

</tr>

</table>

<br />

<h5>ArrayList 결과 출력하기</h5>

<!-- 다중 회원 출력 -->

<table border="1" style="width:500px">

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

<tr>

<td>ID</td>

<td>${list.id}</td>

<td>비밀번호</td>

<td>${list.passwd }</td>

</tr>

</c:forEach>

</table>

</body>

</html>

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

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

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

    pageEncoding="UTF-8"%>

<!DOCTYPE html>

<html>

<head>

<meta charset="UTF-8">

<title>등록 결과 - Post방식(실험)</title>

</head>

<body>

<!-- 결과 -->

<h3>POST 방식 - 실험</h3>

<br/>

<table border="1" style="width:500px">

<tr>

<td>서버시간</td>

<td>${serverTime }</td>

</tr>

<tr>

<td>ID</td>

<td>${memberVO.id}</td>

</tr>

<tr>

<td>비밀번호</td>

<td>${memberVO.passwd }</td>

</tr>

</table>

<h5>Map 결과 출력하기</h5>

<!-- 다중 회원 출력 -->

<table border="1" style="width:700px">

<c:forEach items="${map}" var="i">

<tr>

<td style="width:50px">1. Key값</td>

<td style="width:150px">${i.key}</td>

<td style="width:50px">2. 객체주소</td>

<td style="width:200px">${i.value}</td>

<%

%>

<td style="width:50px">3. 객체값(ID)</td>

<td style="width:100px">${i.value.id}</td>

<td style="width:50px">3. 객체값(Passwd)</td>

<td style="width:100px">${i.value.passwd}</td>

</tr>

</c:forEach>

</table>

</body>

</html>

* 형식문자

-> 년도 출력

<fmt:formatNumber value="${변수명}" pattern="yyyy-mm-dd" />

<fmt:formatNumber value="${객체명.멤버변수명}" pattern="yyyy-mm-dd" />

* 웹 서버: Apache Tomcat 9

  - JSP/Servlet 기초 이해력

* 라이브러리: Maven(메이븐)

* Spring 프레임워크없이, JSP/Servlet 기반으로 MVC2를 직접 구현할 수 있는 수준

운영체제: Microsoft Windows 10

개발환경: Spring Tool-Suite 4-4.7.2. Release (Eclipse 기반 개발)

웹 서버: apache-tomcat-9.0.37-windows-x64

의외로 쉽게 MVC 프로젝트가 생성되는 것을 확인할 수 있다.

참고로 com.springMVC1.web으로 작성하면, 태스트 배포 주소는 "홈페이지주소:8080/web"이 된다.

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

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

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

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

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

xsi:schemaLocation="http://www.springframework.org/schema/mvc https://www.springframework.org/schema/mvc/spring-mvc.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">

<!-- DispatcherServlet Context: defines this servlet's request-processing infrastructure -->

<!-- Enables the Spring MVC @Controller programming model -->

<annotation-driven />

<!-- Handles HTTP GET requests for /resources/** by efficiently serving up static resources in the ${webappRoot}/resources directory -->

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

<!-- Resolves views selected for rendering by @Controllers to .jsp resources in the /WEB-INF/views directory -->

<beans:bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">

<beans:property name="prefix" value="/WEB-INF/views/" />

<beans:property name="suffix" value=".jsp" />

</beans:bean>

<context:component-scan base-package="com.springMVC1.web" />

</beans:beans>

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

<web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee"

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

xsi:schemaLocation="http://java.sun.com/xml/ns/javaee https://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">

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

<!-- UTF-8 -->

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

</web-app>

정정: 

(1) HomeController.class -> BoardController.class로 변경 (탈자)
(2) Welcome home: The ..... -> Welcome board: The...로 변경

[1부] [Spring-Framework] 6. Maven, Hibernate 5.4, Servlet, MySQL 8 연동(CRUD) - (1) , 2020-09-21 14:36
https://yyman.tistory.com/1404

package com.hibernateMaven.web.model;

import java.sql.Date;

import javax.persistence.*;

@Entity

@Table(name = "emptable")

public class Emptable

{

@Id

@GeneratedValue(strategy = GenerationType.AUTO)

private int empno;

@Column(name="name")

private String name;

@Column(name="address")

private String address;

@Column(name="createdate")

private Date createdate;

public int getEmpno() {

return empno;

}

public void setEmpno(int empno) {

this.empno = empno;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public String getAddress() {

return address;

}

public void setAddress(String address) {

this.address = address;

}

public Date getCreatedate() {

return createdate;

}

public void setCreatedate(Date createdate) {

this.createdate = createdate;

}

}

package com.hibernateMaven.web.model;

import java.sql.Date;

import javax.persistence.Column;

import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.GenerationType;

import javax.persistence.Id;

import javax.persistence.Table;

@Entity

@Table(name = "student")

public class Student {

@Id

@GeneratedValue(strategy = GenerationType.AUTO)

private int studentno;

@Column(name="name")

private String name;

@Column(name="address")

private String address;

@Column(name="createdate")

private Date createdate;

public int getStudentno() {

return studentno;

}

public void setStudentno(int studentno) {

this.studentno = studentno;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public String getAddress() {

return address;

}

public void setAddress(String address) {

this.address = address;

}

public Date getCreatedate() {

return createdate;

}

public void setCreatedate(Date createdate) {

this.createdate = createdate;

}

}

Xml-Mapping 관련 파일이 필요한 이유는 JBoss-Hibernate에서 현재 생성이 되지 않고 있다.
(JBoss의 업데이트 등의 문제) - [2020-09-21 기준]

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

<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN"

                                         "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">

<hibernate-configuration>

 <session-factory name="">

  <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>

  <property name="hibernate.connection.username">사용자계정</property>

  <property name="hibernate.connection.password">비밀번호</property>

  <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/web?serverTimezone=UTC&amp;characterEncoding=utf8</property>

  <property name="hibernate.dialect">org.hibernate.dialect.MySQL8Dialect</property>

  <property name="hibernate.show_sql">true</property>

  <property name="hibernate.hbm2ddl.auto">update</property>

  <property name="hibernate.default_entity_mode">pojo</property>

  <property name="hibernate.current_session_context_class">thread</property>

  <mapping class="com.hibernateMaven.web.model.Emptable"/>    <!-- empTable 클래스 맵핑-->

  <mapping class="com.hibernateMaven.web.model.Student"/>

 </session-factory>

</hibernate-configuration>

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

<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN"

                                         "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">

<hibernate-configuration>

 <session-factory name="">

  <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>

  <property name="hibernate.connection.username">사용자명</property>

  <property name="hibernate.connection.password">비밀번호</property>

  <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/web?serverTimezone=UTC&amp;characterEncoding=utf8</property>

  <property name="hibernate.dialect">org.hibernate.dialect.MySQL8Dialect</property>

  <property name="hibernate.show_sql">true</property>

  <property name="hibernate.hbm2ddl.auto">update</property>

  <property name="hibernate.default_entity_mode">pojo</property>

  <property name="hibernate.current_session_context_class">thread</property>

  <mapping class="com.hibernateMaven.web.model.Student"/>

  <!-- XML Resource 자원으로 맵핑 -->

  <mapping resource="com/hibernateMaven/web/model/Emptable.hbm.xml"/>

 </session-factory>

</hibernate-configuration>

package com.hibernateMaven.web.factory;

import org.hibernate.SessionFactory;

import org.hibernate.boot.Metadata;

import org.hibernate.boot.MetadataSources;

import org.hibernate.boot.registry.StandardServiceRegistry;

import org.hibernate.boot.registry.StandardServiceRegistryBuilder;

public class HibernateUtil

{

    private static StandardServiceRegistry registry;

    private static SessionFactory sessionFactory;

    public static SessionFactory getSessionFactory() {

        if (sessionFactory == null) {

            try {

                // Create registry

                registry = new StandardServiceRegistryBuilder().configure().build();

                // Create MetadataSources

                MetadataSources sources = new MetadataSources(registry);

                // Create Metadata

                Metadata metadata = sources.getMetadataBuilder().build();

                // Create SessionFactory

                sessionFactory = metadata.getSessionFactoryBuilder().build();

            } catch (Exception e) {

                e.printStackTrace();

                if (registry != null) {

                    StandardServiceRegistryBuilder.destroy(registry);

                }

            }

        }

        return sessionFactory;

    }

    public static void shutdown() {

        if (registry != null) {

            StandardServiceRegistryBuilder.destroy(registry);

        }

    }

}

package com.hibernateMaven.web.service;

import java.util.List;

import com.hibernateMaven.web.model.Emptable;

public interface IEmpTable {

public List<Emptable> allList(); // Select * FROM empTable

public Emptable getList(Integer num); // Select * from emptable where empno = ?

public void save(Emptable emptable); // insert into emptable

public int update(Emptable emptable); // update emptable set cause where emp = ? 

public int delete(Integer num); // delete from emptable where empno = ?

public Emptable searchName(Integer num, String name); // 두 가지 조건 동시 만족 조회

}

package com.hibernateMaven.web.service;

import java.util.List;

import com.hibernateMaven.web.model.Student;

public interface IStudent {

public List<Student> allList(); // Select * FROM student

public Student getList(Integer num); // Select * from student where studentno = ?

}

package com.hibernateMaven.web.service;

import java.util.List;

import org.hibernate.query.*;

import org.hibernate.Session;

import org.hibernate.Transaction;

import com.hibernateMaven.web.factory.HibernateUtil;

import com.hibernateMaven.web.model.Emptable;

public class EmpTableService implements IEmpTable {

@Override

public List<Emptable> allList() {

Transaction transaction = null;

List<Emptable> list = null;

try (Session session = HibernateUtil.getSessionFactory().openSession()) {

// start a transaction

transaction = session.beginTransaction();

// get an user object

list = session.createQuery("from Emptable").getResultList();

System.out.println("연습:" + list.get(0).getName());

// commit transaction

transaction.commit();

} catch (Exception e) {

if (transaction != null) {

transaction.rollback();

}

e.printStackTrace();

}

        return list;

}

@Override

public Emptable getList(Integer num) {

Transaction transaction = null;

Emptable emptable = null;

try (Session session = HibernateUtil.getSessionFactory().openSession()) {

transaction = session.beginTransaction();

emptable = session.get(Emptable.class, num);

System.out.println("특정 조회:" + emptable.getName());

transaction.commit();

return emptable;

        } catch (Exception e) {

            if (transaction != null) {

                transaction.rollback();

            }

            e.printStackTrace();

            return null;

        }

}

@Override

public void save(Emptable emptable) {

Transaction transaction = null;

try (Session session = HibernateUtil.getSessionFactory().openSession()) {

// start a transaction

transaction = session.beginTransaction();

// save the student object

session.save(emptable);

// commit transaction

transaction.commit();

} catch (Exception e) {

if (transaction != null) {

transaction.rollback();

}

e.printStackTrace();

}

}

@Override

public int update(Emptable emptable) {

Transaction transaction = null;

String hql = null;

int result = -1;

try (Session session = HibernateUtil.getSessionFactory().openSession()) {

// start a transaction

transaction = session.beginTransaction();

// save the student object

hql = "UPDATE Emptable set name = :name, "  +

"address = :address, createdate = :createdate " +

             "WHERE empno = :empno";

Query query = session.createQuery(hql);

query.setParameter("name", emptable.getName());

query.setParameter("address", emptable.getAddress());

query.setParameter("createdate", emptable.getCreatedate());

query.setParameter("empno", emptable.getEmpno());

result = query.executeUpdate();

System.out.println("수정결과:" + result);

// commit transaction

transaction.commit();

} catch (Exception e) {

if (transaction != null) {

transaction.rollback();

}

e.printStackTrace();

}

return result;

}

@Override

public int delete(Integer num) {

Transaction transaction = null;

String hql = null;

int result = -1;

try (Session session = HibernateUtil.getSessionFactory().openSession()) {

// start a transaction

transaction = session.beginTransaction();

// save the student object

hql = "Delete from Emptable " +

             "WHERE empno = :empno";

Query query = session.createQuery(hql);

query.setParameter("empno",  num);

result = query.executeUpdate();

System.out.println("삭제결과:" + result);

// commit transaction

transaction.commit();

} catch (Exception e) {

if (transaction != null) {

transaction.rollback();

}

e.printStackTrace();

}

return result;

}

@Override

public Emptable searchName(Integer num, String name) {

Transaction transaction = null;

String hql = null;

Emptable emp = null;

try (Session session = HibernateUtil.getSessionFactory().openSession()) {

// start a transaction

transaction = session.beginTransaction();

// save the student object

hql = "from Emptable " +

             "WHERE empno = :empno and name = :name";

Query query = session.createQuery(hql);

query.setParameter("empno", num);

query.setParameter("name",  name);

emp = (Emptable) query.list().get(0);

System.out.println("번호, 이름 검색");

// commit transaction

transaction.commit();

return emp;

} catch (Exception e) {

if (transaction != null) {

transaction.rollback();

}

e.printStackTrace();

return null;

}

}

}

package com.hibernateMaven.web.service;

import java.util.List;

import org.hibernate.query.*;

import org.hibernate.Session;

import org.hibernate.Transaction;

import com.hibernateMaven.web.factory.HibernateUtil;

import com.hibernateMaven.web.model.Student;

public class StudentService implements IStudent {

@Override

public List<Student> allList() {

return null;

}

@Override

public Student getList(Integer num) {

Student student;

Transaction transaction = null;

        Session session= HibernateUtil.getSessionFactory().openSession();

         try

         {

  transaction = session.beginTransaction();

             student=(Student)session.get(Student.class,num); 

             System.out.println("특정 조회:" + student.getName());

             transaction.commit();

             return student;

         }

         catch (Exception e) 

         {

        e.printStackTrace();

            return null;

         }

         finally 

         {

             session.close(); 

         }

         /*

Student student = null;

try (Session session = HibernateUtil.getSessionFactory().openSession()) {

transaction = session.beginTransaction();

student = session.get(Student.class, num);

System.out.println("특정 조회:" + student.getName());

        } catch (Exception e) {

            if (transaction != null) {

                transaction.rollback();

            }

            e.printStackTrace();

        }

*/

}

}

package com.hibernateMaven.web.controller;

import java.io.IOException;

import java.io.PrintWriter;

import java.sql.Date;

import java.util.List;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import com.hibernateMaven.web.model.Emptable;

import com.hibernateMaven.web.service.EmpTableService;

import com.hibernateMaven.web.service.StudentService;

public class SampleServlet extends HttpServlet {

private static final long serialVersionUID = 1L;

    public SampleServlet() {

        super();

    }

/**

* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)

*/

protected void doGet(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException {

res.setContentType("text/html;charset=UTF-8");

PrintWriter out = res.getWriter();

// HTML 스타일 정의

out.println("<html><head><title>Hibernate Maven - MySQL 8 실험</title></head><body>");

EmpTableService service = new EmpTableService();

// 1. 전체 조회

List<Emptable> emp1 = service.allList();

// 2. 특정 ID 조회

Emptable emp2 = service.getList(1);

// 3. 삽입 구현

Emptable createEmp = new Emptable();

createEmp.setName("홍길동");

createEmp.setAddress("행복구 행복시");

java.sql.Date sqlDate = java.sql.Date.valueOf("2020-05-20");

createEmp.setCreatedate(sqlDate);

service.save(createEmp);

// 4. 수정

Emptable updateEmp = new Emptable();

updateEmp.setName("동길홍");

updateEmp.setAddress("시복행 구복행");

sqlDate = java.sql.Date.valueOf("1990-09-01");

updateEmp.setCreatedate(sqlDate);

updateEmp.setEmpno(3);

service.update(updateEmp);

// 5. 삭제

service.delete(4);

// 6. 특정 이름, 번호 조회

Emptable query1 = service.searchName(18, "홍길동");

out.println("주소:" + query1.getAddress() + "<br/>" );

out.println("</body></html>");

out.close();

}

/**

* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)

*/

protected void doPost(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException {

}

}

package com.hibernateMaven.web.controller;

import java.io.IOException;

import java.io.PrintWriter;

import java.util.List;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import com.hibernateMaven.web.model.Student;

import com.hibernateMaven.web.service.StudentService;

public class StudentServlet extends HttpServlet {

private static final long serialVersionUID = 1L;

    public StudentServlet() {

        super();

    }

/**

* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)

*/

protected void doGet(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException {

res.setContentType("text/html;charset=UTF-8");

PrintWriter out = res.getWriter();

// HTML 스타일 정의

out.println("<html><head><title>Hibernate Maven - MySQL 8 실험(Table:Student)</title></head><body>");

StudentService service2 = new StudentService();

// 1. 전체 조회

//List<Student> student1 = service.allList();

service2.getList(1);

out.println("</body></html>");

out.close();

}

/**

* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)

*/

protected void doPost(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException {

doGet(req, res);

}

}

추가적으로 알아보면 도움되는 것: Hibernate HQL
-> SQL언어가 사라진 것이 아니라, 또 Hibernate가 제공하는 HQL이 탄생하게 되었다.
ORM을 맹신하면 안 되는 이유가 있는 것이다.

* Link1: https://howtodoinjava.com/hibernate/complete-hibernate-query-language-hql-tutorial/ (영어), 2014-10-30

  Link2: https://www.tutorialspoint.com/hibernate/hibernate_query_language.htm, (영어)

* 공식 사이트: https://docs.jboss.org/hibernate/orm/5.4/userguide/html_single/Hibernate_User_Guide.html#hql, Hibernate 5.4 Final 메뉴얼

  (조금 공부할 양이 많다.) - 2020-08-27 10:35:21

Hibernate(하이버네이트)란?

하이버네이트 ORM은 자바 언어를 위한 객체 관계 매핑 프레임워크이다.
객체 지향 도메인 모델을 관계형 데이터베이스로 매핑하기 위한 프레임워크를 제공한다.
하이버네이트는 GNU LGPL 2.1로 배포되는 자유 소프트웨어이다.

https://hibernate.org/

ORM(Object-relational mapping)이란?

ORM(Object-relational mapping)을 단순하게 표현하면 객체와 관계와의 설정이라 할 수 있다.
ORM에서 말하는 객체(Object)의 의미는 우리가 흔히 알고 있는 OOP(Object_Oriented Programming)의 그 객체를 의미한다는 것을 쉽게 유추할 수 있을 것이다. 그렇다면 과연 관계라는 것이 의미하는 것은 무엇일까?
지극히 기초적인 이야기지만 개발자가 흔히 사용하고 있는 관계형 데이터베이스를 의미한다.

[환경]

- 운영체제: Microsoft Windows 10

- 웹서버: Apache-tomcat-9.0.37                (http://tomcat.apache.org/)

- IDE: Spring Tool-Suite 4-4.7.2 Releases      (https://spring.io/tools)

  * JBoss -> Hibernate만 설치할 것(필수) - Help->Eclipse Marketplace

- 데이터베이스: MySQL 8.0.21 Windows 64bit  (https://dev.mysql.com/downloads/mysql/)

  * Library: Connector/J 8.0.21

- 개발 라이브러리:

  * Maven

    - https://maven.apache.org/

  * hibernate-entitymanager (5.4.21 Final)

    - https://mvnrepository.com/artifact/org.hibernate/hibernate-entitymanager

    - https://hibernate.org/

  * javax.inject (1)
  * javax.servlet-api (4.0.1)

    - https://mvnrepository.com/artifact/javax.servlet/javax.servlet-api

  * mysql-connector-java (8.0.21)

    - https://mvnrepository.com/artifact/mysql/mysql-connector-java

설치 방법은 워낙 간단하고, 잡음이 적은 프로그램이므로 짧게 정리하였다.

CREATE TABLE `emptable` (

  `empno` INT NOT NULL AUTO_INCREMENT,

  `name` VARCHAR(20) NULL,

  `address` VARCHAR(100) NULL,

  `createdate` DATE NULL,

  PRIMARY KEY (`empno`));

INSERT INTO `emptable` (`name`, `address`, `createdate`) VALUES ('하하', '안녕', '20-09-1');

CREATE TABLE `student` (

  `studentno` INT NOT NULL AUTO_INCREMENT,

  `name` VARCHAR(45) NULL,

  `address` VARCHAR(100) NULL,

  `createdate` DATE NULL,

  PRIMARY KEY (`studentno`))

ENGINE = InnoDB

DEFAULT CHARACTER SET = utf8

COLLATE = utf8_unicode_ci;

INSERT INTO `student` (`name`, `address`, `createdate`) VALUES ('홍길동', '연습', '21-3-4');

* Servlet 구성

- /SampleServlet

- /StudentServlet

(중략)

      <!-- https://mvnrepository.com/artifact/org.hibernate/hibernate-entitymanager -->

<dependency>

    <groupId>org.hibernate</groupId>

    <artifactId>hibernate-entitymanager</artifactId>

    <version>5.4.21.Final</version>

</dependency>

    <!-- @Inject -->

    <dependency>

        <groupId>javax.inject</groupId>

        <artifactId>javax.inject</artifactId>

        <version>1</version>

    </dependency>

    <!-- https://mvnrepository.com/artifact/javax.servlet/javax.servlet-api -->

<dependency>

    <groupId>javax.servlet</groupId>

    <artifactId>javax.servlet-api</artifactId>

    <version>4.0.1</version>

    <scope>provided</scope>

</dependency>

    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->

<dependency>

    <groupId>mysql</groupId>

    <artifactId>mysql-connector-java</artifactId>

    <version>8.0.21</version>

</dependency>

(중략)

- Hibernate XML Mapping file 기능은 생성이 되지 않는다.

  (꼭 반드시 XML Mapping으로 hibernate resources를 잡을 필요는 없으며, Class Mapping도 사용된다.)

* hibernate.connection.driver_class = com.mysql.jdbc.Driver

* hibernate.connection.url = jdbc:mysql://localhost:3306/web?serverTimezone=UTC&characterEncoding=utf8

* hibernate.dialect = org.hibernate.dialect.MySQL8Dialect

* hibernate.show_sql = true

* hibernate.hbm2ddl.auto = update

* hibernate.default_entity_mode = pojo

* hibernate.current_session_context_class = thread

문제1. [MySQL 초기 설치 후 작업했을 때, 타임존 문제]

타임존 미설정시

* ERROR: The server time zone value

해결 방법1: serverTime=UTC를 입력해준다.

해결 방법2: 서버 my.cnf 파일에서 timezone을 지정해준다.

* my.cnf 파일 내에 환경 설정을 변경해주는 방법이 있다.
[mysqld]

default-time-zone='+9:00'

문제2. [UTF-8 환경설정 문제]

해결 방법1: characterEncoding=utf8을 입력해준다.

해결 방법2: 서버 my.cnf 파일에서 timezone을 지정해준다.

* my.cnf 파일 내에 환경 설정을 변경해주는 방법이 있다.

[mysqld]

init_connect = SET collation_connection = utf8_general_ci

init_connect = SET NAMES utf8

character-set-server = utf8

collation-server = utf8_general_ci

폴더: src/main/resources

공식 메뉴얼: https://docs.jboss.org/hibernate/orm/5.4/quickstart/html_single

[Spring-Framework] 7. Maven, Hibernate 5.4, Servlet, MySQL 8 연동(CRUD) - (2)

https://yyman.tistory.com/1405

* 미리 준비해야 할 것: Oracle Database 설치되어 있어야 함.

-> 오라클의 경우에는 오라클 데이터베이스가 설치되어야만, pom.xml에서 <dependency>로 불러올 수가 있음.

[글의 주제]

1. 흥미로운 실험 - Spring Framework가 없는 Maven MVC 웹 프로젝트로 Servlet(서블릿) 구성해보기

2. Oracle Database 연결해보기

[사용 환경]

1. Oracle Databases 19g (2020-09월 기준: 최신버전)

2. OpenJDK (2020-09월 기준: 최신버전)

3. Apache Tomcat 9

4. SpringToolSuite4 (2020-09월 기준: 최신버전)

5. MS Windows 10

파일경로: src/main/webapp/WEB-INF/web.xml

* 이전 게시글(동일하거나 참고하면 되는 게시글):

1. [JSP] 영속프레임워크 MyBatis를 활용한 CRUD 구현 - JSP와 Oracle, https://yyman.tistory.com/1390?category=810693, 2020-09-19 01:31

-> 비교해서 읽어 보기: 현재 게시글에서는 프레임워크 없이 JDBC 처리에 대해서 소개하고 있음.