JAVA-WEB-使用JSP从数据库读取数据并展示到网页上

By Diskobólos at: 2020-02-15

JAVA WEB 使用JSP从数据库读取数据并展示到网页上

2020-02-15

整个项目结构，jar包可网上下载导入

数据库链接以及增删改查方法

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
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
​package com.Podcast.dbutil;

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

public class DBconn {
	//三属性，四方法
	
	//三大核心接口
	private Connection conn=null;
	private PreparedStatement pstmt=null;
	private ResultSet rs=null;
	
	String driver="com.mysql.jdbc.Driver";
	String url="jdbc:mysql://localhost:3306/podcast";
	String user="root";
	String password="123456";
	//四个方法
	//method1：创建数据库的连接
	private void getConntion(){
		try{
			//加载连接驱动
			Class.forName(driver);
			//连接mysql数据库
			conn=DriverManager.getConnection(url,user,password);
		} catch(ClassNotFoundException e){
			e.printStackTrace();
		} catch(SQLException e){
			e.printStackTrace();
		}
	}
	
	//method2:关闭数据库连接
	public void closeConn(){
		if(rs!=null){
			try{
				rs.close();
			} catch(SQLException e){
				e.printStackTrace();
			}
		}
		if(pstmt!=null){
			try{
				pstmt.close();
				
			} catch(SQLException e){
				e.printStackTrace();
			}
		}
		if(conn!=null){
			try{
				conn.close();
			} catch(SQLException e){
				e.printStackTrace();
			}
		}
		
	}
	
	//method3:专门用于发送增删改语句的方法
	public int execOther(final String strSQL, final Object[] params){
		//连接
		getConntion();
		System.out.println("SQL:>"+strSQL);
		try{
			//创建statement接口对象
			pstmt=conn.prepareStatement(strSQL);
			//动态为pstmt对象赋值
			for(int i=0;i<params.length;i++){
				pstmt.setObject(i+1, params[i]);
			}
			//使用Statement对象发送SQL语句
			int affectedRows=pstmt.executeUpdate();
			return affectedRows;
			
			
		} catch(SQLException e){
			e.printStackTrace();
			return -1;
		}
	}
	
	//method4:专门用于发送查询语句
	public ResultSet execQuery(final String strSQL,final Object[] params){
		getConntion();
		System.out.println("SQL:>"+strSQL);
		try{
			pstmt=conn.prepareStatement(strSQL);
			for(int i=0;i<params.length;i++){
				pstmt.setObject(i+1, params[i]);
			}
			rs=pstmt.executeQuery();
			return rs;
		} catch(SQLException e){
			e.printStackTrace();
			return null;
		}
	}
	
}

实体类

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
package com.Podcast.entity;

public class PostBar {
	private int Pid;//帖子id
	private String Topic;//帖子主题
	private String Message;//帖子内容
	private String PDate;//帖子发布日期
	private String ChangeDate;//帖子修改日期
	private int Uid;//发布帖子的用户ID
	private String Pkind;//帖子类型
	
	public int getPid() {
		return Pid;
	}
	public void setPid(int pid) {
		Pid = pid;
	}
	public String getTopic() {
		return Topic;
	}
	public void setTopic(String topic) {
		this.Topic = topic;
	}
	public String getMessage() {
		return Message;
	}
	public void setMessage(String message) {
		this.Message = message;
	}
	public String getDdate() {
		return PDate;
	}
	public void setPDate(String pdate) {
		PDate = pdate;
	}
	public String getChangeDate() {
		return ChangeDate;
	}
	public void setChangeDate(String changeDate) {
		ChangeDate = changeDate;
	}
	public int getUid() {
		return Uid;
	}
	public void setUid(int uid) {
		Uid = uid;
	}
	public String getPkinnd() {
		return Pkind;
	}
	public void setPkind(String pkind) {
		Pkind = pkind;
	}
}

读取数据库数据具体方法

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
package com.Podcast.Dao;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.PreparedStatement;

import com.Podcast.entity.*;
import com.Podcast.dbutil.*;

public class GetPostDao {

	public List<PostBar> GetPost(int Pid){
		
		List<PostBar> lstpost=new ArrayList<PostBar>();
		DBconn dbconn=new DBconn();
		String strSQL="select * from posttopic where Pid=?";
		ResultSet rs=dbconn.execQuery(strSQL,new Object[]{Pid});
		try{
			while(rs.next()){
				PostBar postbar=new PostBar();
				postbar.setPid(rs.getInt("Pid"));
				postbar.setTopic(rs.getString("Topic"));
				postbar.setMessage(rs.getString("Message"));
				postbar.setPDate(rs.getString("PDate"));
				postbar.setChangeDate(rs.getString("ChangeDate"));
				postbar.setUid(rs.getInt("Uid"));
				postbar.setPkind(rs.getString("PKind"));
				lstpost.add(postbar);
			}
			return lstpost;
		} catch(Exception e){
			e.printStackTrace();
			return null;
		}finally{
			dbconn.closeConn();
		}
		
	}
	
}

servlet方法

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
package com.Podcast.servlet;

import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;

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

import com.Podcast.Dao.GetPostDao;

/**
 * Servlet implementation class PostBar
 */
@WebServlet("/PostBar")
public class PostBarServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public PostBarServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		GetPostDao gpd=new GetPostDao();
		
		List list=gpd.GetPost(4);
		request.setAttribute("list",list);
		
		request.getRequestDispatcher("/PostBar.jsp").forward(request, response);
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
	}

}

jsp页面

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
<%@ page language="java" contentType="text/html; charset=utf-8"
    pageEncoding="utf-8"%>
<%@ taglib uri = "http://java.sun.com/jsp/jstl/core" prefix = "c" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>贴吧</title>
</head>
<body>

<form action="servlet/PostBarServlet" method="get" name="postform">
<table border=1 cellpadding="10" cellspacing="0">
<c:forEach items="${list}" var="post">
	<tr>
		<td>id</td>
		<td>${post.pid }</td>
	</tr>
	<tr>
		<td>主题</td>
		<td><a href="PostDetail.jsp">${post.topic }</a></td>
	</tr>
	<tr>
	 <td>内容</td>
	 <td>${post.message }</td>
	</tr>
</c:forEach>
</table>
</form>
</body>
</html>

servlet注册xml文件

1
2
3
4
5
6
7
8
9
10
<?xml version="1.0" encoding="UTF-8"?>
<servlet>
	<servlet-name>PostBarServlet</servlet-name>
	<servlet-class>com.Podcast.servlet.PostBarServlet</servlet-class>
</servlet>

<servlet-mappping>
	<servlet-name>PostBarServlet</servlet-name>
	<url-pattern>/servlet/PostBarServlet</url-pattern>
</servlet-mappping>

至此，我们就实现了使用jsp从数据库读取数据并展示到网页上！