Provides many useful CRUD, Pagination, Sorting operations with Thread-safe Singleton support through the native JDBC API.

Related tags

Database java orm jdbc
Overview

BangMapleJDBCRepository

  • Inspired by the JpaRepository of Spring framework which also provides many capabilities for the CRUD, Pagination and Sorting operations.
  • Maximizing the performance by using the native JDBC API with built-in Thread-safe Singleton pattern for your Data Access Objects (DAO).
  • Annotate your DTO with @Table, @Id, @Column to map your DTO to the table (Inspired by the ORM).
  • Friendly-to-use syntax to retrieving your DAO by classpath or class type (Inspired by the Store of the NgRX library for Angular framework): Store.select(UsersDAO.class) to get UsersDAO instance. Store.select("com.bangmaple.dao.UsersDAO") to also get the UsersDAO instance.

🛠 Features:

  • CRUD Operations.
  • Paging operations.
  • Sorting operations.

🕹 Current situation:

  • Support for Microsoft SQL Server (MSSQL).

🔌 To do:

  • Add support for MySQL and PostgreSQL.

How to use:

  • Create a new project then add this library by downloading the jar file from the Release tab.
  • Or you can clone this repository without having download the jar file.
  • Remember to also add the JDBC driver.
  • For Servlet environment (non-context.xml users), you can configure like this by creating a new ServletListener class or the class you just created that implementing the ServletContextListener interface then override the contextInitialized method:
import bangmaple.jdbc.utils.ConnectionManager;

import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
import javax.servlet.annotation.WebListener;

@WebListener
public class ServletListener implements ServletContextListener {
  @Override
  public void contextInitialized(ServletContextEvent sce) {
    ConnectionManager.PROTOCOL = "jdbc:sqlserver";
    ConnectionManager.HOST = "localhost";
    ConnectionManager.PORT = 1433;
    ConnectionManager.USERNAME = "sa";
    ConnectionManager.PASSWORD = "IloveFPT";
  }
} 
  • If you use META-INF/context.xml for Database datasource then remember to set the name property of the Resource tag as JDBCRepository or the ConnectionManager will not initialize your application, you should configure like this (You don't need to implement ServletListener, the ConnectionManager will automatically initialize the Connection Pool if you correctly set-up the context.xml file):
<?xml version="1.0" encoding="UTF-8"?>
<Context antiJARLocking="true" path="/">
    <Resource name="JDBCRepository" auth="Container" type="javax.sql.DataSource"
              username="sa" password="IloveFPT"
              driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
              url="jdbc:sqlserver://localhost:1433;databaseName=YOUR_DATABASE_NAME"
              maxActive="8" maxIdle="4"/>
</Context>
  • For non-Servlet environment, navigate to your Main class and configure like this:
import bangmaple.jdbc.dao.base.Store;
import bangmaple.jdbc.repository.JdbcRepository;
import bangmaple.jdbc.utils.ConnectionManager;

public class Main {
  public static void main(String[] args) throws Exception {
    JdbcRepository.DEBUG = true;
    ConnectionManager.PROTOCOL = "jdbc:sqlserver";
    ConnectionManager.HOST = "localhost";
    ConnectionManager.PORT = 1433;
    ConnectionManager.USERNAME = "sa";
    ConnectionManager.PASSWORD = "IloveFPT";
  }
}

❗️ Appendix:

  • If you want to show the SQL Query while executing the methods, there is a JdbcRepository.DEBUG variable needed to be set as true. The default value is false.
  • You may notice there is a ConnectionManager class.
    • This class responsible for getting the Connection instance.
    • If you use Data Source way, there is a file context.xml in the META-INF folder.
    • If you chose the way to create a new context.xml file, please set the name of the data source by JDBCRepository or the ConnectionManager won't initialize your connection pool.
Class Variable Description
JdbcRepository DEBUG (boolean) Responsible for logging SQL queries while running the application.
ConnectionManager PROTOCOL (String) The protocol for connecting to the database vendor. For MSSQL: jdbc:sqlserver
ConnectionManager HOST (String) The host of the SQL server. Example: localhost
ConnectionManager PORT (Integer) The port number of the SQL server. Example: 1433
ConnectionManager USERNAME (String) The username for logging in to the SQL server. Example: sa
ConnectionManager PASSWORD (String) The password for logging in to the SQL server. Example: IloveFPT
  • In order to use the JDBC Repository, you will need DTO and DAO. Let we configure these:
import bangmaple.jdbc.annotations.Column;
import bangmaple.jdbc.annotations.Id;
import bangmaple.jdbc.annotations.Table;

@Table(name = "users", catalog = "users_management", schema = "dbo")
public class UsersDTO {
  @Id
  @Column(value = "username")
  private String username;

  @Column(value = "password")
  private String password;

  @Column(value = "fullname")
  private String fullname;

  @Column(value = "role")
  private String role;
  
  //Constructor + Getter + Setter + ToString
} 
Annotation Can be applied on Description
Table Class Make a class become an object for communicating with the database corresponding with the current mapping table. name for specifying the current mapping table, catalog for specifying the current mapping database name.
Id Field Mark a field as primary key column. Each DTO class must have at least 1 Id annotation applied only for only one field.
Column Field Mark a field to be mapped with the corresponding table's column. Use value to specify the value.
  • Now for the DAO class, create a normal class with a private constructor to respect the Singleton pattern.
import bangmaple.dto.UsersDTO;
import bangmaple.jdbc.utils.ConnectionManager;
import bangmaple.jdbc.dao.base.Store;

public class UsersDAO extends Store<UsersDTO, String> {
  private UsersDAO() {}
}
  • You may notice our DAO class extends the Store class which will responsible for storing the DAO classes only one instance each class by the Thread-safe Singleton pattern.
    • Why you named the class is Store:
      • Inspired by the NgRx (@ngrx/store) library for the Angular framework.
    • What is Singleton pattern?
      • It is a pattern to allow class to have only one and one instance during the application's lifecycle.
      • Preventing initializing many instances of a class to prevent memory leaks.
    • Why Thread-safe Singleton pattern?
      • I believe that in a Multithreading environment like Servlet, threads will modify/use the instance safely for CRUD operations by applying this pattern.
  • There is one thing that extends the Store class requires two generic types T and ID:
    • T stands for the matching DTO class. For example the UsersDTO class.
    • ID stands for the matching Id of the DTO class (table) type. For example, my annotated Id field is String type.
    • The result would be Store<UsersDTO, String> instead of Store<T, ID>. For that, we have implemented our CRUD, Pagination, Sorting operations for our application.

To retrieve the UsersDAO instance, we have to select the instance:

import bangmaple.jdbc.dao.base.Store;

public class Main {
  public static void main(String[] args) {
    UsersDAO dao = Store.select(UsersDAO.class);
  }
}

This is similar to the usual way that we do:

UsersDAO dao = new UsersDAO();

Please don't initialize the UsersDAO instance by using the new keyword, this is against the Singleton pattern. Luckily, we have the implemented private constructor to prevent that thing to be happening.

Now we have all the methods we need, I will list them all by the below table:

Method Argument(s) Returns Description
count() void int Returns the number of entities available.
deleteAll() void void Delete all the entities.
deleteAllByIds(Iterable<? extends ID>) List of ids void Deletes all instances of entities with the given IDs.
deleteById(ID) The id of the entity void Deletes the entity with the given id.
existsById(ID) The id of the entity boolean Returns whether an entity with the given id exists.
findAll() void Iterable Returns all instances of the type.
findAll(Pageable) Pageable object will be described below Iterable Returns entities meeting the paging restriction provided in the Pageable object.
findAll(boolean) boolean will be described below Iterable Returns all entities sorted by the given option.
findAllByIds(Iterable<? extends ID>) Iterable void Returns all instances of the type {@code T} with the given IDs.
findById(ID) The id of the entity T Retrieves an entity by its id.
insert(T) The entity to be inserted void Insert the entity to the table as a record.
insertAll(Iterable) The list of entities to be inserted void Insert the entities to the table as records.
update(T, ID) The entity to be updated, the id corresponding to the entity to be updated. void Update the specified entity with the corresponding id.
updateAll(Iterable, Iterable<? extends ID> The list entities to be updated, the list of ids corresponding to the the list of entities to be updated. void Update the specified entities with the corresponding ids.
  • We found that there are two operations findAll(Pageable) and findAll(boolean).
    • For the Pageable there are many way to use this operation:
import bangmaple.jdbc.dao.base.Store;
import bangmaple.jdbc.paging.PageRequest;
import bangmaple.jdbc.paging.Pageable;

public class Main {
  public static void main(String[] args) {
    UsersDAO dao = Store.select(UsersDAO.class);
    Pageable pageable = PageRequest.of(0, 5, Pageable.SORT_DESC);
    dao.findAll(pageable);
  }
}
  • Respects to the PageRequest object, we used the of method. For the example, we want to retrieve 5 records by the first page then we use PageRequest.of(0, 5);.
import bangmaple.jdbc.dao.base.Store;
import bangmaple.jdbc.paging.PageRequest;
import bangmaple.jdbc.paging.Pageable;

public class Main {
  public static void main(String[] args) {
    UsersDAO dao = Store.select(UsersDAO.class);
    Pageagle pageable = PageRequest.of(0, 5, Pageable.SORT_DESC);
    dao.findAll(pageable);
  }
}
  • Now we passed the third parameter - Pageable.SORT_DESC this will be described as we want to retrieve 5 records by the first page in the descending order then we used PageRequest.of(0, 5, Pageable.SORT_DESC).
import bangmaple.jdbc.dao.base.Store;
import bangmaple.jdbc.paging.PageRequest;
import bangmaple.jdbc.paging.Pageable;

public class Main {
  public static void main(String[] args) {
    UsersDAO dao = Store.select(UsersDAO.class);
    Pageable pageable = PageRequest.of(0, 5, Pageable.SORT_DESC, "fullname", "role");
    dao.findAll(pageable);
  }
}
  • By default Pagination operation, the records are ordered by the primary key column (property that is annotated with @Id).
  • Now that we passed the final parameter - String.... For example, we want to retrieve 5 records by the first page in the descending order based on fullname and role then we used PageRequest.of(0, 5, Pageable.SORT_DESC, "fullname", "role").

Let say we want to retrieve a list of users by their role name, but we didn't find any method related to that requirement. We can customize to add our method by the following code snippet:

import bangmaple.jdbc.utils.ConnectionManager;
import bangmaple.jdbc.dao.base.Store;

public class UsersDAO extends Store<UsersDTO, String> {
    
    private UsersDAO() {}
    
    public List<UsersDTO> findUsersByRole(String role) throws SQLException {
        List<UsersDTO> list = null;
        conn = ConnectionManager.getConnection();
        try {
            if (conn != null) {
                conn.setCatalog("users_management");
                String query = "SELECT username, password, fullname, role FROM users WHERE role = ?";
                prStm = conn.prepareStatement(query);
                prStm.setString(1, role);
                rs = prStm.executeQuery();
                list = new ArrayList<>();
                while (rs.next()) {
                    list.add(this.parseResultSetToDTO(rs, UsersDTO.class));
                }
            }
        } finally { closeConnection(); }
        return list;
    }
}

Now we implemented the findUsersByRole(String) to retrieve the users by their role name. We may notice there are many variables we don't know, they are described as below:

  • conn is javax.sql.Connection
  • prStm is javax.sql.PreparedStatement
  • rs is javax.sql.ResultSet
  • ConnectionManager.getConnection() retrieves the connection instance between our application and the database.
  • closeConnection() closes the connection between our application and the database.
  • conn.setCatalog("users_management") this is how we specify the database name. For example: users_management, please change to your database name.
  • this.parseResultSetToDTO(ResultSet, UsersDTO.class) this method helps us to map the ResultSet's records after the executeQuery() operation to the UsersDTO.class then it will return the UsersDTO instance containing the information we need, this helper instance method is inherited from Store class.

  • For the complete example of the CRUD operations, please navigate to this page.
  • For the example of Data Source configuration as context.xml, please navigate to this page.

Thank your using this library. I made this library for more than 72 hours.

Please don't hesitate to report bug(s) if you found, thanks again in advance!


Q&A:

The application server such as Apache Tomcat won't load the library even we already embedded the library into the application:

05-Sep-2021 07:35:06.443 SEVERE [RMI TCP Connection(2)-127.0.0.1] org.apache.catalina.core.StandardContext.listenerStart Exception sending context initialized event to listener instance of class [com.example.demo.ServletListener]
	java.lang.NoClassDefFoundError: bangmaple/jdbc/utils/ConnectionManager
		at com.example.demo.ServletListener.contextInitialized(ServletListener.java:13)
        ...
	Caused by: java.lang.ClassNotFoundException: bangmaple.jdbc.utils.ConnectionManager
		at org.apache.catalina.loader.WebappClassLoaderBase.loadClass(WebappClassLoaderBase.java:1365)
		... 48 more
  • This is how to fix the problem, this is the normal operation of the application. You may have even met this if you didn't put the JDBC Driver library. Drag the bangmaple-jdbc-repository.jar into your lib folder of your Apache Tomcat directory, for me it is: /Users/bangmaple/apache-tomcat-9.0.40/lib/.
  • You may also need to drag the JDBC Driver library like sqljdbc.jar driver as well.

After that, confirm it is existed at this directory then restart your application server! Happy coding!


💌 Credits

You might also like...

Online Quiz system - JDBC, JSP

Online-Quiz-System-in-Java Online Quiz system - JDBC, JSP Java Project based on JDBC, JSP, Java Servlet and Server Deployment Project Aim Develop web

Oct 14, 2022

Hi, Spring fans! In this installment, we'll look at how to build tenancy-aware JDBC applications

Multitenant JDBC You'll need to spin up two separate PostgreSQL instances. Put this script into a file called postgres.sh: #!/usr/bin/env bash NAME=${

Nov 7, 2022

A JDBC driver for Cloudflare's D1 product, compatible with Jetbrains tools.

A JDBC driver for Cloudflare's D1 product, compatible with Jetbrains tools.

D1 JDBC Driver A JDBC driver for Cloudflare's D1 Database product! JDBC is the technology that drives popular database tools such as Jetbrains' databa

Dec 9, 2022

MixStack lets you connects Flutter smoothly with Native pages, supports things like Multiple Tab Embeded Flutter View, Dynamic tab changing, and more. You can enjoy a smooth transition from legacy native code to Flutter with it.

MixStack lets you connects Flutter smoothly with Native pages, supports things like Multiple Tab Embeded Flutter View, Dynamic tab changing, and more. You can enjoy a smooth transition from legacy native code to Flutter with it.

中文 README MixStack MixStack lets you connects Flutter smoothly with Native pages, supports things like Multiple Tab Embeded Flutter View, Dynamic tab

Dec 19, 2022

MapDB provides concurrent Maps, Sets and Queues backed by disk storage or off-heap-memory. It is a fast and easy to use embedded Java database engine.

MapDB: database engine MapDB combines embedded database engine and Java collections. It is free under Apache 2 license. MapDB is flexible and can be u

Dec 30, 2022

MapDB provides concurrent Maps, Sets and Queues backed by disk storage or off-heap-memory. It is a fast and easy to use embedded Java database engine.

MapDB: database engine MapDB combines embedded database engine and Java collections. It is free under Apache 2 license. MapDB is flexible and can be u

Jan 1, 2023

A RatingBar library for android, you can customize size, spacing, color and image easily, and support right to left.

A RatingBar library for android, you can customize size, spacing, color and image easily, and support right to left.

AndRatingBar A RatingBar library for android, you can customize size, spacing, color and image easily, and support right to left. 安卓RatingBar终极方案,继承自原

Aug 14, 2021

eXist Native XML Database and Application Platform

eXist Native XML Database and Application Platform

eXist-db Native XML Database eXist-db is a high-performance open source native XML database—a NoSQL document database and application platform built e

Dec 30, 2022
Releases(1.0)
Owner
Ngô Nguyên Bằng
Information Assurance and Software Engineering
Ngô Nguyên Bằng
🔥 强大的动态线程池,附带监控线程池功能(没有依赖任何中间件)。Powerful dynamic thread pool, does not rely on any middleware, with monitoring thread pool function.

?? 动态线程池系统,包含 Server 端及 SpringBoot Client 端需引入的 Starter. 动态线程池监控,主意来源于美团技术公众号 点击查看美团线程池文章 看了文章后深受感触,再加上最近线上线程池的不可控以及不可逆等问题,想做出一个 兼容性、功能性、易上手等特性 集于一身的的

龙台 3.4k Jan 3, 2023
esProc SPL is a scripting language for data processing, with well-designed rich library functions and powerful syntax, which can be executed in a Java program through JDBC interface and computing independently.

esProc esProc is the unique name for esProc SPL package. esProc SPL is an open-source programming language for data processing, which can perform comp

null 990 Dec 27, 2022
Core ORMLite functionality that provides a lite Java ORM in conjunction with ormlite-jdbc or ormlite-android

ORMLite Core This package provides the core functionality for the JDBC and Android packages. Users that are connecting to SQL databases via JDBC shoul

Gray 547 Dec 25, 2022
EBQuery allows you to easily access databases through a REST API.

EBQuery Table of Contents Introduction - Enterprise Backend as a Service Requirements Getting started Using EBQuery Features Introduction - Enterprise

null 15 Nov 9, 2021
光 HikariCP・A solid, high-performance, JDBC connection pool at last.

HikariCP It's Faster.Hi·ka·ri [hi·ka·'lē] (Origin: Japanese): light; ray. Fast, simple, reliable. HikariCP is a "zero-overhead" production ready JDBC

Brett Wooldridge 17.7k Jan 1, 2023
Vibur DBCP - concurrent and dynamic JDBC connection pool

Vibur DBCP is concurrent, fast, and fully-featured JDBC connection pool, which provides advanced performance monitoring capabilities, including slow S

Vibur 94 Apr 20, 2022
JDBC driver for ClickHouse

This is a basic and restricted implementation of jdbc driver for ClickHouse. It has support of a minimal subset of features to be usable.

ClickHouse 1.1k Jan 1, 2023
Multi-DBMS SQL Benchmarking Framework via JDBC

BenchBase BenchBase (formerly OLTPBench) is a Multi-DBMS SQL Benchmarking Framework via JDBC. Table of Contents Quickstart Description Usage Guide Con

CMU Database Group 213 Dec 29, 2022
SPRING MySQL Database Connection using JDBC STEPS

SPRING-MySQL-Database-Connection-using-JDBC-STEPS SPRING MySQL Database Connection using JDBC STEPS Step1: Create maven project Group id: com.cdac Art

Dnyaneshwar Madhewad 1 Jan 27, 2022
Amazon AppFlow Custom JDBC Connector example

Amazon AppFlow Custom JDBC Connector example This project contains source code and supporting files that implements Amazon Custom Connector SDK and re

AWS Samples 6 Oct 26, 2022