how to identify whether or not particular ID exists or not in database?

[Gobindap]

I am using Jtable to display and edit data. ID, name and marks columns are required to display here. I want both to edit existing value and insert new value in it. If certain ID exists, I need to update and if not I need to insert new record.

For this how to detect whether or not certain ID already exists there in database and some not? What type of code should I user here?

Please help me to overcome my problem.

[r035198x]

Different databases have their SQL specific ways of handling this e.g MySQL has an

ON DUPLICATE KEY UPDATE

clause which is not standard SQL . So you can either

1.) Use a database specific clause.
2.) Try an update first then if the update statement returns zero records then you run an insert. If you use this approach you should find ways of synchronizing the logic so that you don't have two threads trying to insert or update the same record.
3.) You can use JPA instead of direct JDBC. This would require a bit of reading. JPA shields you from working with SQL directly and it generates the right SQL for the right database.

[Gobindap]

Thank you thank you thank you sir very much.

Would you mind sir, can you give me links for documents to study "ON DUPLICATE KEY UPDATE" and JPA.
I don't know how to use ON DUPLICATE KEY UPDATE and what is JPA.

[r035198x]

The two are not related. As I said above. ON DUPLICATE KEY UPDATE is a MySQL only clause so you only use it if you are using MySQL server database. If you use it then you use it with plain JDBC not with JPA.

JPA is an approach to data access that uses java objects to model database tables. If you are new to Java then learn JDBC first and only learn JPA when you have had lots of experience it.

I would suggest you use approach (2) from the suggestions I gave. The one where you run an update first and check the return value of execute update. If executeUpdate returns zero then no records where updated so you can run the insert. Also, if you are using the database to generate Ids for your records then you know if a record is new or not by checking if it has an Id or not.

[Gobindap]

Thank you very much. I tried your second suggestion and that solved my problem completely.

Thank you very much

[r035198x]

Well done! Don't forget the warning about two people doing the check at the same time. They will both try an update and get zero back at almost the same time then they will both try an insert which could duplicate the data or result in one of them failing. You can solve that by synchronizing the code that does the update and select so that only one thread will run it at a time.