java version SQLHelper

 

package com.jack.SQLHelper;

 

import java.sql. *;
import java.util.logging. *;
import javax.swing.table. *;

 

/ **
* SQL basics
* Through it, you can easily manipulate the database using JDBC
* @ author Null
* /
public class SQLHelper
{

 

/ **
* Drive
* /
public static String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
/ **
* connection string
* /
public static String url = "jdbc: sqlserver :/ / localhost: 1433; databaseName = J2ee;";
; / **
* username
* /
public static String user = "null";
/ **
* Password
* /
public static String password = "123456";

 

/ **
* not instantiate this class
* /
; private SQLHelper ()
{
}

 

/ **
* Get a database connection
* by setting the class driver / url / user / password which four static variable to set the database connection properties
* @ return the database connection
* /
public static Connection getConnection ()
{
try
{
/ / get the driver used here is sqljdbc_1.2.2828. 100_chs.exe, different versions of the driver, the statement is different
Class.forName (driver);
} catch (ClassNotFoundException ex)
{
; Logger.getLogger (SQLHelper.class.getName ()). log (Level.SEVERE, null, ex);
;}

 

try
{
; return DriverManager.getConnection (url, user, password);
} catch (SQLException ex) < br /> {
Logger.getLogger (SQLHelper. class.getName ()). log (Level.SEVERE, null, ex);
return null;
}
}

 

/ **
* Get a Statement
* This Statement has set a dataset can scroll, You can update
* @ return if the acquisition fails to return null, remember to check the return value when called
* /
public static Statement getStatement ()
{
Connection conn = getConnection ();
if (conn == null)
{
return null;
}
try
{
return conn.createStatement (ResultSet.TYPE_SCROLL_SENSITIVE,
; ResultSet.CONCUR_UPDATABLE);
/ / set a dataset can scroll, you can update
} catch (SQLException ex)
{
; Logger.getLogger (SQLHelper.class.getName ()). log (Level.SEVERE, null, ex);
; close (conn);
}
return null;
}

 

/ **
* Get a Statement
* This Statement has set a dataset can scroll, You can update
* @ param conn Database connection
* @ return if the acquisition fails to return null, remember to check the return value when called
* /
public static Statement getStatement (Connection conn)
{
; if (conn == null)
{
; return null;
}
; try
{

 

return conn.createStatement (ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
; / / Set the data sets can scroll, you can update
} catch (SQLException ex)
; {
Logger.getLogger (SQLHelper.class.getName ()). log (Level.SEVERE, null, ex);
return null;
;}
}

 

/ **
* Get a parameterized PreparedStatement
* The data set has been set PreparedStatement You can scroll, you can update
* @ param cmdText need? parameters SQL statement
* @ param cmdParams SQL statement parameter table
* @ return if the acquisition fails to return null, remember to check the return value when called
* /
public static PreparedStatement getPreparedStatement (String cmdText, Object ... cmdParams)
{
Connection conn = getConnection ();
if (conn == null)
{
return null;
}

 

PreparedStatement pstmt = null;
try
; {
pstmt = conn.prepareStatement (cmdText, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet. CONCUR_UPDATABLE);
int i = 1;
for (Object item: cmdParams)
{
pstmt.setObject (i, item);
; i + +;
}
} catch (SQLException e)
{
; e.printStackTrace ();
; close (conn);
}
return pstmt;
}

 

/ **
* Get a parameterized PreparedStatement
* The PreparedStatement has been set Data sets can be scrolled, you can update
* @ param conn Database connection
* @ param cmdText need? parameters SQL statement
* @ param cmdParams SQL statement parameter table
* @ return if the acquisition fails to return null, remember to check the return value when called
* /
public static PreparedStatement getPreparedStatement (Connection conn, String cmdText, Object ... cmdParams)
{
if (conn == null)
{
return null;
}

 

PreparedStatement pstmt = null;
try
; {
pstmt = conn.prepareStatement (cmdText, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet. CONCUR_UPDATABLE);
int i = 1;
for (Object item: cmdParams)
{
pstmt.setObject (i, item);
; i + +;
}
} catch (SQLException e)
{
; e.printStackTrace ();
; close (pstmt);
}
return pstmt;
}

 

/ **
* execute the SQL statement and returns the result as an integer
* mainly used for perform a non-query
* @ param cmdText SQL statement
* @ return non-negative: normal execution; -1: Execution error; - 2: Connection error
* /
public static int ExecSql (String cmdText)
{
Statement stmt = getStatement ();
if (stmt == null)
{
return -2;
}
int i;
try
{
i = stmt.executeUpdate (cmdText);
} catch (SQLException ex)
; {
Logger.getLogger (SQLHelper.class.getName ()). log (Level.SEVERE, null, < br /> ex);
i = -1;
}
closeConnection (stmt);
return i;
} < / p>  

/ **
* execute the SQL statement and returns the result as an integer
* mainly used for perform a non-query
* @ param cmdText SQL statement
* @ return non-negative: normal execution; -1: Execution error; - 2: Connection error
* /
public static int ExecSql (Connection conn, String cmdText)
{
Statement stmt = getStatement (conn);
if (stmt == null )
{
return -2;
}
int i;
try
{
i = stmt.executeUpdate (cmdText);

 

} catch (SQLException ex)
{
Logger.getLogger (SQLHelper.class.getName ()). log (Level.SEVERE, null,
; ex);
i = -1;
}
close (stmt);
return i;
}

 

/ **
* execute the SQL statement and returns the result as an integer
* mainly used for perform a non-query
* @ param cmdText need? parameters SQL statement
* @ param cmdParams SQL statement parameter table
* @ return non-negative: normal execution; -1: Execution error; -2: Connection error
* /
; public static int ExecSql (String cmdText, Object ... cmdParams)
{
PreparedStatement pstmt = getPreparedStatement (cmdText, cmdParams);
if (pstmt == null)
{
return -2;
} < br /> int i;
try
; {
i = pstmt.executeUpdate ();
} catch (SQLException ex)
{
; Logger.getLogger (SQLHelper.class.getName ()). log (Level.SEVERE, null,
ex);
; i = -1;
}
closeConnection (pstmt);
return i;
}

 

/ **
* execute the SQL statement and returns the result as an integer
* mainly used for perform a non-query
* @ param conn Database connection
* @ param cmdText need? parameters SQL statements
* @ param cmdParams SQL statement parameter table
* @ return non-negative: normal execution; -1: Execution error; -2: Connection error
* /
public static int ExecSql (Connection conn, String cmdText, Object ... cmdParams)
{< br /> PreparedStatement pstmt = getPreparedStatement (conn, cmdText, cmdParams);
if (pstmt == null)
{
return -2;
}
int i;
; try
{
; i = pstmt.executeUpdate ();

 

} catch (SQLException ex)
{
Logger.getLogger (SQLHelper.class.getName ()). log (Level.SEVERE, null, ex);
i = -1;
}
close (pstmt);
return i;
}

 

/ **
* returns the first row of the result set one value, the other ignored
; * @ param cmdText SQL statement
* @ return
* /
public static Object ExecScalar (String cmdText)
{
ResultSet rs = getResultSet (cmdText);
; Object obj = buildScalar (rs);
closeConnection (rs);
return obj;
}

 

/ **
* returns the first row of the result set one value, the other ignored
; * @ param conn Database connection
* @ param cmdText SQL statement
* @ return
* /
public static Object ExecScalar (Connection conn, String cmdText)
{
ResultSet rs = getResultSet (conn, cmdText);
Object obj = buildScalar (rs);
closeEx (rs);
return obj;
}

 

/ **
* returns the first row of the result set one value, the other ignored
; * @ param cmdText need? parametric SQL statements
* @ param cmdParams SQL statement parameter table
* @ return
* /
public static Object ExecScalar (String cmdText, Object ... cmdParams)
{
ResultSet rs = getResultSet (cmdText, cmdParams);
Object obj = buildScalar (rs);
closeConnection (rs);
return obj;
}

 

/ **
* returns the first row of the result set one value, the other ignored
; * @ param conn Database connection
* @ param cmdText need? parametric SQL statements
* @ param cmdParams SQL statement parameter table
* @ return
* /
public static Object ExecScalar (Connection conn, String cmdText, Object ... cmdParams)
{
ResultSet rs = getResultSet (conn, cmdText, cmdParams);
Object obj = buildScalar (rs);
closeEx (rs);
return obj;
}

 

/ **
* return a ResultSet
* @ param cmdText SQL statement
* @ return
* /
public static ResultSet getResultSet (String cmdText)
; {
Statement stmt = getStatement ();
if (stmt == null)
{
return null;
}
try
{
return stmt.executeQuery (cmdText);
;} catch (SQLException ex)
{
Logger.getLogger (SQLHelper.class.getName ()). log (Level.SEVERE, null, ex);
closeConnection (stmt);
}
return null;
}

 

/ **
* return a ResultSet
* @ param conn
* @ param cmdText SQL statement
* @ return
* /
; public static ResultSet getResultSet (Connection conn, String cmdText)
{
Statement stmt = getStatement (conn); < br /> if (stmt == null)
{
; return null;
}
; try
{
; return stmt.executeQuery (cmdText);
} catch (SQLException ex)
; {
Logger.getLogger (SQLHelper.class.getName ()). log (Level.SEVERE , null, ex);
close (stmt);
;}
return null;
}

 

/ **
* return a ResultSet
* @ param cmdText need? parametric SQL statements
* @ param cmdParams SQL statement parameter table
* @ return
* /
public static ResultSet getResultSet (String cmdText, Object ... cmdParams)
{
; PreparedStatement pstmt = getPreparedStatement (cmdText, cmdParams);
if (pstmt == null)
{
return null;
}
try
{
; return pstmt.executeQuery ();
} catch (SQLException ex)
{
Logger.getLogger (SQLHelper . class.getName ()). log (Level.SEVERE, null, ex);
closeConnection (pstmt);
}
return null;
}

 

/ **
* return a ResultSet
* @ param conn Database connection
* @ param cmdText need? parametric SQL statements
* @ param cmdParams SQL statement parameter table
; * @ return
* /
public static ResultSet getResultSet (Connection conn, String cmdText, Object ... cmdParams)
; {
PreparedStatement pstmt = getPreparedStatement (conn, cmdText, cmdParams);
if (pstmt == null)
{
return null;
}
try
{
return pstmt.executeQuery (); < br />} catch (SQLException ex)
{
Logger.getLogger (SQLHelper.class.getName ()). log (Level.SEVERE, null, ex);
close (pstmt);
}
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;
;}

 

/ **
* from the ResultSet build DefaultTableModel
* @ param rs ResultSet
* @ return
* /
public static DefaultTableModel buildTableModel (ResultSet rs)
{
if (rs == null)
{< br /> return null;
}
ResultSetMetaData rsm;
String [] columnNames = null; / / column header
Object [] [] data = null; / / data items
DefaultTableModel model; / / table model
try
{
; / / query
rsm = rs . getMetaData ();
/ / determine when updates to the database can be achieved
; if (rs.getConcurrency () == ResultSet.CONCUR_UPDATABLE)
{
System.out.println ("Can UPDATABLE ");
} else
{
System.out.println (" ; Only Ready ");
}
; / / Get column headings
columnNames = new String [rsm.getColumnCount () ];
for (int i = 0; i