DB작업

▷ DB 생성

create table dbtest (

id varchar2(10) not null,

passwd varchar2(10) not null,

name varchar2(20),

logtime date default sysdate

);

이것을 웹브라우저의 oracle페이지에서
localhost:8080에 해준다. 

 
 
desc dbtest;
//로 테이블 생성 확인

 

테이블삽입

▷ Select

파일 생성 \WebContent\db\selectTest.jsp

파일 생성 \Java Resources : src\db\SelectTest.java

 

SelectTest.java

package db;

import java.sql.ResultSet;

import java.sql.Statement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

public class SelectTest {

Connection con;

Statement stmt;

ResultSet rs;

public SelectTest(){

//드라이버로딩

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

} catch (ClassNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

public void getConnection(){

String url = "jdbc:oracle:thin:@localhost:1521:xe";

String dbId = "jsp";

String dbPasswd = "itbank";

try {

con = DriverManager.getConnection(url, dbId, dbPasswd);

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

public void getList(){

try{

//Statement 생성

stmt = con.createStatement();

//쿼리 전송

String sql = "select * from dbtest";

rs = stmt.executeQuery(sql);

//출력

System.out.println("id\t passwd\t name\t logtime");

while(rs.next()){

System.out.println(

rs.getString("id")+"\t"+

rs.getString("passwd")+"\t"+

rs.getString("name")+"\t"+

rs.getTimestamp("logtime")+"\t"

);

}

}catch(SQLException e){

e.printStackTrace();

}

}

public static void main(String[] args) {

SelectTest st = new SelectTest();

st.getConnection();

st.getList();

}

}

 

selectTest.jsp

<%@page import="java.sql.ResultSet"%>

<%@page import="java.sql.Statement"%>

<%@page import="java.sql.DriverManager"%>

<%@page import="java.sql.Connection"%>

<%@page import="java.sql.SQLException"%>

<%@ page language="java" contentType="text/html; charset=EUC-KR"

pageEncoding="EUC-KR"%>

<h2>JDBC - select</h2>

<%

Connection con = null;

Statement stmt = null;

ResultSet rs = null;

try {

//드라이버로딩

Class.forName("oracle.jdbc.driver.OracleDriver");

//DB연결

String url = "jdbc:oracle:thin:@localhost:1521:XE";

String dbId = "jsp";

String dbPasswd = "itbank";

con = DriverManager.getConnection(url, dbId, dbPasswd);

//Statement 생성

stmt = con.createStatement();

//쿼리전송

String sql = "select * from dbtest";

//쿼리받기

rs = stmt.executeQuery(sql);

%>

<table border="1">

<tr align="center">

<td>ID</td>

<td>PASSWORD</td>

<td>NAME</td>

<td>LOG TIME</td>

</tr>

<%

while (rs.next()) {

%>

<tr>

<td><%=rs.getString("id")%></td>

<td><%=rs.getString("passwd")%></td>

<td><%=rs.getString("name")%></td>

<td><%=rs.getTimestamp("logtime")%></td>

</tr>

<%

}

%>

</table>

<%

} catch (ClassNotFoundException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

} finally{

try{

if(rs != null) rs.close();

if(stmt != null) stmt.close();

if(con != null) con.close();

//작업 닫아주기. 닫는순서는 rs 부터

}catch(SQLException e){

e.printStackTrace();

}

}

%>

select 작업 후 close(); 작업을 해주어야한다.
닫는 순서는 rs부터 (역순)  해주어야 한다. 




111214 백업파일

JSP.war