Skip to content

Latest commit

 

History

History
285 lines (228 loc) · 7.11 KB

README.md

File metadata and controls

285 lines (228 loc) · 7.11 KB

DAO Simple Solution

Description

The main purpose of the API is to implement DAO pattern that uses java.sql package to access database.

There are three topics:

  • DTO File Generator
  • DAO Pattern
  • Statement Builder

Databases the API supports are:

  • PostgreSQL
  • Oracle
  • SQLServer
  • HANA

Maven

<dependency>
	<groupId>org.uia.solution</groupId>
	<artifactId>uia-dao</artifactId>
	<version>0.3.0</version>
</dependency>

Other topics

Core Concept

The implementation of the API uses java.sql package tos access the database. You can use pre-implemented Dao class or inherit it to provide meaningful methods to CRUD data.

When you use the API, you can

  • Use annotations to design DTO classes without any XML file.
  • No need to implement the standard CRUD methods.
  • Minimum implementation, maximum functionality.

Key Points

classes

  • TableDao - The generic DAO to access a table.
  • ViewDao - The generic DAO to access a view.
  • TableDaoHelper - The DAO helper for a table.
  • ViewDaoHelper - The DAO helper for a view.

annotations

  • TableInfo - annotate a class for a table.
  • ViewInfo - annotate a class for a view.
  • ColumnInfo - annotate attributes for the columns.
  • SelectInfo - annotate methods to select data.
  • UpdateInfo - annotate methods to update data.
  • DeleteInfo - annotate methods to delete data.

How To Use

  1. Define a DTO for a table.

    package a.b.c;

@TableInfo(name = "job_detail")
public class JobDetail {

    @ColumnInfo(name = "id", primaryKey = true)
    private String id;

    @ColumnInfo(name = "job_id")
    private String jobId;

    @ColumnInfo(name = "job_detail_name")
    private String jobDetailName;
}

  2. Define a DTO for a view. Setup inherit levels if the class inherits from some class.

    package a.b.c;

@ViewInfo(name = "view_job_detail")
public class ViewJobDetail extends JobDetail {

    @ColumnInfo(name = "job_name")
    private String jobName;
}

  3. Create a factory and load definition of DTO classes.

    DaoFactory factory = new DaoFactory();
factory.load("a.b.c");

  4. Run CRUD on a table

    // create a dao object
TableDao<JobDetail> dao = new TableDao(
        conn, 
        factory.forTable(JobDetail.class));
dao.insert(...);
dao.update(...);
dao.deleteByPK(...);
List<JobDetail> result = dao.selectAll();
JobDetail one = dao.selectByPK(...);

    or

    // create a dao object
TableDao<JobDetail> dao = factory.createTableDao(
        JobDetail.class,
        conn);
dao.insert(...);
dao.update(...);
dao.deleteByPK(...);
List<JobDetail> result = dao.selectAll();
JobDetail one = dao.selectByPK(...);

  5. Run a SELECT on a view

    // create a dao object
ViewDao<ViewJobDetail> dao = new ViewDao(
        conn, 
        factory.forView(ViewJobDetail.class));
List<ViewJobDetail> result = dao.selectAll();

    or

    // create a dao object
ViewDao<ViewJobDetail> dao = factory.createViewDao(
        ViewJobDetail.class,
        conn);
List<ViewJobDetail> result = dao.selectAll();

Custom DAO

Use simple SQL statements instead of writing Spaghetti SQL.

Inherit from uia.dao.TableDao to access a table

public class JobDetailDao extends TableDao<JobDetail> {

    public JobDetailDetail(Connection conn) {
        super(conn, factory.forTable(JobDetail.class));
    }

    public List<JobDetail> selectByName(String name) {
        // Get the SELECT method
        DaoMethod<JobDetail> method = this.tableHelper.forSelect();

        // Prepare a statement with custom WHERE criteria.
        try (PreparedStatement ps = this.conn.prepareStatement(method.getSql() + "WHERE job_detail_name like ?")) {
            ps.setString(1, name);

            // Execute
            try (ResultSet rs = ps.executeQuery()) {
                // Convert result to DTO object list
                return method.toList(rs);
            }
        }
    }
}

Inherit from uia.dao.ViewDao for access a view

public class ViewJobDetailDao extends ViewTableDao<ViewJobDetail> {

    public ViewJobDetailDetail(Connection conn) {
        super(conn, factory.forTable(ViewJobDetail.class));
    }

    public List<ViewJobDetail> selectByName(String name) {
        // Prepare a statement with custom WHERE criteria.
        try (PreparedStatement ps = this.conn.prepareStatement(getSql() + "WHERE job_detail_name like ?")) {
            ps.setString(1, name);

            // Execute
            try (ResultSet rs = ps.executeQuery()) {
                return toList(rs);
            }
        }
    }
}

Statement Builder

AND

Example: c1=? and (c2 between ? and ?) and c3 like ? and c4<>?

SimpleWhere and = Where.simpleAnd()
    .eq("c1", "abc")
    .between("c2", "123", "456")
    .likeBegin("c3", "abc")
    .notEq("c4", "def");

OR

Example: c1=? or (c2 between ? and ?) or c3 like ? or c4<>?

SimpleWhere or = Where.simpleOr()
    .eqOrNull("c1", "abc")
    .between("c2", "123", "456")
    .likeBeginOrNull("c3", "abc")
    .notEq("c4", "def");

AND + OR

Example #1: (A=? and B=?) or (C=? and D=?)

SimpleWhere and1 = Where.simpleAnd()
        .eq("A", "A1")
        .eq("B", "B1");

SimpleWhere and2 = Where.simpleAnd()
        .eq("C", "C1")
        .eq("D", "D1");

WhereOr where = Where.or(and1, and2);

Example #2: (A=? or B=?) and (C=? or D=?)

SimpleWhere or1 = Where.simpleOr()
        .eq("A", "A1")
        .eq("B", "B1");

SimpleWhere or2 = Where.simpleOr()
        .eq("C", "C1")
        .eq("D", "D1");

WhereAnd where = Where.and(or1, or2);

SelectStatement

Used to create a READY TO BE EXECUTED PreparedStatement object.

Example: SELECT id,revision,sch_name FROM pms_schedule WHERE state_name=? ORDER BY id

// where
SimpleWhere where = Where.simpleAnd()
        .notEq("state_name", "on");

// select
SelectStatement select = new SelectStatement("SELECT id,revision,sch_name FROM pms_schedule")
    	.where(where)
    	.orderBy("id");
try (PreparedStatement ps = select.prepare(conn)) {
    try (ResultSet rs = ps.executeQuery()) {
    	while (rs.next()) {
        	System.out.println(rs.getObject(3));
    	}
    }
}

DTO File Generator

The tool can generate the Java file based on the table and view schema in the database.

String sourceDir = "d:/my_project/src/main/java";   // save path
String dtoPackage = "a.b.c";                        // package name
String tableName = "job_detail";                    // table name
String viewName = "view_job_detail";                // view name

// PostgreSQL
Database db = new PostgreSQL(host, port, dbName, user, password);

// save to files
DaoFactoryTool tool = new DaoFactoryTool(db);
// save to files: table
tool.toDTO(sourceDir, dtoPackage, tableName)
// save to files: view
tool.toDTO(sourceDir, dtoPackage, viewName)