[DB]MYSQL(INSERT/UPDATE/DELETE/SELECT)사용하기
@욕심쟁이
·2020. 8. 25. 11:52
반응형
1. insert
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>WebContent/Study/jsp2/jdbcPro.jsp</title>
</head>
<body>
<h1>WebContent/Study/jsp2/jdbcPro.jsp</h1>
<%
request.setCharacterEncoding("utf-8");
//num, name 파라미터 => 서버 request 내장객체 저장
// 1단계 - JDBC 프로그램안에 Driver 프로그램 가져오기
// 2단계 - DriverManager 자바프로그램이 Driver 프로그램을 가지고 디비서버 접속 → 접속정보 저장
//3단계 접속정보를 이용해서 sql구문 만들고 실행 할 수 있는 자바프로그램 생성 (insert, update, delete, select)
Class.forName("com.mysql.jdbc.Driver");
String dbUrl="jdbc:mysql://localhost:3306/jspdb1";
String dbUser = "root"; //db접속아이디
String dbPass ="1234"; //db접속패스워드
Connection con = DriverManager.getConnection(dbUrl,dbUser, dbPass);
//반복사용안해도되게 변수에 담음 // 연결정보니까 connection
//insert into 체이블이름(열이름, 열이름) values(값1, '값2');
//insert into student(num, name) values(1,'kim'');
//statement, PreparedStatement, CallableStatement 자바프로그램
int num = Integer.parseInt(request.getParameter("num"));
String name = request.getParameter("nm");
// String sql = "insert into student (num, name) values ("+num+", '"+name+"')";
//현재 잘사용되지않는 방법 //보안상 좋지않음
String sql = "insert into student (num, name) values (?,?)";
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setInt(1, num); // parameterIndet ? 물음표순서
pstmt.setString(2, name); // parameterIndet ? 물음표순서
//4단계 - sql실행 (insert, update, delete) sql실행 → 결과저장(select)
pstmt.executeUpdate();
%>
학생등록성공 : <%= pstmt %><br>
학생번호 : <%= num %><br>
학생이름 : <%= name %><br>
</body>
</html>2. update
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>WebContent/Study/jsp2/updateFrom.jsp</title>
</head>
<body>
<h1>WebContent/Study/jsp2/updateFrom.jsp</h1>
<%
request.setCharacterEncoding("utf-8");
%>
<form action="updatePro.jsp" method="post">
학생번호 : <input type="text" name="num"><br>
학생이름 : <input type="text" name="name"><br>
<input type="submit" value="수정하기">
</form>
</body>
</html><%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>WebContent/Study/jsp2/updatePro.jsp</title>
</head>
<body>
<h1>WebContent/Study/jsp2/updatePro.jsp</h1>
<%
request.setCharacterEncoding("utf-8");
Class.forName("com.mysql.jdbc.Driver");
String dbUrl="jdbc:mysql://localhost:3306/jspdb1";
String dbUser = "root"; //db접속아이디
String dbPass ="1234"; //db접속패스워드
Connection con = DriverManager.getConnection(dbUrl,dbUser, dbPass); //반복사용안해도되게 변수에 담음 // 연결정보니까 connection
String name = request.getParameter("name");
int num = Integer.parseInt(request.getParameter("num"));
String sql = "update student set name=? where num=?";
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1, name);
pstmt.setInt(2, num);
pstmt.executeUpdate(); //sql실행 → 결과저장(select)
%>
수정성공 : <%=pstmt %><br>
학생번호 :<%=num %><br>
학생이름 :<%=name %><br>
</body>
</html>3. DELETE
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>WebContent/Study/jsp2/deleteFrom.jsp</title>
</head>
<body>
<h1>WebContent/Study/jsp2/deleteFrom.jsp</h1>
<%
request.setCharacterEncoding("utf-8");
%>
<form action="deletePro.jsp" method="post">
학생번호 : <input type="text" name="num"><br>
학생이름 : <input type="text" name="name"><br>
<input type="submit" value="삭제하기">
</form>
</body>
</html><%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>WebContent/Study/jsp2/deletePro.jsp</title>
</head>
<body>
<h1>WebContent/Study/jsp2/deletePro.jsp</h1>
<%
request.setCharacterEncoding("utf-8");
Class.forName("com.mysql.jdbc.Driver");
String dbUrl="jdbc:mysql://localhost:3306/jspdb1";
String dbUser = "root"; //db접속아이디
String dbPass ="1234"; //db접속패스워드
Connection con = DriverManager.getConnection(dbUrl,dbUser, dbPass); //반복사용안해도되게 변수에 담음 // 연결정보니까 connection
String name = request.getParameter("name");
int num = Integer.parseInt(request.getParameter("num"));
String sql = "delete from student where num=? and name=?";
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1, name);
pstmt.setInt(2, num);
pstmt.executeUpdate(); //sql실행 → 결과저장(select)
%>
삭제성공 : <%=pstmt %><br>
</body>
</html>4. SELECT
첫번째 방법) 모든 정보 출력
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>WebContent/Study/jsp2/select.jsp</title>
</head>
<body>
<h1>WebContent/Study/jsp2/select.jsp</h1>
<%
request.setCharacterEncoding("utf-8");
Class.forName("com.mysql.jdbc.Driver");
String dbUrl = "jdbc:mysql://localhost:3306/jspdb1";
String dbUser = "root";
String dbPass = "1234";
Connection con = DriverManager.getConnection(dbUrl, dbUser, dbPass);
String sql ="select * from student";
PreparedStatement pstmt = con.prepareStatement(sql);
//4단계 - sql구문 실행(select) = > rs에 결과저장
ResultSet rs = pstmt.executeQuery(sql);
//5단계 - 결과저장 = > 출력
//rs.next() 다음행 이동 시 데이터가 있으면 true / 데이터가 없으면 false
//while(조건){
//행이동 데이터가 있으면 true반복문안에 명령실행
//조건이 참이면 실행문(열 데이터 뽑아오기)
//}
//행이동 데이터 없으면 false반복문중지
// while(rs.next()){
// out.println(rs.getInt(1) + "," + rs.getString(2)+"<br>"); //1열, 2열
// out.println(rs.getInt("num") + "," + rs.getString("name")+"<br>"); //네임열로도 출력가능 -> 이문법이 더좋음
// }
%>
<h3>학생목록</h3>
<table border="1">
<tr>
<td>학생번호</td>
<td>학생이름</td>
</tr>
<%while(rs.next()){
%>
<tr>
<td><%=rs.getInt("num") %></td>
<td><%= rs.getString("name") %></td>
</tr>
<%}%>
</table>
</body>
</html>두번째 방법) 원하는 정보만 출력
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>WebContent/Study/jsp2/selectForm.jsp</title>
</head>
<body>
<h1>WebContent/Study/jsp2/selectForm.jsp</h1>
<%
request.setCharacterEncoding("utf-8");
%>
<form action="selectPro.jsp" method="post">
조회할 학생번호 : <input type="text" name="num"><br>
<input type="submit" value="조회하기">
</form>
</body>
</html><%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>WebContent/Study/jsp2/selectPro.jsp</title>
</head>
<body>
<h1>WebContent/Study/jsp2/selectPro.jsp</h1>
<%
request.setCharacterEncoding("utf-8");
int num = Integer.parseInt(request.getParameter("num")) ;
Class.forName("com.mysql.jdbc.Driver");
String dbUrl = "jdbc:mysql://localhost:3306/jspdb1";
String dbUser = "root";
String dbPass = "1234";
Connection con = DriverManager.getConnection(dbUrl, dbUser, dbPass);
String sql = "select * from student where num = ?";
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setInt(1,num);
ResultSet rs = pstmt.executeQuery();
%>
<table border="1">
<tr>
<td>학생번호</td>
<td>학생이름</td>
</tr>
<%while(rs.next()){ %>
<tr>
<td><%= rs.getInt("num") %></td>
<td><%=rs.getString("name") %></td>
</tr>
<%} %>
</table>
</body>
</html>반응형
'IT > DB' 카테고리의 다른 글
| [DB] 디비 작업한 것 추출방법(MySql) (0) | 2020.09.24 |
|---|---|
| [DB] ORACLE DATABASE 구조 (0) | 2020.09.04 |
| [DB] 제약조건 5가지(NOT NULL/UNIQUE/PK/FK/CHECK) (0) | 2020.08.24 |
| [DB] 트랜잭션(Transaction)이란? (0) | 2020.08.21 |
| [DB] JDBC란?(MySQL 기준) (0) | 2020.08.20 |
