This small database operating 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 member only is valid if it is directly established with the database.
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 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 * established 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 the database connection from the connection pool. * @Param DataSource * /
Public Database (String DataSource) {this.datasource = DataSource;}
/ ** * Get the database connection, to determine whether the user calls from the connection pool to establish a connection directly to the database or 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 to the database
IF (conn == null) {conn = drivermanager.getConnection (URL, User, Password);}}}} else {// Get a connection from the connection pool of the application server
ServiceLocator SL = ServiceLocator.getInstance (); DataSource DS = SL.GetDataSource (DataSource); Return DS.GetConnection (); // Each call returns a connection pool in a connection pool connection
} Return conn;
/ ** * Release the connection, if it is directly connected to the database connection, what does not do * If it is a connection from the connection pool, then released * @Param conn * /
Public Void Disconnect (Connection Connection) {if (DataSource! = NULL) {// only processes the connection 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);}} This class is an abstraction of DBMS, so as long as there is a Database object in the application when using it, if you are between Use the connection to use then you initialize with the Database (String Url, String User, String Password) constructor. 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 connections through this database * /
Private Database DataBase;
/ ** * Name of one or more (query) tables in the database * / private string name;
/ ** * Initialize the table object, do not do any database related operation * General passed through Database GetTable * @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 record * @Param criteria query condition * @Param args query condition parameter list * @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 * @Param criteria users entered in the query table WHERE Condition * @param args Number * @return Return Compliance 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 (); // Get database connections, processing different connectivity inside the method
SQL = "SELECT" Fields "from" Name ((criteria == null)? ": (" Where " criteria); preparedStatement Pstmt = conn.prepareStatement (SQL); if (args! = null ) {// set parameters if there is a query parameter
For (int i = 0; i / ** * Here you need to do null value processing, because if you have a null value when performing ROWTOVALUEOBJECT conversion, you cannot get the value of the value *, so if it is a null value, set the value to type information * / If (value == null) {value = class.forname (rsmd.getcolumnclassname (i));} // system.out.println (value.getclass ()); // Used to get the type in the database corresponds to Java What type of type Row.Put (Name, Value);} rows.close (); pstmt.close (); pstmt.close ();} catch (Exception EX) {throw new dbaccessException (this, "ExecuteQuery" , EX, "Execute SQL (" SQL ") query error!"));} Finally {database.disconnect (conn); // Call the release connection method of the database object (this method is different in this method The situation has been processed) } Return Rows; / ** * Add a line * @Param Row * / Public int Putrow (Row Row) THROWS DBACCESSEXCEPTION {Return Putrow (Row, null, null) / ** * Modify a line (no condition is increasing) * @Param Row * @Param conditions * / Public Int Putrow (Row Row, String Conditions, Object [] args) throws dbaccessException {string ss = ""; int acidTableRow = 0; // number of rows affects after SQL IF (conditions == null) {ss = "INSERT INTO" Name "("; for (int i = 0; i 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 }}} F (args! = Null) {for (int i = 0; i }} AffectableRow = st.executeUpdate (); st.close ();} catch (exception ex) {ex.printStackTrace (); throw new dbaccessException (Inforget.GeterrorInfor " Name "The data is wrong!"));} finally {database.disconnect (conn);} returnific}} / ** * Delete a line * @Param Row * / Public int delrow (Row Row) THROWS DBACCESSEXCEPTION {String SS = ""; INT AffectableRow = 0; ss = "delete from" name "where"; for (INT i = 0; i IF (i! = row.length () - 1) {ss = "and";}} connection conn = NULL; try {conn = Database.getConnection (); preparedStatement St = conn.prepareStatement (ss); int J = 0; // Query Parameter Counter For (int i = 0; i }}} AffectableRow = st.executeUpdate (); st.close ();} catch (exception ex) {throw new dbaccessException (INFORGETER.GETERRORINFOR (THIS, "DELROW", EX, "Delete Table" Name "data Error! "));} Finally {database.disconnect (conn);} returnific / ** * 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 (s); 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 () {} / ** * Additional key value pair in HashMap, ie the field name and field value * @Param name * @Param value * / Public void put (string name, object value) {if (! map.containskey (name)) {Order.adDelement (name); // Save the key} map.put (name, value);} / ** * Get the number of fields in the line object * @return * / Public intlength () {return map.size (); / ** * get a field value * @Param name * @return * / Public Object Get (String Name) {return map.get (name);} / ** * Added field value * @param which * @return * / Public Object Get (int which) {string key = (string) Ordering.Elementat (which); return map.get (key);} / ** * get a field name according to the field 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 to the value of the value * @Param Row * @Param Type Value Object Type * @return * @Throws java.lang.Exception This exception is typically 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 fields in value objects For (int i = 0; i String methodname = "set" character.touppercase (name.charat (0)) name.substring (1); // Get setXXXX method class argclass = null; if (value instanceof class) {argclass = (class) Value Value = null;} else {argclass = value.getClass ();} method method = type.getMethod (MethodName, new class [] {argclass}); // Get SET method Method.Invoke (Vo, New Object [] {Value}); // Call setXXXX method } Return Vo; / ** * Translate the value object to the row object to the row object * @Param Vo * @return * @param vo * @return * @Throws java.lang.exception is generally processed in DAO, because DAO call * This method Conversion of Row and ValueObject * / Public Static Row fromValueObject (Object Vo) THROWS Exception {Row = New Row (); Class Type = Vo.getClass (); // Get Class for Reflection Processing Field [] Fields = type.getDeclaredfields (); for (int i = 0; i Row.put (nameinrow, value);} return row; / ** * Transfer the property name in the value object into a field name in the corresponding row object (because the field name in the row object * must fully match the field name in the database table when updating the database) * General rules for fsiid - -> 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 underscore) * @Param voname * @Return * / Public static string toinrowname (string voname) {stringbuffer sb = new stringbuffer (); for (int i = 0; i Char cur = voname.charat (i); if (character.isuppercase (cur)) {sb.append ("_"); sb.append (character.tolowercase (Cur));} else {sb.append (Cur) }}}}}}}}}}}}}}} The HashMap object is used to store the field names and corresponding values in the corresponding database table. Due to the disorder of the MAP object, it is used in the use of a vector (of course, List replacement) To store the field name (in order of user-added order), you can provide a GET (INT i) method to get the value in the map. Note that three static auxiliary methods toValueObject, fromvauleObject, toinrowname. The TovalueObject method is used to convert a row object into a value-based object method (please refer to reflection API). FromValueObject is the reverse operation of the previous method, the TOINROWNAME method is the conversion of the property name in the value object to the field name in the database table, because the form of this form of STU_ID is usually used when the database is built, and the attribute of JavaBean in Java is Such stuid. • 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 address {Vector .addelement (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 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 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.getable (name); / ** * Returns the data to the data in accordance with the conditions of the value object collection * @Param Fields To find the field (* or null representation all fields) * @Param criteria query condition * @Param args and query conditions correspondence * @param voType value array type object * @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 Col.Add (VO);} Return Col; / ** * Insert a data in the table * @Param Vo corresponds to the value object corresponding to the table object * @return * @throws java.lang.exception * / Public int INSERTDATA (Object Vo) THROWS Exception {Return Table.putrow (Row.FromValueObject (VO)); / ** * Update a data * @Param Vo corresponds to the value object corresponding to the table object * @Param criteria Update Condition * @Param Args and Update Parameters * @return * @Throws java.lang.exception * / Public int Updatedata (Object Vo, String criteria, Object [] args) throws Exception {Return Table.pute (row.fromvalueObject (vo), criteria, args; / ** * Delete a data (condition comparison strict value of each field must match the value of the attribute in the value object) * @param vo * @return * @throws java.lang.exception * / Public int deletedata (Object Vo) throws exception {return (row.fromvalueObject (vo));} / ** * Delete multiple data * @Param Condition Delete Condition * @Param Args and Condition Parameter Array * @Return * @Throws Java.lang.Exception * / Public int deletedAns (String Condition, Object [] args) THROWS Exception {Return Table.delrow (Condition, Args);}} This DAO class is a convenient package for Table classes. 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)); Vo.setIncome (New Java. Math.BigDecimal (1000)); Vo.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 Equity Conditional Record }
