正文
JSP_DAO方式实现数据库查询（MyEclipse10,Tomcat7.0,JDK1.7,）——Java Web练习（四）
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
1.项目结构：
2.创建数据库、表、插入记录
create database TestDao; use TestDao; create table student(
stuid int,
username varchar(20),
password varchar(20)
); insert student(stuid,username,password)
values ("10001","Eastmount","111111");
insert student(stuid,username,password)
values ("10002","Yangxiuzhang","123456"); desc student; select * from student;
3.创建类：util的Package下创建JDBCConnect.java:
package util; import java.sql.*;
import com.mysql.jdbc.Driver; public class JDBCConnect { //获取默认数据库连接
public static Connection getConnection() throws SQLException {
return getConnection("TestDAO", "root", "mysql"); //数据库名 默认用户 密码
} //连接数据库 参数:数据库名 root登录名 密码
public static Connection getConnection(String dbName, String userName,
String password) throws SQLException { String url = "jdbc:mysql://localhost:3306/" + dbName
+ "?characterEncoding=utf-8";
//连接MySQL"com.mysql.jdbc.Driver"
DriverManager.registerDriver(new Driver());
return DriverManager.getConnection(url, userName, password);
} //设置 PreparedStatement 参数
public static void setParams(PreparedStatement preStmt, Object... params)
throws SQLException { if (params == null || params.length == 0)
return;
for (int i = 1; i <= params.length; i++) {
Object param = params[i - 1];
if (param == null) {
preStmt.setNull(i, Types.NULL);
} else if (param instanceof Integer) {
preStmt.setInt(i, (Integer) param);
} else if (param instanceof String) {
preStmt.setString(i, (String) param);
} else if (param instanceof Double) {
preStmt.setDouble(i, (Double) param);
} else if (param instanceof Long) {
preStmt.setDouble(i, (Long) param);
} else if (param instanceof Timestamp) {
preStmt.setTimestamp(i, (Timestamp) param);
} else if (param instanceof Boolean) {
preStmt.setBoolean(i, (Boolean) param);
} else if (param instanceof Date) {
preStmt.setDate(i, (Date) param);
}
}
} //执行 SQL，返回影响的行数 异常处理
public static int executeUpdate(String sql) throws SQLException {
return executeUpdate(sql, new Object[] {});
} //带参数执行SQL，返回影响的行数 异常处理
public static int executeUpdate(String sql, Object... params)
throws SQLException { Connection conn = null;
PreparedStatement preStmt = null;
try {
conn = getConnection();
preStmt = conn.prepareStatement(sql);
setParams(preStmt, params);
return preStmt.executeUpdate(); //执行SQL操作
} finally {
if (preStmt != null)
preStmt.close();
if (conn != null)
conn.close();
}
}
}
4.创建类：bean的Package下创建Student.java：
package bean;
public class Student {
private Integer id; //学号
private String name; //姓名
private String password; //密码
public Integer getId() { return id; }
public String getName() { return name; }
public String getPassword() { return password; }
public void setId(Integer id) { this.id = id; }
public void setName(String name) { this.name = name; }
public void setPassword(String pwd) { this.password = pwd; }
}
5.创建类：DAO的Package下创建StudentDAO.java:
package DAO; import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List; import bean.Student;
import util.JDBCConnect; public class StudentDAO { //插入学生
public static int insert(Student stu) throws Exception { String sql = "INSERT INTO student (stuid,username,password) VALUES (?,?,?) ";
return JDBCConnect.executeUpdate(sql, stu.getId(),stu.getName(),stu.getPassword());
} //更新学生姓名
public static int update(Student stu) throws Exception { String sql = "UPDATE student SET stuid = ? WHERE username = ? ";
return JDBCConnect.executeUpdate(sql,stu.getId(),stu.getName());
} //删除操作
public static int delete(Integer id) throws Exception { String sql = "DELETE FROM student WHERE stuid = ? ";
return JDBCConnect.executeUpdate(sql, id);
} //查找记录 某学号
public static Student find(Integer id) throws Exception { String sql = "SELECT * FROM student WHERE stuid = ? ";
Connection conn = null;
PreparedStatement preStmt = null;
ResultSet rs = null; try {
//链接数据库执行SQL语句
conn = JDBCConnect.getConnection(); //连接默认数据库
preStmt = conn.prepareStatement(sql);
preStmt.setInt(1, id);
rs = preStmt.executeQuery();
//获取查询结果
if (rs.next()) {
Student student = new Student();
student.setId(rs.getInt("stuid"));
student.setName(rs.getString("username"));
return student;
} else {
return null;
} } finally { //依次关闭 记录集 声明 连接对象
if (rs != null)
rs.close();
if (preStmt != null)
preStmt.close();
if (conn != null)
conn.close();
}
} //查询所有学生信息
public static List<Student> listStudents() throws Exception { List<Student> list = new ArrayList<Student>();
String sql = "SELECT * FROM student";
Connection conn = null;
PreparedStatement preStmt = null;
ResultSet rs = null; try {
conn = JDBCConnect.getConnection();
preStmt = conn.prepareStatement(sql);
rs = preStmt.executeQuery();
while (rs.next()) {
//设置数据库中表参数 否则报错java.sql.SQLException: Column 'id' not found.
Student student = new Student();
student.setId(rs.getInt("stuid"));
student.setName(rs.getString("username"));
student.setPassword(rs.getString("password"));
list.add(student);
} } finally {
if (rs != null)
rs.close();
if (preStmt != null)
preStmt.close();
if (conn != null)
conn.close();
}
return list;
} }
6.index.jsp:
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%
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 'index.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>
This is my JSP page. <br>
<A href="student.jsp">JDBC操作</A>
</body>
</html>
7.student.jsp
<%@ page language="java" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<jsp:directive.page import="DAO.StudentDAO"/>
<jsp:directive.page import="java.util.List"/>
<%
List studentList = StudentDAO.listStudents();
request.setAttribute("studentList", studentList);
%> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>My JSP 'student.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">
<style type="text/css">
body, td, th, input {font-size:12px; text-align:center; }
</style>
</head> <body>
<form action="operateStudent.jsp" method=get>
<table bgcolor="#CCCCCC" cellspacing=1 cellpadding=5 width=100%>
<tr bgcolor=#DDDDDD>
<th>选择</th>
<th>学号</th>
<th>姓名</th>
<th>密码</th>
<th>操作</th>
</tr> <c:forEach items="${studentList}" var="stu">
<tr bgcolor="#FFFFFF">
<td><input type="checkbox" name="id" value="${stu.id}" /></td>
<td>${stu.id}</td>
<td>${stu.name}</td>
<td>${stu.password}</td>
<td>
<a href="addEmployee.jsp?action=edit&id=${stu.id}">修改</a>
</td>
</tr>
</c:forEach>
</table>
</form>
</body>
</html>
8.效果：
本文引用自：http://blog.csdn.net/eastmount/article/details/45833663
