CODEWITHSUNDEEP
javasqloracle-databaseoop
Lestat
Lestat

Reputation: 71

Do I have to Get Connection and Close Connection every time I insert into the database?

I just want to know if it is really necessary, in my java application, to call getConnection/closeConnection every time I want to insert into the database, or could I authenticate User/Pass at the beginning of the application and never call Connection again?

//Conn class
private static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
private static final String URL = "jdbc:oracle:thin:@localhost:1521:XE";
private static final String USER = "hr";
private static final String PASS = "hr";

//Connect
public static Connection getConnection(){
    try {
        Class.forName(DRIVER);
        return DriverManager.getConnection(URL, USER, PASS);
    } catch (ClassNotFoundException | SQLException ex) {
        throw new RuntimeException("Error: RuntimeException",ex);
    }
}

//Close
public static void closeConnection(Connection con){
        try {
            if(con!=null){
            con.close();
        } 
    } catch (SQLException ex) {
            Logger.getLogger(ConnectionAcademia.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

//DAO Class
//Insert product
public void createProduct(Product p){

    Connection con = ConnectionGym.getConnection();
    PreparedStatement stmt = null;

    try {

        stmt = con.prepareStatement("INSERT INTO tb_product(key_product, name, price, status) "
                                  + "VALUES(?,?,?,?)");
        stmt.setInt(1, p.getKey_Product());
        stmt.setString(2, p.getName());
        stmt.setDouble(4, p.getPrice());
        stmt.setBoolean(6, p.getStatus());

        stmt.executeUpdate();

        JOptionPane.showMessageDialog(null, "Success!");
    } catch (SQLException ex) {
        JOptionPane.showMessageDialog(null, "Error: " + ex);
    } finally{
        ConnectionAcademia.closeConnection(con, stmt);
    }
}

Upvotes: 2

Views: 848

Answers (1)

StuPointerException
StuPointerException

Reputation: 7267

You certainly don't want to be doing this. The overhead in creating a connection and closing it is massive compared to the time required to execute your statement. You'll also lose the benefit of preparing the statement, since the prepared statement is linked to the database connection for later reuse.

If you're not working in a multi-threaded environment (such as a web application) then having a single connection is perfectly reasonable, create the connection when the app starts and then close in once the app finishes.

If you require concurrent access then you should look into configuring a connection pool, to prevent blocking IO.

As mentioned in the comment above, if you're intending to do lots of a Database updates in your application you should consider using an ORM such as Hibernate, it'll make your life easier in the long run.

Upvotes: 1

Related Questions