Java + SQL : INSERT INTO doesn't works

Question

I am a beginner on Java and I am having a problem inserting into a HSQLDB table.

I want this code to add an entry in the VEHICLE table.

But after launching thic code, no new line appears in table VEHICULE, and I don't have any error message in the console.

In console, I can read the result of println() : VehiculeDAO 85 : vehicule query : INSERT INTO VEHICULE (MARQUE, MOTEUR, PRIX, NOM, ID) VALUES ('2','2','14322.429728209721','tbsyfewdfj','52')

Here is my code

package fr.ocr.dao.implement;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import fr.ocr.dao.DAO;
import fr.ocr.sql.HsqldbConnection;
import test.database.Main;
import voiture.Marque;
import voiture.Vehicule;
import voiture.moteur.Moteur;
import voiture.option.Option;

public class VehiculeDAO extends DAO<Vehicule>{

  Connection connect;
  String query = "";

  public VehiculeDAO(Connection conn) {
    super(conn);
    connect = conn;
  }

  public boolean create(Vehicule obj) {
    boolean action = false;
    try {

      // Table vehicule
      Statement state = connect.createStatement(
          ResultSet.TYPE_SCROLL_INSENSITIVE,
          ResultSet.CONCUR_UPDATABLE);
      String queryVeh = "INSERT INTO VEHICULE (MARQUE, MOTEUR, PRIX, NOM, ID)" +
                " VALUES ("
                + "'" + obj.getMarque().getId() +"',"
                + "'" + obj.getMoteur().getId() +"',"
                + "'" + obj.getPrix() +"',"
                + "'" + obj.getNom() +"',"
                + "'" + obj.getId()
                + "')";

      System.out.println("VehiculeDAO 85 : vehicule query : \n" + queryVeh);
      ResultSet resVeh = state.executeQuery(queryVeh);

      resVeh.close();
      state.close();
      }

    } catch (SQLException e) {
      e.printStackTrace();
    }

    return action;

  }
}

However the database is functional and returns its tables and contents during this SELECT query example in another .java file :

  Connection conn = DriverManager.getConnection("jdbc:hsqldb:file:"
      + path + "VEHICULE", "SA", "");
  String[] tablesnames = { "marque", "type_moteur", "moteur",
      "option", "vehicule_option", "vehicule" };

  for (String table : tablesnames) {
    Statement state = conn.createStatement();
    System.out.println(("\nContenu de la table : " + table)
        .toUpperCase());
    ResultSet result = state.executeQuery("SELECT * FROM " + table);
    ResultSetMetaData resultMeta = result.getMetaData();

    String columnSeparator = "", rowSeparator = "";
    for (int i = 1; i <= resultMeta.getColumnCount(); i++) {
      columnSeparator += "********************";
      rowSeparator += "--------------------";
    }

The problems is the same if I use PreparedStatement.

I don't have any error in console and nothing added to VEHICULE table :

public boolean create(Vehicule obj) {
  boolean action = false;
  try {

    Statement state = connect.createStatement(
        ResultSet.TYPE_SCROLL_SENSITIVE,
        ResultSet.CONCUR_UPDATABLE);
    PreparedStatement prepare = connect
        .prepareStatement("INSERT INTO VEHICULE (MARQUE, MOTEUR, PRIX, NOM, ID) VALUES(?, ?, ?, ?, ?)");
    prepare.setInt(1, obj.getMarque().getId());
    prepare.setInt(2, obj.getMoteur().getId());
    prepare.setDouble(3, obj.getPrix());
    prepare.setString(4, obj.getNom());
    prepare.setInt(5, obj.getId());
    System.out.println("VehiculeDAO 85 : prepare : " + prepare);
    Integer resVeh = prepare.executeUpdate();
    System.out.println("VehiculeDAO 85 : prepare.executeUpdate() : " + resVeh);

In console I get :

VehiculeDAO 82 : prepare : org.hsqldb.jdbc.JDBCPreparedStatement@55e71685[sql=[INSERT INTO VEHICULE (MARQUE, MOTEUR, PRIX, NOM, ID) VALUES(?, ?, ?, ?, ?)], parameters=[[2], [2], [15016], [qtrqphfrur], [52]]]

VehiculeDAO 84 : prepare.executeUpdate() : 1

I've tried this : no console error and nothing added :

state.executeUpdate(queryVeh);

And also this : no console error and nothing added :

try ( final PreparedStatement ps = connect.prepareStatement(
    "INSERT INTO VEHICULE ( MARQUE, MOTEUR, PRIX, NOM, ID ) VALUES ( ?,?,?,?,? )" ) )
{
ps.setInt(1, obj.getMarque().getId() );
ps.setInt(2, obj.getMoteur().getId() );
ps.setDouble(3, obj.getPrix() );
ps.setString(4, obj.getNom() );
ps.setInt(5, obj.getId() );
System.out.println("VehiculeDAO 53 : vehicule query : \n" + ps);
//      return ps.executeUpdate();
ps.executeUpdate();

} catch (SQLException e) {
e.printStackTrace();
}

In console, println returns : VehiculeDAO 53 : vehicule query : org.hsqldb.jdbc.JDBCPreparedStatement@680836d5[sql=[INSERT INTO VEHICULE ( MARQUE, MOTEUR, PRIX, NOM, ID ) VALUES ( ?,?,?,?,? )], parameters=[[2], [2], [19519], [pzqazmngln], [52]]]

Thanks all for help, I really don't understand, it certainly comes from somewhere else ....?!

EDIT 2 ------------------------------------------------------------------

I found a solution, adding

this.connect.setAutoCommit(false);

before preparedStatments, and at the end to execute :

this.connect.commit();

Thanks for help !

Answer 1

Try something like this:

public int create( final Vehicule obj )
{
  try ( final PreparedStatement ps = connect.prepareStatement(
        "INSERT INTO VEHICULE ( MARQUE, MOTEUR, PRIX, NOM, ID ) VALUES ( ?,?,?,?,? )" ) )
  {
    ps.setInt(1, obj.getMarque().getId() );
    ps.setInt(2, obj.getMoteur().getId() );
    ps.setDouble(3, obj.getPrix() );
    ps.setString(4, obj.getNom() );
    ps.setInt(5, obj.getId() );
    System.out.println("VehiculeDAO 85 : vehicule query : \n" + ps);
    return ps.executeUpdate();
  }
  catch (SQLException e)
  {
    e.printStackTrace();
    return 0;
  }
}

It uses PreparedStatement, returns number of rows affected, (0 on error).
On some JDBC drivers it prints out the final SQL query, but some drivers do not implement Statement.toString() and it outputs only the ? instead of the actual values...
I was just guessing the types, based on your query's output...