Over a million developers have joined DZone.

3 More SQL Mistakes You Are Still Making As a Java Developer

· Java Zone

Discover how AppDynamics steps in to upgrade your performance game and prevent your enterprise from these top 10 Java performance problems, brought to you in partnership with AppDynamics.

Java developers make use of various web development frameworks and tools to develop customized software, eCommerce websites, web portals and mobile applications. Apart from the knowledge of these frameworks and tools, Java developers must also possess strong skills in SOAP, WSDL, UDDI and HTTP specifications.

A Java developer must also be a master in SQL programming.

In our last article, we pointed out a few SQL mistakes committed by Java developers. Since that list was far from complete, here are 3 more SQL mistakes that Java developers at times make:

1.  Misunderstanding NULL

This is again a very common mistake made by Java developers when writing SQL.  This happens because Java developers at times forget that NULL can be added as a value even for missing or unknown information.

Also, this confusion occurs as NULL=NULL in SQL gives different result than null==null in Java.

Let’s understand this with an example:

Consider the following Employee table:

Eno

Ename

Ecode

Edept

Mngrname

1

Allen

abc123

Sales

James

2

Max

def102

Marketing

NULL

3

Ward

abc516

Marketing

Max

4

Sam

def324

Sales

James

Consider another Manager table:

Mngrname

Email_id

James

james@xyz.com

Max

max@pqr.com

Michael

michael@lmn.com

Note that these tables do not represent a perfect design, but at times Java developers are faced working with a poorly designed database.

The request is to select the name of managers who work alone or don’t have any employees under them. Mostly, the first instinct in such cases would be to use the NOT IN predicate:

SELECT M.Mngrname

FROM Manager As M

WHERE M.Mngrname NOT IN (SELECT DISTINCT E.Mngrname

    FROM Employee AS E);

Output:

Mngrname

(0 row(s) affected)

But, you don’t get the desired output.

So, what went wrong?

SQL implements three-valued logic when it comes to handling NULL. On applying the NOT operator to the list of values generated by the subquery, it translates into:

“Mngrname NOT IN (James, NULL, Max)

This is equivalent to:

“NOT(Mngrname=James OR Mngrname=NULL OR Mngrname=Max)”

The expression “Mngrname=NULL” evaluates to UNKOWN and, according to the rules of three-valued logic, NOT UNKNOWN also evaluates to UNKNOWN. As a result, all rows are filtered out and the query returns an empty set.

This may happen if a column that has to have non-nullable values is created without adding the NOT NULL predicate. So, Java developers must ensure they craft queries that work correctly even with NULL values.

One of the ways to get the desired results is to use EXISTS predicate in place of IN.

sqlQuery = “SELECT M.Mngrname

FROM Manager As M

WHERE NOT EXISTS (SELECT E.Mngrname

    FROM Employee As E

    WHERE M.Mngrname = E.Mngrname)”;

preStmt= connection2.prepareStatement(sqlQuery);

result = preStmt.executeQuery();

Since EXISTS uses two-valued predicate logic evaluating TRUE/FALSE, the above query correctly returns the expected result set:

Mngrname

Michael

Other possible solutions are as follows:

/* Using IS NOT NULL in the subquery */

SELECT M.Mngrname

FROM Manager AS M

WHERE M.Mngrname NOT IN (SELECT E.Mngrname

    FROM Employee AS E

    WHERE E.Mngrname IS NOT NULL);

/* Using EXCEPT */

SELECT Mngrname

FROM Manager

EXCEPT

SELECT Mngrname

FROM Employee

2.  NOT USING PARAMETERIZED STATEMENTS

Some Java developers are skeptical of using parameterized statements as certain myths prevail related to them. They hold the misconception that parameterized statements are slower or constructing such SQL queries usually requires more time and effort on their part.

On the contrary, it is better to include parameterized statements rather than static statements in your code.

Let’s look at some reasons that will clearly show why Java developers must prefer parameterized statements in their code:

Ø  Increases Efficiency: Parameterized statements let you query the database repeatedly with different parameters saving the additional round-trip to the server for each query. You can use a loop in your code to set different values for the parameters. Since parameterized statements are pre-compiled only once, it lessens the load on the DBMS engine at execution time.

Ø  Prevents SQL Injection Attacks in Java: With prepared statements, values that will be inserted into a SQL query are sent to the SQL server only after the actual query is sent to the server. This will prevent a potential hacker from sending data along with the prepared query statement; thereby preventing SQL injection. On the other hand, using static statements will lead to SQL injection vulnerabilities which will provide means for the hacker to communicate directly to the database. This clearly shows it is better not to inline bind values into your SQL.

Ø  Boosts Readability: Using parameterized statements will increase the readability tremendously as it separates query code and parameter values unlike cluttered concatenated SQL strings. This will help the code maintainer easily understand your code.  Apart from readability, prepared statements are more secure than string concatenation.

3.  Returning Too Many Rows and Columns

One of the biggest and most frequent mistakes by Java developers is to craft SQL queries that return too many rows or too many columns. This mistake has an adverse impact on the Java application and overall SQL performance.

One of the ways Java developers commit this sin is by using SELECT * statement. This query not only returns unnecessary data (returns all the columns of the table), but also can force clustered index scans for the query plan.

If you are using SELECT * query, you are transferring a lot of redundant data from the database into a JDBC ResultSet. Irrespective of whether you read the data from the ResultSet or not, using SELECT * query will make JDBC driver load all data into your memory. This results in wastage of IO and memory especially when not all but only few specific columns are required.

Let’s understand this with an example using JOIN on the above mentioned Employee and Manager table:

PreparedStatement pst;

String sql = “SELECT * FROM Employee e JOIN Manager m ON e.Mngrname = m.Mngrname WHERE e.Eno = ?”;

pst = cn.prepareStatement(sql);

ResultSet rs = pst. executeQuery(sql);

Given the use of SELECT * in query, your database has no choice but to fully perform the loading of these joined tables, when in fact, the only thing required was:

String sql = “SELECT e.Ename, m.Email_id FROM Employee e JOIN Manager m ON e.Mngrname = m.Mngrname WHERE e.Eno = ?”;

End-users generally are looking for specific results and not hundreds or thousands of records. Furthermore, returning large sets (rows and columns) of redundant data increases the load on the server. So, it is vital to design queries in such a way that they demand less IO and memory consumption.

CONCLUSION

All developers are well aware of the fact that any SQL query can be written in multiple ways, but developers must always include the query that gives the fastest response.

Programming SQL is a challenging task for Java developers. It’s not easy to get it right. But SQL can be mastered with practice.

The Java Zone is brought to you in partnership with AppDynamics. AppDynamics helps you gain the fundamentals behind application performance, and implement best practices so you can proactively analyze and act on performance problems as they arise, and more specifically with your Java applications. Start a Free Trial.

Topics:

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}