Incorrect data getting fetched from select query

Question

Can someone review my code

This query is fetching two values address_id and postcode from table1. Here AddressID class has two variable postcode(string) and address_id(integer) :

@Select("SELECT address_id,postcode FROM table1 WHERE custom_field_1 = #{caseid}")
public List<AddressID> getAddressIdPostCodeList(String caseid);

Here is how AddressID looks AddressID.java

  private int addressId;

  private String postcode;

    //getters and setters of Pstcode and addressId

    @Override
        public String toString() {
            return "PostCode : " + this.postcode;

        }

while executing this query I get value of address_id as 0 and required postcode. Although DB has values of address_id which is not zero. Where my code is failing?

This is where in my main method I am calling

 List<AddressID> addresses = new ArrayList<>();
 addresses = mainClassObject.getAddressIdPostCodeList(address.getcaseId());

Ideally addresses object should have both address_id and postcode. I am getting both values but address_id I am getting 0 and correct values for postcode.

So where is the code that actually runs that query and populates your Java instances? — , Jan 22 '18 at 14:40
@a_horse_with_no_name I am calling that method in my implementation class like this List addresses = new ArrayList<>(); addresses = mainClassObject.getAddressIdPostCodeList(address.getcaseId()); and as you can see in my above piece of code posted in my question that this method returns me result from query getAddressIdPostCodeList() — user8181539, Jan 22 '18 at 14:47

Omega Kewt · Answer 1 · 2018-01-22T15:47:22.997

Since I cant comment because I dont have 50 reputation, I had to write it here, its hard to tell from the code you posted, to give you an answer I need more detail on what getAddressIdPostCodeList() does, since you said the data in the DB has no ceros the error must be in the method getAddressIdPostCodeList() and/or in how you are handling the resultset of the Query

EDIT: Solution using Oracle JDBC Driver

Since I dont know how to use Mybatis, heres a solution using JDBC.

to connect using JDBC to your Oracle DB here's a simple tutorial:

1. First you need to download de JDBC driver from Oracle depending your DB version (11g,12c,10g), the driver Its called ojdbcX.jar where X is a number of the version of the driver

2. After you have downloaded the driver you need to add the .jar to your project, if you are using Netbeans IDE you can add it like this:

if you are using Eclipse you can use the following Link to see how to add the .JAR file: How to import a jar in Eclipse

3. After adding the .JAR its pretty simple, you just need to connect to the DB using your credentials, here is an example on how to do it:

Connection connection = null;
            try {
                    Class.forName("oracle.jdbc.driver.OracleDriver");
                    connection = DriverManager.getConnection(
                "jdbc:oracle:thin:@localhost:1521:xe", "system", "password");                    } catch (Exception ex) {
                    ex.printStackTrace();
                }

for more deatiled information on how to connect, you can check the oracle.jdbc Class OracleDriver Documentation

4. After the connection has been made its pretty simple, Heres a short code example to get the result you want, you need to modify it with your connection details and as you see fit because im making a couple of assumptions, this code is just an example:

Main.Java

public class Main {

    public static void main(String[] argv) {

           List<AddressID> addresses;
           SQLConnect conex= new SQLConnect();
           String caseid="the id you want";
           addresses=conex.getAddressIdPostCodeList(caseid);
}

AddressID.java

public class AddressID {

private String addressId;
private String postcode;     
}

SQLConnect.Java

public class SQLConnect {


   public static Connection connection;

            public SQLConnect (){
                createConnection();    
              }


            public void CreateConnection(){
             try {
                    Class.forName("oracle.jdbc.driver.OracleDriver");
                    connection = DriverManager.getConnection(
                "jdbc:oracle:thin:@localhost:1521:xe", "system", "password")
                    } catch (Exception ex) {
                    ex.printStackTrace();
                } 
            }


            public void closeConnection(){
                if(connection!=null){
                    try {
                        connection.close();   
                    } catch (SQLException ex) {
                         ex.printStackTrace();
                    }
                }


            public ResultSet ExecuteQuery(String queryTXT) throws SQLException{

             Statement query = connection.createStatement();
             ResultSet table=query.executeQuery(queryTXT);
             return table;                   
            }


            public List<AddressID> getAddressIdPostCodeList(String caseid) throws SQLException{
             List<AddressID> addresses = new ArrayList <> ();
             ResultSet table=ExecuteQuery("SELECT address_id,postcode FROM table1 WHERE custom_field_1 ='"+caseid+"';");
              while (table.next()) {
                 AddressID aux;
                 aux.addressId=table.getString(1); 
                 aux.postcode=table.getString(2);                           
                 addresses.add(aux); 
              }       
             return addresses;                 
            }
 }

This is my repository class.I guess thats how we use for writing queries.This method is only executing select query... @Select("SELECT address_id,postcode FROM table name WHERE custom_field_1 = #{caseId}") public List getAddressIdPostCodeList(String caseId); — user8181539, Jan 22 '18 at 14:07
What database are you using ? (Oracle,SQL-Server,etc...) and What are you using to connect to the database ? jdbc ? — Omega Kewt, Jan 22 '18 at 14:11
I've never used mybatis, If you want i can give you a solution using `JDBC` for your problem, but if you need to use necessarily mybatis I can't help you, sorry — Omega Kewt, Jan 22 '18 at 14:23
My application use mybatis.still I want to know what is the solution using jdbc...I think the issue in my code could be AddressID,java class.Can you check if I am missing anything there.I converted toString the Postcode as it is string.As address id is integer.Unable to understand it what is wrong with code — user8181539, Jan 22 '18 at 14:27
Im gonna write you a solution using `JDBC`, regarding your `AdressID` class, have you tried changing `private int addressId;` to `private String addressId;` and handling it just like you handle `postcode` ? — Omega Kewt, Jan 22 '18 at 14:34
Kewl Yes I did.I was getting null instead of 0..:(..But checked in DB.It has got values..It has values of datatype Number — user8181539, Jan 22 '18 at 14:37
I have provided a solution using `JDBC` editing the same answer above, hope it helps — Omega Kewt, Jan 22 '18 at 15:50

score 0 · Answer 2 · answered Jan 23 '18 at 11:34

I did forgot to wtite my result query which is actually binding data fetched 
from select query to the AddressId class..

@Results(id = "result", 
value = {
    @Result(property = "addressId", column = "address_id"),
    @Result(property = "postcode", column = "postcode")
        }
        )

Incorrect data getting fetched from select query

2 Answers2