使用Jdbc Template的基本操作步驟，jdbctemplate

使用Jdbc Template的基本操作步驟，jdbctemplate

　1.appliactionContext.xml配置

<?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">
      
      <!--數據源的配置 -->
      <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.接口：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.接口實現類：UserDAOImpl.java

Spring提供了JdbcDaoSupport支持類，所有DAO繼承這個類，就會自動獲得JdbcTemplate（前提是注入DataSource）。

<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主要提供下列方法：

　　1、execute方法：可以用於執行任何SQL語句，一般用於執行DDL語句；

　　2、update方法及batchUpdate方法：update方法用於執行新增、修改、刪除等語句；batchUpdate方法用於執行批處理相關語句；

　　3、query方法及queryForXXX方法：用於執行查詢相關語句；

　　4、call方法：用於執行存儲過程、函數相關語句。

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) {// 簡單查詢，按照ID查詢，返回字符串
         String sql = "select username from user where id=?";
         // 返回類型為String(String.class)
         return this.getJdbcTemplate().queryForObject(sql, String.class, id);
 
     }
 
     public List<User> findAll() {// 復雜查詢返回List集合
         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為返回結果集，以每行為單位封裝著
         public User mapRow(ResultSet rs, int rowNum) throws SQLException {
     
             User user = new User();
             user.setId(rs.getInt("id"));
             user.setUsername(rs.getString("username"));
             user.setPassword(rs.getString("password"));
             return user;
         }
 
    }
 
 }

4.測試類：UserTest.java

public class UserTest {
     
     @Test//增
     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//改
     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//刪
     public void demo3(){
         ApplicationContext applicationContext=new ClassPathXmlApplicationContext("applicationContext.xml");
         IUserDAO dao=(IUserDAO) applicationContext.getBean("userDao");
         dao.deleteUser(3);
     }
     
     @Test//查（簡單查詢，返回字符串）
     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//查（簡單查詢，返回對象）
     public void demo5(){
         ApplicationContext applicationContext=new ClassPathXmlApplicationContext("applicationContext.xml");
         IUserDAO dao=(IUserDAO) applicationContext.getBean("userDao");
         User user=dao.searchUser(1);
         System.out.println(user.getUsername());
     }
     
     @Test//查（復雜查詢，返回對象集合）
     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());
     }
     
     
 
 }