DB연동하기

[ex]
server.xml
==========
<Resource auth="Container"
driverClassName="oracle.jdbc.driver.OracleDriver"
maxActive="20" maxIdle="10" maxWait="-1"
name="jdbc/myoracle" password="tiger" type="javax.sql.DataSource"
url="jdbc:oracle:thin:@127.0.0.1:1521:xe" username="scott"/>
</GlobalNamingResources>위소스를 추가한다




context.xml
===========
<ResourceLink global="jdbc/myoracle" name="jdbc/myoracle"
type="javax.sql.DataSource"/>
</Context>위소스를 추가한다




DataAccessException.java
========================
package kr.co.seoulit.common.dao;

@SuppressWarnings("serial")
public class DataAccessException extends RuntimeException {
public DataAccessException(){
super();
}
public DataAccessException(String message){
super(message);
}
public DataAccessException(Throwable cause){
super(cause);
}
public DataAccessException(String message, Throwable cause){
super(message, cause);
}
}


MemberDAO.java
==============
package kr.co.seoulit.member.dao;

import kr.co.seoulit.common.dao.DataAccessException;
import kr.co.seoulit.member.to.MemberBean;

public interface MemberDAO {
public List<MemberBean> selectMemberList() throws DataAccessException;
}


ServiceLocator.java
===================
package kr.co.seoulit.common.sl;

import java.util.Collections;
import java.util.HashMap;
import java.util.Map;

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


public class ServiceLocator {
private Map<String,DataSource> cache;
private Context envCtx;
private static ServiceLocator instance;

static {
try {
instance = new ServiceLocator();
} catch (ServiceLocatorException e) {
e.printStackTrace();
}
}

private ServiceLocator() {
try {
envCtx = new InitialContext();
cache = Collections.synchronizedMap(new HashMap<String,DataSource>());
} catch (Exception e) {
e.printStackTrace();
throw new ServiceLocatorException(e.getMessage());
}
}

public static ServiceLocator getInstance() {
return instance;
}

public DataSource getDataSource(String jndiName) throws ServiceLocatorException {
DataSource dataSource;
try {
if (cache.containsKey(jndiName)) {
dataSource=cache.get(jndiName);
} else {
dataSource = (DataSource)envCtx.lookup("java:comp/env/"+jndiName);
cache.put(jndiName, dataSource);
}
} catch (NamingException e) {
throw new ServiceLocatorException(e.getMessage());
}
return dataSource;
}
}

MemberBean.java
===============

package kr.co.seoulit.member.to;

public class MemberBean {
private String id, pw, addr, tel;

public void setId(String id){ this.id=id; }
public void setPw(String pw){ this.pw=pw; }
public void setAddr(String addr){ this.addr=addr; }
public void setTel(String tel){ this.tel=tel; }

public String getId(){ return id; }
public String getPw(){ return pw; }
public String getAddr(){ return addr; }
public String getTel(){ return tel; }
}


ServiceLocatorException.java
============================
package kr.co.seoulit.common.sl;

public class ServiceLocatorException extends RuntimeException {

private static final long serialVersionUID = 1L;

public ServiceLocatorException(String message) {
// TODO Auto-generated constructor stub
super(message);
}

}





MemberDAOImpl.java
==================
package kr.co.seoulit.member.dao;

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

import javax.sql.DataSource;

import kr.co.seoulit.common.dao.DataAccessException;
import kr.co.seoulit.common.sl.ServiceLocator;
import kr.co.seoulit.member.to.MemberBean;

public class MemberDAOImpl implements MemberDAO {

private static MemberDAOImpl instance=new MemberDAOImpl();
private MemberDAOImpl(){}
public static MemberDAOImpl getInstance(){
return instance;
}
public List<MemberBean> selectMemberList()
throws DataAccessException{
List<MemberBean> v=new ArrayList<MemberBean>();
Connection con = null;
PreparedStatement pstmt = null;
try {
StringBuffer query = new StringBuffer();
query.append("select * from member");
DataSource dataSource=ServiceLocator.getInstance().getDataSource("jdbc/myoracle");
con = dataSource.getConnection();
pstmt = con.prepareStatement(query.toString());
ResultSet rs = pstmt.executeQuery();
while(rs.next()){
MemberBean member=new MemberBean();
member.setId(rs.getString("id"));
member.setPw(rs.getString("pw"));
member.setAddr(rs.getString("addr"));
member.setTel(rs.getString("tel"));
v.add(member);
}
return v;
} catch(Exception sqle) {
throw new DataAccessException(sqle.getMessage());
} finally {
try{
if(pstmt!=null){pstmt.close(); pstmt=null;}
if(con!=null){con.close(); con=null; }
}catch(Exception e){throw new RuntimeException(e.getMessage());}
}
}
}




db1.jsp
=======
<%@page import="kr.co.seoulit.member.to.MemberBean"%>
<%@page import="java.util.List"%>
<%@page import="kr.co.seoulit.member.dao.MemberDAOImpl"%>
<%@ page language="java" contentType="text/html; charset=EUC-KR"
pageEncoding="EUC-KR"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">
<title>Insert title here</title>
<style type="text/css">
td {border:3px solid navy; }
</style>
</head>
<body><table>
<%
List<MemberBean> list=MemberDAOImpl.getInstance().selectMemberList();
for(MemberBean bean:list){
out.println("<tr>");
out.println("<td>"+bean.getId()+"</td>");
out.println("<td>"+bean.getPw()+"</td>");
out.println("<td>"+bean.getAddr()+"</td>");
out.println("<td>"+bean.getTel()+"</td>");
out.println("</tr>");
}
%>
</table></body>
</html>


[ex] 아래와 같이 출력될 수 있도록 ´db2.jsp´를 작성하시오.

http://localhost:8282/p1/db2.jsp?id=aa

회원정보
ID : aa
PW : bb
ADDR : cc
TEL : dd

[an]
<%@ page language="java" contentType="text/html; charset=EUC-KR"
pageEncoding="EUC-KR"%>
<%@page import="kr.co.seoulit.member.to.MemberBean"%>
<%@page import="java.util.List"%>
<%@page import="kr.co.seoulit.member.dao.MemberDAOImpl"%>
<%
String id=request.getParameter("id");
MemberBean bean=MemberDAOImpl.getInstance().selectMember(id);
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<style type="text/css">
td { border:1px solid navy; width:100px; }
</style>
<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">
<title>Insert title here</title>
</head>
<body>
<h1>회원정보보기</h1>
<table>
<tr><td>ID</td><td><%=bean.getId()%></td></tr>
<tr><td>PW</td><td><%=bean.getPw()%></td></tr>
<tr><td>ADDR</td><td><%=bean.getAddr()%></td></tr>
<tr><td>TEL</td><td><%=bean.getTel()%></td></tr>
</table>
</body>
</html>