KOSA FullStack 교육/java
KOSA fullstack 교육(ConnectionPooling)
로미로미로
2025. 7. 2. 17:08
ConnectionPooling
connection을 반환받아올 때마다 가장 많은 리소스를 사용한다.
이를 connectionpooling 방법으로 바꾸어보자
프로젝트를 새로 파고 해당 경로에(META-INF) 아래에 다음과 같은 xml 파일을 만든다.
“XML 파일의 내용을 WAS에 등록”
- server.xml 또는 context.xml과 같은 XML 설정 파일에 데이터베이스 연결 정보를 정의하면, 이 정보가 WAS(Web Application Server), 즉 Tomcat 같은 컨테이너에 의해 읽혀지고, JNDI 리소스로 등록됨
- 즉, 서버가 시작될 때 “jdbc/mysql”이라는 이름으로 객체가 등록되어, 나중에 Java 코드에서 이 이름을 통해 DB 연결 자원을 꺼내 쓸 수 있게 되는 것
<%@page import="java.sql.Connection"%>
<%@page import="javax.sql.DataSource"%>
<%@page import="javax.naming.InitialContext"%>
<%@page import="javax.naming.Context"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h2>ConnectionPool Test Using JDNI(Naming) Service</h2>
<%
Context ic = new InitialContext(); //자식
DataSource ds = (DataSource)ic.lookup("java:comp/env/jdbc/mysql"); //해당 이름의 제품을 찾아옴
out.println("<b>1. DataSource Lookup....</b><br>");
Connection conn = ds.getConnection();
out.println("<b>2. A Connection Rent Success....</b>");
%>
</body>
</html>
이제 DAO구현 클래스에서도 해당 방식으로 바꾸어보자
private ProductDAOImpl() {
try {
//JNDI 서비스 코드로 수정..
Context ic = new InitialContext(); //자식
ds = (DataSource)ic.lookup("java:comp/env/jdbc/mysql"); //해당 이름의 제품을 찾아옴
System.out.println("DataSource Lookup.. sucess");
}catch(Exception e) {
System.out.println(e.getMessage());
}
}
private Connection getConnect() throws SQLException{
//Connection conn = DriverManager.getConnection(ServerInfo.URL, ServerInfo.USER, ServerInfo.PASS);
Connection conn = ds.getConnection();
System.out.println("디비연결 성공");
return conn;
}
이제 워크샵을 해보자.
작업 순서
1. 테이블 작성
2. VO 작성
3. DAO Template
4. DAOImpl
5. 단위테스트 (비지니스 로직 단위)
1. 테이블 작성
DROP TABLE USERINFO;
CREATE TABLE USERINFO (
userId varchar(12) primary key,
password varchar(12) NOT NULL,
name varchar(20) NOT NULL,
email varchar(50));
INSERT INTO USERINFO VALUES('admin', 'admin', '관리자', 'admin@jaen.com');
INSERT INTO USERINFO VALUES('java', 'java', '자바지기', 'java@jaen.com');
INSERT INTO USERINFO VALUES('kosa', '1234', '연수생', 'hsh@kosta.com');
commit;
DROP TABLE book;
create table book(
isbn char(12) primary key,
title varchar(50) not null,
catalogue varchar(30) not null,
nation varchar(30),
publish_date date,
publisher varchar(50),
author varchar(50) not null,
price decimal(6,0) not null,
currency char(6),
description varchar(2000));
INSERT INTO book VALUES('1233-111-111','Java 완성','프로그래밍','국내도서',
'2019.01.01','사무국','사무국',3000,'원','java를 마시자');
INSERT INTO book VALUES('1233-111-222','금융권의 대용량 데이타분석','프로그래밍','국외도서',
'2023.01.02','사무국','사무국',4000,'원','대용량 데이타');
INSERT INTO book VALUES('1233-111-333','AI정복','프로그래밍','국내도서',
'2025.03.22','사무국','사무국',6000,'원','AI와 동행');
commit;
select * from userinfo;
select * from book;
2. VO 작성(로그인 구현)
package servlet.model;
public class User {
private String userId;
private String password;
private String name;
private String email;
public User(String userId, String password, String name, String email) {
super();
this.userId = userId;
this.password = password;
this.name = name;
this.email = email;
}
public String getUserId() {
return userId;
}
public void setUserId(String userId) {
this.userId = userId;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
@Override
public String toString() {
return "User [userId=" + userId + ", password=" + password + ", name=" + name + ", email=" + email + "]";
}
}
3. DAO Template
package servlet.model;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public interface UserDAO {
//공통 로직
public Connection getConnection() throws SQLException;
public void close(Connection conn, PreparedStatement ps) throws SQLException;
public void close(Connection conn, PreparedStatement ps, ResultSet rs) throws SQLException;
//비지니스 로직
public User login(String userId, String password) throws SQLException;
}
4. DAOImpl
package servlet.model;
/*
1. 싱글톤
2. 생성자에서 JNDI서비스로 DataSource받아옴
3. 공통로직 구현
4. login 구현, 단위테스트
*/
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
public class UserDAOImpl implements UserDAO{
private static UserDAOImpl daoImpl = new UserDAOImpl();
private DataSource ds = null;
private UserDAOImpl() {
try {
Context ic = new InitialContext();
ds = (DataSource)ic.lookup("java:comp/env/jdbc/mysql");
System.out.println("DataSource Lookup.. sucess");
}catch(Exception e) {
System.out.println(e.getMessage());
}
}
public static UserDAOImpl getInstanse() {
return daoImpl;
}
@Override
public Connection getConnection() throws SQLException {
return ds.getConnection();
}
@Override
public void close(Connection conn, PreparedStatement ps) throws SQLException {
}
@Override
public void close(Connection conn, PreparedStatement ps, ResultSet rs) throws SQLException {
}
@Override
public User login(String userId, String password) throws SQLException {
String sqlString = "SELECT * FROM userInfo where id = ?";
User user = null;
try(Connection conn = getConnection(); PreparedStatement ps = conn.prepareStatement(sqlString);){
ps.setString(1, userId);
String sqlPW = null;
try(ResultSet rs=ps.executeQuery()){
if(rs.next()) {
sqlPW = rs.getString("password");
if(sqlPW.equals(password)) {
System.out.println("로그인 성공");
user = new User(rs.getString("userId"), sqlPW, rs.getString("name"), rs.getString("email"));
}
}
}
if(user == null) return null;
else return user;
}
}
public static void main(String[] args) {
}
}
5. 단위테스트는 DataSource를 사용했기 때문에 메인에서 테스트 못한다. 생략
controller연결
package web.servlet.controller;
import java.io.IOException;
import java.io.PrintWriter;
import java.io.Writer;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.tomcat.util.descriptor.web.LoginConfig;
import servlet.model.User;
import servlet.model.UserDAOImpl;
@WebServlet("/MainServlet")
public class MainServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
public MainServlet() {
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doProcess(request, response);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doProcess(request, response);
}
protected void doProcess(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String id = request.getParameter("id");
String password = request.getParameter("password");
String path = "./error/error.html";
try {
User user = UserDAOImpl.getInstanse().login(id, password);
if(user != null) {
request.setAttribute("user", user);
path = "loginSuccess.jsp";
request.getRequestDispatcher(path).forward(request, response);
}else {
response.sendRedirect(path);
}
} catch (SQLException e) {
}
}
}