wood burning stoves 2.0*
The moose likes JDBC and the fly likes inserting values from a  class to a mysql database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "inserting values from a  class to a mysql database" Watch "inserting values from a  class to a mysql database" New topic
Author

inserting values from a class to a mysql database

JC Bismark
Greenhorn

Joined: Jun 27, 2008
Posts: 29
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

Joined: Nov 11, 2008
Posts: 4
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

Joined: Jun 27, 2008
Posts: 29
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

Joined: Apr 14, 2004
Posts: 10336


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.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
JC Bismark
Greenhorn

Joined: Jun 27, 2008
Posts: 29
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

Joined: Oct 13, 2005
Posts: 38057
    
  22
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
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

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

Joined: Jun 27, 2008
Posts: 29
Thank you Campbell and Paul.Good advises.I will continue to look for the problem...
JC Bismark
Greenhorn

Joined: Jun 27, 2008
Posts: 29
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

Joined: Apr 14, 2004
Posts: 10336


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

Joined: Jun 27, 2008
Posts: 29
Thank you Paul.It fine now!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: inserting values from a class to a mysql database
 
Similar Threads
retreiving all rows from a mysql table to a jsp page
JPA - Hibernate - query.getResultList() returns 3 null values.
how to insert an image into MYSQL database using java
how to upload pdf files to My sql with java
how to keep register page data if register fails