2

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?

Improve this question

3 Answers 3

Reset to default
5

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.

Improve this answer
8
  • What is the difference SELECT * FROM... between SELECT id FROM.
    – user5182503
    Commented Jul 23, 2015 at 11:03
  • SELECT * will select all the properties of Person while SELECT id will select only the id.
    – cнŝdk
    Commented Jul 23, 2015 at 11:05
  • To tell the truth I don't understand because josql returns THE SAME OBJECTS we give to it.
    – user5182503
    Commented Jul 23, 2015 at 11:07
  • What do you mean by THE SAME OBJECTS?
    – cнŝdk
    Commented Jul 23, 2015 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.
    – user5182503
    Commented Jul 23, 2015 at 11:13
2

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

Improve this answer
0

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

Improve this answer