Connect Java to the Oracle database --- sqlhelper class (example in Han shunping's video)

Package com. xuankai. JDBC;

Import java. Io. fileinputstream;
Import java. Io. ioexception;
Import java. SQL. connection;
Import java. SQL. drivermanager;
Import java. SQL. resultset;
Import java. SQL. sqlexception;
Import java. SQL. statement;
Import java. util. properties;
Import java. SQL .*;

Public class sqlhelper
{
// Define variables
Private Static connection Ct = NULL;
// Replace statement with preparedstatement in most cases
Private Static preparedstatement PS = NULL;
Private Static resultset rs = NULL;

// Database connection Parameters
Private Static string url = "";
Private Static string username = "";
Private Static string driver = "";
Private Static string passwd = "";

Private Static callablestatement cs = NULL;
Public static callablestatement getcs ()
{
Return Cs;
}

Private Static Properties pp = NULL;
Private Static fileinputstream FCM = NULL;
// Load the driver, only once, with a static code block
Static
{
Try
{
// From dbinfo. Properties
Pp = new properties ();
FD = new fileinputstream ("dbinfo. properties ");
Pp. Load (FCM );
Url = pp. getproperty ("url ");
Username = pp. getproperty ("username ");
Driver = pp. getproperty ("driver ");
Passwd = pp. getproperty ("passwd ");

Class. forname (driver );
}
Catch (exception E)
{
E. printstacktrace ();
}
Finally
{
Try
{FCM. Close ();}
Catch (ioexception e) {e. printstacktrace ();}
FS = NULL; // clean up on the recycle bin
}

}
// Get the connection
Public static connection getconnection ()
{
Try
{Ct = drivermanager. getconnection (URL, username, passwd );}
Catch (exception e) {e. printstacktrace ();}
Return CT;
}


// ************* Callpro1 stored procedure function 1 *************
Public static callablestatement callpro1 (string SQL, string [] parameters)
{
Try {
Ct = getconnection ();
Cs = CT. preparecall (SQL );
If (parameters! = NULL ){
For (INT I = 0; I <parameters. length; I ++ ){
CS. setobject (I + 1, parameters [I]);
}
}
Cs.exe cute ();
}
Catch (exception e) {e. printstacktrace (); throw new runtimeexception (E. getmessage ());}
Finally
{Close (RS, Cs, CT );}
Return Cs;
}

// ******************* Callpro2 Stored Procedure 2 *************** *********
Public static callablestatement callpro2 (string SQL, string [] inparameters,
Integer [] outparameters)
{
Try
{
Ct = getconnection ();
Cs = CT. preparecall (SQL );
If (inparameters! = NULL)
{
For (INT I = 0; I <inparameters. length; I ++)
{
CS. setobject (I + 1, inparameters [I]);
}
}
// CS. registeroutparameter (2, Oracle. JDBC. oracletypes. cursor );
If (outparameters! = NULL)
{
For (INT I = 0; I <outparameters. length; I ++)
{
CS. registeroutparameter (inparameters. Length + 1 + I, outparameters [I]);
}
}
Cs.exe cute ();
}
Catch (exception e ){
E. printstacktrace (); throw new runtimeexception (E. getmessage ());
}
Finally
{

}
Return Cs;
}
Public static resultset executequery (string SQL, string [] parameters)
{
Try
{
Ct = getconnection ();
PS = CT. preparestatement (SQL );
If (parameters! = NULL)
{
For (INT I = 0; I <parameters. length; I ++)
{
PS. setstring (I + 1, parameters [I]);
}
}
Rs = ps.exe cutequery ();
}
Catch (exception E)
{
E. printstacktrace ();
Throw new runtimeexception (E. getmessage ());
}
Finally
{

}
Return Rs;
}


Public static connection getct ()
{
Return CT;
}

Public static preparedstatement getps ()
{
Return pS;
}

Public static resultset getrs ()
{
Return Rs;
}

Public static void executeupdate2 (string [] SQL, string [] [] parameters)
{
Try
{
Ct = getconnection ();
Ct. setautocommit (false );

For (INT I = 0; I <SQL. length; I ++)
{

If (null! = Parameters [I])
{
PS = CT. preparestatement (SQL [I]);
For (Int J = 0; j <parameters [I]. length; j ++)
{
PS. setstring (J + 1, parameters [I] [J]);
}
Ps.exe cuteupdate ();
}

}


Ct. Commit ();


} Catch (exception E)
{
E. printstacktrace ();
Try
{
Ct. rollback ();
}
Catch (sqlexception E1)
{
E1.printstacktrace ();
}
Throw new runtimeexception (E. getmessage ());
} Finally
{
Close (RS, PS, CT );
}

}

// Write an update, delete, and insert statement first.
// SQL format: Update table name: Set field name =? Where field =?
// Parameter should be ("ABC", 23)
Public static void executeupdate (string SQL, string [] parameters)
{
Try
{
Ct = getconnection ();
PS = CT. preparestatement (SQL );
If (parameters! = NULL)
{
For (INT I = 0; I <parameters. length; I ++)
{
PS. setstring (I + 1, parameters [I]);
}

}
Ps.exe cuteupdate ();
}
Catch (exception E)
{
E. printstacktrace (); // development stage
// Throw an exception
// It can be processed or not.
Throw new runtimeexception (E. getmessage ());
}
Finally
{
Close (RS, PS, CT );
}
}

Public static void close (resultset RS, statement ps, connection CT)
{
// Close the resource (first enable and then disable)
If (RS! = NULL)
{
Try
{
Rs. Close ();
}
Catch (sqlexception E)
{
E. printstacktrace ();
}
Rs = NULL;
}
If (PS! = NULL)
{
Try
{
PS. Close ();
}
Catch (sqlexception E)
{
E. printstacktrace ();
}
PS = NULL;
}
If (null! = CT)
{
Try
{
Ct. Close ();
}
Catch (sqlexception E)
{
E. printstacktrace ();
}
Ct = NULL;
}
}
}