티스토리 뷰

☠️ Java

JSP & Oracle 연동-(컨넥션 풀, Connection Pool) 방식

James Wetzel 2024. 8. 24. 16:13
728x90
반응형

1. ojdbc.jar(Oracle Java DataBase Connectivity) 드라이버 추가

ojdbc11.jar 추가

 

 

2. Tomcat.context.xml(컨넥션 풀, Connection Pool) 설정

Resource 추가

 

 

3. MemberVO.java

package jspMemberSearch.ex1;

public class MemberVO {
	private String name;
	private String id;
	
	public MemberVO() {}
	
	public String getName() {
		return this.name;
	}
	
	public void setName(String name) {
		this.name = name;
	}
	
	public String getId() {
		return this.id;
	}
	
	public void setId(String id) {
		this.id = id;
	}

}

 

4. MemberDAO.java

package jspMemberSearch.ex1;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;


public class MemberDAO {
	private DataSource dataFactory;
	private Connection connection;
	private PreparedStatement preparedStatement;
	
	public MemberDAO() {
		try {
			// JNDI(Java Naming and Directory Interface) 선언
 			Context context = new InitialContext();
 			Context envContext = (Context)context.lookup("java:/comp/env");
 			this.dataFactory = (DataSource)envContext.lookup("jdbc/myDataSource");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public List<MemberVO> memberList(String name) {
		List<MemberVO> memberList = new ArrayList<MemberVO>();
		
		try {
			this.connection = this.dataFactory.getConnection();
			String query = "select * from t_member";
			
			if(name.length() != 0) {
				query += " where name = ?";
				this.preparedStatement = this.connection.prepareStatement(query);
				this.preparedStatement.setString(1, name);
			} else {
				this.preparedStatement = this.connection.prepareStatement(query);
			}
			
			ResultSet resultSet = this.preparedStatement.executeQuery();
			while (resultSet.next()) {
				MemberVO mVO = new MemberVO();
				mVO.setName(resultSet.getString("name"));
				mVO.setId(resultSet.getString("id"));
				
				memberList.add(mVO);
			}
			
			resultSet.close();
			this.preparedStatement.close();
			this.connection.close();
		} catch (Exception ex) { ex.printStackTrace(); }
		
		return memberList;
	}
    
    public void addMember(MemberVO memberVO) {
		try {
			this.connection = this.dataFactory.getConnection();
			
			StringBuilder query = new StringBuilder();
			query.append("insert into t_member");
			query.append(" (id, pwd, name, email)");
			query.append(" values(?, ?, ?, ?)");
			
			this.preparedStatement = this.connection.prepareStatement(query.toString());
			this.preparedStatement.setString(1, memberVO.getId());
			this.preparedStatement.setString(2, "new pwd");
			this.preparedStatement.setString(3, memberVO.getName());
			this.preparedStatement.setString(4, "new email");
			
			this.preparedStatement.executeUpdate();
			
			this.preparedStatement.close();
			this.connection.close();
		} catch(Exception ex) {
			ex.printStackTrace();
		}
	}

}

 

5. memberList.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"
    import="java.util.*"
    import="jspMemberSearch.ex1.*"%>
    
<%
	MemberDAO memberDAO = new MemberDAO();
	List<MemberVO> memberList = memberDAO.memberList("");
%>
    
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<table>
		<tr>
			<th>Name</th>
			<th>Id</th>
		</tr>
		
		<%
			for(MemberVO memberVO: memberList) {
		%>
		<tr>
			<td><%= memberVO.getName() %></td>
			<td><%= memberVO.getId() %></td>
		</tr>
		<%		
			}
		%>
	</table>

	<Br>
	
	<table>
		<tr>
			<th>Name</th>
			<th>Id</th>
		</tr>
		
		<%
			for(int i=0; i<memberList.size(); i++) {
				MemberVO memberVO = (MemberVO)memberList.get(i);
		%>
		<tr>
			<td><%= memberVO.getName() %></td>
			<td><%= memberVO.getId() %></td>
		</tr>
		<%		
			}
		%>
	</table>
</body>
</html>
728x90
반응형
저작자표시