java 我们可以在java中创建一个通用的数据库类吗

can we create a common database class in java

提问by akshay

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.

我想知道我们是否可以像创建连接类一样创建一个通用的数据库类，并在需要建立连接时调用 getConnection 。
基本上，我想要一个数据库管理器类，它可以处理数据库操作，而不管表名、列数等。
要插入的表名、列名、值将作为参数从 servlet 传递。这样，我可以减少代码重复。我尝试使用 jsp-servlet 制作一个简单的 mvc 应用程序。我的数据库是mysql。我不知道struts，spring，hibernate。

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

例如，servlet 代码将调用（databaseManager是类名。）：

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

and in databaseManager, there will be a function -

并且在databaseManager，会有一个函数——

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.

同样，对于其他功能。我用谷歌搜索并发现它可以使用元数据来完成。但我不知道如何使用它。如果您可以为类似的方法发布一个函数的代码，那将会很有帮助。

回答by

Check DbUtils componentof Apache Commons. Also there are examples provided.

检查Apache Commons 的DbUtils 组件。还提供了示例。

回答by Ond?ej Holman

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");

    }

}