Preface This small database Operation package frame is to refer to IBM Development Online and expand some features on the basis of it. So first, thank the author of the two articles. Since studying JDBC, I have always wanted a simple package to facilitate programming, but because there is no better approach, I'm thinking of 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?). The two articles of IBM Development Network are "a simple JDBC wrapper" "" For a simple JDBC packager, my email is Xsimple2003@yahoo.com.cn, please contact me. Design ideas? Abstract DBMS abstract into class Database, this class 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. • Abstract Class ROW in the database table, this class uses the HashMap to save the field names and values of a line of data in the table in the table in the database and provide some relevant operations. In addition, this class also provides two static methods for easy conversion between ROW objects and ValueObject. • Abstract 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. Database Class source code as follows: package com.gdrj.util.database; import java.sql. *; Import javax.sql. *; Import com.gdrj.util.serviceLocator. *; Public class database {/ ** * This database Connection members only have valid * / private connection conn = null; / ** * When this parameter is valid, the program is connected to the database, not from the connection pool. * / Private string URL, User, Password; / ** * When this parameter is valid, it indicates that the program is connected from 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, ST The Ring Password) constructor performs initialization. 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.
• TABLE Class source code is as follows: package com.gdrj.util.database; import java.sql. *; Import java.util. *; Import com.gdrj.util. *; Public class table {/ ** * via this database object Get the database connection * / private database database; / ** * Name * / private string name; / ** * initialize the table object in the database in the database, this time does not do any database related operations * General Call * @Param Database * @Param Name * / Public Table (THIS) {this.Database = Database;} / ** * 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 multiple rows * @param query criteria parameter list query condition * @param args * @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, indicate all fields in the query table. * @Param Where query criteria entered by the user condition parameter array * * @param args used @return returns results for rows set * / 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 (); // Get database connection, in-way Different connection conditions have been processed SQL = "SELECT" Fields "from" Name (criteria == NULL)? ": (" Where " criteria); prepletedStatement PSTMT =
Conn.preparestatement (SQL); if (args! = null) {// If there is a query parameter set parameter for (int i = 0; i / ** * 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; // Execute SQ L number IF (conditions == NULL) {ss = "INTO" Name "("; for (int i = 0; i = ")";} Else {ss = "update" name "set"; for (int i = 0; i ((Row.get (I) == NULL? "is null": "=?"); // Set the query parameter has a null value processing IF (i! = row.length () - 1) {ss = "And";}} connection.getConnection (); preparedStatement St = conn.preparestatement (ss); int J = 0; // Query Parameter counter for (INT i = 0; i < Row.Length (); i ) {if (Row.get (i)! = null) {st.setobject ( J, row.get (i)); // Analysis query parameter}} AffectableRow = st.executeUpdate (); St.close ();} catch new dbaccessException (INFORGETER.GETERRORINFOR (this, "DELROW", EX, "Delete the data in the table" Name "!")); } Finally {database.disconnect (conn);} Return AffectableRow;} / ** * Conditional deletion is deleted Multi-line * @Param Condition * @Param args * / public int Delrow (String Condition, Object [] args) THROWS DBACCESSEXCEPTION {String SS = ""; int acidTableRow = 0; ss = "delete from" name "where"; ss = condition; connection conn = null; try {c ONN = Database.getConnection (); preparedStatement St = conn.preparestatement (SS); if (args! = null) {for (int i = 0; i } Return AffectableRow;}} You can get a Table object with the GetTable method of the Database object when you are used. After getting this object, you can operate this database table, this class provides six ways to add a modified deletion of database tables based on the parameters passed. There is no particularly difficult to understand in the code, you need to pay attention to the processing of empty values on the basis of the original code. If the data in the table is empty, then I put the Java type corresponding to the field to ROW. In the object, because the Java Reflection API is used in the conversion of the ROW object to the value object, you must know the type of field values in the ROW to go to the setxxxx method of the value object (see the ToValueObject method of the ROW object). • 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, due to HashTable does not provide a method of acquiring value by an index, and the key value pair is not arranged in the order of 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, get the field value * @Param which * @return * / public 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.Elementat (Which); return key;} / ** * Print, used to debug * / PUBL IC 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 object to the value of the value * @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 conversions * / 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 In order to make it easy to use a Generaldao class (I am also in understanding the DAO mode, please ask the master criticism advice) code as follows: package com.gdrj.util.database; import java.util. *; Public class generaldao {/ * * * This DAO corresponds to the table object * / private table table; / ** * Default constructor * / public generaldao () {} / ** * Initialize DAO * @Param DB * @Param Tablename * with database objects and table names / Public generaldao (Database DB, String TableName) {GetTable (DB, TableName);} Private Void gettable (Database DB, String Name) {Table = DB.GetTable (Name);} / ** * According to the condition Data Returns * @Param Fields To find the field (* or null means all fields) * @Param criteria query criteria * @Param args and query conditions correspond to the parameter array * @Param Votype Value Object Type * @return * @throws java.lang.Exception * / public Collection findDatas (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 Return * @Throws java.lang.exception * / public int deletedata (object vo) throws exception {returnTTurn Table.delrow (Row.FromValueObject (VO));} / ** * Delete multiple data * @Param condition delete condition * @Param Args and Conditions Parameter Array * @Return * @Throws Java.lang.Exception * / Public Int deletedAns (String Condition, Object [] args) throws Exception {returnTable.delrow (condition, args);}} The DAO class 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. Demo first establish a teacher table, the following syntax 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 object class 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: package org.together.jd bcwrap.test; import java.util *;. import com.gdrj.util.database *;. public class GeneralDAOExample {public static void main (String [] args) throws Exception {Class.forName ( "sun.jdbc.odbc. JDBCODBCDRIVER "); Database DB = New Database (" JDBC: ODBC: Emmis "," SA "," 815023 "); Generaldao Dao = New Generaldao (DB," Teacher "); / ** * Use GeneralDao to query * / Collection col = DAO.FindData ("*", "birthday is null", null, teachervo.class; for (item.hasnext (); itute.hasnext ();) {Object item = iter.next ); System.out.println ("item =" item);} / ** * Use GeneralDao to add * / teachervo vo = new teachervo (); vol.Setaddress ("Shenyang"); Vo.SetBirthDay (new java.sql .Timestamp (0)); vol.setId (new integer (11)); vol.setIncome (new java.math.bigdecimal (1000)); vol.setname ("Tao Xiaochuan"); // dao.insertdata (VO ); // Add a record // dao.updatedata (Vo, "ID = 10", null); // update a record // DAO.DELETEDATA (VO); // Delete a record // DAO.DELETEDAS (" ID> 5 ", NULL); // Add qualified record}} Author BLOG: http://blog.9cbs.net/chensheng913/
