J-Framework Data

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.

Basic Usage

  • For JDBC data access, create JKDataAccessService as follows, then enjoy calling SQL statements on database with which could be the easiest JDBC approach ever.

 	JKDataAccessService jdbc = JKDataAccessFactory.getDataAccessService();

  • For JPA and ORM Mapping, place your JPA entities in com.app.entities package, then create JKObjectDataAccess as follows, thats it, now you have a wonderful set of methods to be used for your database driven applications.

	JKObjectDataAccess orm = JKDataAccessFactory.getObjectDataAccessService();

You can always change the default package names of the entities using db-entities-packages property in the config file src/main/resources/config.properties.

Even if you would like to use JDBC in your code, I always prefer to build JPA entities to to create the database structure on the dev environment, for me, its convenient, easy, and Java :)

Main features

  1. Unified configuration file for both, JDBC and JPA.

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

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

  4. Convention for keeping SQL queries inside external files, perfectly makes sense to.

  5. Built-in caching mechanism.

  6. Built-in connection pooling.

  7. Multi-database support

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

Configurations

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 go with production grade database engines with optimized configurations.

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

Basic JDBC Example

  1. 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.runScript("/script.sql");
			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
		JK.line();
		long result = dao.executeQueryAsLong("SELECT BALANCE FROM ACCOUNTS WHERE ID=?", 5);
		JK.print(result);

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

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

		JK.line();
		// 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) {
			JK.print(eachRow);
		}

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

		for (Account account : rows3) {
			JK.print(account);
		}

		// execute query and fill directly into bean, and map the fields using Map
		// object
		JK.line();

		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) {
			JK.print(account);
		}

		// execute query with custom populator
		//Create populator that convert build object from ResultSet using Lambda expression
		JKPopulator<Account> accountPopulator = (rs)->{
			Account account = new Account();
			account.setId(rs.getInt("id"));
			account.setName(rs.getString("name"));
			account.setBalance(rs.getInt("balance"));
			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) {
			System.out.println(JK.buildToString(account));
		}
		
		//find single record with populator and paramters
		Account account = dao.find("SELECT * FROM accounts WHERE name=?", accountPopulator, "Jalal");
		System.out.println(JK.buildToString(account));
		
		

	}
}

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.*;

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

	@Id
	@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;
	}

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

	@Override
	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();
		student.setNumber("121212");
		student.setName("Jalal");
		student.setPhone("123456789");
		student.setAvg(90.0);

		//Create JPA dao
		JKObjectDataAccess dao = new JKObjectDataAccessImpl();
		
		//insert the object in the database using JPA/Hibernate implementation
		dao.insert(student);
		int id=student.getId();
		
		//Retrieve list of objects from database using JPA 
		List<Student> list = dao.getList(Student.class);
		for (Student std : list) {
			System.out.println(std);
		}
		
		//Find an object from database using JPA
		Student std = dao.find(Student.class, id);
		std.setName("Updated Jalal");
		
		//update record in the databse
		dao.update(std);
		
		//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_

Default Configuration

Below are the default configurations, which are set in the default.config.porperties in the framework

#H2 Configurations
#hibernate.connection.driver_class = org.h2.Driver
#hibernate.connection.url = jdbc:h2:file:~/.jk/h2-db/h2db2.data
#hibernate.dialect = org.hibernate.dialect.H2Dialect

#MySQl Config
#hibernate.connection.driver_class = com.mysql.jdbc.Driver
#hibernate.connection.url = jdbc:mysql://localhost:3306/app?createDatabaseIfNotExist=true
#hibernate.dialect = org.hibernate.dialect.MySQL57Dialect
#hibernate.connection.username = root
#hibernate.connection.password = 123456

#Oracle Config, you will need to add oracle driver dependency,
#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.dialect = org.hibernate.dialect.Oracle10gDialect
#hibernate.c3p0.preferredTestQuery=SELECT 1 FROM DUAL

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

#To build database structure from Entities
#hibernate.hbm2ddl.auto=update
#Package to scan for JPA entities, CSV value is allowed for multiple packages
#db-entities-packages=com.app,

nosql.url=mongodb://localhost:27017/
nosql.db.name=app
nosql.db.user=
nosql.db.password=

hibernate.connection.url = jdbc:h2:file:~/.jk/h2-db/h2db2.data;IGNORECASE=TRUE;CASE_INSENSITIVE_IDENTIFIERS=TRUE;DATABASE_TO_UPPER=FALSE;
hibernate.connection.username = sa
hibernate.connection.password = sa

#hibernate.c3p0.initialPoolSize=1
hibernate.c3p0.min_size=1
hibernate.c3p0.max_size=100

#important to to avoid AUTO id error in JPA: https://stackoverflow.com/questions/49813666/table-dbname-hibernate-sequence-doesnt-exist
hibernate.id.new_generator_mappings=false
#hibernate.physical_naming_strategy: already calculated internally
#this will ensure escaping all fields that are keywords
hibernate.globally_quoted_identifiers=true
#hibernate.use_sql_comments=false
hibernate.format_sql=true
hibernate.show_sql=false
hibernate.hbm2ddl.auto=update

#hot deployment
hibernate.c3p0.privilegeSpawnedThreads=true
hibernate.c3p0.contextClassLoaderSource=library

#connections cleanup
#seconds, connections stay un-used for n seconds 
hibernate.c3p0.timeout=3600
hibernate.c3p0.unreturnedConnectionTimeout=3600
#millis, this time could be needed in the init phase of the pool, need verification
hibernate.c3p0.checkoutTimeout=20000
hibernate.c3p0.testConnectionOnCheckout=true
#refresh the connection anyways after this timeout
#hibernate.c3p0.maxConnectionAge=18000
hibernate.c3p0.debugUnreturnedConnectionStackTraces=true

#millis-seconds
hibernate.c3p0.acquire_increment=1
hibernate.c3p0.acquireRetryDelay=10000
hibernate.c3p0.acquireRetryAttempts=1

hibernate.c3p0.idleConnectionTestPeriod=30

hibernate.c3p0.maxStatements=1000
hibernate.c3p0.maxStatementsPerConnection=100
hibernate.c3p0.numHelperThreads=10
#hibernate.c3p0.maxAdministrativeTaskTime=100
#hibernate.c3p0.automaticTestTable=
#hibernate,c3p0,plain,dbcp
jk.data.datasource.impl=hibernate
hibernate.connection.autocommit=true

Git DataAccess

JKGitWrapper is a full wrapper that enables full communication with a Git repository.

Full example can be found below.

	String url = "Remote Repository URL";
	String userName = "UserName";
	String password = "Password";
	String localPath = "local-source-path";

	JKGitWrapper gw = new JKGitWrapper();

	gw.url(url).user(userName).password(password).localPath(localPath);

	Git git = gw.cloneRepo();

	JKIOUtil.createDirectory(gw.getLocalPath(), "dir1");
	JKIOUtil.createDirectory(gw.getLocalPath(), "dir2");
	JKIOUtil.createDirectory(gw.getLocalPath(), "dir2/dir2-1");
	JKIOUtil.createDirectory(gw.getLocalPath(), "dir3");

	JKIOUtil.writeDataToFile("Hello from uncle Jalal0", new File(gw.getLocalPath(), "dir1/test.txt"));
	JKIOUtil.writeDataToFile("Hello from uncle Jalal1", new File(gw.getLocalPath(), "dir2/test.txt"));
	JKIOUtil.writeDataToFile("Hello from uncle Jalal2", new File(gw.getLocalPath(), "dir2/dir2-1/test.txt"));
	JKIOUtil.writeDataToFile("Hello from uncle Jalal3", new File(gw.getLocalPath(), "dir3/test.txt"));

	gw.add(git);
	gw.commit(git);
	gw.push(git);

Multi-Database Support

In the config file, you can add a prefix to the same configuration used in normal database setup.

mydb.hibernate.connection.url =jdbc:oracle:thin:@0.0.0.0:1521:DB
mydb.hibernate.connection.username =user
mydb.hibernate.connection.password = pass

Then, in your dataaccess class, create an instance of the JKDataAccessService with the prefix:

JKDataAccessService service=JKDataAccessFactory.getDataAccessService("mydb);

To unify the database prefix in one place, it is recommanded to create a Data Access class that extends one of JKDataAccessImpl classes, and pass the prefix as constructor parameter to super class, in this case, other team members shall only extends your new DataAccess class to connect to the right database.