Programming Help

Web Development



What's Here?

  • Members: 150,105
  • Replies: 507,668
  • Topics: 80,035
  • Snippets: 2,668
  • Tutorials: 706
  • Total Online: 1,868
  • Members: 76
  • Guests: 1,792

Welcome to Dream.In.Code
Become a Java Expert!

Join 150,105 Java Programmers for FREE! Get instant access to thousands of Java experts, tutorials, code snippets, and more! There are 1,868 people online right now. Registration is fast and FREE... Join Now!




Set auto_increment field using prepareStatement

 
 Reply to this topicStart new topic

Set auto_increment field using prepareStatement
shadowBean
18 Jun, 2008 - 12:54 AM
Post #1

New D.I.C Head
*

Joined: 13 Jun, 2008
Posts: 27


My Contributions
How do we set value to auto_increment field in MySQL using prepareStatement

CODE


stmt = con.prepareStatement("INSERT INTO member VALUES(?,?,?,?)";

stmt.setDate(1, new java.sql.Date(System.currentTimeMillis()));
stmt.setInt(2,xxx);
stmt.setString(3,name);
stmtsetString(4,lName);
.
.
int insertId = stmt.executeUpdate();


What do we put in xxx if field 2 is auto_increment?
And
How do we get it as return value?
How do we use RETURN_GENERATED_KEYS ?
User is offlineProfile CardPM
+Quote Post

1lacca
RE: Set Auto_increment Field Using PrepareStatement
18 Jun, 2008 - 01:07 AM
Post #2

code.rascal
Group Icon

Joined: 11 Aug, 2005
Posts: 3,822



Thanked: 12 times
My Contributions
You don't have to specify what to put into an autoincrement field, because it is incremented automatically.
Getting the generated values read this and this.
User is offlineProfile CardPM
+Quote Post

shadowBean
RE: Set Auto_increment Field Using PrepareStatement
18 Jun, 2008 - 04:57 PM
Post #3

New D.I.C Head
*

Joined: 13 Jun, 2008
Posts: 27


My Contributions
thanks 1lacca

I tried

CODE


stmt.setDate(1, new java.sql.date(System.currentTimeMillis()));
stmt.setString(3, company);



I also tried (column 2 is auto_increment)

CODE


stmt.setDate(1, new java.sql.date(System.currentTimeMillis()));
stmt.setString(2 , "");
stmt.setString(3 , company);



and

CODE


stmt.setInt(2,"");



I got an error Incorrect interger value : for column 'id' at row 1;
setInt() cannot be applied to String

How do I set this right?

This post has been edited by shadowBean: 18 Jun, 2008 - 09:38 PM
User is offlineProfile CardPM
+Quote Post

shadowBean
RE: Set Auto_increment Field Using PrepareStatement
18 Jun, 2008 - 11:05 PM
Post #4

New D.I.C Head
*

Joined: 13 Jun, 2008
Posts: 27


My Contributions
I got it

It's

CODE


stmt.setString(2, null);



Thanks
User is offlineProfile CardPM
+Quote Post

1lacca
RE: Set Auto_increment Field Using PrepareStatement
18 Jun, 2008 - 11:08 PM
Post #5

code.rascal
Group Icon

Joined: 11 Aug, 2005
Posts: 3,822



Thanked: 12 times
My Contributions
Could you post the schema of the table MEMBER?
User is offlineProfile CardPM
+Quote Post

shadowBean
RE: Set Auto_increment Field Using PrepareStatement
18 Jun, 2008 - 11:36 PM
Post #6

New D.I.C Head
*

Joined: 13 Jun, 2008
Posts: 27


My Contributions
CODE

CREATE TABLE `member` (
  `date` date NOT NULL default '0000-00-00',
  `memberid` smallint(8) unsigned NOT NULL auto_increment,
  `company` varchar(25) NOT NULL default '',

.....cut off many fields........
  
  PRIMARY KEY  (`memberid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=134;
User is offlineProfile CardPM
+Quote Post

pbl
RE: Set Auto_increment Field Using PrepareStatement
19 Jun, 2008 - 03:54 AM
Post #7

D.I.C Lover
Group Icon

Joined: 6 Mar, 2008
Posts: 3,587



Thanked: 233 times
Dream Kudos: 75
My Contributions
QUOTE(shadowBean @ 19 Jun, 2008 - 12:36 AM) *

CODE

CREATE TABLE `member` (
  `date` date NOT NULL default '0000-00-00',
  `memberid` smallint(8) unsigned NOT NULL auto_increment,
  `company` varchar(25) NOT NULL default '',

.....cut off many fields........
  
  PRIMARY KEY  (`memberid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=134;




stmt.setString(2, null);

Might work but don't think this is write. You have a smallInt there not a String.

Just remove the auto increment field from the preparedStatement.

smallInt for an autoincrement field ! I hope you are not planning to have a lot of rows or a lot of insert/delete in that table.
User is offlineProfile CardPM
+Quote Post

shadowBean
RE: Set Auto_increment Field Using PrepareStatement
22 Jun, 2008 - 02:11 AM
Post #8

New D.I.C Head
*

Joined: 13 Jun, 2008
Posts: 27


My Contributions
I am only expect a few hundred users for this webapp I'm working on so I think small int ought to be sufficient.

The class will not work without

stmt.setString(2, null);

I don't know why, I am new at java. But this works.

Thanks,
User is offlineProfile CardPM
+Quote Post

pbl
RE: Set Auto_increment Field Using PrepareStatement
22 Jun, 2008 - 11:32 AM
Post #9

D.I.C Lover
Group Icon

Joined: 6 Mar, 2008
Posts: 3,587



Thanked: 233 times
Dream Kudos: 75
My Contributions

Use an insert statement specifying the column names... but do NOT specify the autoincrement field

INSERT INTO tablename (filed1, field2, field3) VALUES (?, ?, ?);
User is offlineProfile CardPM
+Quote Post

Fast ReplyReply to this topicStart new topic
Time is now: 1/9/09 12:48AM
Invision Power Board v2.1.7 © 2009  IPS, Inc.
Licensed to: MediaGroup1

Be Social

Dream.In.Code RSS Feed Dream.In.Code LinkedIn Group Follow Us On Twitter

Live Java Help!

Java Tutorials

Reference Sheets

Java Snippets

DIC Chatroom

Bye Bye Ads

Monthly Drawing

Thumb Drive

Top Contributors

Top 10 Kudos This Month