mybatis “Select * from Table” 返回数字列表而不是对象列表

huangapple go评论76阅读模式
英文:

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

问题

我遇到了一个问题,当我使用MyBatis映射器从我的H2数据库中获取对象时,我得到的是一系列连续的数字,而不是映射到我插入到数据库中的对象类的数组。例如,如果我向Message表中插入3条消息,当我查询数据库时,我得到的是[1,2,3],而不是包含我的消息的列表。以下是我的代码:

模式:

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

模型:

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
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);
}

消息服务:

@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);
    }
}

消息控制器:

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";
    }
}

我可以在H2控制台中看到,当我添加消息时,它们被正确地插入到数据库中,但是当我尝试在MessageController中使用方法messageService.getAllMessages()时,它抛出错误:

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"

同样地,如果我添加3条消息,System.out.println(messageService.getAllMessages())会打印出[1,2,3]。有人可以告诉我我犯了什么错误吗?

英文:

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(&quot;SELECT * from MESSAGES ORDER BY createdTime&quot;)
    List&lt;Message&gt; getAllMessages();

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

    @Insert(&quot;INSERT INTO MESSAGES (username, messageText, createdTime) VALUES(#{username}, #{messageText}, #{createdTime})&quot;)
    @Options(useGeneratedKeys = true, keyProperty = &quot;messageid&quot;)
    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&lt;Message&gt; 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(&quot;/chat&quot;)
    public String getChats(@ModelAttribute(&quot;message&quot;) Message message, Authentication authentication, Model model) {
        model.addAttribute(&quot;messages&quot;, this.messageService.getAllMessages());
        model.addAttribute(&quot;username&quot;, authentication.getName());
        return &quot;chat&quot;;
    }

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

        message.setMessageText(&quot;&quot;);

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

        return &quot;chat&quot;;
    }

}

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 &quot;I wanna dance&quot; [22018-200]; nested exception is org.h2.jdbc.JdbcSQLDataException: Data conversion error converting &quot;I wanna dance&quot; [22018-200]] with root cause

java.lang.NumberFormatException: For input string: &quot;I wanna dance&quot;

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

得分: 1

你的问题可能是列的顺序。

当你查询 SELECT * from MESSAGES 时,你没有定义列。因此,你会得到按照创建表时定义的列:

  • messageid
  • username
  • messageText
  • createdTime

但是你的 Message 类按照以下顺序定义了属性:

  • username
  • messageText
  • messageid
  • createdTime

你得到的错误信息似乎无法将某个内容转换为 int 字段,可能是第三列 messageText 转到 messageid。

我建议尝试以正确的顺序将列添加到你的选择语句中,像这样 SELECT username, messageText, messageid, createdTime from MESSAGES

如果这没有帮助,我建议先只选择单个列,然后逐步增加。

关于打印 "1,2,3" - 你的 Message 类中的 toString() 方法是什么样子的?因为如果你打印一个列表,那就会打印出这个方法的内容。

英文:

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.

huangapple
  • 本文由 发表于 2020年7月23日 20:42:06
  • 转载请务必保留本文链接:https://go.coder-hub.com/63054549.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定