How to fetch records from Oracle databse using Java

Question

I'm learning database with Java using JDBC. I've created a svery basic program after reading some articles on JDBC on the internet. My code is not giving any error but I'm not getting the output also. Here's is my code:

This is my connectivity code: DataService

    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.logging.Level;
    import java.util.logging.Logger;
    
    public class DataService {
        static Connection con;
        public static void main(String args[]){
            try {
                Class.forName("oracle.jdbc.driver.OracleDriver");
                con=DriverManager.getConnection(
                "jdbc:oracle:thin:@localhost:1521:xe","system","scott");
            }
            catch(Exception e){ 
                System.out.println(e);
            }
        }
        
        public ResultSet getRecords() {
            System.out.println("inside getRecords()");
            ResultSet rs=null;
            try {
                System.out.println("Inside Rs loop");
                Statement stmt=con.createStatement();
                rs=stmt.executeQuery("select * from emp");
                System.out.println("RS before entering while: "+rs);
                while(rs.next())
                    System.out.println("Inside while loop");
                    System.out.println("Data: " +rs.getInt(0)+"  "+rs.getString(1));
            } catch (SQLException ex) {
                Logger.getLogger(DataService.class.getName()).log(Level.SEVERE, null, ex);
            }
            System.out.println("Before sending: "+rs);
            return rs;
        }
    }

And here's is my application MainFrame.java

public class MainFrame extends Application {

    ...

    @Override
    public void start(Stage primarystage) {        
        ...
        try {
            System.out.println("Inside try");
            DataService dataservice = new DataService();
            System.out.println("Result set: "+dataservice.getRecords());
        } catch (Exception e) {
            //TODO: handle exception
        }
    }
}

I'm getting this output:

Inside try

inside getRecords()

Inside Rs loop

I cant see any output related to records. Also I've checked select * from emp. There are rows in that table already. I'm missing out something very important here. Please correct me.

Unrelated to your problem, but: do **NOT** use the SYSTEM (or SYS) account for your normal work and never create your own tables in the SYSTEM or SYS schema. Never. Create a new regular user and use that. — , Feb 23 '20 at 09:24
Oh. I thought lets quickly try this with the SYS account then I'll create a new dedicated user for actual project. But thank you so much. I appreciate this. Even my professors told me so. :-) — Tanzeel, Feb 23 '20 at 09:31

score 2 · Accepted Answer · answered Feb 23 '20 at 09:55

When you create DataService instance DataService dataservice = new DataService(); method main() is not called, therefore variable conn is not initialized and remains null. After that when calling Statement stmt=con.createStatement(); a NullPointerException (NPE) is thrown. This NPE is not catched neither in this catch block catch (SQLException ex) because it catches only SQLExceptions nor in this one catch (Exception e), because there is no handling logic provided.

First step in making code work is adding some exception handling logic in method start() of MainFrame class (never leave empty catch blocks, it's considered as an antipattern).

Then move logic for connection initialization either to getRecords() method making it local variable or to DataService constructor, so variable conn will be initialized, when accessing it.

Next big issue is closing Connection, Statement and ResultSet variables after finishing work with them. Since java 7 you can use try-with-resources syntax to handle such resources.

Also I would recommend to handle ResultSet inside of getRecords() method without exposing it outside DataService class.This class acts as Data Access Object (DAO) and it would be better to keep implementation details inside. Instead you could create some simple Employee class, which instances could be initialized inside while(rs.next()) loop, filled with values from result set and added to some collection. This collection could be returned from getRecords() method.

I implemented changes that you suggested and code is working now. I didn't realize that `conn` is not initialized. So I added a constructor in my code. But I didn't understand the last para of your answer. Here's the code: http://collabedit.com/rr4by. Please point out more mistakes. I'll be grateful to you. :-) — Tanzeel, Feb 23 '20 at 13:34

score -1 · Answer 2 · answered Feb 23 '20 at 09:57

-1

you should obtain connection instance in same thread as you use it move connection creation to getRecords method

answered Feb 23 '20 at 09:57

Anton Straka

139
2

How to fetch records from Oracle databse using Java

2 Answers2