Database Connectivity (JDBC) from a group with the Java programming language classes and interfaces. JDBC for tool / database developers with a standard API, so that they can use a pure Java API to write database applications. However, each developer's interface is not identical, changes in the development environment will bring a certain configuration changes. In this paper, a collection of different database connections. One to connect a variety of database mode Fact Sheet
The following lists the various databases using the JDBC connection, it can be used as a manual.
1, Oracle8/8i/9i database (thin model) Class.forName ( "oracle.jdbc.driver.OracleDriver"). NewInstance (); String url = "jdbc: oracle: thin: @ localhost: 1521: orcl"; / / orcl is the database SIDString 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 Server7.0/2000 database Class.forName ( "com.microsoft.jdbc.sqlserver.SQLServerDriver"). NewInstance (); String url = "jdbc: microsoft: sqlserver: / / localhost: 1433; DatabaseName = mydb" ; / / mydb 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: / / 220.127.116.11:1533 / myDB: INFORMIXSERVER = myserver; user = testuser ; password = testpassword "; / / myDB name for the database 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 for the database name String user = "myuser"; String password = "mypassword"; Connection conn = DriverManager.getConnection (url, user, password);
8, access the database directly used in conjunction 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 (); 2, JDBC connection MySql method
The following is the use of JDBC to connect MySql a small tutorial
1, find the driver
MySQL currently offers a java driver for the Connection / J, can be downloaded from the MySQL website, and find the mysql-connector-java-3.0.15-ga-bin.jar file, this driver is a pure java driver, no need to do other configurations.
2, dynamic specify the classpath
If you need to specify dynamically at runtime classpath, on implementation of the method used-cp. Otherwise the above. Jar files added to the classpath environment variable.
3, loading 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 the connection urljdbc: mysql: / / localhost / databasename [? Pa = va] [& pa = va]
3, the following are listed in the use of JDBC to connect to Oracle database, you can use some of the techniques
1, the client-side software development using the Thin Driver
In the development of Java software, Oracle's database provides four types of drivers, two kinds of application software used, applets, servlets and other client software, and another two kinds of Java for database stored procedures and other server-side software. In the client-side software development, we can choose OCI driver or the Thin driver. OCI driver is the use of Java localized interface (JNI), through the Oracle client software to communicate with the database. Thin driver is a pure Java driver, it is to communicate directly with the database. In order to obtain the highest performance, Oracle recommends the development of client software to use OCI driver, which seems to be correct. However, I recommend the use of Thin driver, because, by repeated tests found that, in normal circumstances, Thin driver performance in excess of OCI driver.
2, turn off auto-commit feature to improve system performance
In the first establish a database connection, by default, the connection is in auto-commit mode. In order to obtain better performance, you can call with a Boolean value false parameter Connection class setAutoCommit () method to close auto-commit feature, as follows:
It is noteworthy that, once turned off auto-commit feature, we need to by calling the Connection class, commit () and rollback () method to artificial means to manage the transaction.
3, in the dynamic SQL or the use of time-limited commands Statement Object
In the implementation of SQL commands, we have two choices: you can use the PreparedStatement object, you can use Statement objects. No matter how many times using the same SQL command, PreparedStatement parsing and compiling it only once. When using the Statement object, each time you run an SQL command will be parsed and compiled it. This may make you think that the use of PreparedStatement object than using Statement objects faster. However, I have conducted tests show that the client software, not the case. Therefore, time-bound SQL operations, unless the bulk handling SQL commands, we should consider using the Statement object.
In addition, the use Statement objects also makes easier the preparation of dynamic SQL commands, because we can string together to create a valid SQL command. So, I think, Statement objects can dynamically create and execute SQL commands easier.
4, the use of helper functions for dynamic SQL commands to format
When you create using the Statement object to perform a dynamic SQL command, we need to deal with some formatting problems. For example, if we want to create a table to insert the name of O'Reilly's SQL commands, you must use two linked "''" was to replace O'Reilly's " '" No.. Completion of these tasks the best way is to create a complete replacement operations helper method, and then taking the formula in the connection string to express a heart SQL command used to create a helper method. Similarly, we can let helper method to accept a Date type value, and then let it out based on Oracle's to_date () function is a string expression.
5, using PreparedStatement object to improve the overall efficiency of the database
The implementation of PreparedStatement object using the SQL command, the command was parsing and compiling the database, and then was placed in the command buffer. Then, whenever the implementation of the same PreparedStatement object, it will be resolved once again, but will not be re-compiled. Can be found in the buffer pre-compiled commands, and can be used again. In a large number of users of enterprise application software, often repeat the same SQL commands, use the PreparedStatement object to reduce the number brought to compile the database can improve overall performance. If not, the client creation, preparation, implementation of the PreparedStatement task longer than the time required Statement tasks, I would recommend Dynamic SQL commands in addition to all the circumstances beyond the use of PreparedStatement object.
6, in the repeated batch insert or update operations using the PreparedStatement object
If the batch insert and update operations to deal with, we can significantly reduce the time they need. Oracle provided by the Statement and CallableStatement does not really support the batch, only the PreparedStatement object really to support batch processing. We can use addBatch () and executeBatch () method of the selection criteria JDBC batch, or by using the PreparedStatement object's setExecuteBatch () methods and standards of executeUpdate () method of selection of the Oracle proprietary faster approach. To use Oracle's proprietary batch mechanism, a way can be invoked as follows setExecuteBatch (): PreparedStatement pstmt3D null; try (((OraclePreparedStatement) pstmt). SetExecuteBatch (30); ... pstmt.executeUpdate ();)
Call setExecuteBatch () when the specified value is an upper limit, when reached this value, it will automatically trigger the implementation of SQL commands, the standard of executeUpdate () method will be used as a batch sent to the database. We can call the PreparedStatement class sendBatch () method at any time transfer batch tasks.
7, using the Oracle locator to insert, update large object (LOB)
Oracle of the PreparedStatement class does not fully support the BLOB and CLOB handling such large objects, especially the Thin driver does not support the use of PreparedStatement object's setObject () and setBinaryStream () method to set the value of the BLOB does not support the use of setCharacterStream () method to set CLOB value. Only locator itself in ways we can get from the database LOB type. PreparedStatement objects can be used to insert or update the LOB, but need to use the locator to obtain the LOB value. Because of these two problems, I recommend the use of locator method to insert, update, or access to LOB values.
8, use the SQL92 syntax for calling stored procedure
Calling stored procedure, we can use the SQL92 or Oracle PL / SQL, due to the use of Oracle PL / SQL, and there is no real benefit, but will maintain your application after the developer to bring trouble, so I suggest that used when calling a stored procedure SQL92.
9, using the Object SQL object model will be transferred to the database
As can be Oracle database as an object-oriented database to use, you can consider in the application of object-oriented model to the database. The current approach is to create a Java bean as a disguised the database objects, their properties will be mapped to relational tables, and then add in those bean methods. Despite this, as in Java, no problem, but because operations are outside the database, so other applications can not access the database using the object model. If we use Oracle's object-oriented technology, you can create a new database object type in the database, copy its data and operations, and then use JPublisher to generate their own tools such as Java bean class. If you use this approach, not only Java applications can use the object model of application software and other applications need to share your data and operation of application software can also use the application in the object model.
10, using SQL to complete the database operations
I would like to introduce the most important experience is full use of SQL's set-oriented approach to solving database processing needs, rather than using Java and other process-oriented programming languages.
If the programmers want to find a table of many rows, each row will result in the search for the other data in the table, finally, programmers created a separate batch UPDATE command to update the first data in the table. With such similar tasks can be set by using multi-Liezi query clause in an UPDATE command to complete. When he was able in a single SQL command to complete the task, so why let the data stream to stream go online? I recommend that users carefully to learn how to maximize the SQL function. Basic tutorial of JDBC driver to set an overview of DriverManager class is the JDBC management, role between the user and the driver. It can be used to track drivers, and drivers in the database and the corresponding connection between the. In addition, DriverManager class is also addressing issues such as driver login time limits and log and track messages displayed affairs. For simple applications, usually programmers need to be used directly in such the only way to DriverManager.getConnection. As the name implies, this method will establish the database connection. JDBC allows the user to call the DriverManager methods getDriver, getDrivers and registerDriver and Driver method connect. However, in most cases, so that DriverManager class manage the details of the best way to establish a connection. 1, trace the driver DriverManager class can be used with a Driver class by calling the method they had carried out its own registration DriverManager.registerDriver right. All Driver classes must contain a static part. It creates an instance of the class, and then load the DriverManager class when the instance to register. In this way, the user under normal circumstances would not directly call DriverManager.registerDriver; but the driver is loaded by the driver automatically call. Load Driver class, and then automatically registered in DriverManager in two ways: by calling the method Class.forName. This will explicitly load the driver class. As this has nothing to do with the external setting, it is recommended to use this method of loading the driver. The following code loads class acme.db.Driver: class.forname ( "acme.db.driver"); If acme.db.Driver prepare for the loading to create an instance, and calls to the instance as a parameter DriverManager.registerDriver (this the case), then it is in the DriverManager driver list, and can be used to create connections. By the driver added to the properties of java.lang.System in jdbc.drivers. This is a DriverManager class loads the driver class name list, separated by a colon: Initialize DriverManager class, it search the system properties jdbc.drivers, if the user has entered one or more of the drivers, then the DriverManager class will attempt to load them. The following code shows programmers how to ~ / .hotjava / properties enter three driver classes (starts, HotJava will it be loaded into the system properties list): jdbc.drivers = foo.bah.driver: wombat.sql . driver: bad.test.ourdriver; right DriverManager method for the first time call will automatically load the driver class. Note: The second method of loading the driver to maintain the default environment. If this point can not guarantee that, then call the method Class.forName to explicitly load each driver becomes more secure. This is also the introduction of a specific driver, because once the DriverManager class is initialized, it will no longer check the jdbc.drivers list of attributes. In both cases, the new Driver class should be loaded by calling DriverManager.registerDriver class to register itself. As mentioned above, will automatically load the class in the process. For security reasons, JDBC management will keep track of which class loader provided which driver. In this way, when the DriverManager class to open a connection, it uses only the local file system or with the connection request issued by the code in the same class loader provided by the driver. 2, to establish a connection and load the Driver class registration in the DriverManager class, they can be used in conjunction with a database connection. When you call the method DriverManager.getConnection send connection request, DriverManager will check each driver to see if it can establish a connection. Sometimes, there may be multiple JDBC drivers can be connected with a given URL. For example, a given remote database connection, you can use the JDBC-ODBC bridge driver, JDBC driver to the common network protocol or database vendor-supplied drivers. In this case, the test driver, the order of importance, because DriverManager will use it to find the first one can successfully connect to the given URL drivers. First of all DriverManager tries to register in order to use each driver (jdbc.drivers drivers are always listed in the first register). It will skip the code can not be trusted driver, unless the load of their sources and trying to open the source code for the same connection. It does this by rotating each driver call the method Driver.connect, users have begun to pass to them to pass to the method DriverManager.getConnection the URL to the driver of the test, and then connect the first one out the URL to identify the driver. At first glance, the efficiency of this method is not high, but can not be loaded at the same time dozens of drivers, so each time you connect the actual takes only a few procedure calls and string comparison. The following code is normally used driver (such as the JDBC-ODBC bridge driver) to connect all of the steps required for an example: Class.forName ( "sun.jdbc.odbc.JdbcOdbcDriver"); / / load the driver String url = "jdbc: dbc: fred"; DriverManager.getConnection (url, "userID", "passwd"); this article from 51CTO.COM technology blog