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)
※ 수정 내용은 댓글에 적어주시기 바랍니다.