liver! Spring JDBC persistence layer framework "family bucket" tutorial

Contents

1. What is JdbcTemplate?

II. JdbcTemplate frame construction

(1), directly configure the data source in sp _p3sp _span_span2, import external span_

3. Configure the JdbcTemplate object

4. Use the JdbcTemplate with named parameters

3. Use the SqlParameterSource object to pass in the value _span3 _span3 _span3 _span5, and automatically assemble the value _span3 _span_span_span_span3.

written in front

Hello, hello, I am gray little ape ,A programmer who can write bugs!

**Use persistence to create technology, tap the future with your fingertips! ** I hope that every time we hit the keyboard, we can make our life smarter and the world more interesting!

When using Spring for business logic layer processing, have you ever thought about whether such a powerful Spring framework has more convenient operations for database-related business processing? To what extent can the Spring framework optimize the operation of traditional JDBC databases?

Today I will explore with you a lightweight framework for JDBC database operations — JdbcTemplate _span3 span5span _strongspan62 span3 span3 _strongspan. Teach you an article to master the core of the Spring JDBC framework.

  < Bean  id="dataSource">      Copy Code  

  jdbc.user=rootjdbc.password=ADMINjdbc.jdbcurl=jdbc:mysql://localhost:3306/jdbc_templatejdbc.driverClass=com.mysql.jdbc.Driver  

     The class here represents the file under the classpath.After    
 
   is configured in the same way to configure the data source under the label in the container, but now the value is assigned using "     ${} _spanstrong span3  _strong3. data. The code is as follows:   
 
      <>Copy code  
 
 3. Configure JdbcTemplate object 
 
   After we configure the data source,Just configure the JdbcTemplate object. Since the JdbcTemplate object is just a JDBC operation template, it needs to introduce an external data source to be operated on. The specific operation is to assign a data source to the dataSource attribute of the JdbcTemplate class in the IOC.   
 
   code is as follows:   
 
   name="dataSource" ref="dataSource"> JdbcTemplate can be used normally for related operations in the database. Let’s first write a test statement to test whether the database connection is normal under normal connection and JdbcTemplate connection:   
 
  public class JdbcTest {ApplicationContext = new ClassPathXmlApplicationContext("applicationContext.xml"); JdbcTemplate jdbcTemplate = context.getBean(JdbcTemplate.class); // Get the database connection through ordinary methods @Test public void test() throws SQLException {System.out.println("jdbc_template execution "); DataSource bean = context.getBean(DataSource.class); Connection connection = bean.getConnection(); System.out.println("Common method to get database connection:" + connection);} /** * Via jdbcTemp Late to get database connection * Experiment: Test data source * */ @Test public void test01() {System.out.println("jdbcTemplate to get database connection:" + jdbcTemplate); }} The following information appears when running,It means that the connection is normal:   
 
  
 
 
 
   ​   
 
   After confirming that the database is connected to the core part of the Jdbc, it is now the blackboard! To the point! ! !   
 
  
 
 
 
   ​   
 
 Three, the persistence layer operation detailed explanation 
 
 span3dspan will be changed by the following special operations to check the data of the bc The table "employee" will introduce their specific use:   
 
  
 
 
 
   ​   
 
. The deletion and modification operation is done using the same method, namely:   
 
 JdbcTemplate.update(String, Object...) 
 
   This method has two most commonly used parameters:   span5span  
 p7p first span The parameter String is passed into the SQL statement that needs to be executed,  
 
   The second parameter Object...pass in the parameters that need to be carried in the sql statement. Using object... means that there may be more than one parameter behind. This method will have a return value of type int, indicating how many rows of data have been modified. Let me show you through an example;   
 
 Example: Update the salary field of the record with emp_id=5 to 1300.00 
 
   First, we need to write the corresponding SQL statement. The position of the parameter that needs to be passed in the statement is indicated by "?", and then the update method is called to implement the modification operation, and returns the number of modified rows:   
 
  / ** Modify the data in the data table of the database* Update the salary field of the record with emp_id=5 to 1300.00*/// @Testpublic void test02() {String sql = "UPDATE employee SET salary=? WHERE emp_id=?"; int update = jdbcTemplate.update(sql, 1300.00, 5); System.out.println("Update successful!" + update);} The same way can be done.   
 
 2. Batch addition, deletion and modification operations 
 
   The above is the addition, deletion and modification operations for ordinary single data.But what if there is a large amount of data to perform the same operation? Isn't it too troublesome to come one by one? Therefore, in response to this situation, JdbcTemplate also deliberately provides batch addition, deletion and modification methods to facilitate our operations on large amounts of data. The specific use is like this.   
 
   is implemented by calling the following functions:   
 
 JdbcTemplate.batchUpdate(String, List) 
 
 This method will return an array of int type every time it is executed. The number of rows modified by the statement. 
 
   The String still represents the SQL statement to be executed,   
 
   But Object[] encapsulates the parameters required for each execution of the SQL statement, and the List collection encapsulates the SQL statement multiple executions All the parameters.   
 
   We use the following example to verify the operation of this method:   
 
 Example: Insert data in batches into the employee table 
 
  , and then write the statements that need spansql . Write the parameters into the list collection, and then pass the sql statement and the list collection to the batchUpdate() method.   
 
  /** * Insert data in batches * */ @Test public void test03() {String sql = "INSERT INTO employee(emp_name,salary) VALUES(?,?)"; List batchArgs = new ArrayList(); batchArgs.add(new Object[]{"张三","999"}); batchArgs.add(new Object[]{"李四","1999"}) ; batchArgs.add(new Object[]{"王五","2999"}); int[] batchUpdate = jdbcTemplate.batchUpdate(sql, batchArgs); for (int i: batchUpdate) {System.out.println(i );}} Copy code  
 
 3. Query a single row of data 
 
   Above we learned how to add, delete, and modify operations in jdbcTemplate,So how can the four CRUD brothers ignore such an important operation as lookup? It's not coming! ! !   
 
  
 
 
 
   ​   
 
   In jdbcTemplate, why is it not the same operation as the other three operations?   
 
   **The reason is actually very simple. **It is not that addition, deletion and modification operations will modify the data table and return the number of modified rows of int type, while query operations will not modify the data table, but return other types The result of the query!   
 
   First, let's take a look at how to query a single row of data. The function used to query a single row of data in jdbcTemplate is:   
 
 JdbcTemplate.queryForObject(String, RowMapper, Object...) 
 
   In the parameters of this method, String also represents the SQL statement to be searched,   
 
   **But there is a pit to note: **What is the parameter RowMapper passed in the middle? In fact, the value here is to pass the type of the bean object that needs to be returned, **but in the real use, we do not map the bean object to be returned through RowMapper, but through its subclass Bea*** *nPropertyRowMapper,** Their inheritance concerns are as follows:   
 
  
 
 
 
   ​   
 
 If the object returned by the BeanPropertyRowMapper is not successfully found and mapped, the returned object will be mapped. Report an error when it arrives. 
 
   The third parameter object... still represents the incoming query parameters.   
 
   Look at such an example below, you will understand.   
 
 Example: Query the database record with emp_id=5, encapsulate it as a Java object and return it. 
 
  /** * Query a single data in the database * Experiment 4: Query the database record with emp_id=5, and encapsulate it as a Java object to return * The field in the created javabean must be the same as the field name in the data table, otherwise it will Need to perform mapping * Query a single data using queryForObject, but in the middle need to use BeanPropertyRowMapper to map the bean objects that need to be generated * An error will be reported when it is not found * * */ @Test public void test04() {String sql = "SELECT * FROM employee WHERE emp_id=?"; Employee employee = null; try {employee = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper(Employee.class),5);} catch (Exception e) {// TODO: handle exception} System .out.println(employee); }Copy code  
 
 4. Query multiple rows of data 
 
   is different from querying single row data.The method to query multiple rows of data is:   
 
 JdbcTemplate.query(String, RowMapper, Object...) 
 
   But the parameters passed are the same, the only difference is that the method returns one Array list, which contains every piece of data found.   
 
   Such as the following example:   
 
 Example: Query the database records of salary>4000, and encapsulate it as a List collection and return. 
 
  /** * Query multiple data in the database * Experiment 5: Query the database records of salary>4000, encapsulate it as a List collection and return * */ @Test public void test05() {String sql = "SELECT * FROM employee WHERE salary>?"; List employees = jdbcTemplate.query(sql, new BeanPropertyRowMapper(Employee.class),4000); for (Employee employee: employees) {System.out.println(employee);} }Copy code  
 
 5. Query a single specified value 
 
   Now we know how to query a single piece of data,I also know how to query multiple pieces of data, but all these data returns are rows of data. What if we only want to get the data of a certain row?   
 
   That's easy. jdbcTemplate has a special method to return a single value that needs to be queried.   
 
 JdbcTemplate.queryForObject(String, Class, Object...) 
 
   One of the return values ​​of this method is class, which indicates the type of data to be returned, such as int type or double type. At the same time, the method returns the queried value.   
 
   as the following example:   
 
 Example: Query the largest salary in the employee table. 
 
   This method obviously returns a specific value, and it still has no parameters, so we don't need to pass the following object...type parameters when passing parameters.   
 
  /** * Query the data in the data table, but only returns a value * Experiment 6: Query the maximum salary * */ @Test public void test06() {String sql = "SELECT MAX(salary) FROM employee "; Double quDouble = jdbcTemplate.queryForObject(sql, Double.class); System.out.println(quDouble);}But there is another way to operate jdbcTemplate, which is to express the "?" in the sql statement with specific parameters. Next we will introduce this way to execute SQL statements.   
 
 Four. JdbcTemplate with named parameters 
 
   The operation mode of this JdbcTemplate to be introduced next is different from the above, here a named parameter is used to indicate the parameters that need to be passed in the SQL statement , So what is a named parameter?   
 
   **Named parameter: **refers to a parameter with a name, the parameter is no longer a placeholder, but a variable name   
 
   syntax format: ":parameter name"  After 
 uses the named parameter, spring will automatically find the parameter with the corresponding name from the incoming parameters, and assign its value to the sql statement. 
 
   Spring has a jdbcTemplate that supports the function of named parameters, that is, the NamedParameterJdbcTemplate class. In Spring, SQL statements with named parameters can be used through objects of the NamedParameterJdbcTemplate class.   
 
 1. Declare a named parameter class 
 
  . The way to use NamedParameterJdbcTemplate is similar to that of ordinary JdbcTemplate, and it needs to be declared in the ioc, as shown below:  _codespan5span  
   JdbcTemplate supported by named parameters --> Copy code  
 
 2. Ordinary use of named parameters 
 
   Let’s take the insert statement as an example,To demonstrate the use of named parameters,   
 
 The traditional SQL statement looks like this: 
 
  INSERT INTO employee(emp_name,salary) values(?,?) Like this;  
  INSERT INTO employee(emp_name,salary) values(:emp_name,:salary) An employee record, and the parameter value is passed in in the form of Map.  
  /** * Experiment 7: Use a SQL statement with named parameters to insert an employee record, and pass in the parameter value in the form of a Map * Placeholder to check parameters: The order of? must not be wrong, when passing parameters Be sure to pay attention * */ @Test public void test07() {String sql = "INSERT INTO employee(emp_name,salary) values(:emp_name,:salary)"; Map paramMap = new  HashMap  (); paramMap.put("emp_name", "赵六"); paramMap.put("salary", 998.12); int updateNum = jdbcTemplate2.update(sql, paramMap); System.out.println(updateNum ); }Copy code  
 
   ** Here is one thing to note: ** Whether you use ordinary sql statements, or use sql statements with named parameters.The order of the incoming parameters must be consistent with the order of the parameters in the sql statement, otherwise there will be a parameter call error, this must be paid attention to!   
 
 3. Passing in the value through the SqlParameterSource object 
 
   Passing in the value through the SqlParameterSource object is actually the need to pass in the parameter in the form of javabean, but there are points that need attention.   
 
   Note: When using sqlParmeterSource to fill the data in the database, be sure to pay attention to the parameter name after values ​​and the parameter name in the bean corresponding to   
 
, otherwise the following error will be reported: 1 p3span3h11 No value supplied for the SQL parameter'emp_Name': Invalid property'emp_Name' of bean class [com.spring.beans.Employee]: Bean property'emp_Name' is not readable or has an invalid getter method:   
 
  
 
 
 
     
 
   The following is an example to illustrate the passing of parameters through the SqlParameterSource object.  
 
 Example: Use a SQL statement with named parameters to insert an employee record, and pass in the parameters through the SqlParameterSource object. 
 
  /** * Experiment 8: Repeat experiment 7 and pass in parameter values ​​in the form of SqlParameterSource* */ @Test public void test08() {String sql = "INSERT INTO employee(emp_name,salary) values(:emp_name,: salary)"; Employee employee = new Employee(); employee.setEmp_name("吴九"); employee.setSalary(997.7); int updateNum = jdbcTemplate2.update(sql, new BeanPropertySqlParameterSource(employee)); System.out.println (updateNum); }Copy code  
 
 5. Automatically assemble JdbcTemplate and realize Dao 
 
   **Because the JdbcTemplate class is thread-safe,** it is possible to declare a single instance of it in the IOC container and add this The instance is injected into all Dao instances, and the JdbcTemplate is automatically assembled in the Dao class. It also implements the addition, deletion, modification, and check method. The automatic assembly of jdbcTemplate can reduce the code operation in Dao, and realize the addition, deletion, modification, and check operation more easily.   
 
 Use this method to automatically assemble JdbcTemplate and realize Dao steps. I have summarized it for everyone: 
 
 
   Establish dao class   
 li147_spanspan  147 span_ _span  147 span_span _span  147 span_ 152 Scan it to automatically assemble    
   Obtain the dao class   
 
   from the IOC container. The following method is used to verify the database operations that respond to it.  
 
 Example: Create BookDao, automatically assemble the JdbcTemplate object, and implement an add operation. 
 
   In the Dao class, we use the @Autowired annotation to automatically assemble the jdbcTemplate, and implement a data addition method:   
 
  @Repositorypublic class EmployeeDao {// automatically inject jdbcTemplate; * Save data to the data table * */ public int saveEmployee(Employee employee) {String sql = "insert into employee(emp_name,salary) values(?,?)"; return jdbcTemplate.update(sql, employee.getEmp_name(), employee.getSalary());} }Copy code  
 
   Use the test method to test:   
 
  /** * Experiment 9: Create BookDao, automatically assemble JdbcTemplate object * */ @Test public void test09 () {Employee employee = new Employee(); employee.setEmp_name("王八"); employee.setSalary(888.7); int saveEmployeeNum = employeeDao.saveEmployee(employee); System.out.println(saveEmployeeNum); }Copy code_ code282code 
 
   This completes the work of automatically assembling JdbcTemplate and realizing Dao,This way avoids repeated creation of jdbcTemplate and reduces the amount of code.   
 
 6. Write at the end 
 
   Ding ding! At this point, all the operations and usage of Spring's JdbcTemplate framework have been explained to you,   
 
  , which includes building from ordinary JdbcTemplate, to implementing simple CURD, and then to complex named parameters. I hope that through this article, friends can master the tutorial of using JdbcTemplate. At the same time, there are some things that you don’t understand or don’t understand during the learning process. Welcome to leave a message and let us study together!   
 
 No matter how advanced the technology is, you need one-click one-key tapping. Strive! To every "creator" on the road to Java! 
 
 I am the little gray ape, see you in the next issue! 
 
  
 
 
 
 
 
 
.