updating mysql with java

Question

my database consist of 2 columns username and balance. username has the value Mike, and balance has the value null. i am trying to update balance, but for some reason it doesn't update. also i don't get any errors. any suggestions

 <p>This is the deposit page</p>
    <form action="deposit.jsp" method="POST">
    <label>deposit: </label><input type="text" name="deposit"><br>
    <label>name: </label><input type="text" name="name"><br>
    <input type="submit" value="deposit">

  <%

  String deposit=request.getParameter("deposit");
 String name=request.getParameter("name");
   try{
  Connection conn = null;
  Class.forName("com.mysql.jdbc.Driver").newInstance();
  conn =    DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","root"); 
  Statement st=null;
  st=conn.createStatement();
 st.executeUpdate("update username set balance='"+deposit+"' where username='"+name+"'");

 }
catch(Exception e){
 System.out.println(e);
}
%>
</form>
 </body>
 </html>

score 2 · Answer 1 · answered May 16 '12 at 20:28

2

Add: conn.commit()
at the end of the block

answered May 16 '12 at 20:28

Dave DiFranco

1,695
10
9

i added conn.commit() after st.executeUpdate("update us.... and it still doesn't work – toky May 16 '12 at 20:44

Yevgeny Simkin · Accepted Answer · 2012-05-16T20:55:00.043

If I understand what's happening here correctly, you're actually calling the db insert as soon as the page loads (not when the user hits submit)... In which case your sql is going to looking for a username = '', right? Maybe I'm not understanding what you're doing. and you need to call commit() on the whole shebang, but that's after you actually give it some useful values.

[edit..] oh I see you're calling the same page twice, depending on if the user hits submit... this is an INCREDIBLY bad way to handle this sort of thing... As a test to see if you can put stuff in your database it's fine, but you really must never build anything real like this. Keep your presentation and business logic separate at all costs. I know that JSP (and PHP and all the other template engines) give you the ability to merge them, but just because you "CAN" do something, doesn't mean that you should.

[EDIT 2] We can't see your database, but do you really have a table called username in which you have a column called username? Cause that's what you're talking to right now. update [TABLE NAME]. I'm guessing this is failing cause you're trying to write to a non-existant table.

oh and commit is called after update.

i feel really stupid right now, i had the wrong name for the table. thank you it works now. — toky, May 16 '12 at 21:01
no need to feel stupid, it's just a typo... like I said, what you really want to rethink is your approach to how you handle separating your presentation from your business logic. The correct way to handle this is to make AJAX calls from your display pages to JSPs that ONLY handle data manipulation and hand you back JSON that you can then display in the page (if you need to). — Yevgeny Simkin, May 16 '12 at 21:24

score 1 · Answer 3 · edited May 16 '12 at 20:49

Better if you create two pages so that the query runs only once. First page includes the input fields like in your case name and balance. So after submitting the form forward it to the next page where you can put the code you write in the scriptlet. Also write System.out.println(e.getmessage()). So you can see the proper message. And also try your query in Mysql browser to confirm that there is not spelling mistake in your query.

score 1 · Answer 4 · edited May 23 '17 at 12:20

The first time you load this page attempts to update the following:

update username set balance='null' where username='null';

At least there is a user with this information and are of type char, nothing will happen. On the other hand, using scriptlets today is considered bad practice. I recommend that you be applying the MVC design pattern.

There is an excellent answer JSP using MVC and JDBC.

updating mysql with java

4 Answers4