grails标准：如何选择mysql中的所有列，并同时格式化日期然后使用 [英] grails criteria :How to select all columns in mysql and at the same time format the date then use like

问题描述

我目前正在为我的网页创建搜索功能。数据将来自数据库。

在HTML中，日期以这种格式显示 dd / mm / yyyy 。我想要做的是，在select查询中，我想选择所有列，并且应该使用该格式显示日期，并且我有一些类似的条件将用于检查是否存在匹配。 p>

如何选择所有列，格式日期并同时使用标准中的单个语句检查所有匹配？

$ b

  searchString = searchString.toLowerCase（）$ b $这是我迄今为止所做的，但是这是错误的。 b 
 def employeeSearchCri = Employee.createCriteria（）; 
 def searchEmployeeList = employeeSearchCri.list（）{
或{
 ilike（employeeNo，％+ searchString +％）
 ilike（firstName， ％+ searchString +％）
 ilike（lastName，％+ searchString +％）
 ilike（middleName，％+ searchString +％）
 ilike（jobPosition，％+ searchString +％）
 ilike（date_format（hireDate，'％d /％m /％Y'），％+ searchString + ％）
 ilike（status，％+ searchString +％）
} 
和{
 eq（companyId，companyId）
 } 
} 
 return searchingEmployeeList; 
  

解决方案

我推荐的第一件事是将所有 ilike s除外。选择任何一个。重点在于让他们中的一个人工作。然后，然后添加另一个 ilike 。

其次，确认您使用的模式（例如％SEARCHSTRING％）对于您使用的数据库的LIKE操作符。

第三，一旦你得到它的工作，你会发现，不幸的是它不会像你想要的那样工作。例如，多词搜索不适用于您的方法。搜索很难。我工作得很好，但男孩真的很痛苦。如果你想让它正确完成，最好让专业人员处理它，比如使用。

匹配日期

也就是说，处理日期的最佳方法是将其转换为日期对象并匹配。您可以使用像这样的东西：

  import java.text.SimpleDateFormat 
 
 def searchDate 
 
 try {
 searchDate = new SimpleDateFormat（'dd / MM / yyyy'）。parse（searchString）
} catch（java.text.ParseException e）{} 
 
 ... 
 
或{
 ... 
 if（searchDate）eq（hireDate，searchDate）
} 
  





小费





您可以重新编写 ilike s通过使用 GString s：

  ilike（employeeNo，％$ searchString％）
  

如果您创建了要使用 ilike 的列的列表，则可以简化查询。这是一个完整的例子：

  searchString = searchString.toLowerCase（）
 
 def columns = [
employeeNo，
firstName，
lastName，
middleName，
jobPosition，
status
] 
 $ b $ def searchDate 
 
 try {
 searchDate = new java.text.SimpleDateFormat（'dd / MM / yyyy'）。parse（searchString）
 } catch（java.text.ParseException e）{} 
 $ b $ def employeeSearchCri = Employee.createCriteria（）; 
 def searchEmployeeList = employeeSearchCri.list（）{
或{
 columns.each {ilike（it，％$ searchString％）} 
 if（searchDate）eq（ （companyD，searchDate）
} 
 
和{
 eq（companyId，companyId）
} 
} 
 
 return searchingEmployeeList 
  

I'm currently creating a search functionality for my page. The data will be coming from DB.

In the HTML, the date is displayed in this format dd/mm/yyyy. What I want to do is, in the select query, I want to select the all columns and the date should be displayed using that format and I have a a couple of like conditions that will be used to check if there is a match.

How will I select all column, format date and at the same time check for all matches using one single statement in criteria?

Here's what I've done so far, but this wrong.

 searchString = searchString.toLowerCase() 

        def employeeSearchCri = Employee.createCriteria();
        def searchedEmployeeList = employeeSearchCri.list(){
            or {
               ilike("employeeNo", "%" + searchString + "%")
               ilike("firstName", "%" + searchString + "%")
               ilike("lastName", "%" + searchString + "%")
               ilike("middleName", "%" + searchString + "%")
               ilike("jobPosition", "%" + searchString + "%")
               ilike("date_format(hireDate, '%d/%m/%Y' )", "%" + searchString + "%")
               ilike("status", "%" + searchString + "%")
            }
            and{
                eq("companyId",companyId)
            }
        }
        return searchedEmployeeList;     

解决方案

The first thing I recommend is to comment out all of the ilikes except for one. Pick any one. The point is to focus on getting just one of them to work. Then, and only then, add another ilike.

Second, confirm that the pattern your using (ex. %SEARCHSTRING%) is valid for the LIKE operator of the database your using.

Third, once you get it working, you'll learn that unfortunately it won't work as well as you'd like. For example, multi-word searches won't work with your approach. Searching is hard. I got it working quite well, but boy was it a pain. If you want it done right, it's best to let the pros handle it, such as by using Apache Lucene.

Matching the date

That said, the best way to handle the date is to convert it into a Date object and to match on that. You can use something like this:

import java.text.SimpleDateFormat

def searchDate

try { 
    searchDate = new SimpleDateFormat('dd/MM/yyyy').parse(searchString)
} catch (java.text.ParseException e) { }

...

or {
    ...
    if(searchDate) eq("hireDate", searchDate)
}

Tip

You can re-write your ilikes in a more concise form by using GStrings:

ilike("employeeNo", "%$searchString%")

And if you build a list of the columns you want to use ilike on, you can simplify your query. Here's a complete example:

searchString = searchString.toLowerCase()

def columns = [
    "employeeNo",
    "firstName",
    "lastName",
    "middleName",
    "jobPosition",
    "status"
]

def searchDate

try { 
    searchDate = new java.text.SimpleDateFormat('dd/MM/yyyy').parse(searchString)
} catch (java.text.ParseException e) { }

def employeeSearchCri = Employee.createCriteria();
def searchedEmployeeList = employeeSearchCri.list() {
    or {
        columns.each { ilike(it, "%$searchString%") }        
        if(searchDate) eq("hireDate", searchDate)
    }

    and{
        eq("companyId",companyId)
    }
}

return searchedEmployeeList

这篇关于grails标准：如何选择mysql中的所有列，并同时格式化日期然后使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！