Separating SQL from Java Code

Question

This is a problem that I always encounter when I need to connect to a database; How to separate SQL from normal Java code? Usually I use a separate class to connect to the database, however, when each database has several databases and several tables, it is always difficult to do this 100%

As an example, if we want to put the entire Java class in a class called DBConnector.java, how will we generate common code for various attachments, deletions, data extraction, etc.? What I consider as an ideal case is that all SQL statements should be in the same class and must be compatible with different variants of the same operation within the database application, thereby ensuring logical separation from the rest of the code.

public void insertData (String db, String table, <Whatever Fields to be Inserted>) { //generic SQL INSERT statement and execution } public ResultSet retrieveData (String db, String table, <Whatever Fields Relevant>) { //generic retrieval of data } 

Is there any way to do this? Or we just add functionality for different tastes of Inserting, Querying, etc.

Thanks!

Answer 1

If you need sound architecture, you will need at least a few layers to sort out the problems.

First of all, start with the model classes (in most cases you will use them for each table in your database). Write them yourself or create them automatically using ORM (e.g. EclipseLink, Hibernate). These must be POJOs (plain old Java objects), which means that they are simple objects with properties (for example, Name type String, Id type integer, etc.). Your model objects must be data carriers and nothing more (of course, no logic or processing).

Then create DAOs (data access objects) for all model classes (you can build the GenericDao class for inheritance if you want). Here you will provide CRUD operations (insert, update, delete) using methods that take model objects as arguments. It is a database-specific database, although you can insert an attribute DAO-level database if you want.

Thirdly, to have a service or manager level for each logical group of classes (this is the layer on which all interfaces and controller code must speak for all the desired functions). A typical method can be called registerCustomer(...) (which can use different DAO classes). Or findCustomerByName() , etc.

Structuring your application in this way is called MVC (Model - View - Controller), so the term is for google for if you want more information.

Thus, you will usually not have SQL queries that exceed the DAO level, which means that your application is a) supported and b) it is easier to change servers later.

Answer 2

The best approach is to use Hibernate , which is now the industry standard.

In a nutshell, it generates an SQL query, and the code deals with Java objects representing strings. If you call setters, Hibernate calculates the SQL necessary to complete the update.

For getters, your code might look like this:

 shoppingCart.getCustomer().getCountry().getCode(); 

And hibernate calculates the SQL connections needed to go from the shopping_cart table to the country table through the customer table.

It's really awesome and worth a switch on.

Answer 3

Izza, here we discuss the separation of SQL from java code: Java - saving SQL queries in an external file Your solution is clear, but it will have problems if the queries are not standard (for example, not only where a = 10, but also contains in (...) or a group, so I suggest you avoid this. To minimize boilerplate code when working with a database in Java, you should use Spring JDBC. You can also use Hibernate, if this is acceptable in your case, this allows you avoid some sql customs.

Answer 4

You should use some DAOFactory , this class is used to get connections. To display tables in a database, you must create DTO - Data Tranfer Objects representing entities. Therefore, if you have a User table, simply create UserDTO.java with attributes and getters and setters. And the class for communicating with the database is DAO - Data Access Object . Here you should only create SQL statutes and methods to retrieve data from your database. Well designed structure first . Then your code becomes cleaner, faster and more secure. I recommend you create your own ORM . So take a look and check out a few frames

---

EasyORM

 double count = 0; TransDB trans = new TransDB() ; List<Trans> list = new ArrayList<Trans>(); list = trans.getAll(); for (Trans element : list) { count+= element.getData(); } ... 

Hibernate

 double count = 0; Session session = null; List<Trans> list = new ArrayList<Trans>(); list = HibernateUtil.getSessionFactory().openSession(); list = (List<Trans>) session .createQuery("from Trans").list(); for (Trans element : list) { count += element.getData().doubleValue(); } ... 

and compare

Rating (in ms)

EasyORM: MySQL: init - 6344, avg - 4868 MS SQL: init - 8126, avg - 6752

Hibernate: MySQL: init - 27406, avg - 23728 MS SQL: init - 28605 (+ 250%), avg - 24912

So, your own ORM , actually created from an SQL script , has an order faster than Hibernate (up to 10) and why ? Insertion between layers, of course, cannot go to increase throughput. This is just one test, I have others. Therefore, for me, I recommend that you create your own ORM , there are also some disadvantages here, such as consuming time, for example, or using problematic DMS changes, but the advantages are as complete control over the generated commands, you can use functions specific to a specific DMS (ORDM , special teams, etc.). Therefore, I do not think that Hibernate is the best, actually not.