Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

help with duplicate values problem

 
Robert Ingmarsson
Greenhorn
Posts: 27
Java Netbeans IDE PHP
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Best forumists!

Im a beginner to jdbc and I have encountered a huge problem for me. I have a project, a simple address book application.
In my gui i have a JTable containing data about the users first and last names. I know the basics in jdbc so i can insert data
into the database and display it in the model of the JTable using the below listed method:



The problem in my project is that i want the JTable be allowed to contain duplicate entries. Like for instance i want the user to be able to store
entries with the same and maybe only first name or other values like home address, home phone etc. How can i then delete theese duplicate
entries in my database one by one and not all entries that contain duplicate labels? I am using a very large sql table with an id as a primary
key. In an action event i want the user to be able to delete an entry in the JTable based on the getSelectedRow(); method in that class.
This is where the problems take over. How can i delete a unique duplicate value based on the getSelectedRow(); method without deleting
all entries with the same value?

Heres my deleete action event method ..., keep in mind that i am a hard struggling beginner that have gone through most of the api that
is kept opened in a chm-file while i run netbeans.

The code in the event method below will delete all duplicate values if the user select one row with same first name or last name. The
primary key wont match with the value of the getSelectedRow() method of course. Please help to see what i am doing wrong or what i am
missing out. The primary key wont match with the JTable previously mentioned method that is ob.viously.



And i can't use and sql query like the one listed below

This would of course delete all values that are stored in the fname and lname columns in the database.
I am totally stuck with this problem. I know it might be of a silly nature though but i am absolutely lost
and the ranch is the only source of help that i turn to when error occurs, but this is a major one for me.
I truly need guidance here and also pardon for bad grammar or lack of code but i hope i have made
my problem clear. What do i need to solve this problem? Any hint or code will be much appreciated.

Best regards,
Robert.










 
Paul Clapham
Sheriff
Posts: 21567
33
Eclipse IDE Firefox Browser MySQL Database
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So now you have several records with the same first and last name. And you only want to keep one of them. Does it matter which one? I ask because there might be three, for example, all having different data in them. How do you choose which of the three to keep, or do you care? Or would it be all right to end up with only one record which contains some data from each of the three?
 
Robert Ingmarsson
Greenhorn
Posts: 27
Java Netbeans IDE PHP
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:So now you have several records with the same first and last name. And you only want to keep one of them. Does it matter which one? I ask because there might be three, for example, all having different data in them. How do you choose which of the three to keep, or do you care? Or would it be all right to end up with only one record which contains some data from each of the three?


Thanks for the reply!

The problem is that i want to keep the duplicated values but be able to delete one or the other from the database with an delete record action event.
I might make things more complicated than they should be here. I have seen this in other applications. Hope it makes some sense.

FNAME | LNAME
Peter |
Peter |
Thomas | Anderson

I want the user to be able to insert unlimited records with the firstName of for example 'Peter' in the address table.

//Robert
 
Paul Clapham
Sheriff
Posts: 21567
33
Eclipse IDE Firefox Browser MySQL Database
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Okay, I understand now. Your database table has a problem: it doesn't have a primary key. And without a primary key you won't be able to write a query identifying a particular record and hence won't be able to delete a particular record.

The usual way to deal with that is to have an integer column named something like "UserID" and to declare it as auto-increment, so that the database has the responsibility to assign unique numbers and you don't.
 
Robert Ingmarsson
Greenhorn
Posts: 27
Java Netbeans IDE PHP
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:Okay, I understand now. Your database table has a problem: it doesn't have a primary key. And without a primary key you won't be able to write a query identifying a particular record and hence won't be able to delete a particular record.

The usual way to deal with that is to have an integer column named something like "UserID" and to declare it as auto-increment, so that the database has the responsibility to assign unique numbers and you don't.


Thank you again!

My sql table do contains a primary key with the value of ID. My problem is how i can use this to match it to the number of the selected row
in my JTable. I am using a DefaultTableModel with two columns, firstName and lastName. I can't say "delete from address where id = " + getSelectedRow();
Is there a way to manipulate the primary key so that it follows the rows in the jtable after each delete event. I have tried an "alter table alter column id
restart with 0" in my action event but it didnt worked. How can i correctly update an userId column in my table model class so that it matches up with the
current row count in my JTable?

//Robert
 
Paul Clapham
Sheriff
Posts: 21567
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Robert Ingmarsson wrote:How can i correctly update an userId column in my table model class so that it matches up with the
current row count in my JTable?


I don't understand that. What you need is for each row to contain the actual userId which is in the database. And the normal way to do that is to get the userID from the database immediately after you write the record and the database assigns a value to that column.

I also notice that in your original post your query only reads the name fields. If you need the userId field (and you do, if you're going to delete records) then your query should read that as well.
 
Robert Ingmarsson
Greenhorn
Posts: 27
Java Netbeans IDE PHP
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:
Robert Ingmarsson wrote:How can i correctly update an userId column in my table model class so that it matches up with the
current row count in my JTable?


I don't understand that. What you need is for each row to contain the actual userId which is in the database. And the normal way to do that is to get the userID from the database immediately after you write the record and the database assigns a value to that column.

I also notice that in your original post your query only reads the name fields. If you need the userId field (and you do, if you're going to delete records) then your query should read that as well.


Thank you so much for your answer. I don't quite get this though. I understand the basics maybe but i am very confused. Could you show some code on how to do this? Is the userID column you are talking about the same as the primary key column?
 
Paul Clapham
Sheriff
Posts: 21567
33
Eclipse IDE Firefox Browser MySQL Database
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Robert Ingmarsson wrote:Could you show some code on how to do this? Is the userID column you are talking about the same as the primary key column?


Here's how to write a query which reads the ID column as well as the name columns:



But were you really asking that? I couldn't quite tell what "this" means in "how to do this".
 
Robert Ingmarsson
Greenhorn
Posts: 27
Java Netbeans IDE PHP
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:
Robert Ingmarsson wrote:Could you show some code on how to do this? Is the userID column you are talking about the same as the primary key column?


Here's how to write a query which reads the ID column as well as the name columns:



But were you really asking that? I couldn't quite tell what "this" means in "how to do this".


Thank you so much for your time. But i am pretty lost over this entire issue. I have added an id column to my jtable in its table model as listed below



But the values in the id column does not match up with the actual amount of rows in the model. This drives me crazy.

//Robert
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:without a primary key you won't be able to write a query identifying a particular record and hence won't be able to delete a particular record.


Both of those statements are incorrect. Some databases have a method to identify individual records. Oracle has ROWID, and others have similar setups. But that doesn't matter anyway, as the query itself can provide an id, via ROW_NUMBER(), or some other analytical function, or even Oracle's ROWNUM or equivalent in other databases. Once the query provides this the individual record can be deleted via an updateable view. IIRC, SQL Server is particularly good at this, making CTEs updateable! In fact, i specifically remeber seeing a query to remove duplicates with a PK, using this method.
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Robert Ingmarsson wrote:the values in the id column does not match up with the actual amount of rows in the model.

And why should they? The id column is noting more than unique identifier, and the values should be treated as if they they were absolutely nonsensical, but unique. If you want to know the number or records, use COUNT(), or something similar. For example, you could use ROW_NUMBER():

 
Robert Ingmarsson
Greenhorn
Posts: 27
Java Netbeans IDE PHP
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Brian Tkatch wrote:
Paul Clapham wrote:without a primary key you won't be able to write a query identifying a particular record and hence won't be able to delete a particular record.


Both of those statements are incorrect. Some databases have a method to identify individual records. Oracle has ROWID, and others have similar setups. But that doesn't matter anyway, as the query itself can provide an id, via ROW_NUMBER(), or some other analytical function, or even Oracle's ROWNUM or equivalent in other databases. Once the query provides this the individual record can be deleted via an updateable view. IIRC, SQL Server is particularly good at this, making CTEs updateable! In fact, i specifically remeber seeing a query to remove duplicates with a PK, using this method.


Thanks a lot for your answer. That was pretty fun to read and a lot of intel. My problem is that i want to allow duplicates but i dont know how to distinct them apart in a query during a delete event. I have spent a lot of time on this problem of mine. I dont want to give it up,
but i am getting close to that point. I dont know how to write this query "delete value in the selected table row from the database based on its primary key " Remember i am a beginner to jdbc.
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Robert Ingmarsson wrote: I dont know how to write this query "delete value in the selected table row from the database based on its primary key " Remember i am a beginner to jdbc.

If you are "leaving" the database after getting the values, and want to "go back" and refer to one of the records, you must retrieve an identifier (as Paul explained), which in your case is the id column. While you do not need to display the id to your users, you do need to internally associate each record with that id. Once the user decides to delete a record, a standard DELETE can be issue for that record's id:
 
Robert Ingmarsson
Greenhorn
Posts: 27
Java Netbeans IDE PHP
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Brian Tkatch wrote:
Robert Ingmarsson wrote: I dont know how to write this query "delete value in the selected table row from the database based on its primary key " Remember i am a beginner to jdbc.

If you are "leaving" the database after getting the values, and want to "go back" and refer to one of the records, you must retrieve an identifier (as Paul explained), which in your case is the id column. While you do not need to display the id to your users, you do need to internally associate each record with that id. Once the user decides to delete a record, a standard DELETE can be issue for that record's id:


Thank you again! Now i am even more confused, but i take it as a good sign. Ok, so how do i associate each record in the database internally with that id?
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Robert Ingmarsson wrote:Thank you again! Now i am even more confused, but i take it as a good sign. Ok, so how do i associate each record in the database internally with that id?

I'm no expert at Java, so i best leave that to others. Though, the object that stores the data being used by the displayed table ought to be able to store the id as well.
 
Paul Clapham
Sheriff
Posts: 21567
33
Eclipse IDE Firefox Browser MySQL Database
  • Likes 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Robert Ingmarsson wrote:Ok, so how do i associate each record in the database internally with that id?


Well, first of all you do what I suggested earlier and build a JTable with three columns: ID, first name, and last name. Then look at your code which you originally posted for deleting a row in your database table. You'll notice you use the row number as the ID, but that was never the right thing to do and that was the source of much of your confusion. The row number in the JTable isn't the key for the row in the database table, although it seems like you expected that would be the case. You should modify that code to get the ID value from the first column of the JTable row you've identified.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you are interested in the best solution, you should think about creating your own custom table model (instead of using the default one). And of course you use a custom POJO as well instead of an array of Objects.

Here is some code you can build on. First the POJO which holds all required dataNow you have a custom table modelNow you can easily create this table model and fill it with the records from your database tableAnd finally when you want to delete a record, you can easily get the id (unique identifier) of that record and be 100% sure to delete only this record from the database

Hope it helps!
Kind regards,
Roel

PS. You should always close the resources inside a finally block. Because finally always run, you are guaranteed the resources are always closed (even when an exception occurs)! Or even better would be to use a try-with-resources statement. More info about this statement can be found here.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic