728x90
더보기
Day42. 230628
JDBC드라이버 준비하기(라이브러리 추가)
오라클과 MySQL연동을 위해 ojdbc6과 Connector/J를 추가해준다.
1) 오라클 홈디렉토리에서 ojdbc6찾아 추가/없으면 다운 받아 추가
2) Connector/J를 다운받아 추가한다.
https://dev.mysql.com/downloads/connector/j/
MySQL :: Download Connector/J
MySQL Connector/J 8.0 is highly recommended for use with MySQL Server 8.0, 5.7 and 5.6. Please upgrade to MySQL Connector/J 8.0.
dev.mysql.com
두 파일을 드래그해 lib폴더에 추가해준다.
JSP에서 JDBC 프로그래밍
<%@ 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.자원반환
--%>
<%//1.드라이브로딩
try {
//Class.forName("oracle.jdbc.OracleDriver"); //오라클용
Class.forName("com.mysql.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("%5d\t %30s\t %20s\t %20s\t %30s\r\n", mno,mmemberid,mpassword,mname,memail);
}
} 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.자원반납");
%>
<h2>회원목록(p380)</h2>
<table border="1">
<thread>
<tr>
<th>번호</th>
<th>memberid</th>
<th>비밀번호</th>
<th>회원명</th>
<th>email</th>
</tr>
</thread>
<tbody>
<%--회원수만큼 tr요소가 반복=>반복문사용--%>
<%--반복문시작 --%>
<tr>
<td>번호예정</td>
<th>memberid</th>
<th>비밀번호</th>
<th>회원명</th>
<th>email</th>
<%--반복문끝 --%>
</tr>
</tbody>
</table>
</body>
</html>
콘솔창 출력
웹페이지 실행
728x90
'개발 수업 > DB' 카테고리의 다른 글
| 이클립스에서 오라클 데이터베이스 연동 (0) | 2023.06.29 |
|---|---|
| [MySQL] DB를 웹에 출력 (0) | 2023.06.28 |
| [MySQL] MySQL 데이터 베이스 생성,입력,수정,삭제 (0) | 2023.06.28 |
| [MySQL] MySQL Diagram만들기 (0) | 2023.06.28 |
| [MySQL] MySQL 쿼리문 (0) | 2023.06.28 |
