SELECT query with ResultSet arguments in JAVA

Question

The server code of my Oracle database contains two files (classes) namely: Main.javaand DBManager.java. DBManager.java contains SQLPRocessing and SQLProcess methods (functions) that perform executeUpdate and executeQuery operations with the query statement, respectively. The SQL queries for dumping and fetching the data are written at the Main.java class. My database contains two tables T1and T2. My code has two queries for inserting the data as:

public void SQLProcessing(String ID, String X, String Y, String XE, String YE){
    db.SQLProcess("INSERT INTO T1 VALUES("+Double.parseDouble(ID)+ "," +Double.parseDouble(X)+ "," +Double.parseDouble(Y)+")");
    db.SQLProcessing("INSERT INTO T2 VALUES("+Double.parseDouble(ID)+ "," +Double.parseDouble(XE)+ "," +Double.parseDouble(YE)+")");                    
}

Similarly, for getting the data back from the database, I use single SELECT query as:

public ResultSet SQLProcess(String msg1, String msg2, String msg3, String msg4, String msg5){
    ResultSet rs = db.SQLProcess("SELECT * FROM T1, T2 WHERE T1.ID = T2.ID");
    return rs;
}

While executing the code, I get an error:

Exception in thread "Thread-1" java.lang.NullPointerException at Main.sendTo(Main.java:132) at Main$ServerReceiver.run(Main.java:79)

The line 132 of Main.java contains while(rs.next()) and line 79 contains sendTo(socket.getInetAddress()); . However, when I see the database, data is sent and stored in the database. So, I think the problem is with the SELECT query. I tried many possible SELECT queries from different threads, I could not solve the problem. Is my guess correct? Could anybody provide the real SELECT query for my case? Or is the error is triggered from another source?

The problem is with the cursor. It is null and I added an if statement as:

if(rs!=null) {
        while(rs.next()){//code}

Then it does not generate any error. However, the client device does not get any data from the server. How can I fix it?

Regardless of which DBMS you are actually using you need to use parameterized queries before bobby tables comes to visit. Also you should start using ANSI-92 style joins, they have been available for over 25 years now. http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/08/bad-habits-to-kick-using-old-style-joins.aspx — Sean Lange, Sep 26 '17 at 13:54
I am wondering; all parameters were sent to the method as String, why did you convert them to Double? — Kh.Taheri, Sep 26 '17 at 14:05

score 1 · Accepted Answer · answered Sep 26 '17 at 13:52

1

Have you checked if (T1.ID = T2.ID) there are IDs which equal each other? Otherwise your queryresponse is empty because your where case declines a result.

Sometimes there is a extra column at first place. So maybe your data is not inserted correctly?

answered Sep 26 '17 at 13:52

dso

89
9

Yes, they are equal. – santobedi Sep 27 '17 at 02:36
is there any db function called between insert and select? Maybe your db object is not the same? – dso Sep 28 '17 at 07:45
I solved the problem. I initialized the ResultSet variable to some value until it gets the proper one such that it won't be null at any time. Thank you. – santobedi Sep 28 '17 at 09:09

score 1 · Answer 2 · answered Sep 26 '17 at 14:08

1

Using Double or Float for id purposes is an issue here, I suppose, as precision is always in issue. Simply, when you save a floating point number, like 7.2 it may be represented as 7.199999 or 7.199998 or 7.2000001 etc, thus ids won't be equal. For id I suggest using integer or something like UUID.

answered Sep 26 '17 at 14:08

Stas Shafeev

436
4
8

Do you think the primary key will be saved in the database like this: 7.199998? – Kh.Taheri Sep 26 '17 at 14:10
However, you are somehow right, why he is converting the parameters to Double? – Kh.Taheri Sep 26 '17 at 14:15
It depends on several factors, but if the primary key data type is double, it could be so, because Double.parseDouble(ID) may provide different results for the same string. I've had such a problem some time ago when comparing seemingly same coordinates. – Stas Shafeev Sep 26 '17 at 14:20
Well, can't say for the author, what benefit could be gained by using double. Though using String (varchar) as a primary key may still be a valid idea in this case. – Stas Shafeev Sep 26 '17 at 14:24
I changed the ID to integer, still getting same error. – santobedi Sep 27 '17 at 02:37
Could you update your code and provide table DDL, please? – Stas Shafeev Sep 27 '17 at 06:08

SELECT query with ResultSet arguments in JAVA

2 Answers2