DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Newsletter
Log In / Join
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Related

  • Projections/DTOs in Spring Data R2DBC
  • Testcontainers With Kotlin and Spring Data R2DBC
  • JQueue: A Library to Implement the Outbox Pattern
  • From Database Changes to Real-Time Actions at Scale: Oracle GoldenGate and Java

Trending

  • You Don't Have to Pick One: A Hybrid Approach to Enterprise Data Refresh
  • One Database, Two Models: Building AI-Friendly Java Applications With MySQL JSON Duality Views
  • Introducing senro: A Pipeline Engine You Program in Go
  • Ingesting Public Data That Fails Silently: Patterns From a TypeScript and Cloudflare Workers Pipeline

Draft This article hasn't been published, so readers can't see it.

In moderation This article is waiting for review, so readers can't see it yet.

Deleted This article has been deleted, so readers can't see it.

  1. DZone
  2. Coding
  3. Languages
  4. JDBC ResultSet and Generic Class List Using Java Reflection Annotations

JDBC ResultSet and Generic Class List Using Java Reflection Annotations

Check out this post to learn more about Java reflection annotations to query the JDBC database.

By 
Naveen Yalla
·
Apr. 17, 19 · Tutorial
Post on X (opens in a new tab) Share on LinkedIn (opens in a new tab)
55.5K Views

Join the DZone community Save articles, join the discussion, and download Refcards and Trend Reports with a free account.

Join for free Already a member? Log in

When working with web projects, the frequency required to query the database and get results is quite high. This involves a lot of code in your DAO classes and will affect readability and connection backlogs.

If we observe closely, its code is static, except for the Model class type and query in it.

For example, if we need Employee details, we will create the code with Connection, Statement, ResultSet, and closing blocks. If we require Department details, we need to do the same as before, except we change the List type to Department and the query we run through it.

Based on this experience, I came across one article by Roberto Benitez on this blog.

Please go through the above article and come back here to see how I used it to serve the previously discussed problems.

To implement, check out the steps below:

  1. Create a custom annotation
  2. Create a Model class, which contains mapping fields to the ResultSet column names with the created annotation.
  3. Call the ResultSet 
  4. Load the ResultSet for each value into the object
  5. Check for the Primitive type
  6. Auto-box the Primitive type class.

Create the of Custom Annotation

Here is how we created the custom annotation:

@Retention(RetentionPolicy.RUNTIME)
public @interface DBTable {
public String columnName();

}


Creation of the Model Class

Here is how we created the Model class, which contains mapping fields for the ResultSet column names with the created annotation:

public Class Employee{
@DBTable(columnName ="emp_id")
private int empId;
@DBTable(columnName ="emp_name")
private String empName;

//Getters and setters
//Default constructor // mandatory
}


Calling the ResultSet

 String query =”select emp_id,emp_name from employee”  
In the above query, column names should be equal to the @DBTable annotation column names.

public <T> List<T> selectQuery(Class<T> type, query) throws SQLException {
    List<T> list = new ArrayList<T>();
    try (Connection conn = dataSource.getConnection()) {
        try (Statement stmt = conn.createStatement()) {
            try (ResultSet rst = stmt.executeQuery(query)) {
                while (rst.next()) {
                    T t = type.newInstance();
                    loadResultSetIntoObject(rst, t);// Point 4
                    list.add(t);
                }
            }
        } catch (InstantiationException | IllegalAccessException e) {
            throw new RuntimeException("Unable to get the records: " + e.getMessage(), e);
        }
    }
    return list;


Loading the ResultSet for Each Value Into the Object

This looks quite interesting. Here's how Java annotations help us:

public static void loadResultSetIntoObject(ResultSet rst, Object object)
        throws IllegalArgumentException, IllegalAccessException, SQLException {
    Class<?> zclass = object.getClass();
    for (Field field : zclass.getDeclaredFields()) {
        field.setAccessible(true);
        DBTable column = field.getAnnotation(DBTable.class);
        Object value = rst.getObject(column.columnName());
        Class<?> type = field.getType();
        if (isPrimitive(type)) {//check primitive type(Point 5)
            Class<?> boxed = boxPrimitiveClass(type);//box if primitive(Point 6)
            value = boxed.cast(value);
        }
        field.set(object, value);
    }
}


Check for Primitive Types

This will return a Primitive type:

public static boolean isPrimitive(Class<?> type) {
    return (type == int.class || type == long.class || type == double.class || type == float.class
            || type == boolean.class || type == byte.class || type == char.class || type == short.class);
}


Auto-Boxing to Primitive Type Class

Here is the code for how we implemented this:

public static Class<?> boxPrimitiveClass(Class<?> type) {
    if (type == int.class) {
        return Integer.class;
    } else if (type == long.class) {
        return Long.class;
    } else if (type == double.class) {
        return Double.class;
    } else if (type == float.class) {
        return Float.class;
    } else if (type == boolean.class) {
        return Boolean.class;
    } else if (type == byte.class) {
        return Byte.class;
    } else if (type == char.class) {
        return Character.class;
    } else if (type == short.class) {
        return Short.class;
    } else {
        String string = "class '" + type.getName() + "' is not a primitive";
        throw new IllegalArgumentException(string);
    }
}


We have made all arrangements to call our magic method with the help of Roberto Benitez. Now, we will try to call this method and see the magic behind it all.

Use Cases

By usingselectQuery, this annotation can provide whatever class name you want from the list. You just need to provide the query that needs to run against the DB.

Use case 1: I want all the details of the student: selectQuery(Student.class, "SELECT * FROM STUDENT") 

Use case 2: I want all of the details of the Department: selectQuery(Department.class, "SELECT * FROM DEPARTMENT")  

Use case 3: I want all of the students in the Department and their enrolled courses. Quite a bit overhead, right? Dont worry too much. The solution is as simple as those mentioned above. Just make the Model class with as many fields as you want, but make sure your query (bit joins) return all fields in the Model class.

Note: Please make sure the @columnName annotation value name of the fields for your Model class and column names of the DB result are the same. Keep whatever name you want for the fields.

PS: I am not concerned about the performance, as of now, because I use this as a Helper method, which I want to use frequently with less code.

Happy coding!

Annotation Database Java (programming language) Use case

Opinions expressed by DZone contributors are their own.

Related

  • Projections/DTOs in Spring Data R2DBC
  • Testcontainers With Kotlin and Spring Data R2DBC
  • JQueue: A Library to Implement the Outbox Pattern
  • From Database Changes to Real-Time Actions at Scale: Oracle GoldenGate and Java

Partner Resources

×

Comments

Loading comments

The comments didn't load

Please try again in a moment.

Likes

Loading

The next likes didn't load. Please try again.

The likes didn't load

Please try again in a moment.

See who liked this

Log in or join DZone for free to see who liked this article.

Log in Join for free
  • RSS
  • X
  • Facebook

ABOUT US

  • About DZone
  • Support and feedback
  • Community research

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 215
  • Nashville, TN 37211
  • [email protected]

Let's be friends:

  • RSS
  • X
  • Facebook