브라우저에서 요청사항이 있을 경우 JSP파일과 DB를 연동하여 데이터를 등록, 수정, 삭제하는 과정
등록:
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="javax.sql.*" %>
<%@ page import="javax.naming.*" %>
<%
request.setCharacterEncoding("utf-8");
Connection conn = null;
try {
String sql = "insert into EVENT(NAME,EMAIL) values(?,?)";
Context init = new InitialContext();
DataSource ds = (DataSource) init.lookup("java:comp/env/jdbc/OracleDB");
conn = ds.getConnection();
PreparedStatement psmt = conn.prepareStatement(sql);
String name = request.getParameter("name");
String email = request.getParameter("email");
psmt.setString(1,name);
psmt.setString(2,email);
if(name !=null && name !="" && email !=null && email !=""){
psmt.executeUpdate();
}
}catch(Exception e){
e.printStackTrace();
}
%>
<!DOCTYPE html>
<html>
<script type="text/javascript">
function fnHome() {
location.href = "03_DBUpdate.jsp";
}
</script>
<head>
<meta charset="UTF-8">
<title>JSP - DB Sync</title>
</head>
<body>
<div align="center">
<h1>데이터 등록</h1>
<hr>
<br>
<form method="post" >
이름:<input type="text" name="name">
이메일:<input type="text" name="email"><br>
<input type="submit" value="등록" formaction="01_DBInsert.jsp">
<input type="submit" value="삭제" formaction="02_DBDelete.jsp" ><br><br><br>
<input type="button" value="수정하러가기" onClick="fnHome()">
<br>
<br>
<hr>
</form>
</div>
#등록 목록<br>
<%
try {
String sql = "select * from EVENT ORDER BY NAME asc";
PreparedStatement pstmt = conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
int i=1;
while(rs.next()){
out.println(i+":"+rs.getString(1)+","+rs.getString(2)+"<br>");
i++;
}
rs.close();
pstmt.close();
conn.close();
}catch(Exception e){
e.printStackTrace();
}
%>
</body>
</html>삭제:
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="javax.sql.*" %>
<%@ page import="javax.naming.*" %>
<%
request.setCharacterEncoding("utf-8");
Connection conn = null;
String sql = "DELETE FROM EVENT WHERE name=? and email=?";
try {
String name = request.getParameter("name");
String email = request.getParameter("email");
Context init = new InitialContext();
DataSource ds = (DataSource) init.lookup("java:comp/env/jdbc/OracleDB");
conn = ds.getConnection();
PreparedStatement psmt = conn.prepareStatement(sql);
psmt.setString(1,name);
psmt.setString(2,email);
int data = psmt.executeUpdate();
psmt.close();
conn.close();
if(data !=0){ %>
<script>
alert(" 삭제되었습니다.");
</script>
<% }else{%>
<script>
alert(" 이름과 메일을 확인해주세요.");
</script>
<% }%>
<script>
location.href = '01_DBInsert.jsp';
</script>
<%
}catch(Exception e){%>
<% e.printStackTrace();
}
%>수정:
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="javax.sql.*" %>
<%@ page import="javax.naming.*" %>
<%
request.setCharacterEncoding("utf-8");
Connection conn = null;
try {
String sql = "UPDATE event SET name=?, email=? where name=? ";
String sql1 = "UPDATE event SET name=? where name=? ";
String sql2 = "UPDATE event SET email=? where name=? ";
String name = request.getParameter("name");
String name1 = request.getParameter("name1");
String email1 = request.getParameter("email1");
Context init = new InitialContext();
DataSource ds = (DataSource) init.lookup("java:comp/env/jdbc/OracleDB");
conn = ds.getConnection();
PreparedStatement psmt = conn.prepareStatement(sql);
PreparedStatement psmt1 = conn.prepareStatement(sql1);
PreparedStatement psmt2 = conn.prepareStatement(sql2);
if(name1 !=null && name1 !="" && email1 !=null && email1 !=""){
psmt.setString(1,name1);
psmt.setString(2,email1);
psmt.setString(3,name);
psmt.executeUpdate(); %>
<% }else if(name1 !=null && name1 !="" && email1 ==""){
psmt1.setString(1,name1);
psmt1.setString(2,name);
psmt1.executeUpdate();
}else if(email1 !=null && email1 !="" && name1 ==""){
psmt2.setString(1,email1);
psmt2.setString(2,name);
psmt2.executeUpdate();
}else if (name==""){
}
psmt.close();
conn.close();
}catch(Exception e){
e.printStackTrace();
}
%>
<html>
<head>
<meta charset="UTF-8">
<title>JSP - DB Sync</title>
<script type="text/javascript">
function fnHome() {
location.href = "01_DBInsert.jsp";
}
</script>
</head>
<body>
<div align="center">
<h1>수정하기</h1>
<hr>
<form method="post" action="03_DBUpdate.jsp">
등록된 이름:<input type="text" name="name"><br><br>
수정할 이름:<input type="text" name="name1"><br>
수정할 이메일:<input type="text" name="email1"><br><br>
<input type="submit" value="수정" >
<input type="button" value="뒤로가기" onClick="fnHome()">
</form>
</div>
#등록 목록<br>
<%
try {
String sql = "SELECT * FROM EVENT ORDER BY NAME asc";
Context init = new InitialContext();
DataSource ds = (DataSource) init.lookup("java:comp/env/jdbc/OracleDB");
conn = ds.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
int i=1;
while(rs.next()){
out.println(i+":"+rs.getString(1)+","+rs.getString(2)+"<br>");
i++;
}
rs.close();
pstmt.close();
conn.close();
}catch(Exception e){
e.printStackTrace();
}
%>
</body>
</html>
728x90
'WORKS > DataBase' 카테고리의 다른 글
| [DataBase] DBeaver 설치 MariaDB 연결 (0) | 2023.01.17 |
|---|---|
| [DataBase][mysql] mariaDB, MySQL 문법 / 데이터베이스 날짜함수 숫자함수 문자열함수 (0) | 2023.01.16 |
| [DataBase] JSP 파일에서 DB 연동하기 / JSP DB 연동하기 / Connection Pool 이용 Oracle 데이터베이스 연결 / DBCP, JNDI (2) | 2022.02.25 |
| [DataBase] JDBC 프로그래밍이란? / JDBC를 이용한 오라클 데이터베이스 연동 (Oracle) / Database JDBC (0) | 2022.02.24 |
| [DataBase] 데이터베이스란? SQL? DBMS란? (0) | 2022.02.24 |
댓글