5.Java开发_11.一个网页访问数据库的小例子

112 阅读 0 评论 74 点赞

我是靠谱客的博主醉熏酸奶，这篇文章主要介绍5.Java开发_11.一个网页访问数据库的小例子，现在分享给大家，希望可以做个参考。

要求：

1.linux安装mysql并建立一个数据库和一张表，windows上使用Navicat for MySQL可以连接数据库并访问。
2.IntelliJ IDEA建立一个maven工程，搭建一个tomcat服务器，网页可访问。
3.启动工程后，网页访问时候，request带参数字段。
4.服务器后端根据解析后的参数字段，查询数据库，得到结果数据。
4.把结果数据封装在response里面，发给页面。
5.页面展示数据库查询的结果数据。
6.结果数据存储到会话session中。
7.如果session超时，则重新访问数据库。
8.如果sessoin不超时，则直接读取session的数据。

实现：

1. linux安装数据库并访问

2. IntelliJ IDEA创建Maven工程举例

3. 代码

3.1 访问数据库的代码

复制代码

package com.servlet;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class dbTest {
//
private static String driver = "com.mysql.jdbc.Driver";
private static String driver = "com.mysql.cj.jdbc.Driver";
private static String dburl = "jdbc:mysql://192.168.1.152:3306/RUNOOB";
private static String username = "root";
private static String password = "root";
private static String sqlstr = "select * from epet";
public static void main(String[] args) {
dbTest dt = new dbTest();
List<Object> list = dt.executeQuery("100");
System.out.println(list.toString());
}
public List<Object> executeQuery(String id_str){
//1. load driver
try{
Class.forName(driver);
}catch (Exception e){
System.out.println("1:" + e.getMessage());
}
//2.create connection
Connection conn = null;
try {
conn = DriverManager.getConnection(dburl, username, password);
}catch (Exception e){
System.out.println("2:" + e.getMessage());
}
//3.prepare statement
Statement st = null;
try {
st = conn.createStatement();
}catch (Exception e){
System.out.println("3:" + e.getMessage());
}
//4.execute query
ResultSet rs = null;
try
{
String para_str = " where id = " + id_str;
rs = st.executeQuery(sqlstr + para_str);
}catch (Exception e){
System.out.println("4:" + e.getMessage());
}
//5.process resultSet
ResultSetMetaData rsmd = null;
int columnCount = 0;
try{
rsmd = rs.getMetaData();
columnCount = rsmd.getColumnCount();
}catch (Exception e){
System.out.println("5.1:" + e.getMessage());
}
List<Object> list = new ArrayList<Object>();
try{
while(rs.next()){
Map<String,Object> map = new HashMap<String, Object>();
for (int i = 1; i <= columnCount; i++) {
map.put(rsmd.getColumnLabel(i), rs.getObject(i));
}
list.add(map);
System.out.println("map=" + map);
System.out.println("list="+list);
}
}catch (Exception e){
System.out.println("5.2:" + e.getMessage());
}
//6.close
try {
rs.close();
st.close();
conn.close();
}catch (Exception e)
{
System.out.println("6:" + e.getMessage());
}
return list;
}
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
package com.servlet;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class dbTest {
//
private static String driver = "com.mysql.jdbc.Driver";
private static String driver = "com.mysql.cj.jdbc.Driver";
private static String dburl = "jdbc:mysql://192.168.1.152:3306/RUNOOB";
private static String username = "root";
private static String password = "root";
private static String sqlstr = "select * from epet";
public static void main(String[] args) {
dbTest dt = new dbTest();
List<Object> list = dt.executeQuery("100");
System.out.println(list.toString());
}
public List<Object> executeQuery(String id_str){
//1. load driver
try{
Class.forName(driver);
}catch (Exception e){
System.out.println("1:" + e.getMessage());
}
//2.create connection
Connection conn = null;
try {
conn = DriverManager.getConnection(dburl, username, password);
}catch (Exception e){
System.out.println("2:" + e.getMessage());
}
//3.prepare statement
Statement st = null;
try {
st = conn.createStatement();
}catch (Exception e){
System.out.println("3:" + e.getMessage());
}
//4.execute query
ResultSet rs = null;
try
{
String para_str = " where id = " + id_str;
rs = st.executeQuery(sqlstr + para_str);
}catch (Exception e){
System.out.println("4:" + e.getMessage());
}
//5.process resultSet
ResultSetMetaData rsmd = null;
int columnCount = 0;
try{
rsmd = rs.getMetaData();
columnCount = rsmd.getColumnCount();
}catch (Exception e){
System.out.println("5.1:" + e.getMessage());
}
List<Object> list = new ArrayList<Object>();
try{
while(rs.next()){
Map<String,Object> map = new HashMap<String, Object>();
for (int i = 1; i <= columnCount; i++) {
map.put(rsmd.getColumnLabel(i), rs.getObject(i));
}
list.add(map);
System.out.println("map=" + map);
System.out.println("list="+list);
}
}catch (Exception e){
System.out.println("5.2:" + e.getMessage());
}
//6.close
try {
rs.close();
st.close();
conn.close();
}catch (Exception e)
{
System.out.println("6:" + e.getMessage());
}
return list;
}
}

3.2 处理网页请求的代码

复制代码

package com.servlet;
import java.io.IOException;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpSession;
public class firstPage extends javax.servlet.http.HttpServlet {
protected void doPost(javax.servlet.http.HttpServletRequest request, javax.servlet.http.HttpServletResponse response) throws javax.servlet.ServletException, IOException {
request.getRequestDispatcher("/index.jsp").forward(request,response);
}
protected void doGet(javax.servlet.http.HttpServletRequest request, javax.servlet.http.HttpServletResponse response) throws javax.servlet.ServletException, IOException {
//
doPost(request,response);
System.out.println("do Get");
HttpSession ss = request.getSession(); //获取会话
ss.setMaxInactiveInterval(10);//设置会话超时时间，超时则删除属性配置
//如果会话配置为空，则访问数据库
if (null == ss.getAttribute("id")) {
//获取request中的参数字段
System.out.println("request getQueryString :" + request.getQueryString());
String para_str = request.getQueryString();
String id_str = para_str.split("=")[1];
System.out.println("id_str = " + id_str);
//访问数据库
dbTest dt = new dbTest();
List<Object> list = dt.executeQuery(id_str);
System.out.println(list.toString());
//读取数据库查询结果，存储到会话中
try {
Map<String, Object> map = (Map) list.get(0);
request.setAttribute("id", map.get("id"));
request.setAttribute("name", map.get("name"));
ss.setAttribute("id", map.get("id"));
ss.setAttribute("name", map.get("name"));
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
//转到index.jsp也没
request.getRequestDispatcher("/index.jsp").forward(request,response);
}
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
package com.servlet;
import java.io.IOException;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpSession;
public class firstPage extends javax.servlet.http.HttpServlet {
protected void doPost(javax.servlet.http.HttpServletRequest request, javax.servlet.http.HttpServletResponse response) throws javax.servlet.ServletException, IOException {
request.getRequestDispatcher("/index.jsp").forward(request,response);
}
protected void doGet(javax.servlet.http.HttpServletRequest request, javax.servlet.http.HttpServletResponse response) throws javax.servlet.ServletException, IOException {
//
doPost(request,response);
System.out.println("do Get");
HttpSession ss = request.getSession(); //获取会话
ss.setMaxInactiveInterval(10);//设置会话超时时间，超时则删除属性配置
//如果会话配置为空，则访问数据库
if (null == ss.getAttribute("id")) {
//获取request中的参数字段
System.out.println("request getQueryString :" + request.getQueryString());
String para_str = request.getQueryString();
String id_str = para_str.split("=")[1];
System.out.println("id_str = " + id_str);
//访问数据库
dbTest dt = new dbTest();
List<Object> list = dt.executeQuery(id_str);
System.out.println(list.toString());
//读取数据库查询结果，存储到会话中
try {
Map<String, Object> map = (Map) list.get(0);
request.setAttribute("id", map.get("id"));
request.setAttribute("name", map.get("name"));
ss.setAttribute("id", map.get("id"));
ss.setAttribute("name", map.get("name"));
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
//转到index.jsp也没
request.getRequestDispatcher("/index.jsp").forward(request,response);
}
}

3.3 过滤网页请求的代码（暂时无用，只是测试）

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
package com.servlet;
import java.io.IOException;
import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.FilterConfig;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;
import javax.servlet.http.HttpServletRequest;
public class HelloFilter implements Filter {
@Override
public void init(FilterConfig arg0) throws ServletException {
System.out.println("Filter 初始化");
}
@Override
public void doFilter(ServletRequest req, ServletResponse res,
FilterChain chain) throws IOException, ServletException {
HttpServletRequest request = (HttpServletRequest)req;
System.out.println("拦截 URI="+request.getRequestURI());
chain.doFilter(req, res);
}
@Override
public void destroy() {
System.out.println("Filter 结束");
}
}

3.4 应答页面（index.jsp）

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
<html>
<body>
<h2>Hello World-first!</h2>
<%--<h3>${dongjuwei}</h3>--%>
<%--访问会话中的属性数据--%>
<%
//response.getWriter().write("id:"+request.getAttribute("id") + ", name:"+request.getAttribute("name"));
response.getWriter().write("id:"+request.getSession().getAttribute("id") + ", name:"+request.getSession().getAttribute("name"));
%>
</body>
</html>

3.5 访问页面的配置（web.xml）

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
<!DOCTYPE web-app PUBLIC
"-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"
"http://java.sun.com/dtd/web-app_2_3.dtd" >
<web-app>
<display-name>Archetype Created Web Application</display-name>
<filter>
<filter-name>helloFilter</filter-name>
<filter-class>com.servlet.HelloFilter</filter-class>
</filter>
<filter-mapping>
<filter-name>helloFilter</filter-name>
<url-pattern>/abc</url-pattern>
</filter-mapping>
<servlet>
<servlet-name>IndexPage</servlet-name>
<servlet-class>com.servlet.firstPage</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>IndexPage</servlet-name>
<url-pattern>/first-test</url-pattern>
</servlet-mapping>
<welcome-file-list>
<welcome-file>/first-test</welcome-file>
</welcome-file-list>
</web-app>

3.6 Maven的依赖（pom.xml）

复制代码

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>Java_Maven_1</groupId>
<artifactId>Java_Maven_1_t1</artifactId>
<version>1.0-SNAPSHOT</version>
<packaging>war</packaging>
<name>Java_Maven_1_t1 Maven Webapp</name>

<url>http://www.example.com</url>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>1.7</maven.compiler.source>
<maven.compiler.target>1.7</maven.compiler.target>
</properties>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>

</dependency>

<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>4.0.1</version>
</dependency>

<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.12</version>
</dependency>
</dependencies>
<build>
<finalName>Java_Maven_1_t1</finalName>
<pluginManagement>
<plugins>
<plugin>
<artifactId>maven-clean-plugin</artifactId>
<version>3.0.0</version>
</plugin>

<plugin>
<artifactId>maven-resources-plugin</artifactId>
<version>3.0.2</version>
</plugin>
<plugin>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.7.0</version>
</plugin>
<plugin>
<artifactId>maven-surefire-plugin</artifactId>
<version>2.20.1</version>
</plugin>
<plugin>
<artifactId>maven-war-plugin</artifactId>
<version>3.2.0</version>
</plugin>
<plugin>
<artifactId>maven-install-plugin</artifactId>
<version>2.5.2</version>
</plugin>
<plugin>
<artifactId>maven-deploy-plugin</artifactId>
<version>2.8.2</version>
</plugin>
</plugins>
</pluginManagement>
</build>
</project>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>Java_Maven_1</groupId>
<artifactId>Java_Maven_1_t1</artifactId>
<version>1.0-SNAPSHOT</version>
<packaging>war</packaging>
<name>Java_Maven_1_t1 Maven Webapp</name>
<!-- FIXME change it to the project's website -->
<url>http://www.example.com</url>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>1.7</maven.compiler.source>
<maven.compiler.target>1.7</maven.compiler.target>
</properties>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<!--<scope>test</scope>-->
</dependency>
<!-- https://mvnrepository.com/artifact/javax.servlet/javax.servlet-api -->
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>4.0.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.12</version>
</dependency>
</dependencies>
<build>
<finalName>Java_Maven_1_t1</finalName>
<pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) -->
<plugins>
<plugin>
<artifactId>maven-clean-plugin</artifactId>
<version>3.0.0</version>
</plugin>
<!-- see http://maven.apache.org/ref/current/maven-core/default-bindings.html#Plugin_bindings_for_war_packaging -->
<plugin>
<artifactId>maven-resources-plugin</artifactId>
<version>3.0.2</version>
</plugin>
<plugin>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.7.0</version>
</plugin>
<plugin>
<artifactId>maven-surefire-plugin</artifactId>
<version>2.20.1</version>
</plugin>
<plugin>
<artifactId>maven-war-plugin</artifactId>
<version>3.2.0</version>
</plugin>
<plugin>
<artifactId>maven-install-plugin</artifactId>
<version>2.5.2</version>
</plugin>
<plugin>
<artifactId>maven-deploy-plugin</artifactId>
<version>2.8.2</version>
</plugin>
</plugins>
</pluginManagement>
</build>
</project>