Spring4.3.1 JDBC Notes

Personal Summary, reproduced please specify the source: http://www.cnblogs.com/lidabnu/p/5679354.html

Based on Spring4.3.1 Official document summary, Official document link http://docs.spring.io/spring/docs/current/spring-framework-reference/htmlsingle/#jdbc

What Spring JDBC can do, as shown, is the purpose of "simplifying development" and doing all of the common business-agnostic operations in the framework.

Think about the issues that you need to address to manipulate the database using JDBC:

1. How to configure the database connection;
2. Execute query: According to the return value type can be divided into query out object, query out the object list, query the quantity and so on scalar; Then there is a conditional query.
3. Perform deletions, modifications, and insertions;
4. Bulk Operations database.

The following is a brief introduction to the JDBC operation related classes in Spring4.3.1:

1. JdbcTemplate: The most classic is also the most popular class, other classes are based on the JdbcTemplate package;Jdbctemplat is thread-safe, which is the premise of multiple DAO sharing a JdbcTemplate
2. Namedparameterjdbctemplate: Encapsulation JdbcTemplate provides a named parameter assignment method instead of the traditional placeholder "?" of JdbcTemplate, which is more convenient when there are multiple parameters to be assigned in SQL.
3. Simplejdbcinsert and Simplejdbccall: Simplify configuration with database metadata by providing only the table name or stored procedure name and a set of parameters that match the column name in the database. Requires sufficient metadata support for the database.
4. Rdbmsobject: Slightly

Look at the problem to be solved:

How to configure a database connection:

Spring has three ways to configure the data source: using Jndi to take advantage of external data sources (such as web containers such as Tomcat), database connection pooling with DBCP management, and the use of spring's own drivemanagerdatasource (this does not support connection pooling And therefore cannot be used in production environments). This is just about DBCP. It is very simple to use, which is to configure a DBCP data source in the configuration file.

    <BeanID= "DataSource"class= "Org.apache.commons.dbcp.BasicDataSource">        < Propertyname= "Driverclassname"value= "Com.mysql.jdbc.Driver"></ Property>        < Propertyname= "url"value= "Jdbc:mysql://localhost:3306/stock"></ Property>        < Propertyname= "username"value= "root"></ Property>        < Propertyname= "Password"value= "123456"></ Property>        < Propertyname= "InitialSize"value= "5"></ Property>        < Propertyname= "Maxactive"value= "Ten"></ Property>    </Bean>

Of course, in general, the URL of the database and the user name, password, etc. will be configured in the additional configuration file, so you can use the Spel expression to get. You need to use Context:property-placeholder to configure the path to the configuration file.

<?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"Xmlns:util= "Http://www.springframework.org/schema/util"Xmlns:mvc= "Http://www.springframework.org/schema/mvc"xsi:schemalocation= "Http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.0 . xsd Http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3 .0.xsd Http://www.springframework.org/schema/context Http://www.springframework.org/schema/context/spring-c Ontext-3.0.xsd "><!--<bean id= "DataSource" class= "Org.apache.commons.dbcp.BasicDataSource" > -<!--<property name= "Driverclassname" value= "Com.mysql.jdbc.Driver" ></property> -<!--<property name= "url" value= "Jdbc:mysql://localhost:3306/stock" ></property> -<!--<property name= "username" value= "root" ></property> -<!--<property name= "password" value= "123456" ></property> -<!--<property name= "InitialSize" value= "5" ></property> -<!--<property name= "maxactive" value= "ten" ></property> -<!--</bean> -    <BeanID= "DataSource"class= "Org.apache.commons.dbcp.BasicDataSource">        < Propertyname= "Driverclassname"value= "Com.mysql.jdbc.Driver"></ Property>        < Propertyname= "url"value= "Jdbc:mysql://localhost:3306/stock"></ Property>        < Propertyname= "username"value= "${jdbc.username}"></ Property>        < Propertyname= "Password"value= "${jdbc.password}"></ Property>        < Propertyname= "InitialSize"value= "5"></ Property>        < Propertyname= "Maxactive"value= "Ten"></ Property>    </Bean>        <Context:property-placeholder Location= "Jdbc.properties"></Context:property-placeholder>        <BeanID= "Stockdao"class= "Testjdbc.dao.StockDao">        < Propertyname= "DataSource"ref= "DataSource"></ Property>    </Bean>    <!--<context:component-scan base-package= "Stock.dao" ></context:component-scan> -</Beans>

How to execute a query:

Query scalar:

int countofactorsnamedjoe = this.jdbcTemplate.queryForObject (        "SELECT count (*) from t_actor where first_name =?", in Teger.class, "Joe"); String lastName = this.jdbcTemplate.queryForObject (        "Select Last_Name from t_actor where id =?",        new object[]{12 12L}, String.class);

Query object: using the queryForObject method, the input parameter uses the object array, and the overload defines the "row-Object" transform object RowMapper

Stock Querystock (String code) {return Getjdbctemplate (). queryForObject ("SELECT * from stock where code=?", New object[]{ Code}, new Rowmapper<stock> () {public Stock Maprow (ResultSet rs, int arg1) throws SQLException {stock stock = new Sto CK (), Stock.setcode (rs.getstring ("Code")), Stock.setname (rs.getstring ("name")), Stock.setid (Rs.getint ("id")); return stock;}});

Querying a batch of objects: Same as queryForObject

List<stock> querystocks ()//without condition {return getjdbctemplate (). Query (Select_sql, new rowmapper<stock> () { Public Stock Maprow (ResultSet rs, int arg1) throws SQLException {Stock stock = new Stock (); Stock.setcode (rs.getstring ("Cod E ")); Stock.setname (rs.getstring (" name ")); Stock.setid (Rs.getint (" id ")); return stock;}}); List<stock> querystocks (String codeprefix)//Conditional {return getjdbctemplate (). Query ("SELECT * from Stock where code Like? ", new object[]{codeprefix+"% "}, new Rowmapper<stock> () {public Stock Maprow (ResultSet rs, int arg1) throws SQL Exception {Stock stock = new Stock (), Stock.setcode (rs.getstring ("Code")), Stock.setname (rs.getstring ("name")); Stock.setid (Rs.getint ("id")); return stock;}});

　　

How to perform delete, modify, and insert:

int Insert (stock stock) {return getjdbctemplate (). Update ("INSERT into Stock (id,code,name) value (?,?,?)", Stock.getid () , Stock.getcode (), Stock.getname ());} int delete (String stockcode) {return getjdbctemplate (). Update ("Delete from stock where code=?", Stockcode);

How to perform bulk operations:

Bulk operations reduce the network back and forth between the client side (application) and the database server, thus improving performance, and next time you write a separate article

Spring4.3.1 JDBC Notes