Flexible configuration of spring data sources

Environment:
Java SE 1.5
Spring-1, 2.5.1
Mysql-connector-java-5.1.5.zip
MySQL 5.x

To obtain data from a database, we first need to obtain a database connection. Spring uses the datasource object to complete this task. Datasource is part of the JDBC specification and is considered a common database connection factory. Using datasource, the container or framework can separate the connection pool and transaction management details from the application code. As a developer, you may need to know the details of connecting to the database during product development and testing. But you don't need to know the details when implementing the product. Generally, the database administrator will help you set the data source.

When using spring JDBC, you can obtain the data source through JNDI or configure the data source by yourself (using the datasource implementation class provided by spring ). The latter can be used for unit testing without the Web container. Here we will use drivermanagerdatasource, but datasource has multiple implementations, which will be discussed later. Using drivermanagerdatasource is similar to obtaining a JDBC connection. First, you must specify the full qualified name of the JDBC driver so that drivermanager can load the JDBC Driver Class. Then you must provide a URL (different from the JDBC driver, to ensure correct settings, please refer to the relevant JDBC driver documentation). Finally, you must provide a user name and password for connecting to the database.

The above two paragraphs are taken from the original spring development document. I will write a small application to test the performance of srping JDBC support.

In the following test, we need to achieve the following goal: Query t_user data in a table of MySQL database testdb by obtaining the spring data source.

1. Create a project and load the Toolkit and driver used by the program. Add the spring configuration file applicationcontext. XML to the source code directory. The configuration content is as follows:

<? XML version = "1.0" encoding = "gb2312"?>
<! Doctype beans public "-// spring // DTD bean // en" "http://www.springframework.org/dtd/spring-beans.dtd">
<Beans>
<! -- Database data source definition -->
<Bean id = "rptds" class = "org. springframework. JDBC. datasource. drivermanagerdatasource" Destroy-method = "close">
<Property name = "driverclassname">
<Value> com. MySQL. JDBC. Driver </value>
</Property>
<Property name = "url">
<Value> JDBC: mysql: // localhost: 3306/testdb </value>
</Property>
<Property name = "username">
<Value> root </value>
</Property>
<Property name = "password">
<Value> leizhimin </value>
</Property>
</Bean>
</Beans>
 

2. Create a database environment:

Create Database if not exists testdb;
Drop table if exists t_user;

Create Table t_user (
Id int (11) not null auto_increment,
Firstname varchar (50) default null,
Lastname varchar (50) default null,
Zipcode varchar (10) default null,
Tel varchar (20) default null,
Address varchar (200) default null,
Primary Key (ID)
) Engine = InnoDB default charset = GBK;

Insert into t_user (ID, firstname, lastname, zipcode, tel, address) Values
(1, 'hahaha', 'hehes', '20170101', '20160301', 'astica '),
(2, 'lei', 'aaa', '000000', '000000', 'zhengzhou Jing San road ');
 

Iii. Tools for getting spring bean container Environment

Package com. lavasoft. dbtest;
Import org. springframework. Context. Support. classpathxmlapplicationcontext;

/**
* Created by intellij idea.
* File: contexthelper. Java
* User: leizhimin
* Date: 14:20:46
* Spring bean container environment obtaining tool class
*/
Public final class contexthelper {
Private Static classpathxmlapplicationcontext _ CTX;

Static {
_ CTX = new classpathxmlapplicationcontext ("applicationcontext. xml ");
}

Private contexthelper (){
}

Public static classpathxmlapplicationcontext getcontext (){
Return _ CTX;
}
}
 

Iii. database tools for writing and Retrieval

Package com. lavasoft. dbtest;
Import org. Apache. commons. Logging. log;
Import org. Apache. commons. Logging. logfactory;

Import javax. SQL. datasource;
Import java. SQL. connection;
Import java. SQL. sqlexception;
Import java. SQL. callablestatement;

/**
* Created by intellij idea.
* File: dbutil. Java
* User: leizhimin
* Date: 14:26:30
* Database tools
*/
Public final class dbutil {
Private Static final log = logfactory. getlog (dbutil. Class );

/**
* Obtain the system data source
*
* @ Return datasource
*/
Public static datasource getdatasource (){
Datasource = NULL;
Try {
Datasource = (datasource) contexthelper. getcontext (). getbean ("rptds ");
} Catch (exception e ){
Log. Error ("An error occurred while obtaining the data source. Check the spring data source configuration! ");
}
Return datasource;
}

/**
* Getting database connections
*
* @ Return connection
*/
Public static connection makeconnection (){
Connection conn = NULL;
Try {
Conn = getdatasource (). getconnection ();
} Catch (sqlexception e ){
Log. Error ("An error occurred while obtaining the database connection from the data source! ");
E. printstacktrace ();
}
Return conn;
}

/**
* Execute an SQL statement without Parameters
*
* @ Param procedurename: name of the stored procedure
* @ Return Boolean return the execution result of the stored procedure. True indicates that the execution is successful, and false indicates that the execution fails.
*/
Public static Boolean executebsdprocedure (string procedurename ){
Boolean flag = false;
String sqlstr = "{call" + procedurename + "()}";
Callablestatement Cs;
Connection conn = makeconnection ();
Try {
Cs = (callablestatement) Conn. preparestatement (sqlstr );
Cs.exe cuteupdate (sqlstr );
Flag = true;
} Catch (sqlexception e ){
Log. Error ("calling stored procedure" + sqlstr + "failed! ");
E. printstacktrace ();
}
Return flag;
}
}
 
Iv. Write Test

Package com. lavasoft. dbtest;
Import org. Apache. commons. Logging. log;
Import org. Apache. commons. Logging. logfactory;

Import java. SQL. connection;
Import java. SQL. statement;
Import java. SQL. resultset;
Import java. SQL. sqlexception;

/**
* Created by intellij idea.
* File: Test. Java
* User: leizhimin
* Date: 14:41:49
* Spring Data Source Application Test
*/
Public class test {
Private Static final log = logfactory. getlog (test. Class );

Public static void main (string ARGs []) {
Test. Test ();
}

Public static void test (){
String testsql = "select * From t_user ";
Connection conn = dbutil. makeconnection ();
Statement stmt = NULL;
Try {
Stmt = conn. createstatement (resultset. type_forward_only, resultset. concur_read_only, resultset. close_cursors_at_commit );
Resultset rs = stmt.exe cutequery (testsql );
While (Rs. Next ()){
String firstname = Rs. getstring ("firstname ");
String lastname = Rs. getstring ("lastname ");
System. Out. println (firstname + "" + lastname );
}
} Catch (sqlexception e ){
E. printstacktrace ();
} Finally {
If (stmt! = NULL ){
Try {
Stmt. Close ();
} Catch (sqlexception e ){
Log.info ("an exception occurred when closing the statement object! ");
E. printstacktrace ();
}
}
If (Conn! = NULL ){
Try {
Conn. Close ();
} Catch (sqlexception e ){
Log. Error ("An error occurred while closing the database connection! ");
E. printstacktrace ();
}
}
}
}
}
 

Test result:

Haha hehe
Lei aaa
Process finished with exit code 0
 

V. Data source replacement

The drivermanagerdatasource implemented by spring does not provide the connection pool function. It is only used for simple standalone connection tests and is not suitable for real projects. You can consider replacing it with a mature data connection pool. The Apache DBCP connection pool is good. To replace it, you need to load the DBCP-related toolkit.

<? XML version = "1.0" encoding = "gb2312"?>
<! Doctype beans public "-// spring // DTD bean // en" "http://www.springframework.org/dtd/spring-beans.dtd">
<Beans>
<! -- Database data source definition -->
<Bean id = "rptds" class = "org. Apache. commons. DBCP. basicdatasource" Destroy-method = "close">
<Property name = "driverclassname">
<Value> com. MySQL. JDBC. Driver </value>
</Property>
<Property name = "url">
<Value> JDBC: mysql: // localhost: 3306/testdb </value>
</Property>
<Property name = "username">
<Value> root </value>
</Property>
<Property name = "password">
<Value> leizhimin </value>
</Property>
</Bean>
</Beans>
 

Org. apache. commons. DBCP. basicdatasource serves as the injected datasource instance. To use the functionality of DBCP, you need to set the commons-dbcp.jar, commons-pool.jar, and commons-collections in the classpath path. jar. Note that the "Destroy-method" attribute is set on datasource. This ensures that beanfactory also closes basicdatasource when it is disabled.

6. Use the JNDI Data Source

If your servlet container provides the datasource of JNDI (Java Naming and Directory Interface), you can simply replace it with the datasource:

<? XML version = "1.0" encoding = "gb2312"?>
<! Doctype beans public "-// spring // DTD bean // en" "http://www.springframework.org/dtd/spring-beans.dtd">
<Beans>
<! -- Database data source definition -->
<Bean id = "datasource"
Class = "org. springframework. Indi. jndiobjectfactorybean">
<Property name = "jndiname">
<Value> JDBC/testds </value>
</Property>
</Bean>
</Beans>
 

To use org. springframework. Indi. jndiobjectfactorybean, you need a spring-context.jar that "jndiname" actually depends on the JNDI query name you set.