Home

Welcome

Application

The checked in unit tests are executed using PostgreSQL but the same product code (with somewhat different unit test code) has been used with MS SQL and H2 as well. So I am hoping that it would work with the large manjority of databases.

I have added this project to maven (https://mvnrepository.com/artifact/net.sf.dyndblayer/dyndblayer) so you can get version 0.2.2 by this snippet in your pom.xml:

<dependency>
    <groupId>net.sf.dyndblayer</groupId>
    <artifactId>dyndblayer</artifactId>
    <version>0.2.2</version>
</dependency>

Dependencies

Min jdk version is 8. DynDBayer has no dependencies, but you'll need to add the relevant jdbc driver to your project as well.

Example

Below is a snippet that shows the idea, you can find the same code in the Demo project. For further uses please see the unit tests.

public class Demo {

 // This needs to have a public no argument constructor
 // Field names should match column names
 // When datatype is nullable use nullable java type
 // When using nested class make it static
 public static class TestTblEntry
 {
     public int id;
     public int col1;
     public String col2;
 }

 public static void main(String[] args) throws Exception {

  // DynDBL prints logs. You can change this by creating your own 
  // logger object and injecting that via calling DynDBLLog.setLoggerImpl(). 
  // Then You can redirect the logs into your own logger whatever you use.

  // Adjust these to match your local PC
  String dbhost = "localhost";
  int dbport=5432;
  String dbname = "dyndbltestdb";
  String dbusn="postgres";
  String dbpwd="1";

  // We need a connection pool. In a proper app this would typically be
  // created at startup and closed during shutdown, so this is long lived.
  // DynDBL instances are typically short lived: get one from the pool,
  // do the job, close it.
  try (DynDBLConnPoolImpl pool = new DynDBLConnPoolImpl(3, 10)) {

   // Here we open the master db - we want to create a database first, hence
   // the null for dbname.
   try (DynDBL db = pool.openDB(generatePSQLConnectionString(
          dbhost, dbport, null, dbusn, dbpwd))) {
    db.executeNonQuery("drop database if exists " + dbname);
    db.executeNonQuery("create database " + dbname);
   }

   // Now we can open the db we just created above
   try (DynDBL db = pool.openDB(generatePSQLConnectionString(
          dbhost, dbport, dbname, dbusn, dbpwd))) {

    // We create a table and insert a row. Also showing how to do it in a 
    // transaction.

    // If an exception is thrown then the close() function gets called in 
    // db which would call rollback so no need to worry about calling that.
    db.beginTransaction();
    db.executeNonQuery(
      "create table testtbl (id serial primary key, col1 int, col2 text)");
    db.executeNonQuery(
      "insert into testtbl (col1, col2) values (?,?)", 999, "999");
    db.commit();

    // We query the data back. One feature of DynDBL is that it can return
    // the result of a query in a list of objects,
    // the columns in the query are matched to the fields of the class.
    TestTblEntry[] resArr = new DynDBLQuery().executeQuery(db,
      TestTblEntry.class, 
      "select" + DynDBLUtils.getColumnNames(TestTblEntry.class) + "from testtbl");

    // See that we did get something back.
    System.out.println("Result size: " + resArr.length);
    System.out.println("Result entry 0 col2 : " + 
    resArr[0].col2);
   }
  }
 }

 static String generatePSQLConnectionString(
     String host, Integer port, String dbName,
     String dbusn, String dbpwd)
 {
  if (host == null || host.isEmpty())
  {
   return null;
  }

  String ret = "jdbc:postgresql://" + host;
  if (port != null)
  {
   ret += ":" + port;
  }

  if (dbName != null && !dbName.isEmpty())
  {
   ret += "/" + dbName;
  }
  else
  {
   ret += "/postgres";
  }

  if (dbusn != null && !dbusn.isEmpty())
  {
   ret += "?user=" + dbusn;
  }
  if (dbpwd != null && !dbpwd.isEmpty())
  {
   ret += "&password=" + dbpwd;
  }
  return ret;
 }

}