Java Web series: JDBC basics, webjdbc

Java Web series: JDBC basics, webjdbc

The corresponding technology of ADO. NET in Java is JDBC, the enterprise database DataAccessApplicationBlock module corresponds to the spring-jdbc module in Java, and the corresponding ORM of EntityFramework in Java is Hibernate. Relational databases, SQL statements, database transactions, and distributed transactions are all common.

1. JDBC

JDBC code is the same as ADO. NET Code. It is not suitable for direct use in projects except to write a demo to grasp the core objects. In addition, the academic abstract interface that has not changed for many years in Java has brought great inconvenience to most of the containers and frameworks I currently see, for example, some abstract types defined in Tomcat and Spring are both attributes and methods. NET, but has to adapt to the basic interface in Java. Abstract cannot focus only on the operation interface, rather than the basic data structure, as in Java. Correct type abstraction is more important than method abstraction, however, the abstract method in Java seems to have another principle that is distinctive, not just focusing on operations.

(1) core objects

The four core abstract classes in ADO. NET are DbConnection, DbCommand, DbParameter, DbTransaction. DataAdapter, and DataSet built on the core object.

The four core interfaces of JDBC: DataSource, Connection, Statement, and ResultSet. PreparedStatement is the subtype of Statement.

Although the two cannot be completely matched, the core operations on database operations are the same. They are the process of opening links, sending commands, and closing connections.

(2) parameter Processing

The core of a parameter is the index and parameter value. ADO. NET provides the DbDataParameter type for parameter abstraction. JDBC does not provide the abstract type of a parameter. Instead, it uses the PreparedStatement defined method to set the parameter. You can obtain this type object through the Connection object.

(3) Transaction Processing

The core operations of a transaction are commit and rollback. ADO. NET provides the DbTransaction type for transaction abstraction. You can use the BeginTransaction method of DbConnection to enable explicit transactions. JDBC does not provide the abstract type of the transaction, but uses the setAutoCommit, commit, and rollback methods defined by Connection to operate the transaction. The automatic commit of implicit transactions is consistent with the manual control of explicit transactions.

(4) Data Source

The Connection in JDBC does not have advanced functions such as Connection pool. DataSource defines a data source interface for advanced functions such as connection management, connection pool, and distributed transactions. You do not need to emphasize the importance of the connection pool. We can use third-party DataSource implementation such as Apache Commons DBCP 2 or C3P0 (Hibernate). The Spring framework also has some built-in simple DataSource implementation, such as SimpleDriverDataSource, SingleConnectionDataSource, and DriverManagerDataSource.

We use DriverManager, commons-dbcp2 (reference 1), c3p0 (Reference 2) and h2database (Reference 3) to demonstrate the use of no connection pool, dbcp2 connection pool, c3p0 connection pool respectively:

 1 package test.jdbc; 2  3 import java.beans.PropertyVetoException; 4 import java.sql.Connection; 5 import java.sql.DriverManager; 6 import java.sql.PreparedStatement; 7 import java.sql.ResultSet; 8 import java.sql.SQLException; 9 import javax.sql.DataSource;10 11 import org.apache.commons.dbcp2.BasicDataSource;12 import com.mchange.v2.c3p0.ComboPooledDataSource;13 14 public class Jdbc {15     public static void main(String[] args) throws ClassNotFoundException, SQLException, PropertyVetoException {16         String ddl = "create table user(id integer not null primary key,username varchar(64))";17         String dml = "insert into user (id,username) values(1,'tom')";18         String query = "select id,username from user where username=?";19         String className = "org.h2.Driver";20         String url = "jdbc:h2:mem:test";21         // Connection conn = DriverManager.getConnection(url);22         Connection conn = get_dbcp2_dataSource(className, url).getConnection();23         // Connection conn = get_c3p0_dataSource(className,//24         // url).getConnection();25         conn.setAutoCommit(false);26         conn.prepareStatement(ddl).execute();27         conn.createStatement().execute(dml);28         conn.commit();29         conn.setAutoCommit(true);30         PreparedStatement statement = conn.prepareStatement(query);31         statement.setString(1, "tom");32         ResultSet rs = statement.executeQuery();33         User user = new User();34         while (rs.next()) {35             user.setId(rs.getInt(1));36             user.setUsername("username");37             break;38         }39         conn.close();40         System.out.println(String.format("id:%d,username:%s", user.getId(), user.getUsername()));41     }42 43     public static Connection getConnection(String driverClassName, String url) {44         try {45             Class.forName(driverClassName);46         } catch (ClassNotFoundException e1) {47             e1.printStackTrace();48         }49         Connection conn = null;50         try {51             conn = DriverManager.getConnection(url);52         } catch (SQLException e) {53             e.printStackTrace();54         }55         return conn;56     }57 58     public static DataSource get_dbcp2_dataSource(String clssName, String url) {59 60         BasicDataSource dataSource = new BasicDataSource();61         dataSource.setDriverClassName(clssName);62         dataSource.setUrl(url);63         return dataSource;64     }65 66     public static DataSource get_c3p0_dataSource(String clssName, String url) throws PropertyVetoException {67 68         ComboPooledDataSource dataSource = new ComboPooledDataSource();69         dataSource.setDriverClass(clssName);70         dataSource.setJdbcUrl(url);71         return dataSource;72     }73 }

 

Maven dependencies of 2 dbcp, c3p0, and h2database are as follows:

 1         <dependency> 2             <groupId>com.h2database</groupId> 3             <artifactId>h2</artifactId> 4             <version>1.4.190</version> 5         </dependency> 6         <dependency> 7             <groupId>org.apache.commons</groupId> 8             <artifactId>commons-dbcp2</artifactId> 9             <version>2.1.1</version>10         </dependency>11         <dependency>12             <groupId>com.mchange</groupId>13             <artifactId>c3p0</artifactId>14             <version>0.9.5.2</version>15         </dependency>

2. Spring Jdbc

JDBC and ADO. NET APIs are similar and are not suitable for our direct use. If it is not applicable to the ORM framework, we use DataAccessApplicationBlock in. NET, and we can use Spring JDBC in Java. The core class of Spring JDBC is JdbcTemplate.

(1) Sprnig delegate implementation

To use Spring Jdbc functions, we must first understand the use of delegation in Spring. Spring defines an interface containing the signature of the Delegate (these interfaces are usually not implemented internally ), when you need to delegate a parameter, use this interface as the parameter type. Using the Anonymous class function in Java, You can implement interface-defined delegation in Inline code. The following lists several common delegate interfaces:

Func <Connection, PreparedStatement>: createPreparedStatement method of the PreparedStatement interface.

1 public interface PreparedStatementCreator {2 3     PreparedStatement createPreparedStatement(Connection con) throws SQLException;4 5 }

Func <PreparedStatement, T>: doInPreparedStatement method of the PreparedStatementCallback generic interface.

1 public interface PreparedStatementCallback<T> {2 3     T doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException;4 5 }

Func <TransactionStatus, T>: The T doInTransaction (TransactionStatus status) method of the TransactionCallback generic interface.

1 public interface TransactionCallback<T> {2 3     T doInTransaction(TransactionStatus status);4 5 }

Fcun <ResultSet, T>: The T extractData (ResultSet rs) method of the ResultSetExtractor generic method.

1 public interface ResultSetExtractor<T> {2 3     T extractData(ResultSet rs) throws SQLException, DataAccessException;4 5 }

(1) JdbcTemplate

JdbcTemplate must be used with DataSource. Many JdbcTemplate codes are generated using the IDE to automatically generate anonymous classes. There are not many handwritten codes.

 1     public static void main(String[] args) throws ClassNotFoundException, SQLException, PropertyVetoException { 2         String ddl = "create table user(id integer not null primary key,username varchar(64))"; 3         String dml = "insert into user (id,username) values(1,'tom')"; 4         String query = "select id,username from user where username=?"; 5         String className = "org.h2.Driver"; 6         String url = "jdbc:h2:mem:test"; 7  8         DataSource dataSource = get_dbcp2_dataSource(className, url); 9         JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);10         PlatformTransactionManager tm = new DataSourceTransactionManager(dataSource);11         TransactionTemplate tt = new TransactionTemplate(tm);12         tt.execute(new TransactionCallback() {13 14             @Override15             public Object doInTransaction(TransactionStatus status) {16                 jdbcTemplate.execute(ddl);17                 jdbcTemplate.execute(dml);18                 return status;19             }20         });21 22         User user = jdbcTemplate.query(query, new Object[] { "tom" }, new ResultSetExtractor<User>() {23             @Override24             public User extractData(ResultSet rs) throws SQLException, DataAccessException {25                 User user = new User();26                 while (rs.next()) {27                     user.setId(rs.getInt(1));28                     user.setUsername(rs.getString("username"));29                     break;30                 }31                 return user;32             }33         });34         System.out.println(String.format("id:%d,username:%s", user.getId(), user.getUsername()));35     }

(3) TransactionTemplate

In the above Code, TransactionTemplate is used for transaction processing. TransactionTemplate depends on PlatformTransactionManager. The cetcetransactionmanager implementation class defined in Spring Jdbc is used to process database transactions. There are other implementations in Spring's orm, jsm, tx, and other modules.

(4) JdbcDaoSupport

JdbcDaoSupport is an abstract class that uses the JdbcTemplate field internally and implements the DaoSupport interface. It can be used as a reference or a base class for custom DAO classes.

 1 class MyDao extends JdbcDaoSupport { 2     public MyDao(DataSource dataSource) { 3         this.setJdbcTemplate(new JdbcTemplate(dataSource)); 4     } 5  6     @SuppressWarnings("unchecked") 7     public void init(String ddl, String dml) { 8         PlatformTransactionManager tm = new DataSourceTransactionManager(this.getDataSource()); 9         TransactionTemplate tt = new TransactionTemplate(tm);10 11         tt.execute(new TransactionCallback() {12 13             @Override14             public Object doInTransaction(TransactionStatus status) {15                 getJdbcTemplate().execute(ddl);16                 getJdbcTemplate().execute(dml);17                 return status;18             }19         });20     }21 22     public User getUser(String query, String username) {23         return this.getJdbcTemplate().query(query, new Object[] { username }, new ResultSetExtractor<User>() {24             @Override25             public User extractData(ResultSet rs) throws SQLException, DataAccessException {26                 User user = new User();27                 while (rs.next()) {28                     user.setId(rs.getInt(1));29                     user.setUsername(rs.getString("username"));30                     break;31                 }32                 return user;33             }34         });35     }36 }

Reference

(1) http://commons.apache.org/proper/commons-dbcp/download_dbcp.cgi

(2) http://sourceforge.net/projects/c3p0/

(3) http://www.h2database.com/html/cheatSheet.html

(4) http://commons.apache.org/proper/commons-dbcp/guide/jndi-howto.html