Note: to create a message book using JSP + MySQL databases, first create a MySQL database. for MySQL usage, go to pinghui.51.netdownload012mysql. chm to download the tutorial. Now, for the purpose of illustration, we assume that the database has been created and the database name is pinghui, where the comment table records the message. Database Structure Description: with JSP + MySQL database to create a message book, first of all to create a MySQL database, the use of MySQL please go to the http://pinghui.51.net/download/012mysql.chm download tutorial. Now, for the purpose of illustration, we assume that the database has been created and the database name is pinghui, where the comment table records the message.
The database structure is:
+ ----------- + ------------- + ------ + ----- + --------------------- + ---------------- +
| Field | Type | Null | Key | Default | Extra |
+ ----------- + ------------- + ------ + ----- + --------------------- + ---------------- +
| Userid | int (11) | PRI | 0 | auto_increment |
| Username | char (20) |
| Sex | char (2) |
| Address | char (40) | YES | NULL |
| Ip | char (15) |
| Post | int (11) | YES | 0 |
| Oicq | int (11) | YES | 0 |
| Icq | int (11) | YES | 0 |
| Telnumber | char (30) | YES | NULL |
| Comment | text | NULL |
| Time | datetime | 0000-00-00 00:00:00 |
+ ----------- + ------------- + ------ + ----- + --------------------- + ---------------- +
If you think creating a database is too troublesome
The statement for creating a database is:
Drop database if exists pinghui;
Create database pinghui;
USE pinghui;
Create table comment (
Userid int not null default 0 AUTO_INCREMENT primary key,
Username char (20) not null,
Sex char (2 ),
Address char (40 ),
Ip char (15) not null,
Post int DEFAULT 0,
Oicq int DEFAULT 0,
Icq int DEFAULT 0,
Telnumber char (30 ),
Comment text not null,
Time datetime NOT NULL
);
Insert into comment (username, ip, comment, time) VALUES ("pinghui", "127.0.0.1 ",
Try {Page = Integer. parseInt (string_page );
} Catch (NumberFormatException e)
{Page = 0;
}
Java. SQL. Connection sqlConn; // database Connection object
Java. SQL. Statement sqlStmt; // Statement object
Java. SQL. ResultSet sqlRst; // result set object
// Register the JDBC driver object
Class. forName ("org. gjt. mm. mysql. Driver"). newInstance ();
// Connect to the database
SqlConn = java. SQL. DriverManager. getConnection ("jdbc: mysql: // localhost/p", "test ","");
// Create a statement object
SqlStmt = sqlConn. createStatement
(Java. SQL. ResultSet. TYPE_SCROLL_INSENSITIVE, java. SQL. ResultSet. CONCUR_READ_ONLY );
// Execute the SQL statement
String sqlQuery = "select count (*) from comment ";
SqlRst=sqlStmt.exe cuteQuery (sqlQuery );
SqlRst. next ();
Int count = sqlRst. getInt (1); // obtain the total number of message records
If (Page> = 0) RecoderPage = Page; // process the Page
Else RecoderPage = 0-Page * 10;
If (RecoderPage> count/15) {RecoderPage = count/15; Page = RecoderPage;} // The Page is out of bounds.
RecoderRow = RecoderPage * 15; // Obtain the message record number to be displayed
SqlQuery = "select * from comment order by userid desc limit" + RecoderRow + ", 15;"; // read 15 records at a time
SqlRst=sqlStmt.exe cuteQuery (sqlQuery );
%> A total of <% = count %> Messages
<% While (sqlRst. next () // display the message
{// Obtain the <% = sqlRst. getString ("userid") %> next record
Bordercolorlight = "# 6666FF" bordercolordark = "# 6666FF" bgcolor = "# CCCCFF" align = "center">
| Nickname: <% = sqlRst. getString ("username") %> |
Gender: <% = sqlRst. getString ("sex") %> |
Address: <% = sqlRst. getString ("address") %> |
| Tel: <% = sqlRst. getString ("telnumber") %> |
Zip code: <% = sqlRst. getString ("post") %> |
OICQ: <% = sqlRst. getString ("oicq") %> |
ICQ: <% = sqlRst. getString ("icq") %> |
Email:
"Title =" writing to message recipients "> <% = sqlRst. getString (" email ") %> |
URL:
"Title =" writing to message recipients ">
<% = SqlRst. getString ("urltitle") %> |
Message: <% = sqlRst. getString ("comment") %>
-<% = SqlRst. getString ("time") %>
(From <% = sqlRst. getString ("ip") %>) |
<% }%> <%
// Pagination of the following messages
Int I = count/15; // total page, 15 records per page
Int j = I/10; // The total size of the page. if there are no 10 pages, a large page is displayed.
// Page
Int StartPage;
// Int HrefPage;
If (Page <0) Page = 0-Page * 10; // every 10 pages
StartPage = Page/10; // every 10 pages
Out. print ("[total" + (I + 1) + "page]");
// Out. print ("total" + j + "screen ");
// Out. print ("display Page" + Page + "Page ");
// Out. print ("StartPage =" + StartPage );
If (StartPage> 0)
Out. print ("| first 10 pages ");
For (int k = 0; k <10; k ++)
{Int p = StartPage * 10 + k;
If (p> I) break;
If (p = Page)
Out. print ("| no." + Integer. toString (p + 1) + "page ");
Else
Out. print ("| no." + Integer. toString (p + 1) + "page ");
}
If (StartPage Out. print ("| last 10 pages ");
Out. print ("| ");
%>
<%
// Close the result set object
SqlRst. close ();
// Close the statement object
SqlStmt. close ();
// Close the database connection
SqlConn. close ();
%> Program ended
Program limitations:
The error is not captured, but here we only want to explain how to read the Mysql database from jsp. If it is used as a message book, you must handle the error! Do we have to leave a message?
Next we will start to create a message page!
<% @ Page
Import = "java. util .*"
Import = "java. text .*"
Import = "java. SQL .*"
Import = "java. io .*"
Import = "java. lang .*"
ContentType = "text/html; charset = gb2312"
%>
<%
Class CommentError // throws java. lang. NullPointerException
{Public String Username = "", Sex = "", Address = "", Postal = "", Oicq = "", Icq = "", Tel = "", comment = "";
Public boolean NoError = true; // false;
Public int ErrorCount = 0;
Private boolean IsNumber (String s1 ){}
Public String font (String se ){}
Public String Comment_Er (String se ){}
Public void Username (String se ){}
Public void Sex (String se ){}
Public void Address (String se ){}
Public void Comment (String se ){}
Public void Tel (String se ){}
Public void Postal (String se ){}
Public void Oicq (String se ){}
Public void Icq (String se ){}
}
Class FormatComment
{Public String Replace (String source, String oldString, String newString ){}
Public String formatint (String se ){}
Public String fromatcomment (String se ){}
Public String toHtmlInput (String str ){}
Public String toHtml (String str ){}
Public String toSql (String str) {}// convert to the format that can be added to Myqal
}
%>
Use MYsql database
Ping Hui jsp message board
<%! String username, sex, address, post, oicq, icq, telnumber, comment, email, url. urltitle;
%>
<%
Try {username = request. getParameter ("name ");
} Catch (NullPointerException e) {username = "";}
Try {comment = request. getParameter ("comment ");
} Catch (NullPointerException e) {comment = "";}
Try {sex = request. getParameter ("sex ");
} Catch (NullPointerException e) {sex = "";}
Try {address = request. getParameter ("address ");
} Catch (NullPointerException e) {address = "";}
Try {post = request. getParameter ("postal ");
} Catch (NullPointerException e) {post = "";}
Try {oicq = request. getParameter ("oicq ");
} Catch (NullPointerException e) {oicq = "";}
Try {icq = request. getParameter ("icq ");
} Catch (NullPointerException e) {icq = "";}
Try {telnumber = request. getParameter ("telphone ");
} Catch (NullPointerException e) {telnumber = "";}
Try {email = request. getParameter ("email ");
} Catch (NullPointerException e) {email = "";}
Try {url = request. getParameter ("url ");
} Catch (NullPointerException e) {url = "";}
Try {urltitle = request. getParameter ("urltitle ");
} Catch (NullPointerException e) {urltitle = "";}
String ip = request. getRemoteAddr (); // obtain the ip address
String time = (new SimpleDateFormat ("yyyy-MM-dd hh: mm: ss", Locale. US). format (new java. util. Date ());
String userid = "";
String MyQuery = "";
Here, I call a class that checks the message validity.
CommentError testcomment = new CommentError ();
Testcomment. Username (username );
Testcomment. Postal (post );
Testcomment. Sex (sex );
Testcomment. Address (address );
Testcomment. Tel (telnumber );
Testcomment. Comment (comment );
Testcomment. Oicq (oicq );
Testcomment. Icq (icq );
If (testcomment. NoError) // There is no error in the message, write to the database
Try {// The database is successfully written.
Java. SQL. Connection sqlConn; // database Connection object
Java. SQL. Statement sqlStmt; // Statement object
Java. SQL. ResultSet sqlRst; // result set object
// Register the JDBC driver object
Class. forName ("org. gjt. mm. mysql. Driver"). newInstance ();
// Connect to the database
SqlConn = java. SQL. DriverManager. getConnection ("jdbc: mysql: // localhost/pinghui", "test ","");
// Create a statement object
SqlStmt = sqlConn. createStatement (java. SQL. ResultSet. TYPE_SCROLL_INSENSITIVE, java. SQL. ResultSet. CONCUR_READ_ONLY );
// Execute the SQL statement
Here, a class is called to process messages, which can be recognized by Mysql,
FormatComment FC = new FormatComment ();
Oicq = FC. formatint (oicq );
Icq = FC. formatint (icq );
Post = FC. formatint (post );
Telnumber = FC. formatint (telnumber );
Username = FC. toSql (username );
Comment = FC. toSql (comment );
MyQuery = "insert into comment (username, sex, address, ip, post, oicq, icq, telnumber, comment, time, url, email) values ('"+ username +"', '"+ sex +"', '"+ address +"', '"+ ip +"', "+ post + ", "+ oicq +", "+ icq +", '"+ telnumber +"', '"+ comment +"', now (), '"+ url + "', '"+ email + "');";
SqlRst=sqlStmt.exe cuteQuery (MyQuery); // add data to the database
SqlRst. close (); // close the result set object
SqlStmt. close (); // close the statement object
SqlConn. close (); // close the database connection
Out. print (time );
%>
Message successful. thank you!
| Nickname: <% = username %> |
Gender: <% = sex %> |
Address: <% = address %> |
| Tel: <% = telnumber %> |
Zip code: <% = post %> |
OICQ: <% = oicq %> |
ICQ: <% = icq %> |
| Email: |
URL: |
Message: <% = comment %>
-<% = Time %> (from <% = ip %>) |
<%
} Catch (SQLException e) // failed to write to the database
{Out. print ("Message failed ");
Out. print (MyQuery );
Out. print (userid );
}
Else
{
Out. print ("total" + testcomment. ErrorCount + "error data, please modify it! ");
%>
