insertTestForm.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>레코드 삽입 (추가 ) 예제</title>
</head>
<body>
<h2>member1 테이블에 레코드 삽입(추가)예제</h2>
<form method="post" action="insetTest.jsp">
아이디: <input type="text" name="id"><br/>
패스워드: <input type="password" name="passwd"><br/>
이름: <input type="text" name="name"><br/>
<input type="submit" value="보내기">
</form>
</body>
</html>insertTest.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%
request.setCharacterEncoding("utf-8");
//전송된 데이터 처리
String id =request.getParameter("id");
String passwd =request.getParameter("passwd");
String name =request.getParameter("name");
//전송되지 않은 데이터를 jsp에서 생성함(날짜/시간)
Timestamp register= new Timestamp(System.currentTimeMillis());
Connection conn=null;
PreparedStatement pstmt=null;
try{
String jdbcUrl="jdbc:oracle:thin:@localhost:1521:orcl";
String dbId="hr";
String dbPass="hr";
//JDBC 패턴을 아는것이 중요하다.
//JDBC 수행1단계 : JDBC driver 로드
Class.forName("oracle.jdbc.driver.OracleDriver");
//JDBC 수행2단계 : Connection 객체 생성
conn=DriverManager.getConnection(jdbcUrl,dbId,dbPass);
//String sql ="insert into member1(id,passwd) values(?,?)";//특정 컬럼일경우 명시해야함
//String sql ="insert into member1(id,passwd,name,register) values(?,?,?,?)";//컬럼명을 명시해야하지만 전체입력이라 생략
//? : 바인드 문자
String sql ="insert into member1 values(?,?,?,?)";
//JDBC 수행3단계 : PreparedStatement 객체 생성
//conn.prepareStatement : 에 sql문장 보관
pstmt=conn.prepareStatement(sql);
//각각의 바인드 문자에 매칭시킴
pstmt.setString(1, id);
pstmt.setString(2, passwd);
pstmt.setString(3, name);
pstmt.setTimestamp(4, register);
//JDBC 수행4단계 : SQL문 실행
pstmt.executeUpdate();
}catch(Exception e){
e.printStackTrace();
}finally{
//자원정리
if(pstmt !=null) try{pstmt.close();}catch(SQLException sqle){}
if(conn !=null) try{conn.close();}catch(SQLException sqle){}
}
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>레코드 삽입 (추가) 예제</title>
</head>
<body>
member1 테이블에 새로운 레코드를 삽입 (추가) 했습니다.
</body>
</html>
selectTest.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</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="100">가입일자</td>
</tr>
<%
Connection conn=null;
PreparedStatement pstmt=null;
ResultSet rs=null;
try{
String jdbcUrl="jdbc:oracle:thin:@localhost:1521:orcl";
String dbId="hr";
String dbPass="hr";
//JDBC 수행1단계 : JDBC driver 로드
Class.forName("oracle.jdbc.driver.OracleDriver");
//JDBC 수행2단계 : Connection 객체 생성
conn=DriverManager.getConnection(jdbcUrl,dbId,dbPass);
String sql ="select * from member1";
//JDBC 수행3단계 : PrepareStatement 객체생성
pstmt=conn.prepareStatement(sql);
//JDBC 수행4단계 : SQL문 실행
//JDBC 수행5단계 : SQL문 실행으로 얻어진 모든 레코드를 담은 ResultSet 객체생성
rs=pstmt.executeQuery();
while(rs.next()){
String id= rs.getString("id"); //(id)컬럼명 대신 숫자 1,2,3,4 등으로 사용가능 / 컬럼명 사용을추천
String passwd= rs.getString("passwd");
String name = rs.getString("name");
//밀리세컨드까지 뽑을경우 Timestamp사용 ,그렇지 않으면 사용 할 필요없슴
Timestamp register=rs.getTimestamp("register");
%>
<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(pstmt !=null) try{pstmt.close();}catch(SQLException sqle){}
if(conn !=null) try{conn.close();}catch(SQLException sqle){}
}
%>
</table>
</body>
</html>
updateTestForm.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head><title>레코드 수정예제</title></head>
<body>
<h2>member1 테이블의 레코드 수정예제</h2>
<form method="post" action="updateTest.jsp">
아이디 : <input type="text" name="id"><p>
패스워드 : <input type="password" name="passwd"><p>
변경할 이름:<input type="text" name="name"><p>
<input type="submit" value="보내기">
</form>
</body>
</html>updateTest.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import = "java.sql.*" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>레코드 수정예제</title>
</head>
<body>
<%
request.setCharacterEncoding("utf-8");
String id =request.getParameter("id");
String passwd =request.getParameter("passwd");
String name =request.getParameter("name");
Connection conn=null;
PreparedStatement pstmt=null;
ResultSet rs=null;
try{
String jdbcUrl="jdbc:oracle:thin:@localhost:1521:orcl";
String dbId="hr";
String dbPass="hr";
//JDBC 수행1단계 : JDBC driver 로드
Class.forName("oracle.jdbc.driver.OracleDriver");
//JDBC 수행2단계 : Connection 객체 생성
conn=DriverManager.getConnection(jdbcUrl,dbId,dbPass);
String sql ="select id, passwd from member1 where id= ?";
//JDBC 수행3단계 : PrepareStatement 객체생성
pstmt=conn.prepareStatement(sql);
//JDBC 수행4단계 : SQL문 실행
//JDBC 수행5단계 : SQL문 실행으로 얻어진 모든 레코드를 담은 ResultSet 객체생성
pstmt.setString(1,id);
rs=pstmt.executeQuery();
//레코드를 읽어오는데
//re.next =true => 아이디에 맞는 레코드가 있다.
//re.next =false => 아이디에 맞는 레코드가 없다.
if(rs.next()){
String rId=rs.getString("id");
String rPasswd=rs.getString("passwd");
//db에서 가져온 아이디 패스워드와 입력한 아이디패스워드가 같은가 조건체크
if(id.equals(rId) && passwd.equals(rPasswd)){
sql="update member1 set name=? where id=?";
pstmt =conn.prepareStatement(sql);
pstmt.setString(1,name);
pstmt.setString(2,id);
pstmt.executeUpdate();
%>
member1 테이블 레코드를 수정했습니다.
<%
}else{
out.println("패스워드가 틀렸습니다.");
}
}else{
out.println("아이디가 틀렸습니다.");
}
}catch(Exception e){
e.printStackTrace();
}finally{
//자원정리
if(rs !=null) try{rs.close();}catch(SQLException sqle){}
if(pstmt !=null) try{pstmt.close();}catch(SQLException sqle){}
if(conn !=null) try{conn.close();}catch(SQLException sqle){}
}
%>
</body>
</html>
deleteTestForm.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head><title>레코드 삭제예제</title></head>
<body>
<h2>member1 테이블의 레코드 삭제예제</h2>
<form method="post" action="deleteTest.jsp">
아이디 : <input type="text" name="id"><p>
패스워드 : <input type="password" name="passwd"><p>
<input type="submit" value="보내기">
</form>
</body>
</html>deleteTest.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>레코드 삭제 예제</title>
</head>
<body>
<%
request.setCharacterEncoding("utf-8");
String id =request.getParameter("id");
String passwd =request.getParameter("passwd");
Connection conn=null;
PreparedStatement pstmt=null;
ResultSet rs=null;
try{
String jdbcUrl="jdbc:oracle:thin:@localhost:1521:orcl";
String dbId="hr";
String dbPass="hr";
//JDBC 수행1단계 : JDBC driver 로드
Class.forName("oracle.jdbc.driver.OracleDriver");
//JDBC 수행2단계 : Connection 객체 생성
conn=DriverManager.getConnection(jdbcUrl,dbId,dbPass);
String sql ="select id, passwd from member1 where id= ?";
//JDBC 수행3단계 : PrepareStatement 객체생성
pstmt=conn.prepareStatement(sql);
//JDBC 수행4단계 : SQL문 실행
//JDBC 수행5단계 : SQL문 실행으로 얻어진 모든 레코드를 담은 ResultSet 객체생성
pstmt.setString(1,id);
rs=pstmt.executeQuery();
if(rs.next()){
String rId=rs.getString("id");
String rPasswd=rs.getString("passwd");
if(id.equals(rId) && passwd.equals(rPasswd)){
sql="delete from member1 where id= ? ";
pstmt=conn.prepareStatement(sql);
pstmt.setString(1, id);
pstmt.executeUpdate();
%>
member1 테이블의 레코드를 삭제했습니다.
<%
}else{
out.println("패스워드가 틀렸습니다.");
}
}else{
out.println("아이디가 틀렸습니다.");
}
}catch(Exception e){
e.printStackTrace();
}finally{
//자원정리
if(rs !=null) try{rs.close();}catch(SQLException sqle){}
if(pstmt !=null) try{pstmt.close();}catch(SQLException sqle){}
if(conn !=null) try{conn.close();}catch(SQLException sqle){}
}
%>
</body>
</html>
ojdbc14.jar
