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
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
  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.

Naveen Yalla user avatar by
Naveen Yalla
·
Apr. 17, 19 · Tutorial
Like (14)
Save
Tweet
Share
50.22K Views

Join the DZone community and get the full member experience.

Join For Free

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.

Popular on DZone

  • Learning by Doing: An HTTP API With Rust
  • How To Avoid “Schema Drift”
  • How To Get Page Source in Selenium Using Python
  • Multi-Cloud Database Deep Dive

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

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

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: