1

Lets say I have a table with 2 columns:

  1. city
  2. name (of a person).

I also have a Java "city" object which contains:

  1. city name
  2. a list of all the people in that city

So now I have two options to get the data:

  1. First use DISTINCT to get a list of all the cities. Then, for each city, query the database again, using WHERE to get only records where the person lives in that city. Then I can store this in a City object.
  2. Get a list of all the data, using ORDER BY to order by the city name. Then loop through all the records and start storing them in City objects. When I detect that the city name changes then I can create a new City object and store the records in that.

Which of these methods is faster / better practice? Or is there some better way of getting this information than these two methods? I am using Oracle database.

Improve this question
6
  • You are mixing ORM and raw SQL stuff here, and I can't follow your question. What are you trying to do exactly? Commented Jul 26, 2018 at 6:18
  • 1
    First I would recommend making this kind of stuff with SQL, but why would you make multiple queries to get your results ? It seems feasible with one query Commented Jul 26, 2018 at 6:21
  • Definitely #2. Note that you can use Map in the Java code, eliminating the need for ORDER BY. Commented Jul 26, 2018 at 6:33
  • @Henkan Question says DISTINCT, WHERE, and ORDER BY, indicating that question is about SQL, so saying "I would recommend making this kind of stuff with SQL" is kind of redundant. --- Also, OP already talks about 1-query solution in #2, so saying "It seems feasible with one query" is kind of redundant. --- Your comment is redundant, and doesn't eveen attempt to answer the question: Which is better? Commented Jul 26, 2018 at 6:35
  • @Andreas I guess my comment was unclear : I know it will use SQL, I just meant that to me, if it possible of course, making a query which returns the exact desired result is better than getting some data and then process this data in code. So that's why I recommend SQL. Commented Jul 26, 2018 at 6:44

3 Answers 3

Reset to default
2

A database query is a relatively expensive operation - you need to communicate with another server over the network, it then may need to access its disk, compute a result, return it to you, etc. You'd want to minimize these as much as possible. Having a single query and going over its results is by far a better idea than having multiple queries, unless you have some killer reason not to do so - which doesn't seem to be the case here, at least not from the information you shared.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Sort answer is #2. You wish to make as less queries to the database as possible. #2 if i got it correct you will make a join of city/people and then create the object.

Better way: Use JPA/Hibernate. i.e check http://www.baeldung.com/hibernate-one-to-many

Improve this answer

4 Comments

JPA/Hibernate is not a better way.
This is opinion i would guess. I think it is. Can you explain why it is not?
Because adding JPA/Hibernate for just the problem described in the question is overkill. --- Also, I don't believe defining a mapping of CREATE TABLE city_person ( city_name VARCHAR(50), person_name VARCHAR(50) ) into a List<City> with class City { String name; List<String> personNames; } is that straight-up.
The key words here is 'you believe' and 'you dont believe'. In my opinion, for modern java projects bestaproach is following JPA because my guess is that the application will not simply have two entities. Following the map aproach you mentioned may soon lead him to the need to use custom comparators and collators since string comparison in java will be made lexicography. Also as his data grow he may need pagination. I would strongly believe conrrary to you that JPA is improvement for modern applications especially since in the end of the day he wishes to have java pojos.
0

Answer number #2 is optimal, in all cases.

You'll need to code the logic in Java to differentiate when you change from one city to the next one.

Alternatively, if you were using MyBatis the solution becomes very simple by using "collections". These perform a single database call and retrieve the whole Java tree you specify, including all sublists in multiple levels. Very performant and also easy to code.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.