4

Question

In some projects of mine I used SQL code, this is nothing special. But I was rethinking this strategy. I searched SO for something to separate my code and SQL statements.

I found one answer which I liked, Collecting all your SQL statements in a properties file. I think it's nice and clean. But what if you want to insert variables.

Another solution I found was the use of DAO objects. I you for instance create a MYSQLFactory class with the following methods: addCustomer(Customer customer), deleteCustomer(int CustomerId) etc. You would have centralized your SQL code. Still wouldn't call it separated.

Is there a better way to do this without using ORM's like Hibernate. The more I read, the more I become convinced that there isn't a neat way of doing this with JAVA.

Searched Sources

Java - Storing SQL statements in an external file

Separating SQL from the Java Code

Update

Thanks for the great responses. I was just curious about this. @Gilbert, I think it's a great solution, As a matter of fact I already used your solution at one time, so thanks for that. :)

Sometimes I just like to research new approches to certain problems.

Community
  • 1
  • 1
TrashCan
  • 817
  • 4
  • 13
  • 20
  • 2
    "there isn't a neat way of doing this with JAVA"? Have you found neat approach with any other language? If so, you could try re-engineering that approach. – kosa Jan 29 '13 at 14:49
  • This answer is what I do. What is wrong with this approach? http://stackoverflow.com/a/10873022/300257 – Gilbert Le Blanc Jan 29 '13 at 14:55
  • Personally I say maximum you can do is separating it in properties file, and if you want to substitute variables instead of using "?", the your query is subjected to injection attacks. – Pradeep Simha Jan 29 '13 at 14:55
  • @GilbertLeBlanc there is nothing wrong with that approach. But your are making your own orm at that point. The only difference between what you are doing and any other orm is you are generating the code by hand. There isnt any logical difference. – gh9 Jan 29 '13 at 14:57

4 Answers4

2

You don't state what your goals are. I'm not sure if you are just looking for a technical solution to put all SQL code in one place or advice on how to manage applications across multiple languages.

For managing SQL code in another language, the following are your friends:

  • Stored Procedures
  • Views

The following are not your friends:

  • Ad hoc queries

The issue is the "API" to the database. SQL is the interface to raw data tables. However, typically the database is a component of an application that should have its own API. This layer should, in general, consist of views and stored procedures. So, for instance, updates and inserts (which are ad hoc queries) should be wrapped in stored procedures.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

No, there isnt. If you do not use an ORM you will need to make direct SQL calls somewhere in your code. If you start obfuscating the sql calls out in a factory pattern you are just making a bad ORM.

gh9
  • 10,169
  • 10
  • 63
  • 96
1

I personally prefer to have the SQL query in the Java file where it's executed. That makes it easier to maintain the code: no need to switch between several files.

That said, storing the SQL queries in an external file (properties or XML or whatever) is very simple. If you need to insert a variable, you would use a prepared statement anyway, so your SQL would look like

select foo.* from Foo foo where foo.id = ?

The only difficulty is with the SQL queries that are dynamically generated (fom a set of optional search criteria for example). But there are APIs that allow generating this kind of SQL queries without concatenating parts of a query.

JB Nizet
  • 678,734
  • 91
  • 1,224
  • 1,255
0

Regarding your question:
There is no ideal way, there are several solutions:
A. Use ORM - not sure why you don't want it.
B. Use a properties file as you suggested - regarding parameters,
You will need to handle it, consider using some framework to perform the parameter substitution for you.
C. You can abstract some of your SQL queries with stored procedures/views layer at your DB.

Yair Zaslavsky
  • 4,091
  • 4
  • 20
  • 27