Query returning only empty value in java

Question

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.

BTW, using inline creation of queries is a good receipe for SQL injection — Scary Wombat
– Scary Wombat, Commented Jun 23, 2014 at 5:37
You did not use a parameterised query here, so that your application will be vulnerable to the user who can easily inject malicious query through text box/ something... Read here to know more about it.. en.wikipedia.org/wiki/SQL_injection — Rajaprabhu Aravindasamy
– Rajaprabhu Aravindasamy, Commented Jun 23, 2014 at 6:30

Olaf Dietsche · Accepted Answer · 2014-06-23 07:37:18Z

1

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.

answered Jun 23, 2014 at 7:37

Olaf Dietsche

74.4k9 gold badges113 silver badges214 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sergey Alaev · Accepted Answer · 2014-06-23 05:34:48Z

0

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.

answered Jun 23, 2014 at 5:34

Sergey Alaev

4,0112 gold badges24 silver badges36 bronze badges

2 Comments

Sergey Alaev Over a year ago

small comment - your RowMapper implementation should be private static and can be cached in private field.

vinayknl Over a year ago

if you have checked the logs, can you cross check the actual query being executed ?

Collectives™ on Stack Overflow

Query returning only empty value in java

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related