一个简单的练习,没有做什么前台,用到了递归,参数传递,数据库操作,session以及字符编码转换。
其中有两个主要问题费了一些时间:
1.ResultSet对象的getRow()方法是返回指针指向的行,next()可以移动到下一行
2.字符转换的问题可以用 new String(Str.getBytes("ISO-8859-1")) ,有时候request.setCharacterEncoding("GB2312")转换的时候不是很有效。还是太菜,有待考证。
- --bbs.sql
- --创建数据库
- create database bbs;
- use bbs;
- --创建表
- --使用
- create table article(
- id int primary key auto_increment,
- --父节点id
- pid int,
- --根结点id
- rootid int,
- title varchar(255),
- cont text,
- pdate datetime,
- --是否是叶子节点
- isleaf int
- );
- --0 代表leaf ,1代表非leaf
- 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);
- //ShowArticleTree.jsp
- <%@ page language="java" contentType="text/html;charset=gb2312" pageEncoding="gb2312"%>
- <%@ page import="java.sql.*" %>
- <%
- String path = request.getContextPath();
- String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
- Class.forName("com.mysql.jdbc.Driver");
- String url = "jdbc:mysql://localhost:3306/bbs?user=root&password=root";
- Connection conn = DriverManager.getConnection(url);
- Statement smt = conn.createStatement();
- ResultSet rs = smt.executeQuery("select * from article where pid=0");
- while( rs.next() ){
- str = "<tr><td>" + rs.getInt("id") + "</td><td>" +
- "<a href='ArticleDetail.jsp?id=" + rs.getInt("id") + "'>"
- + rs.getString("title") + "</a>" + "</td><td>" + "<a href='Delete.jsp?id=" +
- rs.getInt("id") + "'>" + "删除</a>"+ "</td></tr>";
- if( rs.getInt("isleaf") != 0){
- Tree(conn,rs.getInt("id"),1);
- }
- }
- rs.close();
- smt.close();
- conn.close();
- %>
- <%!
- String str="";
- private void Tree(Connection conn,int id,int level) {
- String preString = "";
- Statement stm = null;
- ResultSet rs = null;
- for(int i=0;i<level;i++){
- preString += "----";
- }
- try{
- stm = conn.createStatement();
- rs = stm.executeQuery("select * from article where pid="+ id );
- while(rs.next()){
- str += "<tr><td>" + rs.getInt("id") + "</td><td>" +
- preString + "<a href='ArticleDetail.jsp?id=" + rs.getInt("id") + "'>"
- + rs.getString("title") + "</a></td>"+ "<td>" + "<a href='Delete.jsp?id=" +
- rs.getInt("id") + "'>" + "删除</a>"+ "</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(stm != null){
- stm.close();
- stm = null;
- }
- }catch(SQLException e){
- e.printStackTrace();
- }
- }
- }
- %>
- <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
- <html>
- <head>
- <base href="<%=basePath%>">
- <title>论坛的树状结构</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">
- <!--
- <link rel="stylesheet" type="text/css" href="styles.css">
- -->
- </head>
- <body>
- This is my BBS page. <br>
- <table border="1">
- <%= str %>
- </table>
- </body>
- </html>
- //ArticleDetail.jsp
- <%@ page language="java" contentType="text/html;charset=gbk" pageEncoding="gbk"%>
- <%@ page import=" java.sql.* " %>
- <%
- String path = request.getContextPath();
- String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
- String paraId=request.getParameter("id");
- int id = Integer.parseInt(paraId);
- Class.forName("com.mysql.jdbc.Driver");
- String url = "jdbc:mysql://localhost:3306/bbs?user=root&password=root";
- Connection conn = DriverManager.getConnection(url);
- Statement smt = conn.createStatement();
- ResultSet rs = smt.executeQuery("select * from article where id=" + id);
- %>
- <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
- <html>
- <head>
- <base href="<%=basePath%>">
- <title>主题的标题和内容</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">
- <!--
- <link rel="stylesheet" type="text/css" href="styles.css">
- -->
- </head>
- <body>
- <%
- if( rs.next() ) {
- %>
- <table border=1>
- <tr>
- <td>ID</td>
- <td><%= rs.getInt("id") %></td>
- </tr>
- <tr>
- <td>Title</td>
- <td><%= rs.getString("title") %></td>
- </tr>
- <tr>
- <td>Cont</td>
- <td><%=rs.getString("Cont")%></td>
- </tr>
- </table>
- <a href="Reply.jsp?id=<%=rs.getInt("id") %>&rootid=<%=rs.getInt("rootid") %>">回复</a>
- <a href="Delete.jsp?id=<%=rs.getInt("id") %>">删除</a>
- <% } %>
- <%
- rs.close();
- smt.close();
- rs.close();
- %>
- </body>
- </html>
- //Delete.jsp
- <%@ page language="java" contentType="text/html;charset=GB2312" pageEncoding="GB2312"%>
<%@ page import="java.sql.*" %> - <%!
private void delete(Connection conn,int id) {
Statement stm = null;
ResultSet rs = null;
try{
stm = conn.createStatement();
String sql = "select * from article where pid=" + id;
rs = stm.executeQuery(sql);
while( rs.next() ) {
delete(conn , rs.getInt("id") );
}
stm.executeUpdate("delete from article where id=" + id);
}catch(SQLException e){
e.printStackTrace();
}finally{
try {
if( rs != null) {
rs.close();
rs = null;
}
if( stm != null ) {
stm.close();
stm = null;
}
}catch(SQLException e){
e.printStackTrace();
}
}
} - %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";- int id = Integer.parseInt(request.getParameter("id"));
int pid = Integer.parseInt(request.getParameter("pid")); - Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/bbs?user=root&password=root";
Connection conn = DriverManager.getConnection(url);
conn.setAutoCommit(false); - delete(conn,id);
- conn.commit();
conn.setAutoCommit(true); - String sql = "select count(*) from article where pid=" + pid;
Statement stm1 = conn.createStatement();
ResultSet rs = stm1.executeQuery(sql);
rs.next();
int count = rs.getInt(1);
if(count <=0 ) {
stm1.executeUpdate("update article set isleaf=0 where id=" + pid);
}
rs.close();
stm1.close();
conn.close();
%> - <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>My JSP 'Delete.jsp' starting page</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">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
--> - </head>
<body>
<% response.sendRedirect("ShowArticleTree.jsp"); %>
</body>
</html>
- //Reply.jsp
- <%@ page language="java" contentType="text/html; charset=GB2312"
- pageEncoding="GB2312"%>
- <%
- String path = request.getContextPath();
- String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
- int id = Integer.parseInt(request.getParameter("id"));
- int rootid = Integer.parseInt(request.getParameter("rootid"));
- %>
- <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
- <html>
- <head>
- <base href="<%=basePath%>">
- <title>回复页面</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">
- <!--
- <link rel="stylesheet" type="text/css" href="styles.css">
- -->
- </head>
- <body>
- <form action="ReplyOk.jsp">
- <input type="hidden" name="id" value="<%=id %>" />
- <input type="hidden" name="rootid" value="<%=rootid %>" />
- <input type="hidden" name="testString" value="回答是开放" />
- <table border="1">
- <tr>
- <td><input type="text" name="title" size="80" /></td>
- </tr>
- <tr>
- <td><textarea rows="15" cols="80" name="cont"></textarea></td>
- </tr>
- <tr>
- <td><input type="submit" value="回复" /></td>
- </tr>
- </table>
- </form>
- </body>
- </html>