Java version of sqlhelper, pure JDBC tool class

Source: Internet
Author: User
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 );}}}

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.