TransWikia.com

mybatis "Select * from Table" return list of numbers instead of list of objects

Stack Overflow Asked by Stephen Agwu on December 11, 2021

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?

One Answer

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.

Answered by TomStroemer on December 11, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP