Creating a connection handling class – JAVA

Creating a connection handling class – JAVA

When you are programming in JAVA, or any object oriented programming language you will need to sort everything some how in order to make the programming mutch easier and pleasant for you.

In this tutorial you will learn how to create a class that will handle our database connections and queries – that way you will always know where to look at when something is wrong and all the database related operations will be at one place.

In the class that you are going to implement you will notice 3 parts:

• The initialization of the datasource.
• The query methods
• The closing methods

Let’s create our class!

Connector.java

[code language=”java”]
public class Connector{

// Creating a connection handling class – JAVA

private final DataSource dataSource;

private Connector(final DataSource dataSource) {
this.dataSource = dataSource;
}

public static Connectorinstance(final DataSource dataSource) {
return new Connector(dataSource);
}

private static void closeResultSet(final ResultSet rs) {
try {
if (rs != null)
rs.close();
} catch (final SQLException e) {}

}

private static void closeStatement(final Statement st) {
try {
if (st != null)
st.close();
} catch (final SQLException e) {
}
}

private static void closeConnection(final Connection conn) {
try {
if (conn != null)
conn.close();
} catch (final SQLException e) {
}
}

// Creating a connection handling class – JAVA

}

[/code]

In the code above you already implemented 2 of the 3 parts, but how do we run a query?

Really easy! Here is how:

[code language=”java”]
// Creating a connection handling class – JAVA

private static final String SELECT_QUERY = "SELECT id FROM  your_table WHERE name = ?";

public int getId(String name) throws Exception {
Connection conn = null;
PreparedStatement stmnt = null;
ResultSet rs = null;
try {
conn = dataSource.getConnection();
stmnt = conn.prepareStatement(SELECT_QUERY );
stmnt.setString(1, name);

stmnt.execute();
rs = stmnt.getResultSet();
if (rs.next()) {
return Integer.parseInt(rs.getString(1));
} else {
return 0;
}
} finally {
closeResultSet(rs);
closeStatement(stmnt);
closeConnection(conn);
}
}

// Creating a connection handling class – JAVA

[/code]

Now you have a full method that maintains a database connection and executes some queries, but how can we get an instance of it and get the query result?

Here is how to implement it to your servlet:

[code language=”java”]
private DataSource dataSource;

public void init() throws ServletException {
try {
connector= Connector.instance(dataSource);
} catch (final Exception e) {
e.printStackTrace();
}
}

// Run your query

int id = connector.getId("tes");

//Creating a connection handling class – JAVA

[/code]

Using the above class and implementation methods, you can run a lot of queries simultaneously, without bothering of the connections, because your Connector class already takes care of everything.

Now you know how to run your code smoothly and how to organize everything in one class.