CODEWITHSUNDEEP
javadatabaselinuxoracle-databasejdbc
AngelsandDemons
AngelsandDemons

Reputation: 2843

Oracle JDBC not giving expected output on Linux

Trying to use Oracle DB and Java application in order to use JDBC on Linux platform.

Downloaded ojdbc6.jar and ojdbc6dms.jar

Installed SQLDeveloper on Linux.

Established a connection in SQLDeveloper with name : Dummy

UserName : abc
Password : abc
DB Name : oracle
DB port : 8181

Table Name in DB : usertable Columns in table : username,contactnumber

Table contains 3 entries.

The java code snippet is:

package com.demo.oracleDB;


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

public class JC {

    /**
     * @param args
     */
    public static void main(String[] args) {

                System.out.println("-------- Oracle JDBC Connection Testing ------");

                try {

                    Class.forName("oracle.jdbc.driver.OracleDriver");

                } catch (ClassNotFoundException e) {

                    System.out.println("Where is your Oracle JDBC Driver?");
                    e.printStackTrace();
                    return;

                }

                System.out.println("Oracle JDBC Driver Registered!");

                Connection connection = null;

                try {

                    connection = DriverManager.getConnection(
                            "jdbc:oracle:thin:@IP:8181:oracle", "abc",
                            "abc");


                    PreparedStatement Pstmt=connection.prepareStatement("select * from abc.usertable");
                    ResultSet rst=null;

                    rst=Pstmt.executeQuery();

                    System.out.println("Before LOOP");

                    System.out.println("Row is  "  +  rst.getRow());
                    System.out.println("Count is   "   +  rst.getFetchSize());

                    while(rst.next())
                    {
                        System.out.println("Values from DB are " );
                        System.out.println("UserName  "  +  rst.getString("username"));

                        System.out.println("Contact NUmber   "  +  rst.getString("contactnumber"));
                    }

                } catch (SQLException e) {

                    System.out.println("Connection Failed! Check output console");
                    e.printStackTrace();
                    return;

                }

                if (connection != null) {
                    System.out.println("You made it.");
                } else {
                    System.out.println("Failed to make connection!");
                }
            }

        }

The output on Linux is 

-------- Oracle JDBC Connection Testing ------
Oracle JDBC Driver Registered!
Before LOOP
Row is  0
Count is   10

No clue why it is not going inside the ResultSet loop although the count shows 10. Only 3 entries are present in the table and still the count is showing as 10.

Can someone please tell if the query string is correct.

Can someone please guide me on how to get it working so that it starts printing data from the table on console.

Upvotes: 0

Views: 336

Answers (2)

user1171848
user1171848

Reputation: 296

The fetchSize field is only used as a hint for how many records to return at one time, and is used for performance tuning. If the value isn't specified using setFetchSize(), then the return value of getFetchSize() depends on your driver implementation.

Statement.getFetchSize()

The Oracle driver implementation probably has a default fetchSize of 10, even if no results are found.

Alternatively, you could call rst.last() and rst.getRow(), which would return the index number of the last row of the ResultSet. Just make sure to call rst.beforeFirst() before you enter your loop.

ResultSet.getRow()

As for why your code isn't entering the ResultSet loop, you may need to look at permissions to make sure your database user has access to that table.

Upvotes: 0

Reimeus
Reimeus

Reputation: 159784

The fetch size is different from the total number of rows returned.

For example, if your query finds 10,000 matches, it won't return all 10,000 rows from the database. This is where fetch size comes in to play. If fetchSize is 10, then it will initially retrieve the first ten rows.

Upvotes: 1

Related Questions