CODEWITHSUNDEEP
hibernategrailsgrails-ormhibernate-criteria
Apoorv
Apoorv

Reputation: 620

Cast String Column to Long grails createCriteria

I have a domain class in grails which has a column of string type. Below is the domain class structure.

class TravelRequestUpdates{
        updatedAt sqlType:'varchar(40)'

String updatedAt


}

I store epoch dates in this column. Using createCriteria i want to perform a between check on this column for long type values but am unable to do so. Is there a way i can store values in this column as String and still perform Long type operation on the values stored. Below is the code for my criteria where i want that the values stored in this table to behave as long type. The dates are not being compared.

              def criteriaObj = TravelRequestUpdates.createCriteria()

def travelToShow = criteriaObj.get{

                    @Formula("cast(updatedAt as NUMBER(20,0))")
                    long tempUpdatedAt

                    tr{
                      eq('id',g.id)
                    }
                    and{
                      between(tempUpdatedAt, startTimeReport.time, endTimeReport.time)
                    }
                    and{
                      newStatus{
                        eq('id',completedTravelsStatus.id)
                      }
                    }
                  }

I want to generate a query like the one below. The below query gives me the desired result.

SELECT *
FROM travel_request_updates
WHERE tr_id = '439'
AND updated_at BETWEEN ('1478016321000') AND('1478189121000')
AND new_status_id = '4'

Upvotes: 2

Views: 1376

Answers (1)

V H
V H

Reputation: 8587

Sorry my comment won't solve it since I think that is your input range.

There are a few options

  1. Enable a transient object of Long in your domain class that converts the underlying string to Long so:

    class TravelRequestUpdates{
 String updatedAt
 Long myConversion

 static transients = [ 'myConversion' ]


 Long getMyConverion() {
  return (this.updatedAt as Long)
 }
}

  2. Would be to write it in something like HQL and possibly cast the object:

    String query = "FROM travelRequestUpdates
WHERE tr_id = :myid
AND cast (updatedAt as Long) BETWEEN (:input1) AND(:input2)
AND newSatusId = :status"
def myParams=[status: '4', input1: 1478016321000L, input2: 1478189121000L, myId: 439L]
def myList = TravelRequestUpdates.execute(query,myParams,[readonly:true])

L has cast them as Long values you can Declare long values if you prefer, alternatively you can run a sql query directly if all of that fails.

Upvotes: 2

Related Questions