Java generic DBHelper class __java

package com.wang.yjs.business.util;

Import java.sql.*;
Import java.util.*;
Import javax.servlet.jsp.jstl.sql.*;

/**
* Common JDBC Database access classes
* @author
* @version 1.0
* January 2009 12
*/

public class DB {
Private Connection Conn;
Private String Sqlvalue;
Private List values;

/**
* Set Connection Class
* @param Conn Database connection
*/
public void SetConnection (Connection conn) {
This.conn = conn;
}

/**
* Set SQL statement
* @param values SQL statements
*/
public void Setsqlvalues (String sqlvalue) {
This.sqlvalue = Sqlvalue;
}

/**
* Set parameters for SQL statements
* @param values SQL statement parameters
*/
public void Setvalues (List values) {
This.values = values;
}

/**
* Execute Query
* @return Result Data set
* @throws SQLException
*/
Public result ExecuteQuery () throws SQLException {
result result = NULL;
ResultSet rs = null;
PreparedStatement pstmt = null;
Statement stmt = null;
try{
if (values!= null && values.size () > 0) {
pstmt = Conn.preparestatement (Sqlvalue);
Setvalues (pstmt, values);
rs = Pstmt.executequery ();
}else{
stmt = Conn.createstatement ();
rs = Stmt.executequery (Sqlvalue);
}
result = Resultsupport.toresult (RS);
}finally{
Closers (RS);
Closestmt (stmt);
Closepstmt (PSTMT);
Sqlvalue = null;
values = NULL;
}
return result;
}


/**
* EXECUTE UPDATE statement
* Number of rows affected @return execution
* @throws SQLException
*/
public int executeupdate () throws SQLException {
int noofrows = 0;
PreparedStatement pstmt = null;
Statement stmt = null;
try{
if (values!= null && values.size () > 0) {
pstmt = Conn.preparestatement (Sqlvalue);
Setvalues (pstmt, values);
Noofrows = Pstmt.executeupdate ();
}else{
stmt = Conn.createstatement ();
Noofrows = Stmt.executeupdate (Sqlvalue);
}
}finally{
Closestmt (stmt);
Closepstmt (PSTMT);
Closeconn ();
Sqlvalue = null;
values = NULL;
}
return noofrows;
}

/**
* Set the parameters of the statement
* @param preparedstatement objects
* @param values parameter list
* @throws SQLException
*/
private void Setvalues (PreparedStatement pstmt, List values) throws SQLException {
for (int i = 0; i < values.size (); i++) {
Object v = values.get (i);
Pstmt.setobject (i+1, v);
}
}

/**
* Close Database connection
*/
public void Closeconn () {
try {
if (this.conn!= null) {
This.conn.close ();
}
catch (SQLException e) {
E.printstacktrace ();
}
}

/**
* Close Statement
* @param stmt
*/
private void Closestmt (Statement stmt) {
try {
if (stmt!= null) {
Stmt.close ();
}
catch (SQLException e) {
E.printstacktrace ();
}
}
/**
* Close PreparedStatement
* @param pstmt
*/
private void Closepstmt (PreparedStatement pstmt) {
if (pstmt!= null) {
try{
Pstmt.close ();
}catch (SQLException e) {
Todo
}
}
}

/**
* Close ResultSet
* @param RS
*/
private void Closers (ResultSet rs) {
try {
if (Rs!= null) {
Rs.close ();
}
catch (SQLException e) {
E.printstacktrace ();
}
}
}

testing Generic DBHelper classes

package com.wang.owbHome.business.util;

Import java.sql.Connection;
Import Java.sql.DriverManager;
Import java.sql.SQLException;
Import java.util.*;
Import Javax.servlet.jsp.jstl.sql.Result;

Import Junit.framework.TestCase;

public class Dbtest extends TestCase {
Connection conn = null;
protected void SetUp () throws Exception {
Class.forName ("Com.microsoft.sqlserver.jdbc.SQLServerDriver");

conn = Drivermanager.getconnection ("jdbc:sqlserver://localhost:1433;" +
"DATABASENAME=QQDB;USER=SA;PASSWORD=123456A;");
}
/**
* Test DB no-parameter queries
*/
public void Testdb_executequery () {
DB db = new db ();
Db.setconnection (conn);
String SQL1 = "SELECT * from Qquser";
Db.setsqlvalues (SQL1);
try {
Result rs = Db.executequery ();

if (rs = = null | | Rs.getrowcount () = = 0) {
System.out.println ("no results");
}else{
SYSTEM.OUT.PRINTLN ("Total" + rs.getrowcount () + "Results!");
}
Db.closeconn ();
catch (SQLException e) {
TODO auto-generated Catch block
E.printstacktrace ();
}
}

/**
* Test db-with-parameter queries
*/
public void Testdb_executequery_param () {
DB db = new db ();
Db.setconnection (conn);
StringBuffer SQL1 = new StringBuffer ();
Sql1.append ("select * from qquser where OnLine =?");
System.out.println ("SQL1:" + sql1.tostring ());
Db.setsqlvalues (Sql1.tostring ());
List values = new ArrayList ();
Values.add (New Integer (1));
Db.setvalues (values);
try {
Result rs = Db.executequery ();

if (rs = = null | | Rs.getrowcount () = = 0) {
System.out.println ("no results");
}else{
SYSTEM.OUT.PRINTLN ("Total" + rs.getrowcount () + "Results!");
}
Db.closeconn ();
catch (SQLException e) {
TODO auto-generated Catch block
E.printstacktrace ();
}
}

/**
* Test db for non-parametric modifications
*/
public void Testdb_executeupdate () {
DB db = new db ();
Db.setconnection (conn);
String SQL1 = "Update qquser set OnLine = 0 where parentid = 1";
Db.setsqlvalues (SQL1);
try {
int rs = Db.executeupdate ();
SYSTEM.OUT.PRINTLN ("Shared data" + RS + "Modify!");
catch (SQLException e) {
TODO auto-generated Catch block
E.printstacktrace ();
}
}

/**
* Test DB for a parameter modification
*/
public void Testdb_executeupdate_param () {
DB db = new db ();
Db.setconnection (conn);
String SQL1 = "Update qquser set OnLine = 0 where parentid =?";
System.out.println ("SQL1:" + SQL1);
Db.setsqlvalues (SQL1);
List values = new ArrayList ();
Values.add (New Integer (1));
Db.setvalues (values);
try {
int rs = Db.executeupdate ();
SYSTEM.OUT.PRINTLN ("Shared data" + RS + "Modify!");
catch (SQLException e) {
TODO auto-generated Catch block
E.printstacktrace ();
}
}
}