• Post Reply Bookmark Topic Watch Topic
  • New Topic

Insights on database programming  RSS feed

 
Jon Camilleri
Ranch Hand
Posts: 664
Chrome Eclipse IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"Some Java programmers avoid complex SQL statements such as this one. A surprisingly common, but very inefficient, workaround is to write lots of Java code that iterates through multiple result sets. But the database is a lot better at executing query code than a Java program can bethat's the core competency of a database.

A rule of thumb: If you can do it in SQL, don't do it in Java."
Sourced from Core Java Volume 2 (7th Edition), Chapter 4

Have you encountered instances of the mentioned workaround?
This is purely for historic fascination. Does it relate to JavaDB?

Is it possible to use DataSource, to take advantage of connection pooling and distributed transactions? Would you have an example? How does DataSource fit into an architectural diagram?




 
Jesper de Jong
Java Cowboy
Sheriff
Posts: 16060
88
Android IntelliJ IDE Java Scala Spring
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Some programmers are good at writing Java code, but not at writing complicated SQL queries, so what they do is write multiple simpler SQL queries and then try to write Java code to find the results out of the query results.

For example, suppose someone wants to get records from two tables that have the same value in some column. Someone who doesn't understand SQL joins might be tempted to get all rows from the first table and all rows from the second table, and then write Java code to find out which ones have the same value for the specific property. That's super-inefficient. Instead of doing something like that, you should write an SQL query that does the joining. The database is especially made for such queries so it will most likely be able to do it in a much more efficient way than hand-written Java code, and also would only the actually desired result need to be sent from the database to the Java program instead of the complete content of the two tables.

This question does not have anything to do with JavaDB in particular, but with programmers who aren't good at SQL.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!