grant to : 권한 부여
jdbc에 입력
//테이블 생성
create table member(
memberid varchar2(20),
password varchar2(100),
name varchar2(90),
email varchar2(60),
constraint pk_member primary key(memberid)
);
//삽입, 검색
INSERT INTO MEMBER(MEMBERID, PASSWORD, NAME, EMAIL)
SELECT MEM_ID, MEM_PASS, MEM_NAME, MEM_MAIL
FROM JSG99.MEMBER;
//커밋
COMMIT;
//모든멤버검색
SELECT*FROM MEMBER;
JDBC URL
thin드라이버를 사용하여 자바랑 오라클을 연결하겠다.
로딩: 메모리에 하드디스크를 올리는것
viewMemberList.jsp
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.DriverManager"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<title>회원 목록</title>
</head>
<body>
member 테이블의 내용<br />
<table width="100%" border="1">
<tr>
<th>이름</th><th>아이디</th><th>이메일</th>
</tr>
<%1. JDBC 드라이버 로딩
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try{2. 데이터베이스 커넥션(연결 객체) 생성
1) jdbcDriver 뭐야? 2) 계정아이디가 뭐야? 3) 비밀번호 뭐야?
String jdbcDriver = "jdbc:oracle:thin:@localhost:1521:xe";
String dbUser = "jspexam";
String dbPass = "java";
String query = "SELECT memberid, PASSWORD, name, email FROM MEMBER";
conn = DriverManager.getConnection(jdbcDriver,dbUser,dbPass); //커넥션에서 3개필요3. Statement 생성
stmt = conn.createStatement();//규칙세우기4. 쿼리 실행
rs = stmt.executeQuery(query);5. 쿼리 실행 결과 화면 출력
while(rs.next()){
out.print("<tr>");
out.print("<td>"+rs.getString("NAME")+"</td>");
out.print("<td>"+rs.getString("MEMBERID")+"</td>");
out.print("<td>"+rs.getString("EMAIL")+"</td>");
out.print("</tr>");
}
}catch(SQLException ex){
out.print(ex.getMessage());
ex.printStackTrace();
}finally{6. 사용한 Statement 객체 종료
if(rs!=null)try{rs.close();}catch(SQLException ex){}
if(stmt!=null)try{stmt.close();}catch(SQLException ex){}7. 커넥션 객체 종료
if(conn!=null)try{conn.close();}catch(SQLException ex){}}
%>
viewMember.jsp
<%@page import="java.sql.SQLException"%>
<%@page import="dto.Mem"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%
String memberId = request.getParameter("memberId");
%>
<!DOCTYPE html>
<html>
<head>
<title>회원 정보</title>
</head>
<body>
<%
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
Mem memVO = null;
String query = "SELECT MEMBERID, PASSWORD, NAME, EMAIL FROM MEMBER "
+ " WHERE MEMBERID = '"+memberId+"'";
try{
conn = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:xe"
,"jspexam"
,"java");
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
if(rs.next()){//next를 했는데 있을때만 실행해라(LIST로 실행 안해도 충분해서 이렇게함)
memVO = new Mem();
memVO.setMemberid(rs.getString("MEMBERID"));
memVO.setName(rs.getString("NAME"));
memVO.setPassword(rs.getString("PASSWORD"));
memVO.setEmail(rs.getString("EMAIL"));
}
out.print("memVO : " + memVO.toString());
}catch(SQLException ex){
out.print(ex.getMessage());
}finally{
//6. 사용한 Statement 객체 종료
if(rs!=null)try{rs.close();}catch(SQLException ex){}
if(stmt!=null)try{stmt.close();}catch(SQLException ex){}
//7. 커넥션 객체 종료
if(conn!=null)try{conn.close();}catch(SQLException ex){}
}
%>
<c:set var="memVO" value="<%=memVO%>" scope="request"/>
<table border="1">
<tr>
<th>아이디</th><td>${memVO.memberid}</td>
</tr>
<tr>
<th>비밀번호</th><td>${memVO.password}</td>
</tr>
<tr>
<th>이름</th><td>${memVO.name}</td>
</tr>
<tr>
<th>이메일</th><td>${memVO.email}</td>
</tr>
</table>
</body>
</html>
updateForm.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<title>이름 변경 폼</title>
</head>
<body>
<form method="post" action="update.jsp">
<table border="1">
<tr>
<td>아이디</td>
<td><input type="text" name="memberId" size="10"></td>
<td>이름</td>
<td><input type="text" name="name" size="10"></td>
</tr>
<tr>
<td colspan="4"><input type="submit" value="변경" /></td>
</tr>
</table>
</form>
</body>
</html>
update.jdp
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.SQLException"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%
request.setCharacterEncoding("UTF-8");파라미터 목록 중에서 memberId 및 name을 get함
String memberId = request.getParameter("memberId");
String name = request.getParameter("name");
out.print("memberId" +memberId+",name : "+name);jdbc 드라이버 로딩(메모리에 올림)
Class.forName("oracle.jdbc.driver.OracleDriver");변경된 건수
int updateCount = 0;커넥션 객체 선언
Connection conn = null;Statement 객체 선언
Statement stmt = null;
String query = "update member set name='"+name
+"' where memberid='"+memberId+"'";
try{
String jdbcDriver = "jdbc:oracle:thin:@localhost:1521:xe";
String dbUser = "jspexam";
String dbPass = "java";커넥션 객체 생성
conn = DriverManager.getConnection(jdbcDriver,dbUser,dbPass);Statement 객체 생성
stmt = conn.createStatement();
insert, update, delete -> executeUpdate 사용 select -> executeQuery 사용
updateCount = stmt.executeUpdate(query);
}catch(SQLException ex){
out.print(ex.getMessage());
}finally{6. 사용한 Statement 객체 종료
if(stmt!=null)try{stmt.close();}catch(SQLException ex){}7. 커넥션 객체 종료
if(conn!=null)try{conn.close();}catch(SQLException ex){}
}
if(updateCount>0){
out.print(memberId + "의 이름을" + name + "(으)로 변경");
}else{
out.print(memberId + "아이디가 없음");
}%>
신입 개발자 입니다!!!