How to create a graph from data stored inside a database |
| Applies to: yFiles 2.5, yFiles 2.4, yFiles 2.3 |
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:
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.
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 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.
| Resources: |
| Keywords: | Oracle - DB2 - SQL - mysql - Derby - PostgreSQL - hSQLDb - graph - create - database - data - base - query - select |


