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

Last Updated: March 26, 2021

Steps:

  1. Create Maven project with the following pom.xml:

<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>
	<groupId>com.jalalkiswani</groupId>
	<version>4.0.4</version>
	<artifactId>smart-cloud-data-jdbc-example</artifactId>
	<dependencies>
		<dependency>
			<groupId>com.jalalkiswani</groupId>
			<artifactId>smart-cloud-data</artifactId>
			<version>4.0.8</version>
		</dependency>
	</dependencies>
	<build>
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>3.8.1</version>
				<configuration>
					<source>1.8</source>
					<target>1.8</target>
				</configuration>
			</plugin>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-war-plugin</artifactId>
				<version>3.3.1</version>
				<configuration>
					<failOnMissingWebXml>false</failOnMissingWebXml>
				</configuration>
			</plugin>
		</plugins>
	</build>
</project>

If you are using Eclipse, be sure to refresh your project (select your project→ right click→ Maven→ Update Project)

  1. Create database script file and place it at src/main/resources/script.sql with the following contents:

create table ACCOUNTS(
ID integer generated by default as identity,
NAME varchar(250),
BALANCE double
);
  1. Create config file at src/main/resources/config.properties with the following contents:

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/example?useSSL=false&createDatabaseIfNotExist=true&allowPublicKeyRetrieval=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=root
hibernate.connection.password=123456
hibernate.c3p0.min_size=1
hibernate.c3p0.max_size=4
hibernate.c3p0.timeout=3
hibernate.c3p0.max_statements=50
  1. Create class Account as a model at src/main/java/com/app/models/Account.java with the following contents:

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

}
  1. Create class Account2 as a model at src/main/java/com/app/models/Account2.java with the following contents:

package com.app.models;

/**
 * Model with different field names that 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;
	}

}
  1. Create App class at src/main/java/com/app/App.java with the following contents:

package com.app;

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

import com.app.models.Account;
import com.app.models.Account2;
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 App {
	public static void main(String[] args) {
		// Create Instance
		JKDataAccessService dao = JKDataAccessFactory.getDataAccessService();

		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));



	}
}

Thats it, now run your App class.

Output

Full example source-code can be found at https://github.com/smartapi-com/smart-cloud-data-jdbc


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