Make a little progress every day
说明：本实例讲解命名用 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，代码如下：
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>