How to create a graph from data stored inside a database

Applies to: yFiles for Java 2.8, 2.7, 2.6, 2.5, 2.4, 2.3

print article

email article

Type: Tips & Tricks

Categories this article belongs to:

yFiles for Java

This article describes how to create a graph from data that is stored inside a database.

Note

The yFiles distribution comes with a detailed tutorial that explains how to gather data from a database and create a graph from this data. You will find this tutorial in the yFiles destination folde: <yFilesDir>/tutorial/doc/getData/database.html

This article uses a similar approach and can be taken as a second reference for this topic.

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:

These are some example tables we are querying data from for this demo:

Department:

id	name
1	Research
2	Development
3	Sales

Employee:

id	name	department_id
1	Jacob Miller	1
2	Joseph Lewis	2
3	Susan Reid	2
4	Gregory Arnold	3

2. Check for database driver depending on your database

Make sure to only use one of the statements depending on your database type.

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:

Make sure to only use one of the statements depending on your database type.

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:

Now that we do have a connection to our database, we query the data and create graph elements that are added to 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:

Here is the diagram resulting from the above query.

Note

The attached Java demo shows a simple example of an application that creates a graph from the database, performs a hierarchic layout and displays the graph in a simple editor. You can copy it to the y.demo.io package, but you need to make sure to adjust the database driver query and the database connectiing accordingly.

The above code snippets can also be integrated in one of the demo applications that are shipped with yFiles and can be found in the demo package. Thus you can easily bundle the graph creation with any layout algorithm or editor you like.

Resources:
DataBaseQueryDemo.java

Keywords:

Oracle - DB2 - SQL - mysql - Derby - PostgreSQL - hSQLDb - graph - create - database - data - base - query - select