Use JDBC API to operate database in the program of spring framework of Java

At the same time, with the work of using ordinary old JDBC with the database, it becomes cumbersome to write unnecessary code to handle exceptions, open and close database connections, etc., but spring's JDBC framework needs all the low-level details from opening connections, preparing and executing SQL statements, process exceptions, handling transactions, and finally closing connections.

Therefore, all you have to do is define the connection parameters, specify the SQL statements to be executed, and do the necessary work to obtain data from the database at each iteration.

Spring JDBC provides some methods and corresponding different classes to interact with the database. I'm going to take the classic and most popular approach, using the jdbctemplateclass framework. This is the communication and exception handling center framework class for all databases managed.

Jdbctemplate class the jdbctemplate class executes SQL queries, updates statements and stored procedure calls, and iterates in the result set and extracts the return parameter values. It also captures JDBC exceptions and converts them to generic, more informative, except at org. Org springframework. The hierarchy defined in the Dao package.

An instance of the jdbctemplate class is a configured thread. Therefore, you can configure an instance of a jdbctemplate and safely inject this shared reference into multiple Daos.

When using the jdbctemplate class, the usual approach is to configure a datasource in the spring configuration file, and then inject the dependency into the shared data source bean to the Dao class, and create the jdbctemplate or in the setter data source.

Configure the data source. Let's create the student of the database test database table together. Suppose you use the MySQL database. If you use other databases, you can change your DDL and SQL queries accordingly.

Now, we need to provide a data source to the jdbctemplate class, so it can configure itself to obtain database access. There is a piece of code in the XML file where you can configure the data source, as shown in the following figure:

Data access object (DAO) Dao means that this is a data access object commonly used for database interaction. Daos exist to provide a means to read and write data to the database. They should expose this function through the interfaces that access them through the rest of the application.

The support of data access object (DAO) in spring makes it easy to access data in a consistent manner with technologies such as JDBC, hibernate, JPA and JDO.

Execute SQL statement let's see how to perform CRUD (create, read, update and delete) operations using SQL and tables in the jdbctemplate object database.

Query an integer:

Query long integer:

Simple query using bound variables:

In the query string:

Query and return an object:

Query and return multiple objects:

Insert a row into the table:

To delete a row from a table:

You can use execute (...) to execute DDL statements Method to execute any SQL statement or DDL statement. The following is an example of creating a table using the create statement:

The class of SQL stored procedure simplejdbccall can be used to call stored procedures with in and out parameters. You can use this method while working with any favorite Apache Derby, DB2, MySQL and Microsoft SQL server, Oracle and Sybase RDBMS.

Secondly, consider the following MySQL stored procedure, which requires the return of student ID and the name and age of the student corresponding to the out parameter. Therefore, let's create the stored procedure in the test database from the MySQL command prompt:

Now let's write a spring JDBC application that will perform simple creation and reading operations on our student desk. To create a spring application: the following is the data access object interface file studentdao Java content:

The following is student Contents of java file:

The following is studentmapper Contents of java file:

The following is the implementation class file studentjdbc template Java defines the Dao interface studentdao:

A few words about the above program: when you write the execution code of the call, you need to create an sqlparametersource containing the in parameter. It is important to provide the name of the input value that matches the parameter name declared in the stored procedure. The execute method receives the passed in parameters and returns a map containing the name of any column specified in the stored procedure and the typed parameters. Now let's modify the main application file mainapp Java, which is as follows:

The following is the configuration file beans XML file:

After creating the source code and bean configuration file, let's run the application. If all goes well, this will print the following information: