AERGO JDBC (Java Database Connectivity) allows database administrators and Java developers to easily view and manipulate data on the blockchain, just like they would using a regular database.
This article will cover what you need to know about JDBC and why we have it implemented.
One of the major bottlenecks in achieving mass adoption of blockchain technology is due to the unfamiliarity of development environments for programmers. In order to address this problem, AERGO has been built to support the SQL language for smart contracts because it is a widely used programming language in a relational database management system (RDMS). There are at least 7 million developers worldwide that are familiar with programming in SQL.
We at AERGO believe that significant productivity can be achieved through SQL as the blockchain application development logic is very familiar for new blockchain developers.
The client application in essence, constantly repeats the process of creating, signing and sending transactions.
This is how you can use SQL as a smart contract in AERGO Enterprise/ SQLnet:
Developers who are already familiar with using SQL may be reluctant to learn Lua and various blockchain APIs separately. We understand this concern and tackle this issue by asking
How can one access our database at the most basic level?
The answer is via JDBC (Java Database Connectivity).
Henceforth, we developed AERGO JDBC — which allows users to use the blockchain the same way as one would with a regular database; and with the added advantage of improving security and reducing the cost of interacting with the blockchain. It allows database administrators and Java developers to easily view and manipulate data on the blockchain.
AERGO JDBC Configuration
AERGO JDBC consists of a Contract and a JDBC (Java Database Connectivity) package.
If you distribute the Lua contract (db.x.lua) shown below to the AERGO blockchain, the DB contract (Address B) becomes a part of the database (DB).
Once a user uses SQL which is part of the DB, AERGO JDBC automatically links the user with the DB contract to call ‘Transaction(A→B), Query(B)’ on the AERGO blockchain.
DB Lua Contract
How to use AERGO JDBC
Data Definition Language (DDL) and Data Manipulation Language (DML) are executed through blockchain transactions.
SELECT is executed through a query, and the result is received in JSON format and set in the resultSet by its fetch size.
Using a URL like below:
- URL jdbc:aergo:<aergo node ip>:<port>@<jdbc contract address>
The following property needs to be registered to execute the Transaction
- User : User’s encrypted private key
- Password : password for encrypted private key
- The amount of data that can be retrieved from the Query at once is limited to the Remote Procedure Call(RPC) max size of (4MB): If the amount of data to be imported exceeds the max size limit, the fetchsize should be adjusted with resultSet.setFetchSize (default: 10 row)
- The DB supported by AERGO is SQLite, so only SQL and DDL supported by SQLite are acceptable.
Features NOT Supported
- Transaction Commits / Rollbacks : The transactions auto commits
- Scrollable Cursor: Not supported
- CallableStatement: Not supported
Integration with Squirrel SQL client
JDBC Driver registration
AERGO JDBC needs to be registered in order to integrate it with the AERGO blockchain. In the Drivers tab, click the ‘+icon’ and register following the example below.
- Name : aergojdbc
- Example URL: jdbc:aergo:<ip:port>@<address>
- Class Name : org.aergojdbc.JDBC
- Add Extra Class Path: aergo-jdbc-1.0.jar
You can also create and connect an alias using the registered JDBC Driver.
To create an alias,
1.Click the + icon on the aliases tab
2. Select driver as the added aergojdbc
3. Fill in the alias name, URL, username, and password as follows. (User Name is encrypted private key, password is password for private key)
Korean language issue
If the Korean language is broken in Squirrel SQL, -Dfile.encoding=UTF-8 needs to be added to the last line of scripts (sh or bat).
- Window bat file :start “SQuirreL SQL Client”/B “%LOCAL_JAVA%” -Dfile.encoding=UTF-8
- Linux sh file: “$JAVACMD” -cp “$CP” -Dfile.encoding=UTF-8
You can download AERGO JDBC and contract file in the link below
In order to test SQL and JDBC, the blockchain is made public as part of AERGO enterprise.
- URL : alpha1.aergo.io:7845
- Constraints: contract DB size is limited to 20mb
Please click the video to understand the AERGO 1.3.0 AERGO JDBC integration process.
Look out for AERGO JDBC 2.0 where there will be a new feature including table permission management and more. To be released soon.