This is the old heard written before, or it should be a simple documentation of the tool written. The topic of this article is a bit big, not to solve all problems. And just only the use of JDBC, huh, huh. Those brothers who are familiar with or mapping, or using such tools don't joke. After all, we still have a lot of opportunities to use SQL JDBC directly, I hope to help everyone. For the source code of the tool, please contact me, welcome to exchange. (Mainly I don't know how to pass attachments, huh, huh)
Simplify the use of JDBC (1)
Ube bun (james.hu@chinacodeline.com)
Xi'an Software Co., Ltd.
December 2003
Foreword
JDBC provides a unified interface to the Java program that enhances the portability of the Java database application, which greatly facilitates the Java database programming. But in practical applications, we still face some questions:
2 JDBC powerful functions, complex interfaces, brought the burden on learning;
2 and with JDBC-related program code (such as increasing, deletion, change) in many places, how to encapsulate these code, make it reused in practical applications?
2 Some common functions in the development process of database applications, JDBCs are currently not supported, such as data paging, output (XML, CSV, etc.) in common formats;
The author writes the corresponding tool class for these issues, making it simplifies the use of JDBC and contains common functions in the database development process. The main features of the toolkit:
Simplify the use of JDBC, so that the newer can quickly write database applications; the most commonly used code (create connection, add modification, calling stored procedure) package does not have to write similar code each time; provide offline data objects, in browsing data Do not maintain database connections during the process; data objects provide "line * columns" data organization form, easy data settings and read; provide data paging function, ideal for Web database applications; support XML, easy to do with other technology For example, XSLT is combined, converted into other formats such as HTML; support the most commonly used CSV format output;
Problem and solution
This section shows the design and application of the tool class.
Create a database connection
The database connection provides the most basic environment for various operations of the database. The most common way to create database connections in JDBC is: Use JNDI and use database connection parameter sets (including: database drivers, database URLs, database users, passwords corresponding to the user). The toolkit provides a corresponding creation function for both cases, providing a unified creation entry for the creation of a database connection. During the actual use, you don't have to write similar code for duplicate writing to each connection.
In the toolkit, the CONNECTIONFAACTORY is responsible for the creation of the database connection connection. According to both ways, two creation functions are provided: getConnectionByparams and getConnectionByjndi. Depending on the actual situation, the user selects the corresponding function to create a connection.
Application examples
1. Use database connection parameters:
Connection conn = connectionFactory.getConnectionByparams ("org.gjt.mm.mysql.driver", "JDBC: MySQL: // LocalHost / DBFormstest", "SA", "");
2. Using JNDI:
Connection conn = connectionFactory.getConnectionByjndi ("java: comp / env / jdbc / oracleds"); database basic operation
The basic operation of the database is: query, increase, modify, delete, and call the stored procedure. The kit encapsulates these operations in DBCommand objects. Before describing it is these operations, we must first resolve the organization's way of organization.
Data object
There is an operation, there is an object being operated. In the database application, the object is operated is data. How to organize these data to make effective operations is the primary issue we have to solve. In JDBC, data is organized in the form of ResultSet.
The ability of ResultSet is very powerful, but it is not no shortcomings. First, to make the resultSet's data is valid, it must be maintained. Keep the database connection, it is very useful for the resulting large amount of data in the ResultSet. However, common situations are that there is not much data contained in ResultSet. In order to release the connection as soon as possible, we usually take out the result of the ResultSet, then release. Frequent writing such code, is really lacking for program writers. Secondly, the data type supported by ResultSet is very rich, but it is "too rich" for common data types (string type, digital type, time type); for habits "line * column" mode, ResultSet does not provide good Use the interface.
Combined with the actual use, the toolkit will organize the data into a DataSet. DataSet features:
2 Keep data without maintaining database connections;
2 Provide data acquisition functions with "line * column" organization;
2 Compose data and metadata;
2 The returned data is saved in String object because it is enough for common data type String;
2 Support for common data types, as well as format extensions. Because each database is less than the time type, if SQL Server can be treated as a string when adding a date data, and Oracle must use the to_date function. But this part of the code book is very small. By default, the kit will use the date type as a string;
The UML map of the data object part in the kit is as follows:
The role of each object is:
2 DataSet, the most important object of external operation. It is a container of DataRow and Metadata;
2 DATAROW, column data container;
2 Metadata, metadata object, is a container of ColumnMetadata
2 ColumnMetadata, column metadata, containing the format interface;
2 format, data SQL statement formatting interface;
2 StringFormat, string SQL statement formatted object;
2 NumbericFormat, digital SQL statement formatted object;
2 datetimeformat, time SQL statement formatted object;
In this way, we organized data. After that, we will use the database operation object to see the specific application.
Application examples
1. Query data
Connection conn = connectionByparams ("org.gjt.mm.mysql.driver", "JDBC: MySQL: // LocalHost / DBFormstest", "SA", ""); DBCommand cmd = new dbcommand (conn); DataSet DS = cmd.query ("select * from service"); conn.close (); string [] [] data1 = ds.getdata (); DBCommand is responsible for Dataset and the creation of each component when performing query operations. In this way, the user not only obtains related data, but also obtains metadata description related to this query. This approach also provides an automatic way to create metadata (with automatic creation is manually created DataSet):
DataSet DS = cmd.query ("SELECT * from service where 1 = 2");
This query statement does not return any data, but it can get metadata by it. Just replace the "*" to replace the relevant column name, then the database description of these column names can be obtained. This, in the example below, the reader can see the actual application.
The basic algorithm for creating a DataSet is:
ResultSet is obtained according to the query statement; extracting the result of ResultSet, getting data; obtaining ResultSetMetadata, using FormatFactory to create a corresponding format according to different column types; create columnMetadata according to metadata; assemble this information into DataSet;
Here, create format using FormatFactory is to create different formats according to different column types, making it data write operation service. There is only one method createformat in FormatFactory, and the incoming parameter type is java.sql.types. By custom FormatFactory, users can support more different types of columns. This step can be omitted if you use manually created DataSet, which is omitted, just need to achieve different format. This will give the corresponding code in the example below.
2. Increasing data
The database is incremented, and the change is implemented using DataSet, and the user can write DataSet's data into the database by assembling DataSet.
Connection conn = connectionsFactory.getConnectionByparams ("org.gjt.mm.mysql.driver", "JDBC: MySQL: // LocalHost / DBFormstest", "SA", ""); dbcommand cmd = new dbcommand (conn); // Automatic acquisition metadata (of course, you can also manually add) DataSet DS = cmd.Query ("SELECT * from service where 1 = 2"); string [] [] DATA1 = {{"400", "Dog Walking", "Talking THE DOG for a walk "}}; // Set the table name ds.getMetadata (). settablename (" service "); // Setting data DS.SETDATA (DATA1); cmd.insert (ds); conn.close () DataSet's data can be obtained / set by getData / setData. The use form is the most familiar 2D number of groups. In addition, DataSet also provides corresponding Iterator, allowing users to traverse data.
3. change the data
Modifying data is very similar to adding data, and the only difference is to add conditions. The conditions are also organized in an array, and its dimension is the same as the number of rows of DataSet, that is, each condition corresponds to a row of data.
Connection conn = connectionsFactory.getConnectionByparams ("org.gjt.mm.mysql.driver", "JDBC: MySQL: // LocalHost / DBFormstest", "SA", ""); dbcommand cmd = new dbcommand (conn); // Automatic acquisition metadata (of course also manually added) DataSet DS = cmd.query ("Select * from service where 1 = 2"); // Setting Change Condition String [] Conditions = {"ID = 100"}; string [ ] [] DATA1 = {{"301", "Dog Walking", "Talking The Dog for A Walk"}; // Settings Table Name
ds.getMetadata (). SettableName ("service"); // Setting data
DS.SETDATA (DATA1); cmd.Update (DS, conditions); conn.close ();
4. delete data
Deleting data is probably the easiest, only need to pass a SQL statement. DBCommand provides an Execute method to execute the SQL command.
