Friday, March 31, 2023

Spring Data JPA Query Methods

 Spring Data JPA Queries are a great way to create queries derived from the method name without needing to write tons of boiler-plate code to create connection & query, execute it and then process the result set. This generally works fine for simple queries and method names are very explicit in pointing out what is being returned by that method so the code is more readable and maintainable. To make this work successfully, the method name has to follow certain conventions:

  1. Method names usually start with the word "find"
  2. Filter clauses are added to the function name as "findBy<ColumnName>" which creates queries of the form "where <ColumnName> = ?"
  3. Multiple Filter clauses are supported using And / Or clauses 
    1. And can be used as "findBy<Col1>And<Col2>"  which creates the query "where <Col1> = ? and <Col2> = ?"
    2. Or can be used as "findBy<Col1>Or<Col2>"  which creates the query "where <Col1> = ? or <Col2> = ?"
  4. Similarly other operators are supported on the lines of Between, After, Before, LessThan, GreaterThan, Like, etc
  5. Ordering of the results can be delegated to the database by adding "OrderBy" to the function name as "findBy<Col1>OrderBy<Col2><Asc|Desc><Col3><Asc|Desc>"
Here is a handy table of supported keywords inside method names taken from the Spring Data documentation:

KeywordSampleJPQL snippet

Distinct

findDistinctByLastnameAndFirstname

select distinct …​ where x.lastname = ?1 and x.firstname = ?2

And

findByLastnameAndFirstname

… where x.lastname = ?1 and x.firstname = ?2

Or

findByLastnameOrFirstname

… where x.lastname = ?1 or x.firstname = ?2

IsEquals

findByFirstname,findByFirstnameIs,findByFirstnameEquals

… where x.firstname = ?1

Between

findByStartDateBetween

… where x.startDate between ?1 and ?2

LessThan

findByAgeLessThan

… where x.age < ?1

LessThanEqual

findByAgeLessThanEqual

… where x.age <= ?1

GreaterThan

findByAgeGreaterThan

… where x.age > ?1

GreaterThanEqual

findByAgeGreaterThanEqual

… where x.age >= ?1

After

findByStartDateAfter

… where x.startDate > ?1

Before

findByStartDateBefore

… where x.startDate < ?1

IsNullNull

findByAge(Is)Null

… where x.age is null

IsNotNullNotNull

findByAge(Is)NotNull

… where x.age not null

Like

findByFirstnameLike

… where x.firstname like ?1

NotLike

findByFirstnameNotLike

… where x.firstname not like ?1

StartingWith

findByFirstnameStartingWith

… where x.firstname like ?1 (parameter bound with appended %)

EndingWith

findByFirstnameEndingWith

… where x.firstname like ?1 (parameter bound with prepended %)

Containing

findByFirstnameContaining

… where x.firstname like ?1 (parameter bound wrapped in %)

OrderBy

findByAgeOrderByLastnameDesc

… where x.age = ?1 order by x.lastname desc

Not

findByLastnameNot

… where x.lastname <> ?1

In

findByAgeIn(Collection<Age> ages)

… where x.age in ?1

NotIn

findByAgeNotIn(Collection<Age> ages)

… where x.age not in ?1

True

findByActiveTrue()

… where x.active = true

False

findByActiveFalse()

… where x.active = false

IgnoreCase

findByFirstnameIgnoreCase

… where UPPER(x.firstname) = UPPER(?1)


Saturday, January 21, 2023

Piping output of top through grep does not give any output

Recently I was trying to grep for "load average" in the output of top and then redirect it to a file to save for later analysis - however the output file would be totally empty for quite some time. That is the time I realized that grep was buffering its output causing the output file to be empty. So the grep command was not writing to the file immediately but was collecting large amounts of data in its buffer before flushing that out to the file.

My original command was

top -b -u myusername -d 5 | grep -i 'load average' > /tmp/load_averages.log

Because this did not write anything to the output file for quite a while, I had to change it buffer only till it saw a newline in the output and then to flush out whatever it has gathered so far in its buffer as below:

top -b -u myusername -d 5 | grep -i 'load average' --line-buffered > /tmp/load_averages.log

And lo behold, tailing the output file shows the load averages being printed every 5 seconds (because of the -d 5 flag given to the top command)

LinkWithin

Related Posts Plugin for WordPress, Blogger...