Your attempt is absolutely not the correct idiom to generate numeric ids
What you are doing is a very naive attempt and will not work with more than one client or more than one thread accessing the database.
Primary keys should be immutable, as in you can't change them once they are created, otherwise data corruption becomes rampant as foreign keys become orphans for example.
Correct Idiom
- Use an auto-increment column for your primary key.
- Learn SQL properly and learn to use more efficient SQL statement:
SELECT MAX(msg_id) as maxid FROM MSG as in the example below, but an auto-increment key is the correct way.
- There is no reason to use a
PreparedStatement if there are no parameters you need to pass in.
- Don't use 10 year old books, tutorials or examples from the web that do things like your example.
That said, you are very close, here is what your code should look like to fix your code that is posted, but keep in mind this is the wrong to do what you are trying to accomplish:
import java.sql.*;
public class Q26227752
{
public static void main(final String[] args)
{
try
{
final Connection cn = DriverManager.getConnection("jdbc:mysql://localhost/finalclient", "root", "");
try
{
final Statement ps = cn.createStatement();
try
{
final ResultSet rs = ps.executeQuery("SELECT MAX(msg_id) as maxid FROM msgs");
try
{
// always check to see if something was actually returned
if (rs.next())
{
final int maxid = rs.getInt("maxid");
System.out.format("maxid = %d\n", maxid);
System.out.format("nextid = %d\n", maxid + 1);
}
else
{
throw new SQLException("No rows returned for \"SELECT MAX(msg_id) FROM msgs\"");
}
}
catch (final SQLException e)
{
throw new RuntimeException(e);
}
finally
{
try { rs.close(); } catch (final SQLException e) { System.err.print(e.getMessage()); }
}
}
catch (final SQLException e)
{
throw new RuntimeException(e);
}
finally
{
try { ps.close(); } catch (final SQLException e) { System.err.print(e.getMessage()); }
}
}
catch (final SQLException e)
{
throw new RuntimeException(e);
}
finally
{
try { cn.close(); } catch (final SQLException e) { System.err.print(e.getMessage()); }
}
}
catch (final SQLException e)
{
throw new RuntimeException(e);
}
}
}
Notes:
As per this answer.
The DriverManager methods getConnection and getDrivers have been
enhanced to support the Java Standard Edition Service Provider
mechanism. JDBC 4.0 Drivers must include the file
META-INF/services/java.sql.Driver. This file contains the name of the
JDBC drivers implementation of java.sql.Driver. For example, to load
the my.sql.Driver class, the META-INF/services/java.sql.Driver file
would contain the entry:
my.sql.Driver Applications no longer need to explicitly load JDBC
drivers using Class.forName(). Existing programs which currently load
JDBC drivers using Class.forName() will continue to work without
modification.
Always close resources correctly:
You MUST always get in a habit of the hierarchy of try/catch/finally blocks when dealing with external resources like database connections, statements and what not. Java 7+ has enhanced syntactic sugary ways of dealing with them, but the principal is the same.
Use a proper primary key generator supported by your RDBMS
CREATE TABLE msgs
(
msg_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
);
SELECT * is never correct:
SELECT * is a very lazy and dangerous way to write SQL SELECT statements.
Especially if you use column numbers instead of names, if the table changes order of the columns and the types are the same you will silently corrupt your data.
Only selecting the columns you need is explicit, and explicit is always better than implicit!
It is also inefficient if there are lots of columns that are large and you don't need.
As always complete examples are available at my GitHub repository of answers and example code.
