Spring框架針對dao層的jdbcTemplate操作crud之query查詢數據操作 —— 查詢表，返回結果為對象的list集合

用JdbcTemplate的方法完成， 查詢資料庫表，把用戶表sw_user所有數據以List<User>集合返回

在JdbcTemplateDemo類中增加查詢返回所有對象集合的方法queryAllObject()

數據源DriverManagerDataSource不變

jdbcTemplate的方法有所改變，使用query(sql, new MyRowMapper());

其中第二個參數是一個介面RowMapper<User>的實現類，可以使用匿名內部類，我使用的是單獨的類MyRowMapper的對象

它要實現重載的方法mapRow(ResultSet rs, int num)只做兩件事

一用ResultSet得到所需User對象數據

二用User進行封裝得到對象並返回

瀏覽器得到查詢效果圖：

JdbcTemplateDemo類：

package com.swift;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.stereotype.Component;

@Component(value="jdbcTemplateDemo")
public class JdbcTemplateDemo {
    
    public List<User> queryAllObject(){
        DriverManagerDataSource dataSource=new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUsername("root");
        dataSource.setPassword("root");
        dataSource.setUrl("jdbc:mysql://localhost:3306/sw_database");
        
        JdbcTemplate jdbcTemplate=new JdbcTemplate(dataSource);
        String sql="select * from sw_user";
        List<User> list=jdbcTemplate.query(sql, new MyRowMapper());
        return list;
        
    }
    
    public User queryOneObject() {
        
        DriverManagerDataSource dataSource=new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/sw_database");
        dataSource.setUsername("root");
        dataSource.setPassword("root");
        
        JdbcTemplate jdbcTemplate=new JdbcTemplate(dataSource);
        String sql="select * from sw_user where username=? and password=?";
        User user=jdbcTemplate.queryForObject(sql, new MyRowMapper(), "zhangsan","123456");
        return user;
        
    }
    
    public boolean delete(String username) {
    DriverManagerDataSource dataSource=new DriverManagerDataSource();
    dataSource.setDriverClassName("com.mysql.jdbc.Driver");
    dataSource.setUrl("jdbc:mysql://localhost:3306/sw_database");
    dataSource.setUsername("root");
    dataSource.setPassword("root");
    
    JdbcTemplate jdbcTemplate=new JdbcTemplate(dataSource);
    int count=jdbcTemplate.update("delete from sw_user where username=?", username);
    if(count!=0) {
        return true;
    }
    return false;
    }
}

//沒有用匿名內部類，註意MyRowMapper後沒有<User>

class MyRowMapper implements RowMapper<User> {

    @Override
    public User mapRow(ResultSet rs, int num) throws SQLException {
        
        String username=rs.getString("username");
        String password=rs.getString("password");
        User user=new User(username,password);
        return user;
    }
    
}

Servlet類調用該JdbcTemplateDemo類

package com.swift;

import java.io.IOException;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

@WebServlet("/demo")
public class ServletDemo extends HttpServlet {
    private static final long serialVersionUID = 1L;
       
    public ServletDemo() {
        super();
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        response.getWriter().append("Served at: ").append(request.getContextPath());
        
        //使用JdbcTemplat的queryForObject方法
        ApplicationContext context=new ClassPathXmlApplicationContext("aop.xml");
        JdbcTemplateDemo jdbcTemplateDemo=(JdbcTemplateDemo) context.getBean("jdbcTemplateDemo");
        List<User> list=jdbcTemplateDemo.queryAllObject();
        for(User user:list) {
            response.getWriter().append(user.toString());
            response.getWriter().println();
        }
        
    }

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request, response);
    }

}

所用到的xml配置文件在前面隨筆中及相關包