Solved

Person to database

Posted on 2011-09-28
25
234 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:Mickeys
  • 12
  • 6
  • 5
  • +1
25 Comments
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 36813014
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
 
LVL 47

Expert Comment

by:for_yan
ID: 36813018
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
 
LVL 47

Expert Comment

by:for_yan
ID: 36813025

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
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 36813029
check below on how to create sequence.

http://www.techonthenet.com/oracle/sequences.php
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36813041
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
 

Author Comment

by:Mickeys
ID: 36813079
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
 
LVL 47

Expert Comment

by:for_yan
ID: 36813084
No you still don't let them enter digits - it makes no sense

0
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 36813088
then your insert statment should be like this.

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

Expert Comment

by:for_yan
ID: 36813092

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
 
LVL 47

Expert Comment

by:for_yan
ID: 36813108
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 36813197
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
 

Author Comment

by:Mickeys
ID: 36813646
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 47

Expert Comment

by:for_yan
ID: 36813656


INSERT INTO passenger(paFName ,paLName,paType) values('Sven','Sven','M')
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36813660
Try this way (see above) with naming the columns
0
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 36813690
INSERT INTO passenger(id,paFName ,paLName,paType) values(SELECT max(ID)+1 from  passenger,"aaa'',"dgdg","M")
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 36813788
So you've decided to ignore the user-input id then?
0
 

Author Comment

by:Mickeys
ID: 36813827
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
 
LVL 47

Expert Comment

by:for_yan
ID: 36813842


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
 
LVL 20

Accepted Solution

by:
chaitu chaitu earned 250 total points
ID: 36813850
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
 
LVL 47

Assisted Solution

by:for_yan
for_yan earned 250 total points
ID: 36813860


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
 

Author Comment

by:Mickeys
ID: 36813903
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
 
LVL 47

Expert Comment

by:for_yan
ID: 36813908
Great!
0
 

Author Comment

by:Mickeys
ID: 36813915
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
 

Author Comment

by:Mickeys
ID: 36813918
hmmm and when I check the other tables I can confirm that this is the case.
That means I cant increase it. :-/
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36813934


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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
count11 challenge 6 47
stringclean challenge 26 56
Free Alternative to JIRA 4 58
@SBGen Method 3 25
This was posted to the Netbeans forum a Feb, 2010 and I also sent it to Verisign. Who didn't help much in my struggles to get my application signed. ------------------------- Start The idea here is to target your cell phones with the correct…
Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now