Home | Tutorials | Smart-Cloud | Smart-EIS | License | FAQ | Contact

The data API is a component of the smart-cloud framework. It makes the development of database driven applications in Java more convenient and efficient.

Main features:

  1. Unified configuration file

  2. Auto synchronization for database in case of JPA entities (thanks to Hibernate)

  3. Elegant and simple API (proven to work with minimum Java experience)

  4. Single API to communicate with databases with both: JDBC (SQL inside Java) and JPA (No SQL, just Java classes with mapping to database).

  5. Convention for keeping SQL queries inside external files, perfectly makes send to.

  6. Built in caching mechanism.

  7. Built in connection pooling.


The framework run on local file based H2 database by default, so basically no configuration needed. This could be good idea for test automation, proof-of-concepts, or trying things out. However, for real-life applications, you will need to control the configurations.

To manage the configuration of your database(s), please refer to the Config Guide.

Maven dependency:



Basic JDBC Example:

1- src/main/resources/config.properties

#H2 Configurations
hibernate.connection.driver_class = org.h2.Driver
hibernate.connection.url = jdbc:h2:file:./h2db.data

#MySQl Config
#hibernate.connection.driver_class = com.mysql.jdbc.Driver
#hibernate.connection.url = jdbc:mysql://localhost:3306/app?createDatabaseIfNotExist=true

#Oracle Config, you will need to add oracle driver dependncy,
#checkout, option 4(System Path): https://www.mkyong.com/maven/how-to-add-oracle-jdbc-driver-in-your-maven-local-repository/

#hibernate.connection.driver_class = oracle.jdbc.driver.OracleDriver
#hibernate.connection.url = jdbc:oracle:thin:@localhost:1521/orclpdb1
#hibernate.c3p0.preferredTestQuery=SELECT 1 FROM DUAL

hibernate.connection.username = sa
hibernate.connection.password = sa

#To build tables from Entities
#Package to scan for JPA entities, CSV value is allowed for multiple packages

2- src/main/java/com/jk/example/jdbc/Account.java

package com.jk.example.jdbc;

public class Account {
	int id;
	String name;
	double balance;

	public int getId() {
		return id;

	public void setId(int id) {
		this.id = id;

	public String getName() {
		return name;

	public void setName(String name) {
		this.name = name;

	public double getBalance() {
		return balance;

	public void setBalance(double balance) {
		this.balance = balance;


3- src/main/java/com/jk/example/jdbc/Account2.java

package com.jk.example.jdbc;

public class Account2 {
	int accountId;
	String accountName;
	double accountBalance;

	public int getAccountId() {
		return accountId;

	public void setAccountId(int accountId) {
		this.accountId = accountId;

	public String getAccountName() {
		return accountName;

	public void setAccountName(String accountName) {
		this.accountName = accountName;

	public double getAccountBalance() {
		return accountBalance;

	public void setAccountBalance(double accountBalance) {
		this.accountBalance = accountBalance;


4- src/main/java/com/jk/example/jdbc/JdbcBasicExample.java

package com.jk.example.jdbc;

import java.util.List;
import java.util.Map;

import com.jk.db.dataaccess.core.JKDataAccessService;
import com.jk.db.dataaccess.core.JKPopulator;
import com.jk.db.datasource.JKDataAccessFactory;
import com.jk.util.JK;

public class JdbcBasicExample {
	public static void main(String[] args) {
		// Create Dao Instance
		JKDataAccessService dao = JKDataAccessFactory.getDataAccessService();

		System.out.println(System.getProperty("test-value", "N/A"));

		if (!dao.isTableExists("ACCOUNTS")) {
			dao.execute("INSERT INTO ACCOUNTS VALUES(?,?,?)", 5, "Jalal", 100);
			dao.execute("INSERT INTO ACCOUNTS VALUES(?,?,?)", 6, "Ata", 200);
			dao.execute("INSERT INTO ACCOUNTS VALUES(?,?,?)", 7, "Essa", 300);
			dao.execute("INSERT INTO ACCOUNTS VALUES(?,?,?)", 8, "Jamal", 400);
		// return single results
		long result = dao.executeQueryAsLong("SELECT BALANCE FROM ACCOUNTS WHERE ID=?", 5);

		// return single row
		Object[] row = dao.executeQueryAsRow("SELECT * FROM ACCOUNTS WHERE ID=?", 5);

		// return multiple rows
		List<List<Object>> rows = dao.executeQueryAsList("SELECT * FROM ACCOUNTS WHERE BALANCE>?", 100);
		for (List<Object> eachRow : rows) {

		// execute query from file, it will look into "/src/main/resources/jk/sql"
		// folder for the scripts file
		List<List<Object>> rows2 = dao.executeQueryAsList("all_accounts.sql", 100);
		for (List<Object> eachRow : rows2) {

		// execute query and fill directly into a bean, the default is to have the same
		// name in both, bean and the tables
		List<Account> rows3 = dao.executeQueryAsListOfObjects(Account.class, "all_accounts.sql", 100);

		for (Account account : rows3) {

		// execute query and fill directly into bean, and map the fields using Map
		// object

		Map<String, Object> fieldsMapping = JK.toMap("accountId", "id", "accountName", "name", "accountBalance", "balance");
		List<Account2> rows4 = dao.executeQueryAsListOfObjects(Account2.class, fieldsMapping, "all_accounts.sql", 100);
		for (Account2 account : rows4) {

		// execute query with custom populator
		//Create populator that convert build object from ResultSet using Lambda expression
		JKPopulator<Account> accountPopulator = (rs)->{
			Account account = new Account();
			return account;
		//Find multiple records with populator and parameters
		List<Account> accounts = dao.getList("SELECT * FROM accounts WHERE balance>?", accountPopulator, 100);
		for (Account account : accounts) {

		//find single record with populator and paramters
		Account account = dao.find("SELECT * FROM accounts WHERE name=?", accountPopulator, "Jalal");


Basic JPA Example:

1- src/main/java/com/jk/example/jpa/Student.java

package com.jk.example.jpa;

import java.io.Serializable;
import javax.persistence.*;

@Table(name = "student")
public class Student implements Serializable {

	@Column(name = "id")
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Integer id;

	@Column(name = "number")
	private String number;

	@Column(name = "name")
	private String name;

	@Column(name = "avg")
	private Double avg;

	@Column(name = "phone")
	private String phone;

	public void setId(Integer id) {
		this.id = id;

	public Integer getId() {
		return this.id;

	public void setNumber(String number) {
		this.number = number;

	public String getNumber() {
		return this.number;

	public void setName(String name) {
		this.name = name;

	public String getName() {
		return this.name;

	public void setAvg(Double avg) {
		this.avg = avg;

	public Double getAvg() {
		return this.avg;

	public void setPhone(String phone) {
		this.phone = phone;

	public String getPhone() {
		return this.phone;

	public String toString() {
		StringBuffer buf = new StringBuffer();
		buf.append(this.number).append(" ");
		buf.append(this.name).append(" ");
		buf.append(this.avg).append(" ");
		buf.append(this.phone).append(" ");
		return buf.toString();

	public boolean equals(Object obj) {
		if (obj == null) {
			return false;
		return this.getId() == ((Student) obj).getId();

2- src/main/java/com/jk/example/jpa/JPATest.java

package com.jk.example.jpa;

import java.util.List;

import com.jk.db.dataaccess.orm.JKObjectDataAccess;
import com.jk.db.dataaccess.orm.JKObjectDataAccessImpl;

public class JPATest {
	public static void main(String[] args) {
		//Create JPA object
		Student student = new Student();

		//Create JPA dao
		JKObjectDataAccess dao = new JKObjectDataAccessImpl();

		//insert the object in the database using JPA/Hibernate implementation
		int id=student.getId();

		//Retrieve list of objects from database using JPA
		List<Student> list = dao.getList(Student.class);
		for (Student std : list) {

		//Find an object from database using JPA
		Student std = dao.find(Student.class, id);
		std.setName("Updated Jalal");

		//update record in the databse

		//Delete object from database using an IDE
		dao.delete(Student.class, id);


Naming Strategy

By default the framework will map to database tables according to the following rules: 1. Convert the camel-case names to snake-case (underscores) 2. lowercase the names

In case of Oracle: 1. All the names will be converted to upper case 2. Any field with reserved keyword name will be post-fixed with underscore, for exampe a field with uid name will be map to a column with name UID_

Home | Tutorials | Smart-Cloud | Smart-EIS | License | FAQ | Contact