Results 1 to 5 of 5
  1. #1
    ShinTec's Avatar
    ShinTec is offline Member
    Join Date
    Jan 2010
    Posts
    51
    Rep Power
    0

    Exclamation Help on inserting into table

    Having trouble getting my code to run im trying to insert variables into a database that i creating using a sql script
    Java Code:
    /*Drop and create database and the tables associated wit it*/
    	public void dbInitizationSequence()
    	{
    		/*Declaring classes needed*/
    		Connection conn = null;
    		Statement statement = null;
    		ResultSet rs = null;
    		
    		try
    		{
    			//loads the mysql driver
    			Class.forName(driver);
    			//establishing connection to the database
    			conn = DriverManager.getConnection(URL,"root","RootPassword");
    			//create access statement
    			statement = conn.createStatement();
    			/**/
    			int stateId;
    			int partyId;
    			int candId;
    			
    			/*insert statement for StateName*/
    			statement.executeUpdate("INSERT INTO tbl_state" + 
    			"VALUES"+ getSqlStateNam());
    			rs = statement.executeQuery("SELECT state_Id FROM state WHERE state_name =" + getSqlStateNam());
    			stateId = rs.getInt("state_Id");
    			/*call a listiterator ssli stands for sqlSeatListIterator
    			 * using this code the loop through the arrayList*/
    			ListIterator<Seat> ssli = getSqlSeatList().listIterator();
    			Seat s = null;
    			
    			/*This while should insert the value from the sqlArrayList 
    			 * into the tables of the database*/
    			while(ssli.hasNext())
    			{
    				s = ssli.next();
    				/*inserting party to database*/
    				statement.executeUpdate("INSERT INTO tbl_Party" +
    				"VALUES"+ s.getTheWinner().getPartyName());
    				/*Query to get party_id then read into a int partyID*/
    				rs = statement.executeQuery("SELECT party_id FROM tbl_party WHERE party_Name =" + s.getTheWinner().getPartyName());
    				partyId = rs.getInt("party_id");
    				
    				statement.executeUpdate("INSERT INTO tbl_Candidate" +
    				"VALUES" + s.getTheWinner().getCandiName() + String.format(",%d", partyId));
    				/*Query to get candidate_id*/
    				rs = statement.executeQuery("SELECT cand_id FROM tbl_candidate WHERE candidate_name =" + s.getTheWinner().getCandiName());
    				candId = rs.getInt("cand_id");
    				statement.executeUpdate("INSERT INTO tbl_Seat" +
    				"VALUES" + String.format("%d", stateId) + s.getSeatName() + String.format("%d", candId));
    				/*increment id*/
    				
    			}
    			/*closing connection to mysql*/
    			rs.close();
    			statement.close();
    			conn.close();
    			
    		}//end of try
    		catch(SQLException ex)
    		{
    			System.out.printf("SQL exception thrown in Class Sql Handler DbInitizationSequence()\n"); 
    			System.out.println(ex.getMessage());
    			ex.printStackTrace();
    			System.exit(0);
    		} 
    		catch(ClassNotFoundException cnfe) 
    		{
    			cnfe.printStackTrace();
    			System.out.printf("\nClassNotFoundException thrown in Class Sql Handler DbInitizationSequence()\n");
    			System.exit(0);
    		}
    		catch(Exception e)
    		{
    			System.out.printf("\nSomething wrong in Class Sql Handler DbInitizationSequence()\n");
    		}
    	}//end of method DbInitizationSequence()
    this is the line that giving me trouble
    Java Code:
    statement.executeUpdate("INSERT INTO tbl_state" + 
    			"VALUES"+ getSqlStateNam());
    it my first time writing this stuff so any help/constructive criticism will be much appreciated and thank you in advance oh also using mysql sever 5.1 64 bit
    and the mysql java connector
    Last edited by ShinTec; 06-07-2010 at 02:38 PM.

  2. #2
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,014
    Rep Power
    20

    Default

    That statement (and at least the next one, looking at it) will yield this:
    INSERT INTO tbl_stateVALUESwhatever_value_state_nam_returns.

    You see this a lot in concatenating things together...I frequently forget to stick a space at the end (or beginning) of a line.

    That should be:
    Java Code:
    statement.executeUpdate("INSERT INTO tbl_state " + 
    			"VALUES "+ getSqlStateNam());
    Now, you should be using PreparedStatements here. Because the above won't work either (unless SqlStateNam is a number). You're missing quotes. A PreparedStatement will handle all the quoting and escaping for you, which is a source of errors otherwise.

    So (and you probably want a PreparedStatement per SQL, which would be 6 I think in your case):
    Java Code:
    PreparedStatement sqlStateInsert = null;
    // other stuff here
    try {
    // more stuff
        sqlStateInsert = conn.prepareStatement("INSERT INTO tbl_state VALUES ?");
        sqlStateInsert.setString(1, getSqlStateNam());
    // More stuff.
    } catch things{}
    Just as a taster. Note, I can't remember offhand if the list after VALUES should be in brackets or not...so you may still get an error with the above, but I'm sure you can check that.

  3. #3
    ShinTec's Avatar
    ShinTec is offline Member
    Join Date
    Jan 2010
    Posts
    51
    Rep Power
    0

    Default

    Okay i got another question to ask, how would i insert the values into tables if im using auto increment in my script file for the primary ids do i still have to insert the primary id value into the sqlStateInsert.setString() or can i leave it blank :confused:

  4. #4
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,014
    Rep Power
    20

    Default

    For an auto-increment column you don't put it in the statement at all.
    So no need to leave it blank because it's not there in the first place.

  5. #5
    ShinTec's Avatar
    ShinTec is offline Member
    Join Date
    Jan 2010
    Posts
    51
    Rep Power
    0

Similar Threads

  1. Replies: 1
    Last Post: 04-23-2009, 07:55 PM
  2. Inserting a Table in a TextFile?
    By Keerti in forum Advanced Java
    Replies: 5
    Last Post: 11-19-2008, 04:31 PM
  3. Inserting current date into a DB table
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 02-15-2008, 08:38 AM
  4. Inserting into a table (Example)
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 02-06-2008, 09:25 AM
  5. menu inserting table in the document
    By daredavil82 in forum New To Java
    Replies: 0
    Last Post: 12-05-2007, 05:05 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •