Calling method from another class and SQL select max id issue

[teser3]

I have a method that has a prepared statement that executes before a
SQL select max id query. The Prepared statement works great where I
call it in another method in my Java Helper Class file.

Now I want to move the method with the prepared statement into another
class and call in in my Java Helper Class.

For some reason it didnt work. Here is what it looked like with both
methods in one class and everything working great with my Access 2003
database where the prepared statement runs and then I get the max id
from the table:

public class HelperDB {
....
PreparedStatement ps = null;
public int insertData(MyBean poc)
{
int status = 0;
try {
ps = connection.prepareStatement("Insert into tableone
(firstname,lastname) values (?,?)");
ps.setString(1,poc.getFirstname());
ps.setString(1,poc.getLastname());
ps.executeUpdate();
}
catch(Exception e)
{
e.printStackTrace();
}
return status;
}

public thehit(MyBean user)
{
Statement statement = null;
ResultSet rs = null;
....
insertData(user);
rs = statement.executeQuery("select max(tableoneId) from tableone");
rs.next();
int myId = rs.getInt(1);
String query = "insert into tabletwo (city, tableoneid) values ('" +
user.getCity()) + "','" + myId + "')";
statement.executeUpdate(query);
....



Now if I put the insertData method in another class and call it
in the HelperDB class it will correctly insert data into tableone but
it doesnt retrieve the correct max tableoneid value.


public class AnotherHelper {
....
PreparedStatement ps = null;
public int insertData(MyBean poc)
{
int status = 0;
try {
ps = connection.prepareStatement("Insert into tableone
(firstname,lastname) values (?,?)");
ps.setString(1,poc.getFirstname());
ps.setString(1,poc.getLastname());
ps.executeUpdate();
}
catch(Exception e)
{
e.printStackTrace();
}
return status;
}




//HelperDB class:
public class HelperDB {
....
PreparedStatement ps = null;
public thehit(MyBean user)
{
Statement statement = null;
ResultSet rs = null;
....
new AnotherHelper().insertData(user);
rs = statement.executeQuery("select max(tableoneId) from tableone");
rs.next();
int myId = rs.getInt(1);
String query = "insert into tabletwo (city, tableoneid) values ('" +
user.getCity()) + "','" + myId + "')";
statement.executeUpdate(query);
....


It seems like the the object (new AnotherHelper().insertData(user))
runs after the select max sql statement (select max(tableoneId) from
tableone) because I always get the second to last max value in
tableone. Please advise.

 

[Lew]

I have a method that has a prepared statement that executes before a
SQL select max id query. The Prepared statement works great where I
call it in another method in my Java Helper Class file.

Now I want to move the method with the prepared statement into another
class and call in in my Java Helper Class.

For some reason it didnt work. Here is what it looked like with both
methods in one class and everything working great with my Access 2003
database where the prepared statement runs and then I get the max id
from the table:

public class HelperDB {
....
PreparedStatement ps = null;

Superfluous initialization.

public int insertData(MyBean poc)
{
int status = 0;
try {
ps = connection.prepareStatement("Insert into tableone
(firstname,lastname) values (?,?)");
ps.setString(1,poc.getFirstname());
ps.setString(1,poc.getLastname());
ps.executeUpdate();
}
catch(Exception e)
{
e.printStackTrace();
}
return status;

A value that is never updated, nor checked.

}

public thehit(MyBean user)

How about you post valid Java? Preferably as an SSCCE.

{
Statement statement = null;
ResultSet rs = null;
....
insertData(user);
Indentation?

rs = statement.executeQuery("select max(tableoneId) from tableone");
rs.next();
int myId = rs.getInt(1);
String query = "insert into tabletwo (city, tableoneid) values ('" +
user.getCity()) + "','" + myId + "')";

If "myId" maps to an INTEGER or NUMERIC column you shouldn't enclose the value
in single quotes in your SQL.

statement.executeUpdate(query);
....

Now if I put the insertData method in another class and call it
in the HelperDB class it will correctly insert data into tableone but
it doesnt retrieve the correct max tableoneid value.


public class AnotherHelper {
....
PreparedStatement ps = null;
public int insertData(MyBean poc)
{
int status = 0;
try {
ps = connection.prepareStatement("Insert into tableone
(firstname,lastname) values (?,?)");
ps.setString(1,poc.getFirstname());
ps.setString(1,poc.getLastname());
ps.executeUpdate();
}
catch(Exception e)
{
e.printStackTrace();
}
return status;
}

public class HelperDB {
....
PreparedStatement ps = null;
public thehit(MyBean user)

This line is not valid Java.

{
Statement statement = null;

You set it to null, why? And why did you never set it to a non-null value?

ResultSet rs = null;
....

What is the max tableone.tableoneid at this exact point, just before the
insertData() call?

new AnotherHelper().insertData(user);
rs = statement.executeQuery("select max(tableoneId) from tableone");

How is statement not null?

rs.next();

It's never really a good idea to ignore the return value, no matter how
confident you are that you can get away with it.

int myId = rs.getInt(1);
String query = "insert into tabletwo (city, tableoneid) values ('" +
user.getCity()) + "','" + myId + "')";

What is the exact value of query at this exact point?
While paused at this breakpoint, what does your RDBMS's query client show for
the max "tableoneId" in "tableone"?

statement.executeUpdate(query);
....


It seems like the the object (new AnotherHelper().insertData(user))
runs after the select max sql statement (select max(tableoneId) from
tableone) because I always get the second to last max value in
tableone. Please advise.

Are you sure? The evidence is not clear from your description.

Please post an SSCCE.

 

[Lew]

Please post an SSCCE.

Since your queries apparently rely on a self-incrementing primary key column
in "tableone", it would help to know what Access 2003's semantics are for such
columns. I'm not familiar with Access 2003's self-incrementing column semantics.

Some RDBMSes will recycle values for such columns from deleted rows, under
certain specific circumstances, others won't. Others will do so if you coerce
them to.

Your answers to my questions upthread, about the value of the max
tableone.tableoneid *in the database* just before and just after the
insertData() call, and about the value in the Java code of the max-value SQL
query string just before it executes, will help a lot to understand what's
happening.