Q&A
前提
Spring Boot version '2.5.6'
認証機能は、Spring Securityを使用
DB : PostgreSQL13
GIt Hub該当のソース:https://github.com/NishiguchiHiroki/TodoApp
質問の内容:
PostgreSQLでenum型のauthorityというカラムを作りました。
createForm.htmlで入力した値(権限)をibatisを使用し、authorityに入れるときエラーが発生します。
言っていることはわかるのですが、コードの書き方がわからないのと、どこを修正すべきかわかりません。
発生している問題・エラーメッセージ
エラー文:column "authority" is of type authority but expression is type character Various ヒント: 式を書き直すか、キャストする必要があります
org.postgresql.util.PSQLException: ERROR: column "authority" is of type authority but expression is of type character varying ヒント: You will need to rewrite or cast the expression. 位置: 65 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2565) ~[postgresql-42.2.24.jar:42.2.24] at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2297) ~[postgresql-42.2.24.jar:42.2.24] at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:322) ~[postgresql-42.2.24.jar:42.2.24] at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481) ~[postgresql-42.2.24.jar:42.2.24] at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401) ~[postgresql-42.2.24.jar:42.2.24] at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164) ~org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433) ~[mybatis-spring-2.0.1.jar:2.0.1] at com.sun.proxy.$Proxy68.insert(Unknown Source) ~[na:na] at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:278) ~[mybatis-spring-2.0.1.jar:2.0.1] at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:62) ~[mybatis-3.5.1.jar:3.5.1] at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:58) ~[mybatis-3.5.1.jar:3.5.1] at com.sun.proxy.$Proxy101.insert(Unknown Source) ~[na:na] at com.example.its.domain.auth.UserService.create(UserService.java:29) ~[main/:na] at com.example.its.web.user.UserController.create(UserController.java:42) ~[main/:na]
該当のソースコード
createForm.html
1<!DOCTYPE html> 2<html xmlns:th="http://www.thymeleaf.org" 3 th:replace="~{fragments/layout :: layout(~{::title}, ~{::body})}"> 4<head> 5 <title>ユーザー作成 | 課題管理アプリケーション</title> 6</head> 7<body> 8 <h1 class="mt-3">ユーザー作成</h1> 9 <a href="../index.html" th:href="@{/}">トップページ</a> 10 11 <form action="#" th:action="@{/users}" method="post" th:object="${userForm}"> 12 <!-- ユーザー名 --> 13 <div class="mt-3"> 14 <label for="usernameInput" class="form-label">ユーザー名</label> 15 <input type="text" id="usernameInput" class="form-control" th:field="*{username}" 16 th:classappend="${#fields.hasErrors('username')} ? is-invalid"> 17 <p class="invalid-feedback" th:if="${#fields.hasErrors('username')}" th:errors="*{username}">(error)</p> 18 </div> 19 <!-- パスワード --> 20 <div class="mt-3"> 21 <label for="passwordInput" class="form-label">パスワード</label> 22 <input type="password" id="passwordInput" class="form-control" th:field="*{password}" 23 th:classappend="${#fields.hasErrors('password')} ? is-invalid"> 24 <p class="invalid-feedback" th:if="${#fields.hasErrors('password')}" th:errors="*{password}">(error)</p> 25 </div> 26 <div class="mt-3"> 27 <lable class="form-label" for="AuthorityRadio">権限</lable> 28 <div id="AuthorityRadio"> 29 <div class="form-check" th:classappend="${#fields.hasErrors('authority')} ? is-invalid"> 30 <input class="form-check-input" type="radio" id="authorityAdminRadio" th:field="*{authority}" value="ADMIN" 31 th:classappend="${#fields.hasErrors('authority')} ? is-invalid"> 32 <label class="form-check-label" for="authorityAdminRadio">ADMIN</label> 33 </div> 34 <div class="form-check" th:classappend="${#fields.hasErrors('authority')} ? is-invalid"> 35 <input class="form-check-input" type="radio" id="authorityUserRadio" th:field="*{authority}" value="USER" 36 th:classappend="${#fields.hasErrors('authority')} ? is-invalid"> 37 <label class="form-check-label" for="authorityUserRadio">USER</label> 38 </div> 39 <p class="invalid-feedback" th:if="${#fields.hasErrors('authority')}" 40 th:errors="*{authority}">(errors)</p> 41 </div> 42 </div> 43 <!-- ボタン --> 44 <div class="mt-3"> 45 <button type="submit" class="btn btn-primary">作成</button> 46 <a href="./list.html" th:href="@{/users}" class="btn btn-secondary">キャンセル</a> 47 </div> 48 </form> 49</body> 50</html>
UserForm.java
1package com.example.its.web.user; 2 3import javax.validation.constraints.NotBlank; 4import javax.validation.constraints.Size; 5 6import com.example.its.web.Validation.UniqueUsername; 7 8import lombok.AllArgsConstructor; 9import lombok.Data; 10 11@Data 12@AllArgsConstructor 13public class UserForm { 14 15 @NotBlank 16 @UniqueUsername 17 private String username; 18 19 @NotBlank 20 @Size(min=12, max = 128) 21 private String password; 22 23 private String authority; 24 25} 26
UserController.java
1package com.example.its.web.user; 2 3import org.springframework.security.core.context.SecurityContextHolder; 4import org.springframework.stereotype.Controller; 5import org.springframework.ui.Model; 6import org.springframework.validation.BindingResult; 7import org.springframework.validation.annotation.Validated; 8import org.springframework.web.bind.annotation.GetMapping; 9import org.springframework.web.bind.annotation.ModelAttribute; 10import org.springframework.web.bind.annotation.PostMapping; 11import org.springframework.web.bind.annotation.RequestMapping; 12import com.example.its.domain.auth.UserService; 13 14import lombok.RequiredArgsConstructor; 15 16@Controller 17@RequestMapping("/users") 18@RequiredArgsConstructor 19public class UserController { 20 21 private final UserService userService; 22 23 @GetMapping 24 public String showList(Model model) { 25 var x = SecurityContextHolder.getContext().getAuthentication().getPrincipal(); 26 model.addAttribute("userList", userService.findAll()); 27 return "users/list"; 28 } 29 30 //Get /user/creationForm 31 @GetMapping("/creationForm") 32 public String showCreationForm(@ModelAttribute UserForm form) { 33 return "users/creationForm"; 34 } 35 36 @PostMapping 37 public String create(@Validated UserForm form, BindingResult bindingResult) { 38 if (bindingResult.hasErrors()) { 39 return showCreationForm(form); 40 } 41 42 userService.create(form.getUsername(), form.getPassword(), form.getAuthority() ); 43 return "redirect:/users"; 44 } 45 46}
UserRepository.java
1package com.example.its.domain.auth; 2 3import java.util.List; 4import java.util.Optional; 5 6import org.apache.ibatis.annotations.Insert; 7import org.apache.ibatis.annotations.Mapper; 8import org.apache.ibatis.annotations.Param; 9import org.apache.ibatis.annotations.Select; 10 11import com.example.its.domain.auth.User.Authority; 12 13@Mapper 14public interface UserRepository { 15 16 @Select("select username, email, password, enabled, authority from users where email = #{email}") 17 Optional<User> findByUsername(String email); 18 19 @Select("select username, email, password, enabled, authority from users") 20 List<User> findAll(); 21 22 @Insert("insert into users(username, password, authority) values(#{username}, #{password}, #{authority})") 23 void insert(@Param("username") String username, @Param("password") String password, @Param("authority") String authority); 24 25}
UserService.java
1 2package com.example.its.domain.auth; 3 4import java.util.List; 5 6import org.springframework.security.access.prepost.PreAuthorize; 7import org.springframework.security.crypto.password.PasswordEncoder; 8import org.springframework.stereotype.Service; 9 10import com.example.its.domain.auth.User.Authority; 11 12import lombok.RequiredArgsConstructor; 13 14 15@Service 16@RequiredArgsConstructor 17public class UserService { 18 19 private final UserRepository userRepository; 20 private final PasswordEncoder passwordEncoder; 21 22 @PreAuthorize("hasAuthority('ADMIN')") 23 public List<User> findAll() { 24 return userRepository.findAll(); 25 } 26 27 @PreAuthorize("hasAuthority('ADMIN')") 28 public void create(String username, String password, String authority) { 29 var encodePassword = passwordEncoder.encode(password); 30 userRepository.insert(username, encodePassword, authority); 31 } 32 33}
試したこと
①UserService.javaで引数で受け取ったauthorityをキャストしました。
以下を試しましたが、エラー文は変わらず。
import java.util.List; import org.springframework.security.access.prepost.PreAuthorize; import org.springframework.security.crypto.password.PasswordEncoder; import org.springframework.stereotype.Service; import com.example.its.domain.auth.User.Authority; import lombok.RequiredArgsConstructor; @Service @RequiredArgsConstructor public class UserService { private final UserRepository userRepository; private final PasswordEncoder passwordEncoder; @PreAuthorize("hasAuthority('ADMIN')") public List<User> findAll() { return userRepository.findAll(); } @PreAuthorize("hasAuthority('ADMIN')") public void create(String username, String password, String authority) { var encodePassword = passwordEncoder.encode(password); Authority auth = Authority.valueof("authority") userRepository.insert(username, encodePassword, auth); } }
②全てのファイルで定義しているauthoirtyのデータ型をString →Authorityに変更。
全く別のエラーに変わりましたので、対策せず。
以上、ご教授をお願い致します。
回答1件
あなたの回答
tips
プレビュー
下記のような回答は推奨されていません。
このような回答には修正を依頼しましょう。
2022/08/14 09:37
2022/08/14 10:34 編集
2022/08/14 12:36