Shulou(Shulou.com)06/02 Report--

This article mainly explains "what is the design concept of JDBC DAO". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what is the design concept of JDBC DAO".

The meaning of Connection in JDBC DAO

Connection represents a link to the database, the underlying need to have operating system Socket support, so Connection is a resource, since it is a resource, it needs to be set up, opened, used, and closed in the order of reasonable use.

Connection is the basis of Java database operations and a series of operations. All derived operations, such as Statement,PreparedStatement,ResultSet, are directly or indirectly derived from Connection.

How to get Connection?

Method one, use the DriverManager class to get it, as long as the database driver needs to be under classpath (that is, programs that use database links can be accessed in the way of Java).

Connectionconn=DriverManager.getConnection ("jdbc:oracle:thin:@192.168.0.1:1521:ORCL", user,pwd)

Method 2, use the database connection pool to get the

What is database connection pooling? database connection pooling is a service of standard JavaEE containers, such as Webspher,Weblogic,Tomcat. The container sets up some database links in advance so that applications can borrow and repay them. When the application is finished, it will return the database links to the connection pool. (please refer to other documents for data source configuration)

The advantage of using connection pooling is that links can be established in advance, reducing the relative time spent on database acquisition.

The way to obtain database links using connection pooling is:

InitialContextctx=newInitialContext (); DataSourceds= (DataSource) ctx.lookup ("java:comp/env/jdbc/DataSource"); Connectionconn=ds.getConnection ()

Since URL, user name, password and other information have been defined when configuring the database connection pool, it is not necessary to pass in this information when using it in the program.

ConnectionManager definition in JDBC DAO

Connection is used to specifically manage database links. Usually, ConnectionManager has only one method, and calling this method will return an instance of Connection. Through ConnectionManager, you can encapsulate the way you get Connection (for example, you use DriverManager when you develop, and you use DataSource when you use it, but you don't need to modify any code other than ConnectionManager) and append the operation before and after getting Connection (for example, setting the properties of Connection).

The following code is an example of a ConnectionManager code:

Packagecom.jpleasure.jdbc.dao; importjava.sql.Connection; importjava.sql.DriverManager; importjava.sql.SQLException; publicclassConnectionManager {publicstaticConnectiongetConnection () throwsDaoException {Connectionconn=null; try {conn=DriverManager.getConnection ("", ",");} catch (SQLExceptione) {thrownewDaoException ("cannotgetdatabaseconnection", e);} returnconn;}}

If you need to change from development mode to application mode, you only need to change the above code to:

Packagecom.jpleasure.jdbc.dao; importjava.sql.Connection; importjava.sql.DriverManager; importjava.sql.SQLException; publicclassConnectionManager {publicstaticConnectiongetConnection () throwsDaoException {Connectionconn=null; try {Contextctx=newInitialContext (); DataSourceds= (DataSource) ctx.lookup ("jdbc/dsname"); conn=ds.getConnection ();} catch (NamingExceptione) {thrownewDaoException ("cannotfinddatasource", e);} catch (SQLExceptione) {thrownewDaoException ("cannotgetdatabaseconnection", e);} returnconn }} if you need to set some properties of Connection in advance, you can also set them in the above code, for example: packagecom.jpleasure.jdbc.dao; importjava.sql.Connection; importjava.sql.DriverManager; importjava.sql.SQLException; publicclassConnectionManager {publicstaticConnectiongetConnection () throwsDaoException {Connectionconn=null; try {Contextctx=newInitialContext (); DataSourceds= (DataSource) ctx.lookup ("jdbc/dsname"); conn=ds.getConnection (); conn.setAutoCommit (false) } catch (NamingExceptione) {thrownewDaoException ("cannotfinddatasource", e);} catch (SQLExceptione) {thrownewDaoException ("cannotgetdatabaseconnection", e);} returnconn;}} CommonDao definition properties and constructors typically, CommonDao has a reference to Connection. All method calls to all instances of a CommonDao depend on this Connection. Another reason for requiring a Connection is that if each method needs to be guaranteed in a transactional environment (context), you must ensure that all operations are on the same Connection. Constructors usually need to instantiate attributes of type Connection, for example: packagecom.jpleasure.jdbc.dao; importjava.sql.Connection; publicclassCommonDao {privateConnectionconn; publicCommonDao () throwsDaoException {this.conn=ConnectionManager.getConnection ();}}

Transaction method

Begin ()

After you start a transaction and call the begin method of CommonDao, all subsequent operations will either be committed or rolled back in a transaction environment.

Commit ()

Commit a transaction that must be called after the begin call. And mutually exclusive with the rollback method.

Rollback ()

Rollback a transaction that must be called after the begin method call. And mutually exclusive with the commit method.

There are two ways to implement a transaction, one is to use a transaction based on a single Connection, and the other is to use the container's JTA (JavaTransactionAPI). It is important to note that * methods can be used in any environment, but only for a single database link. The second method is intelligently used in JavaEE containers that support JTA (for example, Websphere,Weblogic, etc., which is not supported by Tomcat by default), but supports multiple Connection instances.

* the code of the method is:

Packagecom.jpleasure.jdbc.dao; importjava.sql.Connection; importjava.sql.SQLException; publicclassCommonDao {privateConnectionconn; publicCommonDao () throwsDaoException {this.conn=ConnectionManager.getConnection ();} publicvoidbegin () throwsDaoException {if (connexual null) {try {conn.setAutoCommit (false);} catch (SQLExceptione) {thrownewDaoException ("cannotbegintransaction", e);}} else {thrownewDaoException ("connectionnotopened!") }} publicvoidcommit () throwsDaoException {try {if (connawned nullable grains) conn.getAutoCommit () {conn.commit (); conn.setAutoCommit (true);} else {if (conn==null) {thrownewDaoException ("connectionnotopened!");} else {thrownewDaoException ("firstbeginthencommitplease!");} catch (SQLExceptione) {thrownewDaoException ("cannotcommittransaction!", e) }} publicvoidrollback () throwsDaoException {try {if (Connexified nullable materials) conn.getAutoCommit () {conn.rollback (); conn.setAutoCommit (true);} else {if (conn==null) {thrownewDaoException ("connectionnotopened!");} else {thrownewDaoException ("firstbeginthenrollbackplease!");} catch (SQLExceptione) {thrownewDaoException ("cannotrollbacktransaction!", e);}

Second, we show how to use (@ TODO) in an example that uses DAO

Create two new DAO, do different operations, and use JTA to ensure transaction integrity.

Query method

The query method is probably the most commonly used method in CommonDao, which needs to return the results of the database to the screen. We generally do not use ResultSet for the return value, because ResultSet depends on Connection, and if Connection is turned off, ResultSet will no longer be valid, so we usually convert ResultSet to a List and return.

Before we explain the query method, let's talk about how to put the contents of the database in List. We use a List to represent a collection of query results, a Map to represent a row in the collection, the key of Map to represent the field name of the database table, and Value to represent the contents of the database field. The code is:

PrivateListconvert (ResultSetrs) throwsDaoException {/ / recordlist ListretList=newArrayList (); try {ResultSetMetaDatameta=rs.getMetaData (); / / columncount intcolCount=meta.getColumnCount (); / / eachrecord while (rs.next ()) {MaprecordMap=newHashMap (); / / eachcolumn for (inti=1;i0) {/ / parametersiterator Iteratorit=params.iterator (); / / parameterindex intindex=1; while (it.hasNext ()) {Objectobj=it.next (); / / ifnullset "if (obj==null) {pstmt.setObject (index,") } else {/ / elsesetobject pstmt.setObject (index,obj);} / nextindex index++;} catch (SQLExceptionex) {thrownewDaoException ("cannotapplyparameter", ex);}} then let's move on to our query method. With the above two methods, our query method is very simple: publicListquery (Stringsql,Listparams) throwsDaoException {Listresult=null; PreparedStatementpstmt=null; ResultSetrs=null; try {pstmt=conn.prepareStatement (sql); this.apply (pstmt,params) Rs=pstmt.executeQuery (); result=this.convert (rs);} catch (SQLExceptionex) {thrownewDaoException ("cannotexecutequery", ex);} finally {if (rswatches null) {try {rs.close ();} catch (SQLExceptione) {/ / nothing}} if (try {pstmt.close ();} catch (SQLExceptione) {/ / nothing} returnresult;})

Special query method (returns a single value)

Sometimes for ease of use, we need to return a single-value production search method, such as selectmax (id) fromtable_a,selectcount (id) fromtable_b and so on. The following code uses the above general query method, and the code is:

PublicObjectqueryOne (Stringsql,Listparams) throwsDaoException {Listlist=this.query (sql,params); if (list==null | | list.size () = 0) {thrownewDaoException ("datanotexist");} else {Maprecord= (Map) list.get (0); if (record==null | | record.size () = = 0) {thrownewDaoException ("datanotexist");} else {returnrecord.values (). ToArray () [0];}

Update, delete, insert method

Since all three methods in JDBC are done with an execute, we also use a method to accomplish these functions here. The code is:

Publicintexecute (Stringsql,Listparams) throwsDaoException {intret=0; PreparedStatementpstmt=null; try {pstmt=conn.prepareStatement (sql); this.apply (pstmt,params); ret=pstmt.executeUpdate ();} catch (SQLExceptionex) {thrownewDaoException (", ex);} finally {try {pstmt.close ();} catch (SQLExceptione). } returnret;}

Batch method (query)

Sometimes, to facilitate operation, you need to query more than one SQL statement at a time, which we call batch processing. For implementation, see the following method, where the parameters and return values are changed to an array to distinguish them from the query method.

PublicList [] queryBatch (String [] sqlArray,List [] paramArray) throwsDaoException {Listrets=newArrayList (); if (sqlArray.explorthparts paramArray.length) {thrownewDaoException ("sqlsizenotequalparametersize");} else {for (inti=0;iStringsql=sqlArray [I]; Listparam=paramArray [I]; Listret=this.query (sql,param); rets.add (ret);} return (List []) rets.toArray ();}}

Batch method (update)

Sometimes you need to update more than one Sql statement at a time. In order to facilitate operation, a batch update operation is added. See the following code. In order to distinguish it from the update method, the parameters and return values are changed to an array.

Publicint [] executeBatch (String [] sqlArray,List [] paramArray) throwsDaoException {Listrets=newArrayList (); if (sqlArray.length) {thrownewDaoException ("sqlsizenotequalparametersize");} else {for (inti=0;iintret=this.execute (sqlArray [I], paramArray [I]); rets.add (newInteger (ret));} int [] retArray=newint [rets.size ()]; for (inti=0;iretArray [I] = ((Integer) rets.get (I)). IntValue ();} returnretArray;}}

Resource release

Because CommonDao has an attribute of Connection, and Connection is a scarce resource, CommonDao does not need to turn off Connection when it is used. The code is as follows:

Publicvoidclose () throwsDaoException {try {if (connawned nullary materials conn.getAutoCommit ()) {conn.close ();} else {if (conn==null) {thrownewDaoException ("cannotclosenullconnection,firstnewthenclose");} else {thrownewDaoException ("transactionisrunning,rollbakcorcommitbeforcloseplease.");} catch (SQLExceptionex) {thrownewDaoException ("Cannotclosecommondao");}}

JDBC utility class (JDBCUtilClass)

We see a lot of useless processing in the above code, such as:

If {try {pstmt.close ();} catch (SQLExceptione) {/ / nothing. }}

Why do we have to deal with this? It is said that the location of these processes occurs after the normal processing is completed, and these processes (such as pstmt.close ()) will not affect even if they fail. At this time, we need to do the above useless processing, which is a small flaw in JDBCAPI. We usually use a special static tool to supplement it, such as:

Packagecom.jpleasure.jdbc.dao; importjava.sql.Connection; importjava.sql.PreparedStatement; importjava.sql.ResultSet; importjava.sql.SQLException; publicclassJDBCUtil {publicvoidsafelyClose (Connectionconn) {if (connexual null) {try {conn.close ();} catch (SQLExceptione) {/ /} publicvoidsafelyClose (PreparedStatementpstmt) {if (pstmtnull null) {try {pstmt.close () } catch (SQLExceptione) {/ /} publicvoidsafelyClose (ResultSetrs) {if (rswatches null) {try {rs.close ();} catch (SQLExceptione) {/ /}

Exception handling in JDBC DAO

Perhaps carefully you have found a problem, why all the places where we throw exceptions are SQLException wrapped in DaoException thrown, why not just throw SQLException? There are two reasons, * *, can be refined, classification Exception throws appropriate exceptions and adds appropriate messages. Second, isolation and the coupling of Dao and business logic can easily modify the Dao layer without affecting the business logic layer. It is also important to note that SQLException can be included in the DaoExcetion, which can provide customers with more detailed error information, such as ORA-12524, but it is rare.

Packagecom.jpleasure.jdbc.dao; publicclassDaoExceptionextendsException {publicDaoException () {super ();} publicDaoException (Stringmessage,Throwablecause) {super (message,cause);} publicDaoException (Stringmessage) {super (message);} publicDaoException (Throwablecause) {super (cause) }} Thank you for your reading, the above is the content of "what is the design concept of JDBC DAO". After the study of this article, I believe you have a deeper understanding of what the design concept of JDBC DAO is, and the specific use needs to be verified by practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.