Java Data Warehouse connection routines and precautions

The following uses JDBC to connect to Oracle 10 Gb as an example.

Java can connect to the database through JDBC. Download the JDBC jar package.

Official: http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-10201-088211.html

You can download the ojdbc14.jar version. (Jdk1.4, jdk1.5, and jdk1.6 are supported)

This version is: (you may need to log on to the Oracle website)

Http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-10201-088211.html

The instance code is as follows:

import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.util.ArrayList;import java.util.List;import javax.sql.PooledConnection;import oracle.jdbc.pool.OracleConnectionPoolDataSource;public class JDBCTest {private String url = null;/* *  */public JDBCTest(String sHostName, String sPortNumber, String sSid) {url = "jdbc:oracle:thin:@" + sHostName + ":" + sPortNumber + ":" + sSid;// if JDK1.6 you also can use as// url = "jdbc:oracle:thin:@" + sHostName + ":" + sPortNumber + "/" + sSid;}public List<String> getList(String sUsrName, String sPassword, String sql) {List<String> resultList = new ArrayList<String>();try {OracleConnectionPoolDataSource ocpds = new OracleConnectionPoolDataSource();String url1 = System.getProperty("JDBC_URL");if (url1 != null)url = url1;ocpds.setURL(url);ocpds.setUser(sUsrName);ocpds.setPassword(sPassword);PooledConnection pc = ocpds.getPooledConnection();Connection conn = pc.getConnection();PreparedStatement pstmt = conn.prepareStatement(sql);ResultSet rset = pstmt.executeQuery();while (rset.next()) {resultList.add(rset.getString(1));}rset.close();pstmt.close();conn.close();pc.close();} catch (Exception e) {}return resultList;}/** * @param args */public static void main(String[] args) {                // use you real infoString sUsrName = "";String sPassword = "";String sql = "";JDBCTest jdbctest = new JDBCTest("localhost", "1521", "orcl");List<String> list = jdbctest.getList(sUsrName, sPassword, sql);System.out.println(list.size());}}

Note the following:

Url = "JDBC: oracle: thin: @" + shostname + ":" + sportnumber + "/" + SSID;

In jdk1.6, you can use the above URL. That is, the port and Sid can be separated. In JDK 1.5, ":" is the only option ":".

In addition to using the connection pool, you can also use the traditional connection pool.

Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();Connection conn = DriverManager.getConnection(url, suUserName,passWord);

The following is an article about differences between JDBC versions.

Classes12.jar, ojdbc14.jar, ojdbc5.jar, and ojdbc6.jar

When using the oracle JDBC driver, do you solve some problems by replacing different versions of the oracle JDBC driver? The most commonly used ojdbc14.jar has multiple versions, and classes12.jar has multiple versions. Do you know that?

 

Connection Type:
1. jdbc oci: OCI is the abbreviation of Oracle call interface, which is similar to the traditional ODBC driver. Because it requires Oracle call interface andnet8, it needs to install the client software on the machine that runs the Java program using this driver, in fact, it mainly uses the OCI and server configuration provided in DLL mode in the orcale client.
2. JDBC thin: thin is the meaning of for thin client, which is generally used in Java programs running in Web browsers. It does not communicate through OCI or net8, but through Java sockets, and is a pure Java-implemented driver. Therefore, you do not need to install orcale client software on the client machine that uses JDBC thin, therefore, it has good portability and is usually used in Web development.

 

-------------------------------------------------------------------------------

Released with Oracle 8iOracle JDBC driver version 8.1.7
Classes111.zip for JDK 1.1.x
Classes12.zip for JDK 1.2.x
Only ZIP files, no jar files.

 

 

-------------------------------------------------------------------------------

Released with Oracle 9iOracle JDBC driver 9.2.0

 

Classes111.jar for JDK 1.1.x
Classes12.jar is applicable to JDK 1.2 and JDK 1.3 (my project environment is jdk1.6, Oracle 10g, and Windows. No problem is found when this is used)
Ojdbc14.jar for JDK 1.4
Classes111.zip for JDK 1.1.x
Classes12.zip for JDK 1.2.x

* ** _ G. jar is only compiled using javac-g to generate all debugging information.

 

 

New features:
1. The thin connection Driver provides direct support for bfile, blob, and clob. Previously, it was usually implemented by calling PL/SQL.
2. Support for JDBC 3.0 features
3. ojdbc14.jar supports JDK 1.4.
4. ojdbc14.jar supports savepoint)
5. preparedstatement can be used in different connection pools, which is an important performance improvement.

 

Since then, the names of new jar files are in the ojdbc <JDK ver>. Jar format, and the names of previous jar files remain unchanged.
 

 

-------------------------------------------------------------------------------

Released in Oracle 10.2Oracle JDBC driver Version 10.2
 

Classes12.jar is applicable to JDK 1.2 and JDK 1.3.

Ojdbc14.jar for JDK 1.4 and 5.0
* ** _ G. jar is only compiled using javac-g to generate all debugging information.

 

Features:

1. Full support for JDK 1.5
2. Supports JDBC 3.0.

 

-------------------------------------------------------------------------------

Released in Oracle 11.1Oracle JDBC driver version 11.1

Ojdbc5.jar: Applicable to JDK 5
Ojdbc6.jar: Applicable to jdk6. (if you use jdk1.5, you cannot use this driver)
* ** _ G. jar is only compiled using javac-g to generate all debugging information.

 

 

 

New features:
1. ojdbc6.jar: jdk6 and JDBC 4.0 are supported. The new java. SQL. SQLXML type is not supported.

Ojdbc5.jar: jdk5 and JDBC 3.0 are fully supported.
2. Oracle. JDBC. oracledriver is recommended. Oracle. JDBC. Driver. oracledriver is not recommended. Oracle. JDBC is recommended for each release starting from 9.0.1.

3. j2se 1.2, 1.3, and 1.4 are no longer supported. 11r1 does not include jar and zip of these versions. If you still use these versions, you can continue to use JDBC of 10gr2.
4. 11gr1 thin driver supports AES encryption algorithm, sha1 hash algorithm, radius, Kerberos, and SSL authentication mechanism.
5. The anydate and anytype types are supported. These two types are introduced from 9i. Before 11r1, programmers can only operate through PL/SQL.
6. Advanced queue support. 11r1 provides a high-performance interface for accessing aq.
7. Supports Database Change notifications.
8. Start and close the database of thin and OCI. 11r1 provides this method to start and close the database.
9. New Factory methods. Oracle JDBC 11r1 oracle. JDBC. oracleconnection provides a factory method for creating Oracle Objects.
Including array, bfile, date, intervalds, number, struct, time, timestamp, timestamp, etc.

 

 

---------------------------------------------------

In general, the new version of JDBC has high Driver Performance and many bugs have been found and solved.

I encountered a problem where I used ojdbc14.jar (which version I don't remember) to insert 0.1 million records in batches. Actually, I only inserted more than 30 thousand records, but I lost others. After I changed to ojdbc6.jar, it is OK to insert 1 million entries in batches at a time.

 

Try to use the same driver as the database version. If there is a bug, try another JDBC driver of a later version.

 

If you do not know the version of a JDBC jar package, you can decompress the jar package, and then META-INF \ manifest. find "oracle JDBC driver version-10.1.0.2.0" in the MF file and you will find the version.

 

Address: http://tiantian0521.blog.163.com/blog/static/4172088320117294265766/

Add a JDBC connection to each database

Oracle8/8i/9i Database (thin Mode)
Class. forname ("oracle. JDBC. Driver. oracledriver "). Newinstance ();
String url = "JDBC: oracle: thin :@ localhost: 1521: orcl ";
// Orcl is the SID of the database
String user = "test ";
String Password = "test ";
Connection conn = drivermanager. getconnection (URL, user, password );

DB2 database
Class. forname ("com. IBM. db2.jdbc. App. db2driver "). Newinstance ();
String url = "JDBC: DB2: // localhost: 5000/sample ";
// Sample is your database name
String user = "admin ";
String Password = "";
Connection conn = drivermanager. getconnection (URL, user, password );

SQL Server7.0/2000 database
Class. forname ("com. Microsoft. JDBC. sqlserver. sqlserverdriver "). Newinstance ();
String url = "JDBC: Microsoft: sqlserver: // localhost: 1433; databasename = mydb ";
// Mydb is a database
String user = "sa ";
String Password = "";
Connection conn = drivermanager. getconnection (URL, user, password );

Sybase Database

Class. forname ("com. Sybase. JDBC. sybdriver "). Newinstance ();
String url = "JDBC: Sybase: TDS: localhost: 5007/mydb ";
// Mydb is your database name
Properties sysprops = system. getproperties ();
Sysprops. Put ("user", "userid ");
Sysprops. Put ("password", "user_password ");
Connection conn = drivermanager. getconnection (URL, sysprops );

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 indicates the Database Name
Connection conn = drivermanager. getconnection (URL );

MySQL database
Class. forname ("org. gjt. Mm. MySQL. Driver "). Newinstance ();
String url = "JDBC: mysql: // localhost/mydb? User = soft & Password = soft1234 & useunicod
E = true & characterencoding = 8859_1"
// Mydb indicates the Database Name
Connection conn = drivermanager. getconnection (URL );

PostgreSQL database
Class. forname ("org. PostgreSQL. Driver "). Newinstance ();
String url = "JDBC: PostgreSQL: // localhost/mydb"
// Mydb indicates the Database Name
String user = "myuser ";
String Password = "mypassword ";
Connection conn = drivermanager. getconnection (URL, user, password );