Using JDBC in spring

database anomaly system in spring

With JDBC (without spring), we need to force the capture of SqlException, or we can't use JDBC to handle anything. SqlException indicates that there was a problem trying to access the database, but the exception did not tell us what went wrong and how to handle it. Possible SqlException scenarios are:

• Application cannot connect to database
• There is a problem with the SQL query
• Table in query, column does not exist
• Attempt to insert or update the data against the constraint of the offending database

There are other database related exceptions, but the real throw sqlexception, we have no way to accurately locate, only by experience and speculation to deal with. And throwing sqlexception means that there is a fatal error and there is no way to proceed with the process.

Some persistence layer frameworks have relatively rich anomaly systems, such as hibernate with about more than 20 exceptions, which correspond to specific data access problems. This allows you to write catch blocks of code for specific exceptions. However, this hibernate exception corresponds to the corresponding framework, if we want a particular persistence framework to be independent of the data access layer. If we throw these hibernate exceptions, then our use of hibernate will penetrate the other layers. If you do not do this, you will need to capture these specific exceptions and then convert them to platform-independent exceptions for processing.

On the one hand, the exception of JDBC is too simple, and Hibernate's anomaly system is related to its platform. Spring provides multiple data access exceptions for this problem, and spring's exception system is not associated with a particular durable method, so we don't have to care about the persistence scheme we choose. This helps us isolate the specific persistence technology chosen from the data access layer. And spring's data access exception is inherited from DataAccessException, which is a non-check exception, that is, it is not necessary to capture spriing these data access exceptions (if you want to capture is also perfectly possible).

first step to configure the data source

Spring's template class handles the state of the data access Layer-transaction control, management control, and handling exceptions. At the same time, application-related data access-statements, binding parameters, and truth datasets need to be handled by ourselves. Spring provides optional templates for different persistence platforms, and if you use JDBC to select JdbcTemplate, you can use Hibernatetemplcate or jpatemplcate if you use an object-relational mapping framework.

But the first thing to note is that the persistence features supported by spring need to rely on the data source. So don't forget to configure the data source and take it as a first step (it is recommended to use the database connection pool or jndi, if it is possible to use spring's profile is also excellent).

    @Bean    public  DataSource DataSource () {        new  basicdatasource ();        Datasource.seturl ("Jdbc:mysql:///crud");        Datasource.setusername ("root");        Datasource.setpassword ("root");        Datasource.setdriverclassname ("Com.mysql.jdbc.Driver");         return dataSource;    }

The second step is to use JDBC in spring

JDBC is built on top of SQL, and SQL itself is the database access language, and in addition, using JDBC directly can better tune the database compared to other technologies, and JDBC allows all the features of the database to be used, which is not encouraged or even forbidden by other frameworks. In contrast to other persistent layer frameworks, JDBC allows us to process data at a lower level, and we can control how the application reads and manages data, which is a fine-grained way of accessing data.

using the JDBC template

Spring's JDBC template takes care of resource management and exception handling, simplifying JDBC code, abstracting boilerplate code for data access into a template class, and Spring provides three JDBC template classes for JDBC:

• JdbcTemplate: The most basic spring JDBC template class, supported based on index parameters (?) The query.
• Namedparameterjdbctemplate: Use this template class to execute a query using named arguments to bind to SQL instead of using index parameters.
• Deprecated after simplejdbctemplate:spring3.1

Using namedparameterjdbctemplate only when we need to use named parameters is the best solution for most JDBC tasks using JdbcTemplate. For JdbcTemplate to work properly, just configure DataSource for it:

    @Bean    public  jdbctemplate jdbctemplate (DataSource DataSource) {        return  New  JdbcTemplate (DataSource);    }

Then we can assemble the jdbctemplate into the repository.

@Repository  Public class jdbcrepository {        @Autowired    private jdbcoperations jdbcoperations;

The JdbcOperations is used here, and the JdbcOperations interface defines the methods implemented by the JdbcTemplate template class, using interfaces rather than specific JDBC template classes to ensure loose coupling. Using Spring's @autowired here, you can actually use @inject, but the latter is the annotation in the Java injection specification.

After using the template class, all boilerplate code is hidden into the JDBC template class, and the SqlException is not processed, and internally, JdbcTemplate will capture sqlexception that might be thrown, and convert it to a generic data access exception in spring and then re-throw it.

reading data using JdbcTemplate

There are many query ... in jdbctemplate ... () method is used to read the data, but for operations that need to encapsulate the returned result as an object, some special handling is required. This object needs to implement the RowMapper interface, and for each row of data returned by the query, JdbcTemplate will call RowMapper's Maprow method and pass in a resultset and an integer containing the line number.

    Private Static Final classSpitterrowmapperImplementsRowmapper<spitter> {         PublicSpitter Maprow (ResultSet RS,intRowNum)throwsSQLException {//This code is excerpted from the book            Longid = rs.getlong ("id"); String username= Rs.getstring ("username"); String Password= rs.getstring ("Password"); String FullName= Rs.getstring ("FullName"); String Email= rs.getstring ("email"); BooleanUpdatebyemail = Rs.getboolean ("Updatebyemail"); return Newspitter (ID, username, password, fullName, email, updatebyemail); }            }        

Because there is only one Maprow method in the RowMapper interface, it fully conforms to the standard of the functional interface. This means that using JAVA8 to develop an application can use lambda expressions instead of using specific implementation classes. Or use the Java8 method reference to define the mapping rule in a separate method:

     Public Dept FindOne (Integer deptid) {        returnthis:: Mapdept,deptid);    }         Private Dept mapdept (ResultSet rs,int row)throws  SQLException {        return New Dept (Rs.getint ("dept_id"), Rs.getstring ("Dept_name"));    

However, regardless of whether you use a lambda expression or a method reference, you need to ensure that you accept the same parameters as Maprow.

using named parameters

When using index parameters, you need to be aware of the order of the parameters and whether you need to ensure the correct order when passing values to the bustling. If you change the order of the parameters when you modify the SQL, then we also need to modify the order of the parameter values. When we use named arguments, named arguments can give each parameter in SQL a definite name, referencing the argument by name when the binding is worth it.

Private Static Final String sql_insert_spitter= "INSERT into Spitter (username,password,fullname)" + "VALUES (: Username,:p assword,:fullname )";

With named parameter queries, it is not important to bind the order of merit, and we do not need to modify the bound code if the SQL changes cause the parameter order to be inconsistent with the previous.

Namedparameterjdbctemplate is a special JDBC template class that uses named parameters. Namedparameterjdbctemplate usage is consistent with JdbcTemplate:

    @Bean    public  jdbctemplate jdbctemplate (DataSource DataSource) {        return  New  namedparameterjdbctemplate (DataSource);    }

Then, start using it in repository:

 Public void Addspitter (Spitter spitter) {      Map<String,Object> map=new hashmap<string,object> ();      Map.put ("username", spitter.getusername ());       Map.put ("password", Spitter.getpassword ());          Map.put ("username", spitter.getfullname ());                Jdbcoperations.update (sql_insert_spitter, map);}

Because named parameters need to be bound by JAVA.UTIL.MAP, the parameters need to be plugged into the map.

Using Transactions

Since spring is configured in the Javaconfig way, it is also necessary to declare the transaction in Java code, as well as the way XML is used, to review how the transaction is configured in XML (using transaction annotations, Rather than AOP configuration transactions):

1. Platform-related transaction manager
2. Turn on transaction annotation driver: <tx:annotation-driven transaction-manager= "Transactionmanage"/>
3. Use @transactional on methods that require transactions

Java code is also done in this way, first of all, we configure the transaction manager, JDBC and MyBatis are using the data source transaction management (Datasourcetransactionmanager), Hibernate and JPA use separate transaction managers (Hibernatetransactionmanager, Jpatransactionmanager). Because using JDBC here, we use Datasourcetransactionmanager.

// registering the transaction manager in the container     @Bean    publicthrows  exception{        returnnew Datasourcetransactionmanager (DataSource ());    }

The data source needs to be handed to the transaction manager so that the manager can control commit or rollback

Next, enable @EnableTransactionManagement on the configuration class to open the transaction annotation driver

@EnableTransactionManagement @componentscan ("Cn.lynu") @Configuration  Public class Txconfig {

Finally, use the @transactional annotation on the method that requires the transaction, and the usage method is no different from the configuration of the XML.

Using JDBC in spring