How to input data into MySQL from Eclipse

Question

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?

Answer 1

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();
              }
          }

     }

Answer 2

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.

Answer 3

Have you tried committing?

con.commit();

Answer 4

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.