Mechanism similar to SQL to filter and sort objects

Question

Firstly,let me say, that the example I show here is very simple and very far from the real situation. However this example shows the core of the problem well enough.

I have a class Person

public class Person{
private int Id;
private String firstName;
private String lastName;
private Date birthDate;
private int age;
//+ getters and setters
}

And I have for example list of 2000 java(!) objects of this class. I mean I have 2000 persons. Now I need to filter and sort them using sql query. For example I would like to get the persons from these 2000 WHERE birthDate BETWEEN 1920-04-05 AND 2000-08-10 ORDER BY lastName,firstName.

I understand that sql language is part of sql database.s So I could save my objects to mem database,for example H2, after that do select with the necessary WHERE and ORDER clauses. However, the problem is that I this case lose performance as I already have these objects. What I need is just to get the list of the ID of these objects and that's all as I get the interval and order.

So my question - are there any solutions (libs etc) to make filtering and sorting on java objects including all common types? Maybe there are some,that even support sql language?

cнŝdk · Accepted Answer · 2015-07-23T11:23:54.057

6

You can do it with JoSQL:

JoSQL (SQL for Java Objects) provides the ability for a developer to apply a SQL statement to a collection of Java Objects. JoSQL provides the ability to search, order and group ANY Java objects and should be applied when you want to perform SQL-like queries on a collection of Java Objects.

This is the query you need:

SELECT id
FROM   package.Person
WHERE  toDate (birthDate) BETWEEN toDate ('1920-04-05') 
AND    toDate ('2000-08-10')

And this is how to use it:

Query q=new Query();
q.parse("SELECT id FROM   package.Person WHERE  toDate (birthDate) BETWEEN toDate ('1920-04-05') AND  toDate ('2000-08-10')");
List<?> results=q.execute(names).getResults();

EDIT:

This is how you will fetch this results:

List<?> results=q.execute(names).getResults();
Iterator<List> it = results.iterate();
while(it.hasNext()){
    List ls = it.next();
    System.out.println("Id: "+ls.get(0));
}

Because you will get a List of Lists as a result, where each List will contain an id value.

You can follow this JoSQL tutorial.

edited Jul 23 '15 at 11:23

answered Jul 23 '15 at 09:20

cнŝdk

28,676
7
47
67

What is the difference `SELECT * FROM...` between `SELECT id FROM`. – Pavel_K Jul 23 '15 at 11:03
`SELECT *` will select all the properties of `Person` while `SELECT id` will select only the id. – cнŝdk Jul 23 '15 at 11:05
To tell the truth I don't understand because josql returns THE SAME OBJECTS we give to it. – Pavel_K Jul 23 '15 at 11:07
What do you mean by THE SAME OBJECTS? – cнŝdk Jul 23 '15 at 11:11
When you call `q.execute(names)` names is a collection of Objects. JoSql as I understood doesn't create new objects if I just use filtering and sorting. JoSQL returns you the same objects that were in list but applying filtering and sorting rules. – Pavel_K Jul 23 '15 at 11:13
You didn't understand me at all. – Pavel_K Jul 23 '15 at 11:18
@JimJim2000 Yes you are right about it, take a look at the EDIT in my answer, it's all explained. – cнŝdk Jul 23 '15 at 11:25
Thank you. I think then the difference is that `*` returns the same objects and `id` will return the ids of that objects. – Pavel_K Jul 23 '15 at 12:01

score 3 · Answer 2 · answered Jul 23 '15 at 09:18

3

http://josql.sourceforge.net/ looks like a possibility to do exactly that.

answered Jul 23 '15 at 09:18

Florian Schaetz

9,670
3
27
50

Adrian · Answer 3 · 2017-12-09T11:51:11.440

Why not use Java 8 Stream API?

 List<Integer> ids = listOfPerson.stream()
            .filter(person -> person.getBirthDate().isAfter(from) && person.getBirthDate().isBefore(to))
            .sorted(Comparator.comparing(Person::getLastName).thenComparing(Person::getFirstName))
            .map(Person::getId)
            .collect(Collectors.toList());

I believe JoSQL or other libraries like it are using reflection behind, so it won't be very performant. And what happens when you change name of a field? Also, in Stream API you can use parallel processing as easy as listOfPerson.parallelStream()

For more info: Use stream operations to express sophisticated data processing queries

Mechanism similar to SQL to filter and sort objects

3 Answers3