Skip to main content

JDBC in Wallboard

JDBC (Java Database Connectivity) datasources allow you to connect directly to relational databases and fetch data using SQL queries. This enables real-time integration with enterprise databases, data warehouses, and business systems for dynamic content display in your widgets and templates.

Advanced Feature Notice

Using JDBC datasources requires advanced database and SQL knowledge. This feature is designed primarily for custom integration projects and does not come with standard support.

We strongly recommend contacting our support team and requesting professional services before proceeding with JDBC integration.

Prerequisites​

Before creating a JDBC datasource, ensure you have:

  • Database Access: Valid credentials and network access to the target database
  • JDBC Driver: The appropriate .jar file for your database type
  • SQL Knowledge: Understanding of SQL syntax and your database schema
  • Network Connectivity: Proper firewall configuration for database connections
  • Database Permissions: Read access to the required tables and views
  • Administrative Access: Wallboard administrator rights to upload drivers and configure datasources

Supported Database Types​

JDBC datasources support a wide range of relational databases through their respective drivers:

Major Database Systems:

  • PostgreSQL: Open-source object-relational database
  • MySQL/MariaDB: Popular open-source relational databases
  • Microsoft SQL Server: Enterprise database management system
  • Oracle Database: Enterprise-grade relational database
  • SQLite: Lightweight embedded database
  • IBM Db2: Enterprise database platform
  • H2 Database: Java-based embedded database

Driver Requirements: Each database requires its specific JDBC driver JAR file. Common drivers include:

Multiple Drivers

You can upload multiple drivers for different database types. The system automatically selects the appropriate driver based on the connection URL format.

Driver Compatibility

Always use the latest stable version of JDBC drivers that matches your database version for optimal compatibility and security.

Driver Installation and Setup​

Step 1: Install JDBC Driver​

Navigate to Administrator > JDBC drivers where you can manage database drivers.

jdbc-interface.png

Click the + button to add a new JDBC driver:

add-jdbc-driver-filled.png

Driver Configuration Fields:

  1. Driver Name: Provide a descriptive name (e.g., "PostgreSQL 15", "MySQL Production")
  2. Driver Class Name: The main driver class from the JAR file
  3. JAR File: Upload the JDBC driver file

Common Driver Class Names​

PostgreSQL: org.postgresql.Driver
MySQL: com.mysql.cj.jdbc.Driver (MySQL 8+) or com.mysql.jdbc.Driver (MySQL 5.7)
SQL Server: com.microsoft.sqlserver.jdbc.SQLServerDriver
Oracle: oracle.jdbc.driver.OracleDriver
SQLite: org.sqlite.JDBC
H2: org.h2.Driver

Create JDBC Datasource​

Step 1: Configure Datasource​

Go to Datasources > Click Add new in the External datasource tab > Choose the JDBC type.

add-jdbc-driver-filled.png

Basic Configuration​

1. Update Schedule
Configure how frequently the datasource fetches updated data:

  • Select a Refresh Frequency for regular intervals (every minute, hour, day, etc.)
  • Specify a Cron Expression for custom scheduling
Example Cron Expressions
  • 0 */10 * * * * - Every 10 minutes
  • 0 0 */6 * * * - Every 6 hours
  • 0 0 2 * * * - Daily at 2:00 AM
JDBC Refreshing

For more information about external datasource refresh options, see the External Datasource Refreshing documentation.

2. Database Connection Settings

Connection URL: Enter the JDBC connection string for your database:

PostgreSQL: jdbc:postgresql://hostname:5432/database_name
MySQL: jdbc:mysql://hostname:3306/database_name?useSSL=false&serverTimezone=UTC
SQL Server: jdbc:sqlserver://hostname:1433;databaseName=database_name
Oracle: jdbc:oracle:thin:@hostname:1521:SID or jdbc:oracle:thin:@hostname:1521/SERVICE_NAME
SQLite: jdbc:sqlite:/path/to/database.db

3. Authentication

  • JDBC Username: Database user account with read permissions
  • JDBC Password: Corresponding password for the database user

4. SQL Query Configuration Define the SQL query that retrieves your data. The query result will be converted to JSON format for widget consumption.

SQL Query Examples​

Basic Data Selection:

SELECT id, name, department, salary 
FROM employees 
ORDER BY name

Filtered Results:

SELECT product_name, stock_quantity, price 
FROM products 
WHERE stock_quantity > 0 
ORDER BY product_name

Aggregated Data:

SELECT department, COUNT(*) as employee_count, AVG(salary) as avg_salary
FROM employees 
GROUP BY department 
ORDER BY avg_salary DESC

Date-Based Queries:

SELECT order_id, customer_name, order_total, order_date
FROM orders 
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY order_date DESC

Advanced Configuration Options​

5. Datasource Settings

  • Active: Enable/disable the datasource without deleting it
  • Ignore Error counter: Continue operation even if errors occur during data fetching

6. Editor Settings Configure how the system processes database results:

  • Cache external resources: Store external content referenced in query results
  • Remove broken external resource references: Clean up invalid links in data
  • Rotate cache on every update: Clear and rebuild cache with each refresh
  • Exchange internal resource references: Update internal paths automatically

7. Array Processing Enable Randomize arrays to shuffle the order of query results each time the datasource updates for varied content display.

Connection String Parameters​

PostgreSQL Additional Options:

jdbc:postgresql://host:port/database?ssl=true&sslmode=require

MySQL Connection Parameters:

jdbc:mysql://host:port/database?useSSL=true&requireSSL=true&verifyServerCertificate=false

SQL Server with Windows Authentication:

jdbc:sqlserver://host:port;databaseName=database;integratedSecurity=true

8. Save Configuration
Once all connection details and query are configured, click Save to create the datasource.

SQL Syntax

JDBC datasources use standard SQL syntax, not JPQL. Ensure your queries are compatible with your specific database system's SQL dialect.

Using Database Data in Widgets​

After creating the JDBC datasource, query results are available as JSON data for widget binding:

Single Row Results:

  • data.employee_name, data.department, data.salary

Multi-Row Results (Arrays):

  • data[0].product_name, data[1].stock_quantity
  • data.length for total number of rows

Calculated Fields:

  • data[0].employee_count, data[0].avg_salary

Common Use Cases​

Employee Dashboards: Staff directories, org charts, performance metrics
Inventory Management: Stock levels, product information, warehouse data
Sales Reporting: Revenue tracking, customer data, sales performance
Operational Metrics: System status, KPI monitoring, real-time statistics
Financial Reporting: Budget tracking, expense monitoring, financial KPIs
Customer Information: Contact details, account status, service history

Security Considerations​

  • Database User Permissions: Create dedicated read-only database users for Wallboard
  • Network Security: Use VPN or secure network connections for database access
  • Connection Encryption: Enable SSL/TLS for database connections when possible
  • Credential Management: Store database credentials securely and rotate regularly
  • Query Limitations: Avoid queries that could impact database performance
  • Data Sensitivity: Be cautious when displaying sensitive data on public screens
Performance Optimization

For optimal performance, use indexed columns in WHERE clauses, limit result sets with LIMIT or TOP clauses, and avoid complex JOINs that could impact database performance.