Java and SQL question

watsgone

Any ideas, I want to check to see if a record is in a database using the primary key.
Code like this.
public boolean read(int operatorID) {
int number;
number=operatorID;
boolean answer;
answer=false;
Operator operator = new Operator();

try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection conn = DriverManager.getConnection("jdbc:odbc:TextSystem");
Statement statement = conn.createStatement();
String sql = "SELECT operatorID FROM operator WHERE(";
sql += "'";
sql += operator.getOperatorID()==number;
sql += "');";
statement.executeQuery(sql);

so on.

any ideas

lynchie

Couple of points..

Operator operator = new Operator();

Your creating this object, yet you further down you call getOperatorId() on it, yet you never call setOperatorId in the first place?

String sql = "SELECT operatorID FROM operator WHERE(";
sql += "'";
sql += operator.getOperatorID()==number;
sql += "');";

The above statement is wrong in two ways..

1. it should be of the form "Select <key> from operator where <key> = <xx>
2. Your statement that is being constructed above generates "SELECT operatorID FROM operator WHERE('false');"

Add a System.out.println() before executing the sql and you'll see whats being sent to the DB.

Seth Brundle

given that operatorID is an int then it does not require the single quotes with your SQL string. Also there is no column referenced in the SQL string

String sql = "SELECT operatorID FROM operator WHERE operatorID=";
sql += operator.getOperatorID()==number; // or whatever!!!
sql += ");";

smcelhinney

kbannon wrote:

given that operatorID is an int then it does not require the single quotes with your SQL string. Also there is no column referenced in the SQL string

String sql = "SELECT operatorID FROM operator WHERE operatorID=";
sql += operator.getOperatorID()==number; // or whatever!!!
sql += ");";

Always always always always always always use prepared or callable statements. It removes the dependency on YOU to format your inputs according to datatypes. It also allows you to port code very easily, and not have to refactor it all cos your datatype has suddenly changed from String to int. Its very simple

PreparedStatement ps = connObj.prepareStatement("SELECT * FROM operator WHERE operatorID = ?");
ps.setInt(1, operator.getOperatorId());

and to answer your question, to see if a record exists..

boolean recordExists = false;
RecordSet rs = ps.executeQuery(); // from code above

if(rs.first()){
    recordExists = true;
}

HTH,
Stephen