Java version of sqlhelper, pure JDBC tool class

Package SQL; import Java. SQL. *; import Java. util. logging. *;/***** basic SQL operation * through it, you can easily use JDBC to manipulate the database * @ author Xiao Xiaoyong */public class sqlhelper {/*** driver */public static string driver = "com. microsoft. JDBC. sqlserver. sqlserverdriver ";/*** connection string */public static string url =" JDBC: Microsoft: sqlserver: // 192.168.24.246: 1433; databasename = lining "; /*** username */public static string user = "sa";/*** Password */Public static string Password = "123456";/*** this class cannot be instantiated */private sqlhelper () {}/*** get a database connection * set the database connection attribute by setting the four static variables driver/URL/user/password of the class * @ return database connection */public static connection getconnection () {try {// get the driver. Here sqljdbc_1.2.2828.100_chs.exe is used. different versions of the driver have different classes. forname (driver);} catch (classnotfoundexception ex) {logger. getlogger (sqlhelper. class. getname ()). log (L Evel. severe, null, ex);} Try {return drivermanager. getconnection (URL, user, password);} catch (sqlexception ex) {logger. getlogger (sqlhelper. class. getname ()). log (level. severe, null, ex); return NULL ;}/ *** get a statement * this statement has been set to scroll the dataset, you can update * @ return if the acquisition fails, return NULL, remember to check the return value */public static statement getstatement () {connection conn = getconnection (); If (conn = NULL) {retur N null;} Try {return Conn. createstatement (resultset. type_scroll_sensitive, resultset. concur_updatable); // you can scroll through the dataset and update it.} catch (sqlexception ex) {logger. getlogger (sqlhelper. class. getname ()). log (level. severe, null, ex); close (conn);} return NULL;}/*** get a statement * The statement has been set to be able to scroll, you can update * @ Param conn database connection * @ return. If the query fails, null is returned. Remember to check the return value */public static statement getstatem during the call. ENT (connection conn) {If (conn = NULL) {return NULL;} Try {return Conn. createstatement (resultset. type_scroll_sensitive, resultset. concur_updatable); // you can scroll through the dataset and update it.} catch (sqlexception ex) {logger. getlogger (sqlhelper. class. getname ()). log (level. severe, null, ex); return NULL ;}}/*** get a preparedstatement with parameters * The preparedstatement has been set to be able to scroll, and can be updated * @ Param plain text required? Parameter SQL statement * @ Param statement Params SQL statement parameter table * @ return if the query fails, null is returned. Remember to check the return value */public static preparedstatement getpreparedstatement (string plain text, object... extends Params) {connection conn = getconnection (); If (conn = NULL) {return NULL;} preparedstatement pstmt = NULL; try {pstmt = Conn. preparestatement (plain text, resultset. type_scroll_sensitive, resultset. concur_updatable); int I = 1; for (OBJ ECT item: partition Params) {pstmt. setobject (I, item); I ++ ;}} catch (sqlexception e) {e. printstacktrace (); close (conn);} return pstmt;}/*** get a preparedstatement with parameters * the preset dataset can be rolled, can I update * @ Param conn database connection * @ Param plain text required? Parameter SQL statement * @ Param statement Params SQL statement parameter table * @ return if the query fails, null is returned. Remember to check the return value */public static preparedstatement getpreparedstatement (connection Conn, string plain text, object... partition Params) {If (conn = NULL) {return NULL;} preparedstatement pstmt = NULL; try {pstmt = Conn. preparestatement (plain text, resultset. type_scroll_sensitive, resultset. concur_updatable); int I = 1; for (Object item: partition para MS) {pstmt. setobject (I, item); I ++ ;}} catch (sqlexception e) {e. printstacktrace (); close (pstmt);} return pstmt;}/*** run the SQL statement, the returned result is an integer *. It is mainly used to execute non-query statements * @ Param plain text SQL statement * @ return non-negative number: normal execution;-1: execution error;-2: connection Error */public static int execsql (string plain text) {statement stmt = getstatement (); If (stmt = NULL) {return-2;} int I; try {I = stmt.exe cuteupdate (plain text);} catch (sqlexception E X) {logger. getlogger (sqlhelper. class. getname ()). log (level. severe, null, ex); I =-1;} closeconnection (stmt); return I;}/*** execute the SQL statement, the returned result is an integer *. It is mainly used to execute non-query statements * @ Param plain text SQL statement * @ return non-negative number: normal execution;-1: execution error;-2: connection Error */public static int execsql (connection Conn, string plain text) {statement stmt = getstatement (conn); If (stmt = NULL) {return-2;} int I; try {I = stmt.exe cuteupdate (plain text) ;} Catch (sqlexception ex) {logger. getlogger (sqlhelper. class. getname ()). log (level. severe, null, ex); I =-1;} Close (stmt); return I;}/*** execute the SQL statement, the returned result is an integer * mainly used to execute non-query statements * @ Param plain text required? Parameter SQL statement * @ Param statement Params SQL statement parameter table * @ return non-negative number: normal execution;-1: execution error;-2: connection Error */public static int execsql (string plain text, object... extends Params) {preparedstatement pstmt = getpreparedstatement (plain text, plain Params); If (pstmt = NULL) {return-2;} int I; try {I = pstmt.exe cuteupdate ();} catch (sqlexception ex) {logger. getlogger (sqlhelper. class. getname ()). log (level. severe, null, ex); I =-1;} c Loseconnection (pstmt); return I;}/*** run the SQL statement. The returned result is an integer. * It is mainly used to execute non-query statements * @ Param conn database connection * @ Param plain text required? Parameter SQL statement * @ Param statement Params SQL statement parameter table * @ return non-negative number: normal execution;-1: execution error;-2: connection Error */public static int execsql (connection Conn, string plain text, object... extends Params) {preparedstatement pstmt = getpreparedstatement (Conn, plain text, callback Params); If (pstmt = NULL) {return-2;} int I; try {I = pstmt.exe cuteupdate ();} catch (sqlexception ex) {logger. getlogger (sqlhelper. class. getname ()). log (level. severe, Null, ex); I =-1;} Close (pstmt); return I;}/*** returns the value of a column in the first row of the result set, other SQL statements that ignore * @ Param plain text * @ return */public static object execscalar (string plain text) {resultset rs = getresultset (plain text); object OBJ = buildscalar (RS ); closeconnection (RS); Return OBJ;}/*** return the value of a column in the first row of the result set, others ignore * @ Param conn database connection * @ Param plain text SQL statement * @ return */public static object execscalar (connection Conn, stri Ng cmdtext) {resultset rs = getresultset (Conn, cmdtext); object OBJ = buildscalar (RS); closeex (RS); Return OBJ ;} /*** return the value of a column in the first row of the result set. Other values are required to ignore * @ Param plain text? Parameter SQL statement * @ Param statement Params SQL statement parameter table * @ return */public static object execscalar (string plain text, object... response Params) {resultset rs = getresultset (response text, response Params); object OBJ = buildscalar (RS); closeconnection (RS); Return OBJ ;} /*** return the value of a column in the first row of the result set. Other values are required to ignore * @ Param conn database connection * @ Param plain text? Parameter SQL statement * @ Param statement Params SQL statement parameter table * @ return */public static object execscalar (connection Conn, string plain text, object... response Params) {resultset rs = getresultset (Conn, plain text, callback Params); object OBJ = buildscalar (RS); closeex (RS); Return OBJ ;} /*** returns a resultset * @ Param plain text SQL statement * @ return */public static resultset getresultset (string plain text) {statement stmt = getstatement (); If (stmt = NULL) {return NULL;} Try {return stmt.exe cutequery (plain text);} catch (sqlexception ex) {logger. getlogger (sqlhelper. class. getname ()). log (level. severe, null, ex); closeconnection (stmt);} return NULL ;} /*** returns a resultset * @ Param conn * @ Param plain text SQL statement * @ return */public static resultset getresultset (connection Conn, string plain text) {statement stmt = getstatement (conn); If (Stmt = NULL) {return NULL;} Try {return stmt.exe cutequery (plain text);} catch (sqlexception ex) {logger. getlogger (sqlhelper. class. getname ()). log (level. severe, null, ex); close (stmt);} return NULL;}/*** returns a resultset * @ Param plain text required? Parameter SQL statement * @ Param limit Params SQL statement parameter table * @ return */public static resultset getresultset (string plain text, object... response Params) {preparedstatement pstmt = getpreparedstatement (plain text, plain Params); If (pstmt = NULL) {return NULL;} Try {return pstmt.exe cutequery ();} catch (sqlexception ex) {logger. getlogger (sqlhelper. class. getname ()). log (level. severe, null, ex); closeconnection (pstmt);} retu Rn NULL;}/*** returns a resultset * @ Param conn database connection * @ Param plain text required? Parameter SQL statement * @ Param limit Params SQL statement parameter table * @ return */public static resultset getresultset (connection Conn, string limit text, object... extends Params) {preparedstatement pstmt = getpreparedstatement (Conn, plain text, callback Params); If (pstmt = NULL) {return NULL;} Try {return pstmt.exe cutequery ();} catch (sqlexception ex) {logger. getlogger (sqlhelper. class. getname ()). log (level. severe, null, ex); close (P Stmt);} return NULL;} public static object buildscalar (resultset RS) {If (rs = NULL) {return NULL;} object OBJ = NULL; try {If (RS. next () {OBJ = Rs. getObject (1) ;}} catch (sqlexception ex) {logger. getlogger (sqlhelper. class. getname ()). log (level. severe, null, ex);} return OBJ;}/*** get a data model with the update function. If you only need to read data, don't use it. * @ Param plain text returns the SQL statement for querying a dataset * @ return table data model ** where dataset is not found The public static dataset getdataset (string plain text) {statement stmt = getstatement (); dataset DBC = new dataset (); if (stmt = NULL) {DBC. code =-2; return DBC ;}try {// query statement DBC. rs = stmt.exe cutequery (plain text); DBC. model = buildtablemodel (DBC. RS); DBC. code = DBC. model. getrowcount ();} catch (sqlexception ex) {logger. getlogger (sqlhelper. class. getname ()). log (level. severe, null, Ex); DBC. code =-1;} return dBc;} * // *** obtain a data model with the update function. If you only need to read data, do not use it * @ Param conn database connection * @ Param plain text returns the SQL statement for querying a dataset * @ return table data model ** same method as public static dataset getdataset (connection conn, string plain text) {statement stmt = getstatement (conn); dataset DBC = new dataset (); If (stmt = NULL) {DBC. code =-2; return DBC ;}try {// query statement DBC. rs = stmt.exe cutequery (plain text); DBC. m Odel = buildtablemodel (DBC. RS); DBC. code = DBC. model. getrowcount ();} catch (sqlexception ex) {logger. getlogger (sqlhelper. class. getname ()). log (level. severe, null, ex); DBC. code =-1;} return dBc;} * // *** obtain a data model with the update function. If you only need to read data, do not use it * @ Param plain text required? Parameter SQL statement * @ Param multiple Params SQL statement parameter table * @ return table data model ** same as the preceding method ** public static dataset getdataset (string plain text, object... extends Params) {preparedstatement pstmt = getpreparedstatement (plain text, plain Params); dataset DBC = new dataset (); If (pstmt = NULL) {DBC. code =-2; return DBC ;}try {// query statement DBC. rs = pstmt.exe cutequery (); DBC. model = buildtablemodel (DBC. RS); DBC. code = DBC. model. get Rowcount ();} catch (sqlexception ex) {logger. getlogger (sqlhelper. class. getname ()). log (level. severe, null, ex); DBC. code =-1;} return dBc;} * // *** obtain a data model with the update function. If you only need to read data, don't use it * @ Param conn database connection * @ Param plain text required? Parameter SQL statement * @ Param limit Params SQL statement parameter table * @ return table data model ** same as * Public static dataset getdataset (connection Conn, string plain text, object... extends Params) {preparedstatement pstmt = getpreparedstatement (Conn, plain text, writable Params); dataset DBC = new dataset (); If (pstmt = NULL) {DBC. code =-2; return DBC ;}try {// query statement DBC. rs = pstmt.exe cutequery (); DBC. model = buildtablemodel (DBC. RS); DBC. code = DBC. model. getrowcount ();} catch (sqlexception ex) {logger. getlogger (sqlhelper. class. getname ()). log (level. severe, null, ex); DBC. code =-1;} return dBc;} */Private Static void close (Object OBJ) {If (OBJ = NULL) {return;} Try {If (OBJ instanceof Statement) {(statement) OBJ ). close ();} else if (OBJ instanceof preparedstatement) {(preparedstatement) OBJ ). close ();} else if (OBJ instanceof resultset) {(resultset) OBJ ). close ();} else if (OBJ instanceof connection) {(connection) OBJ ). close () ;}} catch (sqlexception ex) {logger. getlogger (sqlhelper. class. getname ()). log (level. severe, null, ex) ;}} Private Static void closeex (Object OBJ) {If (OBJ = NULL) {return ;}try {If (OBJ instanceof Statement) {(statement) OBJ ). close ();} else if (OBJ instanceof preparedstatement) {(preparedstatement) OBJ ). close ();} else if (OBJ instanceof resultset) {(resultset) OBJ ). getstatement (). close ();} else if (OBJ instanceof connection) {(connection) OBJ ). close () ;}} catch (sqlexception ex) {logger. getlogger (sqlhelper. class. getname ()). log (level. severe, null, ex) ;}} Private Static void closeconnection (Object OBJ) {If (OBJ = NULL) {return ;}try {If (OBJ instanceof Statement) {(statement) OBJ ). getconnection (). close ();} else if (OBJ instanceof preparedstatement) {(preparedstatement) OBJ ). getconnection (). close ();} else if (OBJ instanceof resultset) {(resultset) OBJ ). getstatement (). getconnection (). close ();} else if (OBJ instanceof connection) {(connection) OBJ ). close () ;}} catch (sqlexception ex) {logger. getlogger (sqlhelper. class. getname ()). log (level. severe, null, ex );}}}