Java/ Sqlserver transaction divided into multiple methods

Asked

Viewed 778 times

3

I have a method that performs several Prepared Statements, only two of them in methods of other classes. In my method, I have the object connection, and if I want to start a transaction, I have to: 

connection.setAutoCommit(false); 
connection.commit();

and in case of exception, also do the rollback. But my method calls other methods with other objects like Connection, here is my question, how to start the transaction?

My method:

 public void insereProduto(Produto produto, ProdutoDesc productDesc, ArrayList<Autor> autores) throws ClassNotFoundException, SQLException {

    DatabaseNET connection = new DatabaseNET();

    PreparedStatement sp = connection.getConnection().prepareStatement("INSERT INTO products(products_quantity, products_image,products_price,products_date_added,products_last_modified,products_date_available,products_weight,products_status,products_tax_class_id,manufacturers_id,products_ordered,editoras_id,coleccoes_id,tipoproduto_id,condicoes_id,products_code,specials_id,products_ano) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", PreparedStatement.RETURN_GENERATED_KEYS);

    sp.setFloat(1, 99998);
    sp.setString(2, produto.getProducts_image());
    sp.setFloat(3, produto.getProducts_price());
    sp.setDate(4, produto.getProducts_date_added());
    sp.setDate(5, produto.getProducts_last_modified());
    sp.setDate(6, produto.getProducts_date_available());
    sp.setFloat(7, produto.getProducts_weight());
    sp.setBoolean(8, produto.getProducts_status());
    sp.setInt(9, produto.getProducts_tax_class_id());
    sp.setInt(10, produto.getManufacturers_id());
    sp.setInt(11, produto.getProducts_ordered());
    sp.setInt(12, produto.getEditoras_id());
    sp.setInt(13, produto.getColeccoes_id());
    sp.setInt(14, produto.getTipoproduto_id());
    sp.setInt(15, produto.getCondicoes_id());
    sp.setString(16, produto.getProducts_code());
    sp.setInt(17, produto.getSpecials_id());
    sp.setInt(18, produto.getProducts_ano());

    sp.executeUpdate();

    ResultSet rs = sp.getGeneratedKeys();
    rs.next();

    int product_id = rs.getInt(1);
    int category_id = produto.getCategory_id();

    insereProdutoCategoria(product_id, category_id);

    productDesc.setProducts_id(product_id);
    productDesc.insereProdutoDesc(productDesc);

    for (Autor autor : autores) {
        Autor autor_novo_produto = new Autor().getAutorByX3Autor(autor.getName());

        ProdutoAutor produto_autor_novo = new ProdutoAutor(product_id, Integer.parseInt(autor_novo_produto.getCod()), "", "", 0);
        produto_autor_novo.insereProdutoAutor(produto_autor_novo);

    }

    System.err.println("INSERT => "+product_id);
}

My Databasenet Method:

public class DatabaseNET {
private static final String host = "jdbc:mysql://******"; 
private static final String database ="****";
private static final String port = "3306";
private static final String user = "****"; 
private static final String password = "****"; 

public Connection getConnection() throws ClassNotFoundException, SQLException {  

    String connect = host+":"+port+"/"+database+"?user="+user+"&password="+password;

    DriverManager.registerDriver(new com.mysql.jdbc.Driver());

    try {
        Connection conn = DriverManager.getConnection(connect);
        return conn;
    } catch (SQLException ex) {
        Logger.getLogger(DatabaseX3.class.getName()).log(Level.SEVERE, null, ex);
    }

    return null;
}

}

To generate an exception I am inserting the "ahahah" in the method:

 public void insereProdutoDesc(DatabaseNET connection, ProdutoDesc produto) throws ClassNotFoundException {

    try {
        PreparedStatement sp = connection.getConnection().prepareStatement("INSERT INTO products_description(products_id,language_id,products_name,products_description,products_url,products_viewed,products_autores_texto,products_autores_textofinal) VALUES(?,?,?,?,?,?,?,?)");
        sp.setInt(1, produto.getProducts_id());
        sp.setInt(2, produto.getLanguage_id());
        sp.setString(3, produto.getProducts_name());
        sp.setString(4, produto.getProducts_description().trim());
        sp.setString(5, produto.getProducts_url());
        sp.setString(6, "AHAHAHA");
        //sp.setInt(6, produto.getProducts_viewed());
        sp.setString(7, produto.getProducts_autores_texto());
        sp.setString(8, produto.getProducts_autores_textofinal());

        sp.executeUpdate();
    } catch (SQLException ex) {
        Logger.getLogger(ProdutoDesc.class.getName()).log(Level.SEVERE, null, ex);
    }

}
java jdbc

  • Don’t you just create an object from the other class and call out its method? Ah, in Java we call "methods" rather than "functions", because a method is a function that implicitly has a reference to the class object, well, that’s not important now, but I felt an obligation to speak.

  • Yes, it’s a method, thanks for the fix. I’m used to php. My question is: in my method, I have the Connection object, and if I want to start a transaction, I have to do: Connection.setAutoCommit(false); Commit -> Connection.commit(); and in the case of the exception rollback, but my method calls other methods with other objects of type Connection, here is my question, how to start the transaction?

  • 1

    I understand your question, I will try to write an answer already (if not answer before). I felt the freedom to transpose the content of your comment into the body of the question because I believe it encourages those who arrive now, since it is easier for those who try to understand.

  • 1

    I think the ideal is to open the Connection several times, once within each method. If you need to rollback in one of them you can throw an exception or return one false, then you do the rollback in the previous method tb. Do you think this solves? By the way, are you just looking for a way that works or what would be the best way out of all possible?

  • I create a Connection instance in each class, but in the tests I did, when an exception occurred he never rollback the transactions performed in the previous method. But I have to do Connection.setAutoCommit(false); in each method?

  • I will try to pass the connection as parameter

Show 1 more comment

1 answer

3


If you want the invoked methods to execute their SQL commands within the same transaction opened by the main method, they must use the same connection opened by the main method. You have to pass the connection as a parameter or draw some other way for the methods to share the existing connection.

We don’t know your class DatabaseNET and the exact solution depends on it. Here is a suggestion for a possible solution. Note that there are things missing, such as closing the connection.

I explained changes I made to your code in comments on the code itself:

public void insereProduto(Produto produto, ProdutoDesc productDesc, ArrayList<Autor> autores) throws ClassNotFoundException, SQLException {

    DatabaseNET connection = new DatabaseNET();

    PreparedStatement sp = connection.getConnection().prepareStatement("INSERT INTO products(products_quantity, products_image,products_price,products_date_added,products_last_modified,products_date_available,products_weight,products_status,products_tax_class_id,manufacturers_id,products_ordered,editoras_id,coleccoes_id,tipoproduto_id,condicoes_id,products_code,specials_id,products_ano) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", PreparedStatement.RETURN_GENERATED_KEYS);

    sp.setFloat(1, 99998);
    sp.setString(2, produto.getProducts_image());
    sp.setFloat(3, produto.getProducts_price());
    sp.setDate(4, produto.getProducts_date_added());
    sp.setDate(5, produto.getProducts_last_modified());
    sp.setDate(6, produto.getProducts_date_available());
    sp.setFloat(7, produto.getProducts_weight());
    sp.setBoolean(8, produto.getProducts_status());
    sp.setInt(9, produto.getProducts_tax_class_id());
    sp.setInt(10, produto.getManufacturers_id());
    sp.setInt(11, produto.getProducts_ordered());
    sp.setInt(12, produto.getEditoras_id());
    sp.setInt(13, produto.getColeccoes_id());
    sp.setInt(14, produto.getTipoproduto_id());
    sp.setInt(15, produto.getCondicoes_id());
    sp.setString(16, produto.getProducts_code());
    sp.setInt(17, produto.getSpecials_id());
    sp.setInt(18, produto.getProducts_ano());

    // inicia um try..catch para poder fazer rollback em caso de exceção
    try {
        // Inicia a transação antes do primeiro comando SQL.
        // Pelo que entendi, a transação já será aberta implicitamente pelo SGBD
        // e o setAutoCommit(false) é para que ela mantenha-se 
        // aberta depois do primeiro comando.
        connection.setAutoCommit(false); 

        sp.executeUpdate();

        ResultSet rs = sp.getGeneratedKeys();
        rs.next();

        int product_id = rs.getInt(1);
        int category_id = produto.getCategory_id();

        // Se você quer que os comandos executados dentro deste método participem
        // da mesma transação, eles devem utilizar a mesma conexão, 
        // a qual já tem uma transação aberta
        insereProdutoCategoria(connection, product_id, category_id);

        productDesc.setProducts_id(product_id);
        productDesc.insereProdutoDesc(productDesc);

        for (Autor autor : autores) {
            Autor autor_novo_produto = new Autor().getAutorByX3Autor(autor.getName());

            ProdutoAutor produto_autor_novo = new ProdutoAutor(product_id, Integer.parseInt(autor_novo_produto.getCod()), "", "", 0);

            // Mais uma vez a conexão, com a transação já aberta, 
            // sendo passada por parâmetro
            produto_autor_novo.insereProdutoAutor(connection, produto_autor_novo);

    } catch (Exception e) {
        // Talvez você precise verificar se a conexão 
        // e a transação ainda estão abertas, isso depende da implementação de DatabaseNET
        if (connection.isOpen() && connection.isTransactionOpen()) {
            connection.rollback();
        }
        // Propaga a exceção para o chamador do método
        // saber que não funcionou.
        throw e;
    }
    // Se não houve exceção, commita
    connection.commit();
}

My intention here is to help only with the exact problem you raised, so I won’t launch discussion about some things that can be improved in your design. We can discuss them in other questions if you are interested.

  • ok, thank you. I will try to follow your solution, and of course I would like to discuss the improvements

  • I put my Databasenet method in my question for you to see, and one of the methods for entering the data. I followed his example, but he continues to not rollback.

  • 1

    Incidentally connection.getConnection() creates a new connection? If yes, it will not work as you wish; it is necessary to use the same connection. Another problem is handling exceptions on insereProdutoDesc. If the SQL commands in this method fail, the exception will be cleared and will not fall on catch of insereProduto, that is, there will be no rollback. I actually believe that all of your try..catch are wrong. Why do you disappear with the exception and write in the log?

  • I saw my bug now, I’ll post my new code in a reply.

  • I’ve seen the problem now, really had this bug to create new connections but also had the type of table as Mysam and not innoDB. Thank you very much for your reply.

Browser other questions tagged java jdbc

You are not signed in. Login or sign up in order to post.