The correct way to update/query from DB

Question

I am abit new with working with DatatBases from Java, and I have been wondering if I am working in a correct way. In my code all the DB interface is done within one class called DataAccess, an example of my code:

Please note that I opened a connection (connBlng) before entering the function isValidOperator().

Is this the correct way to work or should I open and close a connection everytime I need to access the DB?

if(da.StartBlngConnection() == null)
    return "ERROR"
DataAccess da = new DataAccess();
da.isValidOperator("123")       

//this is code from DataAccess Class
public Boolean isValidOperator(String dn) {
    System.out.println( npgReqID + " - " + LOG_TAG + "inside isValidOperator : " + dn);
    PreparedStatement prepStmt = null;
    ResultSet queryResult = null;
    try{
        prepStmt = connBlng.prepareStatement("select network_id, network_identifier from operators where network_identifier = ?"); 
        prepStmt.setString(1, dn);
        queryResult = prepStmt.executeQuery();
        if (queryResult.next()) {
            return true;
        }
    }catch(Exception e){
        System.out.println(npgReqID + " - "  + e);
        DBLog("", new Date(),"" , npgReqID , "" ,"" , MakeSureNotOutOfRange(GetStackTrace(e),4000), "" , "");
        return false;
    } finally{
        closeStatmentandRS(queryResult, PreparedStatement);
    }

    return false;
}

In finally block you are only closing `queryResult`, `PreparedStatement`. You must close connBlng(Connection), if not number of open connections will exceed beyond the specified limit. Or you should be using some kind connection pooling. — Jacob, Jul 31 '13 at 08:25
I am opening a single connection in the start of my program and i am making sure it is closed at the end — susparsy, Jul 31 '13 at 08:28
If your `connection` is being closed after execution of program, then it is fine. — Jacob, Jul 31 '13 at 08:30

score 0 · Answer 1 · edited May 23 '17 at 12:28

0

JDBC plain is not a very easy to use API. Maybe you can take a look at Dalesbread https://github.com/Blackrush/Dalesbred which is a lightwight JDBC wrapper. Here is a discussion about JDBC wrappers in common: simple jdbc wrapper.

I would not suggest to close the DB connection all the time, you should use pooling for that. Normally creating a DB connection is expensive.

edited May 23 '17 at 12:28

Community

1
1

answered Jul 31 '13 at 08:21

Sorontur

500
4
15

The correct way to update/query from DB

1 Answers1