A simple and practical database operation framework

For the preface to learn JDBC, I have always wanted a simple package to facilitate programming, but because there is no better approach, I've seen the two articles on IBM development online, I feel that the author's design ideas can be expanded into a practical JDBC. Package. So I added some functions on the source code provided by the article, these features include supporting multiple data types, handling null values, using reflection to convert between ROW objects and value objects, and add a self-thinking A generic DAO class to facilitate the user's operation.

I provide the source code to have two purposes. I hope that I can help beginner more than JDBC than me. In addition, I would like to enlighten me. If you have an error in the process, if you can provide your JDBC's packaging method It is better (don't say that you only use EJB or Hibernate, what is JDO?).

Design ideas Abstract DBMS into class Database, which is responsible for managing database connections and providing table objects. Abstract one or more tables in the database becomes class Table, which provides the added, modified, and deleted JDBC packages in the table. Record a database table in the database table Row, this class saves the field name and value of a line of data in the table in the table in the HashMap and provides some relevant operations. In addition, this class also provides two static methods for easy conversion between ROW objects and ValueObject. Put the collection of ROWs into RowSet, this class saves multiple ROW objects in this class and provides some relevant operations. The code analysis has explained the key and needs to be aware of the key and need to pay attention to the source code, and you can perform the source code as a demonstration. The Database Source Code is as follows:

Package com.gdrj.util.database; import java.sql. *; import javax.sql. *; import com.gdrj.util.serviceLocator. *; public class database {/ ** * This database connection member is only in the database It is effective * / private connection conn = null; / ** * When this parameter is valid, it indicates that the program is directly connected to the database instead of obtaining the connection in the connection pool * / private string URL. User, password; / ** * When this parameter is valid, the program is connected to the connection pool. * / Private string DataSource; / ** * Initialize the database object with the database address, the username, password, which is used for the program to be directly * to establish a connection with the database. * @Param URL * @Param User * @Param Password * / Public Database (String Url, String User, String Password) {this.url = url; this.user = user; this.password = password;} / ** * Initialize the database object with the JNDI data source name, this constructor is used to take a database connection from the connection pool. * @Param DataSource * / public database (String DataSource) {this.datasource = DataSource;} / ** * Get database connections, doing automatic processing for use from connecting connections from the connection pool, which constructor is called according to user Judging whether to connect directly to the database or take a connection from the connection pool. * For the user, it is not considered that the program has been connected there, and he is only the correct initialization database object.

* @Return * @Throws SQLEXCEPTION * / PUBLIC Connection getConnection () throws exception {if (DataSource == NULL) {// Direct connection IF (conn == null) {conn = drivermanager.getConnection (URL, User, "directly with the database Password);}} else {// Get connected from the application server's connection pool to the connection service.getinstance (); DataSource DS = sl.getDataSource (Datasource); returnne DS.GetConnection (); // Each transfer Returns a database connection in a connection pool} Return Conn;} / ** * Release connection, if it is directly connected to the database connection, what does not do * If it is connected from the connection in the connection pool, then released * @Param Conn * / Public Void Disconnect (Connection Connect) {if (DataSource! = NULL) {// only processes the case from the connection pool Try {if (connection! = null) {connection.close ();}} Catch (Exception EX) {}}} / ** * Get the table object corresponding to the parameter name, note that this is not any database operation * @Param name * @Return * / public table gettable (String name) {Return New Table (THIS , Name);} This class is an abstraction of DBMS, so as long as there is a Database object in the application when using it, if you use the use of Database (String Url, String User, String Password constructor is initialized. If you use the Database (String DataSource) constructor to initialize the connection in the connection pool of the application server, you will not use this object to make GetConnection and disconnection, you don't have to consider keeping a connection (C / S) Also return the connection to the connection pool because it has been processed in Disconnection. Collective usage methods will TABLE class. The code taken from the connection pool in getConnection You only know what is going on with the service locator mode in the following J2EE core mode, you are initialized with Database (String Url, String User, String Password) The code when it doesn't work.

The TABLE source code is as follows:

Package com.gdrj.util.database; import java.sql. *; import java.util. *; import com.gdrj.util. *; public class table {/ ** * Get database connection through this database object * / private Database Database; / ** * Database in the database * / private string name; / ** * Initialize the table object, this time does not do any database related operation * Generally passed Database's getTable call * @Param Database * @Param Name * / Public Table (Database Database, String Name) {this.Database = Database; this.name = name;} / ** * Query a line * @return * / public row getRow (String Fields , String criteria, object [] args) throws dbaccessException {RowSet Rows = ExecuteQuery (Fields, criteria, args); if (rows == null) {return null;} return rows.get (0);} / ** * Get a multi-line recording * * @param criteria query args parameter list query condition @param * @return * / public RowSet getRows (String fields, String criteria, Object [] args) throws DBAccessException {return executeQuery (fields, criteria, args );} / ** * Execute SQL query * @Param Fields to query the field, if you pass NULL, indicating all fields in the query table * @Param criteria user input Query WHERE Condition * @Param Args Use * @return returns a set of results * / Private RowSet executeQuery (String fields, String criteria, Object [] args) throws DBAccessException {Connection conn = null; RowSet rows = new RowSet (); String sql = null; if (fields == null) {fields = "* "} Try {conn = Database.getConnection (); // acquire database connection, handled different connection conditions inside the method SQL =" SELECT " Fields " from " Name (criteria == null )? "": ("WHERE" CRITERIA)); preparedStatement PSTMT = conn.preparestatement (SQL); if (args! = null) {// Set parameter for (int i = 0; i < Args.length; i

) {PSTMT.SETOBJECT (i 1, args [i]);}}} resultset}}} resultset}; resultsetmetata rsmd = rs.getMetadata (); int cols = rsmd.getColumnCount (); / ** @ Todo Judging whether it is zero * / if (cols == 0) {RETURN NULL;} while (rs.next ()) {row rot = new row (); for (int i = 1; i <= cols; i ) { String name = rsmd.getColumnName (i); Object value = rs.getObject (i); // makes a generic type processing, so that the type in ROW is Object.

/ ** * Here you should do null value processing, because if you are a null value when running ROWTOVALUEOBJECT conversion, the type of value cannot be obtained * So if it is a null value, set value information * / if (value == null) {VALUE = Class.Forname (RSMD.GetColumnClassName (i));} // system.out.println (value.getclass ()); // Used to get what type of Row.Put in Java is used to get the type of database. Value);} rows.close (); pstmt.close (); pstmt.close ();} catCH (Exception ex) {throw new dbaccessException (this, "executeQuery", EX, "execute SQL (" SQL ") Error fails when query! "));} finally {database.disconnect (conn); // Call the release connection method of the database object (this method is handled in this method)} Return Rows;} / ** * Added * @Param Row * / Public INT PUTROW (ROW ROW) THROWS DBACCESSEXCEPTION {Return Putrow (Row, Null, Null);} / ** * Modify a line (no condition is increased) * @Param Row * @Param Conditions * / Public INT PUTROW (Row Row, String Conditions, Object [] args) THROWS DBACCESSEXCEPTION {STRING SS = ""; INT AffectableRow = 0; // The number of rows affects after SQL (Conditions == NULL) {ss = "INSERT INTO" Name "("; for (int i = 0 i

i) {string k = row.getKey (i); ss = k "=" ((rot ":"? "); // Set the query parameter IF (i! = row.length () - 1) {ss = ","}} ss = ";" ss = conditions;} connection conn = null; try {conn = database.getConnection () PreparedStatement St = Conn.PrepareStatement (SS); INT J = 0; // Query Parameter Counter for (INT i = 0; I

PreparedStatement St = conn.preparestatement (SS); INT j = 0; // Query Parameter Counter for (INT i = 0; i

The source code of the line object is as follows:

Package com.gdrj.util.database; import java.util. *; import java.math.bigDecimal; import java.lang.reflect. *; public class row {/ ** * Sort, because HashTable does not provide value by index The method, and the key value pair is not arranged in the order of the PUT.

* Note: The object added in the vector is ordered, that is, the sequential sequence is arranged and can be viewed according to index access, which can be seen as a variable size * List can replace Vector * / private vector ordering = new vector (); / ** * Store key value pair (Table field name and field value) * / private hashmap map = new hashmap (); public row () {} / ** * Additional key value pair in HashMap, ie field name Field value * @Param name * @Param value * / public void Put (String name, object value) {if (! Map.containskey (name)) {Order.adDelement (name); // Save the button} Map. PUT (Name, Value);} / ** * Get a number of fields in the line object * @return * / public int length () {return map.size ();} / ** * Field value according to the field name * @Param name * @return * / public object get (string name) {return map.get (name);} / ** * Number according to the field in HashMap gains field value * @Param which * @return * / public object Object Get (int which) {string key = (string) Ordering.Elementat (Which); return map.get (key);} / ** * get field name * @Param which * @return * / public string getKey (int which) {string key = (string) Ordering.ementat (which); return key;} / ** * Print, used to debug * / public void Dump () {for (Iterator E = Map.keyset (). Iterator (); E.hasNext ();) {String Name = (String) E.NEXT (); object value = map.get (name); System; .out.print (Name "=" value ",");} system.out.println ("");} / ** * Transfer the target object * @Param Row * @Param Type value Object Type * @Return * @Throws Java.lang.Exception This exception is generally processed in DAO, because DAO call * This method performs Row and ValueObject conversion * / public static Object TovalueObject (Row Row, Class Type) Throws Exception { Object vo = type.newinstance (); // Create a value object Field [] Fields = type.getDeclaredfields ();

// Get all fields in the value object (int i = 0; I

FSI_ID (now there is a case where there are two words * or more value object attribute name database table in the database table must be undersired) * @Param voname * @return * / public static string toinrowname (String voname) {StringBuffer SB = New StringBuffer (); for (int i = 0; i

The code of the RowSet is as follows:

Package com.gdrj.util.database; import java.util. *; public class rowset {private vector vector = new vector (); public rowset () {} public void add (row row) {vector.addeElement (row); } Public int length () {return vector.size (); public row get (int which) {if (length () <1) {return null;} else {return (row) vector.Elementat (Which);} } Public void dump () {for (Enumeration E = Vector.ersion (); E.hasMoreElements (); ((row) ney (()). Dump ();}}}

This class is to put the ROW object in the vector for operation. Not much to say. In order to make it easy to use a generaldao class (I am also in understanding the DAO model, please ask the master criticism advice) code as follows:

Package com.gdrj.util.database; import java.util. *; public class generaldao {/ ** * This DAO corresponds to table object * / private table table; / ** * Default constructor * / public generaldao () { } / ** * Initialize DAO * @Param DB * @Param TableName * / Public GeneralDao (DB, TABLENAME);} private void gettable (Database DB, String Name) ) {Table = db.gettable (name);} / ** * Return the data to which the data is found in the value of the value object collection (* or null means all fields) * @Param criteria Query Conditions * @Param args and query criteria * @Param votype Value object type * @return * @Throws java.lang.exception * / public Collection Findata (String Fields, String criteria, Object [] args, Class Votype) THROWS EXCEPTION {RowSet Rows = Table.Getrows (Fields, criteria, args); Collection Col = New ArrayList (); for (int i = 0; i

PARAM ARGS and Conditions Parameter Array * @Return * @Throws Java.lang.Exception * / Public Int DeletedAns (String Condition, Object [] args) throws exception {return table.delrow (condition, args);}} DAO Category is a convenient package for the Table class. Users If you create a Database object, a DAO object, a value object (corresponding table structure), then you can perform an instance to demonstrate the usage of this small frame. The demo first creates a Teacher table, the syntax is as follows

Create Table Teacher (ID INT NOT NULL, NAME VARCHAR (20) Not Null, Birthday SmallDatetime Null, Address Varchar (100) Null, Income Money Null, Constraint ID Primary Key Nonclustered (ID))

Then create a value corresponding to the TEacher table.

public class TeacherVO implements Serializable {private Integer id; private String name; private String address; private BigDecimal income; private java.sql.Timestamp birthday; public TeacherVO () {} public Integer getId () {return id;} public void setId ( Integer id) {this.id = id;} public String getName () {return name;} public void setName (String name) {this.name = name;} public java.sql.Timestamp getBirthday () {return birthday;} public void setBirthday (java.sql.Timestamp birthday) {this.birthday = birthday;} public String getAddress () {return address;} public void setAddress (String address) {this.address = address;} public java.math.BigDecimal GetIncome () {Return INCOME;} public void setIncome (java.math.bigdecimal income) {this.income = income;} public string toString () {return "Name:" ID "Name:" Name "birthday : " BirthDay " Address: " Address " Revenue: " Income;}}

The source code of the last main program is as follows: