JAVA Database Connectivity Daquan
Java Database Connectivity (JDBC) by a group of Java programming language with the class and interface. JDBC as a tool / database developers with a standard API, so that they can use pure Java API to write database applications. However, the interface is not identical to each developer, so development changes in the environment will bring some configuration changes. This paper brings together different database connections. 1, connecting various databases ways Cheat
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 as 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: / / 18.104.22.168: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 Lianyong 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 way
The following is the use of JDBC to connect a small tutorial on MySql
1, find the driver
MySQL currently offers a java driver for Connection / J, can be downloaded from the MySQL website, and find the mysql-connector-java-3.0.15-ga-bin.jar file, this driver for the pure java driver does not need to do other configurations.
2, dynamic designated classpath
If you need to do to dynamically specify the classpath, in execution mode with-cp. Otherwise the above. Jar file to 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 the connection urljdbc: mysql: / / localhost / databasename [? Pa = va] [& pa = va]
3, the following lists the Oracle database using JDBC to connect to a number of techniques can be used
1, the client software development using 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 clients, another two for the database stored procedures and other server-side Java software. In the client-side software development, we can choose OCI driver or the Thin driver. OCI driver localization using Java interfaces (JNI), through the Oracle client software to communicate with the database. Thin driver is a pure Java driver that communicates directly with the database. In order to obtain the highest performance, Oracle recommends the development of the client software to use OCI driver, which seems to be correct. But I recommend using Thin driver, as found by several tests, under normal circumstances, Thin driver performance more than the OCI driver.
2, turn off auto-commit feature to improve system performance
In the first set up the database connection, by default, the connection is in auto-commit mode. In order to obtain better performance, you can call with boolean false parameter Connection class setAutoCommit () method to disable autocommit 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, time-limited dynamic SQL or using Statement object command
In the implementation of the SQL command, we have two choices: You can use the PreparedStatement object, you can use Statement objects. No matter how many times to use the same SQL command, PreparedStatement parsing and compiling it only once. When using the Statement object, each execution of a SQL command, it will be parsed and compiled. This may make you think that using PreparedStatement object using the Statement object than the faster. However, my tests show that the client software is not the case. Therefore, time-limited SQL operation, unless the bulk to handle SQL commands, we should consider the use of Statement Object .
In addition, the use Statement objects also makes easier the preparation of dynamic SQL command, because we can connect the string to create a valid SQL command. So, I think, Statement objects can dynamically create and execute SQL commands easier.
4, using helper function to format the dynamic SQL command
Use of Statement object is created dynamically executed SQL commands, we need to deal with some formatting issues. For example, if we want to create a table to insert the name of O'Reilly's SQL command, you must use two linked "''" was replaced O'Reilly in the "'" No.. Completion of these work the best way is to create a complete replacement operations helper method, and then taking the formula in the connection string expression of a heart SQL command to create a helper method to use. Similarly, we can make helper method accepts a value of type Date, then let it out based on Oracle's to_date () function of the string expression.
5, using PreparedStatement object to improve the overall efficiency of the database
PreparedStatement object using the implementation of SQL command, the command is parsed and compiled the database, then put the command buffer. Then, whenever the implementation of the same PreparedStatement object, it will be re-analysis time, but will not be re-compiled. Can be found in the buffer pre-compiled commands, and can re-use. In a large number of users Diqiyeji Ying Yong software, Jing Chang will repeat the SQL to perform the same command, Shi Yong Dai Lai's compiled PreparedStatement object to increase the number of Jian Shao Xing Neng Shuo Juku's total. If not, the client create, prepare, implement PreparedStatement Statement task requires longer than the task, I would suggest that the dynamic SQL commands, in addition to all the circumstances of use PreparedStatement objects.
6, repeated in the batch insert or update operation using PreparedStatement object
If the batch to handle insert and update operations can significantly reduce the time they need. Oracle provides the Statement and CallableStatement does not really support the batch, only the PreparedStatement object really support batch processing. We can use addBatch () and executeBatch () method of selection criteria for JDBC batch, or through the use of PreparedStatement object setExecuteBatch () methods and standards of executeUpdate () method of selection of the Oracle proprietary faster method. To use the Oracle proprietary batch system, the method can be invoked as follows setExecuteBatch (): PreparedStatement pstmt3D null; try (((OraclePreparedStatement) pstmt). SetExecuteBatch (30); ... pstmt.executeUpdate ();)
Call setExecuteBatch () when the specified value is a ceiling, when reached this value, it will automatically trigger SQL command, the standard of executeUpdate () method will be sent to the database as a batch. We can call the PreparedStatement class sendBatch () method at any time transfer batch tasks.
7, using the Oracle locator to insert, update a large object (LOB)
The PreparedStatement class Oracle BLOB and CLOB are not fully supported the treatment and other large objects, especially the Thin driver does not support the use of PreparedStatement object's setObject () and setBinaryStream () method set the BLOB value, do not support the use setCharacterStream () method set the CLOB value. Only the locator itself in ways we can get from the database type of the value of the LOB. PreparedStatement objects can be used to insert or update the LOB, but the need to obtain the LOB locator value. Because of these two issues, I recommend the use of locator methods to insert, update, or access to LOB values.
8, call a stored procedure using the SQL92 syntax
Call stored procedure, we can use the SQL92 or Oracle PL / SQL, the use of Oracle PL / SQL, and no real benefit, and will maintain your application after the developer of trouble, so I suggest call a stored procedure using SQL92.
9, using the Object SQL object model will be transferred to the database
As can be Oracle's database as an object-oriented database to use, consider the application of object-oriented model to the database. The current approach is to create a Java bean objects as disguised database, the relationship between their properties mapped to the table, and then add in those bean methods. While doing so in Java, no problem, but operations are outside the database, so other applications can not access the database using an object model. If you use Oracle's object-oriented technology, you can create a new database object types in the database, copy the data and operations, and then use JPublisher to generate your own tools such as Java bean class. If you use this method, not only Java application program can use the application software object model, other applications need to share your Zhong Shu Ju and operation of application software Ye can use the software in object mode.
10, using the SQL database operation to complete
I would like to introduce the most important experience is full use of SQL set oriented approach to the database processing requirements, rather than using Java and other process-oriented programming language.
If the programmer to find the number of rows in a table, the results of each line will look the other data in the table, finally, programmers created a separate batch UPDATE command to update the first data in the table. Similarly tasks can be set by using multi-clause in an UPDATE query Liezi command to complete. When can a single SQL command to complete the task, so why let the data stream to stream go online? I suggest that users carefully to learn how to maximize the SQL function. JDBC driver to set a basic tutorial for an overview of JDBC DriverManager class is the management function between the user and the driver. It tracks the available drivers, and drivers in the database and the corresponding connection between. In addition, DriverManager class also address issues such as driver login time limits and the log and trace messages displayed affairs. For simple applications, usually the programmer needs to directly use such the only way to DriverManager.getConnection. As the name implies, this method will establish a connection to the database. JDBC DriverManager method allows the user to call getDriver, getDrivers and registerDriver and Driver method connect. But in most cases, to DriverManager class manages the connection details for the best. 1, tracking available driver Driver DriverManager class contains a class, they have their own by calling the method DriverManager.registerDriver were registered. All Driver classes must contain a static part. It creates an instance of the class, and then load the instance DriverManager class to register. In this way, the user normally 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, the recommended use of this method of loading drivers. The following code loads class acme.db.Driver: class.forname ("acme.db.driver"); if acme.db.Driver preparation for the loading to create an instance, and calls to the instance as a parameter of DriverManager.registerDriver (this the case), it is in the DriverManager driver list, and can be used to create the connection. By the driver to java.lang.System property jdbc.drivers in. This is a DriverManager class loads the driver class name in the list, separated by a colon: initialization DriverManager class, it searches system properties jdbc.drivers, if the user has entered one or more drivers, 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 loaded into the system properties list): jdbc.drivers = foo.bah.driver: wombat.sql . driver: bad.test.ourdriver; on the first call DriverManager method will automatically load the driver class. Note: The second method of loading the driver to maintain the default environment. If this point can not be guaranteed, then call the method Class.forName to explicitly load each driver becomes more secure. This is the introduction of driver-specific methods, because once the DriverManager class is initialized, it will no longer check the jdbc.drivers list of attributes. In both cases, the new class to be loaded by calling DriverManager.registerDriver Driver class to register itself. As mentioned above, will automatically load the class in this process. For security reasons, JDBC management will track which class loader provided which driver. Thus, when the DriverManager class to open a connection, it only uses the local file system or the code of connection requests issued by 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 to establish a connection with the database. When the call connection request issued DriverManager.getConnection method, DriverManager will check each driver to see if it can establish a connection. Sometimes there may be multiple JDBC drivers can be connected with the given URL. For example, a given remote database connection, you can use the JDBC-ODBC bridge driver, JDBC driver to the common network protocols or database vendor drivers. In this case, the test driver for the order is important because DriverManager will use it to find the first to successfully connect to a given URL drivers. First of all, DriverManager attempts to use the order by registered each driver (jdbc.drivers always listed first register the driver). It will skip the code can not trust the driver, unless the source and tried to load them to open the source code for the same connection. It does this by rotating each driver call the method Driver.connect, they transfer the user to start the URL passed to the DriverManager.getConnection method to test on the driver, then connect the first to recognize the driver of the URL. At first glance, the efficiency of this method is not high, but can also add dozens of drivers, so each time you connect the practical with a few procedure calls and string comparison. The following code is usually with the driver (such as JDBC-ODBC bridge driver) all the steps required to establish a connection example: Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver"); / / load the driver String url = "jdbc: dbc: fred"; DriverManager.getConnection (url, "userID", "passwd"); this technology blog from 51CTO.COM