[dao]

DB 연결

1) 프로젝트에서

jdbcTemplate.java

driverPath = "oracle.jdbc.driver.OracleDriver";
url = "jdbc:oracle:thin:@localhost:1521:xe";
uid = "DB";
pwd = "DBpwd";

//DB 연결
try {
	Class.forname(driverPath);
    conn = DriverManager.getConnection(url, uid, pwd);
    if(conn != null) {
    	System.out.println("연결 성공");
    }else {
    	System.out.println("연결 실패");
	}
}catch(Exception e) {
	System.out.println("연결 실패");
	e.printStackTrace();
}

2) servers에서

server.xml

<Resource
    	auth="Container" description="dbcp by datasource"
    	name="jdbc/mylocaloracle" type="javax.sql.DataSource"
    	driverClassName="oracle.jdbc.driver.OracleDriver"
    	url="jdbc:oracle:thin:@localhost:1521:xe"
    	username="DB"
    	password="DBpwd"/>

content.xml

<Resource
    	auth="Container" description="dbcp by datasource"
    	name="jdbc/mylocaloracle" type="javax.sql.DataSource"
    	driverClassName="oracle.jdbc.driver.OracleDriver"
    	url="jdbc:oracle:thin:@localhost:1521:xe"
    	username="DB"
    	password="DBpwd"/>

jdbcTemplate.java

try {
	InitialContext initContext = new InitialContext();
	Context envContext = (Context)initContext.lookup("java:/comp/env");  //Tomcat resource 설정을 찾음: server.xml, context.xml
	DataSource ds = (DataSource)envContext.lookup("jdbc/mylocaloracle");
	conn = ds.getConnection();
			
	if(conn != null) {
		System.out.println("JNDI 연결 성공");
	}else {
		System.out.println("JNDI 연결 실패");
	}
}catch(Exception e) {
	System.out.println("연결 실패");
	e.printStackTrace();
}

Statement

read

//read
try{
	Connection conn = null;
	Statement st = null;
	ResultSet rs = null;
    st = conn.createStatement();
    String query = "select * from member";
    rs = st.executeQuery(query);
    
    memberList = new ArrayList<VO>();
    while(rs.next()){
    	Vo vo = new Vo();
        vo.setId(rs.getString("id");
        vo.setAge(rs.getInt("age");
        vo.setGender(rs.getString("gender").charAt(0));
        ...
   	}
} catch (Exception e){
	System.out.println("연결 실패");
}

PreparedStatement

update

//update
public int updateMember(){
	int result = 0;
    Connection conn = null;
	PreparedStatement ps = null;
    try{
    	String query = "update member set member_age = ?, gender = ? where member_id like ?";
        ps = conn.preparedStatement(query);
        ps.setInt(1, vo.getAge());
        ps.setString(2, String.valueOf(vo.getGender());
        ps.setString(vo.getId());
        result = ps.executeUpdate();
} catch (Exception e) {
	System.out.println("연결 실패");
	e.printStackTrace();
}