Geek Answers Handbook

Separating SQL from JAVA Code

Question

In some of my projects I used SQL code, this is nothing special. But I rethought this strategy. I was looking for SO to separate code and SQL.

I found one answer that I liked, collecting all your SQL statements in a properties file. I think it is beautiful and clean. But what if you want to insert variables.

Another solution I found was to use DAO objects. For example, I create the MYSQLFactory class in the following ways: addCustomer (client client), deleteCustomer (int CustomerId), etc. You would centralize your SQL code. Still will not call it divided.

Is there a better way to do this without using ORM like Hibernate. The more I read, the more I become convinced that there is no clear way with JAVA.

Source sources

Java - saving SQL statements in an external file

Separating SQL from Java Code

Update

Thanks for the great answers. I was just wondering. @ Gilbert, I think this is a great solution. In fact, I already used your solution at a time, so thanks for that. :)

Sometimes I just like to research new problems with specific problems.

+4
source share
4 answers

No no. If you are not using ORM, you will need direct SQL calls in your code. If you start confusing sql calls in a factory pattern, you are just doing a bad ORM.

+2
source

You do not indicate what your goals are. I'm not sure if you're just looking for a technical solution to put all the SQL code in one place or advice on managing applications in several languages.

To manage SQL code in another language, your friends:

  • Saved Procedures
  • representation

The following are not your friends:

  • Special Requests

The problem is the "API" for the database. SQL is an interface to raw data tables. However, as a rule, the database is a component of the application, which must have its own API. This layer should, in general, consist of views and stored procedures. So, for example, updates and inserts (which are special requests) should be wrapped in stored procedures.

+2
source

I personally prefer to have the SQL query in the Java file where it is executed. This simplifies code maintenance: no need to switch between multiple files.

However, storing SQL queries in an external file (properties or XML or something else) is very simple. If you need to insert a variable, you would still use a prepared statement, so your SQL would look like

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

The only difficulty is that SQL queries are dynamically generated (for example, a set of additional search criteria). But there are APIs that allow you to generate such SQL queries without concatenating parts of the query.

+1
source

Regarding your question:
There is no ideal way, there are several solutions:
A. Use ORM - you do not know why you do not want it.
B. Use the properties file as you suggested - regarding the options,
You will need to deal with this, think about how to use some framework to perform parameter replacement for you.
C. You can abstract some of your SQL queries using the stored procedure / view level in your database.

0
source

More articles:


All Articles