当前位置：网站首页>jsp中使用JDBC连接mysql的方法与实例

jsp中使用JDBC连接mysql的方法与实例

2022-08-10 23:05:00 【编程工人】

说明：本实例讲解命名用 mysql-connector-java-*.*.*-bin.jar ,链接mysql 并查询表中的数据，表中的id,title,content字段，表名为 article

1，新建一个 Web Project ，这个不多说了

2，下载 mysql-connector-java-*.*.*-bin.jar ，下载地址：https://pan.baidu.com/s/1i5mTlyd

3，将下载好的 jar包放到项目WEB-INF\lib 目录中

4，创建DBHelper类，代码如下：

package cn.form1;

import java.sql.Connection;

import java.sql.DriverManager;

public class DBhelper {

private static final String driver = "com.mysql.jdbc.Driver"; // 数据库驱动

// 连接数据库的URL地址

private static final String url = "jdbc:mysql://127.0.0.1:3306/testdata?useUnicode=true&characterEncoding=UTF-8";

private static final String username = "root";// 数据库的用户名

private static final String password = "123456";// 数据库的密码

private static Connection conn = null;

// 静态代码块负责加载驱动

static {

try {

Class.forName(driver);

} catch (Exception ex) {

ex.printStackTrace();

}

// 单例模式返回数据库连接对象

public static Connection getConnection() throws Exception {

if (conn == null) {

conn = DriverManager.getConnection(url, username, password);

return conn;

}

return conn;

}

// 测试数据连接

public static void main(String[] args) {

try {

Connection conn = DBhelper.getConnection();

if (conn != null) {

System.out.println("数据库连接正常！");

} else {

System.out.println("数据库连接异常！");

}

} catch (Exception ex) {

ex.printStackTrace();

}

5，创建 Article 类，对应数据表的类，代码如下：

package cn.form1;

public class Article {

private int id;

private String title;

private String content;

public int getId() {

return id;

}

public void setId(int id) {

this.id = id;

}

public String getTitle() {

return title;

}

public void setTitle(String title) {

this.title = title;

}

public String getContent() {

return content;

}

public void setContent(String content) {

this.content = content;

}

6，创建业务逻辑类ArticleDAO，代码如下：

100

101

102

103

104

105

package cn.form1;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.util.ArrayList;

//文档的业务逻辑类

public class ArticleDAO {

// 获得所有的文档信息

public ArrayList<Article> getAllItems() {

Connection conn = null;

PreparedStatement stmt = null;

ResultSet rs = null;

ArrayList<Article> list = new ArrayList<Article>(); // 文档集合

try {

conn = DBhelper.getConnection();

String sql = "select id,title,content from yi_article order by id desc limit 50;"; // SQL语句

stmt = conn.prepareStatement(sql);//执行sql

rs = stmt.executeQuery();//返回集合

while (rs.next()) { // 集合的下一个元素

Article item = new Article();

item.setId(rs.getInt("id"));

item.setTitle(rs.getString("title"));

item.setContent(rs.getString("content"));

list.add(item);// 把一个文档信息加入集合

}

return list; // 返回集合。

} catch (Exception ex) {

ex.printStackTrace();

return null;

} finally {

// 释放数据集对象

if (rs != null) {

try {

rs.close();

rs = null;

} catch (Exception ex) {

ex.printStackTrace();

}

// 释放语句对象

if (stmt != null) {

try {

stmt.close();

stmt = null;

} catch (Exception ex) {

ex.printStackTrace();

}

//conn因为是单例，所以不能被close

}

// 根据文档编号获得文档资料

public Article getItemsById(int id) {

Connection conn = null;

PreparedStatement stmt = null;

ResultSet rs = null;

try {

conn = DBhelper.getConnection();

String sql = "select id,title,content from yi_article where id=?;"; // SQL语句

stmt = conn.prepareStatement(sql);

stmt.setInt(1, id);//设置参数

rs = stmt.executeQuery();

if (rs.next()) {

Article item = new Article();

item.setId(rs.getInt("id"));

item.setTitle(rs.getString("title"));

item.setContent(rs.getString("content"));

return item;

} else {

return null;

}

} catch (Exception ex) {

ex.printStackTrace();

return null;

} finally {

// 释放数据集对象

if (rs != null) {

try {

rs.close();

rs = null;

} catch (Exception ex) {

ex.printStackTrace();

}

// 释放语句对象

if (stmt != null) {

try {

stmt.close();

stmt = null;

} catch (Exception ex) {

ex.printStackTrace();

}

//conn因为是单例，所以不能被close

}

7，jsp页面调用代码 index.jsp 与 detail.jsp，如下：

index.jsp

<%@ page language="java" import="java.util.*" contentType="text/html; charset=utf-8"%>

<%@ page import="cn.form1.*" %>

<%

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>DocumentList</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">

</head>

<body>

<h3>DocumentList</h3>

<hr />

<div>

<ul>

<%

ArticleDAO article = new ArticleDAO();

ArrayList<Article> articleList = new ArrayList<Article>();

articleList = article.getAllItems();

if(articleList != null && articleList.size() != 0){

for(Article rs : articleList){

%>

<li><a href="detail.jsp?id=<%=rs.getId()%>">1. <%=rs.getTitle()%></a></li>

<% }} %>

</ul>

</div>

</body>

</html>

detail.jsp

<%@ page language="java" import="java.util.*" contentType="text/html; charset=utf-8"%>

<%@ page import="cn.form1.*" %>

<%

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>DocumentDetail</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">

</head>

<%

ArticleDAO article = new ArticleDAO();

Article rs = article.getItemsById(Integer.parseInt(request.getParameter("id")));

%>

<body>

<h3><%=rs.getTitle()%></h3>

<hr />

<div style="width:800px;">

<%=rs.getContent()%>

</div>

</body>

</html>

原网站

版权声明
本文为[编程工人]所创，转载请带上原文链接，感谢
https://blog.csdn.net/zjwlgr/article/details/126264348

当前位置：网站首页>jsp中使用JDBC连接mysql的方法与实例

jsp中使用JDBC连接mysql的方法与实例

边栏推荐

猜你喜欢

随机推荐