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();
		}	
		
	}
	
	
	
	
	
}