Question 120958 in Mysql Question posted in
The official MySQL documentation can be found here.

I am trying to insert data into a mysql table from Java but the data won't appear in the table

Here is my code:

public static void insertData(stockData stock) throws Exception{
        Connection con;
        try {
            con = getConnection();
            String sql = "INSERT INTO stocks (id, ticker, price, percentChange, volume, net income) VALUES (?, ?, ?, ?, ?, ?);";
            PreparedStatement insert = con.prepareStatement(sql);

            insert.setInt(1, 0);
            insert.setString(2, stock.Ticker);
            insert.setString(3, stock.Price);
            insert.setString(4, stock.PercentChange);
            insert.setString(5, stock.Volume);
            insert.setString(6, stock.NetIncomeCurrent);

            insert.executeUpdate();

        } catch (SQLException e){
            System.out.println("ERROR");
            throw new SQLException();
        }
        con.close();
}

I believe the problem is coming from the insert.executeUpdate(); statement.

I have tried insert.execute();

The table does exist but all the values say null

Tags:

2

Answers


  1. 0

    I might be missing something here, but net income with a space normally isn’t a valid column name unless you specifically created it that way. You can try wrapping it in backticks and see if it works then. Does the column actually have a space in the name?

    Some reference: How to create column name with space?

    String sql = "INSERT INTO stocks (id, ticker, price, percentChange, volume, `net income`) VALUES (?, ?, ?, ?, ?, ?);";
    Login or Signup to reply.
  2. 0

    I assume that your database connection instance returns false for con.getAutoCommit().

    If so, a call to Connection::commit should do the job:

    public static void insertData( stockData stock ) throws SQLException
{
  try( final var connection = getConnection() )
  {
    assert !connection.getAutoCommit() : "Connection uses autocommit";
    final var sql = 
      """
      INSERT INTO stocks 
      (id, ticker, price, percentChange, volume, net_income) 
      VALUES (?, ?, ?, ?, ?, ?);""";
    try( final var insert = connection.prepareStatement( sql ) )
    {
      insert.setInt( 1, 0 );
      insert.setString( 2, stock.Ticker );
      insert.setString( 3, stock.Price );
      insert.setString( 4, stock.PercentChange );
      insert.setString( 5, stock.Volume );
      insert.setString( 6, stock.NetIncomeCurrent );

      insert.executeUpdate();

      connection.commit();    // <<<<<<
    }
    catch( final SQLException e )
    {
      connection.rollback();  // <<<<<<
      throw e;
    }
  } 
  catch( final SQLException e )
  {
    System.out.println( "ERROR" );
    throw e;
  }
}

    Of course we need to discuss the error handling … in particular as the rollback is implicit if the connection is closed without a commit.

    Alternatively, you can force autocommit by calling connection.setAutoCommit( true ) after you obtained the connection; in that case, the first catch block is obsolete.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search