0

I'm trying to input data into MySQL database through eclipse. Here's what I have so far:

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import com.mysql.jdbc.Statement;

public class MySQL{

public static void main(String[] args)throws Exception{

    Class.forName("com.mysql.jdbc.Driver");

    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/database","root","root");

    PreparedStatement stmt = con.prepareStatement("INSERT INTO 'database'.'Table'(Account_ID,First_Name,Last_Name) VALUES ('hello12','Ed','Lee')");

    ResultSet result = stmt.executeQuery();}

 }

It doesn't work for some reason...any ideas?

Improve this question
5
  • You need to provide more details than it doesn't work. Is the mysql instance up and running? Can you get a connection? Where is it failing? Do you have a stack trace? Besides that, your statement: "I'm trying to input data into MySQL workbench through eclipse" doesn't make any sense to me? Commented Aug 9, 2012 at 23:33
  • Ok so the terminology you need here is that you are trying to input data into you MySQL database rather then workbench. Workbench is just the name MySQL gives to their database management tool. Anyways, back to your question can you give some excerpts from the error message you get...? Commented Aug 9, 2012 at 23:34
  • I already have the db made. I'm trying to insert the values in their respective spots.And the mysql instance is running Commented Aug 9, 2012 at 23:38
  • I didn't get any error messages. However when I refresh the db in MySQL workbench, the values do not appear. Commented Aug 9, 2012 at 23:39
  • Do you need backticks rather than apostrophes in your table names? Did you check the result of your executeQuery statement? Is your connection rigged up to autocommit after each statement? It's good practice always to close your result sets and connections when you're done with them. Commented Aug 10, 2012 at 0:39

4 Answers 4

Reset to default
0

Have you tried committing?

con.commit();

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

where do I put con.commit()? after what line?
0

Change your code likes this;

    public class MySQL{
          public static void main(String[] args)throws Exception{
              Class.forName("com.mysql.jdbc.Driver");
              try{
                  Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/database","root","root");
                   PreparedStatement stmt = con.prepareStatement("INSERT INTO 'database'.'Table'(Account_ID,First_Name,Last_Name) VALUES (?,?,?)");
                   stmt.setString(1, "hello12");
                   stmt.setString(2, "Ed");
                   stmt.setString(3, "Lee");
                   stmt.executeUpdate();
              } catch (Exception e) {
                   e.printStackTrace();
              } finally {
                   stmt.close();
                   con.close();
              }
          }

     }
Improve this answer

1 Comment

there are errors with the stmt.close() and con.close() saying the stmt and con variables are not resolved. I create and initialize both variables, but it still doesn't work
0

As suggested by others you need to commit the changes you made in your sql statements. Here is how it should look:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class MySQL{

    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        Connection con = null;
        PreparedStatement stmt = null;
        try {
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/database", "root", "root");
            stmt = con.prepareStatement("INSERT INTO 'database'.'Table'(Account_ID,First_Name,Last_Name) VALUES (?,?,?)");
            stmt.setString(1, "hello12");
            stmt.setString(2, "Ed");
            stmt.setString(3, "Lee");
            stmt.executeUpdate();
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                   stmt.close();
                } catch (SQLException ex) {
                }
            }
            if (con != null) {
                try {
                   con.close();                    
                } catch (SQLException ex) {
                }
            }
        }
    }
}

Import things to note that have been changed.

  • You declare the variables before the try block so that they are accessible in the finally block, but initialize them inside the try block in case they throw an exception.
  • The PreparedStatement replaces the new values with ? syntax instead of being hard coded. In a real application those will end up being passed in, and if you just did String concatenation, you would be opening yourself up to SQL injection or other problems.
  • The finally block is used to free any resources regardless if an exception is thrown. So say you create the connection and prepared statement, but an error gets thrown executing it. The code in the finally block will still run to close the statement and connection, freeing those resources.
Improve this answer

Comments

0

Change the following line 

PreparedStatement stmt = con.prepareStatement("INSERT INTO 'database'.'Table'(Account_ID,First_Name,Last_Name) VALUES ('hello12','Ed','Lee')");

to 

PreparedStatement stmt = con.prepareStatement("INSERT INTO `database`.`Table`(Account_ID,First_Name,Last_Name) VALUES ('hello12','Ed','Lee')");

the single quotes used for the database name and the table name will throw a syntax error. Instead use ` this symbol.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.