| |
This article aims to tell Spring JDBC module usage. Spring JDBC module is one of the basic module of the Spring Framework.
First, an overview
In the Spring JDBC module, all classes can be assigned to four separate packages:
1) core
That is the core of the package, which contains the JDBC core functions. There are many important classes within this package, including: JdbcTemplate class, SimpleJdbcInsert class, SimpleJdbcCall classes, and NamedParameterJdbcTemplate class.
2) datasource
That source packet data, access the data source utility class. It has achieve a variety of data sources, can JavaEE container external testing JDBC code.
3) object
That object package, an object-oriented way to access the database. It allows to execute the query and returns the result as a business object. It can be mapped between the query results column and business object attribute data table.
4) support
That support package, the package is to support the core classes and object packages. Such as providing a SQLException exception class conversion function.
Second, the configuration
Here we MySQL database, for example, start simple data source configuration:
@Configuration
@ComponentScan ( "Com.ch.myalbumjdbc")
public class SpringJdbcConfig {
@Bean
public DataSource mysqlDataSource () {
DriverManagerDataSource dataSource = new DriverManagerDataSource ();
dataSource.setDriverClassName ( "com.mysql.jdbc.Driver");
dataSource.setUrl ( "jdbc: mysql: // localhost: 3306 / springjdbc");
dataSource.setUsername ( "guest_user");
dataSource.setPassword ( "guest_password");
return dataSource;
}
}
Alternatively, you can also use the embedded database for development or testing, such as making quick configuration with HSQL embedded database and create an instance:
@Bean
public DataSource dataSource () {
return new EmbeddedDatabaseBuilder ()
.setType (EmbeddedDatabaseType.HSQL)
.addScript ( "classpath: jdbc / schema.sql")
.addScript ( "classpath: jdbc / test-data.sql") build ();.
}
Finally, you can also use XML configuration to achieve the foregoing effect of the configuration Notes:
< Bean id = "dataSource" class = "org.apache.commons.dbcp.BasicDataSource"
destroy-method = "close">
< Property name = "driverClassName" value = "com.mysql.jdbc.Driver" />
< Property name = "url" value = "jdbc: mysql: // localhost: 3306 / springjdbc" />
< Property name = "username" value = "guest_user" />
< Property name = "password" value = "guest_password" />
< / Bean>
Third, JdbcTemplate use and run queries
1, the basic query
JDBC Spring JDBC module template is the main API, which provides a common database access functions:
int result = jdbcTemplate.queryForObject (
"SELECT COUNT (*) FROM EMPLOYEE", Integer.class);
Here is a simple insertion:
public int addEmplyee (int id) {
return jdbcTemplate.update (
"INSERT INTO EMPLOYEE VALUES (,,,????)", 5, "Bill", "Gates", "USA");
}
Note that the arguments supplied standard syntax - character "?." Now, let us look at alternative syntax.
2, the query with named parameters
To obtain support named parameters, we need to use other JDBC --NamedParameterJdbcTemplate Spring JDBC template provided.
This class encapsulates JbdcTemplate, and provides for the use "?" To replace the traditional syntax for specifying parameters. It uses the parameters passed to replace the placeholder for the implementation of the Senate inquiry "?":
SqlParameterSource namedParameters = new MapSqlParameterSource () addValue ( "id", 1).;
return namedParameterJdbcTemplate.queryForObject (
"SELECT FIRST_NAME FROM EMPLOYEE WHERE ID =: id", namedParameters, String.class);
Please note that we are using named parameters MapSqlParameterSource to provide value.
Here is the bean class name property to determine the parameters simple example:
Employee employee = new Employee ();
employee.setFirstName ( "James");
String SELECT_BY_ID = "SELECT COUNT (*) FROM EMPLOYEE WHERE FIRST_NAME =: firstName";
SqlParameterSource namedParameters = new BeanPropertySqlParameterSource (employee);
return namedParameterJdbcTemplate.queryForObject (SELECT_BY_ID, namedParameters, Integer.class);
Note how we use BeanPropertySqlParameterSource implementation to replace the specified named parameters.
3, the query results are mapped to Java objects
There is also a very useful feature is the query results are mapped to Java objects - by implementing RowMapper interface.
For example, for each row in the result returned by the query, Spring will use this line mapping to populate Java bean:
public class EmployeeRowMapper implements RowMapper < Employee> {
@Override
public Employee mapRow (ResultSet rs, int rowNum) throws SQLException {
Employee employee = new Employee ();
employee.setId (rs.getInt ( "ID"));
employee.setFirstName (rs.getString ( "FIRST_NAME"));
employee.setLastName (rs.getString ( "LAST_NAME"));
employee.setAddress (rs.getString ( "ADDRESS"));
return employee;
}
}
Now, we pass row mapper to the query API, and get completely fill a good Java objects:
String query = "SELECT * FROM EMPLOYEE WHERE ID =?";
List < Employee> employees = jdbcTemplate.queryForObject (
query, new Object [] {id}, new EmployeeRowMapper ());
Fourth, exception conversion
Spring provides its own data out of the box as the root exception layered --DataAccessException exception, which is responsible for converting all of the original exception.
So developers do not need to deal with the underlying persistence exception, because Spring JDBC modules have been encapsulated in the underlying abnormality DataAccessException class and its subclasses.
This allows exception handling mechanism independent of the specific database currently in use.
In addition to the default SQLErrorCodeSQLExceptionTranslator class, developers can also provide their own SQLExceptionTranslator implementation.
Here is a simple example SQLExceptionTranslator custom implementation, integrity constraint error occurs when a custom error message:
public class CustomSQLErrorCodeTranslator extends SQLErrorCodeSQLExceptionTranslator {
@Override
protected DataAccessException customTranslate
(String task, String sql, SQLException sqlException) {
if (sqlException.getErrorCode () == -104) {
return new DuplicateKeyException (
"Custom Exception translator - Integrity constraint violation.", SqlException);
}
return null;
}
}
To use a custom exception converter, we need to pass it to JdbcTemplate-- by callingsetExceptionTranslator () method:
CustomSQLErrorCodeTranslator customSQLErrorCodeTranslator = new CustomSQLErrorCodeTranslator ();
jdbcTemplate.setExceptionTranslator (customSQLErrorCodeTranslator);
Fifth, use SimpleJdbc class implements JDBC operations
SimpleJdbc class provides easy way to configure and execute SQL statements. These classes use the metadata database to build basic queries. SimpleJdbcInsert class and SimpleJdbcCall class provides an easier way to perform insertion and stored procedure calls.
1, SimpleJdbcInsert class
Now, let us look at the implementation of a simple insert statement of the minimum configuration, INSERT statements based SimpleJdbcInsert class configuration generated.
All you need to provide are: table name, column names and values. Let us create SimpleJdbcInsert:
SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert (dataSource) .withTableName ( "EMPLOYEE");
Now, let us provide the column names and values, and perform actions:
public int addEmplyee (Employee emp) {
Map < String, Object> parameters = new HashMap < String, Object> ();
parameters.put ( "ID", emp.getId ());
parameters.put ( "FIRST_NAME", emp.getFirstName ());
parameters.put ( "LAST_NAME", emp.getLastName ());
parameters.put ( "ADDRESS", emp.getAddress ());
return simpleJdbcInsert.execute (parameters);
}
In order for the database to generate primary keys, you can use executeAndReturnKey () API, the actual auto-generated column, we also need to be configured:
SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert (dataSource)
.withTableName ( "EMPLOYEE")
.usingGeneratedKeyColumns ( "ID");
Number id = simpleJdbcInsert.executeAndReturnKey (parameters);
System.out.println ( "Generated id -" + id.longValue ());
Finally, we can use BeanPropertySqlParameterSource and MapSqlParameterSource transfer data.
2, call a stored procedure with SimpleJdbcCall
Let's see how to execute a stored procedure - we use SimpleJdbcCall abstract:
SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall (dataSource)
.withProcedureName ( "READ_EMPLOYEE");
public Employee getEmployeeUsingSimpleJdbcCall (int id) {
SqlParameterSource in = new MapSqlParameterSource () addValue ( "in_id", id).;
Map < String, Object> out = simpleJdbcCall.execute (in);
Employee emp = new Employee ();
emp.setFirstName ((String) out.get ( "FIRST_NAME"));
emp.setLastName ((String) out.get ( "LAST_NAME"));
return emp;
}
Sixth, batch operations
Another simple use case - the variety of operating together to achieve a batch
1, using JdbcTemplate perform basic batch operations
Use JdbcTemplate class to perform basic batch operations by batchUpdate () API:
Note BatchPreparedStatementSetter implementation is very interesting.
public int [] batchUpdateUsingJdbcTemplate (List < Employee> employees) {
return jdbcTemplate.batchUpdate ( "INSERT INTO EMPLOYEE VALUES (?,?,?,?)",
new BatchPreparedStatementSetter () {
@Override
public void setValues (PreparedStatement ps, int i) throws SQLException {
ps.setInt (1, employees.get (i) .getId ());
ps.setString (2, employees.get (i) .getFirstName ());
ps.setString (3, employees.get (i) .getLastName ());
ps.setString (4, employees.get (i) .getAddress ();
}
@Override
public int getBatchSize () {
return 50;
}
});
}
2, the use of batch operations NamedParameterJdbcTemplate
For batch operations, you can also choose to use NamedParameterJdbcTemplate of batchUpdate () API to perform.
This API is much simpler than the previous - no need to implement any additional interfaces to set the parameters, because it has an internal setter prepared statement to pass the default parameter values.
Parameter values can be passed to SqlParameterSource array by batchUpdate () method.
SqlParameterSource [] batch = SqlParameterSourceUtils.createBatch (employees.toArray ());
int [] updateCounts = namedParameterJdbcTemplate.batchUpdate (
"INSERT INTO EMPLOYEE VALUES (: id,: firstName,: lastName,: address)", batch);
return updateCounts;
Seventh, in conclusion
This article describes the Spring JDBC abstraction framework, covering the Spring JDBC module is built with a variety of features and practical examples. |
|
|
|