data mismatch error

[Roberto Favaro]

Im writing a simple program to read and write from a database.I keep getting an error which I dont understand [Data mismatch in criteria section]....when I try to run the getProduct...Can anyone help.Thanx in advance

//ProductDao.java
 
package cp3039proj.dao ;
 
import java.util.* ;
import java.sql.* ;
import java.io.* ;
 
import cp3039proj.business.* ;
import cp3039proj.broker.* ;
import cp3039proj.exceptions.* ;
 
public class ProductDao
{
MyBroker broker = null ;
 
public ProductDao() throws DaoException
{
broker = MyBroker.getInstance() ;
}
public Connection getConnection() throws DaoException
{
String driver = null ;
String url = null ;
String username = null ;
String password = null ;
Connection con = null ;
 
Properties props = new Properties() ;
try
{
FileInputStream in = new FileInputStream("/cp3039log/project.properties") ;
props.load(in) ;
driver = props.getProperty("jdbc.driver") ;
url = props.getProperty("jdbc.url") ;
username = props.getProperty("jdbc.username") ;
password = props.getProperty("jdbc.password") ;
 
Class.forName(driver) ;
con = DriverManager.getConnection(url, username, password) ;
}
catch(IOException ioe)
{
System.out.println("Failed to find properties file: " + ioe.getMessage() ) ;
System.exit(1) ;
}
catch(ClassNotFoundException cnfe)
{
System.out.println("Failed to find driver: " + cnfe.getMessage() ) ;
System.exit(1) ;
}
catch(SQLException sqle)
{
throw new DaoException("Failed to connect to database: " + sqle.getMessage() ) ;
}
return con ;
}
public void freeConnection(Connection con) throws DaoException
{
if (con != null)
{
try
{
con.close() ;
con = null ;
}
catch(SQLException e)
{
throw new DaoException("freeConection(): " + e.getMessage() ) ;
}
}
}
 
 
public Product getProduct(String item) throws DaoException
{
Connection con = null ;
Statement stmt = null ;
ResultSet rs = null ;
Product p = null ;
try
{
con = getConnection() ;
stmt = con.createStatement() ;
String query =
"SELECT * from Product WHERE id = '" + item + "'" ;
System.out.println(query) ;
rs = stmt.executeQuery(query) ;
 
if (rs.next() )
{
int id = rs.getInt("id") ;
String name = rs.getString("name") ;
double price = rs.getDouble("price") ;
p = new Product(id, name,price) ;
}
}
catch(SQLException e)
{
throw new DaoException("getProduct(): " + e.getMessage() ) ;
}
finally
{
try
{
if (rs != null)rs.close() ;
if (stmt != null)stmt.close() ;
if (con != null)freeConnection(con) ;
}
catch(SQLException e)
{
throw new DaoException("getProduct(): " + e.getMessage() ) ;
}
}
 
return p ;
}
 
public ArrayList getProducts() throws DaoException
{
Connection con = null ;
Statement stmt = null ;
ResultSet rs = null ;
ArrayList a = new ArrayList() ;
 
try
{
con = getConnection() ;
stmt = con.createStatement() ;
String query = "SELECT * from Product" ;
System.out.println(query) ;
rs = stmt.executeQuery(query) ;
 
while (rs.next() )
{
int id = rs.getInt("id") ;
String name = rs.getString("name") ;
double price = rs.getDouble("price") ;
Product p = new Product(id, name,price) ;
a.add(p) ;
}
}
catch(SQLException e)
{
throw new DaoException("getProducts(): " + e.getMessage() ) ;
}
finally
{
try
{
if (rs != null)rs.close() ;
if (stmt != null)stmt.close() ;
if (con != null)freeConnection(con) ;
}
catch(SQLException e)
{
throw new DaoException("getProducts():" + e.getMessage()) ;
}
}
return a ;
}
/*
public int addProduct(Product p) throws DaoException
{
Connection con = null ;
Statement stmt = null ;
int rows = 0 ;
try
{
con = getConnection() ;
stmt = con.createStatement() ;
String command = "INSERT INTO Product VALUES("
+ "'"+ p.getId() + "'"
+ ", '" + p.getName() + "'"
    + ", '" + p.getPrice() + "')" ;
 
System.out.println(command) ;
rows = stmt.executeUpdate(command) ;
}
catch(SQLException e)
{
throw new DaoException("addProduct(): " + e.getMessage() ) ;
}
finally
{
try
{
if (stmt != null)
stmt.close() ;
if (con != null)
freeConnection(con) ;
}
catch(SQLException e)
{
throw new DaoException("addProduct():" + e.getMessage() ) ;
}
}
 
return rows;
}
 
public int deleteProduct(String item) throws SQLException
{
Connection con = null;
Statement stmt = null;
int rows = 0;
try
{
con = getConnection();
stmt = con.createStatement();
String command = "DELETE FROM Product WHERE id = '" + item + "'";
System.out.println(command);
rows = stmt.executeUpdate(command);
 
if (rows > 0)
{
String command1 = "DELETE FROM Product WHERE id = '" + item + "'";
System.out.println(command1);
rows = rows + stmt.executeUpdate(command1);
}
}
catch(SQLException e)
{
throw new DaoException("deleteProduct(): " + e.getMessage() );
}
finally
{
try
{
if (stmt != null)
stmt.close() ;
if (con !=null)
freeConnection(con);
}
catch(SQLException e)
{
throw new DaoException("deleteProduct(): " + e.getMessage() );
}
}
 
System.out.println(rows);
return rows;
}
*/
public static void main(String[] args)
{
String item ;
int rows ;
Product p = null ;
MyBroker broker = null ;
ProductDao dao = null ;
try
{
broker = MyBroker.getInstance() ;
dao = new ProductDao() ;
System.out.println("getProduct(\"3\")") ;
p = dao.getProduct("3") ;
if (p != null)
{
System.out.println("ProductId: " + p.getId()
+ " name: " + p.getName()
+ " price: " + p.getPrice() ) ;
}
else
{
System.out.println("No such product") ;
}
 
System.out.println("getProducts") ;
ArrayList a = dao.getProducts() ;
for (int i = 0 ; i < a.size() ; i++)
{
p = (Product) a.get(i) ;
System.out.println("ProductId: " + p.getId()
+ " name: " + p.getName()
+ " price: " + p.getPrice() ) ;
}
 
System.out.println("addProduct") ;
 
System.out.println("deleteProduct");
//rows = dao.deleteProduct(item);
}
catch(DaoException se)
{
System.out.println(se ) ;
}
finally
{
broker.destroy() ;
}
}
}

[ November 16, 2004: Message edited by: Bear Bibeault ]

[edited subject and added code tags - jeanne]
[ November 16, 2004: Message edited by: Jeanne Boyarsky ]

[Eric Pascarello]

For future reference the topic title of Help does not help you but hurt you. Use a more detailed string of words!

Your error sounds like you are using the wrong type when you are passing parameters to your database. Using a double when it is looking for an integer or an integer when it is looking for a string.

Just an idea.

Eric

[Bob Rocks]

http://www.frontpagewebmaster.com/m-178811/tm.htm

Here is a case of a similar error

[Jeanne Boyarsky]

Roberto,
In the query, you have id has a string:
String query = "SELECT * from Product WHERE id = '" + item + "'" ;

but in the result set it is an int:
int id = rs.getInt("id") ;

One of these needs to be changed.