Shulou(Shulou.com)06/01 Report--

Spring JDBC is the persistence layer technology provided by Spring, which uses JDBC API in a more direct and simpler way. In Spring JDBC, users only need to do the necessary things, and leave the complicated work of resource acquisition, Statement creation, exception handling, resource release and so on to Spring.

Although the framework of ORM is mature, the flexible and direct nature of JDBC still gives it the opportunity to use its talents.

The main content of this section: use JdbcTemplate template classes to manipulate CRUD data, BLOB and CLOB type data, and support the use of named parameter binding NamedParameterJdbcTemplate simulation classes.

1. Using JdbcTemplate in DAO

In general, you can configure JdbcTemplate in XML and then inject it into DAO

JdbcTemplate defined in Spring configuration file and injected into DAO

/ / @ Repository declares a DAO@Repository public class AdminDao implements IAdminDao {private static Logger logger=Logger.getLogger (AdminDao.class); private static final long serialVersionUID = 1L; / / @ Autowired injects an JdbcTemplate instance to declare a DAO@ Autowired private JdbcTemplate jdbcTemplate @ Override public Admin getAdminByNamAndPassword (String adminId, String password) {/ / TODO Auto-generated method stub String sql= "select adminId,adminName,adminPassword,adminHead,MAX (FROM_UNIXTIME (lastvisittime,'%Y-%m-%d% HRV% mRV% S')), lastvisitip from zzia_admin left join zzia_login_log on adminId=userId" + "where adminName='" + adminId+ "'and adminPassword='" + password+ "'; final Admin a=new Admin () This.jdbcTemplate.query (sql, new RowCallbackHandler () {@ Override public void proce***ow (ResultSet rs) throws SQLException {/ / TODO Auto-generated method stub a.setAdminId (rs.getInt (1)); a.setAdminName (rs.getString (2)); a.setAdminPassword (rs.getString (3)); a.setAdminHead (rs.getString (4)); a.setLastVisitTime (rs.getString (5)); a.setLastVisitIp (rs.getString (6)) }); return a;}

two。 Basic data operations

The addition, deletion, query and modification of data and stored procedure calls are the most basic database operations. JdbcTemplate provides many methods, and users can complete these data operations in simple methods through JdbcTemplate.

2.1 change data

JdbcTemplate provides several update () methods that allow users to change and delete data table records

Update example:

Public class TestJdbcTemplate {. / / the definition of jdbcTemplate is omitted here private static final String sql = "insert into jf_user (user_name,user_pwd,user_age) values (?,?)"; public static void addUser (User user) {Object [] params = new Object [] {user.getUsername (), user.getUserpwd (), user.getUserage ()}; int update = jdbcTemplate.update (sql, params); if (update > 0) {System.out.println ("insert successfully") }}}

2.2. Return the table self-increasing primary key value of the database

Users often use the sub-increment field of the data as the table primary key, that is, the primary key value is not generated in the application layer, but by the database when a new record is added. In this way, the application layer does not know the primary key value of the object before saving the object, and can only return the primary key value from the database after saving the data. In many cases, you need to get the primary key value of the persisted new object. In Hibernate, JPA and other ORM frameworks, after the new object is persisted, the primary key value is automatically bound to the object, which brings a lot of convenience to the development of the program.

Spring provides a way to return the primary key value of the new record. Int update (PreparedStatementCreator psc,KeyHolder generatedKeyHolder); org.springframework.jdbc.support.KeyHolder is a callback interface that spring uses to save the primary key corresponding to the new record. Spring refers to a general implementation class GeneratedKeyHolder for the KeyHolder interface, which returns the self-growing primary key value of the new record. Suppose you want to load the primary key value into the object after adding the object, the code example

Public static void addUserWithKeyHolder (final User user) {final String sql = "insert into jf_user (user_name,user_pwd,user_age) values"; KeyHolder keyHolder = new GeneratedKeyHolder (); jdbcTemplate.update (new PreparedStatementCreator () {@ Override public PreparedStatement createPreparedStatement (Connection con) throws SQLException {PreparedStatement ps = con.prepareStatement (sql); ps.setString (1, user.getUsername ()); ps.setString (2, user.getUserpwd ()) Ps.setString (3, user.getUserage ()); return ps;}}, keyHolder); user.setSerialNo (keyHolder.getKey (). IntValue ());}

If the database concurrency rate is relatively high, for example, if the database executes a bar insert record before the query primary key is executed after inserting the record, then adding the primary key value through the data query statement will only return the last inserted primary key value. Therefore, it is not safe to use query statements to obtain primary key values, which is why some databases (such as Oracle) deliberately do not provide self-increasing keys, but only sequences, which force users to obtain primary key values before adding new records. Oracle gets the next value of the sequence through the SELECT .nextval FROM DUAL.

2.3. Batch change data

If you need to insert or update multiple records at once, you can use the batchUpdate method of jdbcTemplate

BatchUpdate example:

Public static void addUsers (final List users) {final String sql = "insert into jf_user (user_name,user_pwd,user_age) values"; jdbcTemplate.batchUpdate (sql, new BatchPreparedStatementSetter () {@ Override public void setValues (PreparedStatement ps, int I) throws SQLException {/ / TODO Auto-generated method stub User user = users.get (I); ps.setString (1, user.getUsername ()); ps.setString (2, user.getUserpwd ()) Ps.setString (3, user.getUserage ();} @ Override public int getBatchSize () {/ / TODO Auto-generated method stub return users.size ();}});}

2.4. Query data

2.4.1. Use RowCallbackHandler to process result sets

Spring provides an org.springframework.jdbc.core.RowCallbackHandler callback interface through which you can define how to get data from the result set.

RowCallbackHandler example:

/ * * @ Title: getUserBySerialNo * @ Description: TODO gets a single result set * @ param @ param serialNo * @ param @ return configuration file * @ return User return type * @ throws * / public User getUserBySerialNo (final int serialNo) {String sql = "SELECT serial_no,user_name,user_pwd,user_age FROM jf_user.jf_users WHERE serial_no =?"; final User user = new User () JdbcTemplate.query (sql, new Object [] {serialNo}, new RowCallbackHandler () {@ Override public void proce***ow (ResultSet rs) throws SQLException {user.setSerialNo (serialNo); user.setUsername (rs.getString ("user_name")); user.setUserpwd (rs.getString ("user_pwd")); user.setUserage (rs.getString ("user_age"));}}); return user } / * * @ Title: getUsers * @ Description: TODO gets multiple result sets * @ param @ param fromSerialNo * @ param @ param toSerialNo * @ param @ return configuration file * @ return List return type * @ throws * / public List getUsers (final int fromSerialNo,final int toSerialNo) {String sql = "SELECT serial_no,user_name,user_pwd,user_age FROM jf_user.jf_users WHERE serial_no between? And? "; final List users = new ArrayList (); jdbcTemplate.query (sql, new Object [] {fromSerialNo,toSerialNo}, new RowCallbackHandler () {@ Override public void proce***ow (ResultSet rs) throws SQLException {/ / TODO Auto-generated method stub User user = new User (); user.setSerialNo (rs.getInt (" serial_no ")); user.setUsername (rs.getString (" user_name ")); user.setUserpwd (rs.getString (" user_pwd ")) User.setUserage (rs.getString ("user_age")); users.add (user);}}); return users;}

2.4.2. Use RowMapper to process result sets

Spring also provides a RowMapper interface similar to RowCallbackHandler, which can also use RowMapper to define the logical mapping of the result set, which is easier to use when the result set is multi-line records.

RowMapper code example

/ * @ Title: getUsersOnRowMapper * @ Description: TODO uses RowMapper to map multiple rows of data * @ param @ param fromSerialNo * @ param @ param toSerialNo * @ param @ return configuration file * @ return List return type * @ throws * / public List getUsersOnRowMapper (final int fromSerialNo,final int toSerialNo) {String sql = "SELECT serial_no,user_name,user_pwd,user_age FROM jf_user.jf_users WHERE serial_no between? And? "; return jdbcTemplate.query (sql, new Object [] {fromSerialNo,toSerialNo}, new RowMapper () {@ Override public User mapRow (ResultSet rs, int rowNum) throws SQLException {/ / TODO Auto-generated method stub User user = new User (); user.setSerialNo (rs.getInt (" serial_no ")); user.setUsername (rs.getString (" user_name ")); user.setUserpwd (rs.getString (" user_pwd ")) User.setUserage (rs.getString ("user_age"); return user;}});}

2.5. Query single-valued data

If the result set of a query has only one value, such as SELECT COUNT (*) FROM jf_users, and so on, you can get the value of the result in an easier way. JdbcTemplate provides three sets of methods for obtaining single-valued data in the result set, which are used to obtain single values of int and long, respectively, and other types of single values are returned as Object type.

2.6. Call stored procedures

JdbcTemplate provides two interface methods for calling stored procedures:

T execute (String callString,CallableStatementCallback action)

T execute (CallableStatementCreator csc,CallableStatementCallback cs)

Example of calling a stored procedure:

CREATE PROCEDURE P_GET_VIEW_POINT_NUM (IN in_serialNO,INT,OUT out_num INT) BEGIN SELECT COUNT (*) INTO out_num FROM jf_user WHERE serialNO = in_serialNo;END / * @ Title: getUserCounts * @ Description: TODO JdbcTemplate calls stored procedure * @ param @ param serailNo * @ param @ return configuration file * @ return int return type * @ throws * / public int getUserCounts (final int serailNo) {String sql = "{call P_GET_VIEW_POINT_NUM (?)}" String execute = jdbcTemplate.execute (sql, new CallableStatementCallback () {@ Override public Object doInCallableStatement (final CallableStatement cs) throws SQLException, DataAccessException {/ / TODO Auto-generated method stub cs.setInt (1, serailNo); cs.registerOutParameter (2, Types.INTEGER); cs.execute (); return cs.getInt (2);}}); return Integer.valueOf (execute);}

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.