connecting to oracle 10g xe from java

Atomic Pineapple

hey

I currently have a java data access object that allows me to connect to an access database but I want to change it to access an oracle 10g xe (express edition) database.

heres the code i have for connecting to the access database

public Connection getConnection() throws DaoException
    {
        String driver = "sun.jdbc.odbc.JdbcOdbcDriver" ;
        String url = "jdbc:odbc:projectDB" ;
        String username = "" ;
        String password = "" ;

        Connection con = null ;

        try
        {
            Class.forName(driver) ;
            con = DriverManager.getConnection(url, username, password) ;
        }
        catch(ClassNotFoundException e)
        {
            throw new DaoException("getConnection(): " + e.getMessage() ) ;
        }
        catch(SQLException e)
        {
            throw new DaoException("getConnection(): " + e.getMessage() ) ;
        }

        return con ;
    }

I have looked through the oracle documentation and its extremely confusing so just thought I'd ask here to see if anyone knows how to do it, is it easy as in do I change the driver and url to whatever it is for oracle 10g xe? or is there much more work involved in it??

Giblet

XE uses the thin driver.

DriverManager.registerDriver(new OracleDriver()); 
this.con = DriverManager.getConnection("jdbc:oracle:thin:@" + server, user, pass);	

Make sure you have the 10g XE jar files loaded in the classpath too, and not the full oracle jar files.

Atomic Pineapple

The Hitcher wrote: »

XE uses the thin driver.

DriverManager.registerDriver(new OracleDriver()); 
this.con = DriverManager.getConnection("jdbc:oracle:thin:@" + server, user, pass);	

Make sure you have the 10g XE jar files loaded in the classpath too, and not the full oracle jar files.

cheers hitcher,

just a few more Q's

what do I import to allow it to creat a new OracleDriver()
and what do I put in for server?

thanks

CreepingDeath

You could register the driver programatically, but it's nicer to do it via command line. You need the JDBC driver on the classpath (as always) and specify the JDBC driver name(s) in -Djdbc.drivers environment variable.
You can add multiple drivers by separating with ":" eg.

java -Djdbc.drivers=oracle.jdbc.driver.OracleDriver -cp ojdbc14.jar  yourClassName

Also note the URL format... example

jdbc:oracle:thin:@mydbserver:1521:XE

XE always uses "XE" as the service name.
A fully Oracle server would use the database name eg.

jdbc:oracle:thin:@mydbserver:1521:STOCKDB

this.con = DriverManager.getConnection("jdbc:oracle:thin:@" + server + ":1521:XE", user, pass);	

Atomic Pineapple

Ok I currently have this

public Connection getConnection() throws DaoException
    {
        String driver = "sun.jdbc.odbc.JdbcOdbcDriver" ;
        String url = "jdbc:oracle:thin:@mydbserver:1521:XE" ;
        String username = "" ;
        String password = "" ;

        Connection con = null ;

        try
        {
            DriverManager.registerDriver([COLOR="red"]new OracleDriver()[/COLOR]);
            con = DriverManager.getConnection("jdbc:oracle:thin:@" + [COLOR="Red"]server[/COLOR] + ":1521:XE", username, password) ;
        }
        catch(ClassNotFoundException e)
        {
            throw new DaoException("getConnection(): " + e.getMessage() ) ;
        }
        catch(SQLException e)
        {
            throw new DaoException("getConnection(): " + e.getMessage() ) ;
        }

        return con ;
    }

new OracleDriver() - It cant find an instance of OracleDriver so what do I import to get this to work?? or do I have to create the class myself??

server - what variable do I use here?? is it the url variable??

java -Djdbc.drivers=oracle.jdbc.driver.OracleDriver -cp ojdbc14.jar  yourClassName

I have tried doin this through the command prompt and I keep getting an error saying it cant find my class (dao.java) any ideas?

also how do I make sure its on the classpath?

CreepingDeath

1. You don't need the line "DriverManager.registerDriver(new OracleDriver());"
Specifying the driver name in the command line is enough, it then self registers itself.

2. Okay, the classpath needs to be extended to include the current directory.

ie.

java -Djdbc.drivers=oracle.jdbc.driver.OracleDriver -cp .;ojdbc14.jar dao

3. Copy the JDBC driver from your Oracle XE installation into the directory you are working in.
The file ojdbc14.jar should be in somewhere like C:\Oracle10_Xe\app\oracle\product\10.2.0\server\jdbc\lib

4. Regarding the "server" variable that's the network name of the machine you have installed Oracle XE Server on.
If it's on the same machine, you can use "localhost" or "127.0.0.1" as the network name or address.

Here's the full example, and I assume that the database username and password is "system" and "password". Alter accordingly.

public Connection getConnection() throws DaoException
    {
        String url = "jdbc:oracle:thin:@localhost:1521:XE" ;
        String username = "system" ;
        String password = "password" ;

        Connection con = null ;

        try
        {
            con = DriverManager.getConnection(url, username, password) ;
        }
        catch(ClassNotFoundException e)
        {
            throw new DaoException("getConnection(): " + e.getMessage() ) ;
        }
        catch(SQLException e)
        {
            throw new DaoException("getConnection(): " + e.getMessage() ) ;
        }

        return con ;
    }

Giblet

If you want to use an import:

import oracle.jdbc.driver.OracleDriver;

Atomic Pineapple

EDIT: got it working thanks for all the help

CreepingDeath wrote: »

2. Okay, the classpath needs to be extended to include the current directory.

ie.

java -Djdbc.drivers=oracle.jdbc.driver.OracleDriver -cp .;ojdbc14.jar dao

thanks for the help I think I'm nearly there, I try running this through the command prompt but I keep getting the class not found exception, I have tried from countless different positions in the directory.

do I do the prompt from c:\ or where do I go to do the prompt, the location of the Dao class would be c:\jevstudio10133\jdev\mywork\rmbuckets\model\src\dao\

CreepingDeath wrote: »

3. Copy the JDBC driver from your Oracle XE installation into the directory you are working in.
The file ojdbc14.jar should be in somewhere like C:\Oracle10_Xe\app\oracle\product\10.2.0\server\jdbc\lib

I have got this file but where should I place it exactly?? I currently have it placed at c:\jevstudio10133\jdev\mywork\ - so its beside the folder with my project in it, is this correct?