jsp 회원제 게시판(1) : DB, 의존성, 커넥션 풀

Mysql 데이터베이스 

create database board default character set utf8;

 

의존성 & 플러그인 : pom.xml

	<dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.48</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-dbcp2</artifactId>
            <version>2.4.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-pool2</artifactId>
            <version>2.5.0</version>
        </dependency>
        <dependency>
            <groupId>commons-logging</groupId>
            <artifactId>commons-logging</artifactId>
            <version>1.2</version>
        </dependency>
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>jstl</artifactId>
            <version>1.2</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.8.1</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>

 

/jdbc/DBCPinitListener : 리스너를 이용한 커넥션풀 생성 

package jdbc;

import org.apache.commons.dbcp2.*;
import org.apache.commons.pool2.impl.GenericObjectPool;
import org.apache.commons.pool2.impl.GenericObjectPoolConfig;

import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
import java.io.IOException;
import java.io.Reader;
import java.io.StringReader;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class DBCPinitListener implements ServletContextListener {

    @Override
    public void contextInitialized(ServletContextEvent sce) {
        String pooConfig = sce.getServletContext().getInitParameter("poolConfig");

        Properties properties = new Properties();

        try (Reader reader = new StringReader(pooConfig)){
            properties.load(reader);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        loadJdbcDriver(properties);
        initConnectionPoll(properties);

    }

    private void loadJdbcDriver(Properties properties) {
        String driverClass = properties.getProperty("jdbcDriver");
        try {
            Class.forName(driverClass);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException("fail to load JDBC Driver");
        }
    }

    private void initConnectionPoll(Properties properties)  {
        String jdbcUrl = properties.getProperty("jdbcUrl");
        String username = properties.getProperty("username");
        String password = properties.getProperty("password");
        String validationQuery = properties.getProperty("validationQuery");
        String poolName = properties.getProperty("poolName");

        int minIdle = getIntProperty(properties,"minIdle",5);
        int maxTotal = getIntProperty(properties,"maxTotal",50);

        ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(jdbcUrl,username,password);
        PoolableConnectionFactory poolableConnectionFactory =
                new PoolableConnectionFactory(connectionFactory,null);


        if (validationQuery!=null && !validationQuery.isEmpty()) {
            poolableConnectionFactory.setValidationQuery(validationQuery);
        }

        GenericObjectPoolConfig poolConfig = new GenericObjectPoolConfig();
        poolConfig.setTimeBetweenEvictionRunsMillis(1000L*60L*5L);
        poolConfig.setTestWhileIdle(true);
        poolConfig.setMinIdle(maxTotal);
        poolConfig.setMaxIdle(minIdle);

        GenericObjectPool<PoolableConnection> connectionPool =
                new GenericObjectPool<>(poolableConnectionFactory,poolConfig);
        poolableConnectionFactory.setPool(connectionPool);
        try {
            Class.forName("org.apache.commons.dbcp2.PoolingDriver");
            PoolingDriver poolingDriver = (PoolingDriver) DriverManager.getDriver("jdbc:apache:commons:dbcp:");
            poolingDriver.registerPool(poolName, connectionPool);
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }

    }

    private int getIntProperty(Properties properties, String propName, int defaultValue) {
        String value = properties.getProperty(propName);
        return Integer.parseInt(value);
    }

}

 

web.xml : 리스너 설정

    <listener>
        <listener-class>jdbc.DBCPinitListener</listener-class>
    </listener>

    <context-param>
        <param-name>poolConfig</param-name>
        <param-value>
            jdbcDriver = com.mysql.jdbc.Driver
            jdbcUrl = jdbc:mysql://localhost:3306/board?characterEncoding=UTF-8&amp;useSSL=false
            username = root
            password = 1234
            validationQuery = select 1
            minIdle = 3
            maxTotal = 30
            poolName = board
        </param-value>
    </context-param>

 

 

/jdbc/ConnectionProvider

package jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class ConnectionProvider {
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection("jdbc:apache:commons:dbcp:board");
    }
}

 

커넥션풀 테스트 

/test/dbconnTest.jsp

<%@ page import="java.sql.Connection" %>
<%@ page import="jdbc.ConnectionProvider" %>
<%@ page import="java.sql.SQLException" %>
<%@ page contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<!DOCTYPE html>
<html lang="ko">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Document</title>
</head>
<body>
<%
    try (Connection connection = ConnectionProvider.getConnection()){
        out.print("연결 성공");
    } catch (SQLException e){
        out.print("연결 실패" + e.getMessage());
    }
%>
</body>
</html>

 

/test/testMain.jsp

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ page contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<c:set var="contextPath" value="${ pageContext.request.contextPath }"/>
<!DOCTYPE html>
<html lang="ko">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Document</title>
</head>
<body>
    <h2>테스트 페이지</h2>
    <a href="${ contextPath }/test/dbconnTest.jsp">커넥션 풀 테스트</a> <br>
</body>
</html>

 

index.jsp

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ page contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<!DOCTYPE html>
<html lang="ko">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>INDEX</title>
</head>
<body>

    <a href="${ pageContext.request.contextPath }/test/testMain.jsp">테스트 페이지</a> <br>

</body>
</html>