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 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.parsWord = password;} / ** * Initialize the database object with JNDI data source name, this constructor is used for Connect the pool to take the database connection. * @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 this database object Database connection * / private database database; / ** * Name of one or more (query) tables in the database * / private string name; / ** * Initialize the table object, this time does not make any database related operation * General passed Database's getTable call * @Param Database * @Param name * / public table (Database.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 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 fields, if you pass NULL, indicate all fields in the query table * @PARAM CRITERIA user input Query WHERE Condition * @Param Args parameters used * @return array 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 (); // acquire database connection, internal to different connectivity, SQL = "SELECT" Fields inside the method "from" name (criteria == null)? ": (" Where " criteria); preparedStatement PSTMT = conn.pr EPARESTATEMENT (SQL); if (args! = null) {// If there is a query parameter set parameter for (int i = 0; i Return null;} while (rs.next ()) {row = new row (); for (int i = 1; i <= cols; i ) {string name = rsmd.getColumnname (i); Object value = rs .GetObject (i); // makes universal 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; // executes the number of rows IF (conditions == null) {ss = "INTO" Name "("; for (int i = 0; i Catch (Exception EX) {EX.PrintStackTrace (); throw new dbaccessException (this, "putrow", ex, "update the data in" Name "!"));} Finally {DATABASE .disconnect (conn);} / ** * Delete one line * @Param Row * / public int DELROW (Row Row) throwssexception {string ss = ""; int acidTableRow = 0; ss = "delete from" Name "where"; for (int i = 0; i / ** * Conditional deletion is deleted Multi-line * @Param Condition * @Param args * / public int DELROW (String condition, Object [] args "throws dbaccessException {string ss =" "; int AffectableRow = 0; ss = "delete from" name "where"; ss = condition; connection conn = null; try {conn = Database.getConnection (); preparedStatement St = conn.prepareStatement (SS); if (args! = null) {for (INT i = 0; i * / Private vector ordering = new vector (); / ** * Store key value pair (Table field name and field value) * / private hashmap map = new hashMap (); public row () {} / ** * HashMap, append key value, ie field name and field value * @Param name * @Param value * / public void put (string name, object value) {if (! Map.containskey (name)) {Ordering.adDelement (Name ); // save the keys} map.put (name, value);} / ** * Get the number of fields in the line object * @return * / public int length () {return map.size ();} / ** * obtains a field value based on the field name * @Param name * @Return * / public object get (since) {return map.get (name);} / ** * get field value according to the number of the field in HashMap * @Param which * @return * / public object GET (int which) {string key = (string) Ordering.Elementat (Which); return map.get (key);} / ** * get a field name according to field serial number * @param which * @return * / public string getKey (int which) {string key = (string) Ordering.Elementat (Which); Return Key;} / ** * Print, used to debug * / public void dump () {for (item E = map.keyset (). iterator (); e.hasnext ();) {string name = (string) E.NEXT (); object value = map.get (name); system.out.print (name "=" value ",");} system.out.println (");} / ** * Convert the target object * @Param Row * @Param Type Value Object Type * @return * @throws java.lang.Exception The exception here is generally processed in DAO, because DAO call * This method is 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 value objects All Field for (int i = 0; i Public Static Row fromValueObject (Object Vo) THROWS Exception {Row Row = New Row (); Class Type = Vo.getClass (); // Get Class for Reflection Field [] Fields = Type.GetDeclaredFields (); for INT i = 0; i 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.ements (); E.haSMoreElements ();) {((row) E.NEXTELEMENT ()). Dump ();}}} This class is to put ROW objects Put it to 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 (Database DB, String Tablename) { GetTable (DB, TableName);} private void gettable (data) {Table = db.gettable (name);} / ** * Return the data to which the data will be found in the form of value object collection * @Param Fields To find the field (* or null means all fields) * @Param criteria query condition * @Param args with the parameter array of query conditions * @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 * @Throws java.lang.exception * / public int deletedata (Object Vo) THROWS Exception {Return 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 {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. 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 "No .:" Name "Birthday:" Birthday "Address:" Address "Revenue:" income;}} The source code of the last main program is as follows:
