JDBC connects to different DBs

1、Oracle8/8i/9i（thin）
Class.forName(“oracle.jdbc.driver.OracleDriver”).newInstance();
String url=”jdbc:oracle:thin:@localhost:1521:orcl”;
//orcl is ORACLE_SID
String user=”test”;
String password=”test”;
Connection conn= DriverManager.getConnection(url,user,password);

 

2、DB2
Class.forName(“com.ibm.db2.jdbc.app.DB2Driver “).newInstance();
String url=”jdbc:db2://localhost:5000/sample”;
//sample is DB name
String user=”admin”;
String password=”";
Connection conn= DriverManager.getConnection(url,user,password);

 

3、Sql Server7.0/2000
Class.forName(“com.microsoft.jdbc.sqlserver.SQLServerDriver”).newInstance();
String url=”jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=mydb”;
//myDB is DB name
String user=”sa”;
String password=”";
Connection conn= DriverManager.getConnection(url,user,password);

 

4、Sybase
Class.forName(“com.sybase.jdbc.SybDriver”).newInstance();
String url =” jdbc:sybase:Tds:localhost:5007/myDB”;
//myDB is DB name
Properties sysProps = System.getProperties();
SysProps.put(“user”,”userid”);
SysProps.put(“password”,”user_password”);
Connection conn= DriverManager.getConnection(url, SysProps);

 

5、Informix
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 DB name
Connection conn= DriverManager.getConnection(url);

 

6、MySQL
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 is DB name
Connection conn= DriverManager.getConnection(url);

7、PostgreSQL
Class.forName(“org.postgresql.Driver”).newInstance();
String url =”jdbc:postgresql://localhost/myDB”
//myDB is DB name
String user=”myuser”;
String password=”mypassword”;
Connection conn= DriverManager.getConnection(url,user,password);

From http://www.ixdba.net/article/0a/499.html

JDBC connects to ORACLE 10g DB

Read the following first

http://download-uk.oracle.com/docs/cd/B10501_01/java.920/a96654/getsta.htm#1001200

System panel => user environment variable
PATH to add D:oracleproduct10.2.0db_1jdkbin => take effect right away

System environment variable PATH to add D:oracleproduct10.2.0db_1jdkbin
=> reboot to take effect
set %PATH%=%PATH%;D:oracleproduct10.2.0db_1jdkbin

ORACLE_SID=FUN
login as SYS
SQL> alter user sh identified by sh account unlock;
SQL> grant create session, resource to sh;

Hello.java

public class Hello {
    public static void main(String[] args) {
        for (int i=0; i < args.length; i++) {
            System.out.println("Hello " + args[i]);
        }
    }
}

C:jdbc>
javac -classpath D:oracleproduct10.2.0db_1jdbclibclasses12.jar Hello.java
C:jdbc>
java -classpath D:oracleproduct10.2.0db_1jdbclibclasses12.jar;. Hello tt yy
Hello tt
Hello yy

JDBCVersion.java

import java.sql.*;
import oracle.jdbc.driver.*;

class JDBCVersion
{
   public static void main (String args[])
          throws SQLException
   {
      // Load the Oracle JDBC driver
      DriverManager.registerDriver
              (new oracle.jdbc.driver.OracleDriver());
      Connection conn = DriverManager.getConnection
              ("jdbc:oracle:thin:@192.168.61.3:1521:FUN","sh","sh");

      // Create Oracle DatabaseMetaData object
      DatabaseMetaData meta = conn.getMetaData();

      // gets driver info:
      System.out.println("JDBC driver version is " + meta.getDriverVersion());
   }
}

C:jdbc>
javac -classpath D:oracleproduct10.2.0db_1jdbclibclasses12.jar JDBCVersion.java
C:Downloadsrxcanadajdbc>
java -classpath D:oracleproduct10.2.0db_1jdbclibclasses12.jar;. JDBCVersion
JDBC driver version is 10.2.0.3.0

JdbcCheckup.java

/*
 * This sample can be used to check the JDBC installation.
 * Just run it and provide the connect information.  It will select
 * "Hello World" from the database.
 */

// You need to import the java.sql package to use JDBC
import java.sql.*;

// We import java.io to be able to read from the command line
import java.io.*;

class JdbcCheckup
{
   public static void main(String args[])
          throws SQLException, IOException
   {
      // Load the Oracle JDBC driver
      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

      // Prompt the user for connect information
      System.out.println("Please enter information to test connection to the database");
      String user;
      String password;
      String database;

      user = readEntry("user: ");
      int slash_index = user.indexOf('/');
      if (slash_index != -1)
      {
         password = user.substring(slash_index + 1);
         user = user.substring(0, slash_index);
      }
      else
         password = readEntry("password: ");
         database = readEntry("database(a TNSNAME entry): ");

      System.out.print("Connecting to the database...");
      System.out.flush();

      System.out.println("Connecting...");
      Connection conn = DriverManager.getConnection ("jdbc:oracle:oci:@" + database, user, password);
      System.out.println("connected.");

      // Create a statement
      Statement stmt = conn.createStatement();

      // Do the SQL "Hello World" thing
      ResultSet rset = stmt.executeQuery("select 'Hello World' from dual");

      while (rset.next())
         System.out.println(rset.getString(1));
      // close the result set, the statement and connect
      rset.close();
      stmt.close();
      conn.close();
      System.out.println("Your JDBC installation is correct.");
   }

   // Utility function to read a line from standard input
   static String readEntry(String prompt)
   {
      try
      {
         StringBuffer buffer = new StringBuffer();
         System.out.print(prompt);
         System.out.flush();
         int c = System.in.read();
         while (c != 'n' && c != -1)
         {
            buffer.append((char)c);
            c = System.in.read();
         }
         return buffer.toString().trim();
      }
      catch(IOException e)
      {
         return "";
      }
   }
}

C:jdbc>
javac -classpath D:oracleproduct10.2.0db_1jdbclibclasses12.jar JdbcCheckup.java
C:jdbc>
java -classpath D:oracleproduct10.2.0db_1jdbclibclasses12.jar;. JdbcCheckup
Please enter information to test connection to the database
user: sh
password: sh
database(a TNSNAME entry): FUN
Connecting to the database…Connecting…
connected.
Hello World
Your JDBC installation is correct.

set classpath in System panel’s user environment variable as follows:
classpath
D:oracleproduct10.2.0db_1jdbclibclasses12.jar;.

Then compile and execute without -classpath option

C:jdbc>javac JDBCVersion.java
C:jdbc>java JDBCVersion
JDBC driver version is 10.2.0.3.0

C:jdbc>javac JdbcCheckup.java
C:jdbc>java JdbcCheckup
Please enter information to test connection to the database
user: sh
password: sh
database(a TNSNAME entry): FUN
Connecting to the database…Connecting…
connected.
Hello World
Your JDBC installation is correct.

JdbcCheckup1.java

/*
 * This sample can be used to check the JDBC installation.
 * Just run it and provide the connect information.  It will select
 * "Hello World" from the database.
 */

// You need to import the java.sql package to use JDBC
import java.sql.*;

// We import java.io to be able to read from the command line
import java.io.*;

class JdbcCheckup1
{
   public static void main(String args[])
          throws SQLException, IOException
   {
      // Load the Oracle JDBC driver
      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

      // Prompt the user for connect information
      System.out.println("Please enter information to test connection to the database");
      String user;
      String password;
      String database;

      user = readEntry("user: ");
      int slash_index = user.indexOf('/');
      if (slash_index != -1)
      {
         password = user.substring(slash_index + 1);
         user = user.substring(0, slash_index);
      }
      else
         password = readEntry("password: ");
         database = readEntry("database(a TNSNAME entry): ");

      System.out.print("Connecting to the database...");
      System.out.flush();

      System.out.println("Connecting...");
      Connection conn = DriverManager.getConnection ("jdbc:oracle:oci:@" + database, user, password);
      System.out.println("connected.");

      // Create a statement
      Statement stmt = conn.createStatement();

      // Do the SQL "Hello World" thing
      ResultSet rset = stmt.executeQuery("select * from user_tables");

      while (rset.next())
         System.out.println(rset.getString(1));
      // close the result set, the statement and connect
      rset.close();
      stmt.close();
      conn.close();
      System.out.println("Your JDBC installation is correct.");
   }

   // Utility function to read a line from standard input
   static String readEntry(String prompt)
   {
      try
      {
         StringBuffer buffer = new StringBuffer();
         System.out.print(prompt);
         System.out.flush();
         int c = System.in.read();
         while (c != 'n' && c != -1)
         {
            buffer.append((char)c);
            c = System.in.read();
         }
         return buffer.toString().trim();
      }
      catch(IOException e)
      {
         return "";
      }
   }
}

C:jdbc>javac JdbcCheckup1.java
C:jdbc>java JdbcCheckup1
Please enter information to test connection to the database
user: sh
password: sh
database(a TNSNAME entry): FUN
Connecting to the database…Connecting…
connected.
SALES_TRANSACTIONS_EXT
DR$SUP_TEXT_IDX$N
DR$SUP_TEXT_IDX$K
SALES
COSTS
PROMOTIONS
MVIEW$_EXCEPTIONS
CHANNELS
DR$SUP_TEXT_IDX$R
SUPPLEMENTARY_DEMOGRAPHICS
CUSTOMERS
CAL_MONTH_SALES_MV
DR$SUP_TEXT_IDX$I
FWEEK_PSCAT_SALES_MV
PRODUCTS
TIMES
COUNTRIES
Your JDBC installation is correct.

The Basic Steps to Connect Oracle and Java Program

To make a connection between Oracle database and your Java program. To do this work, you need the JDBC (Java DataBase Connectivity) driver. The file name of Oracle’s JDBC driver is classes12.zip or classes12.jar. By default, Oracle have included this driver at the software installation process. Its location is in the ORACLE_HOME\jdbc\lib directory. For example, if our ORACLE_HOME is C:\Oracle\Ora90 then the JDBC driver will be placed in C:\Oracle\Ora90\jdbc\lib directory.

Make sure to set the Java CLASSPATH correctly. Here is the command line to do it. (Note: Assume the ORACLE_HOME is C:\Oracle\Ora90)

set CLASSPATH=.;C:\Oracle\Ora90\jdbc\lib\classes12.jar
or
set CLASSPATH=.;C:\Oracle\Ora90\jdbc\lib\classes12.zip

In addition, you can also set the CLASSPATH in your autoexec.bat file on your Windows operating system.

Now, just follow these steps:

STEP 1. Import the java.sql package into your program. ——————————————————
import java.sql.*;

The syntax above will import all classes in the java.sql package. If you want to import a few of them, you can write the syntax like this

import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.Connection;

It only imports the Driver, DriverManager, and Connection class into your Java program.

STEP 2. Load the Oracle’s JDBC driver.
————————————–
There are two ways to load your JDBC driver. The first, use the forName() method of java.lang.Class class.

Class.forName(“oracle.jdbc.driver.OracleDriver”);

And the second way is use the registerDriver() method of DriverManager class.

DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

STEP 3. Create a Connection object.
———————————–
To create a Connection object, use the getConnection() method of DriverManager class. This method takes three parameters: URL, username, and password.

Connection conn =
DriverManager.getConnection(
“jdbc:oracle:thin:@mylaptop:1521:ORADB”, // URL
“budiraharjo”, // username
“SDDNBandung” // password
);

STEP 4. Create a Statement object.
———————————-
The Statement object can be created by call the createStatement() method of Connection object that you made before. So, you can write the following code

Statement mystat = conn.createStatement();

STEP 5. Execute your SQL statement.
———————————–

After you create a Statement object successfully, you can execute a query (SELECT statement) from your Java program using the executeQuery() method of Statement class. The result of execution process will be stored in ResultSet object, so you need to declare an object of ResultSet first. Here is the code.

ResultSet rs = mystat.executeQuery(“select custno, custname from customer”);

STEP 6. Display your data.
————————–
The next step is display your data using the looping control.

while (rs.next()) {
System.out.println(
rs.getInt(1) + // first column
“\t” + // the horizontal tab
rs.getString(2) // second column
);
}

STEP 7. Close your statement and connection.
——————————————–
mystat.close();
conn.close();

Here is the complete code.

/*********************************************************************
* File name : SimpleOraJava.java
* Author : Budi Raharjo (PT. Sigma Delta Duta Nusantara, Bandung)
* Blog : http://mbraharjo.blogspot.com
*
*********************************************************************/
import java.sql.*;

class SimpleOraJava {
public static void main(String args[]) throws SQLException {
DriverManager.registerDriver(
new oracle.jdbc.driver.OracleDriver()
);
String serverName = “mylaptop”;
int port = 1521;
String user = “budi”;
String password = “SDDNBandung”;
String SID = “ORADB”;
String URL = “jdbc:oracle:thin:@” + serverName + “:” + port + “:” + SID;
Connection conn = DriverManager.getConnection(URL, user, password);
String SQL = “SELECT CUSTNO, CUSTNAME FROM CUSTOMER”;
Statement stat = conn.createStatement();
ResultSet rs = stat.executeQuery(SQL);
while (rs.next()) {
System.out.println(
rs.getInt(1) +
“\t” +
rs.getString(2)
);
}
stat.close();
conn.close();
}
}