728x90
반응형

aa

SELECT

20_01_select01.jsp

20_01_select01.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<%@ page import="java.sql.*"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<%@ include file="20_01_dbconn.jsp"%>
	<table width="300" border="1">
		<tr>
			<th>아이디</th>
			<th>비밀번호</th>
			<th>이름</th>
		</tr>
		<%
		ResultSet rs = null;
		Statement stmt = null;

		try {
			String sql = "select id as u_id, passwd upw, name unm from member";
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);

			while (rs.next()) {
				String id = rs.getString("u_id");
				String pw = rs.getString("upw");
				String name = rs.getString("unm");
		%>
		<tr>
			<td><%=id%></td>
			<td><%=pw%></td>
			<td><%=name%></td>
		</tr>
		<%
		}
		} catch (SQLException ex) {
		out.println("Member 테이블 호출이 실패했습니다.<br>");
		out.println("SQLException : " + ex.getMessage());
		} finally {
		if (rs != null)
		rs.close();
		if (stmt != null)
		stmt.close();
		if (conn != null)
		conn.close();
		}
		%>
	</table>
</body>
</html>​
20_01_dbconn.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<%@ page import="java.sql.*"%>
<%
Connection conn = null;
String user = "test";
String password = "1111";

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

try {
	Class.forName("oracle.jdbc.driver.OracleDriver");
	conn = DriverManager.getConnection(url, user, password);
} catch (ClassNotFoundException e) {
	out.print("ClassNotFoundException : " + e.getMessage());
} catch (SQLException e) {
	out.print("SQLException : " + e.getMessage());
}
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>

</body>
</html>​


sql문
drop table member ;

CREATE TABLE member(
id VARCHAR2(20),
passwd VARCHAR2(20),
name VARCHAR2(30),
PRIMARY KEY(id)
);

select * from member;

insert into member values('user1', 'user1','김알지');
insert into member values('user2', 'user2','김수로');
insert into member values('user3', 'user3','박혁거세');
insert into member values('admin', '1234','단순');

commit;​
drop table student ;

CREATE TABLE student(
id VARCHAR2(20),
name VARCHAR2(30),
gender VARCHAR2(20),
address VARCHAR2(200),
PRIMARY KEY(id)
);

select * from student;

nsert into student values('stu1', '김알지', '남', '서울');
insert into student values('stu2', '김수로', '여', '수원');
insert into student values('stu3', '박혁거세', '남', '제주');

commit;

20_01_select02.jsp
20_01_select02.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<%@ page import="java.sql.*"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<%@ include file="20_01_dbconn.jsp"%>
	<table width="300" border="1">
		<tr>
			<th>아이디</th>
			<th>비밀번호</th>
			<th>이름</th>
		</tr>
		<%
		// 		Statement stmt = null;
		ResultSet rs = null;
		PreparedStatement pstmt = null;

		try {
			String sql = "select * from member where id like ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "user%");
			rs = pstmt.executeQuery();

			while (rs.next()) {
				String id = rs.getString("id");
				String pw = rs.getString("passwd");
				String name = rs.getString("name");
		%>
		<tr>
			<td><%=id%></td>
			<td><%=pw%></td>
			<td><%=name%></td>
		</tr>
		<%
		}
		} catch (SQLException ex) {
		out.println("Member 테이블 호출이 실패했습니다.<br>");
		out.println("SQLException : " + ex.getMessage());
		} finally {
		if (rs != null)
		rs.close();
		if (pstmt != null)
		pstmt.close();
		if (conn != null)
		conn.close();
		}
		%>
	</table>
</body>
</html>​
20_01_connection.jsp
20_01_connection.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<%@ page import="java.sql.*"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<%
	Connection conn = null;
	Statement stmt = null;
	ResultSet rs = null;
	//지역변수 초기에 선언
	try {
		// 		String url = "jdbc:mariadb://localhost:3306/testdb";
		// 		String url = "jdbc:mysql://localhost:3306/testdb";
		String url = "jdbc:oracle:thin:@localhost:1521:xe";
										//localhost 실제 도메인주소 기재
		String user = "test";
		String password = "1111";

		// 		Class.forName("org.mariadb.jdbc.Driver");
		// 		Class.forName("com.mysql.jdbc.Driver");
		Class.forName("oracle.jdbc.driver.OracleDriver");
		conn = DriverManager.getConnection(url, user, password);
		System.out.println("데이터베이스 연결이 성공되었습니다.");

		String sql = "SELECT * FROM student";
		stmt = conn.createStatement();//
		rs = stmt.executeQuery(sql);
	%>

	<h1>학생 테이블의 데이터 정보</h1>
	<table>
		<colgroup>
			<col width="10%" />
			<col width="30%" />
			<col width="30%" />
			<col width="30%" />
		</colgroup>
		<tr>
			<th class="tbr">아이디</th>
			<th class="tbr">이름</th>
			<th class="tbr">성별</th>
			<th>지역</th>
		</tr>
		<%
		while (rs.next()) {
			String id = rs.getString("id");
			String name = rs.getString("name");
			String gender = rs.getString("gender");
			String address = rs.getString("address");
		%>
<!-- 		오라클 대소문자 가리지 않는다 -->
		<tr>
			<td><%=id%></td>
			<td><%=name%></td>
			<td><%=gender%></td>
			<td><%=address%></td>
		</tr>
		<%
		}

		} catch (SQLException ex) {
		out.println("데이터베이스 연결이 실패되었습니다.<br>");
		out.println("SQLException : " + ex.getMessage());
		} catch (ClassNotFoundException ex) {
		out.println("드라이버를 찾을 수 없습니다.<br>");
		out.println("ClassNotFoundException : " + ex.getMessage());
		} finally {
		if (rs != null)
		rs.close();
		if (stmt != null)
		stmt.close();
		if (conn != null)
		conn.close();
		}
		%>
	</table>
</body>
</html>​

 

 

INSERT

20_02_insert01.jsp
20_02_insert01_process.jsp
20_02_insert01.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<title>Database SQL</title>
</head>
<body>
	<form method="post" action="20_02_insert01_process.jsp">
		<p>	아이디 : <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>​

20_02_insert01_process.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<%@ include file="20_01_dbconn.jsp"%>

	<%
	Statement st = null;
	PreparedStatement ps = null;
	ResultSet rs = null;

	try {
		String id = request.getParameter("id");
		String pw = request.getParameter("passwd");
		String nm = request.getParameter("name");

		String sql1 = "select count(*) as cnt from member where id = ? ";
		ps = conn.prepareStatement(sql1);
		ps.setString(1, id);
		rs = ps.executeQuery();
		rs.next();
		if (rs.getInt("cnt") > 0) {

			String sql = "insert into member values ('" + id + "','" + pw + "','" + nm + "')";
			sql1 = "insert into member values (? , ? , ?)";

			//	 	 st = conn.createStatement();
			//	 	 int cnt = st.executeUpdate(sql);

			ps = conn.prepareStatement(sql1);
			ps.setString(1, id);
			ps.setString(2, pw);
			ps.setString(3, nm);
			int cnt = ps.executeUpdate();

			if (cnt > 0) {
		//	 		 sql = "select * from member where id = '"+id+"'";
		//	 		 st = conn.createStatement();
		//	 		 rs = st.executeQuery(sql);
		sql1 = "select * from member where id = ? ";
		ps = conn.prepareStatement(sql1);
		ps.setString(1, id);
		rs = ps.executeQuery();

				while (rs.next()) {
	%>
	데이터가 정상적으로 추가되었습니다.
	<br> 아이디 :
	<%=rs.getString("id")%><br> 비번 :
	<%=rs.getString("passwd")%><br> 이름 :
	<%=rs.getString("name")%><br>

	<%
				}
			} else {
			out.print("데이터가 정상적으로 처리되지 못했습니다.<br>");
			}

		} else {
		out.print("중복된 아이디 값이 존재합니다.");
		}
	} catch (SQLException e) {
		out.print("SQLException: " + e.getMessage());
	} finally {
		try {
			if (rs != null) rs.close();
			if (st != null) st.close();
			if (ps != null) ps.close();
			if (conn != null) conn.close();
		} catch (SQLException e) {
			out.print("SQLException: " + e.getMessage());
		}
	}
	%>

	<%

	%>

</body>
</html>​

  
 

 

20_03_update01.jsp
20_03_update01_process.jsp

20_03_update01.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<title>Database SQL</title>
</head>
<body>	
	<form method="post" action="20_03_update01_process.jsp">
		<p>	아이디 : <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>
20_03_update01_process.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%@ include file="20_01_dbconn.jsp" %>
<%
	PreparedStatement ps = null;
	Statement st = null;
	ResultSet rs = null;
	
	try{
		String id = request.getParameter("id");
		String pw = request.getParameter("passwd");
		String nm = request.getParameter("name");
		
// 		//delete처리 : Statement
// 		String sql = "delete from member where id = '"+id+"' ";
// 		st = conn.createStatement();
// 		int cnt = st.executeUpdate(sql);
		
// 		//delete처리 : PreparedStatement
// 		String sql = "delete from member where id =  ? ";
// 		ps = conn.prepareStatement(sql);
// 		ps.setString(1 , id);
// 		int cnt = ps.executeUpdate();

		//update처리 : Statement
		String sql = "update member set passwd = '"+pw+"', name = '"+nm+"' where id = '"+id+"' ";
		st = conn.createStatement();
		int cnt = st.executeUpdate(sql);
		out.print("update completed");
		
// 		//update처리 : PreparedStatement
// 		String sql = "update member set passwd = ?, name = ? where id = ? ";
// 		ps = conn.prepareStatement(sql);
// 		ps.setString(1 , pw);
// 		ps.setString(2 , nm);
// 		ps.setString(3 , id);
// 		int cnt = ps.executeUpdate();
		
	}catch(Exception e){
		out.print("에러메세지: "+e.getMessage());
	}finally{
		
	}


%>
</body>
</html>
728x90
반응형
저작자표시 비영리 변경금지

'[JAVA]' 카테고리의 다른 글

[JSP] 마리아 db설정  (0) 2024.03.21
[JSP] API  (0) 2024.03.21
Filter  (0) 2024.03.20
[JSP] filter - monitor.log  (0) 2024.03.20
[JSP] cookies  (0) 2024.03.20

+ Recent posts

티스토리툴바