0

I'm having a problem where when I use mybatis Mappers to get and Object from my h2 database I end up getting an array of subsequent numbers instead of an array mapped to the Class of objects I inserted into the database. For instance, if I insert 3 messages into the Message table I get [1,2,3] when I query the database instead of a List containing my messages. Here is my code:

Schema:

CREATE TABLE IF NOT EXISTS MESSAGES (
    messageid INT PRIMARY KEY auto_increment,
    username VARCHAR NOT NULL,
    messageText VARCHAR NOT NULL,
    createdTime DATETIME NOT NULL
);

Model:

public class Message {
    private String username;
    private String messageText;
    private Integer messageid;
    private Date createdTime;

    public Message(String username, String messageText, Integer messageid, Date createdTime) {
        this.username = username;
        this.messageText = messageText;
        this.messageid = messageid;
        this.createdTime = createdTime;
    }
// Getters and Setters
}

Mapper

@Mapper
public interface MessageMapper {

    @Select("SELECT * from MESSAGES ORDER BY createdTime")
    List<Message> getAllMessages();

    @Select("SELECT * from MESSAGES WHERE username = #{username}")
    Message getMessage(String username);

    @Insert("INSERT INTO MESSAGES (username, messageText, createdTime) VALUES(#{username}, #{messageText}, #{createdTime})")
    @Options(useGeneratedKeys = true, keyProperty = "messageid")
    int insert(Message message);

}

Message Service

@Service
public class MessageService {

    private MessageMapper messageMapper;

    public MessageService(MessageMapper messageMapper) {
        this.messageMapper = messageMapper;
    }

    public int createChatMessage(Message message) {
        Message newMessage = new Message(message.getUsername(), message.getMessageText(), null, new Date());
        return messageMapper.insert(newMessage);
    }

    public List<Message> getAllMessages() {
        return messageMapper.getAllMessages();
    }

    public Object getMessage(String username) { return messageMapper.getMessage(username); }
}

And Message Controller

public class MessageController {

    private MessageService messageService;

    public MessageController(MessageService messageService) {
        this.messageService = messageService;
    }

    @GetMapping("/chat")
    public String getChats(@ModelAttribute("message") Message message, Authentication authentication, Model model) {
        model.addAttribute("messages", this.messageService.getAllMessages());
        model.addAttribute("username", authentication.getName());
        return "chat";
    }

    @PostMapping("/chat")
    public String postChat(@ModelAttribute("message") Message message, Authentication authentication, Model model) {
        String username = authentication.getName();
        message.setUsername(username);
        message.setCreatedTime(new Date());
        messageService.createChatMessage(message);

        message.setMessageText("");

        model.addAttribute("messages", this.messageService.getAllMessages());
        model.addAttribute("username", username);

        return "chat";
    }

}

I can see in my h2 console that when I add messages they are correctly inserted into the database but when I try and use the method messageService.getAllMessages() in the MessageController it throws the error:

Data conversion error converting "I wanna dance" [22018-200]; nested exception is org.h2.jdbc.JdbcSQLDataException: Data conversion error converting "I wanna dance" [22018-200]] with root cause

java.lang.NumberFormatException: For input string: "I wanna dance"

Again, if I add 3 messages System.out.println(messageService.getAllMessages()) prints [1,2,3]

Can anybody tell me what mistake I am making?

1 Answer 1

1

Your problem is probably the order of columns.

When you query for SELECT * from MESSAGES you don't define the columns. So you will get the columns as defined in your create-table:

  • messageid
  • username
  • messageText
  • createdTime

But your Message class defines the properties in the following order:

  • username
  • messageText
  • messageid
  • createdTime

The error message you get looks like it can't convert something to an int field, probably the third column messageText to messageid.

I would try to add the columns in the correct order to your select clause. Like SELECT username, messageText,messageid,createdTime from MESSAGES

If that doesn't help I would start with selecting just a single column and building up from there.

About the printing of "1,2,3" - how does your toString() Method look like in the Message class? Because that's what will get printed, if you print a List.

Sign up to request clarification or add additional context in comments.

1 Comment

The order ended up being the problem. I just ended up changing the order in my schema to fix this. I had was under the impression that mybatis matched the columns based on the string values and not the order. Thanks for the help!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.