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> View Code
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
