javajdbcdynamic-sql

How to detect if a Java class contains a foreign key field when generating a MySQL CREATE TABLE query?


User.java

// User.java
public class User {
    private int userId;
    private String userName;
    private String userEmail;
    private String userPassword;

    // getters and setters
}

Post.java

// Post.java
public class Post {
    private int postId;
    private String postCategory;
    private String postTitle;
    private String post;

    private int userId; // foreign key reference to User

    // getters and setters
}

I'm generating the SQL query using a createTable() method like this:

public String createTable(Class<?> users) throws IllegalAccessException {
    String message = "";
    String className = printClassName(users); // assumes it returns the class name

    StringBuilder sql = new StringBuilder("CREATE TABLE IF NOT EXISTS " + className);
    sql.append("(");

    Field[] fields = users.getDeclaredFields();
    for (int i = 0; i < fields.length; i++) {
        if (fields[i].getType() == int.class && fields[i].getName().equals("userId")) {
            sql.append(fields[i].getName()).append(" INT AUTO_INCREMENT NOT NULL");
        } else if (fields[i].getType() == int.class) {
            sql.append(fields[i].getName()).append(" INT NOT NULL");
        } else if (fields[i].getType() == String.class) {
            sql.append(fields[i].getName()).append(" VARCHAR(255) NOT NULL");
        }

        if (i < fields.length - 1) {
            sql.append(", ");
        }

        if (i == fields.length - 1) {
            sql.append("PRIMARY KEY (").append(fields[0].getName()).append(")");
        }
    }

    sql.append(");");
    System.out.println("Executing SQL Query: " + sql.toString());

    try {
        dataBaseConnection.pstmt = dataBaseConnection.conn.prepareStatement(sql.toString());
        int i = dataBaseConnection.pstmt.executeUpdate();
        message = (i > 0) ? "Table created with name: " + className + " successfully." : "Something went wrong.";
    } catch (Exception e) {
        e.printStackTrace();
    }

    return message;
}

This approach works fine for simple tables, but now I want to detect and handle foreign key relationships without using annotations. I know I could use annotations (like @ForeignKey), but I’m explicitly avoiding that due to processing overhead and a preference for reflection-based solutions.

My Question: How can I programmatically detect if a class has a field that should be treated as a foreign key (e.g., userId in Post referencing User), and modify my createTable() method to automatically add foreign key constraints in the generated SQL?

Any guidance, strategy, or pattern recognition (based on field names, relationships, etc.) would be helpful.


Solution

  • Since you're already using reflection, you can do the same here to check the name of the field to see if it contains id. Change your primary key for each class to just be called id, so all other ___Id's are easily identified as foreign keys.

    public class Post {
        private int id;
        // ...
    }
    
    public class User {
        private int id;
        // ...
    }
    

    (Alternatively, you could check the name of the class using reflection and ignore the field that contains the name of the class.)

    Field[] fields = users.getDeclaredFields();
    for ( int i = 0; i < fields.length; i++ ) {
        if (fields[i].getName().contains("Id")) {
            // This field is a foreign key
        }
    }