This discussion is archived
11 Replies Latest reply: Apr 29, 2013 6:17 AM by 1006118 RSS

first time JDBC connection

1005985 Newbie
Currently Being Moderated
Hi guys,

just installed oracle database express edition for my home computer. i have the database running and i am connected to the database through SQL developer. i couldn't connect via the basic connection as i kept getting an error (listener refused connection... TNS: listener does not know of SID given connect descriptor) so i connected through a TNS connection with network alias: ORCL

my problem is now i don't know how to connect my java code to the database so i can run CreateContacts class to make the tables ... currently when i run the CreateContacts class it does not create the tables in the database (i can create them fine through SQL developer but i need to create them via the CreateContacts class).. so i assume my java connection(// Home Oracle XE) is incorrect in the DBConnection class. can anyone have a look please and help me get my java code connected to my database so i can run the CreateContacts class?

DBConnection class
package database;
  
import java.sql.*;
  
import oracle.jdbc.pool.OracleDataSource;
  
public class DBConnection {
  
    private Connection conn = null;
  
    public Connection openDB()
    {
        try
            {   
                OracleDataSource ods = new OracleDataSource();
  
  
                // College
                // ods.setURL("jdbc:oracle:thin:@//10.10.2.7:1521/global1");
                // ods.setUser("x00075734");
                // ods.setPassword("db27Oct90");
  
                // Home Oracle XE
                ods.setURL("jdbc:oracle:thin:HR/SYSTEM@localhost:1521:XE"); *// not sure if this line is*
                                                                        *// correct, maybe this is why it is not connecting to database*
                ods.setUser("SYSTEM");  // username i use to log into oracle database
                ods.setPassword("db27Oct90"); //password i use to log into oracle database
  
                conn = ods.getConnection();
                System.out.println("connected.");               
            }
        catch (Exception e)
            {
                System.out.print("Unable to load driver " + e);
                System.exit(1);
            }   
        return conn;
    }
    public void closeDB()
    {
        try
            {   
                conn.close();
                System.out.print("Connection closed");
            }
        catch (SQLException e)
            {
                System.out.print("Could not close connection ");
                e.printStackTrace();
            }
    }
}
CreateContacts class
package database;
  
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
  
public class CreateContacts
{ 
    private Connection conn = null;
    private PreparedStatement stmt = null;
    private DBConnection db;
  
    public CreateContacts()
    {
        db = new DBConnection();
        conn = db.openDB();
    }   
    public void CreateContactsTable()
    {
        try
            {           
                // Dropping a Table
                String drop = "DROP TABLE Contacts";
                stmt = conn.prepareStatement(drop);
                stmt.executeUpdate();
  
                // Create a Table
                String create = "CREATE TABLE Contacts " +
                        "(id NUMBER PRIMARY KEY, name VARCHAR(40), address VARCHAR(30), pnumber VARCHAR(30), email VARCHAR(20))";
                stmt = conn.prepareStatement(create);
                stmt.executeUpdate(create);
  
                // Insert data into table
  
                String insertString = "INSERT INTO Contacts(id,name,address,pnumber,email) values(?,?,?,?,?)";          
                stmt = conn.prepareStatement(insertString);
  
                stmt.setInt(1, 1);
                stmt.setString(2, "Peter");
                stmt.setString(3, "23 Lime Lane");
                stmt.setString(4, "018776543");
                stmt.setString(5, "p.cassisy@b.com");               
                stmt.executeUpdate();
  
                stmt.setInt(1, 2);
                stmt.setString(2, "Donal");
                stmt.setString(3, "2 Shelbourne rd");
                stmt.setString(4, "012445678");
                stmt.setString(5, "d.oreilly@b.com");               
                stmt.executeUpdate();
  
                stmt.setInt(1, 3);
                stmt.setString(2, "Mary");
                stmt.setString(3, "4 Richmond rd");
                stmt.setString(4, "018765456");
                stmt.setString(5, "m.lawlor@b.com");                
                stmt.executeUpdate();
  
                stmt.setInt(1, 4);
                stmt.setString(2, "Glen");
                stmt.setString(3, "4 Richmond lane");
                stmt.setString(4, "017854563");
                stmt.setString(5, "g.whelan@b.com");                
                stmt.executeUpdate();
  
                conn.commit();
                stmt.close();
                db.closeDB();
            }       
            catch (SQLException e)
            {
                System.out.print("SQL Exception " + e);
                System.exit(1);
            }   
    }   
    public static void main(String args[]) 
    {
        CreateContacts ct = new CreateContacts();
        ct.CreateContactsTable();   
    }
  
}
Thanks in advance

Edited by: 1002982 on 28-Apr-2013 13:23

Edited by: 1002982 on 28-Apr-2013 13:24

Edited by: 1002982 on 28-Apr-2013 13:25

Edited by: 1002982 on 28-Apr-2013 13:25

Edited by: 1002982 on 28-Apr-2013 13:30

Edited by: EJP on 29/04/2013 11:02
  • 1. Re: first time JDBC connection
    user639304 Explorer
    Currently Being Moderated
    Hello 1002982,

    Have you executed the code you posted? If yes, what error did you get?
  • 2. Re: first time JDBC connection
    1005985 Newbie
    Currently Being Moderated
    hi 639304, thanks for the reply.

    yes when i run the code i get this in the console (not an error but i'm not sure what it is suppose to imply)

    testing US7ASCII against <abc>
    PASSED LOSSY
    testing US7ASCII against <ab?c>
    PASSED LOSSY
    testing US7ASCII against <XYZ>
    PASSED LOSSY
    testing US7ASCII against <longlonglonglong...>
    PASSED LOSSY
    testing WE8ISO8859P1 against <abc>
    PASSED LOSSY
    testing WE8ISO8859P1 against <ab?c>
    PASSED LOSSY
    testing WE8ISO8859P1 against <XYZ>
    PASSED LOSSY
    testing WE8ISO8859P1 against <longlonglonglong...>
    PASSED LOSSY
    testing AL24UTFFSS against <abc>
    PASSED
    testing AL24UTFFSS against <ab?c>
    PASSED
    testing AL24UTFFSS against <XYZ>
    PASSED
    testing AL24UTFFSS against <longlonglonglong...>
    PASSED
    testing UTF8 against <abc>
    PASSED
    testing UTF8 against <ab?c>
    PASSED
    testing UTF8 against <XYZ>
    PASSED
    testing UTF8 against <longlonglonglong...>
    PASSED


    but i'm sure there is something wrong because the tables are not created in the database after i run the CreateContacts class! :P

    Edited by: 1002982 on 28-Apr-2013 14:44

    Edited by: 1002982 on 28-Apr-2013 14:44
  • 3. Re: first time JDBC connection
    EJP Guru
    Currently Being Moderated
    Come off it. The code you posted doesn't print those messages. The question is what exception did you get if any?
  • 4. Re: first time JDBC connection
    1005985 Newbie
    Currently Being Moderated
    that is everything that is printed out in the console window.. and it is not my code it is my lecturers code, the assignment was to link the code up to my database although i can't see to get the connection working. thanks for the replies by the way. is there any other information you need from me to try get it working?

    edit: what are those messages then and what are they telling me? since they are in the console window

    Edited by: 1002982 on 28-Apr-2013 15:34

    Edited by: 1002982 on 28-Apr-2013 15:36
  • 5. Re: first time JDBC connection
    EJP Guru
    Currently Being Moderated
    Then I don't see how you can rationally expect your question to be answered. You are asking us to debug messages printed by unknown code for unknown reasons.
  • 6. Re: first time JDBC connection
    1005985 Newbie
    Currently Being Moderated
    i see.. can you at least tell me if these lines of code look right?

    // Home Oracle XE
    ods.setURL("jdbc:oracle:thin:HR/SYSTEM@localhost:1521:XE");
    ods.setUser("SYSTEM"); // username i use to log into oracle database
    ods.setPassword("db27Oct90"); //password i use to log into oracle database

    that is the connection to my database. SYSTEM and db27Oct90 are the details i use to log in to the database also
  • 7. Re: first time JDBC connection
    rp0428 Guru
    Currently Being Moderated
    >
    i see.. can you at least tell me if these lines of code look right?

    // Home Oracle XE
    ods.setURL("jdbc:oracle:thin:HR/SYSTEM@localhost:1521:XE");
    ods.setUser("SYSTEM"); // username i use to log into oracle database
    ods.setPassword("db27Oct90"); //password i use to log into oracle database

    that is the connection to my database. SYSTEM and db27Oct90 are the details i use to log in to the database also
    >
    No - it isn't right. Why do you have HR/SYSTEM in the URL and then specify SYSTEM and a different password.

    Use the more standard form of the url that does NOT include the user and password.
            String url = "jdbc:oracle:thin:@localhost:1521:mySid";
    driver - hostname - port - sid

    See 'Database URLs and Database Specifiers in the JDBC Developer's Guide
    http://docs.oracle.com/cd/B28359_01/java.111/b31224/urls.htm#BEIJFHHB

    Edited by: rp0428 on Apr 28, 2013 5:11 PM
  • 8. Re: first time JDBC connection
    1005985 Newbie
    Currently Being Moderated
    thanks for the reply. i changed it to ""jdbc:oracle:thin:@localhost:1521:orcl".

    although i am still getting this garbage in the console. where is this coming from? no exceptions, nothing except:

    testing US7ASCII against <abc>
    PASSED LOSSY
    testing US7ASCII against <ab?c>
    PASSED LOSSY
    testing US7ASCII against <XYZ>
    PASSED LOSSY
    testing US7ASCII against <longlonglonglong...>
    PASSED LOSSY
    testing WE8ISO8859P1 against <abc>
    PASSED LOSSY
    testing WE8ISO8859P1 against <ab?c>
    PASSED LOSSY
    testing WE8ISO8859P1 against <XYZ>
    PASSED LOSSY
    testing WE8ISO8859P1 against <longlonglonglong...>
    PASSED LOSSY
    testing AL24UTFFSS against <abc>
    PASSED
    testing AL24UTFFSS against <ab?c>
    PASSED
    testing AL24UTFFSS against <XYZ>
    PASSED
    testing AL24UTFFSS against <longlonglonglong...>
    PASSED
    testing UTF8 against <abc>
    PASSED
    testing UTF8 against <ab?c>
    PASSED
    testing UTF8 against <XYZ>
    PASSED
    testing UTF8 against <longlonglonglong...>
    PASSED
  • 9. Re: first time JDBC connection
    rp0428 Guru
    Currently Being Moderated
    >
    although i am still getting this garbage in the console. where is this coming from? no exceptions, nothing except:
    >
    How would anyone know. We can't see your console. You haven't posted ANY info about how you are executing the code or what commands you are using. Nothing except: 'still getting this garbage'.
  • 10. Re: first time JDBC connection
    DrClap Expert
    Currently Being Moderated
    Obviously I don't know where that output is coming from. But the fact that you are getting it suggests that you are running some other code and not the CreateContacts class which you posted. And since you aren't running CreateContacts, then perhaps it isn't surprising that the database tables it's supposed to create aren't being created.

    So you're going to have to go back to your instructor and find out how to run code which you wrote.
  • 11. Re: first time JDBC connection
    1006118 Newbie
    Currently Being Moderated
    I would like to suggest you is use same code in eclispe run with debug points.meaning make some breakpoints check the error where it goes.

    the other issue may be go to run->type services.msc check the oracle database ex:(10g or 11g) is on or off. start or stop mode. if stop.. start the service then run the application with break points

    Edited by: 1003115 on Apr 29, 2013 6:17 AM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points