What database universal access methods are based on JDBC?
1. Universal Database Bean Design
In this example, universal database operations such as database connections and execution SQL statements are encapsulated, and the above functions are completed by implementing DBConnBean and DBQueryBean two JavaBeans. The DBConnBean is responsible for Java applications and database connections; DBQueryBean provides a set of functionality that performs standard SQL, which can implement all the features of standard SQL completion. Its function code is shown below:
1 The source code of dbconnbean.java is as follows:
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 = "loading 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 status Value = '" E.GETSQLSTATE () "' Error Code = '" E.GetersRrorcode () ") ";} throw new exception (msg);} Return Con;} protected {Super.Finalize (); if (stmt! = null) stmt.close (); if (result! = null) Result.close ();} // The most recent database queries 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 (conn == 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 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;}} 2 dbQueryBean. Java source code as follows: package dbaccess; import java.sql. *; Import java.util. *; Import java.io. *; Import java.lang.reflect. *;
Public class dbquerybeanimplements serializable {dbconnbean dbc; string sql = null; int = 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 == null || row> = Result.size ()) Return Null; string r [] = (String []) Result.Element (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.element (ROW); / * string ret [] = new string [colcount]; vector r = (Vector) Result.element (for (int i = 0) I COLCOUNT = 0; // Limitcount = 0; Result = null;} PUBLIC VOID OVER () throws throwable {femance ();} public int query (String SQL) {result = new vector (); int RET = 0; try { ResultSet RS = dbc.query (SQL); if (rs == null) {RET = dbc.getaffectedRows ();} else {resultetMetaData RM = rs.getMetAdata (); color = rm.getColumnCount (); while (RS). NEXT ()) {String Row [] = new string [color]; for (int i = 0; i 2. Database table structure The three database tables, table names, and fields are mainly shown in this example: Plan purchase list: JHCG_TABLE Field Name Chinese Name Type Length Goods_no Item No. Vchar 10 Goods_name Item Name Vchar 50 Amount Purchase Quantity INT Price Purchase Units Float Gold Coin Vchar 15 Units Vchar 10 Date Time Date Remark Remarks Vchar 100 Stock statistics table: KCTJ_TABLE Field Name Chinese Name Type Length Goods_no Item No. Vchar 10 Goods_Name Item Name Vchar 50 Amount Inventory Quantity INT Date Time Date Remark Remarks Vchar 100 Actual Purchase Table: SJCG_TABLE Field Name Chinese Name Type Length Goods_no Item No. Vchar 10 Goods_Name Item Name Vchar 50 Amount Purchase Quantity INT Price Price Purchase Units Float Gold Coin Vchar 15 Units Purchasing Unit Vchar 10 Date Time Date Remark Remarks Vchar 100 Among them, business logic is very simple, which generates actual procurement tables according to the planned purchase list and inventory statistics. At the same time, universal operations are completed, deleted, changed, and conclude and install general operations for each table. 3. JSP design 1 Insert operation Complete the record insert function of the database table, where the plans to put the home page (INSERT_JHCG.HTM) is: INSERT_JHCG.HTM Transports user input to DEMO_INSERT_JHCG.JSP, complete the insertion operation. The function code for the JSP file is: <% try {dbconn.connect ();} catch (excetion e) {outputln (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 " '" ", " "' " ", " InSamount ", " ", " " " " " ", " " " insignit " ' " ", " " '" ") "; Try {dbbean.query (sqlStatement);} catch (exception e) {out.println (E.GetMessage ());}%> < A href = "demo_main.htm"> Records Inserted ... Click Here to Return < / body> 2 query operation The query main page mainly provides the conditional query function of the three database tables, query.htm sends the user to the database table and query criteria to Demo_Query.jsp, complete the database query operation and query results set by the JSP file. Return and display The function code is as follows:
<% IF (dbconn == null || dbbean == null) {Out.println (" JavaBean Not Found! "); Return;}%>
<% IF (dbconn == null || dbbean == null) {Out.println ("JavaBean Not Found!"); Return;}%>
<% try {dbconn.connect ();} catch (excetion e) {outputln (E.GetMessage ());}%>
<% 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 " '";}}} f (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 WHERECLASE = "where good_name =" "" queryname "'";}} f (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 [] PRINTTILE = Request.getParameterValues (Whichcb);
%> <% try {dbbean.query (sqlStatement);} catch (exception e) {output ("Database Error!");} Int Rows = dbbean.getrowcount (); int cols = dbbean.getColumnCount (); %>
