This week's book giveaway is in the Mac OS forum. We're giving away four copies of a choice of "Take Control of Upgrading to Yosemite" or "Take Control of Automating Your Mac" and have Joe Kissell on-line! See this thread for details.
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 .......
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?
Joined: Apr 13, 2013
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!!!
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.
Joined: Apr 13, 2013
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!!
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.