도도의 초록누리

IDE: Spring-Tool Suite 4-4.7.2 RELEASES

Databases: Oracle Databases 19g

 - Maven 3.6.3/1.16.0.20200610-1735

 - javax.servlet-api (4.0.1) - servlet(서블렛)

 - jstl (1.2) - jstl 태그 적용

 - taglibs (1.1.2) - c태그 적용

 - HikariCP (3.4.2) - 커넥션 풀

 - com.oracle.database.jdbc - ojdbc8 (19.7.0.0)

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

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">

  <modelVersion>4.0.0</modelVersion>

  <groupId>com.boardMaven</groupId>

  <artifactId>web</artifactId>

  <version>0.0.1-SNAPSHOT</version>

  <packaging>war</packaging>

  <name>board Maven Webapp</name>

  <!-- FIXME change it to the project's website -->

  <url>http://www.example.com</url>

  <properties>

    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>

    <maven.compiler.source>1.7</maven.compiler.source>

    <maven.compiler.target>1.7</maven.compiler.target>

  </properties>

  <dependencies>

    <dependency>

      <groupId>junit</groupId>

      <artifactId>junit</artifactId>

      <version>4.11</version>

      <scope>test</scope>

    </dependency>

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

<!-- JSTL -->

<dependency>

<groupId>javax.servlet</groupId>

<artifactId>jstl</artifactId>

<version>1.2</version>

</dependency>

<!-- taglibs -->

<dependency>

<groupId>taglibs</groupId>

<artifactId>standard</artifactId>

<version>1.1.2</version>

<scope>compile</scope>

</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.7.0.0</version>

</dependency>

  </dependencies>

  <build>

    <finalName>board</finalName>

    <pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) -->

      <plugins>

        <plugin>

          <artifactId>maven-clean-plugin</artifactId>

          <version>3.1.0</version>

        </plugin>

        <!-- see http://maven.apache.org/ref/current/maven-core/default-bindings.html#Plugin_bindings_for_war_packaging -->

        <plugin>

          <artifactId>maven-resources-plugin</artifactId>

          <version>3.0.2</version>

        </plugin>

        <plugin>

          <artifactId>maven-compiler-plugin</artifactId>

          <version>3.8.0</version>

        </plugin>

        <plugin>

          <artifactId>maven-surefire-plugin</artifactId>

          <version>2.22.1</version>

        </plugin>

        <plugin>

          <artifactId>maven-war-plugin</artifactId>

          <version>3.2.2</version>

        </plugin>

        <plugin>

          <artifactId>maven-install-plugin</artifactId>

          <version>2.5.2</version>

        </plugin>

        <plugin>

          <artifactId>maven-deploy-plugin</artifactId>

          <version>2.8.2</version>

        </plugin>

      </plugins>

    </pluginManagement>

  </build>

</project>

폴더명: Resource

파일명: db.properties

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

dataSourceClassName=oracle.jdbc.driver.OracleDriver

dataSource.user=userName

dataSource.password=password

cachePrepStmts=true

prepStmtCacheSize=250

prepStmtCacheSqlLimit=2048

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

xmlns="http://java.sun.com/xml/ns/javaee" 

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

id="WebApp_ID" version="3.0">

  <display-name>Archetype Created Web Application</display-name>

  <servlet>

  <servlet-name>FrontController</servlet-name>

  <servlet-class>com.smile.web.controller.FrontController</servlet-class>

  <init-param>

<param-name>charset</param-name>

<param-value>UTF-8</param-value>

</init-param>

  </servlet>

  <servlet-mapping>

  <servlet-name>FrontController</servlet-name>

  <url-pattern>*.do</url-pattern>

  </servlet-mapping>

</web-app>

폴더1: /src/main/webapp/WEB-INF/settings

폴더2: /src/main/webapp/WEB-INF/views

파일: /src/main/webapp/WEB-INF/settings/root-servlet.xml

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

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

파일명: FrontController.java

package com.smile.web.controller;

import java.io.IOException;

import java.sql.SQLException;

import javax.servlet.ServletConfig;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import com.smile.web.db.DBFactory;

public class FrontController extends HttpServlet {

private static final long serialVersionUID = 1L;

private String charset = null;

/**

* @see HttpServlet#doGet(HttpServletRequest req, HttpServletResponse res)

*/

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

doAction(req, res);

}

/**

* @see HttpServlet#doPost(HttpServletRequest req, HttpServletResponse res)

*/

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

doAction(req, res);

}

// FrontController 패턴 & Command 패턴

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

ServletConfig sc = this.getServletConfig();

charset = sc.getInitParameter("charset");

req.setAttribute("charset", charset);

req.setCharacterEncoding(charset);

res.setContentType("text/html; charset=" + charset);

String uri = req.getRequestURI();

String conPath = req.getContextPath();

String command = uri.substring(conPath.length());

Controller subController = null;

if(command.equals("/board/list.do")){

System.out.println("-----------------------------");

System.out.println("게시판 목록");

System.out.println("-----------------------------");

req.setAttribute("controllerName", "list");

subController = new com.smile.web.controller.board.ListController();

subController.execute(req, res);

}

}

}

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

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

파일명: Controller.java

package com.smile.web.controller;

import java.io.IOException;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

public interface Controller {

public void execute(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException;

}

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

패키지명: com.smile.web.controller.board

파일명: ListController.java

package com.smile.web.controller.board;

import java.io.IOException;

import java.net.URLEncoder;

import java.util.List;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import com.smile.web.controller.Controller;

import com.smile.web.logic.Paging;

import com.smile.web.model.Board;

import com.smile.web.service.BoardService;

import com.smile.web.util.HttpUtil;

public class ListController implements Controller {

@Override

public void execute(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException {

String controllerName = (String) req.getAttribute("controllerName");

BoardService service = BoardService.getInstance();

List<Board> boardList = null;

// long totalCount = boardList.size();

long currentPage = 1; // 기본값

long pageSize = 10;

long totalCount = service.getTotalCount();

long startNum, endNum;

String keyword = null;

String pagingUrl = controllerName + ".do?";

// 페이지 번호 존재할 때

if (req.getParameter("page") != null){

currentPage = Integer.valueOf( req.getParameter("page") );

}

// 키워드가 존재할 때

if (req.getParameter("keyword") != null) {

keyword = req.getParameter("keyword");

totalCount = service.getTotalKeywordCount(keyword);

// 키워드 값이 하나라도 존재할 때

if ( keyword.length() > 0) {

pagingUrl = pagingUrl + "keyword=" + URLEncoder.encode(keyword, "UTF-8") + "&";

}

}

        Paging paging = new Paging();

        /*

        paging.setPageNo(1);

        paging.setPageSize(10);

        paging.setTotalCount(totalCount);

        */

        paging.setPageNo(currentPage);

        paging.setPageSize(pageSize);

        paging.setTotalCount(totalCount);

        System.out.println("현재페이지번호:" + currentPage);

        System.out.println("페이지크기:" + pageSize);

        System.out.println("키워드:" + keyword);

        System.out.println("페이징URL:" + pagingUrl);

        startNum = paging.getDbStartNum();

        endNum = paging.getDbEndNum();

// 키워드가 존재할 때

if (keyword != null) {

// 키워드 값이 하나라도 존재할 때

if ( keyword.length() > 0) {

boardList = service.getBoardKeywordList(keyword, startNum, endNum);

}

else {

boardList = service.getBoardList(startNum, endNum);

}

}

else {

        boardList = service.getBoardList(startNum, endNum);

}

        req.setAttribute("paging", paging);

        req.setAttribute("list", boardList);

        req.setAttribute("pagingUrl", pagingUrl);

HttpUtil.forward(req, res, "/WEB-INF/views/board/" + controllerName + ".jsp");

}

}

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

패키지명: com.smile.web.controller.util

파일명: HttpUtil.java

package com.smile.web.util;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.io.InputStream;

import java.io.OutputStream;

import java.io.PrintWriter;

import javax.servlet.RequestDispatcher;

import javax.servlet.ServletConfig;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

public class HttpUtil extends HttpServlet {

private static final long serialVersionUID = 1L;

private static String charset = null;

public static void forward(HttpServletRequest req, HttpServletResponse res,

String path) throws ServletException, IOException {

try {

RequestDispatcher dispatcher = req.getRequestDispatcher(path);

dispatcher.forward(req, res);

}catch(Exception e) {

e.printStackTrace();

}

}

public static void fileUpload(HttpServletRequest req, HttpServletResponse res,

String path) throws ServletException, IOException {

charset = (String) req.getAttribute("charset");

System.out.println(charset);

PrintWriter out = res.getWriter();

// 파일 업로드된 경로

String root = req.getSession().getServletContext().getRealPath("/");

String savePath = root + "upload" + File.separator + "upload";

// 서버에 실제 저장된 파일명

String filename = "1600955663095" ;

System.out.println("파일 실제 폴더경로:" + savePath);

// 실제 내보낼 파일명

String orgfilename = "license한글.txt" ;

req.setCharacterEncoding(charset);

res.setCharacterEncoding(charset);

InputStream in = null;

OutputStream os = null;

File file = null;

boolean skip = false;

String client = "";

try{

    // 파일을 읽어 스트림에 담기

    try{

        file = new File(savePath, filename);

        in = new FileInputStream(file);

    }catch(FileNotFoundException fe){

        skip = true;

    }

    client = req.getHeader("User-Agent");

    // 파일 다운로드 헤더 지정

    res.reset() ;

    res.setContentType("application/octet-stream");

    res.setHeader("Content-Description", "JSP Generated Data");

    if(!skip){

        // IE

        if(client.indexOf("MSIE") != -1){

            res.setHeader ("Content-Disposition", "attachment; filename="+new String(orgfilename.getBytes("KSC5601"),"ISO8859_1"));

        }else{

            // 한글 파일명 처리

            orgfilename = new String(orgfilename.getBytes("KSC5601"),"iso-8859-1");

            res.setHeader("Content-Disposition", "attachment; filename=\"" + orgfilename + "\"");

            res.setHeader("Content-Type", "application/octet-stream; charset=utf-8");

        }  

        res.setHeader ("Content-Length", ""+file.length() );

        os = res.getOutputStream();

        byte b[] = new byte[(int)file.length()];

        int leng = 0;

        while( (leng = in.read(b)) > 0 ){

            os.write(b,0,leng);

        }

    }else{

    // 한글 깨짐 - 해결

    res.setContentType("text/html;charset=" + charset);

        out.println("<html><head>");

        out.println("<script language='javascript'>alert('파일을 찾을 수 없습니다.');history.back();</script>");

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

    }

    in.close();

    os.close();

}catch(Exception e){

e.printStackTrace();

}

}

}

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

패키지명: com.smile.web.controller.db

파일명: DBFactory.java

package com.smile.web.db;

import java.io.IOException;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.Properties;

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 com.zaxxer.hikari.HikariConfig;

import com.zaxxer.hikari.HikariDataSource;

import oracle.jdbc.pool.OracleDataSource;

public class DBFactory {

private static final Logger logger = LoggerFactory.getLogger(DBFactory.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 HikariConfig config;

    public DBFactory() {

    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("jdbcurl:" + properties.getProperty("jdbcUrl"));

            System.out.println("className" + 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 {

    /*

        try(Connection con = ds.getConnection()){

            System.out.println("연결상태확인:" + con);

            String sql = "SELECT * from board where id > 4 and id < 10";

            PreparedStatement pstmt = con.prepareStatement(sql);

            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {

                System.out.println("진짜 연결되었는가:" + rs.getString(2));

            }

        }

        catch(Exception e) {

        System.out.println("연결실패확인:" + e.getMessage());

        }

    */

        return ds.getConnection();

    }

    public void close(Connection conn, PreparedStatement ps, ResultSet rs) {

if ( rs != null ) {

try {

rs.close();

}

catch(Exception ex) {

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

}

close(conn, ps); // Recursive 구조 응용(재귀 함수)

} // end of if

}

public void close(Connection conn, PreparedStatement ps) {

if (ps != null ) {

try {

ps.close();

}

catch(Exception ex) {

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

}

} // end of if

if (conn != null ) {

try {

conn.close();

}

catch(Exception ex) {

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

}

} // end of if

}

}

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

https://yyman.tistory.com/1429

[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

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

* 웹 서버: 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...로 변경

운영체제: 리눅스 우분투(Ubuntu-20 Desktop 64bit)

[명령어 요약]

user@localhost :sudo -i

root#apt update

(처음 우분투 설치하면, 업데이트 하라고 GUI화면에 올라옴. 재부팅을 몇 번 해보고 나서 작업하는 것을 권장함.)

root#apt install tomcat9

root#service tomcat9 start

(war 파일 경로 찾기)

#mv {파일명}.war /var/lib/tomcat9/webapps

(수동 컴파일 버전은 수동 설치 경로로 할 것)

(잠시 기다리고 나서 1분 이내)

* 파이어폭스 열어서 접속할 것

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