获取缺少列名的jdbi错误

我正在尝试使用jdbi向mysql数据库添加用户，但是当我尝试添加用户时，我得到一个错误，说我缺少一列 username sql语句

CREATE TABLE users
(
    id        int          NOT NULL AUTO_INCREMENT,
    username  varchar(100) NOT NULL,
    password  text         NOT NULL,
    email     varchar(150) NOT NULL,
    salt      text         NOT NULL,
    is_admin  boolean DEFAULT FALSE,
    is_active boolean DEFAULT TRUE,
    PRIMARY KEY (id),
    UNIQUE KEY username (username),
    UNIQUE KEY email (email)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

用户类

public class UserEntity implements Serializable {

    private static final long serialVersionUID = 1L;

    private Integer id;
    @NotEmpty(message = "The username must be set")
    @Size(min = 3, max = 100, message = "The username must be between 3 and 100 characters")
    private String username;
    @NotEmpty(message = "The password must be set")
    @Size(min = 8, message = "The password must be at least 8 characters long")
    private String password;
    @NotEmpty(message = "email must be set")
    @Email(message = "The email must be in the form user@domain.com")
    private String email;
    private String salt;
    private boolean isAdmin;
    private boolean isActive;

    public UserEntity(String username, String password, String email, boolean isAdmin, boolean isActive) {
        this(username, password, email, null, isAdmin, isActive);
    }

    public UserEntity(Integer id, String username, String password, String email, boolean isAdmin, boolean isActive) {

    }

    @JsonCreator
    public UserEntity(
            @JsonProperty("username") String username,
            @JsonProperty("password") String password,
            @JsonProperty("email") String email,
            String salt,
            @JsonProperty("isAdmin") boolean isAdmin,
            @JsonProperty("isActive") boolean isActive) {
        this.username = username;
        this.password = password;
        this.email = email;
        this.salt = salt;
        this.isAdmin = isAdmin;
        this.isActive = isActive;
    }

    @JsonProperty
    @NotEmpty
    public Integer getId() {
        return id;
    }

    @JsonProperty
    @NotEmpty
    public String getUsername() {
        return username;
    }

    @JsonProperty
    @NotEmpty
    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @JsonProperty
    @NotEmpty
    public String getEmail() {
        return email;
    }

    public String getSalt() {
        return salt;
    }

    public void setSalt(String salt) {
        this.salt = salt;
    }

    @JsonProperty
    public boolean isAdmin() {
        return isAdmin;
    }

    @JsonProperty
    public boolean isActive() {
        return isActive;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        UserEntity userEntity = (UserEntity) o;
        return getId() == userEntity.getId() &&
                isAdmin() == userEntity.isAdmin() &&
                isActive() == userEntity.isActive() &&
                Objects.equals(getUsername(), userEntity.getUsername()) &&
                Objects.equals(getPassword(), userEntity.getPassword()) &&
                Objects.equals(getSalt(), userEntity.getSalt()) &&
                Objects.equals(getEmail(), userEntity.getEmail());
    }

    @Override
    public int hashCode() {
        return Objects.hash(getId(), getUsername(), getPassword(), getEmail(), getSalt(), isAdmin(), isActive());
    }

    @Override
    public String toString() {
        return "UserEntity{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", email='" + email + '\'' +
                ", isAdmin=" + isAdmin +
                ", isActive=" + isActive +
                '}';
    }

    @RegisterRowMapper(MapperImpl.class)
    @RegisterBeanMapper(MapperImpl.class)
    public interface DAO {
        @SqlUpdate("INSERT INTO users (username, password, email, salt, is_admin, is_active) " +
                "VALUES (:username, :password, :email, :salt, false, true)")
        @GetGeneratedKeys
        UserEntity addUser(@Bind("username") String username,
                           @Bind("password") String password,
                           @Bind("email") String email,
                           @Bind("salt") String salt);

        @SqlUpdate("INSERT INTO users (username, password, email, salt, is_admin, is_active) " +
                "VALUES (:username, :password, :email, :salt, true, true)")
        @GetGeneratedKeys
        UserEntity addAdminUser(@Bind("username") String username,
                                @Bind("password") String password,
                                @Bind("email") String email,
                                @Bind("salt") String salt);

        @SqlQuery("SELECT username, password, email, is_admin, is_active " +
                "FROM users")
        @GetGeneratedKeys
        List<UserEntity> getAllUsers();

        @SqlQuery("SELECT username, password, email, is_admin, is_active " +
                "FROM users " +
                "WHERE is_admin = false")
        @GetGeneratedKeys
        List<UserEntity> getAllRegularUsers();

        @SqlQuery("SELECT username, password, email, is_admin, is_active " +
                "FROM users " +
                "WHERE is_admin = true")
        @GetGeneratedKeys
        List<UserEntity> getAllAdminUsers();

        @SqlQuery("SELECT username, password, email, is_admin, is_active " +
                "FROM users " +
                "WHERE email = :email")
        @GetGeneratedKeys
        UserEntity getUserByEmail(@Bind("email") String email);

        @SqlUpdate("UPDATE users " +
                "SET is_admin = false " +
                "WHERE email = :email")
        @GetGeneratedKeys
        UserEntity revokeAdminRights(@Bind("email") String email);

        @SqlUpdate("UPDATE users " +
                "SET is_admin = true " +
                "WHERE email = :email")
        @GetGeneratedKeys
        UserEntity promoteToAdmin(@Bind("email") String email);

        @SqlUpdate("UPDATE users " +
                "SET is_active = false " +
                "WHERE email = :email")
        @GetGeneratedKeys
        UserEntity deactivateUser(@Bind("email") String email);

        @SqlUpdate("UPDATE users " +
                "SET is_active = true " +
                "WHERE email = :email")
        @GetGeneratedKeys
        UserEntity reactivateUser(@Bind("email") String email);

        @SqlUpdate("UPDATE users " +
                "SET password = password, salt = :salt " +
                "WHERE email = :email")
        @GetGeneratedKeys
        UserEntity updateUserPassword(@Bind("password") String password,
                                      @Bind("email") String email,
                                      @Bind("salt") String salt);
    }

    public static class MapperImpl implements RowMapper<UserEntity> {
        @Override
        public UserEntity map(ResultSet rs, StatementContext ctx) throws SQLException {
            return new UserEntity(
                    rs.getString("username"),
                    rs.getString("password"),
                    rs.getString("email"),
                    rs.getString("salt"),
                    rs.getBoolean("is_admin"),
                    rs.getBoolean("is_active")
            );
        }
    }
}

当我打电话给 addUser() 中的方法 DAO 接口，我得到以下错误；

org.jboss.resteasy.spi.UnhandledException: org.jdbi.v3.core.result.ResultSetException: Error thrown mapping result set into return type [statement:"INSERT INTO users (username, password, email, salt, is_admin, is_active) VALUES (:username, :password, :email, :salt, false, true)", arguments:{positional:{0:danny,1:ce672ce95ca0d5792b2d24fa9c58c078a78545d2bfc3354d23c0d5066c1d7aac49fa308a7ffc9b8bf449e9b62e732e4ea742026086ab5e252a0ced2cf59edea8,2:doldy101@gmail.com,3:1b922bc3dadc6a5ecc5e46ba241c1223}, named:{password:ce672ce95ca0d5792b2d24fa9c58c078a78545d2bfc3354d23c0d5066c1d7aac49fa308a7ffc9b8bf449e9b62e732e4ea742026086ab5e252a0ced2cf59edea8,salt:1b922bc3dadc6a5ecc5e46ba241c1223,email:doldy101@gmail.com,username:danny}, finder:[]}]
    at org.jboss.resteasy.core.ExceptionHandler.handleApplicationException(ExceptionHandler.java:106)
    at org.jboss.resteasy.core.ExceptionHandler.handleException(ExceptionHandler.java:372)
    at org.jboss.resteasy.core.SynchronousDispatcher.writeException(SynchronousDispatcher.java:216)
    at org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:515)
    at org.jboss.resteasy.core.SynchronousDispatcher.lambda$invoke$4(SynchronousDispatcher.java:259)
    at org.jboss.resteasy.core.SynchronousDispatcher.lambda$preprocess$0(SynchronousDispatcher.java:160)
    at org.jboss.resteasy.core.interception.jaxrs.PreMatchContainerRequestContext.filter(PreMatchContainerRequestContext.java:364)
    at org.jboss.resteasy.core.SynchronousDispatcher.preprocess(SynchronousDispatcher.java:163)
    at org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:245)
    at io.quarkus.resteasy.runtime.standalone.RequestDispatcher.service(RequestDispatcher.java:73)
    at io.quarkus.resteasy.runtime.standalone.VertxRequestHandler.dispatch(VertxRequestHandler.java:132)
    at io.quarkus.resteasy.runtime.standalone.VertxRequestHandler.access$000(VertxRequestHandler.java:37)
    at io.quarkus.resteasy.runtime.standalone.VertxRequestHandler$1.run(VertxRequestHandler.java:94)
    at io.quarkus.runtime.CleanableExecutor$CleaningRunnable.run(CleanableExecutor.java:231)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at org.jboss.threads.ContextClassLoaderSavingRunnable.run(ContextClassLoaderSavingRunnable.java:35)
    at org.jboss.threads.EnhancedQueueExecutor.safeRun(EnhancedQueueExecutor.java:2046)
    at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.doRunTask(EnhancedQueueExecutor.java:1578)
    at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1452)
    at org.jboss.threads.DelegatingRunnable.run(DelegatingRunnable.java:29)
    at org.jboss.threads.ThreadLocalResettingRunnable.run(ThreadLocalResettingRunnable.java:29)
    at java.lang.Thread.run(Thread.java:748)
    at org.jboss.threads.JBossThread.run(JBossThread.java:479)
Caused by: org.jdbi.v3.core.result.ResultSetException: Error thrown mapping result set into return type [statement:"INSERT INTO users (username, password, email, salt, is_admin, is_active) VALUES (:username, :password, :email, :salt, false, true)", arguments:{positional:{0:danny,1:ce672ce95ca0d5792b2d24fa9c58c078a78545d2bfc3354d23c0d5066c1d7aac49fa308a7ffc9b8bf449e9b62e732e4ea742026086ab5e252a0ced2cf59edea8,2:doldy101@gmail.com,3:1b922bc3dadc6a5ecc5e46ba241c1223}, named:{password:ce672ce95ca0d5792b2d24fa9c58c078a78545d2bfc3354d23c0d5066c1d7aac49fa308a7ffc9b8bf449e9b62e732e4ea742026086ab5e252a0ced2cf59edea8,salt:1b922bc3dadc6a5ecc5e46ba241c1223,email:doldy101@gmail.com,username:danny}, finder:[]}]
    at org.jdbi.v3.core.result.ResultSetResultIterator.next(ResultSetResultIterator.java:85)
    at org.jdbi.v3.core.result.ResultIterable.findFirst(ResultIterable.java:204)
    at org.jdbi.v3.sqlobject.statement.internal.ResultReturner$CollectedResultReturner.mappedResult(ResultReturner.java:269)
    at org.jdbi.v3.sqlobject.statement.internal.SqlUpdateHandler.lambda$new$0(SqlUpdateHandler.java:60)
    at org.jdbi.v3.sqlobject.statement.internal.SqlUpdateHandler.lambda$configureReturner$3(SqlUpdateHandler.java:78)
    at org.jdbi.v3.sqlobject.statement.internal.CustomizingStatementHandler.invoke(CustomizingStatementHandler.java:153)
    at org.jdbi.v3.sqlobject.statement.internal.SqlUpdateHandler.invoke(SqlUpdateHandler.java:31)
    at org.jdbi.v3.sqlobject.internal.SqlObjectInitData$1.lambda$invoke$0(SqlObjectInitData.java:125)
    at org.jdbi.v3.core.internal.Invocations.invokeWith(Invocations.java:44)
    at org.jdbi.v3.core.ConstantHandleSupplier.lambda$invokeInContext$0(ConstantHandleSupplier.java:56)
    at org.jdbi.v3.core.internal.Invocations.invokeWith(Invocations.java:44)
    at org.jdbi.v3.core.ConstantHandleSupplier.invokeInContext(ConstantHandleSupplier.java:52)
    at org.jdbi.v3.sqlobject.internal.SqlObjectInitData$1.call(SqlObjectInitData.java:131)
    at org.jdbi.v3.sqlobject.internal.SqlObjectInitData$1.invoke(SqlObjectInitData.java:125)
    at org.jdbi.v3.sqlobject.SqlObjectFactory.lambda$attach$2(SqlObjectFactory.java:110)
    at com.sun.proxy.$Proxy78.addUser(Unknown Source)
    at io.shopik.services.UserService.lambda$addRegularUser$0(UserService.java:29)
    at org.jdbi.v3.core.transaction.LocalTransactionHandler.inTransaction(LocalTransactionHandler.java:134)
    at org.jdbi.v3.core.Handle.inTransaction(Handle.java:422)
    at org.jdbi.v3.core.Jdbi.lambda$inTransaction$4(Jdbi.java:375)
    at org.jdbi.v3.core.Jdbi.withHandle(Jdbi.java:341)
    at org.jdbi.v3.core.Jdbi.inTransaction(Jdbi.java:375)
    at io.shopik.services.UserService.addRegularUser(UserService.java:25)
    at io.shopik.services.UserService_ClientProxy.addRegularUser(UserService_ClientProxy.zig:216)
    at io.shopik.resources.UserResource.createNewRegularUser(UserResource.java:49)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.jboss.resteasy.core.MethodInjectorImpl.invoke(MethodInjectorImpl.java:167)
    at org.jboss.resteasy.core.MethodInjectorImpl.invoke(MethodInjectorImpl.java:130)
    at org.jboss.resteasy.core.ResourceMethodInvoker.internalInvokeOnTarget(ResourceMethodInvoker.java:638)
    at org.jboss.resteasy.core.ResourceMethodInvoker.invokeOnTargetAfterFilter(ResourceMethodInvoker.java:504)
    at org.jboss.resteasy.core.ResourceMethodInvoker.lambda$invokeOnTarget$2(ResourceMethodInvoker.java:454)
    at org.jboss.resteasy.core.interception.jaxrs.PreMatchContainerRequestContext.filter(PreMatchContainerRequestContext.java:364)
    at org.jboss.resteasy.core.ResourceMethodInvoker.invokeOnTarget(ResourceMethodInvoker.java:456)
    at org.jboss.resteasy.core.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:417)
    at org.jboss.resteasy.core.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:391)
    at org.jboss.resteasy.core.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:68)
    at org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:488)
    ... 20 more
Caused by: java.sql.SQLException: Column 'username' not found.
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
    at com.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.result.ResultSetImpl.findColumn(ResultSetImpl.java:556)
    at com.mysql.cj.jdbc.result.ResultSetImpl.getString(ResultSetImpl.java:863)
    at io.agroal.pool.wrapper.ResultSetWrapper.getString(ResultSetWrapper.java:267)
    at io.shopik.entities.UserEntity$MapperImpl.map(UserEntity.java:218)
    at io.shopik.entities.UserEntity$MapperImpl.map(UserEntity.java:214)
    at org.jdbi.v3.core.result.ResultSetResultIterator.next(ResultSetResultIterator.java:83)
    ... 59 more

是说我没有专栏 username 但我可以证实那一列在那里；

我不知道我做错了什么。有什么想法吗？
按照@nbk的建议编辑我删除了表并通过workbench读取，但这次我包含了backticks。这没什么区别！
sql语句

CREATE TABLE users
(
    `id`        int          NOT NULL AUTO_INCREMENT,
    `username`  varchar(100) NOT NULL,
    `password`  text         NOT NULL,
    `email`     varchar(150) NOT NULL,
    `salt`      text         NOT NULL,
    `is_admin`  boolean DEFAULT FALSE,
    `is_active` boolean DEFAULT TRUE,
    PRIMARY KEY (`id`),
    UNIQUE KEY `username` (`username`),
    UNIQUE KEY `email` (`email`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

编辑#2
包括端点和服务方法
用户资源

@Path("/user")
@Consumes(MediaType.APPLICATION_JSON)
@Produces(MediaType.APPLICATION_JSON)
public class UserResource {

    @Inject
    UserService userService;

    @POST
    public Response createNewRegularUser(@Valid UserEntity userEntity) {
        UserEntity user = userService.addRegularUser(userEntity);
        if (user != null) {
            return Response.ok(user).build();
        }
        return Response.noContent().build();
    }

}

用户服务

@ApplicationScoped
public class UserService {

    @Inject
    JdbiProvider dbi;

    public UserEntity addRegularUser(UserEntity user) {
        return dbi.get().inTransaction(handle -> {
            hashPassword(user);
            LOGGER.info("Hashed password for user {} and adding as user", user.getEmail());
            return handle.attach(UserEntity.DAO.class)
                    .addUser(user.getUsername(), user.getPassword(), user.getEmail(), user.getSalt());
        });
    }

}

注：只包括必要的方法，以免问题过重

获取缺少列名的jdbi错误

1条答案

相关问题

热门标签

最新问答