DBO

Package jsswt.sendsms;

/ ** *

Title: *

Description: Database Operation Class * Support Dynamic Profile DBConfig.txt * Support Struts Connection Pool and Other Connection Pool (Requires External Connection Pool Support) * Support Data Patement Display (with laosun.servlet.pagelet can be used to implement unwind one-line code implementation paging JSP) * Support type reflective mechanism Automatically completes any standard FORMBEAN data assignment (for struts) * *

Copyright: Copyright (c) 2004 *

Company: * @Author Sunhui * @version 1.01 * /

Import java.io. *; import java.lang.reflect. *; import java.sql. *; import java.util. *; import javax.servlet.http. *; import javax.sql. *;

Import laosun.string. *;

Public class dbo {

Private boolean connection = false; // Whether the private connection conn = null; // Database connection private file cfgfile = null; // Database configuration information file, mainly for test database connections, do not use commercial private string driverclass = NULL; // Database Drive Private String URL = NULL; // Database URL Private String User = NULL; / / Database Connection User Private String Password = NULL; // Database Connection Password Private Vector ColumnNames = New Vector (); // Column private vector vcontent = new vector (); // Table data private int totalRec = 0; // Total record number, used for paged totalpage = 1; // Total Page number private long aa; private int totalR = 0 Public dbo () {}

// for automatically connecting at ambient Struts public DBO (HttpServlet servlet, String dataSourceName) throws Exception {if (servlet! = Null && dataSourceName! = Null) {DataSource ds = (DataSource) servlet.getServletContext (). GetAttribute ( DataSourceName); setConn (DS.GetConnection ());} else {throw new exception ("servlet or datasourceename is null!");}}

public DBO (DataSource dataSource) {try {setConn (dataSource.getConnection ());} catch (SQLException ex) {ex.printStackTrace ();}} / ** * determine connect state * @return boolean * / public boolean isConnected ( ) {Returnid;

/ ** * load database config file * @param filename String * @return boolean * @deprecated use of the database connection may result in poor performance * / public boolean loadCfgFile (String filename) {Properties p = new Properties (); try { Cfgfile = new file (filename); p.Load (new fileinputstream (cfgfile)); driverclass = p.getProperty ("driverclass"); URL = P.GetProperty ("URL"); user = p.getProperty ("user" ); Password = p.getProperty ("password"); return true;} catch (exception ex) {system.out.println ("Error In dboperator.loadcfgfile (filename)" ex); returnaf false;}}

/ ** * Open database connection * @return boolean * / public boolean OpenDB () {if (connected) {return true;} if (! Connect& cfgfile == null) {system.out.println ("Error Operation! Please Load Database Config File First! "); System.out.println (" Example of Database Config File: "); System.out.Println (" -------------------------------------------------------------------------------------- -------- / N " " SQLServer2000 Example / N " " driverclass = com.microsoft.jdbc.sqlser.sqlserverdriver / n " " URL = JDBC: Microsoft: SQLServer: // localhost: 1433 / N " " User = Shy / N " " Password = Shy / N " " ------------------------------------------------------------------------------------------------------------------------------------------------------------------ / N " " Oracle Example / N " DriverClass = Oracle.jdbc.driver.OracleDriver / N" "URL = JDBC: Oracle: Thin: @localhost: 1521: SID / N" "User = Shy / N " " Password = Shy / N " " -------------------------- / N " " odbc example / n " "Driverclass = sun.jdbc.odbc.jdbcodbcdriver / n" "URL = JDBC: ODBC: ODBCNAME OR JDBC: ODBC: Driver = {Microsoft Access Driver (* .mdb)}; dbq = xxx.mdb / n" "user = shy / n" "password = shy / n " " -------------------------- / n " " mysql eXample / n " " driverclass = com.mysql.jdbc .Driver / n " " URL = JDBC: mysql: // localhost: 3306 / database / n " " user = shy / n "

"Password = Shy / N"); connection = false;} try {class.forname (driverclass) .newinstance (); conn = drivermanager.getConnection (URL, User, password);} catch (Exception E) {Close (NULL , NULL, CONN); Connected = false; System.out.Println ("Error In Dboperator.Opendb ()" E); connection = false;} connection = true; system.out.println ("Connect to" URL "successful"); return connection;} / ** * Table list collection * @return vector * / public vector getColumnNames () {if (ColumnNames.isempty ()) {system.out.println ("Column name Empty ! ");} Return columnnames;}

/ ** * Class reflective mechanism to generate data object collection, used for struts environment * @Param SQL STRING query statement * @Param classname string class * @Param params vector parameter collection * @return v Vector reflect (String sql, String className, Vector params) {Vector v = new Vector (); openDB (); PreparedStatement pstmt = null; ResultSet rs = null; try {if (! connected) {return v;} pstmt = conn .preparestatement (SQL); if (params! = null&&! params.isempty ()) {if (strutil.count (sql, "?")! = params.size ()) {system.out.println ("parameter Matching ");} else {for (int i = 0; i

M.INVOKE (FORMBEAN, New Object [] {RecordValue};} v.add (formbean);}} catch (exception e) {E.PrintStackTrace ();} finally {close (PSTMT, RS, Conn); Return v;}}

/ ** * Use the class reflection mechanism to generate a collection of data objects, used for Struts Environment * @Param SQL STRING Query Statement * @Param ClassName String Class Name * @Param PageNum INT Page * @Param PageSize Int Each page Most records * @ @ @ @ @ @ @ @ @ @ @ @ @ @ @@ a set of return vBean data objects * / public Vector reflect (int totalRec, String sql, String className, long pageNum, int pageSize) {Vector vBean = new Vector (); Vector vTemp = new Vector (); try {vTemp = queryPage ( TotalRec, SQL, PAGENUM, PAGESize;} catch (exception ex) {ex.printstacktrace ();} if (vTemp.size ()> 0) {for (int i = 0; i

/ ** * Generate ByteArray in accordance with the SQL statement, used for image display * @Param SQL STRING * @return vbyteaRray * / public vector createbyterrays (STRING SQL) {Vector VbyteArray = new vector (); OPENDB (); if (! connected) {return vByteArray;} ResultSet rs = null; PreparedStatement pstmt = null; try {pstmt = conn.prepareStatement (sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); rs = pstmt.executeQuery (); while (rs.next ( )) {Object o = rgetObject (1); if (o! = Null) {if (o instanceof byte []) {byte [] b = (byte []) o; vbytearray.addelement (b);} } Else {vbytearray.addelement (null);}}} catch ("Error In Dboperator.createByteaRray (SQL)" E);} Finally {Close (PSTMT, RS, CONN) Return VbyteArray;}}

/ * ** The set ByteArray sql statement generated for image display * @param sql String * @param offset int * @return vByteArray * / public Vector createByteArrays (String sql, int offset) {Vector vByteArray = new Vector () ; openDB (); if (connected!) {return vByteArray;} ResultSet rs = null; PreparedStatement pstmt = null; try {pstmt = conn.prepareStatement (sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); rs = pstmt.executeQuery ( ); While (rs.next ()) {Object O = rs.getObject (offset); if (o! = Null) {if (o instanceof byte []) {byte [] b = (byte []) O; VbyteArray.addelement (b);}} else {vbyteaRray.addelement (null);}}} catch ("Error In dboperator.createbyterray (SQL) E);} finally { Close (PSTMT, RS, CONN); Return VbyteArray; }} / ** * Release connection without performing queries * / public void releaseconn () {Close (Null, Null, Conn);

/ * ** Close all connections * @param resultSet ResultSet * @param statement Statement * @param connection Connection * / private void close (Statement statement, ResultSet resultSet, Connection connection) {try {if (statement! = Null) {statement. Close ();} if (resultset! = null) {resultSet.close ();} if (connection! = null) {connection.close ();}} catch (exception e) {system.out.println ("Error In dboperator.close () " e);} finally {connection = false;}} / ** * Take the total number of pages * @return int * / public int gettotalpage () {Return Totalpage;}

/ ** * Take a total number of records * @return int * / public int getTotalRec () {Return TotalRec;}

// ********** Add Kang Qing (08.24) to get the total number of data according to a single table *********** Start // Number of records PUBLIC INT QueryTotalRec (String SQL ) {clear (); openDB (); ResultSet rs = null; PreparedStatement pstmt = null; try {pstmt = conn.prepareStatement (sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); rs = pstmt.executeQuery (); rs.next (); TotalRec = INTEGER.PARSEINT (rs.getstring (1)); Return TotalRec;} catCh (Exception E) {system.out.print ("Find the total number of companies in the company ~~~~~~~"); Return }}

// ********** Add Guo Hongliang (9.4) Number of data according to single table ********** * ********** ENDPUBLIC INT Querytotal (String SQL) {clear (); OPENDB () ; ResultSet rs = null; PreparedStatement pstmt = null; try {pstmt = conn.prepareStatement (sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); rs = pstmt.executeQuery (); if (rs.next ()) rs.last ( ); TotalR = rs.getrow (); return total;} catCH (Exception E) {system.out.print ("Find the total number of companies happens ~~~~~~~"); return 0;}}

/ ** * page display, used for various database paging display * @Param SQL STRING * @Param PageSize INT page number * @Param PageSize Int per page number * @return vector * @Throws java.lang.exception * / public Vector QueryPage (Int TotalRec, String SQL, Long Pagenum, INT PageSize "Throws Exception {aa = Pagenum * 12-12; SQL = SQL " LIMIT " AA ", 12 "; System.out.Println (" SQL = ===== " SQL); CLEAR (); IF (Pagenum <1 || Pagesize <1) {throw new exception (" Pagenum or PageSize Error! ");}}} (! connection) {system.err. println ( "Not connected!"); return new Vector ();} openDB (); ResultSet rs = null; PreparedStatement pstmt = null; try {pstmt = conn.prepareStatement (sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); rs = pstmt.executeQuery (); // Get total record number // system.out.println ("in the dbo totalrec" TotalRec); // if (TotalRec == 0) {// system.out.println (" DSFFSDAFSADFSDF Sffffffffffffffffffffffffff "); // if (rs.next ()) {// rs.last (); // system.out.println (" 4444444444 = " gtstring (6)); // TotalRec = RS. GetRow (); // system.out.println ("TotalRec1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111); // /} //} // total record number is less than per page number IF (TotalRec

(TotalPage == 0)? 1: TotalPage; // If the positioning page is greater than the total number of pages, the first page should be default the first page if (Pagenum> TotalPage) {PAGENUM = 1;} // positioning to the specified page // long pointer = (PAGENUM - 1) * PageSize; // Position to Home // if (Pointer == 0) {// rs.beforefirst (); //} // else {// rs.absolute ((int) Pointer; //}} // Build data builddatavector (} catch (exception e) {system.out.println ("Error Int Dboperator.getpage (SQL, Pagenum, Pagesize)" E);} Finally {Close (PSTMT, RS, CONN); RETURN VCONTENT;}} / ** * No page to remove all data * @Param sql string * @return vector * / public vector doQuery (String SQL) {OPENDB (); clear (); if (! connected) {return new vector ();} resultset = null; preparedStatement pstmt = null; try {pstmt = conn.prepareStatement (SQL, ResultSet.Type_Scroll_Sensitive, ResultSet.concur_read_only); rs = pstmt.executeQuery (); BuildDataVector (RS, -1);} catch (exception e) {system.out.println ("Error Int dboperator.doQuery ()" E);} finally { Close (PSTMT, RS, CONN); Return vcontent;}}

/ ** * No page to remove all data * @Param sql string * @Param params vector * @return vector * @throws exception * / public vector DOQUERY (STRING SQL, Vector params) {OPENDB (); clear (); if (); ! connected) {return new vector ();} if (Strutil.count (SQL, "?")! = params.size ()) {system.out.println ("parameter number does not match");} ResultSet RS = null; PreparedStatement pstmt = null; try {pstmt = conn.prepareStatement (sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); for (int i = 0; i

/ ** * From the result set to data, process data into two types, one is Vector accommodation hashmap (placed Object), * The other is Vector accommodation vector (placed String, used to build TableModel) * @Param rs ResultSet * @param pageSize int * @throws Exception * / private void buildDataVector (ResultSet rs, int pageSize) throws Exception {// get column name int lvColumnNu = rs.getMetaData () getColumnCount ();. String [] lvColumnNames = new String [lvColumnNu]; for (int lvLoop = 0; lvLoop

/ ** * Get a connection from the outside * @Param c connection * / public void setConn (Connection C) {if (c! = Null) {this.conn = C; this.connected = true;} else {THIS.CONN = null; this.connected = false;}} public connection getConn () {Return Conn;

/ ** * Execute Batch Operation * @Param Sqlvector Vector * @Return Boolean * / Public Boolean Dobatch (Vector Sqlvector) {

OpenDB (); boolean success = false; if (! connect || sqlvector.isempty ()) {system.out.println ("Database Not Connected Or SQL Vector Empty!"); Return Success;} StMT = NULL; TRY {Stmt = conn.createstatement (); for (iTerator i = sqlvector.iterator (); I.hasNext ();) {string SQL = (String) i.next (); stmt.addbatch (sql);} stmt. EXECUTEBATCH (); success = true;} catch (exception e) {success = false; system.out.println ("Error In Dboperator.dobatch (Sqlvector)" E);} Finally {Close (STMT, NULL, CONN) ; Return Success;}}

/ ** * Execute a single update operation * @Param SQL STRING * @Param params vector * @Return Boolean * / public boolean doupdate (String SQL, Vector params) {OPENDB (); boolean success = false; if (! Connection) { Return Success;} // If the number of parameters does not match if (strutil.count (SQL, "?")! = params.size ()) {system.out.println ("parameter number does not match"); Return Success } PreparedStatement PSTMT = NULL; try {pstmt = conn.preparestatement (SQL); for (int i = 0; i