JDBC Connection Database Experience Gathering Z

xiaoxiao2021-03-06  14

JDBC Connection Database Experience Collection

Java Database Connections (JDBC) consists of a set of classes and interfaces written in Java programming languages. JDBC provides a standard API for tools / database developers, enabling them to write database applications with pure Java APIs. However, the interfaces of the developers are not exactly the same, so the development environment changes will bring a certain change. This article mainly gathers different databases of different databases. 1. Connect various database mode Quick check tables The following is a manual to use the JDBC connection below. You can use it as a manual.

1, Oracle8 / 8i / 9i database (Thin mode) class.Forname ("Oracle.jdbc.driver.Oracledriver). NewInstance (); string url =" jdbc: Oracle: Thin: @localhost: 1521: ORCL "; / / orcl is a database sid string user = "test"; string password = "test"; connection conn = drivermanager.getConnection (URL, User, Password); 2, DB2 database class.forname ("com.ibm.db2.jdbc .app.db2driver ") .newinstance (); string url =" jdbc: db2: // localhost: 5000 / sample "; // Sample for your database name String user =" admin "; string password ="; connection CONN = DriverManager.getConnection (URL, User, Password); 3, SQL Server 7.0 / 2000 Database Class.Forname ("com.microsoft.jdbc.sqlser.sqlserverdriver). NewInstance (); string url =" JDBC: Microsoft : SQLSERVER: // localhost: 1433; DatabaseName = mydb "; // mydb is database string user =" sa "; string password =" "; connection conn = drivermanager.getConnection (URL, User, Password); 4, Sybase database Class.Forname ("com.sybase.jdbc.sybdriver). Newinstance (); string url =" jdbc: sybase: tds: localhost: 5007 / mydb "; // mydb for your database name Properties Sysprops = System.getProperties (); Sysprops. PUT ("User", "UserID"); sysprops.put ("password", "user_password"); connection conn = drivermanager.getConnection (URL, Sysprops); 5, Informix database class.forname ("com.informix.jdbc .Ifxdriver "). NewInstance (); string url =" jdbc: informix-sqli: //123.45.67.89: 1533 / mydb: informixserver = myserver; user = testuser; password = testpassword "; // mydb is the database name Connection Conn = DriverManager.getConnection (URL); 6, mysql database class.forname ("org.gjt.mm.mysql.driver). NewInstance (); string url =

"? Jdbc: mysql: // localhost / myDB user = soft & password = soft1234 & useUnicode = true & characterEncoding = 8859_1" // myDB name for the database Connection conn = DriverManager.getConnection (url); 7, PostgreSQL database Class.forName ( "org.postgresql. Driver "); newinstance (); string url =" jdbc: postgreSQL: // localhost / mydb "// mydb is the database name String user =" myuser "; string password =" mypassword "; connection conn = drivermanager.getConnection (URL , User, Password); 8, Access Database Directly with ODBC Class.Forname ("Sun.jdbc.odbc.jdbcodbcdriver"); string url = "JDBC: ODBC: driver = {Microsoft Access Driver (* .mdb)}; DBQ = " Application.getRealPath (" / Data / ReportDemo.mdb); connection conn = drivermanager.getConnection (URL, "," "); statement stmtnew = conn.createstatement ();

Second, JDBC connection MySQL mode The following is a small tutorial to connect Mysql using JDBC

1. Find the driver mysql currently available Java driver for Connection / J, you can download from MySQL official website, and find the mysql-connection-java-3.0.15-ga-bin.jar file, this driver is pure Java driver Program does not need to do other configurations. 2. Dynamically specify that ClassPath If you need to execute the ClassPath, you will use the -cp mode when executed. Otherwise add the above .jar file to the ClassPath environment variable. 3, load the driver try {class.forname (com.mysql.jdbc.driver); system.out.println (Success Loading MySQL DRIVER!);} Catch (exception e) {system.out.println (Error Loading MySQL Driver !); E.PrintStackTrace ();} 4, set up the connected URL JDBC: mysql: // localhost / databasenename [? PA = VA] [& PA = VA] three, the following list is listed in the following JDBC to connect to the Oracle database Some techniques you can use allow us to better exert the system's performance and achieve more features (system reprint).

1. Use the Thin driver in the client software development

In developing Java software, Oracle's database provides four types of drivers, two client software for application, applets, servlets, and two servers such as Java stored procedures in the database. In the development of client-end software, we can select an OCI driver or Thin driver. The OCI driver uses Java localization interface (JNI) to communicate with the database through Oracle client software. The Thin driver is a pure Java driver that communicates directly with the database. In order to achieve the highest performance, Oracle recommends using an OCI driver in the development of client software, which seems correct. But I recommend using Thin drivers because it is discovered by multiple tests, in general, the performance of the Thin driver exceeds the OCI driver. 2. Turn off automatic submission function and improve system performance

When the connection to the database is established for the first time, the connection is in automatic submission mode by default. In order to get better performance, you can turn off the automatic submission function by calling the setAutocommit () method of the Connection class with the Poolery FALSE parameter, as shown below:

Conn.setautocommit (false);

It is worth noting that once the automatic submission function is turned off, we need to manage the transaction by calling the COMMIT () and rollback () method to manually manually.

3. Use the Statement object in dynamic SQL or time-limited commands

When executing the SQL command, we have two options: You can use the PreparedStateMent object, or you can use the Statement object. No matter how many times use the same SQL command, PreparedStatement is only parsed and compiled once. When using the Statement object, it is parsed and compiled each time a SQL command is executed. This may make you think that using the PreparedStateMent object is faster than using the Statement object. However, the test I conducted indicated that in the client software, the situation is not the case. Therefore, in the SQL operation with time-limited, we should consider using the Statement object unless divided by the SQL command.

In addition, using the Statement object also makes the writing dynamic SQL commands easier because we can connect strings together to create a valid SQL command. So I think the Statement object can make the creation and execution of the dynamic SQL command easier.

4. Format the dynamic SQL command using the helper function

When you create a dynamic SQL command executed using the Statement object, we need to process some formatted issues. For example, if we want to create a SQL command to insert the name O'Reilly into the table, you must use the "''" number of O'Reilly in two. Completing the best way to do this is to create a Helper method that completes the replacement operation, and then uses a SQL command when connecting a string to express a SQL command. Similarly, we can let the Helper method accept a DATE-type value and then allow it to output a string expression based on Oracle to_date () function.

5, use preparedStatement object to improve the overall efficiency of the database

When performing a SQL command using the PreparedStatement object, the command is parsed and compiled by the database and then placed in the command buffer. Then, whenever the same preparedStatement object is executed, it will be resolved once, but it will not be compiled again. A pre-compiled command can be found in the buffer and can be reused. In an enterprise application with a large number of users, the same SQL commands are often repeated, and the reduction in the number of compilations that use the PreparedStatement object can improve the overall performance of the database. If you are not created, prepared, executing the pre previoustement task is longer than the Statement task, and I will recommend using the PreparedStateMent object in all situations other than the dynamic SQL command. 6, use preparedStatement objects in a batch repetition insertion or update operation

If the insertion and update operations are processed, they can significantly reduce the time they need. Statement and CallableStatement provided by Oracle do not really support batch, only PreparedStatement objects really support batch. We can use Addbatch () and Executebatch () methods to select a standard JDBC batch, or select the faster Oracle proprietary approach by using the setEcuteBatch () method of the PreparedStatement object and the standard executeUpdate () method. To use the Oracle's proprietary batch mechanism, setExecutebatch () can be called in the following manner; try {(OraclePreParedStatement) PSTMT) .SETEXECUTEBATCH (30); ... pstmt.executeUpdate ();}

When you call setExecutebatch (), the value specified is a upper limit. When this value is reached, the SQL command is automatically executed, and the standard executeUpdate () method will be sent as a batch to the database. We can transfer batch tasks at any time by calling the SendBatch () method of the PreparedStatement class.

7. Use Oracle Locator to insert, update large objects (LOB)

Oracle's PreparedStatement class does not fully supports the processing of large objects such as BLOB and Clob, especially the Thin driver does not support SetObject () and setBinaryStream () methods with preparedStatement objects, and does not support the use of setcharacterstream () methods to set up CLOB Value. Only the method in the Locator itself can get the value of the LOB type from the database. You can use the PreparedStateMent object to insert or update LOB, but you need to use Locator to get the value of the LOB. Due to these two problems, I recommend using Locator's way to insert, update, or get the value of the LOB.

8, use SQL92 syntax to call stored procedures

When calling the stored procedure, we can use SQL92 or Oracle PL / SQL, because there is no practical benefit using Oracle PL / SQL, and will bring trouble to develop developers who have maintained your application, so I suggest it Use SQL92 when calling the stored procedure.

9. Transfer the object mode to the database using Object SQL

Since Oracle's database can be used as an object-oriented database, you can consider transferring object-oriented mode in your application to the database. The current method is to create a Java bean as a camouflage database object, map their properties into the relational table, and then add a method in these beans. Although there is no problem in Java, since operation is performed outside of the database, other access database applications cannot take advantage of object mode. If you use Oracle's object-oriented technology, you can use a new database object type to simulate your data and operations in the database, then use JPublisher and other tools to generate your own Java Bean class. If this is used, not only Java applications can use the application software object mode, other applications that need to share data and operations in your application can also use object patterns in the application. 10. Use SQL to complete the operation within the database

The most important experience I want to introduce you is to make full use of SQL-oriented ways to solve database processing needs, rather than using Java's processes.

If the programmer wants to find a lot of rows in a table, each line in the result looks up the data in other tables, and finally, the programmer creates a separate UPDATE command to update the data in the first table. Tasks similar to this can be done in a UPDATE command by using a multi-column query in the Set clause. When you can complete the task in a single SQL command, how must you let the data flow from the Internet? I recommend that users carefully learn how to maximize the functionality of SQL.

1. Find the driver mysql currently available Java driver for Connection / J, you can download from MySQL official website, and find the mysql-connection-java-3.0.15-ga-bin.jar file, this driver is pure Java driver Program does not need to do other configurations. 2. Dynamically specify that ClassPath If you need to execute the ClassPath, you will use the -cp mode when executed. Otherwise add the above .jar file to the ClassPath environment variable. 3, load the driver try {class.forname (com.mysql.jdbc.driver); system.out.println (Success Loading MySQL DRIVER!);} Catch (exception e) {system.out.println (Error Loading MySQL Driver !); E.PrintStackTrace ();} 4, set up the connected URL JDBC: mysql: // localhost / databasenename [? PA = VA] [& PA = VA] three, the following list is listed in the following JDBC to connect to the Oracle database Some techniques you can use allow us to better exert the system's performance and achieve more features (system reprint).

1. Use the Thin driver in the client software development

In developing Java software, Oracle's database provides four types of drivers, two client software for application, applets, servlets, and two servers such as Java stored procedures in the database. In the development of client-end software, we can select an OCI driver or Thin driver. The OCI driver uses Java localization interface (JNI) to communicate with the database through Oracle client software. The Thin driver is a pure Java driver that communicates directly with the database. In order to achieve the highest performance, Oracle recommends using an OCI driver in the development of client software, which seems correct. But I recommend using Thin drivers because it is discovered by multiple tests, in general, the performance of the Thin driver exceeds the OCI driver. 2. Turn off automatic submission function and improve system performance

When the connection to the database is established for the first time, the connection is in automatic submission mode by default. In order to get better performance, you can turn off the automatic submission function by calling the setAutocommit () method of the Connection class with the Poolery FALSE parameter, as shown below:

Conn.setautocommit (false);

It is worth noting that once the automatic submission function is turned off, we need to manage the transaction by calling the COMMIT () and rollback () method to manually manually.

3. Use the Statement object in dynamic SQL or time-limited commands

When executing the SQL command, we have two options: You can use the PreparedStateMent object, or you can use the Statement object. No matter how many times use the same SQL command, PreparedStatement is only parsed and compiled once. When using the Statement object, it is parsed and compiled each time a SQL command is executed. This may make you think that using the PreparedStateMent object is faster than using the Statement object. However, the test I conducted indicated that in the client software, the situation is not the case. Therefore, in the SQL operation with time-limited, we should consider using the Statement object unless divided by the SQL command.

In addition, using the Statement object also makes the writing dynamic SQL commands easier because we can connect strings together to create a valid SQL command. So I think the Statement object can make the creation and execution of the dynamic SQL command easier.

4. Format the dynamic SQL command using the helper function

When you create a dynamic SQL command executed using the Statement object, we need to process some formatted issues. For example, if we want to create a SQL command to insert the name O'Reilly into the table, you must use the "''" number of O'Reilly in two. Completing the best way to do this is to create a Helper method that completes the replacement operation, and then uses a SQL command when connecting a string to express a SQL command. Similarly, we can let the Helper method accept a DATE-type value and then allow it to output a string expression based on Oracle to_date () function.

5, use preparedStatement object to improve the overall efficiency of the database

When performing a SQL command using the PreparedStatement object, the command is parsed and compiled by the database and then placed in the command buffer. Then, whenever the same preparedStatement object is executed, it will be resolved once, but it will not be compiled again. A pre-compiled command can be found in the buffer and can be reused. In an enterprise application with a large number of users, the same SQL commands are often repeated, and the reduction in the number of compilations that use the PreparedStatement object can improve the overall performance of the database. If you are not created, prepared, executing the pre previoustement task is longer than the Statement task, and I will recommend using the PreparedStateMent object in all situations other than the dynamic SQL command. 6, use preparedStatement objects in a batch repetition insertion or update operation

If the insertion and update operations are processed, they can significantly reduce the time they need. Statement and CallableStatement provided by Oracle do not really support batch, only PreparedStatement objects really support batch. We can use Addbatch () and Executebatch () methods to select a standard JDBC batch, or select the faster Oracle proprietary approach by using the setEcuteBatch () method of the PreparedStatement object and the standard executeUpdate () method. To use the Oracle's proprietary batch mechanism, setExecutebatch () can be called in the following manner; try {(OraclePreParedStatement) PSTMT) .SETEXECUTEBATCH (30); ... pstmt.executeUpdate ();}

When you call setExecutebatch (), the value specified is a upper limit. When this value is reached, the SQL command is automatically executed, and the standard executeUpdate () method will be sent as a batch to the database. We can transfer batch tasks at any time by calling the SendBatch () method of the PreparedStatement class.

7. Use Oracle Locator to insert, update large objects (LOB)

Oracle's PreparedStatement class does not fully supports the processing of large objects such as BLOB and Clob, especially the Thin driver does not support SetObject () and setBinaryStream () methods with preparedStatement objects, and does not support the use of setcharacterstream () methods to set up CLOB Value. Only the method in the Locator itself can get the value of the LOB type from the database. You can use the PreparedStateMent object to insert or update LOB, but you need to use Locator to get the value of the LOB. Due to these two problems, I recommend using Locator's way to insert, update, or get the value of the LOB.

8, use SQL92 syntax to call stored procedures

When calling the stored procedure, we can use SQL92 or Oracle PL / SQL, because there is no practical benefit using Oracle PL / SQL, and will bring trouble to develop developers who have maintained your application, so I suggest it Use SQL92 when calling the stored procedure.

9. Transfer the object mode to the database using Object SQL

Since Oracle's database can be used as an object-oriented database, you can consider transferring object-oriented mode in your application to the database. The current method is to create a Java bean as a camouflage database object, map their properties into the relational table, and then add a method in these beans. Although there is no problem in Java, since operation is performed outside of the database, other access database applications cannot take advantage of object mode. If you use Oracle's object-oriented technology, you can use a new database object type to simulate your data and operations in the database, then use JPublisher and other tools to generate your own Java Bean class. If this is used, not only Java applications can use the application software object mode, other applications that need to share data and operations in your application can also use object patterns in the application. 10. Use SQL to complete the operation within the database

The most important experience I want to introduce you is to make full use of SQL-oriented ways to solve database processing needs, rather than using Java's processes.

If the programmer wants to find a lot of rows in a table, each line in the result looks up the data in other tables, and finally, the programmer creates a separate UPDATE command to update the data in the first table. Tasks similar to this can be done in a UPDATE command by using a multi-column query in the Set clause. When you can complete the task in a single SQL command, how must you let the data flow from the Internet? I recommend that users carefully learn how to maximize the functionality of SQL.

转载请注明原文地址:https://www.9cbs.com/read-48231.html

New Post(0)