• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

inserting values from a class to a mysql database

 
JC Bismark
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have a model class call HandlereplyModel.java that insert values into a mysql database (myfanclub).I included the mysql-connector-java-5.0.8-bin.jar file into the WEB-INF/lib of my application.
When I deployed the app., a form appears that need to be fill,then submit. It sends values to a servlet that forward those values to the model class(see above). I got "Success" in successPage.jsp when the values (a record) are inserted in myfanclub database.
However,I got duplicate record everytime I enter one record in the form.Please,advise...

[code]
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class HandlereplyModel {


public String insert(String fname, String lname, String Email, String phone) {

/* Create string of connection url within specified
format with machine name,
port number and database name. Here machine name id
localhost and database name is myfanclub. */

try {
String url = "jdbc:mysql://localhost:3306/myfanclub"; /* myfanclub is my new database that contains
my MemberDetails table */

// Load JBBC driver "com.mysql.jdbc.Driver"
Class.forName("com.mysql.jdbc.Driver").newInstance();

/* create a connection to the database by using Connection interface
and java.sql.DriverManager's getConnection( ) method */
Connection conn = DriverManager.getConnection(url,"root","root");

/* createStatement() is used for create statement
object that is used for
sending sql statements to the specified database. */
Statement st = conn.createStatement();

// sql query to insert values in the specified table
st.executeUpdate("INSERT into MemberDetails (First_name, Last_Name, E_mail,Phone_num) VALUES ( '" + fname + "' , '" + lname + "' , '" + Email + "' , '" + phone + "' );" );

st.close();
conn.close();
}
catch (Exception e) {

return"Failure";
}
return "Success";//// In Handlereply servlet we can check if the result of this function is success, and if it is, serve back the SuccesPage.jsp

}

}
 
Venkat Pingala
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

execute the commit() before closing the statement and connection.

like as below :

int rowsInserted = stmt.executeUpdate(..);

if(rowsInserted == 1){
conn.commit();
}

st.close();
conn.close();
 
JC Bismark
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Venkat, it's not working.After deploying and filling the form it show the page failure.So the values are not insert.
But when I remove your "if" statement it shows Success,but insert two identical rows.Any thoughts?
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Venkat, it's not working.After deploying and filling the form it show the page failure.So the values are not insert.
But when I remove your "if" statement it shows Success,but insert two identical rows.Any thoughts?


So you are issuing two insert statements, hence rowsInserts does not == 1 and the commit does not happen. I'm not sure what you gain by conditionally executing the commit?

So, you have to work out why your insert is being run twice.
 
JC Bismark
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Do I need to add the JDBC Driver path in the environment variables PATH?
I really need some help to solve this duplicate insert...

thanks,
 
Campbell Ritchie
Sheriff
Pie
Posts: 49796
69
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you are getting duplicate insertions, then whatever the problem is, it isn't about the PATH or the CLASSPATH; if it were either of them you wouldn't have anything inserted at all.
 
Paul Clapham
Sheriff
Posts: 21322
32
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you are getting two rows inserted, then (based on my quick look at that code) your insert() method is being called twice. You should find out why that is.

But could I make a couple of comments about that code?

1. Use PreparedStatement for SQL with parameters, rather than doing string manipulations to generate an SQL command. Otherwise you'll find it crashes unexpectedly when you add somebody named O'Brien to your database. Besides it's easier to read, easier to code, and prevents SQL injection attacks.

2. Close your statement and connection in a finally-block after the try- and catch-blocks rather than where you have it. Otherwise the connection won't be closed when an exception is thrown.

3. If you have a method which is returning one of two alternatives which are basically yes-or-no, true-or-false, then it should return a boolean rather than a String with arbitrary magic constant values.

None of that is the cause of your problem, but those things should simplify your life in the long run.
 
JC Bismark
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you Campbell and Paul.Good advises.I will continue to look for the problem...
 
JC Bismark
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I found the error in my servlet that control this model class.Now I can insert values without duplicate in mysql table.
what's the syntax to delete all duplicate records already in my table?
please,advise...
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

what's the syntax to delete all duplicate records already in my table?

Unfortunately, there is no simple SQL to remove duplicates. This is largely because, if duplicates are undesirable, relational databases provide a mechanism to prevent them getting in there in the first place, so its not something you should ever really have to do.

What you will need to do is create a temporary table that matches the DDL for your current table, insert into this second table all the data from the first, grouping the records. Then delete from the first and insert all the data form the second. Your select query will be something like:
 
JC Bismark
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you Paul.It fine now!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic