본문 바로가기
개발 수업/DB

[MySQL] DB를 웹에 출력

by 오늘 하루s 2023. 6. 28.
728x90
더보기

Day42. 230628

<%@ 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>회원목록</title>
</head>
<body>
<%--1.드라이브로딩
		  2.커넥션얻기	
	      3.객체준비
		  4.쿼리실행
		  5.자원반환
 --%>
 	<h2>회원목록(p380)</h2>
	<table border="1">
	 <thread>
	 <tr>
	  	<th>번호</th>
	  	<th>memberid</th>
	  	<th>비밀번호</th>
	  	<th>회원명</th>
	  	<th>email</th>
	  	
 <%//1.드라이브로딩
	try {
		//Class.forName("oracle.jdbc.OracleDriver"); //오라클용
		Class.forName("com.mysql.cj.jdbc.Driver"); //My-SQL용
		System.out.println("1.JDBC Driver등록");
	} catch (ClassNotFoundException e) {
		e.printStackTrace();
	}
 
 //2.연결 커넥션얻기
 		//오라클용	 	
 		//String url ="jdbc:oracle:thin:@172.30.1.86:1521/xe";
		//String user = "scott";
		//String password = "tiger";
 	 	
		//My-SQL용(p380 26~29라인)
		String url ="jdbc:mysql://172.30.1.86:3306/chap14?useUnicode=true&characterEncoding=utf8";
		String user = "jspexam";
		String password = "jsppw";
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = DriverManager.getConnection(url, user, password);
			System.out.println("2.연결 Connection얻기-성공");
		} catch (SQLException e) {
			e.printStackTrace();
		}
 
 //3.객체준비
   	try {
			stmt = conn.createStatement();
		} catch (SQLException e1) {
			System.out.println("객체관련 에러발생="+e1);
			e1.printStackTrace();
		}
 
 //4.쿼리실행
		String sql = "select no,memberid,password,name,email from member";
try {
		rs = stmt.executeQuery(sql);
		
		System.out.println("회원번호\t회원id\t비밀번호\t회원명\t이메일");
		System.out.println("---------------------------------------------------");
		while(rs.next()) {
			//컬럼 별칭사용시
							int mno = rs.getInt("no"); //첫번째 컬럼
							String mmemberid= rs.getString("memberid");
							String mpassword = rs.getString("password");
							String  mname= rs.getString("name");
							String  memail= rs.getString("email");
							System.out.printf("%8d %20s %10s %20s %30s\r\n", mno,mmemberid,mpassword,mname,memail);
						
 %>
 
		<%--회원수만큼 tr요소가 반복=>반복문사용--%>
		<%--반복문시작 --%>
		<tr>
		<td><%=mno %></td>
		<td><%=mmemberid %></td>
		<td><%=mpassword %></td>
		<td><%=mname %></td>
		<td><%=memail %></td>
		</tr>
		<%--반복문끝 --%>
		<%
		}//while

		} catch (SQLException e1) {
		System.out.println("executeUpdate()실행관련 에러발생");
		e1.printStackTrace();
		}

		//5.자원반환
		try {
					if(rs!=null) {rs.close();}
					if(stmt!=null) {stmt.close();}
					if(conn!=null) {conn.close();}
				} catch (SQLException e) {
					e.printStackTrace();
				}
				System.out.println("5.자원반납");
		 %>
	</tbody>
	</table>
</body>
</html>

DB에 저장된 회원목록이 출력된다.

 

 

<%@ 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>회원목록</title>
</head>
<body>
<%--1.드라이브로딩
		  2.커넥션얻기	
	      3.객체준비
		  4.쿼리실행
		  5.자원반환
 --%>
 	<h2>회원목록(p380)</h2>
	<table border="1">
	 <thread>
	 <tr>
	  	<th>번호</th>
	  	<th>memberid</th>
	  	<th>비밀번호</th>
	  	<th>회원명</th>
	  	<th>email</th>
	  	
 <%//1.드라이브로딩
	try {
		//Class.forName("oracle.jdbc.OracleDriver"); //오라클용
		Class.forName("com.mysql.cj.jdbc.Driver"); //My-SQL용
		System.out.println("1.JDBC Driver등록");
	} catch (ClassNotFoundException e) {
		e.printStackTrace();
	}
 
 //2.연결 커넥션얻기
 		//오라클용	 	
 		//String url ="jdbc:oracle:thin:@172.30.1.86:1521/xe";
		//String user = "scott";
		//String password = "tiger";
 	 	
		//My-SQL용(p380 26~29라인)
		String url ="jdbc:mysql://172.30.1.86:3306/chap14?useUnicode=true&characterEncoding=utf8";
		String user = "jspexam";
		String password = "jsppw";
		Connection conn = null;
		//Statement stmt = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			conn = DriverManager.getConnection(url, user, password);
			System.out.println("2.연결 Connection얻기-성공");
		} catch (SQLException e) {
			e.printStackTrace();
		}
 
 //3.객체준비
   	try {
		String sql = "select no,memberid,password,name,email "+
   								"from member "+
								"where memberid='adminid'";
			//stmt = conn.createStatement();
			stmt = conn.prepareStatement(sql);
		} catch (SQLException e1) {
			System.out.println("객체관련 에러발생="+e1);
			e1.printStackTrace();
		}
 
 //4.쿼리실행
try {
		//rs = stmt.executeQuery(sql);
		rs = stmt.executeQuery();
		
		System.out.println("회원번호\t회원id\t비밀번호\t회원명\t이메일");
		System.out.println("---------------------------------------------------");
		while(rs.next()) {
			//컬럼 별칭사용시
							int mno = rs.getInt("no"); //첫번째 컬럼
							String mmemberid= rs.getString("memberid");
							String mpassword = rs.getString("password");
							String  mname= rs.getString("name");
							String  memail= rs.getString("email");
							System.out.printf("%8d %20s %10s %20s %30s\r\n", mno,mmemberid,mpassword,mname,memail);
						
 %>
 
		<%--회원수만큼 tr요소가 반복=>반복문사용--%>
		<%--반복문시작 --%>
		<tr>
		<td><%=mno %></td>
		<td><%=mmemberid %></td>
		<td><%=mpassword %></td>
		<td><%=mname %></td>
		<td><%=memail %></td>
		</tr>
		<%--반복문끝 --%>
		<%
		}//while

		} catch (SQLException e1) {
		System.out.println("executeUpdate()실행관련 에러발생");
		e1.printStackTrace();
		}

		//5.자원반환
		try {
					if(rs!=null) {rs.close();}
					if(stmt!=null) {stmt.close();}
					if(conn!=null) {conn.close();}
				} catch (SQLException e) {
					e.printStackTrace();
				}
				System.out.println("5.자원반납");
		 %>
	</tbody>
	</table>
</body>
</html>

객체준비 단계에서 memberid를 'adminid' 지정하면 adminid만 출력된다.

 

콘솔창

 

클라이언트가 요청시 보낸 data를 받아 출력>

<%@ 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>회원목록</title>
</head>
<body>
<%--1.드라이브로딩
		  2.커넥션얻기	
	      3.객체준비
		  4.쿼리실행
		  5.자원반환
 --%>
 	<h2>회원목록(p380)</h2>
	<table border="1">
	 <thread>
	 <tr>
	  	<th>번호</th>
	  	<th>memberid</th>
	  	<th>비밀번호</th>
	  	<th>회원명</th>
	  	<th>email</th>
	  	
 <%
 //클라이언트가 요청시 보낸 data를 받는다.
String memberid = request.getParameter("memberid");
 
 //1.드라이브로딩
	try {
		//Class.forName("oracle.jdbc.OracleDriver"); //오라클용
		Class.forName("com.mysql.cj.jdbc.Driver"); //My-SQL용
		System.out.println("1.JDBC Driver등록");
	} catch (ClassNotFoundException e) {
		e.printStackTrace();
	}
 
 //2.연결 커넥션얻기
 		//오라클용	 	
 		// String url ="jdbc:oracle:thin:@172.30.1.86:1521/xe";
		//String user = "scott";
		//String password = "tiger";
 	 	
		//My-SQL용(p380 26~29라인)
		String url ="jdbc:mysql://172.30.1.86:3306/chap14?useUnicode=true&characterEncoding=utf8";
		String user = "jspexam";
		String password = "jsppw";
		Connection conn = null;
		//Statement stmt = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			conn = DriverManager.getConnection(url, user, password);
			System.out.println("2.연결 Connection얻기-성공");
		} catch (SQLException e) {
			e.printStackTrace();
		}
 
 //3.객체준비
   	try {
		String sql = "select no,memberid,password,name,email "+
   								"from member "+
								"where memberid=?";
			//stmt = conn.createStatement();
			stmt = conn.prepareStatement(sql);
		} catch (SQLException e1) {
			System.out.println("객체관련 에러발생="+e1);
			e1.printStackTrace();
		}
 
 //4.쿼리실행
try {
		//rs = stmt.executeQuery(sql);
		stmt.setString(1,memberid);
		rs = stmt.executeQuery();
		
		System.out.println("회원번호\t회원id\t비밀번호\t회원명\t이메일");
		System.out.println("---------------------------------------------------");
		while(rs.next()) {
			//컬럼 별칭사용시
							int mno = rs.getInt("no"); //첫번째 컬럼
							String mmemberid= rs.getString("memberid");
							String mpassword = rs.getString("password");
							String  mname= rs.getString("name");
							String  memail= rs.getString("email");
							System.out.printf("%8d %20s %10s %20s %30s\r\n", mno,mmemberid,mpassword,mname,memail);
						
 %>
 
		<%--회원수만큼 tr요소가 반복=>반복문사용--%>
		<%--반복문시작 --%>
		<tr>
		<td><%=mno %></td>
		<td><%=mmemberid %></td>
		<td><%=mpassword %></td>
		<td><%=mname %></td>
		<td><%=memail %></td>
		</tr>
		<%--반복문끝 --%>
		<%
		}//while

		} catch (SQLException e1) {
		System.out.println("executeUpdate()실행관련 에러발생");
		e1.printStackTrace();
		}

		//5.자원반환
		try {
					if(rs!=null) {rs.close();}
					if(stmt!=null) {stmt.close();}
					if(conn!=null) {conn.close();}
				} catch (SQLException e) {
					e.printStackTrace();
				}
				System.out.println("5.자원반납");
		 %>
	</tbody>
	</table>
</body>
</html>

String memberid = request.getParameter("memberid");사용하여 클라이언트가 요청시 보낸 data를 출력한다.

아직 memberid를 입력받을 수 없으므로 주소표시줄에 직접 입력 해 출력한다.

 

728x90

'개발 수업 > DB' 카테고리의 다른 글

이클립스에서 오라클 데이터베이스 연동  (0) 2023.06.29
[MySQL] JSP에서 JDBC 프로그래밍하기  (0) 2023.06.28
[MySQL] MySQL 데이터 베이스 생성,입력,수정,삭제  (0) 2023.06.28
[MySQL] MySQL Diagram만들기  (0) 2023.06.28
[MySQL] MySQL 쿼리문  (0) 2023.06.28

관련글

티스토리툴바