DaniWeb Forum Index > Web Development > JSP

Updating a MySQL database using Java

Please support our JSP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: Jan 2008

Posts: 19

Reputation: jsully1 is an unknown quantity at this point

Solved Threads: 0

jsully1

Offline

Newbie Poster

Updating a MySQL database using Java

Nov 2nd, 2008

I am taking a web design and development class and we have been thrown into MySQL along with java without much knowledge beforehand. Was wondering if anyone could help me with understanding how to query the database and update entries, as I am confused on how to accomplish this.
I am able to insert new entries and display table info, but when it comes to adding to existing info nothing seems to work. I can get the statement to work if I supply the data in the SQL statement i.e UPDATE tableName SET columnName1 = value1, WHERE criteria. But when I attempt to use user input and send variables along nothing seems to work.

Any help would be appreciated.

Thanks

•

Join Date: Aug 2008

Posts: 1,162

Reputation: dickersonka will become famous soon enough

Solved Threads: 137

dickersonka dickersonka is offline

Offline

Veteran Poster

Re: Updating a MySQL database using Java

Nov 3rd, 2008

same thing as in the forum PreparedStatements

http://java.sun.com/docs/books/tutor.../prepared.html

Custom Application & Software Development
www.houseshark.net

•

Join Date: Dec 2004

Posts: 4,197

Reputation: peter_budo has much to be proud of

Solved Threads: 485

peter_budo peter_budo is offline

Offline

Code tags enforcer

Re: Updating a MySQL database using Java

-1

Nov 3rd, 2008

Maybe this tutorial can help you

Learn to see in another's calamity the ills which you should avoid.
Publilius Syrus
(~100 BC)
LJC - London Java Community, Graduate & Undergraduate Software Development Community, JAVAWUG (Java Web User Group), The London Android Group

•

Join Date: Jan 2008

Posts: 19

Reputation: jsully1 is an unknown quantity at this point

Solved Threads: 0

jsully1

Offline

Newbie Poster

Re: Updating a MySQL database using Java

Nov 6th, 2008

OK, I am still having trouble with this. All I want right now is to get something to work so I am going small. I am accessing a books database that consists of three tables, and all I want is to extract the authorID(primary key) by having the user enter the authors lastName. Currently this is what I have:

 Help with Code Tags 
 JSP Syntax (Toggle Plain Text) 
public void AddTitle() throws SQLException {
 
		//PreparedStatement addTitle = (PreparedStatement) connection.prepareStatement
		//("INSERT INTO titles (title) VALUES (?) WHERE authorId FROM authorISBN = (?)");
		try
		{
		PreparedStatement p = connection.prepareStatement("SELECT authorID FROM authors WHERE lastName = ?") ;
 
		String authorsFirstName, authorsLastName, newTitle, id;
		//authorsFirstName = GetAuthorFirstName();
		authorsLastName = GetAuthorLastName();
		//newTitle = GetNewTitle();
 
		p.setString(1, authorsLastName);
		//p.execute();
		ResultSet rs = p.executeQuery();
		Integer key = rs.getInt("authorID");
 
			//Object key = rs.();
			System.out.printf("Key for author %s is %d \n", authorsLastName, key);
		}//end try
		catch(Exception exception)
		{
 
			exception.printStackTrace();
		}
 
 
	}//end AddTitle method
public void AddTitle() throws SQLException {
		
		//PreparedStatement addTitle = (PreparedStatement) connection.prepareStatement
		//("INSERT INTO titles (title) VALUES (?) WHERE authorId FROM authorISBN = (?)");
		try
		{
		PreparedStatement p = connection.prepareStatement("SELECT authorID FROM authors WHERE lastName = ?") ;
	
		String authorsFirstName, authorsLastName, newTitle, id;
		//authorsFirstName = GetAuthorFirstName();
		authorsLastName = GetAuthorLastName();
		//newTitle = GetNewTitle();
		
		p.setString(1, authorsLastName);
		//p.execute();
		ResultSet rs = p.executeQuery();
		Integer key = rs.getInt("authorID");

			//Object key = rs.();
			System.out.printf("Key for author %s is %d \n", authorsLastName, key);
		}//end try
		catch(Exception exception)
		{

			exception.printStackTrace();
		}
		
		
	}//end AddTitle method

And I receive a java.sql.SQLException: Before start of result set when I try and use the method. Any suggestions would be helpful. Thanks

•

Join Date: Dec 2004

Posts: 4,197

Reputation: peter_budo has much to be proud of

Solved Threads: 485

peter_budo peter_budo is offline

Offline

Code tags enforcer

Re: Updating a MySQL database using Java

-1

Nov 7th, 2008

authorsLastName, I do not see that variable passed down to AddTitle() method. If you not able to solved it, provide the whole code for this servlet

•

Join Date: Feb 2006

Posts: 2,415

Reputation: masijade has much to be proud of

Solved Threads: 256

masijade

Offline

Nearly a Posting Maven

Re: Updating a MySQL database using Java

Nov 7th, 2008

Uhm, Peter, what's this

Help with Code Tags

JSP Syntax (Toggle Plain Text)

authorsLastName = GetAuthorLastName();

;-)

However, with the way it's written (if it compiles at all) authorsLastName is an instance variable anyway (as it is not declared in the method simply defined), and that is probably not a good idea.

Otherwise, knowing exactly what SQLException you get would help. Post the rest of the message. I can almost guarantee, however, that it has to do with what GetAuthorLastName() is returning (or not).

Java Programmer and Sun Systems Administrator

----------------------------------------------

Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it.
--Brian Kernighan