Java Servlet数据库连接案例

Servlets是运行在服务器端的Java小程序,用于处理客户端请求并返回相应的应答。本案例将展示如何使用servelts连接数据库。Servlets是符合Java标准的Java小程序,扩展了javax.servlet和javax.servlet.http包。

项目简介

本案例将从头开始介绍如何使用servlets搭建一个从数据库中读取数据并展示在html页面中的小程序。

项目结构

首先来看看项目结构,

使用maven创建一个空的项目,pom.xml内容为:

4.0.0
JavaServletDbEx
JavaServletDbEx
0.0.1-SNAPSHOT
war ```
### 创建项目
以下的SQL脚本创建一个名为servletDb的数据库并创建一个EmployeeTbl的数据表。可以通过在MySQL命令行中执行之:

```sql
CREATE DATABASE IF NOT EXISTS servletDb;

USE servletDb;

CREATE TABLE IF NOT EXISTS EmployeeTbl (
emp_id VARCHAR(120) NOT NULL,
emp_name VARCHAR(120),
emp_salary VARCHAR(120),
PRIMARY KEY(emp_id)
);

INSERT INTO EmployeeTbl (emp_id, emp_name, emp_salary) VALUES ("101", "Java Code Geek", "2000");
INSERT INTO EmployeeTbl (emp_id, emp_name, emp_salary) VALUES ("102", "April O' Neil", "1500");
INSERT INTO EmployeeTbl (emp_id, emp_name, emp_salary) VALUES ("103", "Harry Potter", "1200");
INSERT INTO EmployeeTbl (emp_id, emp_name, emp_salary) VALUES ("104", "Lucifer Morningstar", "500");
INSERT INTO EmployeeTbl (emp_id, emp_name, emp_salary) VALUES ("105", "Daniel Atlas", "1500");

SELECT * FROM servletDb.EmployeeTbl;

需要为工程添加MySQL和servlet的api依赖:

4.0.0
JavaServletDbEx
JavaServletDbEx
war 0.0.1-SNAPSHOT
JavaServletDbEx Maven Webapp
http://maven.apache.org


javax.servlet
javax.servlet-api
3.1.0


mysql
mysql-connector-java
5.1.41



${project.artifactId}

创建一个DbDemo.java的类,这个类用于查询数据库并且将结果信息返回到HTML页面中。

package com.jcg.servlet;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.ResultSet;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@WebServlet("/servletDbConnectionDemo")
public class DbDemo extends HttpServlet {

private static final long serialVersionUID = 1L;

/***** This Method Is Called By The Servlet Container To Process A 'GET' Request *****/
public void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException {
handleRequest(request, response);
}

public void handleRequest(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException {

/***** Set Response Content Type *****/
response.setContentType("text/html");

/***** Print The Response *****/
PrintWriter out = response.getWriter();
String title = "Employee Details";
String pageTitle = "Servlet Database Connectivity Example";
String docType = "\n";
out.println(docType +
"\n" + "\n");

try {
ResultSet rs = DbDao.getEmployeeList();
if(rs.next()) {
out.println("\n" + "
<h2 align="\"center\"">" + title + "</h2>
\n" +
"

\n" + "\n");do {
out.println("");
} while(rs.next()) ;out.println("\n
<table border="\"1\"" width="\"450px\"" align="\"center\"">
<thead>
<tr align="\"center\"">
<th><strong>Emp. Id</strong></th>
<th><strong>Emp. Name</strong></th>
<th><strong>Emp. Salary (in '$')</strong></th>
</tr>
</thead>
<tbody>
<tr align="\"center\"">
<td>" + rs.getString("emp_id") + "</td>
<td>" + rs.getString("emp_name") + "</td>
<td>" + rs.getString("emp_salary") + "</td>
</tr>
</tbody>
</table>
");
} else {
out.println("\n" + "
<h2 align="\"center\"">No Employees Found In The Db....!</h2>
\n" + "");
}
out.println("");
out.close();
} catch(Exception exObj) {
exObj.printStackTrace();
} finally {
DbDao.disconnectDb();
}
}
}

DbDemo.java的关键就是DbDao.getEmployeeList()方法,它会返回所有雇员的信息。

DbDao.java

package com.jcg.servlet;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class DbDao {

static ResultSet rsObj = null;
static Statement stmtObj = null;
static Connection connObj = null;

/***** Method #1 :: This Method Is Used To Create A Connection With The Database *****/
private static Connection connectDb() {
try {
Class.forName("com.mysql.jdbc.Driver");
connObj = DriverManager.getConnection("jdbc:mysql://localhost:3306/servletDb", "<!-- Db Username --!>", "<!-- Db Password --!>");
} catch (Exception exObj) {
exObj.printStackTrace();
}
return connObj;
}

/***** Method #2 :: This Method Is Used To Retrieve The Records From The Database *****/
public static ResultSet getEmployeeList() {
try {
stmtObj = connectDb().createStatement();

String sql = "SELECT * FROM servletDb.EmployeeTbl";
rsObj = stmtObj.executeQuery(sql);
} catch (Exception exObj) {
exObj.printStackTrace();
}
return rsObj;
}

/***** Method #3 :: This Method Is Used To Close The Connection With The Database *****/
public static void disconnectDb() {
try {
rsObj.close();
stmtObj.close();
connObj.close();
} catch (Exception exObj) {
exObj.printStackTrace();
}
}
}

最后,编译代码,部署war包。执行demo,可以看到如下结果:

Post Author: flygoxc

发表评论

电子邮件地址不会被公开。 必填项已用*标注