Reuseable Java Database Connection Class.
Database Connection class. Which is reuseable. Note the select statement return a statement instead of a resultset, as closing the connection will cause the resultset to be closed as well. Without closing the connection, Mysql will throws error 'too many connections made' when user keep pressing refresh button.
import java.sql.*;
public class DBConn {
String m_Driver = "com.mysql.jdbc.Driver";
Connection connection = null;
Statement statement = null;
ResultSet rset = null;
String dbPassword = "test";
String dbUsername = "root";
String dbName = "testdb";
String connectionURL = "jdbc:mysql://localhost:3306/" + dbName + "?useSSL=false";
public DBConn() {
try {
Class.forName(m_Driver);
} catch (ClassNotFoundException ex) {
ex.printStackTrace();
System.out.println("Error ----------------");
}
//System.out.println("DBConn Initiated... ");
}
public Statement getConnectionStatemet(){
try {
connection = DriverManager.getConnection(connectionURL, dbUsername, dbPassword);
statement = connection.createStatement();
} catch (SQLException ex) {
ex.printStackTrace();
System.out.println("Error Getting statement -- DBConn");
}
return statement;
}
public int updateData(String aQuery) {
int val = 0; // if 0 record not updated
try {
connection = DriverManager.getConnection(connectionURL, dbUsername, dbPassword);
statement = connection.createStatement();
val = statement.executeUpdate(aQuery,Statement.RETURN_GENERATED_KEYS);
if (val > 0) {
rset = statement.getGeneratedKeys();
if (rset.next()){
val = rset.getInt(1);
}
}
System.out.println(aQuery);
} catch (SQLException s) {
System.out.println(s.getMessage());
System.out.println("SQL statement is update error DBConn!");
System.out.println(aQuery);
}
finally {
closeResultSet();
closeStatement();
closeConnection();
}
return val;
}
public int deleteData(String aQuery) {
int val = 0; // if 0 record not updated
try {
connection = DriverManager.getConnection(connectionURL, dbUsername, dbPassword);
statement = connection.createStatement();
val = statement.executeUpdate(aQuery);
//System.out.println(aQuery);
} catch (SQLException s) {
//System.out.println(s);
System.out.println("--- Error deleting data");
//System.out.println(aQuery);
}
finally {
closeStatement();
closeConnection();
}
return val;
}
public void closeResultSet() {
try {
if (rset != null)
rset.close();
} catch (SQLException ex) {
ex.printStackTrace();
System.out.println("**** Error closing result set");
}
}
public void closeStatement() {
try {
if (statement != null)
statement.close();
} catch (SQLException ex) {
ex.printStackTrace();
System.out.println("**** Error closing statement");
}
}
public void closeConnection() {
try {
if (connection != null)
connection.close();
} catch (SQLException ex) {
ex.printStackTrace();
System.out.println("**** Error closing connection");
}
}
}
Comments
Post a Comment