can we create a common database class in java

Question

i want to know if we can create a common database class same like we create a connection class and just call getConnection when we need connection to be established.
Basically, i want a database manager class which can handle database operation irrespective of tablename, columncount,etc.
tablename, columnname, values to be inserted would be passed as parameters from servlet. that way, i can reduce duplication of code. m tryin to make a simple mvc application using jsp-servlets. my database is mysql. i dont know struts, spring, hibernate.

For Example, servlet code will call(databaseManager is the class name.) :

int count=databaseManager.getCount("tableName", "columnName", "value");

and in databaseManager, there will be a function -

public static int getCount(String tableName, String[] arrC, objectArray[] arrV)
{}

similarly, for other functions. i googled and found out that it could be done using metadata. but i dont know how to use it. it would be helpful if u could post code of one function for similar approach.

Solution

Yes, sure you can. I have done something similar (but not the same) and there can be many approaches. I think you should google more, I'm sure, that there are lot of open source applications for database management/database clients. Try to get inspiration there.

Okay, here is some code for inspiration. It is not totally generic, or what are you looking for, but I think, this could lead you somewhere. If not, throw the stone. :-)

Database provider class:

import java.lang.reflect.Constructor;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.beanutils.DynaProperty;

public class DatabaseProvider<T extends DatabaseObject> {
    private static DatabaseProvider databaseProvider;

    private static String connectionString = "";
    private static String password = "";
    private static String username = "";

    private static boolean initialized = true;

    public DatabaseProvider(){ }



    public static void initDatabaseProvider() {
        try {
            DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        }
        catch(SQLException e){
            initialized = false;
            e.printStackTrace();
        }

        connectionString = "XXX";
        username = "XXX";
        password = "XXX";
    }

    public List<T> performSimpleSelectQuery(String table, String columns, String where, Class targetObj) throws SQLException {
        if(!initialized) return null;
        List<T> results = new ArrayList<T>();
        Constructor ct;
        DatabaseObject dbo;
        try {
            ct = targetObj.getConstructor(null);
            dbo = (DatabaseObject)ct.newInstance(null);
        }
        catch(Exception e){
            e.printStackTrace();
            return null;
        }

        String[] cols = columns.split(",");
        String[] properties = new String[cols.length];
        for(int i = 0; i < cols.length; i++){
            cols[i] = cols[i].trim();
            properties[i] = dbo.getMappingFromColumnName(cols[i]);
        }
        Connection conn = DriverManager.getConnection(connectionString, username, password);
        PreparedStatement pst = conn.prepareStatement("SELECT " + columns + " FROM " + table + (where.equals("") ? "" : " WHERE " + where));
        pst.execute();
        ResultSet rs = pst.getResultSet();
        while(rs.next()){
            try {
                dbo = (DatabaseObject)ct.newInstance(null);
                for(int i = 0; i < cols.length; i++){
                    BeanUtils.setProperty(dbo, properties[i], rs.getObject(cols[i]));
                }
                results.add((T)dbo);
            }
            catch(Exception e){
                e.printStackTrace();
                rs.close();
                pst.close();
                conn.close();
                return null;
            }
        }
        rs.close();
        pst.close();
        conn.close();
        return results;

    }

    public int performInsert(String columns, String values, String table) throws SQLException {
        String sqlInsert = "INSERT INTO " + table + " (" + columns + ") VALUES (" + values + ")"; 
        Connection conn = DriverManager.getConnection(connectionString, username, password);
        PreparedStatement pst = conn.prepareStatement(sqlInsert);
        int toReturn = 0;
        try {
            toReturn = pst.executeUpdate();
        }
        catch(Exception e){
            e.printStackTrace();
            pst.close();
            conn.close();
            return toReturn;
        }
        pst.close();
        conn.close();
        return toReturn;
    }

}

Database object class:

import java.util.HashMap;
public abstract class DatabaseObject {
    protected HashMap<String, String> dbToBeanMapping = new HashMap<String, String>();

    public DatabaseObject() {
        initialize();
    }

    protected abstract void initialize();

    public String getMappingFromColumnName(String columnName) {
        return dbToBeanMapping.get(columnName);
    }
}

Example class:

public class CounterParty extends DatabaseObject {
    private String name;

    private int instrument;
    private int partyId;

    public int getPartyId() {
        return partyId;
    }

    public void setPartyId(int partyId) {
        this.partyId = partyId;
    }


    public CounterParty(){}

    public int getInstrument() {
        return instrument;
    }

    public void setInstrument(int instrument) {
        this.instrument = instrument;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    protected void initialize() {
        this.dbToBeanMapping.put("company_name", "name");
        this.dbToBeanMapping.put("party_id", "partyId");
        this.dbToBeanMapping.put("inst_id", "instrument");

    }

}

Comments

[Lukas Eder]

That's like saying: "Yes there are solutions, but I won't tell you" ;-)

[Admin]

i would be thankful if u can post some link where i can get solution to this problem

[Ondřej Holman]

Why would I do that. :-) I think that akshay was not searching enough, because otherwise, he would find it. :-)

[Nishant]

not downvoting, but if you have privilege to comment, you should use it.

[Admin]

m only getting solutions with hibernate or springs involved in it. m not comfortable with those. i want a solution in simple java class

[Ondřej Holman]

I will delete the post, I didn't realize, I could send it as comment. Sorry for that.

[Lukas Eder]

I hope you're aware of SQL injection?? The OP asks for a "generic framework" for passing data from a servlet to the database!