2015年5月19日 星期二

(溫故知新) SpringMVC 簡易練習(查找資料) easy 1

(溫故知新) SpringMVC 簡易練習(查找資料) easy 1

原始碼下載:https://goo.gl/7fEXBV

【開發環境】

Tomcat 7

MySQL

【開發工具】

Intellij IDEA 14

【使用FrameWork】

SpringMVC

jQuery

【程式畫面】

clip_image002

【程式需求】

1. 按“查詢”秀出結果在下方

2. 需要使用annotation

3. 請使用org.springframework.jdbc.core.JdbcTemplate方式做查詢

4. 請使用@Autowired方式將類別注入

當中會使用到的SQL,並建立好測試資料

-- MySQL 語法

CREATE TABLE IF NOT EXISTS `USER_PROFILE` (
   `ID` int(11) NOT NULL AUTO_INCREMENT,
   `USER_ID` varchar(11) NOT NULL,
   `USER_NAME` varchar(10) NOT NULL,
   `USER_SEX` varchar(10) NOT NULL,
   `USER_PHONE` varchar(10) NOT NULL,
   `USER_ADDRESS` varchar(10) NOT NULL,
   PRIMARY KEY (`ID`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;



--INSERT INTO `USER_PROFILE` (`USER_ID`, `USER_NAME`, `USER_SEX`,`USER_PHONE`,`USER_ADDRESS`)
VALUES

('2013000001', '張三', 'M','0911120111','台北市士林區'),

('2013000002', '李四', 'M','0911120112','新北市土城區'),

('2013000003', '王五', 'M','0911120113','新北市三重區'),

('2013000004', '陳二', 'F','0911120114','台北市信義區'),

('2013000005', '孫九', 'F','0911120115','台北市中山區');



SELECT * FROM `user_profile`;

接下來來建立 model

package test.models;


public class USER_PROFILE {
     private int id;
     private String userId;
     private String userName;
     private String userSex;
     private String userPhone;
     private String userAddress;
     private boolean male;
     private boolean female;


     public boolean isMale() {
         return male;
     }


     public void setMale(boolean male) {
         if(male){
             this.userSex = "M";
         }
         this.male = male;
     }


     public boolean isFemale() {
         return female;
     }


     public void setFemale(boolean female) {
         if(female){
             this.userSex = "F";
         }
         this.female = female;
     }


     public int getId() {
         return id;
     }


     public void setId(int id) {
         this.id = id;
     }


     public String getUserId() {
         return userId;
     }


     public void setUserId(String userId) {
         this.userId = userId;
     }


     public String getUserName() {
         return userName;
     }


     public void setUserName(String userName) {
         this.userName = userName;
     }


     public String getUserSex() {
         return userSex;
     }


     public void setUserSex(String userSex) {
         this.userSex = userSex;
     }


     public String getUserPhone() {
         return userPhone;
     }


     public void setUserPhone(String userPhone) {
         this.userPhone = userPhone;
     }


     public String getUserAddress() {
         return userAddress;
     }


     public void setUserAddress(String userAddress) {
         this.userAddress = userAddress;
     }

}

應該有注意到多了兩個屬性跟 Table 裡面的有所不同,一個是 male 一個是 female,這兩個屬性是後面要建立頁面時用到的,主要是要將畫面上選取的男性/女性轉換為單一屬性值 userSex 用。

接著我們使用 JdbcTemplate 取代傳統的 jdbc 連線方式來製作Dao

package test.dao;

import test.models.USER_PROFILE;

import java.util.List;

public interface UserDao {
     public void saveOrUpdate(USER_PROFILE user_profile);
     public void delete(int id);
     public List<USER_PROFILE> get(USER_PROFILE user_profile);
     public List<USER_PROFILE> list();

}

實作 UserDao

package test.dao;

import org.springframework.dao.DataAccessException;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.core.PreparedStatementSetter;

import org.springframework.jdbc.core.ResultSetExtractor;

import org.springframework.jdbc.core.RowMapper;

import test.models.USER_PROFILE;

import test.utils.StringUtil;

import javax.sql.DataSource;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

public class UserDaoImpl implements UserDao {
     private JdbcTemplate jdbcTemplate;
     public UserDaoImpl(DataSource dataSource){
         jdbcTemplate = new JdbcTemplate(dataSource);
     }
     // 儲存資料
     @Override
     public void saveOrUpdate(USER_PROFILE user_profile) {
     }
     // 刪除資料
     @Override
     public void delete(int id) {
     }


     // 取單一資料
     @Override
     public List<USER_PROFILE> get(final USER_PROFILE user_profile) {
         String sql = "SELECT * FROM user_profile where 1=1 " +
                 this.generateWhere(user_profile) + " order by user_id";
         return jdbcTemplate.query(sql,
                 new PreparedStatementSetter() {
                     @Override
                     public void setValues(PreparedStatement preparedStatement) throws SQLException {
                         preparePS(user_profile, preparedStatement);
                     }
                 },
                 new ResultSetExtractor<List<USER_PROFILE>>() {
                     @Override
                     public List<USER_PROFILE> extractData(ResultSet resultSet) throws SQLException, DataAccessException {
                         List<USER_PROFILE> listUser = new ArrayList<USER_PROFILE>();
                         while(resultSet.next()){
                             USER_PROFILE user_profile1 = new USER_PROFILE();
                             user_profile1.setId(resultSet.getInt("id"));
                             user_profile1.setUserId(resultSet.getString("USER_ID"));
                             user_profile1.setUserName(resultSet.getString("USER_NAME"));
                             user_profile1.setUserSex(resultSet.getString("USER_SEX"));
                             user_profile1.setUserPhone(resultSet.getString("USER_PHONE"));
                             user_profile1.setUserAddress(resultSet.getString("USER_ADDRESS"));
                             listUser.add(user_profile1);
                         }
                         if(listUser.size()>0){
                             return listUser;
                         }
                         return null;
                     }
                 });
         //return null;
     }


     private String generateWhere(USER_PROFILE user_profile){
         StringBuffer sb = new StringBuffer();
         // 姓名查詢
         if(!"".equals(StringUtil.NulltoString(user_profile.getUserName()))){
             sb.append(" and user_name like ? ");
         }
         // 電話查詢
         if(!"".equals(StringUtil.NulltoString(user_profile.getUserPhone()))){
             sb.append(" and user_phone = ? ");
         }
         // 性別查詢
         if(!"".equals(StringUtil.NulltoString(user_profile.getUserSex()))){
             sb.append(" and user_sex = ? ");
         }
         return (sb.length()>0?sb.toString():"");
     }


     public void preparePS(USER_PROFILE user_profile, PreparedStatement ps) throws SQLException{
         int count = 1;
         if(!"".equals(StringUtil.NulltoString(user_profile.getUserName()))){
             ps.setString(count++, "%" + user_profile.getUserName() + "%");
         }
         if(!"".equals(StringUtil.NulltoString(user_profile.getUserPhone()))){
             ps.setString(count++, user_profile.getUserPhone());
         }
         if(!"".equals(StringUtil.NulltoString(user_profile.getUserSex()))){
             ps.setString(count++, user_profile.getUserSex());
         }
     }


     // 顯示所有資料
     @Override
     public List<USER_PROFILE> list() {
         String sql = "SELECT * FROM user_profile ";
         List<USER_PROFILE> listUser = jdbcTemplate.query(sql, new RowMapper<USER_PROFILE>() {
             @Override
             public USER_PROFILE mapRow(ResultSet resultSet, int i) throws SQLException {
                 if(null != resultSet){
                     USER_PROFILE user_profile = new USER_PROFILE();
                     user_profile.setId(resultSet.getInt("id"));
                     user_profile.setUserId(resultSet.getString("USER_ID"));
                     user_profile.setUserName(resultSet.getString("USER_NAME"));
                     user_profile.setUserSex(resultSet.getString("USER_SEX"));
                     user_profile.setUserPhone(resultSet.getString("USER_PHONE"));
                     user_profile.setUserAddress(resultSet.getString("USER_ADDRESS"));
                     return  user_profile;
                 }
                 return null;
             }
         });
         return listUser;
     }

}

上面我們只實作了兩個方法 list() 及 get() 一個是顯示全部資料,一個是查找資料用,至於內部的內容就不多講解了,主要就是執行jdbcTemplete的query方法,並將當中的資料解析成我們想要的格式。

裡面有用到一個工具類別,主要是用來避免null 的問題出現

package test.utils;

public class StringUtil {
     public static String NulltoString(String strobj){
         if(null == strobj){
             return "";
         }else{
             return strobj;
         }
     }

}

接下來編寫配置檔的部份,這邊採用程式配置的方式(java code)

package test.config;



import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.ComponentScan;

import org.springframework.context.annotation.Configuration;

import org.springframework.jdbc.datasource.DriverManagerDataSource;

import org.springframework.web.servlet.ViewResolver;

import org.springframework.web.servlet.config.annotation.EnableWebMvc;

import org.springframework.web.servlet.config.annotation.ResourceHandlerRegistry;

import org.springframework.web.servlet.config.annotation.WebMvcConfigurerAdapter;

import org.springframework.web.servlet.view.InternalResourceViewResolver;

import test.dao.UserDao;

import test.dao.UserDaoImpl;

import javax.sql.DataSource;



@Configuration

@ComponentScan(basePackages = "test")

@EnableWebMvc

public class MvcConfiguration extends WebMvcConfigurerAdapter {


     // 設置 View 的解析器
     @Bean
     public ViewResolver getViewResolver(){
         InternalResourceViewResolver resolver = new InternalResourceViewResolver();
         resolver.setPrefix("/WEB-INF/views/");
         resolver.setSuffix(".jsp");
         return resolver;
     }


     // 設定資源檔來源
     @Override
     public void addResourceHandlers(ResourceHandlerRegistry registry) {
         registry.addResourceHandler("/resources/**").addResourceLocations("/resources/");
     }


     // 設定 DataSource
     @Bean
     public DataSource getDataSource(){
         DriverManagerDataSource dataSource = new DriverManagerDataSource();
         dataSource.setDriverClassName("com.mysql.jdbc.Driver");
         dataSource.setUrl("jdbc:mysql://localhost:3306/test");
         dataSource.setUsername("test");
         dataSource.setPassword("test");
         return  dataSource;
     }


     // 設置 DataSource 給 Dao
     @Bean
     public UserDao getUserDao(){
         return new UserDaoImpl(getDataSource());
     }

}

接著是撰寫 Web.xml 的部份,最前面有配置一個轉碼區塊,用來將我們畫面上送入的資料轉碼成正確的格式 (UTF-8),而因為我們原本正常應該要配置一個 spring 的配置檔,但因為我們使用的是 javaCode 的方式,所以這邊要指定給說我們是用什麼方式,否則預設會去讀取mvc-dispatcher-servlet.xml 這個配置檔(依照 servlet-name 而定),但會因找不到檔案而出現錯誤。

<?xml version="1.0" encoding="UTF-8"?>

<web-app
         xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
         xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
         id="WebApp_ID" version="2.5">
   <display-name>Archetype Created Web Application</display-name>


     <!-- UTF
轉碼 -->
    
<filter>
         <filter-name>charsetFilter</filter-name>
         <filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
         <init-param>
             <param-name>encoding</param-name>
             <param-value>UTF-8</param-value>
         </init-param>
     </filter>


     <filter-mapping>
         <filter-name>charsetFilter</filter-name>
         <url-pattern>/*</url-pattern>
     </filter-mapping>


     <!--
設定 Annotation class 資料 -->
    
<context-param>
         <param-name>contextClass</param-name>
         <param-value>org.springframework.web.context.support.AnnotationConfigWebApplicationContext</param-value>
     </context-param>


     <listener>
         <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
     </listener>


     <servlet>
         <servlet-name>mvc-dispatcher</servlet-name>
         <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
         <init-param>
             <param-name>contextClass</param-name>
             <param-value>org.springframework.web.context.support.AnnotationConfigWebApplicationContext</param-value>
         </init-param>
         <init-param>
             <!--
設定搜尋 package 路徑 -->
            
<param-name>contextConfigLocation</param-name>
             <param-value>test</param-value>
         </init-param>
         <load-on-startup>1</load-on-startup>
     </servlet>


     <servlet-mapping>
         <servlet-name>mvc-dispatcher</servlet-name>
         <url-pattern>/</url-pattern>
     </servlet-mapping>


     <welcome-file-list>
         <welcome-file>index.jsp</welcome-file>
     </welcome-file-list>



</web-app>

若是一般的 xml 配置方式應為以下這樣,在於 mvc-dispatcher-servlet.xml 中配置解析器等

<context-param>
     <param-name>contextConfigLocation</param-name>
     <param-value>/WEB-INF/mvc-dispatcher-servlet.xml</param-value>

</context-param>

接下來就是 Controller 的部份,這裡實際有用到的只有 @RequestMapping(value = "/home", method = RequestMethod.GET) @RequestMapping(value = "/get") 的部份。

package test.controller;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Controller;

import org.springframework.web.bind.annotation.ModelAttribute;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RequestMethod;

import org.springframework.web.servlet.ModelAndView;

import test.dao.UserDao;

import test.models.USER_PROFILE;

import java.util.ArrayList;

import java.util.List;

/**

  * Ref: Working with JDBC in Spring (JdbcTemplate)
  * http://www.codejava.net/frameworks/spring/spring-jdbc-template-simple-example
  * Ref: Spring MVC with JdbcTemplate Example
  * http://www.codejava.net/frameworks/spring/spring-mvc-with-jdbctemplate-example
  * Ref: Spring JDBC Template with Spring MVC - Example Hello World Tutorial
  * http://www.beingjavaguys.com/2013/07/spring-jdbc-template-with-spring-mvc.html
  * Ref: Spring MVC hello world example
  * http://www.mkyong.com/spring-mvc/spring-mvc-hello-world-example/
  * Ref: [JSTL] Core
標籤介紹
  * http://goo.gl/0mxiww
  */

@Controller

public class HomeController {
     @Autowired
     private UserDao userDao;


     @RequestMapping(value = "/home", method = RequestMethod.GET)
     public ModelAndView init(ModelAndView model){
         // 設定初始物件
         USER_PROFILE user_profile = new USER_PROFILE();
         List<USER_PROFILE> listUser = new ArrayList<>();
         model.addObject("user_profile", user_profile);
         model.addObject("listUser", listUser);
         // 設定要回傳的 View 名稱
         model.setViewName("home");
         return model;
     }


     @RequestMapping(value = "/listAll")
     public ModelAndView listUser(ModelAndView model){
         // 取得全部資料
         List<USER_PROFILE> listUser = userDao.list();
         model.addObject("listUser", listUser);
         model.setViewName("home");
         return model;
     }


     @RequestMapping(value = "/get")
     public ModelAndView get(@ModelAttribute("user_profile") USER_PROFILE user_profile){
         // 依頁面所輸入及勾選條件取得資料
         List<USER_PROFILE> listUser = userDao.get(user_profile);
         return new ModelAndView("home","listUser",listUser);
     }

}

解說一下過程,當 導至 /home 時,我們會先將畫面上所需要的資料準備好,但因為一開始是沒有任何資料的,故僅僅將其作初始化的動作,並設定好ModelAndView 將其回傳給 View Resolver 做處理,再由 View Resolver 找到所屬的 View(home.jsp),最後再由 Dispatcher Servlet 做資料填入/回應的動作

clip_image004

Image from: Spring Recipes -> http://goo.gl/jzePkj

最後先是 index.jsp

<%response.sendRedirect("home");%>

裡面只負責導頁的動作。

接著就是主要的 home.jsp,裡面用了 JSTL語法及spring 的 form tag,並使用 jQuery 做男/女切換的動作。

<%@ page language="java" contentType="text/html;charset=UTF-8" pageEncoding="UTF-8" %>

<%@ taglib prefix="form" uri="http://www.springframework.org/tags/form" %>

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>

<html>

<head>
     <title>Spring_easy_1</title>
     <meta http-equiv="Content-Type" content="text/html;charset=UTF-8"/>
     <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/2.0.0/jquery.min.js"></script>
     <script>
         $(function(){
             $("#ck_male").click(function(){
                 $("#ck_female").prop("checked",false);
             }) ;
             $("#ck_female").click(function(){
                 $("#ck_male").prop("checked",false);
             }) ;
         });
     </script>
     <style>
         table{
             border: 1px solid black;
         }
         table td{
             border: 1px solid black;
         }
         table th{
             border: 1px solid black;
         }
     </style>

</head>

<body>

<div align="center">
     <h2>Spring_easy_1</h2>
     <form:form method="post" action="/get" modelAttribute="user_profile">
         <table>
             <tr>
                 <td>使用者姓名</td>
                 <td><form:input path="userName"/></td>
             </tr>
             <tr>
                 <td>使用者電話</td>
                 <td><form:input path="userPhone"/></td>
             </tr>
             <tr>
                 <td>使用者性別</td>
                 <td>
                     <form:checkbox id="ck_male" path="male" label="男"/><br>
                     <form:checkbox id="ck_female" path="female" label="女"/>
                 </td>
             </tr>
             <tr>
                 <td colspan="2" align="center">
                     <input type="submit" value="查詢"/>&nbsp;&nbsp;<a href="home">清除</a>
                 </td>
             </tr>
         </table>
     </form:form>
     <hr>
     <c:if test="${not empty listUser}">
         <table>
             <tr>
                 <th>編號</th>
                 <th>姓名</th>
                 <th>性別</th>
                 <th>電話</th>
                 <th>地址</th>
             </tr>
             <c:forEach var="user" items="${listUser}">
                 <tr>
                     <td>${user.userId}<input type="hidden" value="${user.id}">
                     </td>
                     <td>${user.userName}</td>
                     <td>${user.userSex}</td>
                     <td>${user.userPhone}</td>
                     <td>${user.userAddress}</td>
                 </tr>
             </c:forEach>
         </table>
     </c:if>

</div>

</body>

</html>

上面 <form:input path=”???” /> 對應我們設定的 Model 屬性名稱,而 ${listUser} 對應的就是由ModelAndView 中所存放的資料。整個程式會在我們下搜尋條件時,就去執行 /get 這個 action,並將畫面上所選的所填入的資料帶入 user_profile 中。如下面所示程式碼:

Home.Jsp 中的 user_profile

<form:form method="post" action="/get" modelAttribute="user_profile">

對應到

@RequestMapping(value = "/get")

public ModelAndView get(@ModelAttribute("user_profile") USER_PROFILE user_profile){
     // 依頁面所輸入及勾選條件取得資料
     List<USER_PROFILE> listUser = userDao.get(user_profile);
     return new ModelAndView("home","listUser",listUser);

}

執行結果:

clip_image006

沒有留言 :

張貼留言