How to create a graph from data stored inside a database

The easiest way to visualize the data stored inside a database with yFiles is to simply query the data from the database and create graph elements (nodes, edges, labels) from the data. These elements are then added to a graph, which will be laid out using one of yFiles automatic layout algorithms and displayed afterwards.

The following steps are required to build a graph from the database data:

• First of all we need to check for the right database driver according to the existing database
• Next we connect to that database
• After that we simply query the required data and add our graph elements to the graph
• At last we perform a layout and display the graph.

1. The Database Tables:

2. Check for database driver depending on your database

private void checkForDBDriver() {
    try {
      //TODO: Select/add your database driver. 
      //Depending on your database you can for example use one of these drivers:

      //csvDir
      Class.forName("org.relique.jdbc.csv.CsvDriver");

      //Oracle
      Class.forName("oracle.jdbc.driver.OracleDriver");

      //DB2
      Class.forName("com.ibm.db2.jdbc.app.DB2Driver");

      //Microsoft SQL
      Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");

      //mysql
      Class.forName("com.mysql.jdbc.Driver");

      //Derby
      Class.forName("org.apache.derby.jdbc.ClientDriver");

      //PostgreSQL
      Class.forName("org.postgresql.Driver");

      //hSQLDb
      Class.forName("org.hsqldb.jdbcDriver");

    } catch (ClassNotFoundException e) {
      throw new RuntimeException("Database driver not found!");
    }
  }

3. Connect to the database:

private Connection getDBConnection() {
    Connection con = null;
    try {
      String csvDir = getClass().getResource("csvDir").getPath();
      con = DriverManager.getConnection("jdbc:relique:csv:" + csvDir);

      //Oracle
      con = DriverManager.getConnection("jdbc:oracle:thin:@<serverip>:<port>:<sid>", "<username>", "<password>");

      //DB2
      con = DriverManager.getConnection("jdbc:db2://<serverip>:<port>/<databaseName>", "<username>", "<password>");

      //Microsoft SQL
      con = DriverManager.getConnection("jdbc:microsoft:sqlserver://<serverip>:<port>/<databaseName>", "<username>","<password>");

      //mysql
      con = DriverManager.getConnection("jdbc:mysql://<serverip>:<port>/<databaseName>", "<username>", "<password>");

      //derby
      con = DriverManager.getConnection("jdbc:derby://<serverip>:<port>/<databaseName>", "<username>", "<password>");

      //PostgreSQL
      con = DriverManager.getConnection("jdbc:postgreesql://<serverip>:<port>/<databaseName>", "<username>", "<password>");

      //hSQLDb
      con = DriverManager.getConnection("jdbc:hsqldb:hsql://<serverip>:<port>/<databaseName>", "<username>", "<password>");

      return con;
    }
    catch (SQLException e) {
      throw (new IllegalStateException("Could not create connection to database: " + e.getMessage()));
    }
    finally {
      if (con != null) {
        try {
          con.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
      }
    }
  }

4. Fill the graph:

private void fillGraph(Connection con) {
    try {
      Statement stmt = con.createStatement();

      //get graph from view, so we can fill it
      Graph2D graph = view.getGraph2D();

      //create some root node
      Node company = graph.createNode();
      NodeRealizer companyRealizer = graph.getRealizer(company);
      //set the label of this node
      companyRealizer.getLabel().setText("My Company");
      //some style customization
      companyRealizer.setFillColor(new Color(255, 102, 51));

      //query some data and create nodes and edges for the departments
      ResultSet rs = stmt.executeQuery("SELECT department_id, name FROM department");
      HashMap departmentID2Node = new HashMap();
      while (rs.next()) {
        //create a node for each department
        Node department = graph.createNode();
        NodeRealizer realizer = graph.getRealizer(department);
        //set the label of this node
        realizer.getLabel().setText(rs.getString("name"));
        //some style customization
        realizer.setFillColor(new Color(255, 204, 51));

        //create an edge from root to this node
        graph.createEdge(company, department);

        //store the node with its according ID for easy access when creating edges later
        departmentID2Node.put(rs.getString("ID"), department);
      }


      //query some more data and create nodes and edges for employees
      rs = stmt.executeQuery("SELECT name, department_id FROM employee");
      while (rs.next()) {
        //create a node for each employee
        Node employee = graph.createNode();
        NodeRealizer realizer = graph.getRealizer(employee);
        realizer.getLabel().setText(rs.getString("name"));
        realizer.setFillColor(new Color(51, 102, 255));

        //create an edge between the department and the employee
        Node department = (Node) departmentID2Node.get(rs.getString(("department_id")));
        if (department != null) {
          graph.createEdge(department, employee);
        }
      }
      rs.close();
      stmt.close();

      //simply enlarge all nodes, so the labels will fit.
      //Of course there are some more sophisticated methods to do that
      for (NodeCursor nodeCursor = graph.nodes(); nodeCursor.ok(); nodeCursor.next()) {
        Node node = nodeCursor.node();
        graph.getRealizer(node).setSize(100, 30);
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
    finally {
      if (con != null) {
        try {
          con.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
      }
    }
  }

5. The Resulting Diagram: