CODEWITHSUNDEEP
javajdbc
Tanzeel
Tanzeel

Reputation: 4998

How to fetch records from Oracle databse using Java

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.

Upvotes: 0

Views: 469

Answers (2)

Anton Straka
Anton Straka

Reputation: 139

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

Upvotes: -1

Daniil
Daniil

Reputation: 943

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.

Upvotes: 2

Related Questions