Spring's JdbcTemplate

Another spring feature module data access supports the database

Spring data Access First Helloword case:

Implementing access configuration using Java programs

1. Guide Package

2. Test case

@Testpublic void test01 () {Drivermanagerdatasource datasource=new drivermanagerdatasource (); Datasource.setdriverclassname ("Com.mysql.jdbc.Driver");d atasource.seturl ("Jdbc:mysql:///springtest"); Datasource.setusername ("root");d Atasource.setpassword ("123"); JdbcTemplate jdbctemplate=new JdbcTemplate (); Jdbctemplate.setdatasource (DataSource); String sql= "INSERT into T_user values (1, ' Zhang San ', 123, ' Male ')"; Jdbctemplate.execute (sql);}

Give the created object to spring to control, so you need to configure the data access in the Applicationcontext.xml file

Applicationcontext.xml

<?xml version= "1.0" encoding= "UTF-8"? ><beans xmlns= "Http://www.springframework.org/schema/beans" xmlns:c= "HTTP://WWW.SPRINGFRAMEWORK.ORG/SCHEMA/C" xmlns:p= "http://www.springframework.org/schema/p" xmlns:xsi= "http// Www.w3.org/2001/XMLSchema-instance "xmlns:context=" Http://www.springframework.org/schema/context "xsi: schemalocation= "Http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/ spring-beans.xsdhttp://www.springframework.org/schema/contexthttp://www.springframework.org/schema/context/ spring-context.xsdhttp://www.springframework.org/schema/aophttp://www.springframework.org/schema/aop/ Spring-aop.xsdhttp://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd " ><!--Create a data source-<bean id= "Drivermanagerdatasource" class= " Org.springframework.jdbc.datasource.DriverManagerDataSource "> <!--configuration database driver--><property name=" Driverclassname "value=" Com.mysql.jdbc.Driver "></property><p roperty name= "url" value= "jdbc:mysql:///springtest" ></property><property name= "username" value= "root "></property><property name=" password "value=" 123 "></property></bean><bean id="JdbcTemplate"Class=" org.springframework.jdbc.core.JdbcTemplate "><!--injection data source--><property name=" DataSource "ref=" Drivermanagerdatasource "></property></bean></beans>

Test method;

@RunWith (springjunit4classrunner.class)  jdbctemplate; @Testpublic void test01 () {String sql= insert Into T_user values (2, ' Zhang San ', 123, ' Male ') "; Jdbctemplate.execute (sql);}}

　　CRUD Operations

　　　　Create a table

CREATE DATABASEspringtest; Usespringtest;CREATE TABLET_user (IDINT PRIMARY KEYAuto_increment,nameVARCHAR( -), ageINT, SexVARCHAR( -))INSERT  intoT_userVALUES(NULL,'Tom', -,'male');INSERT  intoT_userVALUES(NULL,'Fox', -,'male');INSERT  intoT_userVALUES(NULL,'Tony', +,'male');

Create a table

　　　　Cud operation

@RunWith (Springjunit4classrunner.class)//Integrated JUNIT4@ContextConfiguration (locations= "Classpath:applicationContext.xml")//using annotations to load a configuration file Public classspringjdbctest {@AutowiredPrivateJdbcTemplate JdbcTemplate; @Test//Add Data     Public voidInsertData () {//String sql= "insert into T_user values (4, ' Zhang San ', 123, ' Male ')"; //jdbctemplate.execute (SQL);String sql= "INSERT into T_user values (?,?,?,?)"; Jdbctemplate.update (SQL,5, "Harry", 66, "female"); } @Test//Delete Data     Public voidDeleteData () {//String sql= "Delete from T_user where id=4"; //jdbctemplate.execute (SQL);String sql= "Delete from T_user where id=? "; Jdbctemplate.update (SQL,5); } @Test//Modifying Data     Public voidUpdateData () {/*String sql= "Update t_user set name= ' John Doe ' where id=3"; Jdbctemplate.execute (SQL);*/String SQL= "Update t_user set name=?" where id=? "; Jdbctemplate.update (SQL,"JJ", 1); }}

Delete and change

Query operations

Querying for data that is not encapsulated

@Test//query a simple block of data     Public voidSeachOne3 () {List<map<string, object>> list = Jdbctemplate.queryforobject ("SELECT * from T_user",NewRowmapper<list<map<string,object>>>() {List<Map<String,Object>> list=NewArraylist<map<string,object>>(); @Override PublicList<map<string,object>> Maprow (ResultSet RS,intRowNum)throwsSQLException {System.out.println (rowNum); System.out.println ("------"); Map<String,Object> map=NULL;  while(Rs.next ()) {map=NewHashmap<string,object>(); Map.put ("id", Rs.getint ("id")); Map.put ("Name", Rs.getstring ("name")); Map.put (' Age ', Rs.getint ("Age")); Map.put ("Sex", rs.getstring ("Sex"));                List.add (map); }                returnlist;        }        });    SYSTEM.OUT.PRINTLN (list); }

queryForObject

@Test//query a simple block of data     Public voidSeachOne3 () {List<map<string, object>> list = Jdbctemplate.query ("SELECT * from T_user",NewRowmapper<map<string,object>>() {List<Map<String,Object>> list=NewArraylist<map<string,object>>(); @Override PublicMap<string,object> Maprow (ResultSet RS,intRowNum)throwsSQLException {Map<String,Object> map=NewHashmap<string,object>(); Map.put ("id", Rs.getint ("id")); Map.put ("Name", Rs.getstring ("name")); Map.put (' Age ', Rs.getint ("Age")); Map.put ("Sex", rs.getstring ("Sex")); returnmap;        }        });    SYSTEM.OUT.PRINTLN (list); }

Query

Querying the encapsulated data

@Test//query a simple block of data     Public voidSeachOne2 () {List<User> list = Jdbctemplate.queryforobject ("SELECT * from T_user",NewRowmapper<list<user>>() {List<User> list=NewArraylist<user>(); @Override PublicList<user> Maprow (ResultSet RS,intRowNum)throwsSQLException {//System.out.println (Rs.getrow ()); //Do {User u=NewUser (); U.setid (Rs.getint ("id")); U.setname (Rs.getstring ("Name")); U.setage (Rs.getint ("Age")); U.setsex (Rs.getstring ("Sex"));                    List.add (U); //} while (Rs.next ());                    returnlist;        }        });    SYSTEM.OUT.PRINTLN (list); }

Queryforobjcet

@Test//query a simple block of data     Public voidseachOne4 () {List<User> list = Jdbctemplate.query ("SELECT * from T_user",NewRowmapper<user>() {List<User> list=NewArraylist<user>(); @Override PublicUser Maprow (ResultSet RS,intRowNum)throwsSQLException {User u=NewUser (); U.setid (Rs.getint ("id")); U.setname (Rs.getstring ("Name")); U.setage (Rs.getint ("Age")); U.setsex (Rs.getstring ("Sex")); returnu;        }        });    SYSTEM.OUT.PRINTLN (list); }

Query

The difference between query and queryForObject

Query is the underlying process of querying the data, such as the query result is empty will return empty, do not throw an exception; The return value of query will differentiate the return based on the second parameter

@Overridepublic void query (String sql, RowCallbackHandler rch) throws DataAccessException {   return a collection with a value of T return query (SQL, new Rowmapperresultsetextractor<t> (RowMapper));}

　　

@Overridepublic list<t> Extractdata (ResultSet rs) throws SQLException {list<t> results = (this.rowsexpected > 0? New Arraylist<t> (this.rowsexpected): New Arraylist<t> ()), int rowNum = 0;//set to line 0th start while (Rs.next ()) {// No data will be  returned as Nullresults.add (This.rowMapper.mapRow (RS, rownum++)) without error;} return results;}

　　queryForObject throws an exception if no data is queried at the time of the query because the results is judged to throw an exception if it is empty.

public static <T> T Requiredsingleresult (collection<t> results) throws incorrectresultsizedataaccessexception {int size = (results! = null? Results.size (): 0); if (size = = 0) {throw new Emptyr Esultdataaccessexception (1);} if (Results.size () > 1) {throw new Incorrectresultsizedataaccessexception (1, size);} Return Results.iterator (). Next ();

Bean query similar to beanutils for data assignment to JavaBean class

public class Beanpropertyrowmapper<t> implements Rowmapper<t>

　　

@Test//Use bean to query data public void Seachbean () {list<user> List = Jdbctemplate.query ("SELECT * from T_user", new Beanpropertyrowmapper<user> (User.class)); SYSTEM.OUT.PRINTLN (list);}

　　

Spring's JdbcTemplate