How to use

getConnection

method

in

de.mhus.lib.sql.DbPool

@Override
public DbConnection createTransactionalConnection() {
  try {
    return getConnection();
  } catch (Exception e) {
    return null;
  }
}

/**
 * Return a statement with a new connection from the pool.
 * 
 * @return x
 * @throws Exception
 */
public DbStatement getStatement() throws Exception {
  DbConnection con = pool.getConnection();
  return con.createStatement(this);
}

/**
 * Return a default connection if no connection is given for the operation with the object. If you want to
 * work with transactions use this method to return a transaction bound connection. By default a new
 * connection from the pool are used. You may overwrite the commit() or rollback() methods.
 * 
 * @param pool
 * @return x
 * @throws Exception
 */
public DbConnection getConnection(DbPool pool) throws Exception {
  DbConnection con = (DbConnection) DbTransaction.getConnection(pool);
  if (con != null) return con;
  return pool.getConnection();
}

public void dumpNodes() {
  try {
    DbConnection con = pool.getConnection();
    MProperties prop = new MProperties();
    DbStatement sta = con.createStatement("SELECT * FROM " + prefix + "_node_");
    DbResult res = sta.executeQuery(prop);
    while(res.next()) {
      System.out.println("NODE:");
      for (String name : res.getColumnNames())
        if (!name.toLowerCase().equals("content_"))
        System.out.println("  " + name + ": " + res.getString(name));
    }
  } catch (Exception e) {
    throw new RuntimeException(e);
  }
}

public void dumpCases() {
  try {
    DbConnection con = pool.getConnection();
    MProperties prop = new MProperties();
    DbStatement sta = con.createStatement("SELECT * FROM " + prefix + "_case_");
    DbResult res = sta.executeQuery(prop);
    while(res.next()) {
      System.out.println("CASE:");
      for (String name : res.getColumnNames())
        if (!name.toLowerCase().equals("content_"))
        System.out.println("  " + name + ": " + res.getString(name));
    }
  } catch (Exception e) {
    throw new RuntimeException(e);
  }
}

@Override
public boolean setNodeScope(UUID nodeId, int scope) throws IOException {
  try {
    DbConnection con = pool.getConnection();
    MProperties prop = new MProperties();
    DbStatement sta = null;
    prop.put("id", nodeId);
    prop.put("value", scope);
    sta = con.createStatement("UPDATE " + prefix + "_node_ SET scope_=$value$ WHERE id_=$id$");
    int res = sta.executeUpdate(prop);
    con.close();
    return res == 1;
  } catch (Exception e) {
    throw new IOException(e);
  }
  
}

@Override
public boolean setCasePriority(UUID caseId, int priority) throws IOException {
  try {
    DbConnection con = pool.getConnection();
    MProperties prop = new MProperties();
    DbStatement sta = null;
    prop.put("id", caseId);
    prop.put("value", priority);
    sta = con.createStatement("UPDATE " + prefix + "_case_ SET priority_=$value$ WHERE id_=$id$");
    int res = sta.executeUpdate(prop);
    con.close();
    return res == 1;
  } catch (Exception e) {
    throw new IOException(e);
  }
  
}

@Override
public boolean setNodePriority(UUID nodeId, int priority) throws IOException {
  try {
    DbConnection con = pool.getConnection();
    MProperties prop = new MProperties();
    DbStatement sta = null;
    prop.put("id", nodeId);
    prop.put("value", priority);
    sta = con.createStatement("UPDATE " + prefix + "_node_ SET priority_=$value$ WHERE id_=$id$");
    int res = sta.executeUpdate(prop);
    con.close();
    return res == 1;
  } catch (Exception e) {
    throw new IOException(e);
  }
  
}

@Override
public boolean setCaseScope(UUID caseId, int scope) throws IOException {
  try {
    DbConnection con = pool.getConnection();
    MProperties prop = new MProperties();
    DbStatement sta = null;
    prop.put("id", caseId);
    prop.put("value", scope);
    sta = con.createStatement("UPDATE " + prefix + "_case_ SET scope_=$value$ WHERE id_=$id$");
    int res = sta.executeUpdate(prop);
    con.close();
    return res == 1;
  } catch (Exception e) {
    throw new IOException(e);
  }
  
}

@Override
public Result<PCaseInfo> getCases(STATE_CASE state) throws IOException {
  try {
    DbConnection con = pool.getConnection();
    MProperties prop = new MProperties();
    DbStatement sta = null;
    if (state == null) {
      sta = con.createStatement("SELECT "+CASE_COLUMNS+" FROM " + prefix + "_case_");
    } else {
      prop.put("state", state);
      sta = con.createStatement("SELECT "+CASE_COLUMNS+" FROM " + prefix + "_case_ WHERE state_=$state$");
    }
    DbResult res = sta.executeQuery(prop);
    return new SqlResultCase(con,res);
  } catch (Exception e) {
    throw new IOException(e);
  }
}

public void init() {
  try {
    URL url = MSystem.locateResource(this, "SqlDbStorage.xml");
    DbConnection con = pool.getConnection();
    XmlConfigFile data = new XmlConfigFile(url.openStream());
    data.setString("prefix", prefix);
    pool.getDialect().createStructure(data, con, null, false);
    con.close();
  } catch (Exception e) {
    log().e(e);
  }
}

@Override
public Result<PNodeInfo> getFlowNodes(UUID caseId, STATE_NODE state) throws IOException {
  try {
    DbConnection con = pool.getConnection();
    MProperties prop = new MProperties();
    DbStatement sta = null;
    if (caseId == null && state == null) {
      sta = con.createStatement("SELECT "+NODE_COLUMNS+" FROM " + prefix + "_node_");
    } else 
    if (caseId == null) {
      prop.put("state", state);
      sta = con.createStatement("SELECT "+NODE_COLUMNS+" FROM " + prefix + "_node_ WHERE state_=$state$");
    } else
    if (state == null) {
      prop.setString("case", caseId.toString());
      sta = con.createStatement("SELECT "+NODE_COLUMNS+" FROM " + prefix + "_node_ WHERE case_=$case$");
    } else {
      prop.setString("case", caseId.toString());
      prop.put("state", state);
      sta = con.createStatement("SELECT "+NODE_COLUMNS+" FROM " + prefix + "_node_ WHERE state_=$state$ and case_=$case$");
    }
    DbResult res = sta.executeQuery(prop);
    return new SqlResultNode(con,res);
  } catch (Exception e) {
    throw new IOException(e);
  }
}

@Override
public void deleteCaseAndFlowNodes(UUID id) throws IOException {
  try {
    DbConnection con = pool.getConnection();
    MProperties prop = new MProperties();
    prop.put("id", id);
    {
      DbStatement sta = con.createStatement("DELETE FROM " + prefix + "_case_ WHERE id_=$id$");
      sta.execute(prop);
    }
    {
      DbStatement sta = con.createStatement("DELETE FROM " + prefix + "_node_ WHERE case_=$id$");
      sta.execute(prop);
    }
    con.commit();
    con.close();
  } catch (Exception e) {
    throw new IOException(e);
  }
}

@Override
public PEngine loadEngine() throws IOException, NotFoundException {
  PEngine engine = null;
  try {
    DbConnection con = pool.getConnection();
    MProperties prop = new MProperties();
    prop.put("id", "engine");
    DbStatement sta = con.createStatement("SELECT content_ FROM " + prefix + "_engine_ WHERE id_=$id$");
    DbResult res = sta.executeQuery(prop);
    if (res.next()) {
      InputStream in = res.getBinaryStream("content_");
      engine = new PEngine();
      engine.readExternal(new ObjectInputStream(in));
    }
    res.close();
    con.close();
  } catch (Exception e) {
    throw new IOException(e);
  }
  if (engine == null) throw new NotFoundException("engine");
  return engine;
}

@Override
public Result<PNodeInfo> getScheduledFlowNodes(STATE_NODE state, long scheduled) throws IOException {
  DbConnection con = null;
  try {
    con = pool.getConnection();
    MProperties prop = new MProperties();
    DbStatement sta = null;
    if (state == null) {
      prop.setLong("scheduled", scheduled);
      sta = con.createStatement("SELECT "+NODE_COLUMNS+" FROM " + prefix + "_node_ WHERE scheduled_ <= $scheduled$");
    } else {
      prop.setLong("scheduled", scheduled);
      prop.put("state", state);
      sta = con.createStatement("SELECT "+NODE_COLUMNS+" FROM " + prefix + "_node_ WHERE state_=$state$ and scheduled_ <= $scheduled$");
    }
    DbResult res = sta.executeQuery(prop);
    return new SqlResultNode(con,res);
  } catch (Exception e) {
    try {
      if (con != null) con.close();
    } catch (Exception e2) {}
    throw new IOException(e);
  }
}

@Override
public PCase loadCase(UUID id) throws IOException, NotFoundException {
  PCase caze = null;
  try {
    DbConnection con = pool.getConnection();
    MProperties prop = new MProperties();
    prop.put("id", id);
    DbStatement sta = con.createStatement("SELECT content_ FROM " + prefix + "_case_ WHERE id_=$id$");
    DbResult res = sta.executeQuery(prop);
    if (res.next()) {
      InputStream in = res.getBinaryStream("content_");
      caze = new PCase();
      caze.readExternal(new ObjectInputStream(in));
    }
    res.close();
    con.close();
  } catch (Exception e) {
    throw new IOException(e);
  }
  if (caze == null) throw new NotFoundException("case",id);
  return caze;
}

@Override
public Result<PNodeInfo> getSignalFlowNodes(STATE_NODE state, String signal) throws IOException {
  try {
    DbConnection con = pool.getConnection();
    MProperties prop = new MProperties();
    DbStatement sta = null;
    if (state == null) {
      prop.setString("signal", "%" + PNode.getSignalAsString(signal) + "%");
      sta = con.createStatement("SELECT "+NODE_COLUMNS+" FROM " + prefix + "_node_ WHERE signal_ like $signal$");
    } else {
      prop.setString("signal", "%" + PNode.getSignalAsString(signal) + "%");
      prop.put("state", state);
      sta = con.createStatement("SELECT "+NODE_COLUMNS+" FROM " + prefix + "_node_ WHERE state_=$state$ and signal_ like $signal$");
    }
    DbResult res = sta.executeQuery(prop);
    return new SqlResultNode(con,res);
  } catch (Exception e) {
    throw new IOException(e);
  }
}

@Override
public PCaseInfo loadCaseInfo(UUID caseId) throws IOException {
  try {
    DbConnection con = pool.getConnection();
    MProperties prop = new MProperties();
    DbStatement sta = null;
    prop.put("id", caseId);
    sta = con.createStatement("SELECT "+CASE_COLUMNS+" FROM " + prefix + "_case_ WHERE id_=$id$");
    DbResult res = sta.executeQuery(prop);
    if (!res.next()) {
      res.close();
      con.close();
      return null;
    }
    PCaseInfo out = newPCase(res);
    res.close();
    con.close();
    return out;
  } catch (Exception e) {
    throw new IOException(e);
  }
}

@Override
public PNode loadFlowNode(UUID id) throws IOException, NotFoundException {
  PNode node = null;
  try {
    DbConnection con = pool.getConnection();
    MProperties prop = new MProperties();
    prop.put("id", id);
    DbStatement sta = con.createStatement("SELECT content_ FROM " + prefix + "_node_ WHERE id_=$id$");
    DbResult res = sta.executeQuery(prop);
    if (res.next()) {
      InputStream in = res.getBinaryStream("content_");
      node = new PNode();
      try {
        node.readExternal(new ObjectInputStream(in));
      } catch (java.io.EOFException eofe) {
        log().w(node,eofe); // most because of extended parameters
      }
    }
    res.close();
    con.close();
  } catch (Exception e) {
    throw new IOException(e);
  }
  if (node == null) throw new NotFoundException("node",id);
  return node;
}

@Override
public PNodeInfo loadFlowNodeInfo(UUID nodeId) throws IOException {
  try {
    DbConnection con = pool.getConnection();
    MProperties prop = new MProperties();
    DbStatement sta = null;
    prop.put("id", nodeId);
    sta = con.createStatement("SELECT "+NODE_COLUMNS+" FROM " + prefix + "_node_ WHERE id_=$id$");
    DbResult res = sta.executeQuery(prop);
    if (!res.next()) {
      res.close();
      con.close();
      return null;
    }
    PNodeInfo out = newPNode(res);
    res.close();
    con.close();
    return out;
  } catch (Exception e) {
    throw new IOException(e);
  }
}