Spring----JDBC

In general, the use of jdbctemplate,jdbctemplate in the DAO class is configured in the XML configuration file and injected directly into the DAO.

<?XML version= "1.0" encoding= "UTF-8"?><Beansxmlns= "Http://www.springframework.org/schema/beans"Xmlns:xsi= "Http://www.w3.org/2001/XMLSchema-instance"xmlns:p= "http://www.springframework.org/schema/p"Xmlns:context= "Http://www.springframework.org/schema/context"xsi:schemalocation= "Http://www.springframework.org/schema/beans Http://www.springframework.org/schema/bean                               S/spring-beans.xsd Http://www.springframework.org/schema/context Http://www.springframework.org/schema/context/spring-context-3.1.xsd ">                                     <Context:component-scanBase-package= "Com.ivy"/>              <Context:property-placeholder Location= "Classpath:jdbc.properties"/>       <BeanID= "DataSource"class= "Org.apache.commons.dbcp.BasicDataSource"Destroy-method= "Close"P:driverclassname= "${jdbc.driverclassname}"P:url= "${jdbc.url}"P:username= "${jdbc.username}"P:password= "${jdbc.password}"/>                      <BeanID= "JdbcTemplate"class= "Org.springframework.jdbc.core.JdbcTemplate"P:datasource-ref= "DataSource"/></Beans>

Configuring DAO in the Spring configuration file is generally divided into 4 steps:

1. Define DataSource
2. Define JdbcTemplate
3. Declares an abstract bean so that all DAO multiplexing configures the configuration of the JdbcTemplate property.
4. Configure the specific DAO

Basic Data operations

• Change data

Try to use SQL with bindable parameters so that the database can reuse the SQL execution plan and improve the efficiency of database execution.

• Returns the table self-increment primary key value for the database

For example:

FinalString sqlString = "INSERT into T_forum (Forum_name, Forum_desc) VALUES (?,?)"; Keyholder Keyholder=NewgeneratedKeyHolder (); Jdbctemplate.update (NewPreparedStatementCreator () {@Override PublicPreparedStatement createpreparedstatement (Connection conn)throwsSQLException {preparedstatement ps=conn.preparestatement (sqlString); Ps.setstring (1, Forum.getforumnname ()); Ps.setstring (2, Forum.getforumndesc ()); returnPS;                }}, Keyholder); Forum.setforumnid (Keyholder.getkey (). Intvalue ());

• Batch Update

Private final String Batch_insert_sql = "INSERT into T_forum (Forum_name, Forum_desc) VALUES (?,?)";

Public voidAddforums (FinalList<forum>forums) {jdbctemplate.batchupdate (Batch_insert_sql,NewBatchPreparedStatementSetter () {@Override Public voidSetvalues (PreparedStatement PS,intIndexthrowsSQLException {Forum Forum=Forums.get (index); Ps.setstring (1, Forum.getforumname ()); Ps.setstring (2, Forum.getforumdesc ()); } @Override Public intgetbatchsize () {returnforums.size ();    }        }); }

• Querying data

Spring provides a RowCallbackHandler callback interface that allows you to define how data is fetched from the result set.

Single result set processing:

Private final String select_sql = "Select Forum_name, Forum_desc from T_forum WHERE forum_id=?";

PublicForum Getforum (Final intForumID) {        FinalForum Forum =NewForum (); Jdbctemplate.query (Select_sql,NewObject[]{forumid},NewRowCallbackHandler () {@Override Public voidProcessrow (ResultSet rs)throwsSQLException {forum.setforumid (ForumID); Forum.setforumdesc (Rs.getstring ("Forum_desc")); Forum.setforumname (Rs.getstring ("Forum_name"));        }        }); returnForum; }

Processing of multiple data result sets:

Private final String select_multi_sql = "Select Forum_id,forum_name, Forum_desc from T_forum WHERE forum_id between?" and? ";

PublicList<forum> Getforums (Final intFromid,Final inttoid) {        Finallist<forum> Forums =NewArraylist<>(); Jdbctemplate.query (Select_multi_sql,NewObject[]{fromid, toid},NewRowCallbackHandler () {@Override Public voidProcessrow (ResultSet rs)throwsSQLException {Forum Forum=NewForum (); Forum.setforumid (Rs.getint ("FORUM_ID")); Forum.setforumdesc (Rs.getstring ("Forum_desc")); Forum.setforumname (Rs.getstring ("Forum_name"));            Forums.add (forum);        }        }); returnForums; }

Using rowmapper<t> to process result sets,rowmapper<t> more suitable for use in a multiline result set:

Private final String select_multi_sql = "Select Forum_id,forum_name, Forum_desc from T_forum WHERE forum_id between?" and? ";

PublicList<forum> Getforumsbyrowmapper (Final intFromid,Final inttoid) {                returnJdbctemplate.query (Select_multi_sql,NewObject[]{fromid, toid},NewRowmapper<forum>() {@Override PublicForum Maprow (ResultSet RS,intIndexthrowsSQLException {Forum Forum=NewForum (); Forum.setforumid (Rs.getint ("FORUM_ID")); Forum.setforumdesc (Rs.getstring ("Forum_desc")); Forum.setforumname (Rs.getstring ("Forum_name")); returnForum;    }                    }); }

RowCallbackHandler vs Rowmapper<t>

Functionally, RowCallbackHandler and rowmapper<t> are not very different, they are read logic that defines the rows of the result set, and the data in the resultset is mapped to the object or list.

When working with large result sets, if you use RowMapper, all the data in the result set will eventually be mapped and aggregated into a list<t> object that consumes a large amount of JVM memory and can even directly cause oom, at which point the RowCallbackHandler interface should be used. The result set data is processed internally using the Processrow () method.

• Querying single-valued data

Single-valued query interface of type int: int queryforint (String sql)

Private Final String count_sql = "Select COUNT (*) from T_forum";  Public int Getforumnum () {        return  jdbctemplate.queryforint (count_sql);    }

Long single-valued query interface: Long queryForLong (String sql)

Other types of single-valued query interfaces:<t> T queryforobject (String sql, class<t> requiredtype);

To get a single-valued object using RowMapper:

 Public classTopicdao {@AutowiredPrivateJdbcTemplate JdbcTemplate;  Public DoubleGetreplyrate (intuserId) {String SQL= "Select Topic_replies, topic_views from T_topic WHERE user_id=?"; Doublerate = Jdbctemplate.queryforobject (SQL,NewObject[]{userid},NewRowmapper<double>() {@Override PublicDouble Maprow (ResultSet RS,intIndexthrowsSQLException {//TODO auto-generated Method Stub                intReplies = Rs.getint ("Topic_replies"); intviews = Rs.getint ("Topic_views"); if(Views > 0) {                    return NewDouble ((Double) replies/Views ); } Else {                    return NewDouble (0.0);        }            }                    }); returnRate ; }}

Call a stored procedure

Spring----JDBC