2015年6月10日 星期三

(溫故知新) Hibernate 簡易練習(查找資料)

(溫故知新) Hibernate 簡易練習(查找資料)

原始碼下載: https://goo.gl/xu4KXV https://goo.gl/1S0qJt

【開發環境】

Tomcat 7

MySQL

【開發工具】

Intellij IDEA 14

【使用FrameWork】

SpringMVC

jQuery

【主要畫面】

image

 

簡單的說就是與資料庫結合來查找資料，使用 BasicDataSource Hibernate4搭配 MySQL 使用。

【使用到的 MySQL 資料】

-- 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`;

直接使用 Annotation 來設置

先使用 Intellij IDEA 建立一個 maven 的 webapp 專案。

Web.xml 不需要，因為全部使用 POJO 來設置。先配置好Maven使用的pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
          xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
     <modelVersion>4.0.0</modelVersion>
     <groupId>Hibernate_easy_1</groupId>
     <artifactId>exam</artifactId>
     <packaging>war</packaging>
     <version>1.0-SNAPSHOT</version>
     <name>exam Maven Webapp</name>
     <url>http://maven.apache.org</url>


     <properties>
         <compiler.version>1.7</compiler.version>
         <junit.version>3.8.1</junit.version>
         <springframework.version>4.0.6.RELEASE</springframework.version>
         <hibernate.version>4.3.6.Final</hibernate.version>
         <mysql.version>5.1.31</mysql.version>
     </properties>


     <dependencies>
         <dependency>
             <groupId>junit</groupId>
             <artifactId>junit</artifactId>
             <version>${junit.version}</version>
             <scope>test</scope>
         </dependency>
         <!-- Spring -->
         <dependency>
             <groupId>org.springframework</groupId>
             <artifactId>spring-core</artifactId>
             <version>${springframework.version}</version>
         </dependency>
         <dependency>
             <groupId>org.springframework</groupId>
             <artifactId>spring-web</artifactId>
             <version>${springframework.version}</version>
         </dependency>
         <dependency>
             <groupId>org.springframework</groupId>
             <artifactId>spring-webmvc</artifactId>
             <version>${springframework.version}</version>
         </dependency>
         <dependency>
             <groupId>org.springframework</groupId>
             <artifactId>spring-tx</artifactId>
             <version>${springframework.version}</version>
         </dependency>
         <dependency>
             <groupId>org.springframework</groupId>
             <artifactId>spring-orm</artifactId>
             <version>${springframework.version}</version>
         </dependency>


         <!-- Hibernate -->
         <dependency>
             <groupId>org.hibernate</groupId>
             <artifactId>hibernate-core</artifactId>
             <version>${hibernate.version}</version>
         </dependency>


         <!-- MySQL -->
         <dependency>
             <groupId>mysql</groupId>
             <artifactId>mysql-connector-java</artifactId>
             <version>${mysql.version}</version>
         </dependency>


         <!-- Servlet+JSP+JSTL -->
         <dependency>
             <groupId>javax.servlet</groupId>
             <artifactId>javax.servlet-api</artifactId>
             <version>3.1.0</version>
         </dependency>
         <dependency>
             <groupId>javax.servlet.jsp</groupId>
             <artifactId>javax.servlet.jsp-api</artifactId>
             <version>2.3.1</version>
         </dependency>
         <dependency>
             <groupId>javax.servlet</groupId>
             <artifactId>jstl</artifactId>
             <version>1.2</version>
         </dependency>
     </dependencies>


     <build>
         <finalName>SpringHibernateExample</finalName>
         <plugins>
             <plugin>
                 <groupId>org.apache.maven.plugins</groupId>
                 <artifactId>maven-compiler-plugin</artifactId>
                 <configuration>
                     <source>${compiler.version}</source>
                     <target>${compiler.version}</target>
                 </configuration>
             </plugin>
         </plugins>
     </build>

</project>

首先來完成 Model 用來對應資料庫的欄位

package test.model;



import javax.persistence.*;



/**
  * Created by Hsu on 2015/5/9.
  */

@Entity

@Table(name="USER_PROFILE")

public class USER_PROFILE {
     @Id
     @GeneratedValue(strategy = GenerationType.AUTO)
     private int id;


     @Column(name = "USER_ID", nullable = false)
     private String userId;


     @Column(name = "USER_NAME", nullable = false)
     private String userName;


     @Column(name = "USER_SEX", nullable = false)
     private String userSex;


     @Column(name = "USER_PHONE", nullable = false)
     private String userPhone;


     @Column(name = "USER_ADDRESS", nullable = false)
     private String userAddress;


     // 暫存變數
     @Transient
     private boolean male;


     // 暫存變數
     @Transient
     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;
     }

}

程式中設置了 @Transient 用來存放變數，表示其不需要對應到資料庫的 Column，而整個類別的開頭標示了

@Entity
@Table(name="USER_PROFILE")

註明其為Hibernate 用的實體，且對照到USER_PROFILE這張Table，其餘的 @Column 則是指定該變數對照到的欄位，並且說明儲存資料時其資料不得為 null。

接著新增 Hibernate 的配置文件(POJO)

package test.configuration;



import org.hibernate.SessionFactory;

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

import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.ComponentScan;

import org.springframework.context.annotation.Configuration;

import org.springframework.context.annotation.PropertySource;

import org.springframework.core.env.Environment;

import org.springframework.jdbc.datasource.DriverManagerDataSource;

import org.springframework.orm.hibernate4.HibernateTransactionManager;

import org.springframework.orm.hibernate4.LocalSessionFactoryBean;

import org.springframework.transaction.annotation.EnableTransactionManagement;



import javax.sql.DataSource;

import java.util.Properties;



/**
  * Created by Hsu on 2015/5/9.
  */

@Configuration

@EnableTransactionManagement

@ComponentScan({"test.configuration"})

@PropertySource(value = {"classpath:application.properties"})

public class HibernateConfiguration {
     @Autowired
     private Environment environment;


     @Bean
     public DataSource dataSource(){
         DriverManagerDataSource dataSource = new DriverManagerDataSource();
         dataSource.setDriverClassName(environment.getRequiredProperty("jdbc.driverClassName"));
         dataSource.setUrl(environment.getRequiredProperty("jdbc.url"));
         dataSource.setUsername(environment.getRequiredProperty("jdbc.username"));
         dataSource.setPassword(environment.getRequiredProperty("jdbc.password"));
         return dataSource;
     }


     private Properties hibernateProperties() {
         Properties properties = new Properties();
         properties.put("hibernate.dialect", environment.getRequiredProperty("hibernate.dialect"));
         properties.put("hibernate.show_sql", environment.getRequiredProperty("hibernate.show_sql"));
         properties.put("hibernate.format_sql", environment.getRequiredProperty("hibernate.format_sql"));
         return properties;
     }


     @Bean
     public LocalSessionFactoryBean sessionFactory(){
         LocalSessionFactoryBean sessionFactory = new LocalSessionFactoryBean();
         sessionFactory.setDataSource(dataSource());
         sessionFactory.setPackagesToScan(new String[]{"test.model"});
         sessionFactory.setHibernateProperties(hibernateProperties());
         return sessionFactory;
     }


     @Bean
     @Autowired
     public HibernateTransactionManager transactionManager(SessionFactory s) {
         HibernateTransactionManager txManager = new HibernateTransactionManager();
         txManager.setSessionFactory(s);
         return txManager;
     }

}

由於將使用 Implements WebApplicationInitializer 的方式載入配置檔，所以我們可以將一些Config獨立出來，如下AppConfig.java，開頭的 @Configuration 就宣告了他是一個配置檔

package test.configuration;



import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.ComponentScan;

import org.springframework.context.annotation.Configuration;

import org.springframework.web.servlet.ViewResolver;

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

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



/**
  * Created by Hsu on 2015/5/9.
  */

@Configuration

@EnableWebMvc

@ComponentScan(basePackages = "test")

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

}

上面這個配置檔，我們使用下面這個POJO AppInitializer 來載入。

package test.configuration;



import org.springframework.web.WebApplicationInitializer;

import org.springframework.web.context.support.AnnotationConfigWebApplicationContext;

import org.springframework.web.filter.CharacterEncodingFilter;

import org.springframework.web.servlet.DispatcherServlet;



import javax.servlet.FilterRegistration;

import javax.servlet.ServletContext;

import javax.servlet.ServletException;

import javax.servlet.ServletRegistration;



/**
  * Created by Hsu on 2015/5/9.
  */

public class AppInitializer implements WebApplicationInitializer {
     @Override
     public void onStartup(ServletContext servletContext) throws ServletException {
         AnnotationConfigWebApplicationContext ctx = new AnnotationConfigWebApplicationContext();
         ctx.register(AppConfig.class);
         ctx.setServletContext(servletContext);


         // 設定 UTF-8 強制轉碼
         FilterRegistration.Dynamic encodingFilter = servletContext.addFilter("encoding-filter",new CharacterEncodingFilter());
         encodingFilter.setInitParameter("encoding","UTF-8");
         encodingFilter.setInitParameter("foreEncoding","true");
         encodingFilter.addMappingForUrlPatterns(null, true, "/*");


         // 設置 servlet
         ServletRegistration.Dynamic servlet = servletContext.addServlet("dispatcher", new DispatcherServlet(ctx));
         servlet.setLoadOnStartup(1);
         servlet.addMapping("/");
     }

}

以上的環境配置相等於

<!-- 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>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>dispatcher</servlet-name>
     <url-pattern>/</url-pattern>

</servlet-mapping>

接下來是Dao 的部份，分三層來設計(abstract/interface/implements)，AbstractDao 中的 sessionFactory 對照到HibernateConfiguration

package test.dao;



import org.hibernate.Session;

import org.hibernate.SessionFactory;

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



/**
  * Created by Hsu on 2015/5/9.
  */

public abstract class AbstractDao {
     @Autowired
     private SessionFactory sessionFactory;


     protected Session getSession(){
         return sessionFactory.getCurrentSession();
     }


     public void persist(Object entity){
         getSession().persist(entity);
     }


     public void delete(Object entity){
         getSession().delete(entity);
     }

}

介面層

package test.dao;



import test.model.USER_PROFILE;



import java.util.List;



/**
  * Created by Hsu on 2015/5/9.
  */

public interface UserProfileDao {
     void saveUserProfile(USER_PROFILE user_profile);
     List<USER_PROFILE> findUserProfile(USER_PROFILE user_profile);
     void deleteUserProfileById(int id);

}

實現層

開頭使用@Repository 來指明他是一個 Repository

package test.dao;



import org.hibernate.Query;

import org.springframework.stereotype.Repository;

import test.model.USER_PROFILE;

import test.util.StringUtil;



import java.util.List;



/**
  * Created by Hsu on 2015/5/9.
  */

@Repository("userProfileDao")

public class UserProfileDaoImpl extends AbstractDao implements UserProfileDao {
     @Override
     public void saveUserProfile(USER_PROFILE user_profile) {
         persist(user_profile);
     }


     @SuppressWarnings("unchecked")
     @Override
     public List<USER_PROFILE> findUserProfile(USER_PROFILE user_profile) {
         String hql = "from test.model.USER_PROFILE user where 1=1 ";
         String queryStr = generateWhere(user_profile);
         Query query = getSession().createQuery(hql + queryStr);
         List<USER_PROFILE> returnValue = (List<USER_PROFILE>)query.list();
         return returnValue;
     }


     @Override
     public void deleteUserProfileById(int id) {
         Query query = getSession().createSQLQuery("DELETE FROM user_profile WHERE ID=" + id);
         query.executeUpdate();
     }


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

}

上面比較需要注意的是，我們使用了Session.createQuery(String str)的方式來建立查詢，這樣使用的是 HQL(The Hibernate Query Language) 的語法，詳細的使用介紹請參閱官網。

Ref: https://docs.jboss.org/hibernate/core/4.3/manual/en-US/html/ch16.html

再來是Service 層，使用(Interface/Implements)

package test.service;



import test.model.USER_PROFILE;



import java.util.List;



/**
  * Created by Hsu on 2015/5/9.
  */

public interface UserProfileService {
     void saveUserProfile(USER_PROFILE user_profile);
     List<USER_PROFILE> findUserProfile(USER_PROFILE user_profile);
     void deleteUserProfileById(int id);

}

由於我們的 Dao 都建立好了，所有的Service 只要去呼叫對應的 Dao 來做資料庫的存取，開頭使用@Service 來指明他是一個 Service

package test.service;



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

import org.springframework.stereotype.Service;

import org.springframework.transaction.annotation.Transactional;

import test.dao.UserProfileDao;

import test.model.USER_PROFILE;



import java.util.List;



/**
  * Created by Hsu on 2015/5/9.
  */

@Service("userProfileService")

@Transactional

public class UserProfileServiceImpl implements UserProfileService {


     @Autowired
     private UserProfileDao dao;


     @Override
     public void saveUserProfile(USER_PROFILE user_profile) {
         dao.saveUserProfile(user_profile);
     }


     @Override
     public List<USER_PROFILE> findUserProfile(USER_PROFILE user_profile) {
         return dao.findUserProfile(user_profile);
     }


     @Override
     public void deleteUserProfileById(int id) {
         dao.deleteUserProfileById(id);
     }

}

最後是Controller 的部份

裡面會使用 @Autowired 的方式將 Service 載入，開頭並使用 @Controller 來宣告他是一個 Controller

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.model.USER_PROFILE;

import test.service.UserProfileService;



import java.util.ArrayList;

import java.util.List;



/**
  * Created by Hsu on 2015/5/7.
  * Ref: Spring 4 MVC+Hibernate 4+MySQL+Maven integration example using annotations
  * http://goo.gl/J8Nlws
  */

@Controller

public class HomeController {
     @Autowired
     private UserProfileService service;


     @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){
         // 取得全部資料
         USER_PROFILE user_profile = new USER_PROFILE();
         List<USER_PROFILE> listUser = service.findUserProfile(user_profile);
         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 =  service.findUserProfile(user_profile);
         return new ModelAndView("home","listUser",listUser);
     }

}

application.properties

jdbc.driverClassName = com.mysql.jdbc.Driver

jdbc.url = jdbc:mysql://localhost:3306/test

jdbc.username = test

jdbc.password = test

hibernate.dialect = org.hibernate.dialect.MySQLDialect

hibernate.show_sql = true

hibernate.format_sql = true

StringUtil.java

package test.util;



/**
  * Created by Hsu on 2015/4/16.
  */

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

}

index.jsp

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

home.jsp

<%--
   Created by IntelliJ IDEA.
   User: Hsu
   Date: 2015/5/7
   Time: 下午 06:30
   To change this template use File | Settings | File Templates.

--%>

<%@ 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>Hibernate_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>

接著將其部屬在 Tomcat 就可以看到結果囉。

延伸問題，Dao 中若不使用 Session.createQuery 改使用 Criteria的方式來實現，下面為修改過的 UserProfileDaoImpl.java

package test.dao;



import org.hibernate.Criteria;

import org.hibernate.Query;

import org.hibernate.criterion.Criterion;

import org.hibernate.criterion.Junction;

import org.hibernate.criterion.LogicalExpression;

import org.hibernate.criterion.Restrictions;

import org.springframework.stereotype.Repository;

import test.model.USER_PROFILE;

import test.util.StringUtil;



import java.util.ArrayList;

import java.util.Iterator;

import java.util.List;



/**
  * Created by Hsu on 2015/6/9.
  */

@Repository("userProfileDao")

public class UserProfileDaoImpl extends AbstractDao implements UserProfileDao {
     @Override
     public void saveUserProfile(USER_PROFILE user_profile) {
         persist(user_profile);
     }


     @SuppressWarnings("unchecked")
     @Override
     public List<USER_PROFILE> findUserProfile(USER_PROFILE user_profile) {
         // 使用 Criteria 來實現查詢
         Criteria cr = getSession().createCriteria(USER_PROFILE.class);
         // 將查詢條件逐一整理
         generateWhere(user_profile, cr);
         List<USER_PROFILE> returnValue = (List<USER_PROFILE>)cr.list();
         return returnValue;
     }


     @Override
     public void deleteUserProfileById(int id) {
         Query query = getSession().createSQLQuery("DELETE FROM user_profile WHERE ID=" + id);
         query.executeUpdate();
     }


     private void generateWhere(USER_PROFILE user_profile, Criteria criteria){
         // 總查詢條件 使用 Junction 類別來組合
         List<Criterion> conditionList = new ArrayList<Criterion>();
         // 姓名查詢
         Criterion name = null;
         if(!"".equals(StringUtil.NulltoString(user_profile.getUserName()))){
             name = Restrictions.like("userName","%" + user_profile.getUserName() + "%");
             conditionList.add(name);
         }
         // 電話查詢
         Criterion phone = null;
         if(!"".equals(StringUtil.NulltoString(user_profile.getUserPhone()))){
             phone = Restrictions.eq("userPhone", user_profile.getUserPhone());
             conditionList.add(phone);
         }
         // 性別查詢
         Criterion sex = null;
         if(!"".equals(StringUtil.NulltoString(user_profile.getUserSex()))){
             sex = Restrictions.eq("userSex", user_profile.getUserSex());
             conditionList.add(sex);
         }
         // 將邏輯組合起來
         // Conjunction => And / Junction => OR
         Junction conditionGroup = Restrictions.conjunction();
         if(conditionList.size()>0){
             for(Iterator iterator = conditionList.iterator(); iterator.hasNext();){
                 Criterion criterion = (Criterion)iterator.next();
                 // 若有條件，則加入該集合
                 conditionGroup.add(criterion);
             }
         }
         criteria.add(conditionGroup);
     }

}

查詢條件 【姓名】 / 【電話】 / 【性別】是 AND(聯集) 的條件，在 Criteria 中要加上三個以上的查詢條件，可以使用 Juction 的實現類別 Conjuction 來完成，或是使用

criteria.add(Restrictions.and(Restrictions.and(condition1, condition2), condition3));

的方式來完成。

參考：

How to make a criteria query with 3 OR Criterions properly?

http://goo.gl/wn55gP

Hibernate - Criteria Queries

http://goo.gl/MWXV1d

張貼者： 於
標籤： , , , , , , ,

1 則留言 :

訂閱： 張貼留言 ( Atom )