AY said:
Hi All!
I'm kinda new at this Java stuff! But I gotta question about
insert/updating a list of users on a database. You see I have a table:
USERS
-----
USERID (PK)
USERNAME (UNIQUE INDEXED)
PASSWORD
I made a GUI to allow a user to either add or edit a user. But the problem
is, I need to check whether or not a user exists before I insert or
update.
I have to do a SELECT query first before I do an INSERT. I also do a
SELECT
before I update a user to check if the user exists. (Why? So I don't have
to use exceptions for logic flow) Is this too much querying on a database
just do add/update a user? Any suggestions on what I should do? Is that a
bad design? Should I use the SQLException.getErrorCode() ?
Doing a SELECT so that you can determine whether a key already exists is a
very standard thing in programs that use databases. You should _not_ worry
that this is going to put some great extra load on the database. A properly
written professional database manager, like DB2 or MySQL, will be able to
support hundreds and even thousands of SQL statements per second if the
database is properly designed and the hardware supports it.
I'm not sure why you're so worried about coding exceptions or "logic flow";
they're really not that difficult to write.
Depending on what database you are using, you may be able to simplify your
life somewhat. For example, MySQL has a special version of the INSERT
statement that does an INSERT if a given key is new but does an UPDATE if
the key already exists. Here is the relevant page from the MySQL manual:
http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html. This would
let you have a single statement that can do either an INSERT or an UPDATE
and skip the SELECT entirely. But many database managers don't support this
special extension of SQL; certainly DB2 doesn't unless this support has been
added in Version 9.
With regards to error handling, SQLException.getErrorCode() will give you
the vendor's error code for a given situation. That error code will be
specific to the vendor: that means that the error code that you get for
trying to insert a duplicate record will be different between MySQL and DB2,
for example. If you are absolutely certain that you will stay with the same
database for a very long time, the vendor error code is probably a
reasonable thing to check. On the other hand, if you think you might switch
to a different database vendor somewhere down the road, you might find it
smarter to check the SQLState via SQLException.getSQLState(). The SQLState
value is a return code shared by virtually all of the vendors of database
management systems. Therefore, an Oracle database, a DB2 database, and a
MySQL database will all return the exact same value if they try to insert a
duplicate row. That means that if you eventually switch your database
manager from DB2 to Oracle or Sybase to MySQL, your error checking should
not have to change if your error checking uses the SQLState. Some of your
_other_ database code may have to change, especially if you used database
extensions that are only available on a particular vendor's system rather
than standard stuff available from all vendors, but your error handling
shouldn't have to change. At least in theory!
