How to create a graph from data stored inside a database
Applies to: yFiles 2.5, yFiles 2.4, yFiles 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 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 a graph from it.

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 build our graph
  • At last we set the created graph on the view and display it.

1. Check for database driver according to your database

private void checkForDBDriver() {
  try {
      //Check if the database driver is available in the current classpath.
      // According to your database you can for example use one of these drivers:

      //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!");
    }
}

2. Connect to the database:

private Graph2D createGraphFromDB() {
checkForDBDriver();
Connection con = null;
    try {
      //connect to the database with username and password
      //according to your database for example use one of the following options:

      //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>");

      Graph2D graph = createGraph(con);
      return graph;
    }
    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();
        }
      }
}
    }

3. Create the graph:

private Graph2D createGraph(Connection con) throws SQLException {
    Statement stmt = con.createStatement();

    //create an empty graph 
    Graph2D graph = new Graph2D();
    //create a root node
    Node company = graph.createNode();
    graph.getRealizer(company).getLabel().setText("MyCompany");

    //query some data
    ResultSet rs = stmt.executeQuery("SELECT 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(Color.RED);

      //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"), node);
    }

    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(Color.BLUE);

	//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();
    return graph;
  }

Set the created graph on the view

//view is of type Graph2DView
view.setGraph2D(createGraphFromDB());
The attached file shows a simple example on how to create a application that creates a graph from the database, performs a hierarchic layout and displays the graph in a simple editor.
The above code can 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.

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

Provide feedback:
How useful was this article?    less 1 2 3 4 5 more
Email address (optional):
COPYRIGHT © 2008 yWorks · ALL RIGHTS RESERVED imprint | top | home