A generic JDBC prepare Action Framework

A general JDBC abstract framework

It's not enough to understand the jdbc api and the issues in using it correctly. using JDBC directly is simply too much effort, and too error-prone. we need not just understanding, but a code framework that makes JDBC easier to use.

It is not enough to simply use it to correctly understand the jdbc api and its problems. It is too complicated to use JDBC directly. We need not only understanding, but also a framework to make JDBC more usable.

In this section we'll look at one such solution: a generic JDBC prepare action framework, which we'll use in the sample application, and which can significantly simplify application code and minimize the likelihood of making errors.

In this section, we will look forward to the following question: a general JDBC abstract framework will be used in this simple application to effectively simplify the application.ProgramCodeAnd minimize the possibility of manufacturing errors.

Important

Important

The jdbc api is too low-level for using it directly to be a viable option. when using JDBC, always use helper classes to simplify application code. however, don't try to write your own o/R Mapping layer. if you need o/R Mapping, use an existing solution.

The jdbc api is too low. Using it directly is the final option: when using JDBC, it always uses the help class to simplify the application code. However, do not try to write your own o/R ing layer. If you need o/R ing, use an existing solution.

In this section, we'll examine the implementation and usage of the JDBC procedure action framework used in the sample application, which is already ded in the Framework Code in the download accompanying this book. this is a generic framework, which you can use in your own applications to simplify use of JDBC and lessen the likelihood of JDBC-related errors.

In this section, we will debug the implementation and usage of the JDBC abstract framework in this simple application. It is already included in the Framework Code in this book. This is a general framework. You can use it in your own applications to simplify JDBC usage and reduce JDBC errors.

Motivation

Motivation

Writing low-level JDBC code is painful. the problem is not the SQL, which is usually easy to author and understand, and accounts for little of the total code volume, but the incidentals of using JDBC. the jdbc api is not particle ly elegant and is too low-level for use in application code.

Writing low-level JDBC code is painful. The problem is not in SQL, which is usually easy to create and understand, and accounts for a small proportion of the Code cycle, except for the use of JDBC. JDBC APIs are not very elegant. They are too low-level to write application code.

As we 've seen, using the jdbc api even to perform very simple tasks is relatively complex and error-prone. take the following example, which queries the IDs of available seats in our ticketing system:

As we can see, using JDBC APIs can execute very simple tasks that are quite complex and error-prone. The following example shows the available seat IDs in our ticket system:

 Public List getavailableseatlds (datasource ds, Int Performanceld,
 Int Seattype) Throws Applicationexception {

String SQL = "Select seat_id as ID from available_seats" +
 "Where performance_id =? And price_band_id =? " ;
List seatlds = New Vertex list ();

Connection con = NULL;
Preparedstatement PS = NULL;
Resultset rs = NULL;
 Try {
Con = Ds. getconnection ();
PS = con. preparestatement (SQL );
PS. setlnt (1, performanceld );
PS. setlnt (2, seattype );
Rs = ps.exe cutequery ();

 While (Rs. Next ()){
 Int Seatld = Rs. getlnt (1 );
Seatlds. Add ( New INTEGER (seatld ));
}
Rs. Close ();
PS. Close ();
}
 Catch (Sqlexception ex ){
 Throw   New Applicationexception ( "Couldn't run Query [" + SQL + "]" , Ex );
}
 Finally {
 Try {
 If (Con! = NULL)
Con. Close ();
}
 Catch (Sqlexception ex ){
 // Log and ignore 
}
}
 Return Seatlds;
}

although the SQL is trivial, because of the use of the view in the database to conceal the Outer Join required, executing this query requires 30-odd lines of JDBC code, few of which actually do anything. (I 've highlighted the functional lines of code .) such a predominance of "Plumbing" code that doesn't deliver functionality indicates a poor implementation approach. an approach that makes very sim Ple things so hard is unworkable. if we use this approach every time we need to perform a query, we'll end up with reams of code and the potential for each errors. important operations like closing the connection, which are incidental to the data retrieval operation, dominate the listing. note that closing the connection requires a second try... Catch block in the finally block of the outer try... Catch . Whenever I see a nested try... Catch , I feel a powerful desire to refactor that code into a framework class.

Even if SQL is not important, because the view is used to conceal external join requirements, it requires 30 lines of JDBC code to execute this query. Most of them do not actually do anything. (I highlighted useful code ). Such an excellent Pipeline Code has no delivery function, and shows a poor implementation method. This approach makes very simple things impractical. If we execute a query in this way each time, we will eventually get a lot of code and many potential errors. Important operations, such as closing the connection, and accompanying data query operations, control the list. Note that closing the connection requires the second try... The Catch Block is outside the Finally block. No matter if I see a nested try... catch, I am eager to refactor the code to the framework class.

Note

Note:

I haven't got ded code to obtainDatasource, Which wocould use JNDI in a J2EE application. I assume that this is accomplished elsewhere using a suitable helper class to avoid JNDI access further bloating the code.

I did not include the code for obtaining the datasource connection, and JNDI will be used in the application. I suppose this is done in other suitable help classes to avoid code expansion due to further access.

A simple procedure action framework can make this query much simpler, while still using the jdbc api under the covers. the following code defines a reusable, threadsafe query object extending a framework class, which we'll look at in detail later:

A simple abstract framework can simplify queries, but still use JDBC APIs at the underlying layer. The following code defines a reusable, thread-safe query object that inherits a framework class. We will explore it in detail soon:

 Class Availabilityquery Extends 
Com. interface21.jdbc. Object. manualextractionsqlquery {

 Public Availabilityquery (datasource DS ){
 Super (DS, "Select seat_id as ID from available_seats where" +
 "Performance_id =? And price_band_id =? " );
Declareparameter ( New Sqlparameter (types. Numeric ));
Declareparameter ( New Sqlparameter (types. Numeric ));
Compile ();
}

 Protected Object extract (resultset RS, Int Rownum) Throws Sqlexception {
 Return   New INTEGER (Rs. getlnt ( "ID" ));
}
}

This takes one third of the code of the first version, and every line does something. the troublesome error handling is left to the superclass. we need only to write code to provide the SQL query, provide the BIND parameters (after specifying their JDBC types) and extract the results. in other words, we only need to implement application functionality, not plumbing. the framework superclass provides a number of execute methods, one of which we can run like this:

This only accounts for the 1/3 code of Lele's first version, and each line does something. Annoying error handling is left to the parent class. We only need to write code to provide SQL queries, bind parameters (after their JDBC Type is specified), and provide a series of execution methods, one of which can be run like this:

Availabilityquery =NewAvailabilityquery (DS );
List 1 = availabilityquery. Execute (1, 1 );
 

Once constructed, We can reuse this query object.Execute ()Method throws a runtime exception, not a checked exception, meaning that we need only catch it if it's recoverable. The resesponance to the JDOQueryInterface, which we 've just discussed, is intentional. the jdo api is easy to use and already familiar to a growing number of developers, hence it makes sense to apply the same principles to JDBC. the key difference, of course, is that our JDBC query doesn't return mapped objects; Changes to the returned objects will have no effect on the database.

Once constructed, We can reuse this query object. This execcute () method throws a runtime exception, not a runtime exception, which is specially designed. JDO APIs are easy to use and familiar with a series of growing developers. Therefore, it makes sense to apply the same principle to JDBC. The key difference is, of course, that our JDBC query does not return the ing object; changing the returned object does not affect our database.

In the remainder of this section we'll look at how this happens action framework is implemented and how it can be used to simplify application code.

In the remaining sections, we will go deep into how the framework is implemented and how it can be used to simplify the application code.

Aims

Target

Remember the Pareto Principle (the 80: 20 rule )? The best results can be achieved with JDBC reply action by not trying to achieve too much.

Do you still remember the principle of the "principle of the family" (Law )? The best result of using JDBC abstraction is not to try to complete too much.

The following areas account for most developer effort using the jdbc api directly and are most error-prone. Our export action shocould try to address each:

The following section explains how many developers use JDBC APIs directly and most of them are prone to errors. We must begin with every Abstraction:

• Too much code-queries, for example, must acquireConnection,StatementAndResultset, And iterate overResultset.

• Too many code-queries. For example, a connection, a statement, and a result set must be required to traverse the result set.

• Correct cleanup in the event of errors (ensuring that connections and statements are closed, as we discussed above). Most competent Java developers have cleaned up a lot of brokenTry... CatchBlocks und JDBC code, after seeing the number of open connections gradually mount in server-side applications.

• Correct cleaning after an error event occurs (ensure that the connection and statement are related, as discussed above ). Most competent developers have cleared a large number of try. Catch blocks around the JDBC code, and gradually increased the number of opened connections on the server application.

• DealingSqlexceptions-These are checked exceptions, which application code shouldn't really be forced to catch, as catching them requires knowledge of JDBC.

• Processing sqlexceptions-these are exceptions during the check, and the application code should not be really forced to capture, capture them to understand JDBC.

• Making it easier for user code to establish the cause of exceptions, without needing to examine sqlstate or vendor codes.

• Is the cause that it is easier to establish exceptions for user code, and does not need to check sqlstate and vendor code.

Surprisingly, there don't seem to be either libraries or frameworks that address these problems. most are much more ambitious (aiming at some form of O/R Mapping, for example), meaning they become complex to implement and to use. hence, I 've developed a framework for the sample application, which we'll talk about now and which can be used in any application using JDBC (these classes are actually the most recent in a line of such classes I 've developed for various clients ).

Another surprise is that there are not many libraries or frameworks working on these issues. Most of them have greater ambitions (aiming at some form of O/R ing, for example), which also means they become more complex to implement and utilize. Therefore, I have developed a framework for this simple application. What we are discussing now is, can be used in any application that uses JDBC (these classes are actually mostly developed by me recently for different customers .)

This isn't the only way to address the challenges of working with JDBC, but it's simple and highly valid tive.

This is not just a way to solve the challenge of working with JDBC, but it is simple and efficient.

Another key problem-the danger of SQL being sprinkled around the application-is best addressed with the DAO pattern, which we 've already discussed. in this section, we'll talk about implementing data access objects, not their place in overall application architecture.

Another key issue-the danger of SQL is spread all over the application-it is best to work on the DAO mode. We have already discussed it. In this chapter, we will discuss how to implement data access objects, in the entire application.