数据库的连接，JAVA，Mysql

数据库的连接方法：

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>