3 Common SQL Mistakes You are Probably Making as a Java Developer

A regular work week for Java developers might see them coding to develop a scalable web application, creating a dynamic website, developing efficient eCommerce product pages or probably developing an Android app. Yes, more often than not, they will be working on diverse projects, but with one common thread – coding.

Their career demands long working hours and extensive knowledge of coding.

Java developers also need to understand project requirements, design and develop a prototype for the assigned project, keep themselves updated with the latest changes happening in the programming industry and also have basic knowledge of other languages like HTML, SQL and many more!

Professional life for Java developers is not a bed of roses and they have to try and meet each and every challenge head on. Even the best Java developers, at times, fall prey to some mistakes that could be avoided. Take for example the mistakes they make while writing SQL. These can definitely be avoided.

Here, we highlight some of the common mistakes that a Java developer makes while coding in SQL.

SELECT TOP 3 common_mistakes FROM SQLProgrammingMistakes;

The result of the above query is as follows:

1.  Not Using Batch Update

Forgetting about Batch is the biggest and most common mistake a Java developer can make, when writing SQL.  It is not a good idea to INSERT thousands of records in a table one by one every single time. Rather a developer should create a batch INSERT statement with a single SQL statement and different sets of bind parameters. The speed of executing the INSERT in Batch is much more than executing each INSERT one by one.

Let’s understand this with an example:

String [] queries = {

  “INSERT INTO Employee { Eno, Ename, Ecode, EDept} values (‘1’, ‘Allen’, ‘abc’, ‘Sales’)”,
  “INSERT INTO Employee { Eno, Ename, Ecode, EDept} values (‘2’, ‘Max’, ‘102’, ‘Marketing’)”,
  “INSERT INTO Employee { Eno, Ename, Ecode, EDept} values (‘3’, ‘Ward’, ‘xyz’, ‘Sales’)”,
  “INSERT INTO Employee { Eno, Ename, Ecode, EDept} values (‘4’, ‘Sam’, ‘55’, ‘Marketing’)”,

};

Connection  connection = new getConnection();
Statement statement = connection.createStatement();
for (String query : queries ) {
  statement.execute(query);
}
statement.close();
connection.close();  

This is a bad code. You are executing each query separately. This hits the database for each insert statement.

Rather than sending each query one at a time and waiting for each one to finish, send a batch of inserts to the database in one go:

import java.sql.Connection;

import java.sql.Statement;

//…

Connection connection = new getConnection();
Statement statement = connection.createStatement();
For (Employee employee: employees){
String query = “INSERT INTO Employee (Eno, Ename, Ecode, Edept) values (‘ “ + Employee. getEno()  + “’, ‘” + Employee.getEname() +“’, ‘” + Employee.getEcode() + “’, ‘” + Employee.getEdept() + “’)”; 
statement.addBatch(query);
}

statement. executeBatch();

statement.close();

connection.close();

Batching is very important when inserting large data sets. To get significant performance improvement, whenever possible, a programmer must run a single statement in batch mode. The other way to execute batch inserts is to use PreparedStatement object. Batch Update is not limited to Insert statement only; you can use it for Update and Delete statement as well.

2.  Not Optimizing Queries for DBMS Engine

Not all Java developers know SQL well. In SQL, a query can be written in multiple ways to bring in the same results, but developers should always follow the best practices for getting the fastest response.

For instance, if a Java programmer is asked to retrieve all employees from the Employee table whose name starts with ‘A’, they will generally prefer to use the LEFT function to return the first character of employee’s name:

SELECT Ename FROM Employee WHERE LEFT (Ename,1) = ‘A’;

But this is incorrect. In the above query, the database system will scan the entire table to locate the desired information.

It does not make use of index, thereby, taking a lot of time in executing the query. Instead, a programmer should retrieve results using query:

SELECT Ename FROM Employee WHERE Ename LIKE ‘A%’;

The above query will utilize index to retrieve the data quickly and efficiently. So, in general, developers should use sargable predicates wherever possible. A query is said to be sargable, if the DBMS engine can take advantage of an index to speed up the execution of the query.

It is better to ensure all your queries are sargable as non-sargable queries have adverse effect on query time. So, ensure you optimize your queries for DBMS engine.

3.  Incorrect Predicate Evaluation Order

Many Java programmers believe a query is usually processed in the following order:

FROM, WHERE, GROUP BY, HAVING, SELECT

The above sequence outlines the logical order for executing query. Logically, the FROM clause is processed first, defining the source data table to retrieve data from; the WHERE predicates are applied next, followed by GROUP BY, and so on.

However, physically, the query is processed differently. The order of predicate evaluation is managed by various rules and these usually keep changing with every database release.

For instance, consider the following Employee table:

Given the above table, consider you want to retrieve all marketing department employees with employee code that is greater than 100. Generally, in this situation, developers will query the table in the following manner:

SELECT Eno, Ecode AS Employee_No,Employee_Code
FROM Employee
WHERE Edept LIKE ‘M%’
  AND CAST (Ecode AS INT) > 100;

However, the above query results in error:

“Conversion failed when converting the varchar value ‘abc’ to data type int”

The query fails because, as noted earlier, there is no prescribed order for executing predicates. In this case, the second predicate is evaluated first resulting in a conversion error.

Instead, using a CASE expression here will guarantee that only valid numeric values will be converted to INT data type:

SELECT Eno, Ecode AS Employee_No,Employee_Code
FROM Employee
WHERE Edept LIKE ‘M%’
AND CASE WHEN Ecode NOT LIKE ‘%[^0-9]%’
  THEN CAST (Ecode AS INT)
  END>100;

Wrap Up:

SQL has tremendous importance in the world of Java. You need a lifetime of training and practice to become a developer who knows the ins and outs of various languages. What’s more, you shouldn’t be scared of making mistakes because you will only learn from them.