Servlet 資料庫訪問
從基本概念下手，讓我們來創建一個簡單的表，並在表中創建幾條記錄。
創建測試數據
接下來我們在 MySQL 中創建 RUNOOB 資料庫，並創建 websites 數據表，表結構如下：
CREATE TABLE `websites` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL DEFAULT "" COMMENT "站點名稱",
`url` varchar(255) NOT NULL DEFAULT "",
`alexa` int(11) NOT NULL DEFAULT "0" COMMENT "Alexa 排名",
`country` char(10) NOT NULL DEFAULT "" COMMENT "國家",
PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
插入一些數據：
INSERT INTO `websites` VALUES ("1", "Google", "https://www.google.cm/", "1", "USA"), ("2", "淘寶", "https://www.taobao.com/", "13", "CN"), ("3", "菜鳥教程", "http://www.runoob.com", "5892", ""), ("4", "微博", "http://weibo.com/", "20", "CN"), ("5", "Facebook", "https://www.facebook.com/", "3", "USA");
訪問資料庫
下面的實例演示了如何使用 Servlet 訪問 RUNOOB 資料庫。
package com.runoob.test;import java.io.IOException;import java.io.PrintWriter;import java.sql.*;import javax.servlet.ServletException;import javax.servlet.annotation.WebServlet;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;/**
* Servlet implementation class DatabaseAccess
*/@WebServlet("/DatabaseAccess")public class DatabaseAccess extends HttpServlet { private static final long serialVersionUID = 1L; // JDBC 驅動名及資料庫 URL static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String DB_URL = "jdbc:mysql://localhost:3306/RUNOOB";
// 資料庫的用戶名與密碼，需要根據自己的設置 static final String USER = "root"; static final String PASS = "123456";
/**
* @see HttpServlet#HttpServlet()
*/
public DatabaseAccess() {
super();
// TODO Auto-generated constructor stub
} /**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/ protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { Connection conn = null; Statement stmt = null; // 設置響應內容類型 response.setContentType("text/html;charset=UTF-8"); PrintWriter out = response.getWriter(); String title = "Servlet Mysql 測試 - 菜鳥教程"; String docType = "<!DOCTYPE html>
"; out.println(docType + "<html>
" + "<head><title>" + title + "</title></head>
" + "<body bgcolor="#f0f0f0">
" + "<h1 align="center">" + title + "</h1>
"); try{ // 註冊 JDBC 驅動器 Class.forName("com.mysql.jdbc.Driver");
// 打開一個連接 conn = DriverManager.getConnection(DB_URL,USER,PASS); // 執行 SQL 查詢 stmt = conn.createStatement(); String sql; sql = "SELECT id, name, url FROM websites"; ResultSet rs = stmt.executeQuery(sql); // 展開結果集資料庫 while(rs.next()){ // 通過欄位檢索 int id = rs.getInt("id"); String name = rs.getString("name"); String url = rs.getString("url");
// 輸出數據 out.println("ID: " + id); out.println(", 站點名稱: " + name); out.println(", 站點 URL: " + url); out.println("<br />"); } out.println("</body></html>"); // 完成後關閉 rs.close(); stmt.close(); conn.close(); } catch(SQLException se) { // 處理 JDBC 錯誤 se.printStackTrace(); } catch(Exception e) { // 處理 Class.forName 錯誤 e.printStackTrace(); }finally{ // 最後是用於關閉資源的塊 try{ if(stmt!=null) stmt.close(); }catch(SQLException se2){ } try{ if(conn!=null) conn.close(); }catch(SQLException se){ se.printStackTrace(); } }
} /**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/ protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub doGet(request, response); }}
現在讓我們來編譯上面的 Servlet，並在 web.xml 文件中創建以下條目：
.... <servlet> <servlet-name>DatabaseAccess</servlet-name> <servlet-class>com.runoob.test.DatabaseAccess</servlet-class> </servlet> <servlet-mapping> <servlet-name>DatabaseAccess</servlet-name> <url-pattern>/TomcatTest/DatabaseAccess</url-pattern> </servlet-mapping>....
TAG:程序員小新人學習 |