Spring JdbcTemplate usage summary and experience sharing, springjdbctemplate

Spring JdbcTemplate usage summary and experience sharing, springjdbctemplate
Introduction Some recently developed projects are all web backend projects based on the Spring boot framework. They use JdbcTemplate to perform database operations. In actual development, they have gained some effective development experience, I have made a record and summary on some pitfalls to share with you. You are welcome to leave a message to communicate with me. Correct use of JdbcTemplate to perform database operations 1. Bean declaration New Type DatabaseConfiguration, add annotation @ Configuration this type is used for DataSource and JdbcTempate Bean declaration basic code is as follows

@Configurationclass DatabaseConfiguration {    @Bean    public DataSource dataSource() {        DataSource dataSource;        ...        return dataSource;    }     @Bean    public JdbcTemplate jdbcTemplate() {        return new JdbcTemplate(dataSource());    }}

Note that DataSource is defined as Bean, and the TransactionManager object created by Spring boot is dependent on DataSource by default. If DataSource is not declared as Bean, database transactions cannot be used. 2. encapsulate the Dao type for each database table, construct an independent Dao type, provide interfaces for the business layer to call, and inject the JdbcTemplate object. The actual operation db can define the base class as follows:

/** * Created by Ant on 2015/1/1. */public abstract class AntSoftDaoBase {    @Resource(name = "jdbcTemplate")    private JdbcTemplate jdbcTemplate;     private String tableName;     protected AntSoftDaoBase(String tableName) {        this.tableName = tableName;    }     protected JdbcTemplate getJdbcTemplate() {        return jdbcTemplate;    }     public void clearAll() {        getJdbcTemplate().update("DELETE FROM " + tableName);    }     public int count() {        return getJdbcTemplate().queryForObject( "SELECT count(*) FROM " + tableName, Integer.class);    }}

Inject a jdbcTemplate object through @ Resource. Because I only define a bean OF THE jdbcTemplate type, you can omit the name parameter and @ Resource here, or use @ Autowired to create a corresponding Dao derived class for the table app in the database.

/** * Created by Ant on 2015/1/1. */@Repositorypublic class AppDao extends AntSoftDaoBase{    private Logger logger = LoggerFactory.getLogger(getClass());    private static final String TABLE_NAME = "app";    private static final String COLUMN_NAMES = "name, user_id, title, description, ctime, status";    public AppDao() {        super(TABLE_NAME);    }    public int create(final AppInfo appInfo) {       ...    }    public List<AppInfo> list(int pageNo, int pageSize) {        ...    }    public AppInfo get(int appId) {       ...    }    public void update(AppInfo appInfo) {        ...    }}

This Dao type provides additional, query, modify, and Query Interfaces for AppInfo data. The specific implementation of these interfaces will be detailed later.

 

3. Using the Tomcat-jdbc database connection pool to introduce the database connection pool will greatly improve database operability. This example describes how to use the Tomcat-jdbc database connection pool. The tomcat-jdbc dependency is introduced into the Pom file.

        <dependency>            <groupId>org.apache.tomcat</groupId>            <artifactId>tomcat-jdbc</artifactId>            <version>7.0.42</version>        </dependency>

The logic for creating a connection pool DataSource is encapsulated in the following method. The DatabaseConfiguration. dataSource method can be called directly to obtain the DataSource with the connection pool function.

Private DataSource getTomcatPoolingDataSource (String databaseUrl, String userName, String password) {org. apache. tomcat. jdbc. pool. dataSource dataSource = new org. apache. tomcat. jdbc. pool. dataSource (); dataSource. setDriverClassName ("com. mysql. jdbc. driver "); dataSource. setUrl (databaseUrl); dataSource. setUsername (userName); dataSource. setPassword (password); dataSource. setInitialSize (5); // Number of initialized connections created when the connection pool is started (default value: 0) dataSource. setMaxActive (20); // maximum number of connections simultaneously in the connection pool dataSource. setMaxIdle (12); // The maximum number of idle connections in the connection pool. The number of idle connections that have exceeded will be released. If it is set to a negative number, the dataSource is not limited. setMinIdle (0); // the minimum number of idle connections in the connection pool. If the number is lower than this value, a new connection dataSource is created. setMaxWait (60000); // maximum wait time. When no connection is available, the maximum wait time for the connection pool to wait for the connection to be released is exceeded. An exception is thrown if the maximum wait time limit is exceeded, if the value-1 is set, the dataSource is infinite. setRemoveAbandonedTimeout (180); // when the time limit is exceeded, the useless (obsolete) connection dataSource is recycled. setRemoveAbandoned (true); // indicates whether to recycle dataSource after removeAbandonedTimeout is exceeded. setTestOnBorrow (true); dataSource. setTestOnReturn (true); dataSource. setTestWhileIdle (true); dataSource. setValidationQuery ("SELECT 1"); dataSource. setTimeBetweenEvictionRunsMillis (1000*60*30); // check that the time interval for invalid connections is set to 30 minutes return dataSource ;}

Please adjust the configuration of each value according to the actual situation

Configure the reconnection logic to automatically reconnect when the connection fails. By default, the mysql database will close the connection for more than 8 hours. The first java backend project developed after the connection pool is added to the database connection pool will always fail in the previous database operations on the web platform, configure the reconnection logic.

 

Use HSQL for database operation unit testing Advice: database operations require unit test coverageI give the following reasons: 1. For JdbcTemplate, You Need To directly type an SQL statement in the Code. Now, the editor does not seem to be able to spell out the SQL statements embedded in java code, experienced experts, misspelled SQL is not uncommon 2. After updating the database table structure, I hope to quickly know which code needs to be changed. It is faster than human search to run a single test once. The reconstruction speed is * 103. After a single test is ensured, the Dao layer is almost reliable. Program error. You only need to check the cause at the business layer. The Debug speed is * 104. If there is no single test, you need to build more comprehensive test data during the integration test and insert data to mysql. Difficult data construction and maintenance, long test period 1. The Embedded Database HSQLDBHSQLDB is an open-source JAVA database with standard SQL syntax and JAVA interface a) Configure HSQL DataSource to introduce HSQLDB Dependencies

        <dependency>            <groupId>org.hsqldb</groupId>            <artifactId>hsqldb</artifactId>            <version>2.3.0</version>        </dependency>

You can use the following method to generate DataSource:

    @Bean    public DataSource antsoftDataSource() {        DataSource dataSource;        if (antsoftDatabaseIsEmbedded) {            dataSource = getEmbeddedHsqlDataSource();        } else {            dataSource =                    getTomcatPoolingDataSource(antsoftDatabaseUrl, antsoftDatabaseUsername, antsoftDatabasePassword);        }        return dataSource;    }

The Field Values of antsoftDatabaseIsEmbedded and other objects are defined as follows:

    @Value("${antsoft.database.isEmbedded}")    private boolean antsoftDatabaseIsEmbedded;    @Value("${antsoft.database.url}")    private String antsoftDatabaseUrl;    @Value("${antsoft.database.username}")    private String antsoftDatabaseUsername;    @Value("${antsoft.database.password}")    private String antsoftDatabasePassword;

Use @ Value to specify the key name of the configuration item, and replace the corresponding field with the key search configuration Value at run time.

The configuration file is resources/application. properties.

antsoft.database.isEmbedded=falseantsoft.database.url=jdbc:mysql://127.0.0.1:3306/antsoft_appantsoft.database.username=rootantsoft.database.password=ant

Unit Test profile is resources/application-test.properties

antsoft.database.isEmbedded=true

Indicates using an embedded database for a single test

B) when the HSQL database initialization script is used to create the Hsql DataSource, the database initialization operation is performed to build the required table structure and insert the initial data. The getEmbeddedHsqlDataSource method is implemented as follows:

    private DataSource getEmbeddedHsqlDataSource() {        log.debug("create embeddedDatabase HSQL");        return new EmbeddedDatabaseBuilder().setType(EmbeddedDatabaseType.HSQL).addScript("classpath:db/hsql_init.sql").build();    }

Use addScript to specify the initialization Database SQL script resources/db/hsql_init. SQL. The content is as follows:

SET DATABASE SQL SYNTAX MYS TRUE;CREATE TABLE app (  id int GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL,  name varchar(64) NOT NULL,  user_id varchar(64) NOT NULL,  title varchar(64) NOT NULL,  description varchar(1024) NOT NULL,  ctime datetime NOT NULL,  status int NOT NULL,  PRIMARY KEY (id),  UNIQUE (name));CREATE TABLE app_unique_name (  id int GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL,  unique_name varchar(64) NOT NULL UNIQUE,  PRIMARY KEY (id));...

There are differences between the HSQL syntax and the MySql syntax. Note that the differences I noted during the development process are listed as follows:

-Data types such as tinyint are not supported. parentheses indicating the data length are not allowed after int. For example, int (11) is not supported)

-Index indexing is not supported, but unique index is supported.

-AUTO_INCREMENT syntax is not supported.

C) verify your HSQL script

Verify the correctness of hsql statements using the following methods:

Find hsqldb in the local maven repository (hsqldb has been introduced correctly). The local directory of the blogger is C: \ Users \ ant \. m2 \ repository \ org \ hsqldb \ 2.3.2.

Execute hsqldb-2.3.2.jar (java-jar hsqldb-2.3.2.jar)

By default, a prompt box is displayed. Click OK. Enter an SQL statement on the right and execute the Execuete SQL statement in the toolbar.

The following shows that the SQL statement is successfully executed.

The SQL statement is as follows:

CREATE TABLE app_message (  id bigint GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL,  app_id int NOT NULL,  message varchar(1024) NOT NULL,  ctime datetime NOT NULL,  status int NOT NULL,  PRIMARY KEY (id));

In this SQL statement, GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) is used to replace AUTO_INCREMENT. It seems that HSQL does not support this syntax, the reader personally tried the AUTO_INCREMENT alternative source: http://stackoverflow.com/questions/13206473/create-table-syntax-not-working-in-hsql 2. Write unit test override Dao database operations using JUnit and Spring-test. Single test can directly inject the required Bean uniform definition unit test Annotation

@Retention(RetentionPolicy.RUNTIME)@Target(ElementType.TYPE)@SpringApplicationConfiguration(classes = Application.class)@WebAppConfiguration@IntegrationTest("server.port=0")@ActiveProfiles("test")public @interface AntSoftIntegrationTest {}

Define the test Type and add the following annotation

@AntSoftIntegrationTest@RunWith(SpringJUnit4ClassRunner.class)

My expectation for my code is that as much as 100% of Dao methods as possible are covered by unit tests.

The following code demonstrates the basic unit test on AppService (its interface implements forwarding and calls the corresponding AppDao interface). The create, update, and get operations are tested.

@ AntSoftIntegrationTest @ RunWith (SpringJUnit4ClassRunner. class) public class AppServiceTests {@ Autowired private AppService appService; @ Autowired private TestService testService; @ Before public void clearApp () {testService. clearApp () ;}@ Test public void testApp () {final String name = "xxx"; final String userId = "Ant"; final String title = "Hello World "; final String description = "Description for Hello World"; final String updatedName = "xxx"; final String updatedUserId = "Ant"; final String updatedTitle = "Hello World "; final String updatedDescription = "Description for Hello World"; int appId; {// create AppInfo appInfo = new AppInfo (); appInfo. setName (name); appInfo. setUserId (userId); appInfo. setTitle (title); appInfo. setDescription (description); appId = appService. createApp (appInfo);} CheckAppInfo (appId, name, userId, title, description, AppStatus. NORMAL); {// update AppInfo appInfo = new AppInfo (); appInfo. setId (appId); appInfo. setName (updatedName); appInfo. setUserId (updatedUserId); appInfo. setTitle (updatedTitle); appInfo. setDescription (updatedDescription); appService. updateApp (appInfo);} CheckAppInfo (appId, updatedName, updatedUserId, updatedTitle, updatedDescription, AppStatus. NORMAL);} // obtain the application and verify the data private void CheckAppInfo (int appId, String name, String userId, String title, String description, AppStatus appStatus) {AppInfo appInfo = appService. getApp (appId); assertEquals (appId, appInfo. getId (); assertEquals (name, appInfo. getName (); assertEquals (userId, appInfo. getUserId (); assertEquals (title, appInfo. getTitle (); assertEquals (description, appInfo. getDescription (); assertEquals (appStatus, appInfo. getStatus ());}}

Development Experience Sharing this section records the Problems and Solutions encountered by the author in the actual project development process, as well as some good development practices. 1. invalid transactionsWhen using the transaction processing mechanism provided by Spring, the start and commit rollback operations of the transaction are maintained by the TransactionManager object. during development, we only need to add the @ Transactional annotation to the method for transaction processing, you can easily start the transaction. See Spring boot source code spring-boot/spring-boot-autoconfigure/src/main/java/org/springframework/boot/autoconfigure/jdbc/cetcetransactionmanagerautoconfiguration. java

/** * {@link EnableAutoConfiguration Auto-configuration} for * {@link DataSourceTransactionManager}. * * @author Dave Syer */@Configuration@ConditionalOnClass({ JdbcTemplate.class, PlatformTransactionManager.class })public class DataSourceTransactionManagerAutoConfiguration implements Ordered {    @Override    public int getOrder() {        return Integer.MAX_VALUE;    }    @Autowired(required = false)    private DataSource dataSource;    @Bean    @ConditionalOnMissingBean(name = "transactionManager")    @ConditionalOnBean(DataSource.class)    public PlatformTransactionManager transactionManager() {        return new DataSourceTransactionManager(this.dataSource);    }    @ConditionalOnMissingBean(AbstractTransactionManagementConfiguration.class)    @Configuration    @EnableTransactionManagement    protected static class TransactionManagementConfiguration {    }}

It can be seen that if DataSource is not declared as Bean, transactionManager will not be created, and @ Transactional annotation will be useless.

Of course, there is also another method, and does not use the default transactionManager, but is customized, as shown below, add the following method in the DatabaseConfiguration class

    @Bean    public PlatformTransactionManager transactionManager() {return new DataSourceTransactionManager(myDataSource());    }

By default, the method name is used as the bean name, so the default transactionManager Bean object is overwritten here.

How to enable transactions for multiple data sources (non-distributed transactions described here )? If the project involves operations on multiple databases, multiple data sources exist. The solution is the same as the preceding example, that is, self-declared transactionManager Bean, which corresponds to each DataSource one by one. Note that the @ Transactional annotation is used to add the name of the transactionManager Bean, for example, @ Transactional ("myTransactionManager") 2. The following Dao type is used to obtain the auto-incrementing id of the new data, the create method demonstrates how to create a MessageInfo record and obtain the primary key of the newly added data, that is, the auto-increment id.

@ Repositorypublic class MessageDao extends AntSoftDaoBase {private static final String TABLE_NAME = "app_message"; private static final String COLUMN_NAMES = "app_id, message, ctime, status"; protected MessageDao () {super (TABLE_NAME);} private static final String SQL _INSERT_DATA = "INSERT INTO" + TABLE_NAME + "(" + COLUMN_NAMES + ")" + "VALUES (?, ?, ?, ?) "; Public int create (final MessageInfo messageInfo) {KeyHolder keyHolder = new GeneratedKeyHolder (); getJdbcTemplate (). update (new PreparedStatementCreator () {public PreparedStatement createPreparedStatement (Connection connection) throws SQLException {PreparedStatement ps = connection. prepareStatement (SQL _INSERT_DATA, Statement. RETURN_GENERATED_KEYS); int I = 0; ps. setInt (++ I, messageInfo. getAppId (); ps. setString (++ I, messageInfo. getMessage (); ps. setTimestamp (++ I, new Timestamp (new Date (). getTime (); ps. setInt (++ I, 0); // The default status is 0 return ps; }}, keyHolder); return keyHolder. getKey (). intValue ();}...}

 

3. The data items IN the SQL IN statement IN are separated by commas (,). The number of data items is not fixed ,"? "Only single parameter replacement is supported, so it cannot be used. You can only splice SQL strings. For example, to update the status value of a batch of data, the following is a simple and effective implementation method:

    private static final String SQL_UPDATE_STATUS =            "UPDATE " + TABLE_NAME + " SET "                    + "status = ? "                    + "WHERE id IN (%s)";    public void updateStatus(List<Integer> ids, Status status) {        if (ids == null || ids.size() == 0) {            throw new IllegalArgumentException("ids is empty");        }        String idsText = StringUtils.join(ids, ", ");        String sql = String.format(SQL_UPDATE_STATUS , idsText);        getJdbcTemplate().update(sql, status.toValue());    } 

 

4. General Data Query methods and precautions

The get method is provided in the AppDao type to obtain the APP data based on an appId. The Code is as follows:

    private static final String SQL_SELECT_DATA =            "SELECT id, " + COLUMN_NAMES + " FROM " + TABLE_NAME + " WHERE id = ?";    public AppInfo get(int appId) {        List<AppInfo> appInfoList = getJdbcTemplate().query(SQL_SELECT_DATA, new Object[] {appId}, new AppRowMapper());        return appInfoList.size() > 0 ? appInfoList.get(0) : null;    }

 

 Note:Because the primary key id uniquely identifies a data item, some users use queryForObject to obtain the data item. If the target data is not found, this method does not return null, but throws an error in EmptyResultDataAccessException. Use the query method and check the returned data volume

AppRowMapper is used to parse each row of data and convert it to the Model type. Its code is as follows:

    private static class AppRowMapper implements RowMapper<AppInfo> {        @Override        public AppInfo mapRow(ResultSet rs, int i) throws SQLException {            AppInfo appInfo = new AppInfo();            appInfo.setId(rs.getInt("id"));            appInfo.setName(rs.getString("name"));            appInfo.setUserId(rs.getString("user_id"));            appInfo.setTitle(rs.getString("title"));            appInfo.setDescription(rs.getString("description"));            appInfo.setCtime(rs.getTimestamp("ctime"));            appInfo.setStatus(AppStatus.fromValue(rs.getInt("status")));            return appInfo;        }    }