JSQLEditor

JSQLEditor

SetUp

   Step 1. Add the JitPack repository to your build file
    <repositories>
        <repository>
            <id>jitpack.io</id>
            <url>https://jitpack.io</url>
        </repository>
    </repositories>
    
    Step 2. Add the dependency
    <dependency>
        <groupId>com.github.itsoulltd</groupId>
        <artifactId>JSQLEditor</artifactId>
        <version>v1.1.3-RELEASE</version>
    </dependency>

JSQLEditor has 3 ways of connecting with DataSource:

---

- JDBC Connection URL
- JDBC Connection Pool (J2EE/Servlet Container using JNDI Naming)
- Using JPA persistence.xml

JDBC Connection URL

Creating Connections:

Connection conn = new JDBConnection.Builder(DriverClass.MYSQL)
                  		.host("localhost", "3306")
                  		.database("testDB")
                  		.credential("root","towhid")
                  		.build();

Closing Connections :

JDBConnection.close(conn);

JDBC Connection Pool (J2EE/Servlet Container using JNDI Naming)

First Define a context.xml in /src/main/webapp/META-INF/context.xml (:Maven-Project Structure) OR /WebContent/META-INF/context.xml (:J2EE Webapp Structure in Eclipse)

<?xml version="1.0" encoding="UTF-8"?>
<Context>
	<Resource auth="Container" 
		driverClassName="com.mysql.jdbc.Driver" 
		name="jdbc/testDB" 
		type="javax.sql.DataSource" 
		url="jdbc:mysql://localhost:3306/testDB" 
		username="root" password="****"/>
</Context>

Then Using JNDI Naming

JDBConnectionPool.configure("java:comp/env/jdbc/testDB");
Connection conn = JDBConnectionPool.connection();

We can have multiple resource in context.xml, in that case,

JDBConnectionPool.configure("java:comp/env/jdbc/testDB-1");
Connection conn = JDBConnectionPool.connection(); //First one get configured as default.

JDBConnectionPool.configure("java:comp/env/jdbc/testDB-2");
Connection conn = JDBConnectionPool.connection("testDB-2");
.
.
.

Using JPA persistence.xml

First Define a persistence.xml in /src/main/resources/META-INF/persistence.xml OR /src/META-INF/persistence.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1" xmlns="http://xmlns.jcp.org/xml/ns/persistence"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence
        http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
    <persistence-unit name="testDB">
        <!-- <provider>org.hibernate.ejb.HibernatePersistence</provider> -->
        <properties>
             <property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver"/>
             <property name="hibernate.connection.username" value="root"/>
             <property name="hibernate.connection.password" value="****"/>
             <property name="hibernate.connection.url" value="jdbc:mysql://localhost:3306/testDB?useUnicode=true&amp;useJDBCCompliantTimezoneShift=true&amp;useLegacyDatetimeCode=false&amp;serverTimezone=UTC"/>
             <property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5Dialect"/>
             <!--<property name="hibernate.archive.autodetection" value="class"/>-->
             <property name="hibernate.show_sql" value="true"/>
             <property name="hibernate.format_sql" value="true"/>
             <property name="hibernate.hbm2ddl.auto" value="create"/>
        </properties>
   </persistence-unit>
</persistence>

Sample Code:

ORMController controller = new ORMController("persistence-unit-name");
EntityManager em = controller.getEntityManager();
.
.
//We may have an entity that represent person_tbl in database
@Entity
@Table(name="person_tbl")
public class Person{

    @ID
    private int id;
    
    @Column
    private String name;
    ...
}

//Now we can create a service for Person entity.
ORMService<Person> service = new ORMService(em, Person.class);
List<Person> all = (List<Person>) service.read();
//Create a new Person
Person newOne = new Person();
newOne.setName("Jack Gyl");
service.insert(newOne);
//Update
newOne.setName("Jack Gyllenhaal");
service.update(newOne);
//Delete
service.delete(newOne);
//Check exist
service.exist(newOne.getId()); return true if exist in persistence layer.
//Total rows
long count = service.rowCount(); // return number of rows inserted till now.

JSQLEditor has QueryBuilder to create verbose sql statements.

---

Select Query Example:

SQLSelectQuery query = new SQLQuery.Builder(QueryType.SELECT)
				   .columns()
				   .from("Passenger")
				   .build();

SQLSelectQuery query = new SQLQuery.Builder(QueryType.SELECT)
				   .columns("name", "id")
				   .from("Passenger")
				   .build();
                        
SQLQuery query = new SQLQuery.Builder(QueryType.SELECT)
			     .columns("name","age")
			     .from("Passenger")
			     .whereParams(Logic.OR, "id", "age")
			     .build();
            
Predicate predicate = new Where("id")
			.isEqualTo(229)
			.and("age")
			.isGreaterThenOrEqual(24)
			.or("name")
			.isLike("soha");

SQLQuery query = new SQLQuery.Builder(QueryType.SELECT)
			     .columns()
			     .from("Passenger")
			     .where(predicate)
			     .build();
                                
System.out.printls(query.toString()); //Will print the SQL statement, that can be used any JDBC implementation.

//Here we have a generic JDBC implementation for executing the SQL Statements.

SQLExecutor exe = new SQLExecutor(conn); //Use connection creating by JDBConnection class
ResultSet set = exe.executeSelect(query); //take a SQLSelectQuery
Table table = exe.collection(set);
List<Passenger> passengers = table.inflate(Passenger.class);
                                

Count & Distinct

Expression comps = new Expression("name", Operator.EQUAL);
SQLQuery count = new SQLQuery.Builder(QueryType.COUNT)
			     .columns().on("Passenger")
			     .where(comps)
			     .build();
                                    
SQLQuery distinct = new SQLQuery.Builder(QueryType.DISTINCT)
				.columns().from("Passenger")
				.where(comps)
				.build();

Insert, Update & Delete

//Insert
Row nP = new Row()
	.add("name","Peter Thiel")
	.add("age", 51);
	
Property[] values =  nP.getCloneProperties().toArray(new Property[0]);
	
SQLInsertQuery insert = new SQLQuery.Builder(QueryType.INSERT)
				.into("Passenger")
				.values(values)
				.build();
                                
//Update

Predicate compareWith = new Where("id").isEqualTo(autoId);
	
SQLUpdateQuery update = new SQLQuery.Builder(QueryType.UPDATE)
                        	.set(values)
                        	.from("Passenger")
                        	.where(compareWith)
                        	.build();
                            
//Delete
SQLQuery delete = new SQLQuery.Builder(QueryType.DELETE)
			.rowsFrom("Passenger")
			.where(clause)
			.build();

OrderBY, GroupBy, Limit, Offset

ExpressionInterpreter clause = new AndExpression(new Expression("name", Operator.EQUAL)
											, new Expression("age", Operator.GREATER_THAN));
                
//ORderBy
SQLQuery query = new SQLQuery.Builder(QueryType.SELECT)
						.columns()
						.from("Passenger")
						.where(clause)
						.orderBy("id")
						.addLimit(10, 20)
						.build();
                        

//GroupBy
String groupByColumn = ScalerType.COUNT.toAlias("age");
SQLQuery query = new SQLQuery.Builder(QueryType.SELECT)
						.columns("name",groupByColumn)
						.from("Passenger")
						.groupBy("name")
						.having(new Expression(groupByColumn, Operator.GREATER_THAN))
						.orderBy(groupByColumn)
						.build();

Joins

SQLJoinQuery join = new SQLQuery.Builder(QueryType.INNER_JOIN)
						.join("Customers", "CustomerName")
						.on(new JoinExpression("CustomerID", "CustomerID"))
						.join("Orders", "OrderID")
						.on(new JoinExpression("ShipperID", "ShipperID"))
						.join("Shippers", "ShipperName").build();
                        

SQLJoinQuery leftJoin = new SQLQuery.Builder(QueryType.LEFT_JOIN)
						.join("Customers", "CustomerName")
						.on(new JoinExpression("CustomerID", "CustomerID"))
						.join("Orders", "OrderID")
						.orderBy("Customers.CustomerName").build();

JSQLEditor has built-in Entity class (light weight), only required a JDBC Connection.

---

Here are detailed example:

First assume We Have Person.java class that extends from Entity class

@TableName(value = "Person", acceptAll = false)
public class Person extends Entity {
	
    //Must have to be same as declaired property and as name in @PrimaryKey
    @PrimaryKey(name = "uuid", autoIncrement = false)
    private String uuid;
    
    @Column(defaultValue="Mr/Mrs")
    private String name;

    @Column(defaultValue="34", type = DataType.INT)
    private Integer age;

    @Column(defaultValue="true", type = DataType.BOOL)
    private Boolean active;

    @Column(defaultValue="0.00", type = DataType.DOUBLE)
    private Double salary;

    private Date dob;

    @Column(defaultValue="2010-06-21 21:01:01", type=DataType.SQLTIMESTAMP, parseFormat="yyyy-MM-dd HH:mm:ss")
    private Timestamp