Package com.xlc.utils;
Import Java.io.FileInputStream;
Import java.io.IOException;
Import Java.io.InputStream;
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
{
Defining variables
private static Connection ct = null;
Use PreparedStatement instead of statement in most cases
private static PreparedStatement PS = null;
private static ArrayList rs = null;
Parameters for connecting to a database
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 InputStream FIS = null;
Load driver, only once, with static code block
Static
{
Try
{
From Dbinfo.properties
pp = new Properties ();
Fis=sqlhelper.class.getclassloader (). getResourceAsStream ("dbinfo.properties");
FIS = new FileInputStream ();
Pp.load (FIS);
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
{Fis.close ();}
catch (IOException e) {e.printstacktrace ();}
FIS = null;//garbage collection station to clean up
}
}
Get Connected
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.execute ();
}
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.execute ();
}
catch (Exception e) {
E.printstacktrace (); throw new RuntimeException (E.getmessage ());
}
Finally
{
}
Return CS;
}
public static ArrayList 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.executequery ();
}
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.executeupdate ();
}
}
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);
}
}
First write an update, delete, insert
SQL format: Update table name set field name =? where field =?
Parameter God 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.executeupdate ();
}
catch (Exception e)
{
E.printstacktrace ();//Development phase
Throw exception
Can be handled, or not handled
throw new RuntimeException (E.getmessage ());
}
Finally
{
Close (RS,PS,CT);
}
}
public static void Close (ResultSet rs,statement Ps,connection CT)
{
Close resources (turn on and off first)
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;
}
}
}
Dbinfo.properties configuration file Save database related *********************
#这是我的mysql配置
URL = jdbc:mysql://127.0.0.1/test//Database name (test)
Username =//User Name
Driver = Com.mysql.jdbc.Driver
passwd =//Password
SqlHelper class (By_ Hanshunping)
