StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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.</p> <p>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. :-)</p> <p><strong>Database provider class:</strong></p> <pre><code>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; } } </code></pre> <p>Database object class:</p> <pre><code>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); } } </code></pre> <p>Example class:</p> <pre><code>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"); } } </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload