JDBC Example

<project xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>com.jalalkiswani</groupId>
		<artifactId>j-app-webstack</artifactId>
		<version>7.0.0-SNAPSHOT</version>
	</parent>
	<artifactId>j-framework-data-jdbc-example</artifactId>
</project>

hibernate.connection.url=jdbc:h2:file:./h2db.data
hibernate.connection.username=sa
hibernate.connection.password=


#MySql config
#hibernate.connection.url = jdbc:mysql://localhost:3306/example2?useSSL=false&createDatabaseIfNotExist=true&allowPublicKeyRetrieval=true
#hibernate.connection.username=root
#hibernate.connection.password=123456


#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.url = jdbc:oracle:thin:@localhost:1521/orclpdb1
#hibernate.c3p0.preferredTestQuery=SELECT 1 FROM DUAL

hibernate.c3p0.min_size=1
hibernate.c3p0.max_size=1
hibernate.c3p0.timeout=3
hibernate.c3p0.max_statements=50

package com.app.models;

/**
 * Model with same field names as DB
 * @author Jalal Kiswani
 *
 */
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;
	}

}

package com.app.models;

/**
 * Model with different field names than the DB
 * @author Jalal Kiswani
 */
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;
	}

}

package com.app;

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

import com.app.models.Account;
import com.app.models.Account2;
import com.jk.core.util.JK;
import com.jk.data.dataaccess.JKDataAccessFactory;
import com.jk.data.dataaccess.core.JKDataAccessService;
import com.jk.data.dataaccess.core.JKPopulator;

public class App {
	public static void main(String[] args) throws InterruptedException {
		// Create Instance
		JKDataAccessService dao = JKDataAccessFactory.getDataAccessService();
		JK.line();
		if (!dao.isTableExists("ACCOUNTS")) {
			dao.beginTransaction();
			dao.runScript("/h2-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);
			dao.closeTransaction(true);
		}
		// 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));
	}
}