The basic procedure of using Jdbc Template, jdbctemplate

The basic procedure of using Jdbc Template, jdbctemplate

1. appliactionContext. xml configuration

<? Xml version = "1.0" encoding = "UTF-8"?> <Beans xmlns = "http://www.springframework.org/schema/beans" xmlns: p = "http://www.springframework.org/schema/p" xmlns: xsi = "http://www.w3.org/2001/XMLSchema-instance" xsi: schemaLocation = "http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd"> <! -- Data source configuration --> <bean id = "dataSource" class = "org. springframework. jdbc. datasource. driverManagerDataSource "> <property name =" driverClassName "value =" com. mysql. jdbc. driver "> </property> <property name =" url "value =" jdbc: mysql: // spring "> </property> <property name =" username "value =" root "> </property> <property name =" password "value =" "> </property> </bean> <bean id = "jdbcTemplate" class = "org. springframework. jdbc. core. jdbcTemplate "> <property name =" dataSource "ref =" dataSource "> </property> </bean> <bean id =" userDao "class =" cn. happy. impl. userDAOImpl "> <property name =" jdbcTemplate "ref =" jdbcTemplate "> </property> </bean> </beans>

2. Interface: IUserDAO. java

 public interface IUserDAO {        public void addUser(User user);      public void deleteUser(int id);      public void updateUser(User user);      public String searchUserName(int id);          public User searchUser(int id);          public List<User> findAll();  }

3. Interface implementation class: UserDAOImpl. java

Spring provides the JdbcDaoSupport class. If DAO inherits this class, JdbcTemplate is automatically obtained (provided that DataSource is injected ).

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">         <property name="dataSource" ref="dataSource"></property>     </bean>               <bean id="userDao" class="cn.happy.impl.UserDAOImpl">         <property name="jdbcTemplate" ref="jdbcTemplate"></property>     </bean>

JdbcTemplate mainly provides the following methods:

1. execute method: it can be used to execute any SQL statement and is generally used to execute DDL statements;

2. update method and batchUpdate method: the update method is used to execute statements such as ADD, modify, and delete. The batchUpdate method is used to execute statements related to batch processing;

3. query method and queryForXXX method: used to execute query-related statements;

4. call method: used to execute stored procedures and function-related statements.

Public class UserDAOImpl extends JdbcDaoSupport implements IUserDAO {public void addUser (User user) {String SQL = "insert into user values (?,?,?) "; This. getJdbcTemplate (). update (SQL, user. getId (), user. getUsername (), user. getPassword ();} public void deleteUser (int id) {String SQL = "delete from user where id =? "; This. getJdbcTemplate (). update (SQL, id);} public void updateUser (User user) {String SQL =" update User set username = ?, Password =? Where id =? "; This. getJdbcTemplate (). update (SQL, user. getUsername (), user. getPassword (), user. getId ();} public String searchUserName (int id) {// simple query, query by ID, returns the String SQL = "select username from user where id =? "; // The return type is String (String. class) return this. getJdbcTemplate (). queryForObject (SQL, String. class, id);} public List <User> findAll () {// String SQL = "select * from user"; return this. getJdbcTemplate (). query (SQL, new UserRowMapper ();} public User searchUser (int id) {String SQL = "select * from user where id =? "; Return this. getJdbcTemplate (). queryForObject (SQL, new UserRowMapper (), id);} class UserRowMapper implements RowMapper <User >{// rs is the returned result set, encapsulate the public User mapRow (ResultSet rs, int rowNum) throws SQLException {User user User = new user (); User. setId (rs. getInt ("id"); user. setUsername (rs. getString ("username"); user. setPassword (rs. getString ("password"); return user ;}}}

4. Test class: UserTest. java

Public class UserTest {@ Test // Add public void demo1 () {User user = new User (); user. setId (3); user. setUsername ("admin"); user. setPassword ("123456"); ApplicationContext applicationContext = new ClassPathXmlApplicationContext ("applicationContext. xml "); IUserDAO dao = (IUserDAO) applicationContext. getBean ("userDao"); dao. addUser (user) ;}@ Test // change public void demo2 () {User user = new User (); user. setId (1); user. setUsername ("admin"); user. setPassword ("admin"); ApplicationContext applicationContext = new ClassPathXmlApplicationContext ("applicationContext. xml "); IUserDAO dao = (IUserDAO) applicationContext. getBean ("userDao"); dao. updateUser (user) ;}@ Test // Delete public void demo3 () {ApplicationContext applicationContext = new ClassPathXmlApplicationContext ("applicationContext. xml "); IUserDAO dao = (IUserDAO) applicationContext. getBean ("userDao"); dao. deleteUser (3) ;}@ Test // query (simple query, return string) public void demo4 () {ApplicationContext applicationContext = new ClassPathXmlApplicationContext ("applicationContext. xml "); IUserDAO dao = (IUserDAO) applicationContext. getBean ("userDao"); String name = dao. searchUserName (1); System. out. println (name) ;}@ Test // query (simple query, return object) public void demo5 () {ApplicationContext applicationContext = new ClassPathXmlApplicationContext ("applicationContext. xml "); IUserDAO dao = (IUserDAO) applicationContext. getBean ("userDao"); User user User = dao. searchUser (1); System. out. println (user. getUsername () ;}@ Test // query (complex query, returned object set) public void demo6 () {ApplicationContext applicationContext = new ClassPathXmlApplicationContext ("applicationContext. xml "); IUserDAO dao = (IUserDAO) applicationContext. getBean ("userDao"); List <User> users = dao. findAll (); System. out. println (users. size ());}}