CODEWITHSUNDEEP
javaoracle-database
Nandhini Devi
Nandhini Devi

Reputation: 99

Query returning only empty value in java

I am using java to connect with oracle.This is the code which I have used 

public List<FavouriteShop> getmyfavouriteshop(String username) {
    List<FavouriteShop> res=null;
    res = this.getJdbcTemplate().query("select * from(Select tbl_orderdetails.branch_name as myfavourite,tbl_orderdetails.branch_id as branch_id from tbl_orderdetails inner join tbl_ordermaster on tbl_orderdetails.order_master_id=tbl_ordermaster.ordermasterid where tbl_ordermaster.user_id='"+username+"' group by tbl_orderdetails.branch_name,tbl_orderdetails.branch_id order by count(tbl_orderdetails.branch_name) desc) where rownum<=3", new MyFavourite());   
    return res;
}

private class MyFavourite implements RowMapper<FavouriteShop> {
    public FavouriteShop mapRow(ResultSet rs,int i) throws SQLException {
        FavouriteShop g=new FavouriteShop();
        g.setBranch_id(rs.getString("branch_id"));
        g.setMyfavourite(rs.getString("myfavourite"));
        return g;
    }
}

I tried to execute same query in oracle I am getting output but not here and I am getting only empty result.

Upvotes: 1

Views: 105

Answers (2)

Olaf Dietsche
Olaf Dietsche

Reputation: 74078

First, you have a possible SQL injection. You can avoid this by giving username as an argument to query 

this.getJdbcTemplate().query("select * from (... where tbl_ordermaster.user_id=? ...) where rownum<=3",
                              new Object[]{ username }, new MyFavourite());

A possible reason for the empty result might be 

... where tbl_ordermaster.user_id='"+username+"' ...

Usually, user_id is an integer value, but you compare it to a String and enclose it in quotes. Passing username as an argument to query as shown above, should already take care of this.

Upvotes: 1

Sergey Alaev
Sergey Alaev

Reputation: 3972

Usually it is not the same query or not the same database :)

Extract your query text to separate variable, print it to logs. Then copy-paste from logs to sql developer.

And check database and user name.

Also, it is possible that you inserted that entries but forgot to add COMMIT.

Upvotes: 0

Related Questions