数据库的连接方法:
Mysq连接实例:
1.数据库库的建立:
create database bbs;
use bbs;
create table article
(
id int primary key auto_increment,
pid int,
rootid int,
title varchar(255),
cont text,
pdate datetime,
isleaf int
);
insert into article values (null, 0, 1, '蚂蚁大战大象', '蚂蚁大战大象', now(), 1);
insert into article values (null, 1, 1, '大象被打趴下了', '大象被打趴下了',now(), 1);
insert into article values (null, 2, 1, '蚂蚁也不好过','蚂蚁也不好过', now(), 0);
insert into article values (null, 2, 1, '瞎说', '瞎说', now(), 1);
insert into article values (null, 4, 1, '没有瞎说', '没有瞎说', now(), 0);
insert into article values (null, 1, 1, '怎么可能', '怎么可能', now(), 1);
insert into article values (null, 6, 1, '怎么没有可能', '怎么没有可能', now(), 0);
insert into article values (null, 6, 1, '可能性是很大的', '可能性是很大的', now(), 0);
insert into article values (null, 2, 1, '大象进医院了', '大象进医院了', now(), 1);
insert into article values (null, 9, 1, '护士是蚂蚁', '护士是蚂蚁', now(), 0);
2.JAVA对数据库的访问:
import java.sql.*;
public class ArticleTree {
public static void main(String[] args) {
new ArticleTree().show();
}
public void show() {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager
.getConnection("jdbc:mysql://localhost/bbs?user=root&password=root");
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from article where pid = 0");
while(rs.next()){
System.out.println(rs.getString("cont"));
tree(conn, rs.getInt("id"), 1);
}
} catch(ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if(rs != null) {
rs.close();
rs = null;
}
if(stmt != null) {
stmt.close();
stmt = null;
}
if(conn != null) {
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
private void tree(Connection conn, int id, int level) {
Statement stmt = null;
ResultSet rs = null;
StringBuffer strPre = new StringBuffer("");
for(int i=0; i<level; i++) {
strPre.append(" ");
}
try {
stmt = conn.createStatement();
String sql = "select * from article where pid = " + id;
rs = stmt.executeQuery(sql);
while(rs.next()) {
System.out.println(strPre + rs.getString("cont"));
if(rs.getInt("isleaf") != 0)
tree(conn, rs.getInt("id"), level + 1);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if(rs != null) {
rs.close();
rs = null;
}
if(stmt != null) {
stmt.close();
stmt = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
3,网页对数据库的访问
<%@ page language="java" import="java.util.*"
contentType="text/html;charset=gbk" pageEncoding="gbk"%>
<%@ page import="java.sql.*"%>
<%!String str = "";
private void tree(Connection conn, int id, int level) {
Statement stmt = null;
ResultSet rs = null;
String preStr = "";
for (int i = 0; i < level; i++) {
preStr += "-----";
}
try {
stmt = conn.createStatement();
String sql = "select * from article where pid = " + id;
rs = stmt.executeQuery(sql);
while (rs.next()) {
str += "<tr><td>" + rs.getInt("id") + "</td><td>" + preStr
+ rs.getString("cont") + "</td><tr>";
if (rs.getInt("isleaf") != 0) {
tree(conn, rs.getInt("id"), level + 1);
}
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
rs = null;
}
if (stmt != null) {
stmt.close();
stmt = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}%>
<%
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost/bbs?user=root&password=root";
Connection conn = DriverManager.getConnection(url);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from article where pid = 0");
while (rs.next()) {
str += "<tr><td>" + rs.getInt("id") + "</td><td>"
+ rs.getString("cont") + "</td></tr>";
if (rs.getInt("isleaf") != 0) {
tree(conn, rs.getInt("id"), 1);
}
}
rs.close();
stmt.close();
conn.close();
%>
<%
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%>">
<meta http-equiv="Content-Type" content="text/html; charset=gbk">
<title>My first web</title>
</head>
<body>
<table border = 1>
<%=str%>
</table>
</body>
</html>