How to insert values into database in java

Question

String sql = "INSERT INTO user (userid, username, password, lastname, firstname, "
            + "middlename, birthdate, gender, address, email, contact, "
            + "marital_status, religion) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";
    try {
        pst = conn.prepareStatement(sql);

        pst.setString(1, username);
        pst.setString(2, password.toString()); //chartype
        pst.setString(3, lastName);
        pst.setString(4, firstName);
        pst.setString(5, middleName);
        pst.setString(6, birthdate);
        pst.setString(7, gender1);
        pst.setString(8, address1);
        pst.setString(9, email1);
        pst.setLong(10, mobile);
        pst.setString(11, status1);
        pst.setString(12, religion1);
        pst.execute();
    }catch (Exception e) {
        JOptionPane.showMessageDialog(null, e);
    }

I have 13 columns and userid is autoincrement. But I don't know how do that you don't need to put the user id.

And I got this error. And I want Auto Increment ID. So no need to input right?

Column count doesn't match value count at row 1

you can remove that field since it is auto_increment column or just pass `NULL` value. eg, `VALUES(NULL, ?,?,.....` — John Woo, Aug 31 '13 at 11:07
Send `NULL` or remove the `userid` column. I would also suggest that you use [`INSERT INTO...SET`](http://milov.nl/2836) syntax to improve readability. — Boris the Spider, Aug 31 '13 at 11:08
You have got 13 columns name in your INSERT code but there are 12 VALUES on your sql code. Delete userid column INSERT code or add null value before all question marks on VALUES code. — RuudVanNistelrooy, Aug 31 '13 at 11:35

score 1 · Answer 1 · answered Aug 31 '13 at 11:06

1

Remove the "userid" from the list of columns if you don't intend to supply a value to it.

I.e. INSERT INTO user (username, password, ...)

answered Aug 31 '13 at 11:06

Kayaman

72,141
5
83
121

score 1 · Answer 2 · answered Aug 31 '13 at 11:14

You are specifying 13 columns in your INSERT, but only 12 placeholders in the VALUES clause. The number of columns and values must match. Either you need to remove the userid column from your INSERT, or you need to add an additional placeholder (or literal value) to set its value.

score 0 · Accepted Answer · answered Aug 31 '13 at 11:07

0

You can't insert values to AutoInc column, so just remove it from query:

insert into user(userName, password,...) values (and your values here)

answered Aug 31 '13 at 11:07

Sergio

6,900
5
31
55

so I going to make primary key the username? – JeraldPunx Aug 31 '13 at 11:09
@JeraldPunx nope, your `userid` will be auto incremented by the DB as you specified. – Boris the Spider Aug 31 '13 at 11:11
do you know how to insert char[] for my password? cause when I use `password.toString()` the password on my localhost/phpadmin is different... – JeraldPunx Aug 31 '13 at 11:23
1

actually storing password as plain text is bad idea. So you need to store just a hash (sha1 e.g) – Sergio Aug 31 '13 at 11:30
no i don't need that... this is just a school project... not for company... do you know how to insert char? – JeraldPunx Aug 31 '13 at 11:38
why is your password of char[] type? Why not string? – Sergio Aug 31 '13 at 11:44

score 0 · Answer 4 · edited May 23 '17 at 11:57

0

Is your database MySQL? If yes, you could refer to this post about inserting value to auto incremented column. How to insert data to MySQL having auto incremented primary key?

edited May 23 '17 at 11:57

Community

1
1

answered Aug 31 '13 at 11:13

kenshinji

2,021
4
25
39

How to insert values into database in java

4 Answers4