CODEWITHSUNDEEP
mysqljdbc
bsknblc
bsknblc

Reputation: 79

MySQL query for checking query is not working in JDBC

I have a problem with JDBC about a query. checkdata is the query and it is not working.

String checkData= "SELECT * FROM stock WHERE pdesc="+pdesc+" AND bid="+bid+" AND quantity>0";
ResultSet rs=statement.executeQuery(checkData);
if(isStockEmpty(rs)){
       String purchaseData="INSERT INTO purchases(puid, cid, bid, pdesc, pwhen) VALUES ("+(puidCount+1)+","+cid+","+bid+",'"+pdesc+"','"+pwhen+"')";
       insertQueryPurchases(statement, purchaseData);
}

It works in the MySQL though.

select *
from stock
where pdesc='FoodHorse brand olive oil 1L' and bid=1 and quantity>0;

And here is the table

CREATE TABLE stock
( 
    bid             int             NOT NULL,
    pdesc           varchar(500)    NOT NULL,
    quantity        int             NOT NULL,
    PRIMARY KEY (bid, pdesc)
);

Upvotes: 0

Views: 49

Answers (1)

Satish Varma
Satish Varma

Reputation: 266

As @Barbaros Ozhan mention, Using PreparedStatement is recommended approach to avoid parameter issues in JDBC. And to know exact issue please share your error stack.

Here reference code for PreparedStatement for your case.

String checkdata = "SELECT * FROM jpa_jbd.stock where pdesc=? and bid=? and quantity > ?";
   PreparedStatement ps = conn.prepareStatement(SELECT_SQL);
   ps.setString(1, pdesc);
   ps.setInt(2, bid);
   ps.setInt(3, quantity);
                
   ResultSet rs = ps.executeQuery();
                
   if(isStockEmpty(rs)) {
        String purchaseData = "INSERT INTO purchases(puid, cid, bid, pdesc, pwhen) VLAUES (?,?,?,?,?)";
        insertQueryPurchases(psforinserQuery, purchaseData);
    }

Upvotes: 1

Related Questions