Data-Access API


Light Java database API (Plain JDBC and JPA) for simplifying database driven development with Java. It is straight forward approach with minimal required configurations and environment preparation.

Features

  1. Plain JDBC support
  2. ORM support based on JPA standards with hibernate as implementation.
  3. Transparent connection pooling support (Based on Apache DBCP)
  4. Support for any environment (Web or Desktop)
  5. Unified configurations for JDBC and ORM through unified simple configuration file (No need for persisitnce.xml or hibernate configurations)
  6. straight forward API

Usage

<!-- https://mvnrepository.com/artifact/com.jalalkiswani/jk-db -->
<dependency>
 <groupId>com.jalalkiswani</groupId>
 <artifactId>jk-db</artifactId>
 <version>0.0.9-1</version>
</dependency>
  • Create configurations named jk-db.properties in project working directory just beside the xml file in case if mavenjar project, and inside WEB-INF folder in case of maven war project, with the following contents:
db-driver-name=com.mysql.jdbc.Driver
db-url=jdbc:mysql://localhost:3306/app
db-user=root
db-password=123456
# The below used to for JPA entities packages for auto. scanning of entities
# db-entities-packages=com.jk

Note: the above config file is optional and also the above configurations are already the defaults, so no need to set it up. That’s it, now you can start us the API, have a look at the example sections for more details on the API

JK-DB in web-applications

You can use JK-DB in web-applications as well, just place the jk-db.properties file inside /src/main/webapp/WEB-INF/folder.

Design

Plain JDBC Examples:

 

package com.jk.db.test;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import javax.sql.rowset.CachedRowSet;

import org.junit.Test;

import com.jk.db.dataaccess.plain.JKDbIdValue;
import com.jk.db.dataaccess.plain.JKFinder;
import com.jk.db.dataaccess.plain.JKPlainDataAccess;
import com.jk.db.dataaccess.plain.JKUpdater;
import com.jk.db.datasource.JKDataSourceFactory;
import com.jk.db.examples.beans.Department;
import com.jk.util.JK;

/**
 * Full Plain API exmaple.
 *
 * @author Jalal Kiswani Jul 2, 2016
 */
public class FullPlainDataAccessApiTest extends AbstractDBTest {
 static JKPlainDataAccess dataAccess = JKDataSourceFactory.getPlainDataAccess();

 /**
 * Execute query as single object.
 */
 public void executeQueryAsSingleObject() {
    Department dep = dataAccess.executeQueryAsSingleObject(Department.class, "SELECT * FROM hr_departments WHERE dep_id=?", 3);
    JK.printBlock("executeQueryAsSingleObject() : ", dep);
 }

 /**
 * Execute query as object list.
 */
 public void executeQueryAsObjectList() {
 List<Department> list = dataAccess.executeQueryAsObjectList(Department.class, "SELECT * FROM hr_departments WHERE dep_id<>?", 3);
 JK.printBlock("executeQueryAsObjectList() : ", list);
 }

 /**
 * Execute query as array.
 */
 public void executeQueryAsArray() {
 Object[] list = dataAccess.executeQueryAsArray("SELECT * FROM hr_departments WHERE dep_id<?", 4);
 JK.printBlock("executeQueryAsArray() : ", list);
 }

 /**
 * Gets the system date.
 *
 * @return the system date
 */
 public void getSystemDate() {
 Date systemDate = dataAccess.getSystemDate();
 JK.print("getSystemDate() : ", systemDate);
 }

 /**
 * Execute query as list.
 */
 public void executeQueryAsList() {
 List<List<Object>> list = dataAccess.executeQueryAsList("SELECT * FROM hr_departments WHERE dep_id>?", 1);
 JK.printBlock("executeQueryAsList() : ", list);
 }

 /**
 * Execute query as string.
 */
 public void executeQueryAsString() {
 String result = dataAccess.executeQueryAsString("SELECT * FROM hr_departments WHERE dep_id>?", ",", "\n", 1);
 JK.printBlock("executeQueryAsString() : ", result);
 }

 /**
 * Gets the rows count.
 *
 * @return the rows count
 */
 public void getRowsCount() {
 int rowsCount = dataAccess.getRowsCount("SELECT dep_name FROM hr_departments WHERE 1=?", 1);
 JK.printBlock("getRowsCount() :", rowsCount);
 }

 /**
 * Gets the next id.
 *
 * @return the next id
 */
 public void getNextId() {
 Long nextId = dataAccess.getNextId("hr_departments", "dep_id");
 JK.print("getNextId() : ", nextId);
 }

 /**
 * Exeute single output query.
 */
 public void exeuteSingleOutputQuery() {
 Object name = dataAccess.exeuteSingleOutputQuery("SELECT dep_name FROM hr_departments WHERE dep_id=?", 2);
 JK.print("exeuteSingleOutputQuery() : ", name);
 }

 /**
 * Execute.
 */
 public void execute() {
 dataAccess.execute("UPDATE hr_departments SET dep_name=? WHERE dep_id=?", "I.T.2", 1);
 JK.print("execute()");
 }

 /**
 * Execute query as id value.
 */
 public void executeQueryAsIdValue() {
 List<JKDbIdValue> list = dataAccess.executeQueryAsIdValue("SELECT * FROM hr_departments WHERE dep_id>?", 0);
 JK.print("executeQueryAsIdValue() : ", list);
 }

 /**
 * Execute query as cached row set.
 */
 public void executeQueryAsCachedRowSet() {
 CachedRowSet rs = dataAccess.executeQueryAsCachedRowSet("SELECT * FROM hr_departments where 1=?", 1);
 JK.printBlock("executeQueryAsCachedRowSet() : ", rs);
 }

 /**
 * Execute update.
 */
 // Complex Tables and Quries
 public void executeUpdate() {
 dataAccess.executeUpdate(new JKUpdater() {

 @Override
 public void setParamters(PreparedStatement ps) throws SQLException {
 int counter = 1;
 ps.setString(counter++, "IT-1");
 ps.setInt(counter++, 1);
 }

 @Override
 public String getQuery() {
 return "UPDATE hr_departments SET dep_name=? WHERE dep_id=?";
 }
 });
 JK.print("executeUpdate()");
 }

 /**
 * Gets the list.
 *
 * @return the list
 */
 public void getList() {
 List<Department> list = dataAccess.getList(new JKFinder() {

 @Override
 public void setParamters(PreparedStatement ps) throws SQLException {
 ps.setInt(1, 2);
 }

 @Override
 public Department populate(ResultSet rs) throws SQLException {
 Department d = new Department();
 d.setId(rs.getInt("dep_id"));
 d.setName(rs.getString("dep_name"));
 return d;
 }

 @Override
 public String getQuery() {
 return "SELECT * FROM hr_departments WHERE dep_id>?";
 }
 });
 JK.printBlock("getList() :", list);
 }

 /**
 * Find record.
 */
 public void findRecord() {
 final int id = 3;
 Department d = dataAccess.findRecord(new JKFinder() {

 @Override
 public void setParamters(PreparedStatement ps) throws SQLException {
 ps.setInt(1, id);
 }

 @Override
 public Department populate(ResultSet rs) throws SQLException {
 Department d = new Department();
 d.setId(rs.getInt("dep_id"));
 d.setName(rs.getString("dep_name"));
 return d;
 }

 @Override
 public String getQuery() {
 return "SELECT * FROM hr_departments WHERE dep_id=?";
 }
 }, "hr_departments", id);
 JK.printBlock("findRecord() : ", d);
 }

}

ORM Examples

package com.jk.db.test;

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

import org.junit.FixMethodOrder;
import org.junit.Test;
import org.junit.runners.MethodSorters;

import com.jk.db.dataaccess.orm.JKOrmDataAccess;
import com.jk.db.datasource.JKDataSourceFactory;
import com.jk.db.examples.entities.Department;
import com.jk.util.JK;

/**
 * ORM Example Note : the method naming to ensure the methods ordering.
 *
 * @author Jalal Kiswani Jul 2, 2016
 */
public class BasicOrmTest extends AbstractDBTest {

 /** The id. */
 public final int ID = 100;
 static JKOrmDataAccess orm = JKDataSourceFactory.getOrmDataAccess();

 /**
 * B insert.
 */
 public void insert() {
 Department dep = new Department();
 dep.setId(ID);
 dep.setName("My Department");
 orm.insert(dep);
 JK.print("insert() : ", dep);
 }

 /**
 * C update.
 */
 public void update() {
 Department dep = orm.find(Department.class, ID);
 dep.setName("New Name");
 orm.update(dep);
 JK.print("update() :", dep);
 }

 /**
 * D delete.
 */
 public void delete() {
 orm.delete(ID, Department.class);
 JK.print("delete()");
 }

 /**
 * A get list.
 */
 public void getList() {
 List<Department> list = orm.getList(Department.class);
 JK.print("getList() : ", list);
 }

 /**
 * E get list with filter.
 */
 public void getListWithFilter() {
 Map<String, Object> paramters = JK.toMap("name", "Sales");
 List<Department> list = orm.getList(Department.class, paramters);
 JK.print("getListWithFilter() : ", list);
 }

 /**
 * F execute query.
 */
 public void executeQuery() {
 List<Department> list = orm.executeQuery(Department.class, "SELECT d FROM Department d WHERE d.id>?1", 1);
 JK.print("executeQuery(): ", list);
 }

}