Unable to write data to mysql

Question

I created a simple registration form. I use struts2 and I want that when the user clicks on submit -> the data is displayed in the mysql database.

So, I'm trying to write data to database:

public void registerUser(){ user.add(username); user.add(password); user.add(email); user.add(picture); //connect to database try { Class.forName("com.mysql.jdbc.Driver"); try { connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/myFacebook?" + "user=root&password=root"); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } //Insert Data into database try(PreparedStatement createUser = connection.prepareStatement("Insert into user(username, password, email, picture)" + "VALUES (?, ?, ?, ?)")){ for(int i = 0; i < 1000; i++) { createUser.setInt(0, i+1); } createUser.setString(1, user.get(0)); createUser.setString(2, user.get(1)); createUser.setString(3, user.get(2)); createUser.setString(4, user.get(3)); int rowsUpdated = createUser.executeUpdate(); createUser.close(); }catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } try { connection.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } }

I decided to create an ArrayList in the User class, because it seems to me easier to write data.

 ArrayList<String> user = new ArrayList<String>();

I run all my system through apache tomcat. Everything starts and does not give any errors. All methods and files are correctly connected to each other. But when I check data in the console, the data is not displayed and my user table is empty.

I just can not understand what the error is. Either the data from the form is somehow incorrectly called, or I incorrectly write it to the data.

Please help me figure it out. Thank you in advance!

UPD:

  //Insert Data into database try(PreparedStatement createUser = connection.prepareStatement("Insert into user(user_id, username, password, email, picture)" + "VALUES (?, ?, ?, ?, ?)")){ createUser.setInt(0, userid); createUser.setString(1, user.get(0)); createUser.setString(2, user.get(1)); createUser.setString(3, user.get(2)); createUser.setString(4, user.get(3)); int rowsUpdated = createUser.executeUpdate(); createUser.close();

Accepted Answer · 2016-11-25T12:09:02

Apparently the problem is in this section of the code.

 for(int i = 0; i < 1000; i++) { createUser.setInt(1, i+1); createUser.setString(2, user.get(0)); createUser.setString(3, user.get(1)); createUser.setString(4, user.get(2)); createUser.setString(5, user.get(3)); } int rowsUpdated = createUser.executeUpdate();

Here you set the request parameters in the loop body, but run the execution after exiting the loop.

You need to either move the string to the loop body.

 int rowsUpdated = createUser.executeUpdate();

Or add to the loop line

 createUser.addBatch();

And after the cycle call

 preparedStatement.executeBatch();

And executeUpdate removed altogether.

UPD:

It looks like it's not just that. Here is your sql line:

 "Insert into user(username, password, email, picture)" + "VALUES (?, ?, ?, ?)"

Four apparently string parameters. You are trying to set her five parameters, besides not matching the type!

  createUser.setInt(1, i+1); createUser.setString(2, user.get(0)); createUser.setString(3, user.get(1)); createUser.setString(4, user.get(2)); createUser.setString(5, user.get(3));

A SQLException thrown on the fifth line.

Most likely your sql line should now look something like this:

 "Insert into user(id, username, password, email, picture)" + "VALUES (?,?, ?, ?, ?)"

I tried the first option -> transferred it to the loop body.
Either user.get (...) simply does not receive data from the form.
I, unfortunately, do not even understand how to check what the snag is.
@Alex, can your execution just fail to reach the cycle, but fails while trying to create a PreparedStatement ?
Is it possible to output an error from catch to the log in order to read it later?

Unable to write data to mysql

1 answer 1

More articles: