Connections to various databases

HTTP://TIEBA.BAIDU.COM/F?KZ=275140436 has a more detailed introduction to ======================================== 1 JDBC Connection database experience and skill collection Java Database Connectivity (JDBC) consists of a set of classes and interfaces written in the Java programming language. JDBC provides tool/database developers with a standard API that enables them to write database applications using pure Java APIs. However, each developer's interface is not exactly the same, so changes in the development environment will bring some configuration changes. This paper mainly sets up the connection methods of different databases.

One, the connection of various database mode quick Check table

The following is a list of the ways in which databases use JDBC connections, which can be used as a manual.

1. Oracle8/8i/9i/10i database (thin mode)

Class.forName ("Oracle.jdbc.driver.OracleDriver"). newinstance (); String url= "Jdbc:oracle:thin: @localhost: 1521:ORCL";  ORCL the SID String user= "test" for the database;  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 server7.0/2000 Database

Class.forName ("Com.microsoft.jdbc.sqlserver.SQLServerDriver"). newinstance ();  String url= "Jdbc:microsoft:sqlserver://localhost:1433;databasename=mydb";  MyDB for 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 for database name 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)};D bq=" +application.getrealpath ("/data/reportdemo.mdb "); Connection conn = drivermanager.getconnection (URL, "", ""); Statement stmtnew=conn.createstatement ();

Second, the JDBC connection MySQL mode

Here is a small tutorial on using JDBC to connect to MySQL

1. Find drivers

MySQL currently provides Java drivers for connection/j, which can be downloaded from the official MySQL website, and find the Mysql-connector-java-3.0.15-ga-bin.jar file, this driver is a pure Java driver, do not need to do other configuration.

2. Dynamic designation Classpath

If the classpath is specified dynamically when execution is required, the-CP mode is used at execution time.           Otherwise, add the above. jar file to the CLASSPATH environment variable. --------------------------------------------------------------------------------2 JDBC Connection database experience tips 3, loading drivers

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 the URL of the connection

JDBC:MYSQL://LOCALHOST/DATABASENAME[?PA=VA][&PA=VA]

Three, the following lists some of the techniques you can use to connect to an Oracle database using JDBC

1. Using the thin driver in client software development

In the development of Java software, Oracle's database provides four types of drivers, two for application software, applets, Servlets and other client software, and two other server-side software for Java stored procedures in the database. In the development of client-side software, we can select the OCI driver or the thin driver. The OCI driver uses the Java Localization Interface (JNI) to communicate with the database through the Oracle client software. The thin driver is a pure Java driver that communicates directly with the database. For maximum performance, Oracle recommends using the OCI driver in the development of client software, which seems to be correct. However, I recommend that you use the thin driver because the thin driver is typically more performance-driven than the OCI driver through multiple tests.

2, turn off the auto-submit function, improve the system performance

When you first establish a connection to the database, by default, the connection is in autocommit mode. For better performance, you can turn off autocommit by calling the Setautocommit () method of the connection class with the Boolean false parameter, as follows:

Conn.setautocommit (FALSE);

It is important to note that once the autocommit feature is turned off, we need to manage the transaction manually by invoking the commit () and rollback () Methods of the connection class.

3. Using statement objects in dynamic SQL or time-limited commands

When executing SQL commands, we have two choices: you can use the PreparedStatement object, or you can use the statement object. No matter how many times the same SQL command is used, PreparedStatement only parses and compiles it once. When a statement object is used, it is parsed and compiled each time an SQL command is executed. This may make you think that using PreparedStatement objects is faster than using statement objects. However, the tests I conducted showed that this was not the case in the client software. Therefore, in a time-limited SQL operation, we should consider using the statement object unless the SQL commands are processed in batches.

In addition, using the statement object makes it easier to write dynamic SQL commands because we can concatenate strings together to create a valid SQL command. Therefore, I think that the statement object makes it easier to create and execute dynamic SQL commands.

4. Use the helper function to format dynamic SQL commands

When creating dynamic SQL commands that are executed using the statement object, we need to deal with some formatting problems. For example, if we want to create a SQL command that inserts the name O ' Reilly into a table, you must replace the "'" Sign in O ' Reilly with two connected "" ". The best way to do this is to create a helper method that completes the replace operation, and then use the helper method that you created when the connection string heart takes a formula to express an SQL command. Similarly, we can let the helper method accept a Date type value and let it output a string expression based on the Oracle To_date () function.

5, using PreparedStatement object to improve the overall efficiency of the database

When the SQL command is executed using the PreparedStatement object, the command is parsed and compiled by the database and then placed into the command buffer. Then, whenever the same PreparedStatement object is executed, it will be parsed again, but it will not be compiled again. Pre-compiled commands can be found in the buffer and can be reused. In enterprise applications with a large number of users, the same SQL commands are often repeated, and the reduction in compilation times with PreparedStatement objects can improve the overall performance of the database. If it takes longer than the statement task to create, prepare, and execute the PreparedStatement task on the client, I recommend that you use the PreparedStatement object in all cases except for the dynamic SQL command.

--------------------------------------------------------------------------------3 JDBC Connection database experience tips 6, in batches to handle repeated insertions or more Using PreparedStatement objects in new operations

If you batch insert and update operations, you can significantly reduce the time they require. The statement and CallableStatement provided by Oracle do not really support batching, only PreparedStatement objects really support batch processing. We can use the Addbatch () and ExecuteBatch () methods to select the standard JDBC batch, or by leveraging the PreparedStatement object's Setexecutebatch () method and the standard executeupdate () method to select a faster Oracle proprietary method. To use Oracle's proprietary batching mechanism, you can call Setexecutebatch () in the following manner:

PreparedStatement Pstmt3d null; 　 try {((oraclepreparedstatement) pstmt). Setexecutebatch (30); ... pstmt.executeupdate (); The value specified when calling Setexecutebatch () is an upper bound, and when the value is reached, SQL command execution is automatically raised, and the standard Executeupdate () method is sent to the database as a batch. We can transfer batch tasks at any time by calling the Sendbatch () method of the PreparedStatement class.

7. Inserting and updating large objects (LOBs) using the Oracle Locator method

Oracle's PreparedStatement class does not fully support the processing of large objects such as blobs and CLOB, especially if the thin driver does not support SetObject () and Setbinarystream using PreparedStatement objects The () method sets the value of the blob, nor does it support setting the value of Clob with the Setcharacterstream () method. Only methods 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 lobs, but you need to use locator to get the value of the LOB. Because of these two issues, I recommend using the Locator method to insert, update, or get the value of the LOB.

8. Calling a stored procedure using SQL92 syntax

When calling a stored procedure, we can use SQL92 or Oracle PL/SQL, because there is no real benefit from using Oracle PL/SQL, and will cause trouble for developers who maintain your application later, so I recommend using SQL92 when calling a stored procedure.

9. Use object SQL to transfer the objects schema to the database

Now that you can use an Oracle database as an object-oriented database, consider going to the object-oriented schema in your application to the database. The current approach is to create Java beans as spoofed database objects, map their properties to relational tables, and then add methods to those beans. Although this is not a problem in Java, because operations are performed outside the database, other applications that access the database cannot take advantage of the object schema. With Oracle's object-oriented technology, you can emulate its data and operations in a database by creating a new database object type, and then use tools such as jpublisher to generate your own Java Bean classes. If you use this approach, not only can Java applications use the application's object model, but other applications that need to share data and operations in your app can also use the object patterns in the application software.

10. Using SQL to complete the operation in the database

The most important lesson I would like to introduce to you is to take advantage of SQL's collection-oriented approach to database processing requirements, rather than using a procedural programming language such as Java.

If a programmer wants to find many rows in a table, each row in the result looks for data from another table, and finally, the programmer creates a stand-alone update command to update the data in the first table in batches. A task similar to this can be done in an update command by using a multi-column subquery in the SET clause. When you can complete a task in a single SQL command, why should you let the data flow on the Web? I recommend that users seriously learn how to maximize the functionality of SQL.

Connections to various databases