CODEWITHSUNDEEP
javadatabaseoracle-database
Ali Bassam
Ali Bassam

Reputation: 9969

Do i have to connect to database for each query?

I use the following codes to connect to Oracle's database:

Connection conn = null;
Statement stmt = null;
ResultSet rset = null;
String jdbc_url = "jdbc:oracle:thin:hr/hr@localhost:1521:XE";
String query = "";
try {
   DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
   conn = DriverManager.getConnection(jdbc_url);
   stmt = conn.createStatement();
   query = "select username " + " from users ";
   rset = stmt.executeQuery(query);

   // my codes

} catch (SQLException sqle) {
   System.out.println("result error, " + e.getMessage());
} catch (NumberFormatException nfe) {

} finally {
   try {
      rset.close();
      stmt.close();
      conn.close();

   } catch (Exception e) {
      System.out.println("Error in closing " + e.getMessage());
   }
}

Do I have to use this code in every action that I make for every query that I want? Making the connection and terminating it? Everytime?

Upvotes: 2

Views: 3959

Answers (3)

Paul Vargas
Paul Vargas

Reputation: 42040

If your application runs on a server such as Tomcat, GlassFish, best way is to use JNDI to get a connection. Otherwise, since you are using the Oracle JDBC driver, you could use the class OracleDataSource.

For the getting the Connection object, you could implement the Singleton design pattern. A simple way is using Java enumerations.

enum DBEnum {
    ORACLE_XE {
        private DataSource ds = initDataSource();
        private DataSource initDataSource() {
            try {
                OracleDataSource ds = new OracleDataSource();
                ds.setDriverType("thin");
                ds.setServerName("localhost");
                ds.setPortNumber(1521);
                ds.setDatabaseName("XE"); // Oracle SID
                ds.setUser("Herong");
                ds.setPassword("TopSecret");
                return ds;
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
        }
        @Override
        public Connection getConnection() throws SQLException {
            return ds.getConnection();
        }
    };
    public abstract Connection getConnection() throws SQLException;
}

And with this you would change your code:

Connection conn = null;
Statement stmt = null;
ResultSet rset = null;
String query = "";
try {
   conn = DBEnum.ORACLE_XE.getConnection();
   stmt = conn.createStatement();
   query = "select username " + " from users ";
   rset = stmt.executeQuery(query);

   // my codes

} catch (SQLException sqle) {
   System.out.println("result error, " + e.getMessage());
} catch (NumberFormatException nfe) {

} finally {
   try {
      rset.close();
      stmt.close();
      conn.close();

   } catch (Exception e) {
      System.out.println("Error in closing " + e.getMessage());
   }
}

Upvotes: 0

Puce
Puce

Reputation: 38142

I recommend to use pooled connections/ DataSource.

One DataSource instance is one unit of work and should be closed when the unit of work is finished (but note that it won't close the db connection if you're using pooled connections).

Also consider to use the higher-level JPA.

Upvotes: 0

juergen d
juergen d

Reputation: 204854

You don't have to connect every time. Just make sure you close the resultset and the statement every time. See this answer as example for a good DB wrapper class.

You can make your Connection a class variable that is initalized in the constructor. Something like this:

public class DbWrapper {
    private Connection conn = null;
    String jdbc_url="jdbc:oracle:thin:hr/hr@localhost:1521:XE";

    public DbWrapper() {
        conn=DriverManager.getConnection(jdbc_url);
    }

    public Arraylist<User> getUsers() {
         ...
    }
}

Upvotes: 3

Related Questions