aspose file tools*
The moose likes JDBC and the fly likes Update my primary key after modifiying in the dateBase Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Update my primary key after modifiying in the dateBase" Watch "Update my primary key after modifiying in the dateBase" New topic
Author

Update my primary key after modifiying in the dateBase

nadia ghezaiel
Ranch Hand

Joined: Apr 13, 2013
Posts: 40
hello
Please, I would like to have the sql syntax that allows reinitialiser auto-increment after doing an update on a table
Exp: if jai as key values ​​ranging from 1 to 5 and after deletion of the registration or the key is that the three he faudrai enregistremnt key now has four key 3 .......

Please help me!!!
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18570
    
    8

I'm a bit confused about that. In fact I don't understand what you mean. Perhaps, could you give an example of that?
nadia ghezaiel
Ranch Hand

Joined: Apr 13, 2013
Posts: 40
Thinks,
I have a primary key "auto-incremented",and after deleting from dataBase I want that he update only .
I explaine more:
Imagine this is my database:

Index Name Country

1 Nadia Tunisia
2 Ghezaiel Tunisia
3 XXXX YYYYY
4 AAAA BBBBB

I will delete row number 2,my dataBase will be like that:

Index Name Country

1 Nadia Tunisia
3 XXXX YYYYY
4 AAAA BBBBB



Like you see the primary key(index) is noy updating!!!

I hope to find my dataBase like that after deleting:

Index Name Country

1 Nadia Tunisia
2 XXXX YYYYY
3 AAAA BBBBB

It's clear???


Please help me,I have searched a lot but unfortunaly i don't find a response :'(((((((((((((((((((
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Unfortunately, relational databases are not designed to easily support this kind of functionality. Think about it - if the table contained millions of record, a single delete of the first row would require updating all rows in the table. Furthermore, the primary key can be referenced by foreign keys in other tables, so each of these updates might require updating all of the child rows. It might easily take ages.

Good practices of database design require primary keys to be immutable, for several reasons. So, if you delete a row the way you've described, the numerical sequence of primary keys will contain gaps.

If your requirement is to assign numbers in an increasing sequence to rows, most databases provide functions to do so in a select query. It might be an expensive operation though, for if you query the "tail" of a big table, all rows in the table will have to be read and counted by the database - and this might take a lot of time.

You might need to rethink your requirements. Why do you need the records to be numbered in a sequence without gaps?
nadia ghezaiel
Ranch Hand

Joined: Apr 13, 2013
Posts: 40
Thinks,
I'm working in a small application with NetBeans an SQl server,and in one Frame i created a iTable that containt information from DataBase,and when I want to delete a row from this jTable,I'm obliged to use this function "getSelectedRow" and here we find a difference between the index of the DataBase(especially if we have deleted before) and the selected row number!!!

this is my code:

private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {

ConnPorCreationCompte conn1=new ConnPorCreationCompte();
String NomCompte=Nom.getText();
String Solde1=Solde.getText();
String Date1=Date.getText();
String Description1=Description.getText();
conn1.modifier(NomCompte,Solde1,Date1,Description1,conn1.numero()) ;
this.dispose();

// TODO add your handling code here:
}


and this is my function "modifier":

public void modifier(String NomCompte,String SoldeInitial,String Date,String Description,int lign){
PreparedStatement pst;


try {
pst = C.prepareStatement("UPDATE Compte SET NomCompte=?,SoldeInitial=?,Date=?,Description=? WHERE Indice=?");
System.out.println("***********1111**");
pst.setString(1, NomCompte);
pst.setString(2, SoldeInitial);
pst.setString(3, Date);
pst.setString(4, Description);
pst.setInt(5, numero());
pst.executeUpdate();
System.out.println("*************");
}
catch (SQLException ex) {

System.out.println("Erreur de création du compte.");
ex.printStackTrace();
JOptionPane.showMessageDialog(null,ex.getMessage());
}

}

Do you have any solution please?
nadia ghezaiel
Ranch Hand

Joined: Apr 13, 2013
Posts: 40
I forgot to make this code:

public int numero(){
ListeDesComptes L=new ListeDesComptes();
lign=L.getSelection();
return lign;
}
nadia ghezaiel
Ranch Hand

Joined: Apr 13, 2013
Posts: 40
Also this one :
public int getSelection(){
return(jTable2.getSelectedRow());
}
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

The correct way to do this would be to read the primary keys of individual rows from the database and store them in the table model. When a row is updated or deleted in the GUI, you'll translate the selected row's table index into the model index (as rows in your table might be reordered by a table sorter, see JTable.convertRowIndexToModel) and then obtain the primary key from the table model. You'll then manipulate the row in the database by using it's primary key.
nadia ghezaiel
Ranch Hand

Joined: Apr 13, 2013
Posts: 40
Excuse me but it's not too clear especially for a debutante!!!can you give me an exemple or a part of code!!!because I find difficulty to relate between selection in jTable an my DataBase!!!
And thinks for advance!!
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

My previous post was perhaps a bit dense. I'm not going to provide an example, since there are plenty of examples on the web, if you have a specific topic on your mind, and we're NotACodeMill. But I'll describe in more detail what I believe you ought to do:

1) Create a class that will represent individual rows from your database table. The class will contain fields for primary key and all other columns in the table.

2) Create a method to read data from your table. Individual rows will be stored as instances of the above class. The method will return a List (could be another collection, but List seems to be the best for this) containing all the read rows.

3) Create a method that will convert the list of individual rows into a table model. It could be based either on AbstractTableModel, or on DefaultTableModel. Personally I'd prefer AbstractTableModel, but it is probably the more difficult of the two. If you're already using DefaultTableModel, it's perfectly OK to stick with it for the time being, only populate it from the list, not from the database.

4) Create a JTable based on the table model and the rest of the user interface. You should already have done this and not need to alter this code very much (if at all), actually.

5) Implement actions in the UI that alter the database:
- you know which row in the JTable is selected
- find the corresponding row index in the table model (see the method I've pointed yo in my last post, or the JTable tutorial)
- use the model row index to find the instance representing the selected row in the list of rows you've created in step 2
- the instance you've obtained in this way contains the primary key of the row in question; manipulate the database row using this primary key.

6) Reflect the changes the user has done in the user interface. There are two options: you can either reload the entire list from the database, create new table model and set it to the existing table, or update the existing table model to reflect the action the user has performed. Reloading the model from database is probably easier (you'll reuse existing code).

This approach has the advantage of separating the database and GUI code. Each of the described steps should (hopefully) be isolated and small enough that it should give you some idea of how to go about it. Let me know whether you find it helpful.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Update my primary key after modifiying in the dateBase