웹프로그래밍 3주차 ~ 4주차(JSP)

2022. 9. 26. 10:20ㆍ대학교 수업내용(JSP)/웹프로그래밍(JSP) - 3주차 ~ 4주차

File -> New -> Dynamic web project<프로젝트명:MyBoard
WEB-INF / lib / mysql-connector-java-8.0.28.jar 버전 복사 붙여넣기
webapp -> new -> jsp file<파일명: list.jsp>
MySql Command Line Client -> root number: 12345 -> show databases; 명령어 치기
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

use mydb; (명령어) -> show tables -> ; 
+----------------+
| Tables_in_mydb |
+----------------+
| board          |
+----------------+
1 row in set (0.00 sec)

 

※list.jsp

<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<%@ page import="java.sql.*" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="EUC-KR">
<title>Insert title here</title>
</head>
<body>
<%
	Connection conn = null;
	try {
		String url = "jdbc:mysql://localhost:3306/mydb";
		String user = "root";
		String password = "1234";
		
		Class.forName("com.mysql.jdbc.Driver");
		conn = DriverManager.getConnection(url, user, password);
		out.println("데이터베이스 연결이 성공했습니다.");
	} catch (SQLException ex) {
		out.println("데이터베이스 연결이 실패했습니다.<br>");
		out.println("SQLException: " + ex.getMessage());
	} finally {
		if (conn != null)
			conn.close();
	}
%>
</body>
</html>

※executeQuery() 메소드 사용 예: SELECT 쿼리문

 

▶list.jsp

<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<%@ page import="java.sql.*" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="EUC-KR">
<title>Insert title here</title>
</head>
<body>
<%
	Connection conn = null;
	Statement stmt = null;
	ResultSet rs = null;
	try {
		String url = "jdbc:mysql://localhost:3306/mydb";
		String user = "root";                                                 
		String password = "12345";
		
		Class.forName("com.mysql.jdbc.Driver");
		conn = DriverManager.getConnection(url, user, password);
		
		stmt = conn.createStatement();
		String sql = "select * from board";
		rs = stmt.executeQuery(sql);
		
		while(rs.next())
		{
			String num = rs.getString("num");
			String subject = rs.getString("subject");
			String content = rs.getString("content");
			String name = rs.getString("name");
			
			out.println(num+subject+content+name);
			out.println("<br>");
		}
		
		out.println("데이터베이스 연결이 성공했습니다.");
	} catch (SQLException ex) {
		out.println("데이터베이스 연결이 실패했습니다.<br>");
		out.println("SQLException: " + ex.getMessage());
	} finally {
		if (conn != null)
			conn.close();
		if(stmt!=null)
			stmt.close();
		if(rs!=null)
			rs.close();
	}
%>
</body>
</html>

Client -> (WEB server) [WAS server] {DBMS]

out.println(num+subject+content+name);
out.println("<br>");
이 부분을

<%=num%>, <%=subject%>, <%=content%>,<%=name%><br>

이렇게 쓸 수도 있다.

 

<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<%@ page import="java.sql.*" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="EUC-KR">
<title>Insert title here</title>
</head>
<body>
<%
	Connection conn = null;
	Statement stmt = null;
	ResultSet rs = null;
	try {
		String url = "jdbc:mysql://localhost:3306/mydb";
		String user = "root";                                                 
		String password = "12345";
		
		Class.forName("com.mysql.jdbc.Driver");
		conn = DriverManager.getConnection(url, user, password);
		
		stmt = conn.createStatement();
		String sql = "select * from board";
		rs = stmt.executeQuery(sql);
		
		while(rs.next())
		{
			String num = rs.getString("num");
			String subject = rs.getString("subject");
			String content = rs.getString("content");
			String name = rs.getString("name");
%>			

<%=num%>, <%=subject%>, <%=content%>,<%=name%><br>

<%		
		}
		
		out.println("데이터베이스 연결이 성공했습니다.");
	} catch (SQLException ex) {
		out.println("데이터베이스 연결이 실패했습니다.<br>");
		out.println("SQLException: " + ex.getMessage());
	} finally {
		if (conn != null)
			conn.close();
		if(stmt!=null)
			stmt.close();
		if(rs!=null)
			rs.close();
	}
%>
</body>
</html>

<%=num%>,<a href="read.jsp"><%=subject%></a><%=name%><br>
<%=num%>.<a href="read.jsp?=<%=num%>"><%=subject%></a> <%=name%><br>
※read.jsp

<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="EUC-KR">
<title>Insert title here</title>
</head>
<body>
여기는 글 읽기 페이지 입니다.
<%
String num = request.getParameter("num");

%>
출력할 글 번호는 <%=num %>입니다.
</body>
</html>

MySQL 기본 명령어
http://leechoong.com/posts/2018/mysql_basic/
DB 명령어
https://cleverdj.tistory.com/24

 

MySQL 기본 명령어 • 리충닷컴

MySQL의 기본 명령어들을 정리한다. 생성, 삽입, 조회, 수정, 삭제 MySQL 접속 필자는 MAMP Stack(Mac Apache, MySQL, Php)을 설치하여 사용한다. 이 환경 기준으로 정리할 예정이다. 설처한 폴더/mysql/bin로 접

leechoong.com

 

[정보] DB 명령어 모음

select rid,rglexp from mstrinfo where rglexp like '%href%' 정규표현식으로 검색 select rglexp from mstrinfo where rid = 2018 룰번호로 검색 < DIV > select * from lgintry where intryip is null order b..

cleverdj.tistory.com

 

※ list.jsp 총정리

<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<%@ page import="java.sql.*" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="EUC-KR">
<title>Insert title here</title>
</head>
<body>
<%
	Connection conn = null;
	Statement stmt = null;
	ResultSet rs = null;
	try {
		String url = "jdbc:mysql://localhost:3306/mydb";
		String user = "root";                                                 
		String password = "12345";
		
		Class.forName("com.mysql.jdbc.Driver");
		conn = DriverManager.getConnection(url, user, password);
		//데이터베이스 연결
		
		stmt = conn.createStatement();
		String sql = "select * from board";
		rs = stmt.executeQuery(sql);
		//데이터베이스에 명령 전달 및 결과회신
		
		while(rs.next())
		{
			String num = rs.getString("num");
			String subject = rs.getString("subject");
			String content = rs.getString("content");
			String name = rs.getString("name");
%>			

<%=num%>.<a href="read.jsp?=<%=num%>"><%=subject%></a> <%=name%><br>

 

<%		
		}
		//회신 결과를 출력
		
		out.println("데이터베이스 연결이 성공했습니다.");
	} catch (SQLException ex) {
		out.println("데이터베이스 연결이 실패했습니다.<br>");
		out.println("SQLException: " + ex.getMessage());
	} finally {
		if (conn != null)
			conn.close();
		if(stmt!=null)
			stmt.close();
		if(rs!=null)
			rs.close();
	}
%>
</body>
</html>

 

※ read.jsp

<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<%@ page import="java.sql.*" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="EUC-KR">
<title>Insert title here</title>
</head>
<body>
여기는 read.jsp입니다.
<%
String a = request.getParameter("num");
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;

try{
	String url = "jdbc:mysql://localhost:3306/mydb";
	String user = "root";                                                 
	String password = "12345";
	
	Class.forName("com.mysql.jdbc.Driver");
	conn = DriverManager.getConnection(url, user, password);
	//데이터베이스 연결
	stmt = conn.createStatement();
	String sql = "select * from board where num="+a;
	rs = stmt.executeQuery(sql);
	//명령전달과 결과 회신
	if(rs.next())
		{
			String num = rs.getString("num");
			String subject = rs.getString("subject");
			String content = rs.getString("content");
			String name = rs.getString("name");
%>			

<%=num%>,<%=subject%><%=name%><br>
<hr>
<%=content %>

<%		
		}
	
	//회신결과를 출력
}catch(Exception ex){
	out.println("실패");
	out.println("에러: " + ex.getMessage());
}finally{
	if (conn != null)
		conn.close();
	if(stmt!=null)
		stmt.close();
	if(rs!=null)
		rs.close();
}
%>

</body>
</html>

 

실행결과)
http://localhost:8080/Board/list.jsp

1.sub1 name1
2.sub2 name2
3.sub3 name3
4.sub4 name4
5.sub5 name5
데이터베이스 연결이 성공했습니다.

mysql창
mysql> select * from board where num=2;
+-----+---------+----------+-------+----------+---------------------+
| num | subject | content  | name  | password | signdate            |
+-----+---------+----------+-------+----------+---------------------+
|   2 | sub2    | content2 | name2 | pass     | 2022-03-30 13:49:58 |
+-----+---------+----------+-------+----------+---------------------+
1 row in set (0.00 sec)

※ select 실행문
https://allg.tistory.com/21

 

[JDBC] Select 문 실행

Select 문 실행 1. JDBC 드라이버를 로딩 Class.forName(“com.mysql.jdbc.Driver”) 2. Connection 객체를 생성 con = DriverManager.getConnection(url, user, pw) 3. Statement 객체를 생성 Staement - SQL문..

allg.tistory.com

 

 

※ delete.jsp 구문

<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<%@ page import="java.sql.*" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="EUC-KR">
<title>Insert title here</title>
</head>
<body>
여기는 글삭제 페이지입니다.
<%
String a = request.getParameter("num");

Connection conn = null;
Statement stmt = null;

try{
	String url = "jdbc:mysql://localhost:3306/mydb";
	String user = "root";                                                 
	String password = "12345";
	
	Class.forName("com.mysql.jdbc.Driver");
	conn = DriverManager.getConnection(url, user, password);
//데이터베이스 연결
	stmt = conn.createStatement();
	String sql = "delete from board where num"+a;
	stmt.executeUpdate(sql);
//명령전송
	response.sendRedirect("list.jsp");

//목록으로 돌아가지
}catch(Exception ex) {
	out.println("데이터베이스 연결이 실패했습니다.<br>");
	out.println("SQLException: " + ex.getMessage());
}finally{
	if (conn != null)
		conn.close();
	if(stmt!=null)
		stmt.close();
}
%>
삭제글은 <%=a%>번 입니다.
</body>
</html>

 

※ read.jsp 구문 2

<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<%@ page import="java.sql.*" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="EUC-KR">
<title>Insert title here</title>
</head>
<body>
여기는 read.jsp입니다.
<%
String a = request.getParameter("num");
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;

try{
	String url = "jdbc:mysql://localhost:3306/mydb";
	String user = "root";                                                 
	String password = "12345";
	
	Class.forName("com.mysql.jdbc.Driver");
	conn = DriverManager.getConnection(url, user, password);
	//데이터베이스 연결
	stmt = conn.createStatement();
	String sql = "select * from board where num="+a;
	rs = stmt.executeQuery(sql);
	//명령전달과 결과 회신
	if(rs.next())
		{
			String num = rs.getString("num");
			String subject = rs.getString("subject");
			String content = rs.getString("content");
			String name = rs.getString("name");
%>			

<%=num%>,<%=subject%><%=name%><br>
<hr>
<%=content %>

<%		
		}
	
	//회신결과를 출력
}catch(Exception ex){
	out.println("실패");
	out.println("에러: " + ex.getMessage());
}finally{
	if (conn != null)
		conn.close();
	if(stmt!=null)
		stmt.close();
	if(rs!=null)
		rs.close();
}
%>

<a href="delete.jsp?num=<%=a%>">글삭제</a>

</body>
</html>

 

mysql> delete from board where num=2;
Query OK, 1 row affected (0.00 sec)

mysql> select * from board;
+-----+---------+----------+-------+----------+---------------------+
| num | subject | content  | name  | password | signdate            |
+-----+---------+----------+-------+----------+---------------------+
|   1 | sub1    | content1 | name1 | pass     | 2022-03-30 13:49:45 |
|   3 | sub3    | content3 | name3 | pass     | 2022-03-30 13:50:10 |
|   4 | sub4    | content4 | name4 | pass     | 2022-03-30 13:50:23 |
|   5 | sub5    | content5 | name5 | pass     | 2022-03-30 13:50:33 |
+-----+---------+----------+-------+----------+---------------------+
4 rows in set (0.00 sec)

 

※ 수정 내용은 댓글에 적어주시기 바랍니다.

티스토리툴바