Die SQL-Abfrage funktioniert in der H2-Datenbankkonsole einwandfrei, aber wenn ich versuche, sie über meinen Code auszuf ⇐ MySql

MySQL DBMS-Forum
Post Reply Previous topicNext topic
Guest
 Die SQL-Abfrage funktioniert in der H2-Datenbankkonsole einwandfrei, aber wenn ich versuche, sie über meinen Code auszuf

Post by Guest »

Ich versuche also, einen MySQL-Join zu erstellen, um alle Nachrichten für einen Server abzurufen und den Benutzernamen des Absenders an jede Nachricht anzuhängen. Das SQL funktioniert in der H2-Konsole einwandfrei, aber wenn ich versuche, es über meinen Code auszuführen, erhalte ich eine Fehlermeldung.
Meine SQL-Anfrage sieht so aus:

Code: Select all

SELECT sm.id AS message_id,
sm.sender_id,
u.username AS sender_username,
sm.content,
sm.created_at
FROM server_messages sm
JOIN users u ON sm.sender_id = u.id
WHERE sm.server_id = 1
ORDER BY sm.created_at ASC;
Meine USERS-Tabelle hat 3 Spalten – ID – Benutzername – Passwort
Meine SERVER_MESSAGES hat 5 Spalten – ID – Sender_ID – Server_ID – content - erstellt_at
Meine Nachrichtendienstfunktion sieht so aus:

Code: Select all

public List getServerMessages(int serverId) {
String serverQuery = "SELECT sm.id AS message_id, sm.sender_id, u.username AS sender_username, sm.content, sm.created_at FROM server_messages sm JOIN users u ON sm.sender_id = u.id WHERE sm.server_id = 19 ORDER BY sm.created_at ASC";
List collection = this.db.query(serverQuery, new ServerMessageRowMapper());

return collection;
}
Meine ServerMessage sieht so aus:

Code: Select all

public class ServerMessage {
private int id;
private int senderId;
private int serverId;
private String content;
private Date createdAt;

public int getId() {
return id;
}

public void setId(int id) {
this.id = id;
}

public int getSenderId() {
return senderId;
}

public void setSenderId(int senderId) {
this.senderId = senderId;
}

public int getServerId() {
return serverId;
}

public void setServerId(int serverId) {
this.serverId = serverId;
}

public String getContent() {
return content;
}

public void setContent(String content) {
this.content = content;
}

public Date getCreatedAt() {
return createdAt;
}

public void setCreatedAt(Date createdAt) {
this.createdAt = createdAt;
}
}
Mein ServerMessageRowMapper sieht so aus:

Code: Select all

public class ServerMessageRowMapper implements RowMapper {
@Override
public ServerMessage mapRow(ResultSet rs, int rowNum) throws SQLException {
ServerMessage serverMessage = new ServerMessage();
serverMessage.setId(rs.getInt("id"));
serverMessage.setSenderId(rs.getInt("sender_id"));
serverMessage.setServerId(rs.getInt("server_id"));
serverMessage.setContent(rs.getString("content"));
serverMessage.setCreatedAt(rs.getDate("created_at"));
return serverMessage;
}
}
Der Fehler ist dieser:

Code: Select all

2025-01-05T04:31:25.318+02:00 ERROR 6368 --- [discord] [nio-8081-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [SELECT sm.id AS message_id, sm.sender_id, u.username AS sender_username, sm.content, sm.created_at FROM server_messages sm JOIN users u ON sm.sender_id = u.id WHERE sm.server_id = 19 ORDER BY sm.created_at ASC]] with root cause

org.h2.jdbc.JdbcSQLSyntaxErrorException: Column "server_id"  not found [42122-232]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:514) ~[h2-2.3.232.jar:2.3.232]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:489) ~[h2-2.3.232.jar:2.3.232]
at org.h2.message.DbException.get(DbException.java:223) ~[h2-2.3.232.jar:2.3.232]
at org.h2.message.DbException.get(DbException.java:199) ~[h2-2.3.232.jar:2.3.232]
at org.h2.jdbc.JdbcResultSet.getColumnIndex(JdbcResultSet.java:3492) ~[h2-2.3.232.jar:2.3.232]
at org.h2.jdbc.JdbcResultSet.getInt(JdbcResultSet.java:337) ~[h2-2.3.232.jar:2.3.232]
at com.zaxxer.hikari.pool.HikariProxyResultSet.getInt(HikariProxyResultSet.java) ~[HikariCP-5.1.0.jar:na]
at com.fmi.discord.mappers.ServerMessageRowMapper.mapRow(ServerMessageRowMapper.java:15) ~[classes/:na]
at com.fmi.discord.mappers.ServerMessageRowMapper.mapRow(ServerMessageRowMapper.java:9) ~[classes/:na]
at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:94) ~[spring-jdbc-6.2.0.jar:6.2.0]
at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:61) ~[spring-jdbc-6.2.0.jar:6.2.0]
at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:464) ~[spring-jdbc-6.2.0.jar:6.2.0]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:393) ~[spring-jdbc-6.2.0.jar:6.2.0]
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:476) ~[spring-jdbc-6.2.0.jar:6.2.0]
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:486) ~[spring-jdbc-6.2.0.jar:6.2.0]
at com.fmi.discord.services.MessageService.getServerMessages(MessageService.java:35) ~[classes/:na]
at com.fmi.discord.controllers.MessageController.getServerMessages(MessageController.java:29) ~[classes/:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:569) ~[na:na]
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:255) ~[spring-web-6.2.0.jar:6.2.0]
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:188) ~[spring-web-6.2.0.jar:6.2.0]
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:118) ~[spring-webmvc-6.2.0.jar:6.2.0]
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:986) ~[spring-webmvc-6.2.0.jar:6.2.0]
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:891) ~[spring-webmvc-6.2.0.jar:6.2.0]
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-6.2.0.jar:6.2.0]
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1088) ~[spring-webmvc-6.2.0.jar:6.2.0]
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:978) ~[spring-webmvc-6.2.0.jar:6.2.0]
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1014) ~[spring-webmvc-6.2.0.jar:6.2.0]
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:903) ~[spring-webmvc-6.2.0.jar:6.2.0]
at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:564) ~[tomcat-embed-core-10.1.33.jar:6.0]
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:885) ~[spring-webmvc-6.2.0.jar:6.2.0]
at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:658) ~[tomcat-embed-core-10.1.33.jar:6.0]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:195) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51) ~[tomcat-embed-websocket-10.1.33.jar:10.1.33]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)  ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) ~[spring-web-6.2.0.jar:6.2.0]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116) ~[spring-web-6.2.0.jar:6.2.0]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) ~[spring-web-6.2.0.jar:6.2.0]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116) ~[spring-web-6.2.0.jar:6.2.0]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) ~[spring-web-6.2.0.jar:6.2.0]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116) ~[spring-web-6.2.0.jar:6.2.0]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:167) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:90) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:483) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:115) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:344) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:397) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:905) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1741) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1190) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:63) ~[tomcat-embed-core-10.1.33.jar:10.1.33]
at java.base/java.lang.Thread.run(Thread.java:840) ~[na:na]
Meine Migrationen:

Code: Select all

CREATE TABLE servers (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
is_active BOOLEAN NOT NULL DEFAULT TRUE
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE server_messages (
id INT AUTO_INCREMENT PRIMARY KEY,
sender_id INT NOT NULL,
server_id INT NOT NULL,
content TEXT NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (server_id) REFERENCES servers (id) ON DELETE CASCADE,
FOREIGN KEY (sender_id) REFERENCES users (id) ON DELETE CASCADE
);

CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
password VARCHAR(255) NOT NULL,
);
Top
Post Reply Previous topicNext topic

Quick Reply

Change Text Case: 
   
  • Similar Topics
    Replies
    Views
    Last post

Return to “MySql”