JDBC DB연동

C Jsp

JDBC DB연동

sfeg 2017. 7. 2. 21:37

오라클: ojdbc14.jar

데이타베이스 연동

---------------------------------------------------------------------------------------------------------------------

1. jdbc 드라이버 로드

2. connection 객체 생성

3. statement 객체 생성 - createStatement();

preparedStatement 객체 생성(동일 쿼리를 값만 바꿔서 사용할때) - prepareStatement();

CallableStatement 객체 생성(프로시져 사용시) -prepareCall();

4. query수행

5. result 객체 사용하여 데이타 추출

6. result 객체 close

statement 객체 close

connection객체 close

-----------------------------------------------------------------------------------------------------------------

오라클=> Connection con=null;

Class.forName("oracle.jdbc.driver.OracleDriver");
conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL","아이디","패스워드");

ex)

----------------------------------------------------------------------------

String id=request.getParameter("id");
String passwd=request.getParameter("passwd");
String name=request.getParameter("name");
Timestamp register=new Timestamp(System.currentTimeMillis());

Connection conn=null;
PreparedStatement pstmt=null;

try{
  //오라클 연결정보
  String jdbcUrl="jdbc:oracle:thin:@localhost:1521:ORCL";
  String dbId="scott";
  String dbPass="tiger";

  pstmt=conn.prepareStatement(sql);
  pstmt.setString(1,id);
  pstmt.setString(2,passwd);
  pstmt.setString(3,name);
  pstmt.setTimestamp(4,register);
  pstmt.executeUpdate();
  out.println("레코드 추가 완료");

}
//예외처리
catch(Exception e){
  e.printStackTrace();
}
//객체닫기
finally{
  if(pstmt != null) try{pstmt.close();}catch(SQLException sqle){}
  if(conn != null) try{conn.close();}catch(SQLException sqle){}
}

%>
----------------------------------------------------------------------------

ex)

----------------------------------------------------------------------------
<%@ page contentType="text/html;charset=euc-kr" %>
<%@ page import="java.sql.*" %>
<% request.setCharacterEncoding("euc-kr"); %>

<html>
<head>
<title>select 예제</title>
</head>
<body>
<h2>member1의 테이블의 레코드 표시</h2>
<table width="550" border="1">
<tr>
<td width="100">아이디</td>
<td width="100">패스워드</td>
<td width="100">이름</td>
<td width="250">가입일자</td>
</tr>

<%
Connection conn=null;
Statement stmt=null;
ResultSet rs=null;

try{
  //오라클 연결정보
  String jdbcUrl="jdbc:oracle:thin:@localhost:1521:ORCL";
  String dbId="scott";
  String dbPass="tiger";

  //오라클 드라이버 로드
  Class.forName("oracle.jdbc.driver.OracleDriver");
  //오라클 연결
  conn=DriverManager.getConnection(jdbcUrl,dbId,dbPass);
  out.println("연결완료");


  //쿼리문 실행
  String sql="select * from member1";
  stmt=conn.createStatement();
  rs=stmt.executeQuery(sql);

  while(rs.next()){
   String id=rs.getString("id");
   String passwd=rs.getString("passwd");
   String name=rs.getString("name");
   Timestamp register=rs.getTimestamp("reg_date");

%>
   <tr>
   <td width="100"><%=id%></td>
   <td width="100"><%=passwd%></td>
   <td width="100"><%=name%></td>
   <td width="250"><%=register.toString()%></td>
   </tr>

<%  }
}
//예외처리
catch(Exception e){
  e.printStackTrace();
}
//객체닫기
finally{
  if(rs != null) try{rs.close();}catch(SQLException sqle){}
  if(stmt != null) try{stmt.close();}catch(SQLException sqle){}
  if(conn != null) try{conn.close();}catch(SQLException sqle){}
}

%>

</table>
</body>
</html>