Spring Frame Notes (25)--namedparameterjdbctemplate and named parameters

In the classic JDBC usage, is the SQL parameter used as a placeholder? and is subject to location restrictions. The problem with locating parameters is that once the order of the parameters changes, the parameter bindings must be changed.

In the Spring JDBC framework, another option for binding SQL parameters is to make the appliance name parameter (named parameter).

So what is a named parameter?

Named parameter: SQL is specified by name (beginning with a colon) instead of by location. Named parameters are easier to maintain and improve readability. Named parameters are replaced with placeholders by the framework class at run time

Named parameters are supported only in Namedparameterjdbctemplate.

When using a named parameter in an SQL statement, you can provide a parameter value in a MAP with the parameter name key

You can also use the Sqlparametersource parameter

You can provide an array of Map or Sqlparametersource when you batch update

Now, on the basis of an example from the previous blog post, we continue to write code:

We add the bean to the named class object after Applicationcontext.xml:

Configuration file:

<?xml version= "1.0"  encoding= "UTF-8"? ><beans xmlns= "http://www.springframework.org/ Schema/beans "xmlns:xsi=" http://www.w3.org/2001/XMLSchema-instance " xmlns:aop="/HTTP/ Www.springframework.org/schema/aop "xmlns:context=" Http://www.springframework.org/schema/context "xsi: Schemalocation= "http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/ Spring-aop-4.1.xsdhttp://www.springframework.org/schema/beans http://www.springframework.org/schema/beans /spring-beans.xsdhttp://www.springframework.org/schema/context http://www.springframework.org/schema/ Context/spring-context-4.1.xsd "><!--  Auto-scan package  --><context:component-scan base-package = "Com.happBKs.spring.jdbcSpring" ></context:component-scan><!--  Import resource file  --><context: property-placeholder location= "Classpath:db.properties"  /><!--  Configure C3P0 data sources  -->< Bean id= "DataSource"  class= "com.mchange.v2.C3p0.combopooleddatasource "><property name=" user " value=" ${jdbc.user} "></property>< Property name= "Password"  value= "${jdbc.password}" ></property><property name= " Jdbcurl " value=" ${jdbc.jdbcurl} "></property><property name=" DriverClass " value=" ${ Jdbc.driverclass} "></property><property name=" Initialpoolsize " value=" ${ Jdbc.initpoolsize} "></property><property name=" Maxpoolsize " value=" ${jdbc.maxPoolSize} " ></property></bean><!--  Configure the JDBC template class  --><bean id= "JdbcTemplate"  class = "Org.springframework.jdbc.core.JdbcTemplate" ><property name= "DataSource"  ref= "DataSource" > </property></bean><!--  Configuration  namedparameterjdbctemplate, the object can make the appliance name parameter. However, it does not have a parameterless constructor, so it must be constructed with a constructor parameter, which specifies the C3P0 data source--><bean id= "Namedparameterjdbctemplate" class= " Org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate "><constructor-arg ref= "DataSource" ></constructor-arg></bean></beans> 

We no longer use the Employeebean class we mentioned last time, because we already know that spring JDBC does not provide cascading mappings for class properties such as the ORM framework like hibernate, so we changed the attribute department to DeptID.

package com.happbks.spring.jdbcspring;public class employeebean2 {integer id; string lastname; String email;integer deptid;public integer getid ()  {return id;} Public void setid (Integer id)  {this.id = id;} Public string getlastname ()  {return lastname;} Public void setlastname (String lastname)  {this.lastname = lastname;} Public string getemail ()  {return email;} Public void setemail (String email)  {this.email = email;} Public integer getdeptid ()  {return deptid;} Public void setdeptid (Integer deptid)  {this.deptid = deptid;} Public employeebean2 (integer id, string lastname, string email,integer  DeptID)  {super ();this.id = id;this.lastname = lastname;this.email = email; This.deptid = deptid;} Public employeebean2 ()  {super ();// todo auto-generated constructor stub}@ Overridepublic string tostring ()  {return  "employeebean2 [id="  + id +   ",  lastname="  + lastName+  ",  email="  + email +  ",  Deptid= " + deptId + "] ";}}

We then use the named parameter to complete the various update operations we mentioned earlier:

For example, before we insert a record, the notation is:

In this notation, the parameters of the assignment are not given a specific name, only the placeholder to complete the placeholder, the order of the assignment parameters corresponding to the corresponding parameters. Now we can use Org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate to help us solve this problem:

But there are two ways to do this, let's start with the most general one:

/* * Can name the parameter: ln,:email,:d Eptid * Advantages: If there are multiple parameters, do not have to dwell on the parameter position order, directly corresponding to the parameter name, easy to maintain * disadvantage: more trouble * * @Testpublic void Testnamedparameterjdbctemplate () {///before the use of the name parameter://string sql= "Insert employee (LAST_NAME,EMAIL,DEPT_ID) VALUES (?,? ,?)";/ /We give the parameter assignment must depend on the order of the?//use of named parameters: String sql= "Insert employee (LAST_NAME,EMAIL,DEPT_ID) VALUES (: Ln,:email,:d eptid)"; Map<string,object> parammap=new hashmap<string, object> ();p arammap.put ("ln", "Super Invincible Galaxy Strongest temp"); Parammap.put ("Email", "[email protected]");p arammap.put ("DeptID", 4); Namedparameterjdbctemplate.update (SQL, PARAMMAP);}

Here, the assignment parameter in the SQL statement is given in the form of ":", which is the named parameter. Then we can go through a map object, the map key is our named parameter, and value is the value of the parameter, and then through the Namedparameterjdbctemplate class object method update to complete the previously censored operation.

Operation Result:

However, this approach still has a bit of a problem, we need to specify the parameters in the Map object individually.

At this point, you may not be able to sigh, or the ORM Framework is good, the generic attributes can be automatically mapped with the database table field. Here Spring JDBC also takes a similar approach when assigning a named parameter.

Next, let me introduce the second method of the named parameter:

We need to define the named parameter as the name of the class's property name, and then we can create an object of the corresponding class and call the set method of the corresponding property, and then we call another overloaded method of update:

Operation Result:

Spring Frame Notes (25)--namedparameterjdbctemplate and named parameters