Is there a better way to dynamically implement SQL statements in java?-CodePudding

I have been working with a JDBC for the past couple of weeks and I have come upon a problem that I figure will have subjective answers. Let's suppose we want to create a method that dynamically constructs a SQL statement that is to be executed based upon what a user inputs into some GUI. Based on what the user has put into the GUI, we need to gather that information, validate it, then pass it into the database. That is, if the user has left any field empty, we simply do not add any extra conditionals to the SQL statement.

For example if the user left the hypothetical column "name" blank (and the table automatically generates primary keys) we might write

INSERT INTO <tableName>;

to add a new row to the table.

Alternatively if the user has given a name, we write,

INSERT INTO <tableName> (name) VALUES (?);

With that context given lets suppose I construct a method that dynamically creates this SQL statement:

public void addToDatabase(){
     Connection connection = createConnectionToDatabase();
     String str = "INSERT INTO <tableName>";
     if(!name.isBlank()){
          str  = " (name) VALUES (?)"
     }
     str  = ";";
     PreparedStatement statement = connection.prepareStatement(str);
     if(!name.isBlank()){
          statement.setString(1, name);
     }
     statement.execute();
     connection.close();

If you notice, we check if name is blank twice - which I find rather annoying since it should only be checked once in my opinion. The first time we check if name is blank is to construct the proper string to be placed into the SQL statement. The second time we check if the name is blank is to confirm if we need to pass the parameter into the prepared statement. This creates a sort of catch-22 that forces us to check if the name is blank twice which I do not like. Are there any better ways of handling this situation to only have to check for the name once?

I found a few other answers stating that there is no better way of doing this kind of dyamic SQL statements but I don't like that answer and am hoping for a better one. Thanks.

CodePudding user response：

what you want is equivalent to

String str = "INSERT INTO <tableName> name values (?)";
PreparedStatement statement = connection.prepareStatement(str);

if(!name.isBlank()){
    statement.setString(1, name);
}
else {
    statement.setNull(1, Types.VARCHAR);
}