构建个人博客系统二(连接mysql数据库)

更正上文数据表设计细节不当，外键不能为NULL。

1. CREATE TABLE `article` (
2.   `id` INTEGER(11) NOT NULL AUTO_INCREMENT,
3.   `title` VARCHAR(20) COLLATE utf8_general_ci DEFAULT NULL,
4.   `content` TEXT COLLATE utf8_general_ci,
5.   `categoryid` INTEGER(11) NOT NULL,
6.   `createtime` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
7.   `userid` INTEGER(11) NOT NULL,
8.   PRIMARY KEY (`id`),
9.   KEY `userid` (`userid`),
10.   KEY `article_fk1` (`categoryid`),
11.   CONSTRAINT `article_fk` FOREIGN KEY (`userid`) REFERENCES `user` (`id`),
12.   CONSTRAINT `article_fk1` FOREIGN KEY (`categoryid`) REFERENCES `category` (`id`)
13. )ENGINE=InnoDB
14. AUTO_INCREMENT=4 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
15. COMMENT='InnoDB free: 4096 kB; (`userid`) REFER `sclblog/user`(`id`); (`categoryid`) REFE';

然后建立javabean，用来连接数据库。

 

1. package com.mycompany.db;
3. import java.sql.Connection;
4. import java.sql.DriverManager;
5. import java.sql.ResultSet;
6. import java.sql.SQLException;
7. import java.sql.Statement;
9. public class DBUtil {
10.     public static Connection getConnection() {
11.         Connection conn = null;
12.         try {
13.             Class.forName("com.mysql.jdbc.Driver");
14.             conn = DriverManager
15.                     .getConnection("jdbc:mysql://localhost:3306/sclBlog?user=root&password=root");
16.         } catch (ClassNotFoundException e) {
17.             e.printStackTrace();
18.         } catch (SQLException e) {
19.             e.printStackTrace();
20.         }
22.         return conn;
23.     }
25.     public static Statement getStatement(Connection conn) {
26.         Statement stmt = null;
27.         try {
28.             if (conn != null) {
29.                 stmt = conn.createStatement();
30.             }
31.         } catch (SQLException e) {
32.             e.printStackTrace();
33.         }
35.         return stmt;
36.     }
38.     public static ResultSet getResultSet(Statement stmt, String sql) {
39.         ResultSet rs = null;
40.         try {
41.             if (stmt != null) {
42.                 rs = stmt.executeQuery(sql);
43.             }
44.         } catch (SQLException e) {
45.             e.printStackTrace();
46.         }
48.         return rs;
49.     }
51.     public static void close(Statement stmt) {
52.         if (stmt != null) {
53.             try {
54.                 stmt.close();
55.                 stmt = null;
56.             } catch (SQLException e) {
57.                 e.printStackTrace();
58.             }
59.         }
60.     }
62.     public static void close(Connection conn) {
63.         if (conn != null) {
64.             try {
65.                 conn.close();
66.                 conn = null;
67.             } catch (SQLException e) {
68.                 e.printStackTrace();
69.             }
70.         }
71.     }
73.     public static void close(ResultSet rs) {
74.         if (rs != null) {
75.             try {
76.                 rs.close();
77.                 rs = null;
78.             } catch (SQLException e) {
79.                 e.printStackTrace();
80.             }
81.         }
82.     }
83. }

建立一个servlet，测试

1. package com.mycompany.db;
3. import java.io.IOException;
4. import java.io.PrintWriter;
5. import java.sql.Connection;
6. import java.sql.ResultSet;
7. import java.sql.SQLException;
8. import java.sql.Statement;
9. import javax.servlet.ServletException;
10. import javax.servlet.http.HttpServlet;
11. import javax.servlet.http.HttpServletRequest;
12. import javax.servlet.http.HttpServletResponse;
14. public class TestDb extends HttpServlet {
16.     protected void doGet(HttpServletRequest req, HttpServletResponse resp)
17.             throws ServletException, IOException {
18.         resp.setContentType("text/html;charset=gb2312");
19.         PrintWriter out = resp.getWriter();
21.         out.println("<table border=1>");
22.         out.println("<tr><td>Content:</td></tr>");
24.         Connection conn = DBUtil.getConnection();
25.         Statement stmt = DBUtil.getStatement(conn);
26.         String sql = "select * from article";
27.         ResultSet rs = DBUtil.getResultSet(stmt, sql);
28.         try {
29.             while (rs.next()) {
30.                 out.println("<tr>");
31.                 out.println("<td>" + rs.getString("title") + "</td>");
32.                 out.println("</tr>");
33.             }
34.             out.println("</table>");
35.         } catch (SQLException e) {
36.             e.printStackTrace();
37.         } finally {
38.             DBUtil.close(rs);
39.             DBUtil.close(stmt);
40.             DBUtil.close(conn);
41.         }
42.     }
43. }

web.xml 设置这个servlet。

1. <?xml version="1.0" encoding="UTF-8"?>
2. <web-app version="2.4" xmlns="http://java.sun.com/xml/ns/j2ee"
3.     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
4.     xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee   http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
5.     <servlet>
6.         <servlet-name>testDb</servlet-name>
7.         <servlet-class>com.mycompany.db.TestDb</servlet-class>
8.     </servlet>
9.     <servlet-mapping>
10.       <servlet-name>testDb</servlet-name>
11.       <url-pattern>/testDb</url-pattern>
12.     </servlet-mapping>
13.     <welcome-file-list>
14.         <welcome-file>index.jsp</welcome-file>
15.     </welcome-file-list>
17. </web-app>

测试url：

http://localhost:8080/sclBlog/testDb