CODEWITHSUNDEEP
javaswingjdbcprepared-statement
heisenberg
heisenberg

Reputation: 1954

Prepared Statement Primary Key store seed 1 and increment

Okay, so I just started JDBC with derby client and I'm kind of new with it. I set column ID as primary key with int as it's data type. However, I'm not sure if I should include myStatement.setString(1, ?); because I thought it should Auto Increment but it looks like it's not doing it.

Here's my Grab file details:

create table "ADMIN1".STUDENTPERSONALDETAILS
(
ID INTEGER not null primary key,
STUDENTID VARCHAR(10) not null,
LASTNAME VARCHAR(50) not null,
FIRSTNAME VARCHAR(50) not null,
MIDDLENAME VARCHAR(50) not null,
PLACEOFBIRTH VARCHAR(200) not null,
DOB VARCHAR(50) not null,
GENDER VARCHAR(4) not null,
CIVILSTATUS VARCHAR(7) not null,
RELIGION VARCHAR(15) not null,
NATIONALITY VARCHAR(20) not null
)

How can I correct my PreparedStatement or My Table in such a way that adding of value for column ID will be automatic so that I can start setString(2, studentID) and avoid getting error about the number of columns not matching with what was supplied?

Here's my code:

addButton.addActionListener(new ActionListener () {
        @Override
        public void actionPerformed(ActionEvent e) {
            try {
                String myDbUrl = "jdbc:derby://localhost:1527/Enrollment"; //stores url to string
                String userName = "admin1";
                String Password = "admin1";
                    Connection myDBConnection = DriverManager.getConnection(myDbUrl, userName, Password);
                String myQuery = "INSERT INTO STUDENTPERSONALDETAILS"
                        + "(STUDENTID,LASTNAME,FIRSTNAME,MIDDLENAME,PLACEOFBIRTH,DOB,GENDER,CIVILSTATUS,RELIGION,NATIONALITY) "
                        + "VALUES(?,?,?,?,?,?,?,?,?,?) ";

                String adminissionNo ;
                String studentID = tfStudentId.getText().toString();
                String lastName = tfLastName.getText().toString();
                String firstName = tfFirstName.getText().toString();
                String middleName = tfMiddleName.getText().toString();
                String placeOfBirth = tfPob.getText().toString();
                String dateOfBirth = listDOB.getSelectedItem().toString();
                String gender = listGender.getSelectedItem().toString();
                String civilStatus = listCivilStatus.getSelectedItem().toString();
                String religion = listReligion.getSelectedItem().toString();
                String nationality = listNationality.getSelectedItem().toString();

                PreparedStatement myStatement = myDBConnection.prepareStatement(myQuery);

                    myStatement.setString(2, lastName);
                    myStatement.setString(3, firstName);
                    myStatement.setString(4, middleName);
                    myStatement.setString(5, placeOfBirth);
                    myStatement.setString(6, dateOfBirth);
                    myStatement.setString(7, gender);
                    myStatement.setString(8, civilStatus);
                    myStatement.setString(9, religion);
                    myStatement.setString(10, nationality);

                boolean insertResult = myStatement.execute();
                if(insertResult == true)
                    JOptionPane.showMessageDialog(null, "Successfully Added Information");
                else
                    JOptionPane.showMessageDialog(null, "Encountered an error while inserting data");
            }
            catch(SQLException ex) {
                JOptionPane.showMessageDialog(null, ex.toString());
            }
        }
  });

Is it necessary to include myStatement.setString(1, integervaluehere) for Primary Keys? Isn't it supposed to autoincrement?

I'd appreciate any explanation because I just started learning the basics of PreparedStatements recently.

I tried counting the columns and tried 10 and 11 lines of myStatement.setString(), but still can't get it to insert data because of mismatch.

enter image description here

Thanks in advance.

Upvotes: 1

Views: 267

Answers (4)

Shivam Sinha
Shivam Sinha

Reputation: 92

If you're using MySQL Workbench, which if you're not, I highly recommend because it just works. You have to choose Auto-Increment as a characteristic of that column. If you want your column to auto increment, when creating columns in your database, check the option Auto-Increment, sometimes written as AI.

Upvotes: -1

user207421
user207421

Reputation: 310874

If you want it to autoincrement you need to say so in the column definition, and you haven't.

I don't know what 'default 1' in your title is supposed to mean, as you haven't mentioned it in your question, but you can't have a default value and autoincrement. It doesn't make sense.

I don't know what 'store seed 1' means either, in your edit.

When you have a column with a default value you want to rely on, or autoincrement, you don't mention it at all in the INSERT statement, so there is no positional argument to set.

Upvotes: 2

David Yee
David Yee

Reputation: 3646

First, set the primary identifier column to autoincrement. Since your query already excludes the primary key, you then only have to change the PreparedStatement indexes to match the number of parameters in your query starting from one.

Since you have 10 columns in addition to the primary ID column, your PreparedStatement might look something like the following:

PreparedStatement myStatement = myDBConnection.prepareStatement(myQuery);
myStatement.setString(1, studentId);
myStatement.setString(2, lastName);
myStatement.setString(3, firstName);
myStatement.setString(4, middleName);
myStatement.setString(5, placeOfBirth);
myStatement.setString(6, dateOfBirth);
myStatement.setString(7, gender);
myStatement.setString(8, civilStatus);
myStatement.setString(9, religion);
myStatement.setString(10, nationality);

Note that you do not need to have the instruction, myStatement.setInt(1, primaryId);, once you have changed the primary key in your table to auto-increment. However, if you elect to keep the primary key as non-autoincrementing, then you must explicitly specify the primary key value and provide a parameter in your query to insert that data.

Upvotes: 1

Sarker
Sarker

Reputation: 932

You need to mention 'auto increment' explicitly. Or you can write your own java code to track the Id for each table and whenever you ask the method to give the ID it will return lastID + 1.

But, I think now you can go with auto_increment option.

Upvotes: 2

Related Questions