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);
}
}
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.
– Math
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?
– Ricardo Costa
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.
– Math
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?
– Math
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?
– Ricardo Costa
I will try to pass the connection as parameter
– Ricardo Costa