3 common sql mistakes java programmers may make

You may see that the weekly work of Java programmers is to code and develop a scalable web application, or create a dynamic website, or develop efficient e-commerce product pages, or it may be to develop an Android application, etc. But even though they work on different projects, they often have one thing in common: programming!

Their profession requires long hours of work to accumulate more programming knowledge. Java programmers also need to understand the project requirements, design and develop a prototype project of their own. In order to keep up with the changes in the industry, they must also have basic knowledge of other languages, such as HTML, SQL, etc.

　The career of Java programmers is not smooth sailing. They must try every challenge. Even the best programmers will inevitably make some inevitable mistakes during the programming process. For example, when writing SQL statements, some mistakes are absolutely avoidable. Here, the editor lists some common SQL errors for programmers.

SELECT TOP 3 common_mistakes FROM SQLProgrammingMistakes;

The results of the above query are as follows:

　1. Do not use batch updates

　When writing SQL statements, one of the most common and biggest mistakes of Java programmers is to forget batch processing. It is really not a good idea to write thousands of INSERT statements in a table. Programmers should use a single SQL language to create a batch INSERT statement that binds different parameters. Executing a batch will be much faster than a single execution.

　Let’s take a look at the following 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. Each INSERT statement in the database needs to be executed separately. Send a batch of INSERT statements 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();

Batch processing is very important when inserting large data sets. To significantly improve performance, programmers should try to run a statement in batch mode. Another way to perform bulk inserts is to use PreparedStatement objects. However, batch processing is not limited to INSERT statements, you can also use it to perform operations such as update, delete, and statement.

　2. The DBMS engine does not optimize the query

　Not all Java programmers understand SQL. There are many ways to get the same results in a SQL query, but programmers should always follow the fastest and most responsive way.

For example, a Java programmer is asked to retrieve all employees whose names start with 'A' from the Employee table. Usually they like to use the LEFT function to return the first character of the 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 find the required information. Indexes are not used, so a lot of time is spent executing queries. Instead, programmers should use the query to retrieve results:

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

The above query will utilize exponents to retrieve data quickly and efficiently. So in general, if the DBMS engine can take an index, the programmer should try to use search-optimized terms to speed up query execution.

　 3. Incorrect order of predicate operations

Many Java programmers believe that the usual processing order of queries is as follows: FROM, WHERE, GROUP BY, HAVING, SELECT. The above sequence column is in addition to the logical order used to execute the query. Logically speaking, the FROM clause is processed first and defines the retrieved data in the source data table; followed by WHERE, followed by GROUP BY and so on. However, physically speaking, query processing is different. The order of evaluation of predicates is often changed by various rules and database versions.

　For example, the following Employee table:

In the given above table, we want to retrieve all sales department employees whose employee codes are greater than 100. Usually in this case the programmer will query the table in the following way:

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 an error:

"Conversion failed when converting the varchar value 'abc' to data type int"

　The query failed The reason is exactly as pointed out earlier, the order in which the predicates are executed is not specified. In this case, the second predicate evaluation causes the conversion error first.

　　相反使用CASE表达式，这里将保证只有合法的数值将被转换为int类型：

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;