Java insert to mySQL workbench

Question

I have a mySQL database named Customer that I am trying to insert data from a textfield in a GUI. I ran the code below and in the console it said "Connected Successfully" with no errors. I checked to see if the information had been inserted into the Customer table of my mySQL database and nothing was inserted. Does anyone know why?

         button.setOnAction(e -> {


         Connection dbConnection = null;
            PreparedStatement preparedStatement = null;

         try {


           Customer cust = new Customer();
          dbConnection = Connect();
            String sql="Insert into CIS3270.Customer(firstName,lastName, email,userNAME,Address,Zip,State,SecurityQ,  Password, ConfirmPassword,SSN)VALUES (?,?,?,?,?,?,?,?,?,?,?)"; 
            preparedStatement =  dbConnection.prepareStatement(sql);


            preparedStatement.setString(1,cust.getFirstName()); 
            preparedStatement.setString(2,cust.getLastName()); 
            preparedStatement.setString(3,cust.getEmail()); 
            preparedStatement.setString(4,cust.getUserNAME()); 
            preparedStatement.setString(5,cust.getAddress());
            preparedStatement.setString(6,cust.getZip()); 
            preparedStatement.setString(7,cust.getState());
            preparedStatement.setString(8,cust.getSecurityQuestion());
            preparedStatement.setString(9,cust.getPassWORD()); 
            preparedStatement.setString(10,cust.getConfirmPassword());
            preparedStatement.setString(11,cust.getSSN()); 

            preparedStatement.executeBatch(); 
            preparedStatement.executeUpdate();

            dbConnection.close(); 
            preparedStatement.close(); 



        LoginScreen loginPage = new LoginScreen();

        loginPage.start(primaryStage);

         }
         catch(Exception e1) {
            e1.printStackTrace();
         }
    });

       public static Connection Connect() {
       Connection con = null;
    try {
        Class.forName("com.mysql.jdbc.Driver");
        con = (Connection) DriverManager.getConnection("jdbc:mysql://(ip adress):3306/CIS3270", "root", "password");
    } catch (Exception e) {
        System.out.println("Can not connect");
    }
    if (con != null) {
        System.out.println("Connected Successfully");
    }
    return con;
}

Are you tried commit (dbConnection.commit()) before close()? — Alberto
– Alberto, Commented Apr 23, 2018 at 7:31

Kayaman · Accepted Answer · 2018-04-23 07:32:39Z

1

You have no need for a batch insert since you're inserting a single row.

The reason your code doesn't do anything is because you're executing an empty batch. You need to call preparedStatement.addBatch() too. Or just remove that executeBatch() call, as you seem to call executeUpdate() too.

answered Apr 23, 2018 at 7:32

Kayaman

73.8k6 gold badges91 silver badges128 bronze badges

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

7 Comments

Jay Over a year ago

Yes, I tried both removing executeBatch() and adding it as well and got the same result - Successful Connection but nothing actually inserted

Kayaman Over a year ago

Is the code actually executing? Check with logging or debugging first.

Kayaman Over a year ago

Well, did you try dbConnection.commit(), although autocommit should be enabled by default.

Jay Over a year ago

I just added dbConnection.commit() and ran it. When I clicked the button in my GUI, I got an error in my console saying 'cant call commit when autocommit=true. Is this basically saying there is no need to call commit when autocommit is already true?

Jay Over a year ago

I finally got the insert to work (partially). I say partially because every time I insert in eclipse a new row is created on mySQL Workbench, but all of the values are NULL. Are there any suggestions at this point?

|

Collectives™ on Stack Overflow

Java insert to mySQL workbench

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related