Person to database

I have the follwoing code as you can see below

my table is this:

Table passenger
===============
paId, paFName, paLName, paType
---------------
paId             int(11) PK
paFName          varchar(15)
paLName          varchar(15)
paType           varchar(6)

My question is about the ID (paID)
In the GUI you should fill out an ID but it seems this id should just increase and not be filled out by hand. What do you think about this?
/**
	 * registerPerson - register a new person in database
	 * 
	 * @param id
	 *            - id of the person to register
	 * @param firstName
	 *            - first name
	 * @param lastName
	 *            - last name
	 * @param type
	 *            - type {"F" - female, "M" - male, "C" - child}
	 * @return true if person was saved successfully, else false
	 */
	public boolean registerPerson(int id, String firstName, String lastName,
			String type) {
		return true;
	}

Open in new window

MickeysAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
chaitu chaituConnect With a Mentor Commented:
check this.I


INSERT INTO passenger(paI,paFName ,paLName,paType)

values(SELECT 1 + coalesce((SELECT max(id) FROM passenger), 0), "aaa'',"dgdg","M")


http://stackoverflow.com/questions/1587562/problem-with-mysql-insert-max1
0
 
chaitu chaituCommented:
while inserting id inserting into DB.do like this.your insert statement should be like this.

or create oracle SEQUENCE for ID so it wil automaticlaly incrmented.

(select max(id)+1 from  tablename,firstname,lastName,type)
0
 
for_yanCommented:
I think you should not require the user to enter ID.

You can set up the table in such a way that when  you insert the row into the table it automatically inserts incrementing integer ID as one of the columns.

Another option is to use database object - sequence - which generates incremnting numbers, and before you
make insert you query the sequence for the next number and then use that number as ID
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
for_yanCommented:

the syntax in Oracle is like that:


CREATE SEQUENCE seq_passenger_id;

SELECT seq_passenger_id.NEXTVAL FROM DUAL;


in mySQL I guess they don't have dual, let me check

0
 
chaitu chaituCommented:
check below on how to create sequence.

http://www.techonthenet.com/oracle/sequences.php
0
 
for_yanCommented:
This is how you create table with auto_increment ciolumn in MySQL

http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
) ENGINE=MyISAM;

INSERT INTO animals (name) VALUES
    ('dog'),('cat'),('penguin'),
    ('lax'),('whale'),('ostrich');

SELECT * FROM animals;

Open in new window

0
 
MickeysAuthor Commented:
well the problem is that I aint allowed to change the tables. Otherwise I wouldnt have build it like that.

So my question is basiccly how I make it to run with this setup. I mean even if someon enter a digit ini the textfield......I could just igonre it if it increases it self. So what is your opinion on how to solve it?
0
 
for_yanCommented:
No you still don't let them enter digits - it makes no sense

0
 
chaitu chaituCommented:
then your insert statment should be like this.

INSERT INTO TABLE NAME values
(select max(ID)+1 from  table name ,firstname,lastName,type)
0
 
for_yanCommented:

you first chack if they already cretaed the table with auto-incementing id

because it is rather common parctivce - try tto insert one line and check if it inserted the id
0
 
for_yanCommented:
if not, then perhaps the insert statemnt as suggested chaituu will work

or else you can first select max(id) from table

and then incerement it and  insert in the next statement
0
 
CEHJCommented:
If you can't alter the table then you need to get ready to catch a dup key exception and tell the user to choose something else if so- they're probably going to choose something that doesn't work sooner or later
0
 
MickeysAuthor Commented:
INSERT INTO passenger values('Sven','Sven','M')
Error Code: 1136. Column count doesn't match value count at row 1


and if I do this

INSERT INTO passenger values(SELECT max(ID)+1 from  passenger, 'Sven','Sven','M')
SQL ERROR near SELECT
0
 
for_yanCommented:


INSERT INTO passenger(paFName ,paLName,paType) values('Sven','Sven','M')
0
 
for_yanCommented:
Try this way (see above) with naming the columns
0
 
chaitu chaituCommented:
INSERT INTO passenger(id,paFName ,paLName,paType) values(SELECT max(ID)+1 from  passenger,"aaa'',"dgdg","M")
0
 
CEHJCommented:
So you've decided to ignore the user-input id then?
0
 
MickeysAuthor Commented:
INSERT INTO passenger(paFName ,paLName,paType) values('Sven','Sven','M')
Error Code: 1364. Field 'paId' doesn't have a default value


INSERT INTO passenger(paI,paFName ,paLName,paType) values(SELECT max(ID)+1 from  passenger,"aaa'',"dgdg","M")
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT max(ID)+1 from  passenger,"aaa'',"dgdg","M")' at line 1



Yes I have decided to igonre it. I cant really see why I should be able to input an id??? Since in the table that we got the passenger right now has the number 1-14. It is better it just increased by itself. Our atleast that is my point of view. Otherwivse I need to check if it is available as well.
0
 
for_yanCommented:


you can do it in two statements

select max(id) from passenger

and then retrieve and increment it in java code and

and insaert all four fields

0
 
for_yanConnect With a Mentor Commented:


ResultSet rs = stmt.executeQuery("select max(paId) from passenger");

  int maxId = -1;
  while(rs.next()) maxId = rs.getInt(1);

stmt.executeUpadte("INSERT INTO passenger(paId, paFName ,paLName,paType) values(" + (maxId+1) + ",'" + name + "','" + namelast + "','" + type + "')")
0
 
MickeysAuthor Commented:
this worked
public boolean registerPerson(int id, String firstName, String lastName,
			String type) {
		int ie = 0;

		try {
			mStmt = mCon.createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		try {
			if (mStmt.execute("select max(paId) from passenger")) {
				mRs = mStmt.getResultSet();

			} else {
				System.err.println("select failed");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		try {
			for (int i = 0; mRs.next(); i++) {
				ie = mRs.getInt(1);
				System.out.println(ie);
			}
		} catch (Exception e) {

		}

		int val = 0;
		ie++;

		try {
			val = mStmt
					.executeUpdate("INSERT INTO passenger(paId, paFName ,paLName,paType) values("
							+ ie
							+ ", '"
							+ firstName
							+ "','"
							+ lastName
							+ "','"
							+ type + "')");

		} catch (SQLException e) {
			e.printStackTrace();
		}
		if (val > 0)
			return true;

		else
			return false;

	}

Open in new window

0
 
for_yanCommented:
Great!
0
 
MickeysAuthor Commented:
but  I am still a little confused.

since paID is a primary key....isnt that bound to other tables then? which mean if I add a number it gets screwed up in other tables`?
0
 
MickeysAuthor Commented:
hmmm and when I check the other tables I can confirm that this is the case.
That means I cant increase it. :-/
0
 
for_yanCommented:


primary key is a key of this table - it may or may not be bound to other
tables. If there is already existing schema - you need to contact someone who
was maintaining it before - how otherwise would you know what other dependecy you need to honor?

In general if it allowed you to insert then if the schema was devised properly
 it probably meanse that you should be OK, but you cannot be sure of it of course
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.