Start line:  
End line:  

Snippet Preview

Snippet HTML Code

Stack Overflow Questions
  package jason.bb;
  
  import static jason.asSyntax.ASSyntax.createNumber;
 
 import java.sql.Types;
 import java.util.List;
 import java.util.Map;
 import java.util.Set;
Implementation of BB that stores some beliefs in a relational data base.

The parameters for this customisation are:

  • args[0] is the Database Engine JDBC drive
  • args[1] is the JDBC URL connection string
    The url can use the agent name as parameter as in "jdbc:mysql://localhost/%s". In this case, %s will be replaced by the agent's name.
  • args[2] is the username
  • args[3] is the password
  • args[4] is an AS list with all beliefs that are mapped to DB. Each element of the list is in the form

    predicate(arity [, table_name [, columns]])

    and columns is in the form

    columns( col_name(col_type), col_name(col_type), ....)

Example in .mas2j project, the agent c uses a JDBC belief base:

agents: 
c beliefBaseClass jason.bb.JDBCPersistentBB(
"org.hsqldb.jdbcDriver", // driver for HSQLDB
"jdbc:hsqldb:bookstore", // URL connection
"sa", // user
"", // password
"[a(1,tablea,columns(runs(integer))),
book(5),
book_author(2),
author(2,author,columns(id(integer),name(varchar(30)))),
publisher(2)]");

The predicate a/1 is mapped to a table called "tablea" with an integer column called runs; predicate book (with arity 5) is mapped to a table called "book"; and so on.

The name and type of the columns are used only if the table does not exits and have to be created. If no column name/type is provided, an arbitrary name is used with type varchar(256). If no table name is provided, the predicate name is used for the table name.

Author(s):
Jomi
 
 public class JDBCPersistentBB extends ChainBBAdapter {
     private static Logger logger     = Logger.getLogger(JDBCPersistentBB.class.getName());
 
     static final String   COL_PREFIX = "term";
     static final String   COL_NEG    = "j_negated";
     static final String   COL_ANNOT  = "j_annots";

    
the number of columns that this customisation creates (default is 2: the j_negated and j_annots columns)
 
     protected int extraCols = 0;
     
     protected Connection  conn;
     protected String      url;
    protected String      agentName;
    
    public JDBCPersistentBB() {
         = 2;
    }
    public JDBCPersistentBB(BeliefBase next) {
        super(next);
         = 2;
    }
    
    
    // map of bels in DB
    @Override
    public void init(Agent agString[] args) {
        try {
             = ag.getTS().getUserAgArch().getAgName();
        } catch (Exception _) {
            .warning("Can not get the agent name!");
             = "none";
        }
        try {
            .fine("Loading driver " + args[0]);
            Class.forName(args[0]);
             = String.format(args[1], );
            .fine("Connecting: url= " +  + ", user=" + args[2] + ", password=" + args[3]);
             = DriverManager.getConnection(args[2], args[3]);
            // load tables mapped to DB
            ListTerm lt = ListTermImpl.parseList(args[4]);
            for (Term t : lt) {
                Structure ts = (Structure)t;
                int arity    = Integer.parseInt(ts.getTerm(0).toString());
            	String table = ts.getFunctor();
                if (ts.getArity() >= 2) {
                	table = ts.getTerm(1).toString();
                }
                Structure columns = new Structure("columns");
                if (ts.getArity() >= 3) {
                    columns = (Structure)ts.getTerm(2);
                }
                // create the table and get its Metadata
                Statement stmt = .createStatement();
                ResultSet rs;
                try {
                    rs = stmt.executeQuery("select * from " + table);
                } catch (SQLException e) {
                    // create table
                    stmt.executeUpdate(getCreateTable(tablearitycolumns));
                    rs = stmt.executeQuery("select * from " + table);
                }
                .put(new PredicateIndicator(ts.getFunctor(), arity), rs.getMetaData());
                .put(new PredicateIndicator("~"+ts.getFunctor(), arity), rs.getMetaData());
                stmt.close();
            }
            //logger.fine("Map=" + belsDB);
        } catch (ArrayIndexOutOfBoundsException e) {
            .log(."Wrong parameters for JDBCPersistentBB initialisation."e);
        } catch (ClassNotFoundException e) {
            .log(."Error loading jdbc driver " + args[0], e);
        } catch (SQLException e) {
            .log(."DB connection failure. url= " +  + ", user=" + args[2] + ", password=" + args[3], e);
        }
        .init(agargs);
    }
    @Override
    public void stop() {
        if ( == nullreturn;
        
        try {
            if (.startsWith("jdbc:hsqldb")) {
                .createStatement().execute("SHUTDOWN");
            }
            .close(); // if there are no other open connection
        } catch (Exception e) {
            .log(."Error in shutdown SGBD "e);
        }
        .stop();
    }
    
    public void clear() {
        .warning("clear is still not implemented for JDBC BB!");
    }

    
returns true if the literal is stored in a DB
    protected boolean isDB(Literal l) {
        return .get(l.getPredicateIndicator()) != null;
    }

    
returns true if the table for pi was created by Jason
    protected boolean isCreatedByJason(PredicateIndicator pithrows SQLException {
        ResultSetMetaData meta = .get(pi);
        if (meta != null) {
            int cols = meta.getColumnCount();
            return cols >=  && 
                   meta.getColumnName((cols - ) + 1).equalsIgnoreCase() && 
                   meta.getColumnName((cols - ) + 2).equalsIgnoreCase();
        }
        return false;
    }
    @Override
    public Literal contains(Literal l) {
        if (!isDB(l))
            return .contains(l);
        Statement stmt = null;
        try {
            // create a literal from query
            stmt = .createStatement();
            String q = getSelect(l);
            if (.isLoggable(.)) .fine("query for contains "+l+":"+q);
            ResultSet rs = stmt.executeQuery(q);
            if (rs.next()) {
                return resultSetToLiteral(rs,l.getPredicateIndicator());
            }
        } catch (SQLException e) {
            .log(."SQL Error"e);
        //} catch (ParseException e) {
        //    logger.log(Level.SEVERE, "Parser Error", e);
        } finally {
            try {
                stmt.close();
            } catch (Exception e) {
                .log(."SQL Error closing connection"e);
            }
        }
        return null;
    }
    @Override
    public boolean add(Literal l) {
        return add(0, l);
    }
    
    @Override
    public boolean add(int indexLiteral l) {
        if (!isDB(l))
            return .add(l);
        if (index != 0) 
            .severe("JDBC BB does not support insert index "+index+" for "+l+", using index = 0!");
        
        Literal bl = contains(l);
        Statement stmt = null;
        try {
            if (bl != null) {
                if (isCreatedByJason(l.getPredicateIndicator())) {
                    // add only annots
                    if (l.hasSubsetAnnot(bl))
                        // the current bel bl already has l's annots
                        return false;
                    else {
                        // "import" annots from the new bel
                        bl.importAnnots(l);
                        
                        // check if it needs to be added in the percepts list
                        if (l.hasAnnot()) {
                            getDBBPercepts().add(bl);
                        }
                        // store bl annots
                        stmt = .createStatement();
                        String q = "update "+getTableName(bl)+" set "++" = '"+bl.getAnnots()+"' "+getWhere(l);
                        if (.isLoggable(.)) .fine("query for update "+q);
                        stmt.executeUpdate(q);
                        return true;
                    }
                }
            } else {
                // create insert command
                stmt = .createStatement();
                if (.isLoggable(.)) .fine("query for insert "+getInsert(l));
                stmt.executeUpdate(getInsert(l));
                // add it in the percepts list
                if (l.hasAnnot()) {
                    getDBBPercepts().add(l);
                }
                return true;
            }
        } catch (Exception e) {
            .log(."SQL Error"e);
        } finally {
            try {
                if (stmt != nullstmt.close();
            } catch (Exception e) {
                .log(."SQL Error closing connection"e);                    
            }
        }
        return false;
    }
    @Override
    public boolean remove(Literal l) {
        if (!isDB(l))
            return .remove(l);
        Literal bl = contains(l);
        if (bl != null) {
            Statement stmt = null;
            try {
                if (l.hasSubsetAnnot(bl)) {
                    if (l.hasAnnot()) {
                        getDBBPercepts().remove(bl);
                    }
                    boolean result = bl.delAnnots(l.getAnnots()) || !bl.hasAnnot();
                    stmt = .createStatement();
                    if (bl.hasAnnot() && isCreatedByJason(l.getPredicateIndicator())) {
                        // store new bl annots
                        stmt.executeUpdate("update "+getTableName(bl)+" set "++" = '"+bl.getAnnots()+"' "+getWhere(l));
                    } else {
                        // remove from DB
                        stmt.executeUpdate("delete from "+getTableName(bl)+getWhere(bl));                        
                    }
                    return result;                    
                }
            } catch (SQLException e) {
                .log(."SQL Error"e);
            } finally {
                try {
                    stmt.close();
                } catch (Exception e) {
                    .log(."SQL Error closing connection"e);                    
                }
            }
        }
        return false;
    }
    private Set<LiteralgetDBBPercepts() {
        BeliefBase last = getLastBB();
        if (last instanceof DefaultBeliefBase
            return ((DefaultBeliefBase)last).getPerceptsSet();
        else
            return null;    
    }
    
    
    @Override
    public boolean abolish(PredicateIndicator pi) {
        if (.get(pi) == null)
            return .abolish(pi);
        Statement stmt = null;
        try {
            stmt = .createStatement();
            stmt.executeUpdate(getDeleteAll(pi));
        } catch (SQLException e) {
            .log(."SQL Error"e);
        } finally {
            try {
                stmt.close();
            } catch (Exception e) {
                .log(."SQL Error closing connection"e);
            }
        }
        return false;
    }
    @Override
    public Iterator<LiteralgetCandidateBeliefs(Literal lUnifier u) {
        final PredicateIndicator pi = l.getPredicateIndicator();
        if (.get(pi) == null)
            return .getCandidateBeliefs(lu);
        
        if (l.isVar()) {
            // all bels are relevant
            return iterator();
        } else {
            // get all rows of l's table
            String q = null;
            try {
                q = getSelect(l);
                if (.isLoggable(.)) .fine("getRelevant query for "+l+": "+q);
                final ResultSet rs = .createStatement().executeQuery(q);
                return new Iterator<Literal>() {
                    boolean hasNext   = true;
                    boolean firstcall = true;
                    public boolean hasNext() {
                        if () {
                            try {
                                 = rs.next();
                            } catch (SQLException e) {
                                .log(."SQL Error"e);
                            }
                             = false;
                        }
                        return ;
                    }
                    public Literal next() {
                        try {
                            if () {
                                 = rs.next();
                                 = false;
                            }
                            Literal l = resultSetToLiteral(rs,pi);
                             = rs.next();
                            if (!rs.close();
                            return l;
                        } catch (Exception e) {
                            .log(."Error"e);
                        }
                        return null;
                    }
                    public void remove() { 
                        .warning("remove in jdbc get relevant is not implemented!");  
                    }
                };
            } catch (SQLException e) {
                .log(."SQL Error in getRelevant for "+l+" with query "+qe);
            }
        }
        return null;
    }
    @Override
    public int size() {
        int count = 0;
        Statement stmt = null;
        try {
            stmt = .createStatement();
            // for all tables, count rows
            for (PredicateIndicator pi : .keySet()) {
                if (!pi.getFunctor().startsWith("~")) {
                    ResultSet rs = stmt.executeQuery(getCountQuery(pi));
                    if (rs.next()) {
                        count += rs.getInt(1);
                    }
                }
            }
        } catch (SQLException e) {
            .log(."SQL Error"e);
        } finally {
            try {
                stmt.close();
            } catch (Exception e) {
                .log(."SQL Error closing connection"e);
            }
        }
        return count + .size();
    }
    
    @Override
    public Iterator<Literaliterator() {
        List<Literalall = new ArrayList<Literal>(size());
        
        Iterator<Literalis = .iterator();
        while (is.hasNext()) {
            all.add(is.next());
        }
        
        Statement stmt = null;
        try {
            stmt = .createStatement();
            // for all tables, get rows literal
            for (PredicateIndicator pi : .keySet()) {
                if (!pi.getFunctor().startsWith("~")) {
                    ResultSet rs = stmt.executeQuery(getSelectAll(pi));
                    while (rs.next()) {
                        all.addresultSetToLiteral(rspi));
                    }
                }
            }
        } catch (Exception e) {
            .log(."Error"e);
        } finally {
            try {
                stmt.close();
            } catch (Exception e) {
                .log(."SQL Error closing connection"e);
            }
        }    
        return all.iterator();
    }
    

    
translates the current line of a result set into a Literal
    protected Literal resultSetToLiteral(ResultSet rsPredicateIndicator pithrows SQLException {
        ResultSetMetaData meta = .get(pi);
        boolean isJasonTable = isCreatedByJason(pi);
        Literal ldb = new LiteralImpl(pi.getFunctor());
        int end = meta.getColumnCount();
        if (isJasonTable)
            end = end - ;
        for (int c = 1; c <= endc++) {
            Term parsed = null;
            switch (meta.getColumnType(c)) {
            case .:
            case .:
            case .:
            case .:
            case .:
            case .:
                parsed = new NumberTermImpl(rs.getDouble(c));
                break;
            case .:
                parsed = timestamp2structure(rs.getTimestamp(c));
                break;
            default:
                String sc = rs.getString(c);
                if (sc == null || sc.trim().length() == 0) {
                    parsed = new StringTermImpl("");
                } else if (Character.isUpperCase(sc.charAt(0))) {
                    // there is no var at BB
                    parsed = new StringTermImpl(sc);
                } else {
                    try {
                        parsed = ASSyntax.parseTerm(sc);
                    
                        // if the parsed term is not equals to sc, try it as string
                        if (!parsed.toString().equals(sc))
                            parsed = ASSyntax.parseTerm(sc = "\"" + sc + "\"");
                    } catch (ParseException e) {
                        // can not be parsed, be a string
                        parsed = new StringTermImpl(sc);
                    } catch (TokenMgrError e) {
                        // can not be parsed, be a string
                        parsed = new StringTermImpl(sc);                        
                    }
                }
                break;
            }
            ldb.addTerm(parsed);
        }
        if (isJasonTable) {
            ldb.setNegated(!rs.getBoolean(end + 1));
            ldb.setAnnots(ListTermImpl.parseList(rs.getString(end + 2)));
        }
        return ldb;
    }
    
    protected String getTableName(Literal lthrows SQLException {
        return getTableName(l.getPredicateIndicator());
    }
    
    protected String getTableName(PredicateIndicator pithrows SQLException {
        ResultSetMetaData meta = .get(pi);
        return meta.getTableName(1);
    }
    
    
returns the SQL command to create a new table
    protected String getCreateTable(String tableint arityStructure columnsthrows SQLException {
        StringBuilder ct = new StringBuilder("create table " + table + " (");
        for (int c = 0; c < arityc++) {
            String colName =  + c;
            String colType = "varchar(256)";
            // try to get colName and type from columns
            if (columns.getArity() > c) {
                Structure scol = (Structure)columns.getTerm(c);
                colName = scol.getFunctor();
                colType = scol.getTerm(0).toString();
            }
            ct.append(colName + " " + colType + ", ");
        }
        ct.append( + " boolean, " +  + " varchar(256))");
        .fine("Creating table: " + ct);
        return ct.toString();
    }

    
returns the SQL command for a select that retrieves the literal l from the DB
 
    protected String getSelect(Literal lthrows SQLException {
        return "select * from "+getTableName(l)+getWhere(l);
    }

    
returns the SQL command the selects all literals of type pi
    protected String getSelectAll(PredicateIndicator pithrows SQLException {
        return "select * from " + getTableName(pi);
    }
    
    
    
returns the where clausule for a select for literal l
 
    protected String getWhere(Literal lthrows SQLException {
        ResultSetMetaData meta = .get(l.getPredicateIndicator());
        StringBuilder q = new StringBuilder(" where ");
        String and = "";
        // for all ground terms of l
        for (int i = 0; i < l.getArity(); i++) {
            Term t = l.getTerm(i);
            if (t.isGround()) {
                q.append(and);
                String ts;
                if (t.isString()) {
                    ts = "'" + ((StringTermt).getString() + "'";
                } else if (t.isNumeric()) {
                    ts = t.toString();
                } else {
                    ts = "'" + t.toString() + "'";
                }
                q.append(meta.getColumnName(i + 1) + " = " + ts);
                and = " and ";
            }
        }
        if (isCreatedByJason(l.getPredicateIndicator())) {
            q.append(and +  + " = " + l.negated());
        }
        //System.out.println(q.toString());
        if (and.length() > 0) // add nothing in the clausule 
            return q.toString();
        else
            return "";
    }

    
returns the SQL command to insert l into the DB
    protected String getInsert(Literal lthrows Exception {
        StringBuilder q = new StringBuilder("insert into ");
        ResultSetMetaData meta = .get(l.getPredicateIndicator());
        q.append(meta.getTableName(1));
        q.append(" values(");
        // values
        for (int i = 0; i < l.getArity(); i++) {
            Term t = l.getTerm(i);
            if (t.isString()) {
                q.append("'" + ((StringTermt).getString() + "'");
            } else {
                Timestamp timestamp = structure2timestamp(t);
                if (timestamp != null) {
                    q.append("TIMESTAMP '" + structure2timestamp(t) + "'");
                } else {
                    q.append("'" + t.toString() + "'");
                }
            }
            if (i < meta.getColumnCount() - 1) {
                q.append(",");
            }
        }
        if (isCreatedByJason(l.getPredicateIndicator())) {
            q.append(l.negated() + ",");
            if (l.hasAnnot()) {
                q.append("\'" + l.getAnnots() + "\'");
            } else {
                q.append("\'[]\'");
            }
        }
        q.append(")");
        return q.toString();
    }

    
returns a SQL command to delete all entries for a predicate
    protected String getDeleteAll(PredicateIndicator pithrows SQLException {
        return "delete from " + getTableName(pi);
    }

    
returns a SQL command to count the number of instances of a predicate
    protected String getCountQuery(PredicateIndicator pithrows SQLException {
        return "select count(*) from " + getTableName(pi);
    }


    
just create some data to test
    public void test() {
        Statement stmt = null;
        try {
            // add a "legacy" table
            stmt = .createStatement();
            try {
                stmt.executeUpdate("drop table publisher");
            } catch (Exception e) {
            }
            stmt.executeUpdate("create table publisher (id integer, name varchar)");
            stmt.executeUpdate("insert into publisher values(1, 'Springer')");
            stmt.executeUpdate("insert into publisher values(2, 'MIT Press')");
            ResultSetMetaData meta = stmt.executeQuery("select * from publisher").getMetaData();
            .put(new PredicateIndicator("publisher", 2), meta);
        } catch (SQLException e) {
            .log(."SQL Error"e);
        } finally {
            try {
                stmt.close();
            } catch (Exception e) {
                .log(."SQL Error closing connection"e);
            }
        }
    }
    public static final String timestampFunctor = "timestamp";

    
translates a SQL timestamp into a structure like "timestamp(Y,M,D,H,M,S)"
    public static Structure timestamp2structure(Timestamp timestampthrows SQLException {
        Calendar time = Calendar.getInstance(); 
        time.setTime(timestamp);
        return ASSyntax.createStructure(
                createNumber(time.get(.)),
                createNumber(time.get(.)),
                createNumber(time.get(.)),
                createNumber(time.get(.)),
                createNumber(time.get(.)),
                createNumber(time.get(.)));
    }
    
    
translates structure like "timestamp(Y,M,D,H,M,S)" into a SQL timestamp
    @SuppressWarnings("deprecation")
    public static Timestamp structure2timestamp(Term timestampthrows Exception {
        if (timestamp.isStructure()) {
            Structure s = (Structure)timestamp;
            if (s.getFunctor().equals() && s.getArity() == 6) {
                return new Timestamp(
                        (int)((NumberTerm)s.getTerm(0)).solve() - 1900,
                        (int)((NumberTerm)s.getTerm(1)).solve(),
                        (int)((NumberTerm)s.getTerm(2)).solve(),
                        (int)((NumberTerm)s.getTerm(3)).solve(),
                        (int)((NumberTerm)s.getTerm(4)).solve(),
                        (int)((NumberTerm)s.getTerm(5)).solve(),
                        0
                        );
            }
        }
        return null;
    }
New to GrepCode? Check out our FAQ X