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) {
		}
		
		
	}


}