1. API reference
  2. Welcome
    1. Component overview
    2. Quick start
    3. System requirements
    4. Troubleshooting
    5. Managing license keys
  3. Connecting to Data Source
    1. JSON
      1. Connecting to JSON
      2. Data types in JSON
    2. CSV
      1. Connecting to CSV
      2. Connecting to CSV using the Data Compressor
      3. Data types in CSV
    3. Database
      1. Connecting to SQL databases
      2. Connecting to other databases
      3. Connecting to a database with Node.js
      4. Connecting to a database with .NET
      5. Connecting to a database with .NET Core
      6. Connecting to a database with Java
      7. Connecting to a database with PHP
    4. Microsoft Analysis Services
      1. Connecting to Microsoft Analysis Services
      2. Getting started with the Accelerator
      3. Installing the Accelerator as a Windows Service
      4. Referring the Accelerator as a DLL
      5. Configuring the authentication process
      6. Configuring a secure HTTPS connection
      7. Troubleshooting
    5. Pentaho Mondrian
      1. Connecting to Pentaho Mondrian
      2. Getting started with Accelerator
      3. Configuring Mondrian roles
      4. Сonfiguring username/password protection
      5. Сonfiguring secure HTTPS connection
      6. Troubleshooting
    6. icCube
  4. Security
    1. Accelerator security
    2. Data Compressor Security
  5. Configuring report
    1. What is a report
    2. Data source
    3. Slice
    4. Options
    5. Number formatting
    6. Conditional formatting
    7. Set report to the component
    8. Get report from the component
    9. Date and time formatting
    10. Configuring global options
    11. Export and print
    12. Calculated values
    13. Custom sorting
  6. Integration with frameworks
    1. Available tutorials
    2. Integration with AngularJS (v1.x)
    3. Integration with Angular
    4. Integration with React
    5. Integration with Webpack
    6. Integration with ASP.NET
    7. Integration with jQuery
    8. Integration with JSP
    9. Integration with TypeScript
    10. Integration with RequireJS
    11. Integration with PhoneGap
  7. Integration with charts
    1. Integration with Highcharts
    2. Integration with Google Charts
    3. Integration with FusionCharts
    4. Integration with any charting library
  8. Customizing
    1. Customizing toolbar
    2. Customizing appearance
    3. Customizing context menu
    4. Localizing component
  9. Updating to the latest version
    1. Updating to the latest version
    2. Release notes
    3. Migration guide from 2.5 to 2.6
    4. Migration guide from 2.4 to 2.5
    5. Migration guide from 2.3 to 2.4
    6. Migration guide from 2.2 to 2.3
    7. Documentation for older versions
Table of contents

Connecting to a relational database with Java

Requirements

  • Flexmonster Pivot version 2.213 or higher
  • Java 7 or higher
  • A driver for the database

Supported databases

Step 1: Embed the component into your web page

Set up an empty component in your HTML page. If Flexmonster is not yet embedded – return to Quick start. Your code should look similar to the following example:

<div id="pivotContainer">The component will appear here</div>
<script src="flexmonster/flexmonster.js"></script>
<script>
	var pivot = new Flexmonster({
		container: "pivotContainer",
		toolbar: true,
		licenseKey: "XXXX-XXXX-XXXX-XXXX-XXXX"
	});
</script>

Step 2: Setup the Data Compressor on the server

Add the following dependencies to your project:

  • An appropriate database driver
  • flexmonster-compressor.jar – located in the Pivot Table for Databases/server/java/ folder of the download package.

Below is a connection and query sample for a MySQL database:

Class.forName("com.mysql.jdbc.Driver").newInstance();
String connectionString = "jdbc:mysql://localhost:3306/foodmart";
Connection connection = 
	DriverManager.getConnection(connectionString, "user", "pass");
String query = "SELECT * FROM customer";
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(query);

Then the following line of code will convert ResultSet to a compressed InputStream:

InputStream inputStream = Compressor.compressDb(resultSet);

Now you can create a response from the InputStream. For simple cases, it is suitable to read all content at once:

Scanner s = new Scanner(inputStream).useDelimiter("\\A");
String output = s.hasNext() ? s.next() : "";

It is also possible to create a streaming response. This means that the end user will get the response with minimal delay and the server will use less memory. This is the recommended approach for large datasets:

response.setContentType("text/plain");
OutputStream outputStream = response.getOutputStream();
int length = 0;
byte[] buffer = new byte[10240];
while ((length = inputStream.read(buffer)) > 0) {
    outputStream.write(buffer, 0, length);
    outputStream.flush();
}

The full project is available at Pivot Table for Databases/server/java/ inside the download package.

Step 3: Enable cross-origin resource sharing (CORS)

By default, the browser prevents JavaScript from making requests across domain boundaries. CORS allows web applications to make cross-domain requests. Here are some useful links explaining how to setup CORS on your server:

Step 4: Configure the report with your own data

Now it’s time to configure the pivot table on the web page. Let’s create a minimal report for this (replace filename and other parameters with your specific values):

var pivot = new Flexmonster({
	container: "pivotContainer",
	toolbar: true,
	report: {
		dataSource: {
			dataSourceType: "ocsv",
			/* URL to the Data Compressor Java */
			filename: "http://localhost:8400/FlexmonsterCompressor/get"
		}
	},
	licenseKey: "XXXX-XXXX-XXXX-XXXX-XXXX"
});

Launch the web page from browser — there you go! A pivot table is embedded into your project. Check out the example on JSFiddle.

Java Compressor API

Compression for CSV files:

InputStream compressFile(String fileName, char delimiter)
InputStream compressFile(String fileName)

Compression for strings in CSV format:

InputStream compressString(String string, char delimiter)
InputStream compressString(String string)

Compression for streams:

InputStream compressStream(InputStream inputStream, char delimiter)
InputStream compressStream(InputStream inputStream)

Compression for Databases:

InputStream compressDb(ResultSet resultSet)

Examples

Saving to a file

Class.forName("com.mysql.jdbc.Driver").newInstance();
String connectionString = "jdbc:mysql://localhost:3306/foodmart";
Connection connection = 
	DriverManager.getConnection(connectionString, "user", "pass");
String query = "SELECT * FROM customer";
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(query);

InputStream inputStream = Compressor.compressDb(resultSet);
OutputStream outputStream = new FileOutputStream("data.ocsv");
int length = 0;
byte[] buffer = new byte[10240];
while ((length = inputStream.read(buffer)) > 0) {
    outputStream.write(buffer, 0, length);
}
inputStream.close();
outputStream.close();

Compressing a CSV string

String input = "Category,Color,Destination,Country,Price,Quantity,Discount\n"+
	"Accessories,red,Australia,Australia,174,225,23\n"+
	"Accessories,yellow,Canada,Canada,502,90,17\n"+
	"Accessories,white,France,France,242,855,37\n"+
	"Accessories,yellow,Germany,Germany,102,897,42";
InputStream inputStream = Compressor.compressString(input);
OutputStream outputStream = new FileOutputStream("data.ocsv");
int length = 0;
byte[] buffer = new byte[10240];
while ((length = inputStream.read(buffer)) > 0) {
    outputStream.write(buffer, 0, length);
}
inputStream.close();
outputStream.close();

What’s next?