Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about Java developers writing SQL statements when there are common errors, many people may not know, in order to let you know more, Xiaobian summarized the following content for you, I hope you can harvest according to this article.

Java developers 'compatibility with object-oriented programming thinking and command-line programming thinking depends on their ability to:

Skills (anyone can write command-line code) Dogma (some people use a pattern-pattern approach, i.e. patterns are everywhere and identified by name) Emotional state (In the early days, true object-oriented code is more difficult to understand than imperative code)

But when Java developers write SQL statements, everything changes. SQL is a declarative language, independent of object-oriented and imperative thinking. In SQL, queries are easy to express. But it's also not that easy to write in the best or most correct way. Developers need to rethink not only their programming patterns, but also from a set theory perspective.

Here are a few common errors Java developers make when writing SQL statements with JDBC or jOOQ:

1. Forget NULL.

Misunderstanding the meaning of NULL is probably the most common mistake Java developers make when writing SQL. This may be because NULL is also known as UNKNOWN, but there are other reasons as well. Of course, it would be easier to understand if it was simply called UNKNOWN. Another reason is that NULL in SQL is mapped to null in Java when JDBC fetches data or binds variables. This may lead people to think that null=NULL exists in SQL, similar to null== NULL in Java.

An even more bizarre example of misinterpreting NULL is when NULL predicates are used for row-valued expressions.

Another subtle problem arises with misinterpretation of the meaning of NULL in NOTIn antijoin.

solutions

Continually train yourself. To always be clear about the meaning of NULL, every time you write SQL, consider:

Is the predicate correct for NULL? Does NULL affect the result of this function? 2. Processing data in Java memory

Some Java developers know SQL features well. Occasionally JOIN, scattered UNION, no problem. But what if you encounter window functionality, result set grouping, etc.? A lot of Java developers load SQL data into memory, convert the data into appropriate collection types, and perform annoying mathematical operations on collections in very lengthy loop structures (at least until Java 8 improves containers).

However, some SQL databases support advanced OLAP features in addition to SQL standards, which are more efficient and easier to write. A non-standard example is the MODEL clause of Oracle. Just let the database do the data processing and load the final results into Java memory. Because some very smart people have optimized these expensive products. So, in fact, by migrating to an OLAP database, you get two benefits:

Concise. It may make it easier to write correct code in SQL than it is in Java. The database will likely be faster than your algorithm. What's more, you don't have to send millions of records over the web anymore. solutions

Every time you implement a data-centric algorithm in Java, try asking yourself: Is there a way for the database to perform the work and just deliver the results to me?

3. Use UNION instead of UNION all.

Compared to UNION, UNION ALL requires additional keywords to pale in comparison. It would be much better if the SQL standard had defined support for:

UNION DISTINCT generally requires very little de-duplication (sometimes de-duplication or even errors), and it tends to be slow for large result sets with many columns because the two subqueries need to be sorted and each tuple needs to be compared to the following tuple.

Note that even though the SQL standard specifies INTERSECTALL and EXCEPTALL, few databases implement these less useful operations.

solutions

Every time you write about Union, consider whether you actually want to write about Union.

4. Pagination of large numbers of results using JDBC pagination

Most databases support the use of LIMIT. OFFSET，TOP .. START AT、OFFSET.. Clauses such as FETCH paginate results in some way. Without support for these clauses, there is still ROWNUM(Oracle) or ROW_NUMBER()OVER()(DB2, SQL Server 2008 and earlier), which is much faster than paging in memory. This is especially true for large data sets.

solutions

Just use those clauses or tools (such as jOOQ) to simulate the above pagination clauses for you.

5. Connect in Java memory

From the early days of SQL development, some developers still have an uneasy feeling about SQL connectivity. There has always been an inherent fear that JOIN is slow. If the cost-based optimizer chooses to execute nested loops, loading the complete table into database memory before creating a join table source, it is indeed slow. But it rarely happens. MERGE JOIN and HASHJOIN operations are very fast with appropriate predicates, constraints, and indexes. This relates to the correct metadata (I don't need to cite Tom Kyte again). However, it is still possible that quite a few Java developers will load two tables from separate queries into the map container and join them somehow in Java memory.

solutions

If you select from multiple tables in multiple steps, think twice about whether you can express the query functionality you need in a single statement. 6. Remove duplicates from a Cartesian product using DISTINCT or UNION

The presence of verbose joins causes relationships in SQL statements to appear loose. Specifically, if you are involved in a multi-column foreign key relationship, you are likely to forget to add predicates to JOINON clauses. This may result in duplicate records, but perhaps only in exceptional circumstances. Some developers may then choose to use DISTINCT to delete these duplicates again. This error has three dangers:

It may be a symptom but not a cure. Even in some marginal cases, the standard does not work. This can be slow on large result sets with many columns. DISTINCT performs an ORDER BY operation to remove duplicates. This is also very slow in large Cartesian products, because doing so still results in a large amount of data being loaded into memory. solutions

As a rule of thumb, when you get unwanted duplicate results, you should first check your join predicates. Because it's possible that somewhere there's an imperceptible Cartesian product.

7. Do not use MERGE statements

Strictly speaking, this is not a real mistake, but may simply be a lack of awareness or fear of powerful MERGE statements. Some databases include other forms of UPSERT statements, such as MySQL's ONDUPLICATE KEY UPDATE clause. But MERGE is really powerful, most importantly in databases, and it extends SQL standards like SQL Server to a great extent.

solutions

If you link INSERT and UPDATE or link SELECT... For UPDATE to achieve UPSERTING, then you have to think about it. Regardless of the risks associated with running conditions, you may be able to use a simple MERGE statement to achieve this.

8. Aggregate functions are used instead of form features

Before the introduction of window functions, using GROUPBY clauses with projected aggregate functions was the only way to summarize data. This works well in most cases, and if the aggregated data needs to be supplemented by regular data, the grouped query can be placed in a join subquery.

However, SQL: 2003 defines window functionality, and many mainstream database vendors have implemented window functionality. The Windows feature aggregates ungrouped data in the result set. In fact, each window feature supports its own independent PARTITION BY clause, which is a very useful tool for report-like applications.

Use the window function to:

Resulting in more readable SQL(reducing the presence of non-dedicated GROUP BY clauses in subqueries) improves performance, and as an RDBMS it is likely easier to optimize its window functionality. solutions

When you write a GROUP BY clause in a subquery, think carefully about whether this can be done with a window function.

9. Indirect sorting using memory sorting

Multiple types of expressions are supported in the SQLER BY clause, including CASE statements, which are useful for indirect sorting. You should always be able to sort data in Java memory because you think:

SQL sorting is too slow SQL sorting cannot do this

If you sort any SQL data in memory, think carefully about whether you can migrate the sort to a database. This is for the same reason that paging is migrated to the database.

10 Insert a large number of records one by one

JDBC includes batch processing, and you should use it. When faced with thousands of records, do not create a new Prepared Statement for each record to insert. If you want to insert all records into the same table, create a batch INSERT statement using a single SQL statement and multiple sets of bound values. Depending on your database and database configuration, you may need to commit after a certain number of inserted records in order to keep UNDO logs from becoming overly large.

solutions

Always insert large datasets in bulk.

Java developers write SQL statements when the common 10 kinds of errors, we do not have a general understanding, I hope in the process of writing must pay special attention!

After reading the above, do you have any further understanding of the common mistakes Java developers make when writing SQL statements? If you still want to know more knowledge or related content, please pay attention to the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.