디지털 컨버전스/Spring

[Spring Framework] ojdbc6, DBCP 추가 방법

gimyeondong 2020. 5. 29. 14:15

forward , redirect 차이

 

동적 타입 바인딩

(디폴트생성자 필요!)

넘어온 값을 데이터베이스에 저장

 

 

 

MessagesDAO 클래스 만들기

동시접속자가 다량으로 접속했을 때를 대비한 DBCP 적용

DataBase Connection Pool

커넥션들을 가둬놓은 울타리

순간 접속자가 대량으로 발생할 때, 미리 생성해 놓은 Connection 객체를 대여하는 방식으로 접속자를 제한하는 기술

1. 커넥션 객체를 미리 몇십개 정도 pool에 저장

2. 사용자가 오면 커넥션을 빌려줌

3. 커넥션 개수 이상의 사용자는 대기 시킴

4. 먼저 사용한 사용자가 반환하면 순서대로 배분

5. 사용자는 대기해야 하지만 서버가 터지니는 않음

 

 

ojdbc6 추가 필요

 

porm.xml

 

메이븐 사이트의 링크가 끊겨있는 경우 있음

https://mvnrepository.com/artifact/com.oracle/ojdbc6/12.2.0.1

 

Maven Repository: com.oracle » ojdbc6 » 12.2.0.1

com.oracle ojdbc6 12.2.0.1 // https://mvnrepository.com/artifact/com.oracle/ojdbc6 compile group: 'com.oracle', name: 'ojdbc6', version: '12.2.0.1' // https://mvnrepository.com/artifact/com.oracle/ojdbc6 libraryDependencies += "com.oracle" % "ojdbc6" % "12

mvnrepository.com

 

 

다운로드 받는 사이트 목록을 추가

	<repositories>
		<repository>
			<id>OJDBC6 Repo</id>
			<url>http://maven.icm.edu.pl/artifactory/repo/</url>
		</repository>
	</repositories>

 

그래도 오류!

저장소 문제

 

직접 쓰기

	<repositories>
		<repository>
			<id>OJDBC6 Repo</id>
			<url>http://www.datanucleus.org/downloads/maven2/</url>
		</repository>
	</repositories>
		<!-- DB Related -->
		<dependency>
			<groupId>oracle</groupId>
			<artifactId>ojdbc6</artifactId>
			<version>11.2.0.3</version>
		</dependency>

https://mvnrepository.com/artifact/org.apache.commons/commons-dbcp2/2.7.0

 

Maven Repository: org.apache.commons » commons-dbcp2 » 2.7.0

 

mvnrepository.com

DBCP

		<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-dbcp2 -->
		<dependency>
			<groupId>org.apache.commons</groupId>
			<artifactId>commons-dbcp2</artifactId>
			<version>2.7.0</version>
		</dependency>

 

 

 

DAO를 new 할 때마다 베이직 데이터 소스도 new 되어서 너무 많아짐 -> 싱글턴 패턴 적용

 

스프링에서 new를 하긴 해야 하는데...? : 스프링에게 만들자

 

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="dataSource" class="org.apache.commons.dbcp2.BasicDataSource">
			<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
			<property name="url" value="jdbc:oracle:thin:@localhost:1521:xe"/>
			<property name="username" value="kh"/>
			<property name="password" value="kh"/>
			<property name="initialSize" value="30"/>
		</bean>
</beans>

싱글턴으로 적용

setter 말고 property로 넣기

 

 

DBCP 쓸 준비 끝

 

DAO

package kh.spring.dao;

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

import org.apache.commons.dbcp2.BasicDataSource;
import org.springframework.beans.factory.annotation.Autowired;

public class MessagesDAO {
	
	
	@Autowired
	private BasicDataSource bds;

	private Connection getConnection() throws Exception {
		return bds.getConnection();
	}
}

 

Bean으로 만들어져 있는것 연결

@Autowired

디팬던시 인젝션

스프링이 가진 것들 중 BasicDataSource 자료형을 찾아서 연결

같은자료형이라 자동연결이 안될경우 아이디를 주고 퀄리파이어를 준다.

 

 

 

new는 유지보수에 불리

DAO 생성시마다 커넥션풀 까지 같이 생성되면 서버 터짐

 

 

 

DBCP를 쓰기 위한 인스턴스 : 커넥션

 

 

HomeController

package kh.spring.controller;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import kh.spring.dao.MessagesDAO;
import kh.spring.dto.MessagesDTO;

@Controller
public class HomeController {

	private MessagesDAO mdao;

	@RequestMapping("/")
	public String home() {
		return "home";
	}

	@RequestMapping("input")
	public String toInput() {
		return "messages/input";
		// WEB-INF/views/messages/input.jsp
	}

	@RequestMapping("inputProc")
	public String inputProc(MessagesDTO dto) {

		System.out.println(dto.getWriter()+" : "+dto.getMessage());
		return "redirect:/";
	}
}

 

MessagesDAO 아직 존재하지 않음

 

@Component로 스캔시 MessagesDAO 생성되도록 설정

 

DAO 역할을 하는 경우 @Component 보다는 @Repository 를 붙인다

 

@Repository 

@Component를 상속받아서 기능은 같다

당장은 큰 차이가 없지만 미래에 Repository에 업데이트가 발생할 경우를 대비해서

 

MessagesDAO

package kh.spring.dao;

import java.sql.Connection;

import org.apache.commons.dbcp2.BasicDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

@Repository
public class MessagesDAO {
	
	
	@Autowired
	private BasicDataSource bds;

	private Connection getConnection() throws Exception {
		return bds.getConnection();
	}
}

 

HomeController

package kh.spring.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import kh.spring.dao.MessagesDAO;
import kh.spring.dto.MessagesDTO;

@Controller
public class HomeController {

	@Autowired
	private MessagesDAO mdao;

	@RequestMapping("/")
	public String home() {
		return "home";
	}

	@RequestMapping("input")
	public String toInput() {
		return "messages/input";
		// WEB-INF/views/messages/input.jsp
	}

	@RequestMapping("inputProc")
	public String inputProc(MessagesDTO dto) {

		System.out.println(dto.getWriter()+" : "+dto.getMessage());
		return "redirect:/";
	}
}

 

단 컴포넌트스캔(component-scan)의 스캔 범위가 controller 패키지로 한정되어 있음

 

 

servlet-context.xml 에서 범위를 바꿔야한다

	<context:component-scan base-package="kh.spring" />

 

 

<?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="kh.spring" />
	
	
	
</beans:beans>

 

오타 locahost

더보기 
java.sql.SQLException: Cannot create PoolableConnectionFactory (IO 오류: The Network Adapter could not establish the connection)
	at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:669)
	at org.apache.commons.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:544)
	at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:753)
	at kh.spring.dao.MessagesDAO.getConnection(MessagesDAO.java:20)
	at kh.spring.dao.MessagesDAO.insert(MessagesDAO.java:25)
	at kh.spring.controller.HomeController.inputProc(HomeController.java:30)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190)
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138)
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:892)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:797)
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1039)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:942)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1005)
	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:908)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:660)
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:882)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:543)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81)
	at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:690)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:615)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:818)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1627)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLRecoverableException: IO 오류: The Network Adapter could not establish the connection
	at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:458)
	at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:546)
	at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:236)
	at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
	at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)
	at org.apache.commons.dbcp2.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:55)
	at org.apache.commons.dbcp2.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:355)
	at org.apache.commons.dbcp2.BasicDataSource.validateConnectionFactory(BasicDataSource.java:115)
	at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:665)
	... 44 more
Caused by: oracle.net.ns.NetException: The Network Adapter could not establish the connection
	at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:392)
	at oracle.net.resolver.AddrResolution.resolveAndExecute(AddrResolution.java:434)
	at oracle.net.ns.NSProtocol.establishConnection(NSProtocol.java:687)
	at oracle.net.ns.NSProtocol.connect(NSProtocol.java:247)
	at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1102)
	at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:320)
	... 52 more
Caused by: java.net.UnknownHostException: locahost
	at java.net.Inet6AddressImpl.lookupAllHostAddr(Native Method)
	at java.net.InetAddress$2.lookupAllHostAddr(Unknown Source)
	at java.net.InetAddress.getAddressesFromNameService(Unknown Source)
	at java.net.InetAddress.getAllByName0(Unknown Source)
	at java.net.InetAddress.getAllByName(Unknown Source)
	at java.net.InetAddress.getAllByName(Unknown Source)
	at oracle.net.nt.TcpNTAdapter.connect(TcpNTAdapter.java:117)
	at oracle.net.nt.ConnOption.connect(ConnOption.java:133)
	at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:370)
	... 57 more
package kh.spring.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;

import org.apache.commons.dbcp2.BasicDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import kh.spring.dto.MessagesDTO;

@Repository
public class MessagesDAO {
	
	
	@Autowired
	private BasicDataSource bds;

	private Connection getConnection() throws Exception {
		return bds.getConnection();
	}
	
	public int insert(MessagesDTO dto) throws Exception {
		String sql = "insert into messages values(messages_seq.nextval,?,?,sysdate)";
		try(Connection con = this.getConnection();
			PreparedStatement pstat = con.prepareStatement(sql);){
			pstat.setString(1, dto.getWriter());
			pstat.setString(2, dto.getMessage());
			int result = pstat.executeUpdate();
			con.commit();
			return result;
			
		}
	}
}

package kh.spring.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import kh.spring.dao.MessagesDAO;
import kh.spring.dto.MessagesDTO;

@Controller
public class HomeController {

	@Autowired
	private MessagesDAO mdao;

	@RequestMapping("/")
	public String home() {
		return "home";
	}

	@RequestMapping("input")
	public String toInput() {
		return "messages/input";
		// WEB-INF/views/messages/input.jsp
	}

	@RequestMapping("inputProc")
	public String inputProc(MessagesDTO dto) {
		try {
			int result = mdao.insert(dto);
		}catch(Exception e) {
			e.printStackTrace();
			return "error";
		}
		return "redirect:/";
	}
}