標籤：java jsp網站 myeclipse dao 資料庫操作
正如前面一篇文章的介紹，當使用Servlet提交表單和JSP資料庫查詢時，總是相互交叉著的處理，要麼在JSP中通過<%...%>內嵌Java代碼操作資料庫，要麼JSP中通過Post方法提交表單Form，在Java中通過Servlet擷取請求/響應，再通過Java中out.println("<HTML>...")輸出資料庫中值。 該項目的結構如所示：
開啟MySQL，輸入預設超級root使用者的密碼，然後資料庫的操作如下代碼：
--建立資料庫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;
其中表結構和表中資料顯示如：
1.在src下建立檔案夾util，然後添加類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", "123456"); //資料庫名 預設使用者 密碼}//串連資料庫 參數:資料庫名 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();}}}
其中主要是調用getConnection(url, userName, password); 方法進行串連資料庫操作，我資料庫的名稱為TestDAO，預設的連線物件為root，密碼為123456。同時定義兩個函數執行無參數的SQL語句操作和有參數的SQL語句操作。
2.在src下建立檔案夾bean，然後添加類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; }}
該Student中的變數及類型與資料庫中一一對應，在通過get和set方法擷取和設定其值。同樣如果你的資料庫中有老師、學校表，你只需要在bean檔案夾下添加Teacher.java和School.java即可。
3.在src下建立檔案夾DAO，然後添加類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;}} 通常DAO（Data Access Object）Data Access Objects是負責與資料庫連接，主要功能執行對資料表的CUDR操作。
然後是WebRoot檔案夾下的jsp代碼。其中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>
然後點擊JDBC操作跳轉到student.jsp操作，代碼如下：涉及EL和JSTL。
<%@ 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><a href="addEmployee.jsp?action=del&id=${stu.id}" onclick="return confirm('確定刪除?')">刪除</a></td></tr></c:forEach></table></form> </body></html>
文章運行結果如所示：
最後總結下，文章主要講述了如何通過DAO和Java Bean實現Java資料庫操作、介面顯示分離的操作；同樣的道理，實現修改、刪除、插入方法類似，後面可能會講述。該方法主要是通過上一篇自己的體會，找到的解決辦法。最後希望文章對你有所協助，如果有錯誤或不足之處，還請海涵~
最重要的一個問題，在這過程中你可能會遇到以下兩個錯誤：(困擾我4小時)
Servlet.service() for servlet [jsp] in context with path
javax.el.PropertyNotFoundException: Property ‘id‘ not found on type bean.Student
其解決方案參考：http://blog.csdn.net/eastmount/article/details/45835481
（By:Eastmount 2015-5-19 淩晨2點 http://blog.csdn.net/eastmount/）
Java+MyEclipse+Tomcat (五)DAO和Java Bean實現資料庫和介面分開操作
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
