Oracle 11g Express Java JDBC; Database Workspace

0
votes

I am experiencing some difficulty getting to the Oracle 11g Express Workspace in which resides my database.

I am able to register my JDBC Driver via Class.forName("jdbc.oracle.driver.OracleDriver") and to connect successfully via Connection con = DriverManager.getConnection("jdbc:oracle:thin:" + username + "/" + password + "@localhost:1521:xe") but not able to get into the workspace containing the database I created.

Any attempt to create a database via Statement smt = Connection.createStatement() yields a java.lang.UnsupportedOperationException.

I am not even sure if I am connected to the right place. I know when I look into the Oracle Database 11.2 XE parameters it lists the db_name as xe and I can connect to it via the sys as sysdba and using the password I established for the Oracle 11g Express database but I can't get into the workspace when I had created a database and utilized ddl and dml statements to populate it.

Perhaps I simply don't know what to do at this point because I am new to JDBC. I checked my TNSNAMES.ORA file and indeed there is an XE entry representing my Oracle 11g Express as well as other entries representing databases in Oracle. I think I am there I just don't know what to do to get to my XE database I created.

The code I am using to execute a SQL statement is as follows:

    Statement smt = null;


    try {
        smt = connection.createStatement();

        String command = "CREATE table x(y varchar(2))";

        smt.executeLargeUpdate(command);

    } catch (SQLException e) {
        // TODO Auto-generated catch block
        System.out.println(e.getMessage() + e.getCause());
    }
2
javajdbcoracle11g
That driver is deprecated from oracle 9i onwards, have you tried using Class.forName("oracle.jdbc.OracleDriver")? See the following link :tomcat.apache.org/tomcat-5.5-doc/… - GoldenJam
Just changed the driver registration String to oracle.jdbc.OracleDriver and was able to successfully register the database driver. I still need to understand why I am connected to my database but unable to execute SQL. - Mushy
Could you post the code you are using to execute SQL? - GoldenJam
@GoldenJam Posted in the problem description. - Mushy
Well, ok, that should be executeUpdate not executeLargeUpdate. I was intending to use the correct behavior but was sloppy with the autocomplete from Eclipse. However, I still don't know how to get into a database I created through Oracle 11g Express. Right now I am just connected to xe and can run SQL commands. - Mushy

2 Answers

2
votes

To access the objects in your workspace you have to write the SQL statements specifying the object name with the workspace name like "workspacename.objectname".

for e.g.

If you have created your workspace and named it "XESPACE" and created a table "STUDENT" inside the workspace, then to access the STUDENT table from JDBC follow the code below.

Step 1: Register the JDBC Driver as Class.forName("oracle.jdbc.driver.OracleDriver").

Step 2: Open connection with DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:XE",
            "Username", "Password").
            where 127.0.0.1 stands for localhost.

Step 3: Now to access the table inside your workspace write the sql query as follows
            "select * from XESPACE.STUDENT"

1
votes

You need the ojbdc6.jar or equivalent on the classpath. It could also be a number of other problems, so I will detail a working example in the hope it will highlight the problem.

I am using the following configuration and components:

I perform the following steps to generate a connection to an oracle xe database from a java class:

Run the Oracle XE 32 bit for windows installer providing the password 'password'. Then run services.msc to confirm that the TNS listner and the XE services are both running.

enter image description here

Then create a new java project in eclipse. Adding the ojbc6.jar to the build path as an external jar.

enter image description here

Then I run the following class (right click -> run as -> java application) to create the table:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;


public class MyOracleTest {

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("oracle.jdbc.OracleDriver");
        String username = "sys as sysdba";
        String password = "password";
        Connection conn = null;
        Statement st = null;
        try{
            conn = DriverManager.getConnection("jdbc:oracle:thin:" + username + "/" + password + "@localhost:1521:xe");
            st = conn.createStatement();
            String command = "CREATE table x(y varchar(2))";
            st.executeUpdate(command);
        }finally{
        if(st != null){
            st.close();
        }
        if(conn != null){
            conn.close();
        }
        }
    }

I then verify the existance of the table using sqlplus:

enter image description here