Alright, we now have an idea about few of the basic database commands like select, insert, create table, etc and we explored them in the previous chapter Quick Start Database Programming.

Now, let see the select command in details. Today we will see how to generate the database select command related to the student table using a Java class. Let’s take a look at the below select command.

select register_number, name, age from student;

Okay. Our aim is to create the above select command using Student class. Take a look at the below Student class.

public final class Student {
private String registerNumber;
private String name;
private int age;
public Student(String registerNumber, String name, int age) {
this.registerNumber = registerNumber;
this.name = name;
this.age = age;
}
public void setRegisterNumber(String registerNumber) {
this.registerNumber = registerNumber;
}
public String getRegisterNumber() {
return this.registerNumber;
}
public void setName(String name) {
this.name = name;
}
public String getName() {
return this.name;
}
public void setAge(int age) {
this.age = age;
}
public int getAge() {
return this.age;
}
}
view raw Student.java hosted with ❤ by GitHub

Now I will show you how to dynamically generate a SQL select query using the above Student class object. But before that, we have to understand the use of Annotation and Reflection. Two powerful components of the Java programming language.

In simple terms, Reflection allows the Java program to examine its own behavior and Annotation helps us to attach metadata or specific information to annotated elements.

Why we are using annotation here?

See in a Student class, there are a lot of fields like register number, name, age, token number, etc and each of them must be mapped to a table column in the student table. So here we have to attach information to all the fields which are supposed to be mapped to the database table column (token number given to a student need not be added to the table). For this, we will use Annotations. Let’s take a look at the DbField annotation.

import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
@Retention(RetentionPolicy.RUNTIME)
public @interface DbField {
}
view raw DbField.java hosted with ❤ by GitHub

Now we have to associate this marker annotation to the corresponding fields in Student class. It is simple, take a look at the below line of code.

@DbField
private String regsiterNumber;

@DbField
private String name;

@DbField
private int age;

Now the next part is mapping the Student class object to the corresponding student table in the database. Again it is very simple, now we will be using an interface called DbTable.

public interface DbTable {
}
view raw DbTable.java hosted with ❤ by GitHub

Now use this interface in Student class, or implement the interface in Student class. We are done. Take a look at the below code

public class Student implements DbTable {
}

Now we will see how the select command is generated using the Student class. Take a look at the below program.

package org.sydlabz.wp3;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;
/**
* @author Seyed Sahil
*/
public class JavaToDb {
private static boolean hasTableMapping(Class<?> clazz) {
for (Class<?> clazzInterface : clazz.getInterfaces()) {
if (clazzInterface.getName().equals(DbTable.class.getName())) {
return true;
}
}
return false;
}
private static String convertToDbName(String javaName) {
if (javaName == null || javaName.isEmpty()) {
return null;
}
List<String> characterList = new ArrayList<>();
for (int i = 0; i < javaName.length(); i++) {
characterList.add(String.valueOf(javaName.charAt(i)));
}
String temp = characterList.stream()
.map(charString -> {
char character = charString.charAt(0);
if (Character.isLowerCase(character)) {
return String.valueOf(character);
} else {
return "_" + Character.toLowerCase(character);
}
})
.reduce((string1, string2) -> string1 + string2)
.get();
return temp.charAt(0) == '_' ? temp.substring(1) : temp;
}
private static List<String> getDbFieldFromClass(Class<?> clazz) {
if (clazz == null) {
return null;
}
return Arrays.stream(clazz.getDeclaredFields())
.filter(field -> field.isAnnotationPresent(DBField.class))
.map(field -> JavaToDb.convertToDbName(field.getName()))
.collect(Collectors.toList());
}
private static String getSelectCommandList(List<String> dbFields) {
if (dbFields.isEmpty()) {
return null;
}
return dbFields.stream()
.reduce((field1, field2) -> field1 + ", " + field2)
.get();
}
private static String prepareSelectAll(Class<?> clazz) {
if (clazz == null || !JavaToDb.hasTableMapping(clazz)) {
return null;
}
return "SELECT " +
JavaToDb.getSelectCommandList(JavaToDb.getDbFieldFromClass(clazz)) +
" FROM " +
JavaToDb.convertToDbName(clazz.getSimpleName()) +
";";
}
public static void main(String[] args) {
System.out.println(JavaToDb.prepareSelectAll(Student.class));
}
}
view raw JavaToDb.java hosted with ❤ by GitHub

Hope you have read and understood the program and got a clear idea about the above-given code. I am not going to tell you how this is happening and so you have to figure it out 🙂

But, I will give you a short algorithm on how this works

  1. Check to see if the mapping exists for the given class
  2. Find all the fields with a mapping to the table columns
  3. Prepare a select command field list
  4. Form the complete command

Actually, this technique is a simple solution to create a table specific select command just by using the class associated with it. In the next article, we will see how to apply this select command and convert the values to the actual Java object and display them as an HTML table as promised in the first article.

Stay Tuned

Thanks 🙂

Seyed Sahil

Advertisement