前提・実現したいこと
社員管理できるものをspringbootを使い、JSでAjaxやJqueryを使って作っています。
SQLで社員をキーワード検索できるようにしているのですが、社員全員が表示されてしまい全件検索されてしまいます。
該当のソースコード
SQL
1<select id="search" parameterType="com.example.demo.entity.User" resultType="com.example.demo.entity.User"> 2select 3 e.employee_code, 4 e.employee_name, 5 e.furigana, 6 e.mail_address, 7 e.tel_no, 8 e.affiliation_group as affiliation_group_code, 9 e.position as position_code, 10 a.name as affiliation_group, 11 b.name as position 12from 13 employee_info e 14inner join 15 (select * from code_mast where code='0001') as a 16on 17 e.affiliation_group=a.value 18inner join 19 (select * from code_mast where code='0002') as b 20on 21 e.position=b.value 22<where> 23 <if test="employee_code != ''and employee_code != null"> 24 employee_code like concat('%',#{employee_code},'%') 25 </if> 26 <if test="employee_name != ''and employee_name != null"> 27 employee_name like concat('%',#{employee_name},'%') 28 </if> 29 <if test="furigana != ''and furigana != null"> 30 furigana like concat('%',#{furigana},'%') 31 </if> 32 <if test="mail_address != ''and mail_address != null"> 33 mail_address like concat('%',#{mail_address},'%') 34 </if> 35 <if test="tel_no != ''and tel_no != null"> 36 tel_no like concat('%',#{tel_no},'%') 37 </if> 38 <if test="affiliation_group_code != ''and affiliation_group_code != null"> 39 affiliation_group like concat('%',#{affiliation_group_code},'%') 40 </if> 41 <if test="position_code != ''and position_code != null"> 42 position like concat('%',#{position_code},'%') 43 </if> 44</where> 45</select>
Java
1controllerです 2 3@RestController 4@RequestMapping("/employee") 5public class DBA_Employee_Controller { 6 7 private final Office_Worker_Service office_worker_service; 8 9 @Autowired 10 public DBA_Employee_Controller(Office_Worker_Service office_worker_service) { 11 12 this.office_worker_service = office_worker_service; 13 14 } 15@PostMapping("/search") 16 @ResponseBody 17 public List<User> search(@RequestBody UserForm userForm) { 18 19 User user = new User(); 20 21 user.setEmployee_code(userForm.getEmployee_code()); 22 user.setEmployee_name(userForm.getEmployee_name()); 23 user.setFurigana(userForm.getFurigana()); 24 user.setMail_address(userForm.getMail_address()); 25 user.setTel_no(userForm.getTel_no()); 26 user.setAffiliation_group_code(userForm.getAffiliation_group_code()); 27 user.setPosition_code(userForm.getPosition_code()); 28 29 List<User> resultList = office_worker_service.search(user); 30 31 32 return resultList; 33 } 34@GetMapping("/forSearch") 35 @ResponseBody 36 public List<List<CodeMast>> forSearch(){ 37 38 List<CodeMast> AffiliationGroup = office_worker_service.AllAffiliationGroup(); 39 List<CodeMast> Position = office_worker_service.AllPosition(); 40 41 List<List<CodeMast>> list = new ArrayList<List<CodeMast>>(); 42 43 list.add(AffiliationGroup); 44 list.add(Position); 45 46 return list; 47 }
Java
1package com.example.demo.mapper; 2 3import java.sql.SQLException; 4import java.util.List; 5 6import org.apache.ibatis.annotations.Mapper; 7 8import com.example.demo.entity.CodeMast; 9import com.example.demo.entity.User; 10 11@Mapper 12public interface MyBatis_Mapper { 13 List<User> search(); 14 List<CodeMast> AllAffiliationGroup(); 15 List<CodeMast> AllPosition();
Java
1package com.example.demo.service; 2 3import java.sql.SQLException; 4import java.util.List; 5 6import org.springframework.beans.factory.annotation.Autowired; 7import org.springframework.stereotype.Service; 8 9import com.example.demo.entity.CodeMast; 10import com.example.demo.entity.User; 11import com.example.demo.mapper.MyBatis_Mapper; 12 13@Service 14public class Office_Worker_Service { 15 @Autowired 16 private MyBatis_Mapper mybatis_mapper; 17 18 public List<User> search(User user) { 19 20 List<User> list = mybatis_mapper.search(); 21 22 return list; 23 } 24 public List<CodeMast> AllAffiliationGroup() { 25 26 List<CodeMast> list = mybatis_mapper.AllAffiliationGroup(); 27 28 return list; 29 } 30 public List<CodeMast> AllPosition(){ 31 32 List<CodeMast> list = mybatis_mapper.AllPosition(); 33 34 return list; 35 }
Java
1ゲッター、セッターを用意しており、UserFormクラスも全く同じ中身です。 2 3package com.example.demo.entity; 4 5public class User { 6 private String employee_code; 7 private String employee_name; 8 private String furigana; 9 private String mail_address; 10 private String tel_no; 11 private String affiliation_group; 12 private String position; 13 private String affiliation_group_code; 14 private String position_code;
Java
1ゲッター、セッター用意してあります。 2 3package com.example.demo.entity; 4 5public class CodeMast { 6 7 private String value; 8 private String name; 9 10}
package com.example.demo.app; import org.springframework.stereotype.Controller; import org.springframework.ui.Model; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestMapping; @Controller @RequestMapping("/testapp") public class Employee_Controller { @GetMapping public String testapp(Model model, UserForm userForm) { return "index"; } }
JavaScript
1 //検索結果画面 2 $(document).on("click", "#search", function() { 3 var json1 = { 4 employee_code: $("#employee_code").val(), 5 employee_name: $("#employee_name").val(), 6 furigana: $("#furigana").val(), 7 mail_address: $("#mail_address").val(), 8 tel_no: $("#tel_no").val(), 9 affiliation_group_code: $("#affiliation_group").val(), 10 position_code: $("#position").val(), 11 }; 12 $.ajax({ 13 url: "/employee/search", 14 type: "POST", 15 contentType: "application/json", 16 data: JSON.stringify(json1), 17 dataType: "json", 18 }) 19 .done(function(userList, textStatus, jqXHR) { 20 console.log(jqXHR.status); 21 $("#p1").empty(); 22 $("#p1").append( 23 '<section><br><br><h2>検索結果</h2><br>' + 24 '<table id="shain">' + 25 '<thead>' + 26 '<tr>' + 27 '<th>' + 28 '<label>社員番号<label></th>' + 29 '<th>' + 30 '<label>社員名</label></th>' + 31 '<th>' + 32 '<label>メールアドレス</label></th>' + 33 '<th>' + 34 '<label>役職</label></th>' + 35 '<th>' + 36 '</tr>' + 37 '</thead>' + 38 '<tbody>' 39 ); 40 for (var user in userList) { 41 $("#shain").append( 42 "<tr>" + 43 "<td>" + 44 userList[user].employee_code + 45 "<td>" + 46 userList[user].employee_name + 47 "<td>" + 48 userList[user].mail_address + 49 "<td>" + 50 userList[user].position + 51 "<td>" + 52 "</td>" + 53 "</tr>" 54 ); 55 } 56 $("#p1").append("<tbody></table>") 57 $("#p1").append("<div class='col-md-10' style='text-align: right; margin: 30px;'>") 58 $("#p1").append("</section>"); 59 }) 60 .fail(function(jqXHR, textStatus, errorThrown) { 61 $("#p1").text(jqXHR.status); 62 }) 63 .always(function() {}); 64 }); 65
回答1件
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2021/05/25 02:42
2021/05/25 03:05
2021/05/25 12:32
2021/05/26 03:29
2021/05/27 05:57