How to insert auto increment value in mysql using servlet-jsp?

  • (2 Pages)
  • +
  • 1
  • 2

19 Replies - 30982 Views - Last Post: 15 May 2014 - 06:42 AM

#1 shreyas135   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 30-April 14

How to insert auto increment value in mysql using servlet-jsp?

Posted 01 May 2014 - 12:01 AM

If I try to insert this query, it will work fine.
sql = "insert into users (id, name,email,password) values ('1','Anonymous','[email protected]','*****')";

but "Id" is auto increment primary key so no need to define in value so, query will be look like:
sql = "insert into users (name,email,password) values ('Anonymous','[email protected]','*****')";

But it will not work.

Please help me how to perform this query.

Please follow my existing code for:

Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(DB_URL, USER, PASSWORD);

st = con.createStatement();
sql = "insert into users (id, name,email,password) values (?,?,?,?)";
ps = con.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);

ps.setString(1, null);
ps.setString(2, name);
ps.setString(3, email);
ps.setString(4, password);
ps.executeUpdate();


Is This A Good Question/Topic? 0
  • +

Replies To: How to insert auto increment value in mysql using servlet-jsp?

#2 astonecipher   User is offline

  • Enterprise Software Architect
  • member icon

Reputation: 3097
  • View blog
  • Posts: 11,841
  • Joined: 03-December 12

Re: How to insert auto increment value in mysql using servlet-jsp?

Posted 01 May 2014 - 01:04 AM

Stupid question but, is id set as auto increment?
Was This Post Helpful? 0
  • +
  • -

#3 shreyas135   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 30-April 14

Re: How to insert auto increment value in mysql using servlet-jsp?

Posted 01 May 2014 - 02:29 AM

View Postastonecipher, on 01 May 2014 - 01:04 AM, said:

Stupid question but, is id set as auto increment?

Yes "Id" is auto increment
Was This Post Helpful? 0
  • +
  • -

#4 astonecipher   User is offline

  • Enterprise Software Architect
  • member icon

Reputation: 3097
  • View blog
  • Posts: 11,841
  • Joined: 03-December 12

Re: How to insert auto increment value in mysql using servlet-jsp?

Posted 01 May 2014 - 04:19 AM

When it fails to insert does it give an error message? What is the error message?
Was This Post Helpful? 0
  • +
  • -

#5 shreyas135   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 30-April 14

Re: How to insert auto increment value in mysql using servlet-jsp?

Posted 01 May 2014 - 04:24 AM

try {
                // Register JDBC driver
                Class.forName("com.mysql.jdbc.Driver");

                // Open a connection
                out.println("Connecting to a selected database...");
                con = DriverManager.getConnection(DB_URL, USER, PASSWORD);
                out.println("Connected database successfully...");

                // Execute a query
                st = con.createStatement();
                sql = "insert into users (name,email,password) values ('" + name + "','" + email + "','" + password + "')";
                out.println(sql);
                int i = st.executeUpdate(sql);
                if (i > 0) {
                    out.println("Inserted!!");
                }
            } catch (Exception ex) {
                out.println(ex.getMessage());
            }



Connecting to a selected database...
Connected database successfully...
insert into users (name,email,password) values ('679i89','8i787','78i7')
Duplicate entry '1' for key 'PRIMARY'
Was This Post Helpful? 0
  • +
  • -

#6 astonecipher   User is offline

  • Enterprise Software Architect
  • member icon

Reputation: 3097
  • View blog
  • Posts: 11,841
  • Joined: 03-December 12

Re: How to insert auto increment value in mysql using servlet-jsp?

Posted 01 May 2014 - 04:27 AM

@shreyas135, is that for testing or using? Prepared statements are basically required in an actual application. Your code is prone to sql injection.
Was This Post Helpful? 0
  • +
  • -

#7 shreyas135   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 30-April 14

Re: How to insert auto increment value in mysql using servlet-jsp?

Posted 01 May 2014 - 04:28 AM

I am learning JSP + Servlet
Was This Post Helpful? 0
  • +
  • -

#8 astonecipher   User is offline

  • Enterprise Software Architect
  • member icon

Reputation: 3097
  • View blog
  • Posts: 11,841
  • Joined: 03-December 12

Re: How to insert auto increment value in mysql using servlet-jsp?

Posted 01 May 2014 - 04:30 AM

If this is for a live application you are opening your self to attacks. Prepared statements are the correct way to go.

Do you have a default for the auto increment column?
Was This Post Helpful? 0
  • +
  • -

#9 x68zeppelin80x   User is offline

  • D.I.C Addict

Reputation: 130
  • View blog
  • Posts: 576
  • Joined: 07-March 09

Re: How to insert auto increment value in mysql using servlet-jsp?

Posted 01 May 2014 - 04:35 AM

Change ps.setString(1, null); to ps.setInt(1, null); or just leave it out entirely:

sql = "insert into users (name, email, password) values (?,?,?)";
ps = con.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
ps.setString(1, name);
ps.setString(2, email);
ps.setString(3, password);


Please take a look at: MKYong.com: JDBC PreparedStatement Example – Insert A Record

This post has been edited by x68zeppelin80x: 01 May 2014 - 05:32 AM

Was This Post Helpful? 0
  • +
  • -

#10 g00se   User is offline

  • D.I.C Lover
  • member icon

Reputation: 3662
  • View blog
  • Posts: 16,819
  • Joined: 20-September 08

Re: How to insert auto increment value in mysql using servlet-jsp?

Posted 01 May 2014 - 04:36 AM

Quote

If I try to insert this query, it will work fine.
sql = "insert into users (id, name,email,password) values ('1','Anonymous','[email protected]','*****')";

The very fact you have done that might be interfering with the sequence generator. Delete that test row and try again
Was This Post Helpful? 0
  • +
  • -

#11 shreyas135   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 30-April 14

Re: How to insert auto increment value in mysql using servlet-jsp?

Posted 01 May 2014 - 04:47 AM

If i set
ps.setInt(1, null);

It throw error like Uncompilable source code - Erroneous sym type: java.sql.PreparedStatement.setInt

Here you can see my code have written in two different format.

CODE-1

Class.forName("com.mysql.jdbc.Driver");

                // Open a connection
                out.println("Connecting to a selected database...");
                con = DriverManager.getConnection(DB_URL, USER, PASSWORD);
                out.println("Connected database successfully...");

                // Execute a query
                st = con.createStatement();
                sql = "insert into users (name,email,password) values ('" + name + "','" + email + "','" + password + "')";
                out.println(sql);
                int i = st.executeUpdate(sql);
                if (i > 0) {
                    out.println("Inserted!!");
                }



CODE-2
 Class.forName(JDBC_DRIVER);
            con = DriverManager.getConnection(DB_URL, USER, PASSWORD);
            PreparedStatement ps;
            
            st = con.createStatement();
            sql = "insert into users (id, name,email,password) values (?,?,?,?)";
            ps = con.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
            ps.setInt(1, null);
            ps.setString(2, name);
            ps.setString(3, email);
            ps.setString(4, password);
            ps.executeUpdate();


Give me solution any of above two format. Both are not working for me.
Was This Post Helpful? 0
  • +
  • -

#12 g00se   User is offline

  • D.I.C Lover
  • member icon

Reputation: 3662
  • View blog
  • Posts: 16,819
  • Joined: 20-September 08

Re: How to insert auto increment value in mysql using servlet-jsp?

Posted 01 May 2014 - 04:50 AM

Did you actually do what i suggested?
Was This Post Helpful? 0
  • +
  • -

#13 astonecipher   User is offline

  • Enterprise Software Architect
  • member icon

Reputation: 3097
  • View blog
  • Posts: 11,841
  • Joined: 03-December 12

Re: How to insert auto increment value in mysql using servlet-jsp?

Posted 01 May 2014 - 04:52 AM

Post you show create table ; output
Was This Post Helpful? 0
  • +
  • -

#14 shreyas135   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 30-April 14

Re: How to insert auto increment value in mysql using servlet-jsp?

Posted 01 May 2014 - 04:55 AM

View Postg00se, on 01 May 2014 - 04:50 AM, said:

Did you actually do what i suggested?


Yes I did.

mysql> desc users;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 1 | |
| name | varchar(40) | YES | | NULL | |
| email | varchar(255) | YES | UNI | NULL | |
| password | varchar(40) | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
4 rows in set (0.04 sec)
Was This Post Helpful? 0
  • +
  • -

#15 astonecipher   User is offline

  • Enterprise Software Architect
  • member icon

Reputation: 3097
  • View blog
  • Posts: 11,841
  • Joined: 03-December 12

Re: How to insert auto increment value in mysql using servlet-jsp?

Posted 01 May 2014 - 04:56 AM

I'd is not auto increment but is not null, so it needs a value. You need to modify the table to aut increment that column.
Was This Post Helpful? 1
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2