I am working on a project in Java which includes a lot of database related tasks. For instance, I would have to insert, update, retrieve data so many times within the application. To use code reusability, I've just written this abstraction for my database class. I'm planing to call its methods where ever its needed to perform database related tasks within the code. Please review my code. Are there any bad practices?
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.teamincredibles.settings.Settings;
public class Database {
private Connection connection;
public void connect() {
try {
Class.forName(Settings.getDatabaseDriver());
connection = DriverManager.getConnection(Settings.getJdbcUrl(), Settings.getDBUserName(), Settings.getDBPassword());
} catch (Exception e) {
e.printStackTrace();
}
}
public boolean prepareStatement(String query, String choice1) {
boolean flag = false;
try {
PreparedStatement ps = connection.prepareStatement(query);
ps.setString(1, choice1);
ResultSet rs = ps.executeQuery();
flag = rs.next();
} catch (SQLException e) {
e.printStackTrace();
}
return flag;
}
public boolean prepareStatement(String query, String choice1, String choice2) {
boolean flag = false;
try {
PreparedStatement ps = connection.prepareStatement(query);
ps.setString(1, choice1);
ps.setString(2, choice2);
ResultSet rs = ps.executeQuery();
flag = rs.next();
} catch (SQLException e) {
e.printStackTrace();
}
return flag;
}
public void executeUpdate(String query) {
try {
Statement statement = connection.createStatement();
statement.executeUpdate(query);
} catch (SQLException e) {
e.printStackTrace();
}
}
public boolean execute(String query) {
boolean result = false;
try {
Statement statement = connection.createStatement();
result = statement.execute(query);
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
public ResultSet executeQuery(String query) {
ResultSet result = null;
try {
Statement statement = connection.createStatement();
result = statement.executeQuery(query);
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
public void disconnect() {
try {
if (connection != null) {
connection.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
EDIT:
Thanks all for suggesting me such wonderful solutions. I've read all the solutions carefully and I've considered the following Updated code suggested by @shree. P.S I've updated it a bit.
package com.teamincredibles.database;
import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import com.teamincredibles.settings.Settings;
public class DBUtil {
private PreparedStatement prepStatement = null;
private ResultSet resultSet = null;
private Connection connection = null;
private Statement statement=null;
public DBUtil() throws SQLException, ClassNotFoundException {
connect();
}
public boolean isConnectionClosed() throws SQLException {
return null==connection || connection.isClosed();
}
public void connect() throws ClassNotFoundException, SQLException {
Class.forName(Settings.getDatabaseDriver());
connection = DriverManager.getConnection(Settings.getJdbcUrl(), Settings.getDBUserName(), Settings.getDBPassword());
}
public PreparedStatement getPreparedStatement(String sql) throws SQLException {
return this.connection.prepareStatement(sql);
}
public Map<String,String> executeWithPreparedStatement(String query, String ...values) throws SQLException {
Map<String,String> records = new HashMap<String,String>();
try {
prepStatement = getPreparedStatement(query);
int noOfParameters = values.length;
for(int i=0;i<noOfParameters;i++) {
prepStatement.setString(1, values[i]);
}
resultSet = prepStatement.executeQuery();
ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
while(resultSet.next()){
for(int i=1;i<=resultSetMetaData.getColumnCount();i++){
records.put(resultSetMetaData.getColumnName(i),resultSet.getString(resultSetMetaData.getCo lumnName(i)));
}
}
} finally {
releaseResources();
}
return records;
}
public void insertWithPreparedStatement(String query, String ...values) throws SQLException{
try{
prepStatement = getPreparedStatement(query);
int noOfParameters = values.length;
for(int i=0;i<noOfParameters;i++) {
prepStatement.setString(i+1, values[i]);
}
prepStatement.executeUpdate();
} finally{
releaseResources();
}
}
private void releaseResources() throws SQLException {
if(null!=resultSet){
resultSet.close();
}
if(null!=prepStatement){
prepStatement.close();
}
if(null!=statement){
statement.close();
}
}
public int executeUpdate(String query) throws SQLException {
try {
statement = connection.createStatement();
return statement.executeUpdate(query);
}finally {
releaseResources();
}
}
public boolean executeDDL(String query) throws SQLException {
try {
statement = connection.createStatement();
return statement.execute(query);
}finally {
releaseResources();
}
}
public void disconnect() throws SQLException {
if (!isConnectionClosed()) {
connection.close();
}
}
public static void main(String args[]){
try {
DBUtil db = new DBUtil();
/*String query = "insert into users(id, name) values(?, ?)";
String[] values = {"6", "Bilal Ameer"};
db.insertIntoDatabase(query, values);
System.out.println("Success");*/
String query = "select id, name from users where id = ?";
String[] values = {"1"};
Map<String, String> records = db.executeWithPreparedStatement(query, values);
System.out.println("ID\tNAME");
for(int i=0; i<records.size(); i++){
System.out.println(records.get("id")+"\t"+records.get("name"));
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
}
Please review this code and suggest me if there are any code Smells in the code? Please suggest me a code to select all the records from database, The method executeWithPreparedStatement(String query, String ...values) in this code is used to get records from the database where conditions are provided in values array, what it would be without conditions?
