Searching...
Saturday, 21 December 2013

JOOQ Simple CRUD operation example

21:53

Environment Used

Java 1.7

JOOQ 3.2

Eclipse

MySql

1. What is JOOQ?

Java Object Oriented Querying is a light weight database mapping API in java that implements the active record pattern. The main objective of this API to provide domain specific language to construct queries and generating classes from database schema. See more about jooq

2. Setup JOOQ Project

Create a java project and add below jars in the class path (Click here to download jars)

image

3. Generate Code

jOOQ generates Java code from your database and lets you build typesafe SQL queries through its fluent API. See this example to Generate Jooq code

4. Create a java class to create connection

package com.pretech.utility;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class JdbcConnection {
	
	public static Connection getConnection()
	{Connection conn = null;
	String userName = "root";
	String password = "root";
	String url = "jdbc:mysql://localhost:3306/studentdatabase";
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection(url, userName, password);
		} catch (InstantiationException | IllegalAccessException
				| ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
}

5. Create a main class for CRUD operation

package com.pretech.master;
import org.jooq.DSLContext;
import org.jooq.Record;
import org.jooq.Result;
import org.jooq.SQLDialect;
import org.jooq.impl.DSL;
import org.jooq.types.UInteger;
import com.pretech.jooq.tables.Student;
import com.pretech.jooq.tables.records.StudentRecord;
import com.pretech.utility.JdbcConnection;
public class StudentMaster {
	public static void main(String[] args) {
		try {
			DSLContext create = DSL.using(JdbcConnection.getConnection(),
					SQLDialect.MYSQL);
			// INSERTING STUDENT DETAILS
			create.insertInto(Student.STUDENT, Student.STUDENT.ID,
					Student.STUDENT.NAME, Student.STUDENT.STANDARD)
					.values(UInteger.valueOf(1), "Rajesh", "10th Standard")
					.execute();
			System.out.println("STUDENT RECORDS AFTER INSERTING");
			// SELECTING STUDENT RECORDS
			Result<Record> result0 = create.select().from(Student.STUDENT)
					.fetch();
			for (Record r : result0) {
				StudentRecord stud = (StudentRecord) r;
				System.out.println(stud);
			}
			create.update(Student.STUDENT)
					.set(Student.STUDENT.STANDARD, "9th standard")
					.where(Student.STUDENT.ID.equal(UInteger.valueOf(1))).execute();
			System.out.println("STUDENT RECORDS AFTER UPDATING");
			// SELECTING STUDENT RECORDS
			Result<Record> result1 = create.select().from(Student.STUDENT)
					.fetch();
			for (Record r : result1) {
				StudentRecord stud = (StudentRecord) r;
				System.out.println(stud);
			}
			create.delete(Student.STUDENT).execute();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

6. Final structure of the project


image


7. Output



image



8. Download this example


Download JOOQ Simple CRUD operation example

0 comments: