This week's book giveaway is in the OO, Patterns, UML and Refactoring forum.
We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line!
See this thread for details.
The moose likes JDBC and Relational Databases and the fly likes Does this SQL string need a subquery? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Does this SQL string need a subquery?" Watch "Does this SQL string need a subquery?" New topic

Does this SQL string need a subquery?

Jacky Luk
Ranch Hand

Joined: Aug 02, 2012
Posts: 631
Str = "select p.Patient_First_Name, p.Patient_Last_Name, h.Hospital_Name from patient p LEFT JOIN hospital h ON p.Hospital_ID = h.Hospital_ID "
+" where CONCAT(p.Patient_First_Name, CONCAT(' ', p.Patient_Last_Name)) LIKE '%" + p.getPatient().getPatientName().trim() + "%'";

chris webster

Joined: Mar 01, 2009
Posts: 2204

Who knows? Your query looks like a simple join, so a sub-query shouldn't be necessary, but it depends what you want your query to do, doesn't it? However, it does need a PreparedStatement, as it's not a good idea to build your query with concatenated string parameters like this (Google "SQL injection").

Here's a general way to make sure your SQL is correct before you get it all tangled up in your Java code:

  • Take your SQL and run it in your database's SQL shell e.g. SQL*Plus if you're on Oracle.
  • Does it give you the results you expect?
  • If it does then you know your SQL is correct, so now you can put your SQL into your Java code.
  • If it doesn't give you the right results, fix the SQL and run it again in your SQL shell. Do this until you get the right results, then put your SQL in your Java code.
  • Also, pick a coding convention and stick to it e.g. upper-case for all SQL keywords.

  • No more Blub for me, thank you, Vicar.
    I’ve looked at a lot of different solutions, and in my humble opinion Aspose is the way to go. Here’s the link:
    subject: Does this SQL string need a subquery?
    It's not a secret anymore!