10 SQL errors easily made by Java programmers

Java programmers need to mix object-oriented thinking and general imperative programming methods when programming. Whether they can perfectly combine the two depends on the level of programmers:

But when Java programmers write SQL statements, everything is different. SQL is an illustrative language, not an object-oriented or imperative programming language. It is very simple to write a query statement in SQL. But in Java, similar statements are not easy, because programmers should not only repeatedly consider the programming paradigm, but also consider the problem of algorithm.

The following are the mistakes that Java programmers often make when writing SQL (there is no specific order):

1. Forget null

The misunderstanding of null by Java programmers when writing SQL is probably the biggest mistake. Maybe it's because (not the only reason) null is also called unknown. It's easier to understand if it's called unknown. Another reason is that when you get something from the database or bind variables, JDBC corresponds SQL null to null in Java. This leads to the misunderstanding of null = null (SQL) and null = null (Java).

The biggest misconception about null is when null is used as a row valued expression integrity constraint.

Another misunderstanding occurs in the application of not in anti joins for null.

resolvent:

Train yourself well. When you write SQL, you keep thinking about the use of null:

2. Process data in Java Memory

Few Java developers can understand SQL well Occasionally used join, and weird Union, okay But what about window functions? What about grouping sets? Many Java developers load SQL data into memory, convert these data into some similar collection types, and then use the boundary loop control structure (at least before the collection upgrade of Java 8) to perform annoying mathematical operations on those collections

However, some SQL databases support advanced (and SQL standard support!) OLAP feature, which performs better and is easier to write A (not very standard) example is Oracle's great model clause Just let the database do the processing, and then just bring the results to JAVA memory Because after all, all the very smart guys have optimized these expensive products So in fact, by moving OLAP to the database, you will get the following two benefits:

Perfect method:

Every time you use java to implement a data centric algorithm, ask yourself: is there a way for the database to do this kind of trouble for me

3. Use union instead of union all

It's shameful. Compared with union, union all needs additional keywords. If the SQL standard already provides support, it may be better.

Removing duplicate rows is not only rarely needed (sometimes even wrong), but also quite slow for large data sets with many rows, because two sub selects need to be sorted, and each tuple also needs to be compared with its sub sequence tuples.

Note that even though the SQL standard specifies intersect all and except all, few databases implement these useless set operators.

Treatment method:

Every time you write a union statement, consider whether you actually need a union all statement.

4. Page a large number of results through JDBC paging technology

Most databases support some paging commands to achieve paging effects, such as limit OFFSET,TOP.. START AT,OFFSET.. Fetch statement, etc. Even if there is no database supporting these statements, it is still possible to filter rownum (Oracle) or row number () over () (DB2, SQL Server2008, etc.), which is faster than paging in memory. The effect is particularly obvious in processing a large amount of data.

Correction:

Using only these statements, a tool (such as jooq) can simulate the operation of these statements.

5. Add data to Java Memory

From the beginning of SQL, some developers still feel uneasy when using join statements in SQL. This stems from the inherent fear that joining will slow down. If cost based optimization is selected to implement nested loops, it may be true that all tables may be loaded in database memory before creating a connected table source. But the probability of this happening is too low. Through appropriate prediction, constraint and index, the operation of merge connection and hash connection is quite fast. This is all about the correct metadata (I can't reference too much of Tom Kyte here). Moreover, many Java developers may still load two tables and query them into a map separately, and add them to memory to some extent.

Correction:

If you have query operations from various tables in each step, think about whether you can express your query operations in a single statement.

6. Use distinct or union to de duplicate items in a temporary Cartesian product set

Through complex connections, people may lose the concept of all the relationships that play a key role in SQL statements. In particular, if this involves multi column foreign key relationships, it is likely to be forgotten in the join Add relevant judgment in the on clause. This can lead to duplicate records, but perhaps only in special cases. Some developers may therefore choose distinct to eliminate these duplicate records. This is wrong in three ways:

resolvent:

From experience, if you get unnecessary duplicate records, check your join judgment. Maybe somewhere there is a collection of Cartesian products that is difficult to detect.

7. Do not use merge statements

This is not a mistake, but it may be a lack of knowledge or lack of confidence in strong merge statements. Some databases understand other forms of update insert (upsert) statements, such as the repeated primary key update statement of MySQL, but merge is so powerful and important in databases that it wantonly expands SQL standards, such as SQL server.

Solution:

If you use things like joint insert and update or joint select For update and think twice when inserting updates such as insert or update. You can use a simpler merge statement to stay away from risky competition conditions.

8. Use aggregate functions instead of window functions

Before introducing the window function, aggregating data in SQL means using the group by statement to map to the aggregation function. It works well in many cases. For example, if the aggregation data needs to condense the general data, use the group query in the join sub query.

However, the window function is defined in SQL: 2003, which is implemented in many mainstream databases. The window function can aggregate data on the result set, but there is no grouping. In fact, each window function has its own independent partition by statement. This tool is very good for displaying reports.

Use window functions:

resolvent:

When you use the group by statement in a subquery, please think twice about whether you can use the window function.

9. Use memory to sort indirectly

The order by statement of SQL supports many types of expressions, including case statements, which are very useful for indirect sorting. You may not sort data in JAVA memory again because you think:

Treatment method:

If you sort any SQL data in memory, please think again whether you can't sort in the database. This is useful for database paging data.

10. Insert a large number of records one by one

JDBC "understands" batch processing, and you should not forget it. Do not use insert statements to access thousands of records one by one, A new Preparedstatement object will be created every time. If all your records are inserted into the same table, create an insert batch statement with an SQL statement and many value sets. You may need to submit after reaching a certain number of inserted records to ensure that the undo log is thin, which depends on your database and database settings Set.

Focus on popular foreign websites

facebook: http://www.fb-on.com

Facebook website: http://www.facebookzh.com

facebook: http://www.cn-face-book.com

youtube: http://www.youtubezh.com

twitter: http://www.twitterzh.com

Treatment method:

Always use batch to insert large amounts of data.

The above is the whole content of this article. I hope the content of this article can bring some help to your study or work. At the same time, I also hope to support a lot of programming tips!