Recently I have studied JSP database operations. I was going to use the Oracle database for testing. However, due to the memory limitations of the machine, it is replaced with the MySQL database.
The first time I used JSP to compile a database connection, I found a lot of problems. At the beginning, there was a problem with the JDBC configuration. Later, the database was connected, and the Chinese setting was also a problem. Later, there were many SQL statement writing problems. After three days of hard work, these problems have been solved.
Let's take a look at the code I tested in the past three days:
The first is the connection code of the JavaBean database. The file name is logindata. Java:
Package data;
Import java. SQL .*;
Public class logindata {
// Define the connection address of MySQL
// String url = "JDBC: ODBC: logindemo ";
Connection conn = NULL;
// Statement stmt = NULL;
// Databasemetadata dbmd = NULL;
Public logindata (){
This. Connect ();
}
Public connection getconn (){
Return this. Conn;
}
Public Boolean connect (){
Try {
// Use the JDBC bridge to create a database connection
Class. forname ("org. gjt. Mm. MySQL. Driver"). newinstance ();
// Use the getconnection () method of the drivermanager class to establish a connection
// The first parameter defines the user name and the second parameter defines the password
This. Conn = java. SQL. drivermanager. getconnection ("JDBC: mysql: // localhost: 3306/logindemo? Useunicode = true & amp; characterencoding = gb2312 "," root "," 123456 ");
// Stmt = conn. createstatement ();
// Dbmd = conn. getmetadata ();
} Catch (exception ex ){
Ex. printstacktrace ();
Return false;
}
Return true;
}
}
The following is the code dbcode. jsp on the test code page:
<% @ Page contenttype = "text/html; charset = gb2312" pageencoding = "gb2312" Language = "Java" Import = "Java. SQL. *" errorpage = "%>
<! Doctype HTML public "-// W3C // dtd xhtml 1.0 transitional // en" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<HTML xmlns = "http://www.w3.org/1999/xhtml">
<Head>
<Meta http-equiv = "Content-Type" content = "text/html; charset = gb2312"/>
<Title> untitled document </title>
<SCRIPT type = "text/JavaScript">
<! --
Function checknull (){
If (document. form1.id. value = NULL ){
Document. form1.id. value = 1;
}
}
// -->
</SCRIPT>
</Head>
<Body>
<JSP: usebean id = "logindata" Scope = "page" class = "data. logindata"/>
<%
Request. setcharacterencoding ("gb2312 ");
Try {
Connection conn = logindata. getconn ();
Statement stmt = conn. createstatement ();
String SQL = "select * From userinfo ";
Resultset rs1_stmt.exe cutequery (SQL );
Rs. Last ();
Int id = Rs. getint (1 );
Session. setattribute ("ID", ID + 1 );
Rs. Close ();
Stmt. Close ();
Conn. Close ();
} Catch (exception e ){
E. printstacktrace ();
}
%>
<P> database operations: </P>
<Form ID = "form1" name = "form1" method = "Post" Action = "createtable. jsp">
<Label>
<Input name = "createtable" type = "Submit" id = "createtable" value = "create table: userinfo"/>
<% = Session. getattribute ("userinfo") %>
<Input name = "insertnum" type = "Submit" id = "insertnum" value = "Insert the following data to the table"/> <% = session. getattribute ("insertsql") %>
& Nbsp;
<Input name = "inserttennum" type = "Submit" id = "inserttennum" value = "Insert 10 Data Records"/>
<% = Session. getattribute ("insertten") %> <br/>
& Nbsp; </label>
& Nbsp;
<Table width = "664" Height = "86" border = "2">
<Tr>
<TD width = "168" Height = "41"> id </TD>
<TD width = "223"> Student ID </TD>
& Lt; TD width = "249" & gt; Name & lt;/TD & gt;
</Tr>
<Tr>
<TD Height = "35" align = "center" valign = "Middle"> <input name = "ID" type = "text" id = "ID" onblur = "checknull" value = "<% = session. getattribute ("ID") %> "/> </TD>
<TD align = "center" valign = "Middle"> <label>
<Input name = "xuehao" type = "text" id = "xuehao"/>
_ 1 ~ 10
</Label> </TD>
<TD align = "center" valign = "Middle"> <label>
<Input name = "name" type = "text" id = "name"/>
_ 1 ~ 10
</Label> </TD>
</Tr>
</Table>
<P> Name:
<Input name = "selectnametext" type = "text" id = "selectnametext"/>
<Input name = "selectname" type = "Submit" id = "selectname" value = "query data"/>
& Nbsp; Name:
<Input name = "deletename" type = "text" id = "deletename"/>
<Input name = "deletenum" type = "Submit" id = "deletenum" value = "delete data"/>
<% = Session. getattribute ("deletenames") %> </P>
<P> Student ID:
<Input name = "xuehao2" type = "text" id = "xuehao2"/>
& Nbsp; Name:
<Label>
<Input name = "name2" type = "text" id = "name2"/>
<Input name = "select_name" type = "Submit" id = "select_name" value = "query"/>
</Label>
<Input name = "updatanum" type = "Submit" id = "updatanum" value = "modify"/>
</P>
<%
If (request. getattribute ("data ")! = NULL ){
String [] [] DATA = (string [] []) request. getattribute ("data ");
Out. println ("the query result is shown as follows :");
If (data [0]. length> 1 ){
Out. Print ("<Table border = '2' width = '000000'> ");
Out. Print ("<tr> <TD> NO. </TD> <TD> Student ID </TD> <TD> name </TD> </tr> ");
For (INT I = 0; I <data. length; I ++ ){
Out. Print ("<tr> ");
For (Int J = 0; j <3; j ++ ){
Out. Print ("<TD>" + data [I] [J] + "</TD> ");
}
}
Out. Print ("</table> ");
} Else {
Out. println (data [0] [0]);
}
}
%>
</Form>
<P> & nbsp; </P>
<P> & nbsp; </P>
</Body>
</Html>
The following is the JSP page called on the above interface to process the database code, including creating tables and adding or deleting tables:
<% @ Page contenttype = "text/html; charset = gb2312" pageencoding = "gb2312" Language = "Java" Import = "Java. SQL. *" errorpage = "%>
<! Doctype HTML public "-// W3C // dtd xhtml 1.0 transitional // en" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<HTML xmlns = "http://www.w3.org/1999/xhtml">
<Head>
<Meta http-equiv = "Content-Type" content = "text/html; charset = gb2312"/>
<Title> untitled document </title>
</Head>
<Body>
<JSP: usebean id = "logindata" Scope = "page" class = "data. logindata"/>
<%
Request. setcharacterencoding ("gb2312 ");
%>
<%
// Code for creating a table in the database
String createtable = request. getparameter ("createtable ");
If (createtable! = NULL ){
Try {
String SQL = "CREATE TABLE userinfo (ID int primary key auto_increment, xuehao varchar (20), name varchar (20 ));";
Connection conn = logindata. getconn ();
Statement stmt = conn. createstatement ();
Stmt.exe cute (SQL );
Session. setattribute ("userinfo", "Table userinfo created successfully! ");
Stmt. Close ();
Conn. Close ();
} Catch (exception ex ){
Ex. printstacktrace ();
Session. setattribute ("userinfo", "The table userinfo has been created! ");
}
}
%>
<%
// Code for adding data to a table in the database
String insertnum = request. getparameter ("insertnum ");
If (insertnum! = NULL ){
Try {
String xuehao = request. getparameter ("xuehao ");
String name = request. getparameter ("name ");
String insertsql = "insert into userinfo (xuehao, name) values (?,?) ";
Connection conn = logindata. getconn ();
Preparedstatement pstmt = conn. preparestatement (insertsql );
Pstmt. setstring (1, xuehao );
Pstmt. setstring (2, name );
Pstmt.exe cute ();
Session. setattribute ("insertsql", "a data entry is inserted successfully ");
Pstmt. Close ();
Conn. Close ();
} Catch (exception ex ){
Ex. printstacktrace ();
Session. setattribute ("insertsql", "data insertion failed. Please check and re-insert ");
}
}
%>
<%
// Add data in batches
String inserttennum = request. getparameter ("inserttennum ");
If (inserttennum! = NULL ){
Try {
String xuehao = request. getparameter ("xuehao ");
String name = request. getparameter ("name ");
Connection conn = logindata. getconn ();
Statement stmt = conn. createstatement ();
Int J = 100000;
For (INT I = 0; I <j; I ++ ){
String insertsql = "insert into userinfo (xuehao, name) values ('" + xuehao + I + "', '" + name + I + "')";
Stmt.exe cute (insertsql );
}
Session. setattribute ("insertten", "successfully inserted" + J + "data entry ");
Conn. Close ();
Stmt. Close ();
} Catch (exception ex ){
Ex. printstacktrace ();
Session. setattribute ("insertten", "data insertion failed. Please check and re-insert ");
}
}
%>
<% // Query the table data code
String selectname = request. getparameter ("selectname ");
String [] [] data;
If (selectname! = NULL ){
Try {
Connection conn = logindata. getconn ();
Statement stmt = conn. createstatement ();
Resultset RS;
String selectnametext = request. getparameter ("selectnametext ");
String sqlcount = "select count (*) from userinfo where name like '%" + selectnametext + "% '";
Rs1_stmt.exe cutequery (sqlcount );
Rs. Next ();
Int rowcount = Rs. getint (1 );
If (rowcount> 0 ){
Data = new string [rowcount] [3];
String SQL = "select * From userinfo where name like '%" + selectnametext + "% '";
Rs1_stmt.exe cutequery (SQL );
Int ROW = 0;
While (Rs. Next ()){
For (INT I = 0; I <3; I ++ ){
Data [row] [I] = Rs. getstring (I + 1 );
}
Row ++;
}
} Else {
Data = new string [1] [1];
Data [0] [0] = "cannot be found with" + selectnametext + "related content ";
}
Request. setattribute ("data", data );
Conn. Close ();
Stmt. Close ();
Rs. Close ();
} Catch (exception ex ){
Ex. printstacktrace ();
}
}
%>
<%
// Delete data code
String deletenum = request. getparameter ("deletenum ");
If (deletenum! = NULL ){
Try {
String deletename = request. getparameter ("deletename ");
Connection conn = logindata. getconn ();
Statement stmt = conn. createstatement ();
String deletesql = "delete from userinfo where name like '%" + deletename + "% '";
Int deletenums1_stmt.exe cuteupdate (deletesql );
Session. setattribute ("deletenames", "successfully deleted" + deletenums + "data entry ");
Stmt. Close ();
Conn. Close ();
} Catch (exception ex ){
Ex. printstacktrace ();
Session. setattribute ("deletenames", "deletion failed ");
}
}
%>
<JSP: Forward page = "dbcode. jsp"/>
</Body>
</Html>
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
