JS based on JDBC What are the database common access methods?
1. Common Database Bean Design
In this example, the database connection and the execution of SQL statements and other common database operations are encapsulated, through the implementation of Dbconnbean and Dbquerybean two JavaBean to complete the above functions. Where Dbconnbean is responsible for the connection of the Java application and the database, Dbquerybean provides a set of functions to perform standard SQL, and can achieve all the functions of standard SQL. The functional code is as follows:
①dbconnbean.java's source code looks like this:
Package dbaccess;
Import java.sql.*;
Import java.util.*;
Import java.io.*;
public class Dbconnbean
Implements serializable{
Private String Dbdriver = "Sun.jdbc.odbc.JdbcOdbcDriver";
Private String Dbhost = "127.0.0.1";
Private String dbname = "Demo";
Private String Conp = "Jdbc:odbc:db_demo";
Private String username = "";
Private String Password = "";
Private Boolean xdebug = true;
public Connection con = null;
public String sql = null;
Statement stmt = null;
Public ResultSet result = NULL;
private int affectedrows = 0;
Public Dbconnbean ()
{
Xdebug = true;
con = null;
sql = null;
}
Public Connection Connect ()
Throws Exception
{
String msg = NULL;
Try
{
Class.forName (Dbdriver). newinstance ();
}
catch (Exception e)
{
msg = "Load Database driver failed";
if (xdebug) msg + = "(Drive '" +dbdriver+ ")";
throw new Exception (msg);
}
Try
{
String constr = Conp;
con = drivermanager.getconnection (Constr,username,password);
}
catch (SQLException e)
{
msg = "!! Database connection Failed ";
if (Xdebug)
{
msg + + "(Error message = '" + e.getmessage () + "' SQL state value = '" + e.getsqlstate () + "' Error code = '" + e.geterrorcode () + "')";
}
throw new Exception (msg);
}
return con;
}
protected void Finalize ()
Throws Throwable
{
Super.finalize ();
if (stmt!= null) stmt.close ();
if (result!= null) result.close ();
}
Number of rows affected by the last query to the database
public int getaffectedrows ()
{
return affectedrows;
}
Public Connection Getcon ()
{
return con;
}
Public String Getconp ()
{
return CONP;
}
Public String Getdbdriver ()
{
return dbdriver;
}
Public String Getdbname ()
{
return dbname;
}
public boolean getdebug ()
{
return xdebug;
}
Public String GetPassword ()
{
return password;
}
Public ResultSet GetResult ()
{
return result;
}
Public String GetSQL ()
{
return SQL;
}
Public String GetUserName ()
{
return username;
}
public void Over ()
Throws Throwable
{
Finalize ();
}
Public ResultSet query ()
Throws Exception
{
result = NULL;
affectedrows = 0;
if (con = = null)
Connect ();
if (stmt = null)
stmt = Con.createstatement ();
if (sql.substring (0,6). Equalsignorecase ("select")
{
result = Stmt.executequery (SQL);
}
Else
{
affectedrows = stmt.executeupdate (sql);
}
return result;
}
Public ResultSet query (String s)
Throws Exception
{
sql = s;
return query ();
}
public void Setdbdriver (String s)
{
Dbdriver = s;
}
public void Setdebug (Boolean b)
{
Xdebug = b;
}
public void Setgetconp (String s)
{
Conp = s;
}
public void Setgetdbname (String s)
{
dbname = s;
}
public void Setgetusername (String s)
{
Username = s;
}
public void SetPassword (String s)
{
Password = s;
}
public void SetSQL (String s)
{
sql = s;
}
}
②dbquerybean.java's source code looks like this:
Package dbaccess;
Import java.sql.*;
Import java.util.*;
Import java.io.*;
Import java.lang.reflect.*;
public class Dbquerybean
Implements Serializable
{
Dbconnbean DBC;
String sql = null;
int rowcount = 0;
int colcount = 0;
int limitcount = 0;
Vector result = null;
Public String _watch = "";
Public Dbquerybean ()
{
DBC = new Dbconnbean ();
try {
Dbc. Connect ();
catch (Exception e) {
HandleException (e);
}
}
protected void Finalize ()
Throws Throwable
{
Super.finalize ();
if (DBC!= null) dbc.over ();
if (result!= null) result.removeallelements ();
}
Public String get (int row, int col)
{
if (Result==null | | Row >= result.size ()) return null;
String r[] = (string[]) result.elementat (row);
if (Col >= Java.lang.reflect.Array.getLength (r)) return null;
return R[col];
}
public int getaffrows () {return dbc.getaffectedrows ();}
public int getColumnCount () {
return colcount;
}
Public string[] GetRow (int row)
{
if (Result==null | | Row >= result.size ()) return null;
Return (String []) result.elementat (row);
/*string ret[] = new String[colcount];
Vector r = (vector) result.elementat (row);
for (int i=0; i<colcount; i++)
Ret[i] = (String) r.elementat (i);
Return ret;*/
}
public int GetRowCount () {
return rowcount;
}
public void HandleException (Exception e)
{
_watch = E.getmessage ();
}
public void Init ()
{
RowCount = 0;
ColCount = 0;
Limitcount = 0;
result = NULL;
}
public void Over ()
Throws Throwable
{
Finalize ();
}
public int query (String sql)
{
result = new Vector ();
int ret = 0;
try {
ResultSet rs = dbc.query (SQL);
if (rs = = null)
{
ret = Dbc.getaffectedrows ();
}
Else
{
ResultSetMetaData rm = Rs.getmetadata ();
ColCount = Rm.getcolumncount ();
while (Rs.next ())
{
String row[] = new String[colcount];
for (int i=0; i<colcount; i++)
Row[i] = rs.getstring (i+1);
Result.addelement (row);
rowcount++;
}
Rs.close ();//to release the resource.
ret = Result.size ();
}
}
catch (Exception e)
{
HandleException (e);
return-1;
}
return ret;
}
}
2. Database table structure
In this example, there are three database tables, table names and fields are as follows:
Plan Purchase table: jhcg_table
Field name Chinese name type length
GOODS_NO Item Number Vchar 10
Goods_name article name Vchar 50
Amount Purchase Quantity Int
Price buy float
Gold Currency Vchar 15
Units Unit Vchar 10
Date Time Date
Remark Note Vchar 100
Inventory Statistics: Kctj_table
Field name Chinese name type length
GOODS_NO Item Number Vchar 10
Goods_name article name Vchar 50
Amount Inventory Quantity Int
Date Time Date
Remark Note Vchar 100
Actual Purchase table: Sjcg_table
Field name Chinese name type length
GOODS_NO Item Number Vchar 10
Goods_name article name Vchar 50
Amount Purchase Quantity Int
Price prices Buy Float
Gold Currency Vchar 15
Units Purchase Unit Vchar 10
Date Time Date
Remark Note Vchar 100
The business logic is very simple, that is, based on the planned procurement table and inventory statistics to generate the actual purchase table. At the same time, the table to complete the database to add, delete, change, check and other general operations.
3. JSP Design
① insert Operation
Completes the record insertion function for the database table, where the Insert master page (insert_jhcg.htm) of the planned purchase table is:
Insert_jhcg.htm sends user input to demo_insert_jhcg.jsp to complete the insert operation. The function code of the modified JSP file is:
<body>
<jsp:usebean id= "Dbconn" class= "dbaccess". Dbconnbean "scope=" page/>
<jsp:usebean id= "Dbbean" class= "dbaccess". Dbquerybean "scope=" page/>
!--Test javabean-->
<%
if (dbconn = = null| | Dbbean = = null) {
Out.println ("JavaBean not found!") ）；
Return
}
%>
!--try Db_demo connection-->
<%
try{
Dbconn.connect ();
}catch (Exception e) {
Out.println (E.getmessage ());
}
%>
!--Execute SQL statement-->
<%
String Insgoodno = Request.getparameter ("Ed_jhcg_no");
String insgoodname = Request.getparameter ("Ed_jhcg_name");
int insamount = (integer.valueof (Request.getparameter ("Ed_jhcg_amount")). Intvalue ();
float Insprice = (float.valueof (Request.getparameter ("Ed_jhcg_price")). Floatvalue ();
String insgold = Request.getparameter ("Ed_jhcg_gold");
String insunit = Request.getparameter ("Ed_jhcg_unit");
String Insremark = Request.getparameter ("Ed_jhcg_remark");
String SQLStatement = "INSERT into jhcg_table (Good_no,good_name,amount,
Price,gold,unit,remark) VALUES ("+" ' "+insgoodno+" ' "+", "+" "" "+insgoodname+" "+",
"+insamount+", "+insprice+", "+" "" +insgold+ "" "+", "+" "" "+insunit+" "+", "+" "" +
insremark+ "'" + ")";
try{
Dbbean.query (SQLStatement);
}catch (Exception e) {
Out.println (E.getmessage ());
}
%>
<a Href= "demo_main.htm" >records inserted ... Click here to return </a> </p>
</body>
② Query Operations
The query Main page provides a conditional query function for three database tables,
Query.htm the user selects the Query database table and the query condition sends to demo_query.jsp, by the JSP file completes the database query operation and the query result set return and the display, its function code is as follows:
<body>
<%
String SQLStatement;
String Sqlfield = "";
String whichtable = "";
String whereclause = "";
String Queryno = "";
String queryname = "";
%>
<jsp:usebean id= "Dbconn" class= "dbaccess". Dbconnbean "scope=" page/>
<jsp:usebean id= "Dbbean" class= "dbaccess". Dbquerybean "scope=" page/>
!--Test javabean-->
<%
if (dbconn = = null| | Dbbean = = null) {
Out.println ("JavaBean not found!") ）；
Return
}
%>
!--try Db_demo connection-->
<%
try{
Dbconn.connect ();
}catch (Exception e) {
Out.println (E.getmessage ());
}
%>
!--Prepare SQL statement-->
<%
String queryrequest = Request.getparameter ("Rb_request");
Out.println ("Queryrequest:" +queryrequest);
String WHICHCB = "";
if (Queryrequest.equals ("1")) {
WHICHCB = "CK_JHCG";
whichtable = "Jhcg_table";
Queryno = Request.getparameter ("Ed_jhcg_no");
QueryName = Request.getparameter ("Ed_jhcg_name");
if (!queryno.equals (""))
Whereclause = "where good_no=" + "" "+queryno+" ";
if (!queryname.equals ("")) {
if (!queryno.equals (""))
Whereclause + = "and good_name=" + "'" +queryname+ "";
else Whereclause = "where good_name=" + "" "+queryname+" ";
}
}
if (Queryrequest.equals ("2")) {
WHICHCB = "CK_KCTJ";
whichtable = "Kctj_table";
Queryno = Request.getparameter ("Ed_kctj_no");
QueryName = Request.getparameter ("Ed_kctj_name");
if (!queryno.equals (""))
Whereclause = "where good_no=" + "" "+queryno+" ";
if (!queryname.equals ("")) {
if (!queryno.equals (""))
Whereclause + = "and good_name=" + "'" +queryname+ "";
else Whereclause = "where good_name=" + "" "+queryname+" ";
}
}
if (Queryrequest.equals ("3")) {
WHICHCB = "CK_SJCG";
whichtable = "Sjcg_table";
Queryno = Request.getparameter ("Ed_sjcg_no");
QueryName = Request.getparameter ("Ed_sjcg_name");
if (!queryno.equals (""))
Whereclause = "where good_no=" + "" "+queryno+" ";
if (!queryname.equals ("")) {
if (!queryno.equals (""))
Whereclause + = "and good_name=" + "'" +queryname+ "";
else Whereclause = "where good_name=" + "" "+queryname+" ";
}
}
string[] PrintTitle = request.getparametervalues (WHICHCB);
%>
!--Create Query SQL statement-->
<%
SQLStatement = "Select";
for (int i = 0;i<printtitle.length;i++) {
Sqlfield + = printtitle[i]+ ",";
}
SQLStatement + + sqlfield.substring (0,sqlfield.length ()-1) + "from" +whichtable;
if (!whereclause.equals (""))
SQLStatement + = Whereclause;
%>
!--Show Query response-->
<%
try{
Dbbean.query (SQLStatement);
}catch (Exception e) {
Out.println ("Database error!" ）；
}
int rows = Dbbean.getrowcount ();
int cols = Dbbean.getcolumncount ();
%>
<table align= "center" width= "80%" border=1>
<TR align=center>
<%
for (int i = 0;i printtitle.length;i++) {
Out.println ("<td> <b>");
Out.println (Printtitle[i]);
Out.println ("</b> </td>");
}
%>
</tr>
<%
for (int i = 0;i rows;i++) {
Out.println ("<tr>");
for (int j = 0;j cols;j++)
Out.println ("<td>" +dbbean.get (i,j) + "</td>");
Out.println ("</tr>");
}
%>
</Table>
<br>
<a Href= "Demo_main.htm" >click here to return </a> </p>
</body>
③ Generate actual purchase table
The Build database table is an implicit operation in which the program generates the actual purchase table based on the corresponding fields of the planned purchase table and inventory statistics, without requiring any input from the user, with the following functional code (DEMO_CREATE.JSP):
<% @page import= "java.util.*"%>
<body>
<jsp:usebean id= "Dbconn" class= "dbaccess". Dbconnbean "scope=" page/>
<jsp:usebean id= "Dbbean" class= "dbaccess". Dbquerybean "scope=" page/>
!--Test javabean-->
<%
if (dbconn = = null| | Dbbean = = null) {
Out.println ("JavaBean not found!") ）；
Return
}
%>
!--try Db_demo connection-->
<%
try{
Dbconn.connect ();
}catch (Exception e) {
Out.println (E.getmessage ());
}
%>
!--Prepare SQL statement-->
<%
int AMOUNT_JHCG,AMOUNT_KCTJ;
Vector updaters = new vector ();
Dbbean.query ("Delete * from sjcg_table");//delete-Records in Sjcg_table
Dbbean.query ("Select Jhcg_table.good_no,jhcg_table.good_name,jhcg_table.amount,kctj_table.amount,jhcg_ Table.unit from jhcg_table left join kctj_table on Kctj_table.good_no=jhcg_table.good_no ");
int rows = Dbbean.getrowcount ();
int cols = Dbbean.getcolumncount ();
for (int i = 0;i rows;i++) {
String record[] = new STRING[4];
Record[0] = Dbbean.get (i,0);
RECORD[1] = Dbbean.get (i,1);
AMOUNT_JHCG = (integer.valueof (dbbean.get (i,2)). Intvalue ();
if (Dbbean.get (i,3) = = null) AMOUNT_KCTJ = 0;
else Amount_kctj = (integer.valueof (dbbean.get (i,3)). Intvalue ();
RECORD[2] = integer.tostring (AMOUNT_JHCG-AMOUNT_KCTJ);
RECORD[3] = Dbbean.get (i,4);
Updaters.addelement (record);
}
for (int i = 0;i rows;i++) {
String insrecord[] = (string []) Updaters.elementat (i);
String Insgoodno,insgoodname,insunit,insamount;
Insgoodno = insrecord[0];
Insgoodname = insrecord[1];
Insamount = insrecord[2];
Insunit = insrecord[3];
String SQLStatement = "INSERT into sjcg_table (good_no,good_name,amount,unit) values?quot;+" "+insgoodno+" ' "+", "+" "" + Insgoodname+ "'" + "," +insamount+ "," + "" "" +insunit+ "" + ")";
Dbbean.query (SQLStatement);
Dbbean.query ("Delete * from sjcg_table where amount<=0");
}
%>
<a Href= "demo_main.htm" >database created ... Click here to return </a> </p>
</body>
The comprehensive application of the above development tools introduces the whole process of developing e-business application system based on Java, including application development platform, business process analysis, JavaBean encapsulation and JSP development, JSP development involves the general SQL (query and Insert database table) and cursor operations (generate the actual purchase table), basically can complete any network database application requirements. This example can basically link up the Java based e-commerce development technology, and instruct readers to develop e-commerce application.
