1. mariadb dbcp 다운로드
mariadb-java-client-2.3.0.jar
https://mvnrepository.com/artifact/org.mariadb.jdbc/mariadb-java-client
오라클
■ 파일 다운로드
Collections : http://commons.apache.org/proper/commons-collections/download_collections.cgi
DBCP : http://commons.apache.org/proper/commons-dbcp/download_dbcp.cgi
Pool : http://commons.apache.org/proper/commons-pool/download_pool.cgi
2. 프로젝트 내의 META-INF 폴더 아래
context.xml 파일 을 생성
(톰켓 컨테이너에서 conf 폴더 아래 존재하지만 프로젝트 내에서 설절 할 수 있다.)
<?xml version="1.0" encoding="UTF-8"?> <Context docBase="DataBase" path="/DataBase" reloadable="true" source="org.eclipse.jst.jee.server:DataBase"> <Resource auth="Container" name="jdbc/pool" driverClassName="org.mariadb.jdbc.Driver" type="javax.sql.DataSource" url="jdbc:mysql://ip주소:포트번호/전역 데이터베이스 이름" username="접속계정" password="계정 비밀번호" loginTimeout="10" maxActive="50" maxIdle="20" maxWait="5000" testOnBorrow="true" /> </Context> <!-- auth : 컨테이너를 자원 관리자로 기술 name : JDBC이름, 변경 가능 driverClassName : JDBC 드라이버 type : 웹에서 이 리소스를 사용할 때 DataSource로 리턴됨 username : 접속계정 password : 접속할 계정 비밀번호 loginTimeout : 연결 끊어지는 시간 maxActive : 최대 연결 가능한 Connection수 (기본 20개) maxIdle : Connection pool 유지를 위해 최대 대기 connection 숫자 maxWait : 사용 가능한 커넥션이 없을 때 커넥션 회수를 기다리는 시간 (1000 = 1초) testOnBorrow : db에 test를 해볼 것인지 -->
3.web.xml
<resource-ref> <description>DB Connection</description> <res-ref-name>jdbc/pool</res-ref-name> <res-type>javax.sql.DataSource</res-type> <res-auth>Container</res-auth> </resource-ref>
4.java
package sec01.ex03; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.Date; import java.util.List; import javax.naming.Context; import javax.naming.InitialContext; import javax.sql.DataSource; public class MemberDAO { private Connection con; private Statement stmt; private PreparedStatement pstmt; private DataSource ds; public MemberDAO() { try { Context ctx=new InitialContext(); Context envContext=(Context)ctx.lookup("java:/comp/env"); ds=(DataSource)envContext.lookup("jdbc/pool"); }catch (Exception e) { e.printStackTrace(); } } public List<MemberVO> listMembers() { List<MemberVO> list =new ArrayList<MemberVO>(); ResultSet rs=null; try { //connDB(); con=ds.getConnection(); String query="select * from t_member"; System.out.println("PreparedStatement sql " +query); pstmt=con.prepareStatement(query); //rs=stmt.executeQuery(query); rs=pstmt.executeQuery(); while(rs.next()) { String id=rs.getString("id"); String pwd=rs.getString("pwd"); String name=rs.getString("name"); String email=rs.getString("email"); Date joinDate=rs.getDate("joinDate"); MemberVO vo=new MemberVO(); vo.setId(id); vo.setPwd(pwd); vo.setName(name); vo.setEmail(email); vo.setJoinDate(joinDate); list.add(vo); } }catch (Exception e) { e.printStackTrace(); }finally { try { if(rs!=null) rs.close(); } catch (SQLException e) { e.printStackTrace(); } try { if(stmt!=null) stmt.close(); } catch (SQLException e) { e.printStackTrace(); } try { if(con!=null) con.close(); } catch (SQLException e) { e.printStackTrace(); } } return list; } private void connDB(){ try { Class.forName(driver); System.out.println("mysql 드라이버 로딩 성공"); con=DriverManager.getConnection(url, user, pwd); System.out.println("Connection 생성 성공"); stmt=con.createStatement(); System.out.println("Statement 생성 성공"); }catch (Exception e) { e.printStackTrace(); } } public void addMember(MemberVO vo) { try { Connection con=ds.getConnection(); String sql="insert into t_member"; sql+=" (id, pwd, name, email)"; sql+=" values ( ? , ? , ? , ?)"; System.out.println("preparestatement : "+sql); pstmt=con.prepareStatement(sql); pstmt.setString(1, vo.getId()); pstmt.setString(2, vo.getPwd()); pstmt.setString(3, vo.getName()); pstmt.setString(4, vo.getEmail()); pstmt.executeUpdate(); pstmt.close(); }catch(Exception e) { e.printStackTrace(); } } public void delMember(String id) { try { Connection con=ds.getConnection(); String sql="delete from t_member where id=?"; System.out.println("preparestatement delMember: "+sql); pstmt=con.prepareStatement(sql); pstmt.setString(1,id); pstmt.executeUpdate(); pstmt.close(); }catch(Exception e) { e.printStackTrace(); } } }
댓글 ( 0)
댓글 남기기