Struts2 + jquery (Ajax) connection and read sqlserver data

As a newbie, struts2 and jquery become more and more popular. These two days I summarized their methods for connecting to the sqlserver database. The test is feasible.

========================================================== ========================

Database Name: Test

Data Table Name: testtable, two fields (ID, text)

========================================================== ========================

Struts. xml

<Struts>
<Package name = "mypackage" extends = "JSON-Default">
<Action name = "conndb" class = "com. connmssql">
<Result type = "JSON"> </result>
</Action>
</Package>
</Struts>

========================================================== ============================

Conndb. Java

Package com;
Import java. SQL .*;
Public class conndb
{
Protected static connection conn = NULL;
Protected static final string driver = "com. Microsoft. sqlserver. JDBC. sqlserverdriver ";
Protected static final string url = "JDBC: sqlserver: // localhost: 1433; database = test ";
Protected static final string name = "sa ";
Protected static final string Password = "123 ";

Public connection getdbconn ()
{
/*
* This is a public class used to connect to the sqlserver database.
*
**/
Try {
Class. forname (driver );
Conn = drivermanager. getconnection (URL, name, password );
}
Catch (classnotfoundexception E)

{
E. printstacktrace ();
}
Catch (sqlexception E)
{
E. printstacktrace ();
}
Return conn;
}
}

========================================================== ============================
Connmssql. Java
Package com;
Import java. SQL .*;
Import com. conndb;
Import java. util. arraylist;
Import net. SF. JSON. jsonarray;
Import net. SF. JSON. jsonobject;
Import com. opensymphony. xwork2.actionsupport;
Public class connmssql extends actionsupport
{

/*
* This program reads data from the database, serializes the results to the JSON format, and then transmits the data of the string type to the front-end page.
*
**/
Private string result;
Public String getresult ()
{
Return this. result;
}
Public void setresult (string TMP)
{
This. Result = TMP;
}


Public String execute ()
{
Arraylist <string> recordset = new arraylist <string> (); // This variable is used to store intermediate results.
Conndb myconndb = new conndb (); // create a database connection class
Connection myconn = myconndb. getdbconn ();
// Call the class method to achieve database connection
Try {
Resultset myrs1_myconn.createstatement(cmd.exe cutequery ("select * From testtable"); // create and execute an SQL statement and put the result in the resultsset variable
If (myrs. wasnull ())
This. Result = "no records ";
Else
{
Int columnnum = myrs. getmetadata (). getcolumncount ();
// Obtain the number of columns in the result set
While (myrs. Next ())
{
Arraylist <string> recordrow = new arraylist <string> (); // This variable stores the data of the current row. The result of each successful row is placed in the recordset variable.
For (INT n = 1; n <= columnnum; n ++)
{
Recordrow. Add (myrs. getstring (n); // put the current row in the Variable
}
Jsonarray TMP = jsonarray. fromobject (recordrow); // serialize the result of the current row to JSON format
Recordset. Add (TMP. tostring (); // put the obtained data of the current row into the intermediate result variable.
Recordrow. Clear (); // release the current row
TMP. Clear ();
}

Jsonarray jsonlist = jsonarray. fromobject (recordset );
// Serialize generated intermediate results to JSON format
This. Result = jsonlist. tostring (); // convert the result to the string type to be passed to the page. Page JS then converts the result to the JSON format for display.

Jsonlist. Clear ();
}
Myconn. Close ();
Myrs. Close ();
Recordset. Clear ();

}
Catch (sqlexception e ){
// Todo auto-generated Catch Block
E. printstacktrace ();
}
Return success;
}
}

========================================================== ============================

Index. jsp

<SCRIPT type = "text/JavaScript" src = "JS/jquery-1.9.1.min.js"> </SCRIPT>
<SCRIPT type = "text/JavaScript" src = "JS/index. js"> </SCRIPT>

Browse: <br/>
<Table id = "mytable" border = "1">
<Tr>
<TD> NO. </TD>
<TD> content </TD>
<TD> Delete </TD>
</Tr>
</Table>

========================================================== ============================

Index. js

$ (Function (){
$. Ajax ({
URL: 'conndb. action ',
Type: 'post ',
Datatype: 'json ',
Success: function (serverdate)
{
VaR MSG = eval ("(" + serverdate. Result + ")"); // convert the query result format to JSON. This sentence is very important!
///// Display the query content in a custom table
$. Each (MSG, function (I, item ){
// MSG is the result of action, I is the index number of the current row in the result, and item is an array containing the data of the current row
$ ("# Mytable "). append ("<tr onclick = chooserow (" + item [0] + ", '" + item [1] + "')> <TD> "+ item [0] +" </TD> <TD> "+ item [1] +" </TD> <a href = 'javascript: deleteitem ("+ item [0] +") '> Delete </a> </TD> </tr> ");
})
///// The table display ends.
}
});
})

========================================================== ============================

Final result: