In-depth analysis of JavaWeb Item33-Develop your own simple JDBC framework

In-depth analysis of JavaWeb Item33-Develop your own simple JDBC framework
I. Introduction to metadata

Metadata refers"Database","Table","Column.

1.1 DataBaseMetaData metadata

Connection. getDatabaseMetaData () obtains the DatabaseMetaData object representing the DatabaseMetaData metadata.
Common Methods for DataBaseMetaData objects:

GetURL (): returns a String object, representing the URL of the database. GetUserName (): returns the username used to connect to the current database management system. GetDatabaseProductName (): returns the product name of the database. GetDatabaseProductVersion (): returns the database version number. GetDriverName (): returns the name of the driver. GetDriverVersion (): returns the driver version number. IsReadOnly (): returns a boolean value indicating whether the database only supports read operations.

/*** @ Method: testDataBaseMetaData * @ Description: Obtain the database metadata * @ Anthor: Lone Wolf ** @ throws SQLException */@ Test public void testDataBaseMetaData () throws SQLException {Connection conn = JdbcUtils. getConnection (); DatabaseMetaData metadata = conn. getMetaData (); // getURL (): returns a String class object, representing the database URL System. out. println (metadata. getURL (); // getUserName (): returns the username System used to connect to the current database management System. out. println (metadata. getUserName (); // getDatabaseProductName (): returns the database product name System. out. println (metadata. getDatabaseProductName (); // getDatabaseProductVersion (): returns the database version number System. out. println (metadata. getDatabaseProductVersion (); // getDriverName (): returns the name of the driver System. out. println (metadata. getDriverName (); // getDriverVersion (): returns the driver version number System. out. println (metadata. getDriverVersion (); // isReadOnly (): returns a boolean value indicating whether the database can only read System. out. println (metadata. isReadOnly (); JdbcUtils. release (conn, null, null );}

The running result is as follows:

　　

1.2. ParameterMetaData metadata

PreparedStatement. getParameterMetaData () gets the ParameterMetaData object representing the PreparedStatement metadata.
　　Select * from user where name =? And password =?
Common Methods for ParameterMetaData objects:

GetParameterCount (): Get the number of specified parameters getParameterType (int param): Get the SQL type of the specified parameter, which is not supported by the MySQL database driver

/*** @ Method: testParameterMetaData * @ Description: Obtain the parameter metadata * @ Anthor: Lone Wolf ** @ throws SQLException */@ Test public void testParameterMetaData () throws SQLException {Connection conn = JdbcUtils. getConnection (); String SQL = "select * from user wherer name =? And password =? "; // Pre-compile the SQL statement PreparedStatement st = conn. prepareStatement (SQL); ParameterMetaData pm = st. getParameterMetaData (); // getParameterCount () gets the number of specified parameters. System. out. println (pm. getParameterCount (); // getParameterType (int param): Obtain the SQL type of the specified parameter. The MySQL database driver does not support System. out. println (pm. getParameterType (1); JdbcUtils. release (conn, null, null );}

1.3. ResultSetMetaData metadata

ResultSet. getMetaData () obtains the ResultSetMetaData object that represents the metadata of the ResultSet object.
Common Methods for ResultSetMetaData objects:

GetColumnCount () returns the number of columns of the resultset object. getColumnName (int column) gets the name of the specified column. getColumnTypeName (int column) gets the type of the specified column.

/*** @ Method: testResultSetMetaData * @ Description: metadata of the result set * @ Anthor: Lone Wolf ** @ throws Exception */@ Test public void testResultSetMetaData () throws Exception {Connection conn = JdbcUtils. getConnection (); String SQL = "select * from account"; PreparedStatement st = conn. prepareStatement (SQL); ResultSet rs = st.exe cuteQuery (); // ResultSet. getMetaData () obtains the ResultSetMetaData object ResultSetMetaData = rs that represents the metadata of the ResultSet object. getMetaData (); // getColumnCount () returns the number of columns of the resultset object System. out. println (metadata. getColumnCount (); // getColumnName (int column) gets the name of the specified column System. out. println (metadata. getColumnName (1); // getColumnTypeName (int column) gets the System of the specified column type. out. println (metadata. getColumnTypeName (1); JdbcUtils. release (conn, st, rs );}

Ii. use metadata to encapsulate a simple JDBC framework

All entity objects in the system involve basic CRUD operations.
The CUD operation code of all objects is basically the same, and only the SQL statements sent to the database are different. Therefore, you can extract all the same code of the CUD operation to an update method of the tool class, and define the SQL statement that the parameter receives the change.
In addition to different SQL statements, the R operation of an object maps the ResultSet object to different entities. Therefore, you can define a query method, in addition to the SQL statements that receive changes in the form of parameters, the policy mode is used by the caller OF THE qurey method to determine how to map the data in the ResultSet to the object.

2.1 encapsulate common update Methods and qurey Methods

Defines a JdbcUtils tool class that obtains database connections, releases resources, and executes SQL update and query operations. The Code is as follows:

Package me. gacl. util; import java. io. inputStream; import java. SQL. connection; import java. SQL. driverManager; import java. SQL. preparedStatement; import java. SQL. resultSet; import java. SQL. SQLException; import java. SQL. statement; import java. util. properties; public class JdbcUtils {private static String driver = null; private static String url = null; private static String username = null; private static Str Ing password = null; static {try {// read the database. the database connection information in the properties file is InputStream in = JdbcUtils. class. getClassLoader (). getResourceAsStream ("db. properties "); Properties prop = new Properties (); prop. load (in); // obtain the database connection driver = prop. getProperty ("driver"); // obtain the database connection URL url = prop. getProperty ("url"); // obtain the database connection username = prop. getProperty ("username"); // obtain the database connection password = prop. getProperty ("password "); // Load the database Driver Class. forName (driver);} catch (Exception e) {throw new ExceptionInInitializerError (e);}/*** @ Method: getConnection * @ Description: obtain the database Connection object * @ Anthor: Lone Wolf ** @ return Connection database Connection object * @ throws SQLException */public static Connection getConnection () throws SQLException {return DriverManager. getConnection (url, username, password);}/*** @ Method: release * @ Description: release resources, * Resources to be released include Connection database Connection objects, Statement objects for executing SQL commands, and ResultSet objects for storing query results * @ Anthor: gu AO Canglang ** @ param conn * @ param st * @ param rs */public static void release (Connection conn, Statement st, ResultSet rs) {if (rs! = Null) {try {// close the rs object that stores the query results. close ();} catch (Exception e) {e. printStackTrace ();} rs = null;} if (st! = Null) {try {// close the Statement object st. close ();} catch (Exception e) {e. printStackTrace () ;}} if (conn! = Null) {try {// closes the Connection database Connection object conn. close ();} catch (Exception e) {e. printStackTrace () ;}}/ *** @ Method: update * @ Description: omnipotent update * the CUD operation code of all objects is basically the same, only the SQL statements sent to the database are different. * therefore, you can extract all the same code in the CUD operation to an update method in the tool class, and define the SQL statement * @ Anthor: gu AO Canglang * @ param SQL the SQL statement to be executed * @ param params the parameter * @ throws SQLException */public static void update (String SQL, Object params []) used for SQL Execution throws SQLException {Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try {conn = getConnection (); st = conn. prepareStatement (SQL); for (int I = 0; I
  

When designing the query method, the query method uses the policy mode for processing the returned result set. The query method cannot know how the user processes the returned query result set in advance, that is, if you do not know the processing policy of the result set, the processing policy of the result set is provided by the user, and the query method internally calls the result set processing policy submitted by the user for processing, to allow users to provide processing policies for result sets, a result set processing interface ResultSetHandler needs to be exposed to users. The result set Processor interface ResultSetHandler is defined as follows:

Package me. gacl. util; import java. SQL. resultSet;/*** @ ClassName: ResultSetHandler * @ Description: result set Processor interface * @ author: lone wolf * @ date: 12:01:27 **/public interface ResultSetHandler {/*** @ Method: handler * @ Description: result set Processing Method * @ Anthor: gu AO Canglang ** @ param rs query result set * @ return */public Object handler (ResultSet rs );}

As long as you implement the ResultSetHandler interface, you write a processor for the query result set. In the query method, you can call your own processor to process the result set.

2.2 compile common result set processors

To improve the ease-of-use of the framework, we can write some commonly used processors for the result set in advance, such as converting the result set into the bean object processor, the processor that converts the result set to the list set of bean objects.

2.2.1. BeanHandler -- converts a result set into a bean object Processor

Package me. gacl. util; import java. lang. reflect. field; import java. SQL. resultSet; import java. SQL. resultSetMetaData;/*** @ ClassName: BeanHandler * @ Description: converts the result set to the bean object processor * @ author: lone wolf * @ date: 12:00:33 **/public class BeanHandler implements ResultSetHandler {private Class Clazz; public BeanHandler (Class Clazz) {this. clazz = clazz;} public Object handler (ResultSet rs) {try {if (! Rs. next () {return null;} Object bean = clazz. newInstance (); // obtain the result set metadata ResultSetMetaData metadata = rs. getMetaData (); int columnCount = metadata. getColumnCount (); // The number of columns in the result set. for (int I = 0; I 

2.2.2. BeanListHandler -- convert the result set into the processor of the list set of bean objects

Package me. gacl. util; import java. lang. reflect. field; import java. SQL. resultSet; import java. SQL. resultSetMetaData; import java. util. arrayList; import java. util. list;/*** @ ClassName: BeanListHandler * @ Description: The processor used to convert the result set to the list set of bean objects * @ author: lone wolf * @ date: 12:00:06 **/public class BeanListHandler implements ResultSetHandler {private Class Clazz; public BeanListHandler (Class Clazz) {this. clazz = clazz;} public Object handler (ResultSet rs) {try {ListList = new ArrayList(); While (rs. next () {Object bean = clazz. newInstance (); ResultSetMetaData metadata = rs. getMetaData (); int count = metadata. getColumnCount (); for (int I = 0; I  0? List: null;} catch (Exception e) {throw new RuntimeException (e );}}} 

When the result set Processor provided by the Framework does not meet your requirements, you can implement the ResultSetHandler interface and write the result set processor that meets your business requirements.

With the preceding JdbcUtils framework, CRUD operations on a single object are very convenient, as shown below:

package me.gacl.dao;import java.sql.SQLException;import java.util.List;import me.gacl.domain.Account;import me.gacl.util.BeanHandler;import me.gacl.util.BeanListHandler;import me.gacl.util.JdbcUtils;public class AccountDao { public void add(Account account) throws SQLException{ String sql = "insert into account(name,money) values(?,?)"; Object params[] = {account.getName(),account.getMoney()}; JdbcUtils.update(sql, params); } public void delete(int id) throws SQLException{ String sql = "delete from account where id=?"; Object params[] = {id}; JdbcUtils.update(sql, params); } public void update(Account account) throws SQLException{ String sql = "update account set name=?,money=? where id=?"; Object params[] = {account.getName(),account.getMoney(),account.getId()}; JdbcUtils.update(sql, params); } public Account find(int id) throws SQLException{ String sql = "select * from account where id=?"; Object params[] = {id}; return (Account) JdbcUtils.query(sql, params, new BeanHandler(Account.class)); } public List getAll() throws SQLException{ String sql = "select * from account"; Object params[] = {}; return (List) JdbcUtils.query(sql, params,new BeanListHandler(Account.class)); }}

The compiled JDBC framework simulates the implementation of the Apache DBUtils framework. The next article will introduce the Apache DBUtils framework in detail.