[MySQL 드라이버 다운]
- http://www.mysql.com -> Downloads -> Mysql Connectors -> Connector/J
-> mysql-connector-java-5.1.18.zip 다운받은 후 압축풀기
[MySQL Driver의 추가]
- 이클립스 -> Project -> WebContent -> WEB-INF -> lib
-> 'mysql-connector-java-5.1.18-bin' 복사
[성적테이블 생성하기]
- drop table tb_sungjuk; //성적테이블(tb_sungjuk) 삭제하기
create table tb_sungjuk(
sno int NOT NULL AUTO_INCREMENT PRIMARY KEY,
uname varchar(20) not null,
kor int default 0,
eng int default 0,
mat int default 0,
aver int null,
address varchar(10) null
);
- 성적테이블에 레코드 추가하기
insert into tb_sungjuk(uname,kor,eng,mat,address)
values ('개나리',90,80,50,'seoul');
insert into tb_sungjuk(uname,kor,eng,mat,address)
values ('진달래',50,60,72,'jeju');
insert into tb_sungjuk(uname,kor,eng,mat,address)
values ('무궁화',100,50,70,'busan');
insert into tb_sungjuk(uname,kor,eng,mat,address)
values ('봉숭아',70,80,50,'incheon');
insert into tb_sungjuk(uname,kor,eng,mat,address)
values ('해바라기',40,50,10,'suwon');
[실습 : 성적테이블 목록보기]
파일명 : sungjukList.jsp
############################################################################################
<%@ page contentType="text/html; charset=euc-kr"%>
<%@ page import="java.sql.DriverManager"%>
<%@ page import="java.sql.Connection"%>
<%@ page import="java.sql.PreparedStatement"%>
<%@ page import="java.sql.ResultSet"%>
<%
request.setCharacterEncoding("euc-kr");
//-------------------------------------------------------------
String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=euckr";
String id = "root";
String pass = "1234";
String driver="org.gjt.mm.mysql.Driver";
Class.forName(driver);
//물리적인 클래스 파일명을 인자로 전달해 주면 이에 해당하는 클래스를 반환해 준다.
Connection con = DriverManager.getConnection(url, id, pass);
//------------------------------------------------------------
String sql = " SELECT sno,uname,kor,eng,mat ";
sql = sql + " FROM tb_sungjuk ";
sql = sql + " ORDER BY sno DESC";
PreparedStatement pstmt = con.prepareStatement(sql); //쿼리문 생성
ResultSet rs = pstmt.executeQuery(); // SELECT
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-ｅquiv="Content-Type" content="text/html; charset=euc-kr" />
<title> /daum/sungjukList.jsp </title>
</head>
<body>
<center>
** 성적 리스트 **<br>
<table border=1>
<tr>
<td>번호</td>
<td>이름</td>
<td>국어</td>
<td>영어</td>
<td>수학</td>
</tr>
<%
if(rs.next()){
do {
int sno=rs.getInt("sno");
String uname=rs.getString("uname");
int kor=rs.getInt("kor");
int eng=rs.getInt("eng");
int mat=rs.getInt("mat");
%>
<tr>
<td><%=sno%></td>
<td><%=uname%></td>
<td><%=kor%></td>
<td><%=eng%></td>
<td><%=mat%></td>
</tr>
<%
} while(rs.next());
}
else{
%>
<tr>
<td colspan="5">관련 자료 없음</td>
</tr>
<%
}
%>
</table>
<a href="./sungjukForm.jsp">[성적등록]</a>
</center>
</body>
</html>
<%
//------------------------------------------------
try {
if (rs != null) {
rs.close();
}
} catch (Exception e) {
}
try {
if (pstmt != null) {
pstmt.close();
}
} catch (Exception e) {
}
try {
if (con != null) {
con.close();
}
} catch (Exception e) {
}
//------------------------------------------------
%>
##########################################################################################
파일명 : sungjukForm.jsp
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title> D:\JSP_2012AM\tomcat-6.0\webapps\daum\sungjukForm.jsp </title>
</head>
<body>
<form name="myform" method="post" action="sungjukIns.jsp">
이름:<input type="text" name="uname" value="홍길동"><br>
국어:<input type="text" name="kor" value="90"><br>
영어:<input type="text" name="eng" value="80"><br>
수학:<input type="text" name="mat" value="70"><br>
주소:<input type="text" name="address" value="Guri"><br>
<input type="submit" value="등록">
</form>
</body>
</html>
########################################################################################
파일명 : sungjukIns.jsp
<%@ page contentType="text/html; charset=euc-kr"%>
<%@ page import="java.sql.DriverManager"%>
<%@ page import="java.sql.Connection"%>
<%@ page import="java.sql.PreparedStatement"%>
<%@ page import="java.sql.ResultSet"%>
<%
request.setCharacterEncoding("euc-kr");
//-------------------------------------------------------------
String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=euckr";
String id = "root";
String pass = "1234";
String driver="org.gjt.mm.mysql.Driver";
Class.forName(driver);
Connection con = DriverManager.getConnection(url, id, pass);
//------------------------------------------------------------
String sql = " INSERT INTO tb_sungjuk(uname,kor,eng,mat,aver,address) ";
sql = sql + " VALUES (?,?,?,?,?,?) ";
PreparedStatement pstmt = con.prepareStatement(sql);
String uname=request.getParameter("uname");
int kor=Integer.parseInt(request.getParameter("kor"));
int mat=Integer.parseInt(request.getParameter("eng"));
int eng=Integer.parseInt(request.getParameter("mat"));
int aver=(kor+mat+eng)/3;
String address=request.getParameter("address");
pstmt.setString(1,uname);
pstmt.setInt(2,kor);
pstmt.setInt(3,eng);
pstmt.setInt(4,mat);
pstmt.setInt(5,aver);
pstmt.setString(6,address);
int cnt=pstmt.executeUpdate(); //추가: 1, 실패: 0
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-ｅquiv="Content-Type" content="text/html; charset=euc-kr" />
<title> /daum/sungjukIns.jsp </title>
</head>
<body>
<center>
<%
if (cnt == 1){
out.println("등록 성공!!");
}else{
out.println("등록 실패!!");
}
%>
<a href="./sungjukList.jsp">[성적목록]</a>
</center>
</body>
</html>
<%
//------------------------------------------------
try {
if (pstmt != null) {
pstmt.close();
}
} catch (Exception e) {
}
try {
if (con != null) {
con.close();
}
} catch (Exception e) {
}
//------------------------------------------------
%>
'..열심히 공부하세.. > JSP' 카테고리의 다른 글
| [07] 성적테이블 목록, 내용보기 (0) | 2012.02.13 |
|---|---|
| [06] Eclipse Helios 자바 개발 환경 툴 설치 (0) | 2012.02.12 |
| [04] MySQL root계정 비번 변경 및 SQL문 실습 (0) | 2012.02.09 |
| [03] JSP 기초 문법 (0) | 2012.02.06 |
| [02] MySQL 서버 구축하기 (0) | 2012.02.05 |