3 Common SQL Errors that Java programmers may commit

Overview:Java programmers not only have solid Java programming skills, but also often involve the basics of other languages in their daily work, especially SQL. So what are the common SQL errors that programmers are prone to commit? Let's take a look at it!

You may see that the weekly work of a Java programmer is to encode the development of a scalable Web application, or to create a dynamic Web site, or to develop an efficient e-commerce product page, or to develop an Android application, and so on. But even if they are committed to different projects, they often have one thing in common, which is programming!

Their careers require a long period of work to accumulate more programming knowledge. Java programmers also need to understand the needs of the project, design and develop a prototype project that belongs to them. They must also have the basics of other languages, such as HTML, SQL, and so on, in order to always follow the industry's changing pace.

Java Programmer's career is not smooth sailing, they must try every challenge, even the best programmers, sometimes in the process of programming will inevitably make some inevitable mistakes. For example, some errors are absolutely avoidable when writing SQL statements. Here, the small series for the programmer to enumerate some common SQL errors.

1	SELECTTOP 3 common_mistakes FROMSQLProgrammingMistakes;

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 errors for Java programmers is to forget about batch processing. Write thousands of INSERT statements in a table this is really not a good idea, programmers should use a single SQL language to create a batch insert statement that binds different parameters. Batch processing will be performed much faster than a single execution.

Let's take a look at the following example:

12345678910111213141516

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, and in the database each row of the INSERT statement needs to be executed separately. Send a batch of INSERT statements to the database one go:

123456789101112131415161718

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 datasets. To significantly improve performance, programmers should try to run a statement in batch mode as much as possible. Another way to perform bulk insertions is to use the PreparedStatement object. However, batching is more than just an INSERT statement, you can also use it to perform operations such as updating, deleting, and declaring.

2.DBMS engine not optimized query

Not all Java programmers are aware of SQL. There are many ways to get the same results in SQL queries, but programmers should always follow the quickest way to respond best.

For example, a Java programmer is asked to retrieve all employees whose names begin with ' A ' from the employee table, usually by using the left function to return the first character of the employee's name:

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

But that's not true. In the above query, the database system will scan the entire table to find the required information. The index is not used, so it takes a lot of time to execute the query. Instead, the programmer should use the query to retrieve the results:

1	SELECTEname FROM Employee WHERE Ename LIKE‘A%’;

The above query will use the index to retrieve data quickly and efficiently. So in general, if the DBMS engine can take an index, programmers should use the words that refine the search as much as possible to speed up the execution of the query.

3. Incorrect order of predicate operations

Many Java programmers think that the usual order of processing for queries is as follows: From,where,group By,having,select. The sequence listed above is in addition to the logical order used to execute the query. Logically, the FROM clause is processed first, defining the retrieved data from the source data table, followed by where, then group by, and so on. However, in terms of physics, query processing is different. The order in which predicates are evaluated is usually changed by a variety of rules and database versions.

For example, the following employee table:

Eno	Ename	Ecode	Edept
1	Allen	Abc	Sales
2	Max	102	Marketing
3	Ward	55	Marketing
4	Sam	Xyz	Sales

In the given table above, the employee code to retrieve all sales department employees is greater than 100. Usually in this case the programmer will query the table in the following way:

1234	SELECTEno, Ecode AS Employee_No,Employee_CodeFROM EmployeeWHERE Edept LIKE ‘M%‘AND CAST (Ecode ASINT) > 100;

However, the above query results are incorrect:

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

The reason for the query failure is the previous point, which does not specify the order in which the predicates are executed. In this case, the second predicate evaluates to a conversion error first.

Instead of using a case expression, this will ensure that only valid values will be converted to the INT type:

123456

select eno, ecode  as employee_no,employee_code From employee where EDEPT  like ' M% ' and case when ECODE  not like '%[^0-9]% ' then cast as int end >100;

3 Common SQL Errors that Java programmers may commit