Spring named parameter Namedparameterjdbctemplate

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

We've been using JdbcTemplate for the spelling of SQL statements, but it's easy to confuse which value is when there are multiple placeholders in a row. Then we can make the tool name parameter.

How do I use named parameters?

1. Add support for named parameters in the XML file

Note the named parameter can only use Constructor-arg and must be assigned to DataSource

<!--the constructor for the named parameter must have a parameter without an argument -<BeanID= "Namedparameterjdbctemplate"class= "Org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">    <Constructor-argname= "DataSource"ref= "DataSource"></Constructor-arg></Bean>

Applicationcontext.xml

2. Introduction of named parameters in Java classes

ImportJava.util.HashMap;ImportJava.util.Map;Importorg.springframework.beans.factory.annotation.Autowired;ImportOrg.springframework.jdbc.core.BeanPropertyRowMapper;Importorg.springframework.jdbc.core.JdbcTemplate;ImportOrg.springframework.jdbc.core.RowMapper;ImportOrg.springframework.jdbc.core.namedparam.EmptySqlParameterSource;ImportOrg.springframework.jdbc.core.namedparam.MapSqlParameterSource;Importorg.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;ImportOrg.springframework.jdbc.core.namedparam.SqlParameterSource;Importorg.springframework.stereotype.Repository;Importcom.myth.springJDBC.exception.AddFailedException;ImportCom.myth.springJDBC.po.Employee; @Repository Public classEmployeeDAO {@AutowiredPrivateJdbcTemplate JdbcTemplate; @AutowiredPrivatenamedparameterjdbctemplate namedparameterjdbctemplate; PrivateString SQL;
This is using the traditional jdbctemplate. PublicEmployee GetEmployee (intID) {SQL= "SELECT * FROM employees WHERE id =?"; RowMapper<Employee> RowMapper =NewBeanpropertyrowmapper<employee> (Employee.class); Employee Employee=jdbctemplate.queryforobject (SQL, rowmapper,id); returnemployee; }        //introduction of an empty map emptysqlparametersource.instance     PublicInteger getcount () {SQL= "SELECT count (*) from Employees"; intresult = Namedparameterjdbctemplate.queryforobject (sql,emptysqlparametersource.instance, Integer.class); returnresult; }/* You can also use Beanpropertysqlparametersource here
* This means converting a map to an object to treat
*/     Public voidInsertemployee (Employee employee) {SQL= "INSERT into employees values (: Id,:ln,:email,:d Epartid)"; Map<string, object> parammap =NewHashmap<>(); Parammap.put ("ID", Employee.getid ()); Parammap.put ("LN", Employee.getlast_name ()); Parammap.put ("Email", Employee.getemail ()); Parammap.put ("Departid", employee.getdept_id ()); Try{namedparameterjdbctemplate.update (sql, PARAMMAP); System.out.println ("Add Success"); } Catch(Exception e) {Throw NewAddfailedexception ("Add Failed"); }}//Note that the named parameter should be consistent with the attribute value in the bean.
public void UpdateEmployee (employee employee) {
sql = "UPDATE employees set last_name =:last_name,email=:email,dept_id =:d ept_id WHERE id =: id";
Sqlparametersource Paramsource = new Beanpropertysqlparametersource (employee);
try {
Namedparameterjdbctemplate.update (SQL, Paramsource);
SYSTEM.OUT.PRINTLN ("modified successfully");
} catch (Exception e) {
System.out.println (E.tostring ());
throw new Addfailedexception ("Failed to modify");
}
}/*this will cause an error. * public void Deleteemployee (int id) {sql = "DELETE from EMPLOYEES WHERE ID =: id";        map<string, object> parammap = new hashmap<> ();        Parammap.put ("id", id);            try {namedparameterjdbctemplate.update (sql, PARAMMAP);        System.out.println ("delete succeeded");        } catch (Exception e) {throw new Addfailedexception ("delete failed"); }    }*/    //must pass in employee only incoming int ID will error     Public voidDeleteemployee (Employee employee) {SQL= "DELETE from EMPLOYEES WHERE ID =: id"; Map<string, object> parammap =NewHashmap<>(); Parammap.put ("ID", Employee.getid ()); Try{namedparameterjdbctemplate.update (sql, PARAMMAP); System.out.println ("Delete Succeeded"); } Catch(Exception e) {Throw NewAddfailedexception ("Delete failed"); }    }}

3. Then write the JUnit Test class

1  PackageCom.myth.springJDBC;2 3 4 Importorg.junit.Test;5 ImportOrg.junit.runner.RunWith;6 Importorg.springframework.beans.factory.annotation.Autowired;7 Importorg.springframework.test.context.ContextConfiguration;8 ImportOrg.springframework.test.context.junit4.SpringJUnit4ClassRunner;9 Ten ImportCom.myth.springJDBC.dao.EmployeeDao; One ImportCom.myth.springJDBC.po.Employee; A@RunWith (Springjunit4classrunner.class) -@ContextConfiguration (locations= "Classpath:applicationContext.xml") -  Public classTESTJDBC { the @Autowired -     PrivateEmployeeDAO EmployeeDAO; -      - @Test +      Public voidTestquery () { -System.out.println (Employeedao.getemployee (12)); +     } A  at @Test -      Public voidTestinsert () { -Employee Employee =NewEmployee (); -Employee.setid (12); -Employee.setlast_name ("FF"); -Employee.setemail ("[Email protected]"); inEMPLOYEE.SETDEPT_ID (4); -          to Employeedao.insertemployee (employee); +     } -      the @Test *      Public voidtestupdate () { $Employee Employee =NewEmployee ();Panax NotoginsengEmployee.setid (12); -Employee.setlast_name ("FFF"); theEmployee.setemail ("[Email protected]"); +EMPLOYEE.SETDEPT_ID (4); A          the Employeedao.updateemployee (employee); +     } -      $ @Test $      Public voidTestdelete () { -Employee Employee =NewEmployee (); -Employee.setid (12); the Employeedao.deleteemployee (employee); -     }Wuyi      the @Test -      Public voidTestgetcount () { Wu System.out.println (Employeedao.getcount ()); -     } About}

Junit

Spring named parameter Namedparameterjdbctemplate