# Best Practices For Developing Java Applications __*JDBC best practices*__ + JDBC + **Prepared Statement** is recommended + **AddBatch/ExecuteBatch** recommended + For query:`fetchSize="-2147483648"` + For batch processing:`rewriteBatchedStatements=true` + For large result sets: + Set the**FetchSize** to**Integer.MIN_VALUE** to ensure that the client does not cache + The client will read the execution result from the network connection in the following way:`StreamingResult` + Using connection pools + For example:`HikariCP, tomcat-jdbc, Durid, c3p0, dbcp` + Data access framework, take the**MyBatis** as an example: + MyBatis is highly compatible with TiDB + Use**# {param} ** in**Mapper** instead of**$ {param} ** + For batch operations, set the**ExecutorType** for the session to**Batch** ------------------------------------------------------------ 1. JDBC 1.1 For OLTP scenario, the SQL statement sent by the program to the database are exhaustible after removing flag changes. Therefore, it is recommended to use Prepared Statements (Prepared Statements) instead of plain text execution, and reuse the prepared statement to execute them directly, so as to avoid TiDB repeated parse and generating SQL execution plans overhead. 1.2 When executing SQL statement through JDBC, the SQL statements for update and delete are sent to the database for execution one by one. BUT! The processing speed of the database is fast, the single throughput is large, and the execution efficiency is extremely high. At this point, a "conflict" occurs, and the database processes quickly, but the execution of the statement sent to the database for execution is sent one by one, at an imaginable speed. Therefore, the extremely fast processing speed of the database in this case cannot be reflected. So, to resolve this "conflict", **addBatch () ** appeared! addBatch () loads several SQL statement together, and then sends them to the database for execution at once, that is, batch processing of SQL data. As a result, the "conflict" was resolved and the implementation time was significantly shortened. 1.3 To prevent implicit conversions, generally pay special attention to this problem if the event type on the left side of the equal sign, otherwise you will not be able to go through the index. 1.4 It is recommended to configure RewriteBatchedExecuted = true when performing batch write processing. After using AddBatch or ExecuteBatch, JDBC will still send a single SQL by default. 1.5 In general, to improve execution efficiency, JDBC obtains query results in advance by default and saves them in client memory. However, in a scenario where a query return a very large result set, the client wants the database server to reduce the number of records return to the client at one time, and wait for the client to process a portion of the limited memory before going to the server for the next batch. In JDBC, there are usually two ways to handle it: (1) Set fetchSize to Integer.MIN_VALUE so that the client does not cache, and the client streams the execution result from the network connection by means of StreamingResult. (2) To use Cursor Fetch, you first need to set FetchSize to a positive integer, and configure useCursorFetch = true in the JDBC URL. Both methods are supported in TiDB, but the first method of setting fetchSize to Integer.MIN_VALUE is recommended, which is simpler to implement and more efficient than the second function. My recommendation here is that FetchSize is Integer.MIN_VALUE so that the client does not cache! The reason is to prevent OOM from happening. 2.1 The database connection pool allocates, manages, and releases database connections, and allows applications to reuse an existing database connection instead of re-establishing one; freeing database connections that have been idle longer than the maximum idle time to avoid missing database connections due to unreleased database connections. This technology can significantly improve the performance of database operations. 3.1 MyBatis supports two flag in Mapper: select 1 from t where id = # {param1} will be converted to select 1 from t where id =? Perform preprocessing and reuse the execution with the actual flag. The best performance can be achieved by combining the previous Prepare connection flag. Select 1 from t where id = $ {param2} will be executed with text substituted for select 1 from t where id = 1. If this statement is preprocessed with different flag, it may cause TiDB to cache a large number of preprocessed statement, and SQL execution in this way has injection security risks. 3.2 You can select the ExecutorType at the time of OpenSession, and MyBatis supports three types of executors: Simple: Each execution invokes the prepared statement to the JDBC (repeated prepared statements are reused if the JDBC is configured to turn on CachePrepstMTS). Reuse: Cache prepared statement in the executor, so that calls of repeated prepared statement can be reduced without JDBC's CachePrepstMts. Batch: ExecuteBatch execution is only invoked for each update when addBatch is queried or committed. If RewriteBatchExecution is enabled at the JDBC layer, an overwrite will be attempted, and a single entry will be sent if it is not enabled. Typically, the default value is Simple, and you need to change the ExecutorType when you call OpenSession. If it is a batch execution, you will encounter a situation where the previous update or insert in the transaction is very fast, while reading data or committing the transaction is slow. This is actually normal, and you need to be careful when troubleshooting slow SQL. Here we recommend using Batch, which can override SQL statement with batch conditions.