@Override
 public void updateBook(BookModel bookModel) {
   create
       .update(BOOK)
       .set(BOOK.TITLE, bookModel.getTitle())
       .set(BOOK.DATE_PUBLISH, bookModel.getDatePublish())
       .set(BOOK.SHORT_DESCRIPTION, bookModel.getShortDescription())
       .where(BOOK.ID_BOOK.equal(bookModel.getIdBook()))
       .execute();
   List<Integer> existsAuthors =
       create
           .select(AUTHOR_BOOK.ID_AUTHOR)
           .from(AUTHOR_BOOK)
           .where(AUTHOR_BOOK.ID_BOOK.equal(bookModel.getIdBook()))
           .fetchInto(Integer.class);
   logger.debug("Existing authors: " + existsAuthors);
   List<Integer> newAuthors = new ArrayList<>();
   List<Integer> authors = new ArrayList<>();
   /** */
   for (String idAuthor : bookModel.getAuthors()) {
     int id = Integer.valueOf(idAuthor);
     authors.add(id);
     if (!existsAuthors.contains(id)) {
       newAuthors.add(id);
     }
   }
   existsAuthors.removeAll(authors);
   logger.debug("New authors: " + newAuthors);
   if (!newAuthors.isEmpty()) {
     InsertValuesStep2<AuthorBookRecord, Integer, Integer> insertStep =
         create.insertInto(AUTHOR_BOOK, AUTHOR_BOOK.ID_AUTHOR, AUTHOR_BOOK.ID_BOOK);
     InsertValuesStep2<AuthorBookRecord, Integer, Integer> valuesStep = null;
     for (Integer idAuthor : newAuthors) {
       valuesStep = insertStep.values(idAuthor, bookModel.getIdBook());
     }
     valuesStep.execute();
   }
   logger.debug("Old authors: " + existsAuthors);
   if (!existsAuthors.isEmpty()) {
     create.delete(AUTHOR_BOOK).where(AUTHOR_BOOK.ID_AUTHOR.in(existsAuthors)).execute();
   }
 }
  @Override
  public void update() throws IOException {
    ResourceFileLocation location = getLocation("KEGG Reaction");
    HSQLDBLocation connection = connection();
    try {
      Hsqldb.createReactionSchema(connection.getConnection());
      DSLContext create = DSL.using(connection.getConnection(), HSQLDB);

      Set<String> compoundIds = Sets.newHashSetWithExpectedSize(10000);

      InsertValuesStep2<?, String, String> reactionInsert =
          create.insertInto(REACTION, REACTION.ACCESSION, REACTION.EC);
      InsertValuesStep1<?, String> compoundInsert = create.insertInto(COMPOUND, COMPOUND.ACCESSION);

      List<String[]> reactants = new ArrayList<String[]>(10000);
      List<String[]> products = new ArrayList<String[]>(10000);

      KEGGReactionParser parser =
          new KEGGReactionParser(
              location.open(), KEGGField.ENTRY, KEGGField.EQUATION, KEGGField.ENZYME);
      Map<KEGGField, StringBuilder> entry;
      while ((entry = parser.readNext()) != null) {

        if (isCancelled()) break;

        String equation = entry.get(KEGGField.EQUATION).toString();
        String ec =
            entry.containsKey(KEGGField.ENZYME)
                ? entry.get(KEGGField.ENZYME).toString().trim()
                : "";
        String[] sides = equation.split("<=>");

        String[][] left = getParticipants(sides[0]);
        String[][] right = getParticipants(sides[1]);

        Matcher matcher = ACCESSION.matcher(entry.get(KEGGField.ENTRY).toString());

        if (!ec.isEmpty()) ec = ec.split("\\s+")[0].trim();

        if (matcher.find()) {
          String accession = matcher.group(1);
          reactionInsert.values(accession, ec);

          for (String[] participant : left) {
            String cid = participant[1];
            if (compoundIds.add(cid)) compoundInsert.values(cid);
            participant = Arrays.copyOf(participant, 3);
            participant[2] = accession;
            reactants.add(participant);
          }
          for (String[] participant : right) {
            String cid = participant[1];
            if (compoundIds.add(cid)) compoundInsert.values(cid);
            participant = Arrays.copyOf(participant, 3);
            participant[2] = accession;
            products.add(participant);
          }
        }
      }

      // do the inserts
      fireProgressUpdate("inserting reactions and compounds");
      reactionInsert.execute();
      compoundInsert.execute();

      fireProgressUpdate("inserting reaction relations");

      for (int i = 0, end = reactants.size() - 1; i <= end; i++) {

        String[] participant = reactants.get(i);
        double coef = Double.parseDouble(participant[0]);
        String cid = participant[1];
        String acc = participant[2];
        create
            .insertInto(REACTANT)
            .set(REACTANT.COEFFICIENT, coef)
            .set(
                REACTANT.COMPOUND_ID,
                create.select(COMPOUND.ID).from(COMPOUND).where(COMPOUND.ACCESSION.eq(cid)))
            .set(
                REACTANT.REACTION_ID,
                create.select(REACTION.ID).from(REACTION).where(REACTION.ACCESSION.eq(acc)))
            .execute();
      }

      for (int i = 0, end = products.size() - 1; i <= end; i++) {

        String[] participant = products.get(i);
        double coef = Double.parseDouble(participant[0]);
        String cid = participant[1];
        String acc = participant[2];
        create
            .insertInto(PRODUCT)
            .set(PRODUCT.COEFFICIENT, coef)
            .set(
                PRODUCT.COMPOUND_ID,
                create.select(COMPOUND.ID).from(COMPOUND).where(COMPOUND.ACCESSION.eq(cid)))
            .set(
                PRODUCT.REACTION_ID,
                create.select(REACTION.ID).from(REACTION).where(REACTION.ACCESSION.eq(acc)))
            .execute();
      }

    } catch (SQLException e) {
      throw new IOException(e);
    } finally {
      location.close();
      try {
        connection.commit();
      } catch (SQLException e) {
        System.err.println(e.getMessage());
      } finally {
        try {
          connection.close();
        } catch (SQLException e) {
        }
      }
    }
  }