|
Description |
MySQL |
Oracle |
JDBC driver |
|
Driver: Http://dev.mysql.com/downloads/connector/j/ Driver type: type4 All the latest versions claim to support jdbc4.0 Standard driver registration name: COM. MySQL. JDBC. Driver Used for master-slave read/write splitting driver registration name: COM. MySQL. JDBC. replicationdriver |
Driver: Http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html Driver type: type2 (OCI), type4 (thin) All the latest versions claim to support jdbc4.0 Standard driver Registration Name: Oracle. JDBC. oracledriver Or Oracle. JDBC. Driver. oracledriver |
JDBC URL |
|
The standard driver URL is as follows: JDBC: mysql: // host: Port/dbname The replicationdriver URL is as follows: JDBC: mysql: Replication: // master, slave1, slave2/dbname |
OCI mode: the client needs to install the Oracle client, and the JDBC driver is preferably the same as the version installed on the client. The URL format is as follows: JDBC: oracle: OCI: @ tnsname JDBC: oracle: OCI: @ host: Port: Sid Thin mode: you do not need to install the Oracle client. The URL format is JDBC: oracle: thin: @ host: Port: Sid. |
Connection Property |
|
You can use a key-value pair to specify the connection property like an http url in a jdbc url. For example: JDBC: mysql: // FIG: 3306/test? User = root & Password = 1234 |
Only properties can be configured through Properties |
Connects to a specified Character Set |
|
The default value is server-side character encoding. You can use the characterencoding attribute to forcibly specify the character encoding in the connection. |
The specified database character set is not supported. The default value is server-side character encoding; JDBC data encoding has nothing to do with local nls_lang environment variables; For database encoding of non-international characters such as us7ascii, transcoding is required for accessing Chinese characters; If the data encoding is different from the database, transcoding is also required; Transcoding is not required when nvarchar2 is used as an international field. |
Batch |
Batch Processing of DML operations is generally used to reduce network transmission, which may increase the performance by more than 10 times. |
Yes. The number of rows actually affected by each SQL statement is returned, MySQL batch only supports syntax by default and does not improve performance. You need to specify rewritebatchedstatements = true in the connection attribute to achieve the true batch effect: Insert into values (...) will be converted to insert into values (...), (...),... SQL Execution, The update and delete statements are converted into one SQL statement and sent to the server for execution. During this period, allowmultiqueries = true is set temporarily. |
Yes. The number of affected rows cannot be returned, The old version of the driver has a data loss bug when a batch is very large. |
Client Data Receiving Method |
|
By default, all data is retrieved from the server and stored in the client memory at a time. The fetch size parameter does not work. When an SQL statement returns a large amount of data, JVM OOM may occur. To read a large amount of data from the server without JVM Oom, you can use one of the following methods: 1. When statement sets the following attributes, the stream data receiving method is used. Only some data is received from the server each time until all data processing is complete. No jvm oom occurs. Setresultsettype (resultset. type_forward_only ); Setfetchsize (integer. min_value ); 2. Call the enablestreamingresults method of statement. In fact, the enablestreamingresults method is encapsulated in 1st methods. 3. Set the connection property usecursorfetch = true (supported by the 5.0 driver), set statement to type_forward_only, and set fetch. The size parameter indicates that a server-side cursor is used to fetch fetch_size data from the server each time. |
Yes. By default, the fetch size record is retrieved from the server at a time and placed on the client. After the client completes processing a batch, the next batch is retrieved from the server until all data processing is complete. When a large amount of data needs to be processed, resultsettype should be set to type_forward_only; otherwise, JVM may occur. Oom. |
Preparedstatement |
Pre-processing SQL |
Supported It is mainly used to prevent SQL injection. The current version does not improve the performance. For details, see Http://blog.csdn.net/yzsind/article/details/7266281 |
Supported It is very important not only to prevent SQL injection, but also to greatly reduce server CPU overhead and improve performance. |
SQL Execution timeout settings |
|
You can use preparedstatement. setquerytimeout (value) to set the execution timeout (5.0) for each SQL statement) Set the time-out for all SQL statements through the connection attribute sockettimeout |
You can use preparedstatement. setquerytimeout (value) to set the execution timeout for each SQL statement. Set the timeout time for all SQL statements through the connection attribute oracle. JDBC. readtimeout |
Execute Multiple SQL statements at a time |
|
Controlled by the allowmultiqueries parameter, this parameter is not allowed by default. Multiple result sets are returned when multiple SQL statements are executed. You can use the getmoreresults method of JDBC to process the results. For example: Stmt.exe cutequery ("select * from T1; select * From t2 "); Resultset RS1 = stmt. getresultset (); While (rs1.next ()){ ... } If (stmt. getmoreresults ()){ Resultset rs2 = stmt. getresultset (); While (rs2.next ()){ ... } } |
Not Supported |
Server Load balancer and Failover |
|
Load Balancing (5.1) using loadbalance-related connection Parameters) Use replicationdriver to implement read/write splitting and use connection. setreadonly to automatically process master and slave requests. For example: Class. forname ("com. MySQL. JDBC. replicationdriver"). newinstance (); Connection conn = drivermanager. getconnection ("JDBC: mysql: Replication: // 127.0.0.1, 10.20.36.20/test ", "Yzs", "yzs "); Conn. setreadonly (true ); ... // Operate the master database Conn. setreadonly (false ); ... // Operation slave Database Conn. setreadonly (true ); ... // Operate the master database For details, see: Http://dev.mysql.com/doc/refman/5.6/en/connector-j-reference-replication-connection.html |
By specifying multiple servers in the connection URL and switching parameter configuration |