Home > Enterprise >  how to update a record in SQL database with generated query and annotations and reflections in java
how to update a record in SQL database with generated query and annotations and reflections in java

Time:09-16

I'm going to update a record in the MySQL database and I have to use annotation and reflection in my code to update a record.

The method takes an object from the user as an argument. Each object contains several fields. fields have Annotation and reflection on them.

I tried to write an update query with the following code. I checked my query many times but did not notice my mistake.

I need a tip!

public void updateRecord(Object object) {

String tableName = object.getClass().getDeclaredAnnotation(Table.class).name().equals("")
        ? object.getClass().getSimpleName() : object.getClass().getDeclaredAnnotation(Table.class).name();


try {

    ArrayList<String> paramValue = new ArrayList<>();

    String query = "UPDATE "   tableName   " set ";

    Field[] fields = object.getClass().getDeclaredFields();

    for (Field field : fields) {

        Annotation[] annotations = field.getDeclaredAnnotations();

        for (Annotation annotation : annotations) {

            if (annotation instanceof Column && field.getAnnotation(Id.class) == null) {

                String columnName = field.getAnnotation(Column.class) == null ? field.getName() : field.getAnnotation(Column.class).name();

                query  = columnName   " = "    "? "   ",";
                Field objectFiled = object.getClass().getDeclaredField(field.getName());
                objectFiled.setAccessible(true);
                paramValue.add(String.valueOf(objectFiled.get(object)));

            }

        }

    }

    if (query.endsWith(",")) {
        query = query.substring(0, query.length() - 1);
    }

    query  = " Where ";


    for (Field field : fields) {

        Annotation[] annotations = field.getDeclaredAnnotations();

        for (Annotation annotation : annotations) {

            Field objectFiled = object.getClass().getDeclaredField(field.getName());
            objectFiled.setAccessible(true);

            if (annotation instanceof Id) {

                    String columnName = field.getAnnotation(Column.class) == null ? field.getName() : field.getAnnotation(Column.class).name();
                    query  = columnName   " = "   objectFiled1.get(object);
                    paramValue.add(String.valueOf(objectFiled1.get(object)));

                }


        }

    }

    System.out.println(query);
    preparedStatement = connection.prepareStatement(query);

    for (int i=0; i < paramValue.size(); i  ) {
        preparedStatement.setString(i, paramValue.get(i));
    }

    preparedStatement.executeUpdate();


} catch (SQLException | IllegalAccessException | NoSuchFieldException ex) {

    logger.error("User try to update an record from "   tableName   " but get an Exception");
    ex.printStackTrace();
}

}

and this is the exception:

UPDATE Person set firstname = ? ,lastname = ?  Where ID = 1
        java.sql.SQLException: Parameter index out of range (0 < 1 ).
        at
com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129 
        )
        
 atcom.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
    at com.mysql.cj.jdbc.ClientPreparedStatement.checkBounds(ClientPreparedStatement.java:1372)
    at com.mysql.cj.jdbc.ClientPreparedStatement.getCoreParameterIndex(ClientPreparedStatement.java:1388)
    at com.mysql.cj.jdbc.ClientPreparedStatement.setString(ClientPreparedStatement.java:1755)
    at com.mapsa.orm.database.CRUDGenerator.updateRecord(CRUDGenerator.java:259)
    at com.mapsa.orm.main.Execute.executeApplication(Execute.java:45)
    at com.mapsa.orm.main.App.main(App.java:11)

CodePudding user response:

You need to pass the value of ID field, but you are actually passing the name of the field, i.e. ID.

Try below code:

for (Field field: fields) {

        Annotation[] annotations = field.getDeclaredAnnotations();

        for (Annotation annotation : annotations) {

            Field objectFiled = object.getClass().getDeclaredField(field.getName());
            objectFiled.setAccessible(true);

            if (annotation instanceof Id) {

                String columnName = field.getAnnotation(Column.class) == null ? 
                field.getName() : field.getAnnotation(Column.class).name();
                // query  = columnName; // <-- this is wrong
                query  = objectFiled.get(object); // try this

            }


        }

}

Also, you need to set the parameter values in preparedStatement, which you are missing in your code.

CodePudding user response:

change your if statement with code as follows:

 if (annotation instanceof Id) {

                String columnName = field.getAnnotation(Column.class) == null ? field.getName() : field.getAnnotation(Column.class).name();
                query  = columnName   " = "   objectFiled.get(object);


            }

CodePudding user response:

Problem is in this part of code:

for (int i=0; i < paramValue.size(); i  ) {
    preparedStatement.setString(i, paramValue.get(i));
}

The index for setString method starts from 1, not from 0 - which the error message hints at.

  • Related