In the Spring JDBC framework, another option for binding SQL parameters is named parameter and jdbcparameter.

In the Spring JDBC framework, another option for binding SQL parameters is named parameter and jdbcparameter.

 

Use spring jdbcTemplate ----- use the named Parameter

In JDBC usage, is the SQL parameter a placeholder? And is subject to location restrictions. The problem with locating parameters is that once the parameter location changes, the parameter binding must be changed. In Spring JDBC, another option for binding SQL parameters is to use a named parameter, which is bound by name rather than location.

 

What is a named parameter?

Named parameters: SQL statements are specified by name (starting with a colon) instead of by position. The named parameters are easier to maintain and readability is improved. The named parameters are replaced by placeholders when the framework class is running.

The named parameter is only supported in NamedParameterJdbcTemplate. (SImpleJdbcTemplate is out of date)

NamedParameterJdbcTemplate contains a JdbcTemplate. Therefore, all the tasks that JdbcTemplate can do are NamedParameterJdbcTemplate. Compared with JdbcTemplate, NamedParameterJdbcTemplate mainly supports parameter naming.

 

How to configure it?

Configuration in applicationContext. xml

<! -- Configure the jdbc template class --> <bean id = "jdbcTemplate" class = "org. springframework. jdbc. core. jdbcTemplate "> <property name =" dataSource "ref =" dataSource "> </property> </bean> <! -- Configure NamedParameterJdbcTemplate. This object can use a named parameter. However, it does not have a parameter constructor, so you must specify the constructor for it. Here, the c3p0 data source is specified --> <bean id = "namedParameterJdbcTemplate" class = "org. springframework. jdbc. core. namedparam. namedParameterJdbcTemplate "> <constructor-arg ref =" dataSource "> </constructor-arg> </bean>

 

 

Instance:

Public class BaseService {// The JdbcTemplate object is initialized as the constructor parameter @ Autowired protected NamedParameterJdbcTemplate namedParameterJdbcTemplate; /*** Data Query *** @ param SQL * @ param object * @ return */@ SuppressWarnings ("rawtypes") public List <?> Query (String SQL, Object object) {// BeanPropertySqlParameterSource encapsulates a JavaBean object and uses the JavaBean Object attribute to determine the value of the named parameter SqlParameterSource source = new BeanPropertySqlParameterSource (object ); @ SuppressWarnings ("unchecked") // BeanPropertyRowMapper automatically maps a row of data to the instance of the specified class. It first instantiates the class and then matches the class by name, map to attribute to List <?> List = namedParameterJdbcTemplate. query (SQL, source, new BeanPropertyRowMapper (object. getClass (); return list ;} /*** add, delete, and modify data ** @ param SQL * @ param object * @ return */@ SuppressWarnings ("unchecked") public int excute (String SQL, object object) {if (object! = Null) {// judgment type if (object instanceof Map) {return namedParameterJdbcTemplate. update (SQL, (Map <String,?>) Object);} else {BeanPropertySqlParameterSource source = new BeanPropertySqlParameterSource (object); // return the object return namedParameterJdbcTemplate. update (SQL, source) ;}} else {return namedParameterJdbcTemplate. getJdbcOperations (). update (SQL) ;}/ *** Number of query records ** @ param SQL * @ param object * @ return */public int queryCount (String SQL, Object object) {BeanPropertySqlParameterSource source = null; If (object! = Null) {source = new BeanPropertySqlParameterSource (object);} return namedParameterJdbcTemplate. queryForObject (SQL, source, Integer. class );}
}

 

 

The NamedParameterJdbcTemplate class is based on the JdbcTemplate class and is encapsulated to support the named parameter feature.

 

NamedParameterJdbcTemplate mainly provides the following three methods: execute method, query and queryForXXX method, update and batchUpdate method.

 

1) NamedParameterJdbcTemplate Initialization: DataSource or JdbcTemplate object can be used as the constructor parameter initialization;

2) insert into test (name) values (: name ):": Name" is the name parameter;

3) update (insertSql, paramMap ):ParamMap is a Map type, which contains key-value pairs with a key of "name" and a value of "name5", that is, the data set for the name parameter;

4) query (selectSql, paramMap, new RowCallbackHandler ()......) :Similar to the one described in JdbcTemplate, the only difference is that paramMap needs to be input to set values for the named parameters;

5) update (deleteSql, paramSource ):It is similar to "update (insertSql, paramMap)", but uses the SqlParameterSource parameter to set a value for the named parameter. MapSqlParameterSource is used here, which is a simple encapsulation of java. util. Map.

 

The NamedParameterJdbcTemplate class provides two methods to set the value of a named parameter: java. util. Map and SqlParameterSource:

1) java. util. Map:The Map key data is used for naming parameters, while the Map value data is used for setting values;

2) SqlParameterSource:You can use SqlParameterSource implementation to set values for the named parameters. By default, MapSqlParameterSource and BeanPropertySqlParameterSource are implemented. MapSqlParameterSource implementation is very simple, but java. util. Map is encapsulated. BeanPropertySqlParameterSource encapsulates

The value of the name parameter is determined by the attributes of the JavaBean object.

 

/*** Implement the user service interface */@ Servicepublic class IUserServiceImpl extends BaseService implements IUserService {public final static org. slf4j. logger logger = org. slf4j. loggerFactory. getLogger (IUserServiceImpl. class);/*** query User */@ Override public Object queryUser (String SQL, Object object) {user User = new user (); SQL = "select suser as username, spassword as password, tcreatetime as createtime, cisenabled as enabled, cauthorize as authorize from sys_user ";
// String sqls = "select suser as username, spassword as password, tcreatetime as createtime, cisenabled as enabled, cauthorize as authorize from sys_user where suser like '%: username % '"; // Replace the SQL statement condition // sqls = sqls. replace (": username", user. getUsername (); @ SuppressWarnings ("unchecked") List <User> list = (List <User>) query (SQL, user); return list ;} /*** Add User */@ Override public MsgBean addUser (Use R user) {MsgBean msg = new MsgBean (); try {String SQL = "insert into sys_user (suser, spassword, tcreatetime, cisenabled, cauthorize) values (: username,: password,: createtime,: enabled,: authorize) "; // the automatic acquisition time is used to add a new user. // user. setCreatetime (DateUtil. getCurDate (null); excute (SQL, user); msg. setFlag (true); msg. setText ("OK");} catch (Exception e) {logger. error ("cause of error... "+ E. getMessage ();} return msg;}/*** modify User */@ Override public MsgBean updateUser (user User user) {MsgBean msg = new MsgBean (); try {String SQL = "update sys_user set spassword =: password, tcreatetime =: createtime, cisenabled =: enabled, cauthorize =: authorize where suser =: username"; excute (SQL, user); msg. setFlag (true); msg. setText ("OK");} catch (Exception e) {logger. error ("cause of error... "+ E. getMessage ();} return msg;}/*** delete user */@ Override public MsgBean deleteUser (String username) {MsgBean msg = new MsgBean (); try {String SQL = "delete from sys_user where suser = ': username'"; if (username! = Null & username. length ()! = 0) {SQL = SQL. replace (": username", username);} else {msg. setFlag (false); msg. setText ("unknown reason, failed to delete"); return msg;} excute (SQL, null); msg. setFlag (true); msg. setText ("OK");} catch (Exception e) {logger. error ("cause of error... "+ E. getMessage ();} return msg ;}
}

 

The User is a User entity class. The MsgBean tool entity class contains two parameters: flag and text, which output information.

 

/*** User control layer */@ Controllerpublic class UserController {private static final Log logger = LogFactory. getLog (UserController. class); @ Autowired private IUserService userService;/*** user query ** @ param request * @ param response * @ return */@ ResponseBody @ RequestMapping ("/queryuser ") public Object queryUserList (HttpServletRequest request, HttpServletResponse response) {User user = new User (); // The value sent from the fuzzy query // String username = request. getParameter ("username"); // determines whether the string is null. // if (StringUtil. isBlank (username) {// if it is null, replace it with an empty string // username = ""; //} // user. setUsername (username); Object obj = userService. queryUser (null, user); return obj;}/*** Add a user ** @ param user * @ return */@ ResponseBody @ RequestMapping ("/adduser ") public MsgBean addUser (User user) {MsgBean msg = null; try {msg = userService. addUser (user);} catch (Exception e) {logger. error ("cause of error:" + e. getMessage ();} return msg;}/*** user modify ** @ param user * @ return */@ ResponseBody @ RequestMapping ("/updateuser ") public MsgBean updateUser (User user) {MsgBean msg = null; try {msg = userService. updateUser (user);} catch (Exception e) {logger. error ("cause of error:" + e. getMessage ();} return msg;}/*** delete user ** @ param username * @ return */@ ResponseBody @ RequestMapping ("/deleteuser ") public MsgBean deleteUser (@ RequestParam ("username") String username) {MsgBean msg = null; try {msg = userService. deleteUser (username);} catch (Exception e) {logger. error ("cause of error:" + e. getMessage () ;}return msg ;}}

 

After writing the above code, you can run it. Because there is no front-end jsp file, you can add it directly after the accessed URL? Enter the Parameter