Connect to Oracle, SQL Server, and PostgreSQL through JDBC in Linux

Today, we need to collect statistics on the updates of the three website columns. The three websites use different database management systems at the backend. The initial idea is to create a small Tomcatwebapp to access the three backend databases through JDBC and generate reports based on the returned data. 1. Development Environment Database Management System: one is SqlServer2000,

Today, we need to collect statistics on the updates of the three website columns. The three websites use different database management systems at the backend. The initial idea is to create a small Tomcat webapp to access the three backend databases through JDBC and generate reports based on the returned data. 1. Development Environment Database Management System: one is SqlServer 2000,

Today, we need to collect statistics on the updates of the three website columns. The three websites use different database management systems at the backend. The initial idea is to create a small Tomcat webapp to access the three backend databases through JDBC and generate reports based on the returned data.

1. Development Environment

• Database management system: one is SqlServer 2000, the other is Oracle 9i, and the other is PostgreSQL9.1
• Tomcat execution platform: CentOSx64 + JDK7.0x64 (all 64-bit environment)

2 JDBC driver 2.1 Oracle9i

Oracle officially provides the ojdbc6.jar type 4 JDBC driver, only this file can complete the JDBC support, copy it to the WEB-INF/lib. The key code is as follows:

Class. forName ("oracle. jdbc. driver. OracleDriver ");
DriverManager. registerDriver (new oracle. jdbc. driver. OracleDriver ());
String dbUrl = "jdbc: oracle: thin: @ IP addresses: 1521: Instance name ";

Oracle provides excellent compatibility. The JDBC driver of this version still supports oracle9i.

2.2 SqlServer2000

Microsoft also provides the SqlServer type 4 JDBC driver. Specifically, there are two files: sqljdbc4.jar and sqljdbc. jar, where the latest version with 4 can run under JDK, but the version without 4 cannot run on

In JDK 7, the problem is that sqljdbc4.jar only supports SqlServer 2005 and later versions, and does not support SqlServer 2000. That is to say, to use the JDBC driver provided by Microsoft to operate SqlServer 2000, your JDK version must be lower than JDK 7, but our development environment has been determined to be JDK 7.

There are two solutions:

One is to use the JDBC-ODBC bridge, unixODBC is the ODBC implementation on the Linux platform, and then need to install ODBC 2000 ODBC driver, www.unixODBC.org provides two links, one is not free open source; another is the freetds-based ODBC driver. I don't know why www.freetds.org cannot be opened. I used freetds command lines to connect to SqlServer. The configuration of ODBC is very complex, especially in Linux environments. I gave up this idea in my laziness.

Second, since freetds can operate SqlServer, why not cross ODBC, let JDBC directly based on tds, very lucky to find a good solution on the Internet: jTDS, see http://jtds.sourceforge.net. This project provides the JDBC implementation based on the TDS protocol, is a pure type 4 JDBC driver, through a jtds-1.3.0.jar can meet the needs to access SqlServer2000 through JDBC. It can also run in JDK to meet my requirements.

Finally, the second approach was adopted. The key code is as follows:

Class. forName ("net. sourceforge. jtds. jdbc. Driver ");
DriverManager. registerDriver (new net. sourceforge. jtds. jdbc. Driver ());
String dbUrl = "jdbc: jtds: sqlserver: // ip: 1433/Database Name ";

2.3 PostgreSQL9.1

Official Website jdbc.potgresql.org provides postgresql-9.2-1000.jdbc4.jar, which is also a type 4 JDBC driver, download can be used. The key code is as follows:

Class. forName ("org. postgresql. Driver ");
DbUrl = "jdbc: postgresql: // ip: 5432/Database Name ";

This driver is automatically registered during loading, so you do not need to perform registerDriver separately.

3. Conclusion

Through this small project, we will summarize the JDBC driver options for commonly used databases:

(1) for SQL Server 2005 and later versions, we recommend that you use the official Microsoft type 4JDBC driver. For version 2000, if the JDK environment is older, you can use the official Microsoft version, if JDK7.0 is used, the type 4JDBC driver jTDS is used;

(2) For Oracle, the official thin driver is recommended for all versions, that is, the JDBC driver of type4;

(3) PostgreSQL and MySql provide type 4 JDBC drivers.

Networks are a treasure, but to find what you really need, you must have a complete knowledge architecture and clear analysis ideas.

If you need to access Sqlserver and Oracle in the. net Framework, refer to my other blog: http://blog.csdn.net/smstong/article/details/5874451