<%@ page language="java" import="java.util.*"
contentType="text/html; charset=gb2312" pageEncoding="UTF-8"%>
<%@page import="java.sql.*"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme() + "://"
+ request.getServerName() + ":" + request.getServerPort()
+ path + "/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>My JSP 'jdbcinjsp.jsp' starting page</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
</head>
<body>
<%
try {
//装载驱动程序
// Class.forName(com.mysql.jdbc.Driver);
String driverClass = "com.mysql.jdbc.Driver";
//连接字符串
String url = "jdbc:mysql://localhost:8080/mydata";
//建立连接
Connection conn = DriverManager.getConnection(
"jdbc://localhost:8080/test", "root", "root");
out.print("<h4>1.向表中 插入三个新员工记录 </h4>");
PreparedStatement pstmtInsert = conn
.prepareStatement("insert into employee values(?,?,?,?)");
pstmtInsert.setString(1, "Zhou");
pstmtInsert.setString(2, "Kai");
pstmtInsert.setInt(3, 45);
pstmtInsert.setString(4, "no.45 Changgan Road");
pstmtInsert.setString(5, "Beijing");
if(pstmtInsert.executeUpdate()!=0)
out.println("新增加第一个员工成功<br>");
else
out.println("新增加第一个员工失败 <br>");
pstmtInsert.setString(1, "Zhu");
pstmtInsert.setString(2, "Lin");
pstmtInsert.setInt(3, 28);
pstmtInsert.setString(4, "no.28 Zhonghua Road");
pstmtInsert.setString(5, "Beijing");
if (pstmtInsert.executeUpdate() != 0)
out.println("新增加第二个员工成功<br>");
else
out.println("新增加第二个员工失败 <br>");
pstmtInsert.close();
//使用 Statement
Statement stmtInsert = conn.createStatement();
String sql = "insert into employee values('He','Hai',35,'no.23 Garden Road','Shanghai')";
int result = stmtInsert.executeUpdate(sql);
if (result != 0)
out.println("新增加第三个员工成功 <br>");
else
out.println("新增加第三个员工失败 <br>");
stmtInsert.close();
out.println("<h4>2.修改最后一个插入的员工 He Hai 的年龄为50岁 </h4>");
Statement stmtUpdate = conn.createStatement();
sql = "update employee set age=50 where firstname='He'";
result = stmtUpdate.executeUpdate(sql);
if (result != 0)
out.println("修改年龄成功 <br>");
else
out.println("修改年龄失败 <br>");
stmtUpdate.close();
out.print("<h4>3.查询所有员工记录 </h4>");
%>
<table border=1 cellspacing=1>
<tr>
<td>
firstname
</td>
<td>
lastname
</td>
<td>
age
</td>
<td>
address
</td>
<
<td>
city
</td>
</tr>
<%
Statement stmtSelect = conn.createStatement();
ResultSet rs = stmtSelect
.executeQuery("select * from employee");
while (rs != null && rs.next()) {
out.print("<tr><td>" + rs.getString("firstname") + "</td>");
out.print("<td>" + rs.getString("lastname") + "</td>");
out.print("<td>" + rs.getInt("age") + "</td>");
out.print("<td>" + rs.getString("address") + "</td>");
out.print("<td>" + rs.getString("city") + "</td></tr>");
}
rs.close();
stmtSelect.close();
%>
</table>
<%
out.print("<h4>4.删除最后一个的员工 He Hai</h4>");
Statement stmtDelete = conn.createStatement();
sql = "delete from employee where firstname=='He'";
result = stmtDelete.executeUpdate(sql);
if (result != 0)
out.println("删除成功 <br>");
else
out.println("删除失败 <br>");
stmtDelete.close();
conn.close();
}/* catch(ClassNotFoundException cnfe){
out.print(cnfe);
}*/catch (SQLException sqle) {
out.print(sqle);
} catch (Exception e) {
out.print(e);
}
%>
</body>
</html>
