[Spring-Framework] 11. Spring MVC - HikariCP 3.4.2, Oracle 19g 연동하기(Properties, Java) - (2)
1부에서는 환경설정하는 방법에 대해서 소개하였다.
2부에서는 코드로 구현하는 방법을 위주로 소개하겠다.
[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
10. Properties 방식 - DataSource.java
코드를 하나 만들어보았다.
패키지: 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.java
[첨부(Attachments)]
DataSource-java-type-properties.zip
위의 소스를 static 방식으로 간결하게 코드를 변경할 수 없는 이유는 "getClass().getClassLoader().getResourceAsStream(resource);" 이 부분이 static 변수에서는 처리가 되지 않는다.
비고: 클래스명에 대해서 "DataSource"로 해도 되나요?
javax.sql.DataSource가 존재한다. 예제의 DataSource 클래스명은 좋은 명칭은 아니다.
(동작은 될 수 있겠으나 javax.sql.DataSource를 사용할 때는 명칭에 대해서 다시 생각을 해봐야 할 것이다.)
10-1. Controller - HomeController.java 수정하기(Properties 방식)
HomeController.java 파일로 작업하는 방법이다.
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";
}
}
파일명: HomeController.java
[첨부(Attachments)]
HomeController-java-type-properties.zip
10-2. DAO - AddressBookDao.java (Properties 방식)
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());
}
}
}
파일명: AddressBookDao.java
[첨부(Attachments)]
AddressBookDao-type-properties.zip
11. Java 방식 - DataSource.java
앞에 Properties 방식으로 구성해봐도 되고, 자바 코드 방식으로 해도 무방하다.
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();
}
}
파일명: DataSource.java
[첨부(Attachments)]
DataSource-java-type-java.zip
11-1. Controller - HomeController.java 수정하기(Java 방식)
HomeController.java 파일로 작업하는 방법이다.
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";
}
}
파일명: HomeController.java
[첨부(Attachments)]
HomeController-java-type-java.zip
11-2. DAO - AddressBookDao.java (JAVA 방식)
자바 방식으로 dao를 구성하는 방법이다.
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());
}
}
}
파일명: AddressBookDao.java
[첨부(Attachments)]
AddressBookDao.java-Type-java.zip
12. 출력 결과
작업한 데이터베이스의 모습이다.
작업한 결과를 보면, 무슨 작업을 하려고 했는지 직관적으로 이해될 것으로 보인다.
DB를 처리하거나 호출하는 건 똑같은데, 호출할 때 미리 저장된 풀을 활용하여 부담을 줄여주는 효과가 있다.
그림 11. Oracle DB - SQL Developer 모습(Addressbook)
그림 12. Oracle DB - SQL Developer 모습(Addressbook)
그림 13. 화면 출력 결과 - STS4(Eclipse)
* 맺음글(Conclusion)
시중의 HikariCP에 대한 글이 오류가 많아서 한번 정확하게 최신 버전으로 작성해보고 싶었다.
수차례 태스트 작업을 완료하였으니 사용하는 데 지장없을 것으로 보인다.
공식 사이트: https://github.com/brettwooldridge/HikariCP
-> 공식 사이트의 메뉴얼이 다소 부족한 면이 있다.
* 참고자료(Reference)
1. [Spring] 커넥션 풀(Connection pool)이란?, https://linked2ev.github.io/spring/2019/08/14/Spring-3-%EC%BB%A4%EB%84%A5%EC%85%98-%ED%92%80%EC%9D%B4%EB%9E%80/ , Accessed by 2020-09-23, Last Modified 2019-08-14.
2. Spring-Hikari-Oracle연동 시 나는 oracle.jdbc.driver.OracleDriver not found 에러, https://apiclass.tistory.com/entry/Spring-Hikari-Oracle%EC%97%B0%EB%8F%99-%EC%8B%9C-%EB%82%98%EB%8A%94-oraclejdbcdriverOracleDriver-not-found-%EC%97%90%EB%9F%AC, Accessed by 2020-09-23, Last Modified 2019-10-16.
3. Introduction to HikariCP, https://www.baeldung.com/hikaricp, Accessed by 2020-09-23, Last Modified 2020-06-02.
4. Maven Repository: mysql » mysql-connector-java » 8.0.21, https://mvnrepository.com/artifact/mysql/mysql-connector-java/8.0.21, Accessed by 2020-09-23, Last Modified .
5. GitHub - brettwooldridge/HikariCP: 光 HikariCP・A solid, high-performance, JDBC connection pool at last., https://github.com/brettwooldridge/HikariCP, Accessed by 2020-09-23, Last Modified .
6. [Spring][04]커넥션 풀 설정(HikariCP), https://kimvampa.tistory.com/57, Accessed by 2020-09-23, Last Modified 2020-04-10.
7. [Spring] Spring Test 오류 해결 모음(SpringJUnit4ClassRunner , Resource specification), https://life-with-coding.tistory.com/373, Accessed by 2020-09-23, Last Modified 2020-07-08.
8. [오류, 에러] Spring Junit Test 환경설정 / Class<SpringJUnit4ClassRunner> cannot be resolved to a type, https://developerntraveler.tistory.com/1, Accessed by 2020-09-23, Last Modified 2020-05-14.
9. 오라클 드라이버 에러 - Registered driver with driverClassName=oracle.jdbc.driver.OracleDriver was not found, trying direct instantiation, https://wrkbr.tistory.com/410, Accessed by 2020-09-23, Last Modified 2018.
10. SpringBoot error: Registered driver with driverClassName=oracle.jdbc.driver.OracleDriver was not found, trying direct instantiation, https://stackoverflow.com/questions/52014595/springboot-error-registered-driver-with-driverclassname-oracle-jdbc-driver-orac, Accessed by 2020-09-23, Last Modified 2018.
11. Spring에서 Properties 사용, https://yookeun.github.io/java/2015/12/22/spring-properties/, Accessed by 2020-09-23, Last Modified 2015-12-22.
12. Java에서 Properties 파일 읽기, https://prettymucho.tistory.com/7, Accessed by 2020-09-23, Last Modified 2017-06-12.