[SpringBoot2.4.3] Repository

Repository

@Repository
public interface MyDataRepository extends JpaRepository<MyData, Long> {
	
	@Query("SELECT d FROM MyData d ORDER BY d.name")
	List<MyData> findAllOrderByName();
// 省略

Controller

	@RequestMapping(value="/", method=RequestMethod.GET)
	public ModelAndView index(
			ModelAndView mav) {
		mav.setViewName("index");
		mav.addObject("msg","this is sample content.");
		Iterable<MyData> list = repository.findAllOrderByName();
		mav.addObject("datalist", list);
		return mav;
	}

よく出来てるなー
エンティティ自体にクエリを持たせるか、リポジトリに用意するか、どちらでもできる。

[SpringBoot2.4.3] JPQLを活用

SQLクエリーに似た簡易言語
本格的な検索処理はJPQLを理解する必要がある
1.EntityManagerの用意
2.Queryの作成
3.結果(List)の取得

html

	<table>
	<form action="/find" method="post">
		<tr>
			<td>FIND:</td>
			<td><input type="text" name="fstr" size="20" th:value="${value}"></td></td>
		</tr>
		<tr>
			<td></td>
			<td><input type="submit"></td>
		</tr>
	</form>
	</table>

controller

	@RequestMapping(value="/find", method=RequestMethod.GET)
	public ModelAndView find(ModelAndView mav) {
		mav.setViewName("find");
		mav.addObject("title", "Find Page");
		mav.addObject("msg","MyData sample");
		mav.addObject("value", "");
		Iterable<MyData> list = dao.getAll();
		mav.addObject("datalist", list);
		return mav;
	}
	@RequestMapping(value="/find", method=RequestMethod.POST)
	public ModelAndView search(HttpServletRequest request,
			ModelAndView mav) {
		mav.setViewName("find");
		String param = request.getParameter("fstr");
		if(param =="") {
			mav = new ModelAndView("redirect:/find");
		} else {
			mav.addObject("title","Find result");
			mav.addObject("msg","「" + param + "」の検索結果");
			mav.addObject("value", param);
			List<MyData> list = dao.find(param);
			mav.addObject("datalist", list);
		}
		return mav;
	}

MyDataDao

public interface MyDataDao <T> extends Serializable {
	public List<T> getAll();
	public T findById(long id);
	public List<T> findByName(String name);
	public List<T> find(String fstr);
}
	@Override
	public List<MyData> find(String fstr){
		List<MyData> list = null;
		String qstr = "from MyData where id = :fstr";
		Query query = entityManager.createQuery(qstr).setParameter("fstr", Long.parseLong(fstr));
		list = query.getResultList();
		return list;
	}

[SpringBoot2.4.3] DAOに検索メソッドを追加

MyDataDao.java

import java.io.Serializable;
import java.util.List;

public interface MyDataDao <T> extends Serializable {
	public List<T> getAll();
	public T findById(long id);
	public List<T> findByName(String name);
}

エンティティの追加
MyDataDaoImpl.java

	@Override
	public MyData findById(long id) {
		return (MyData)entityManager.createQuery("from MyData where id = " + id).getSingleResult();
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<MyData> findByName(String name){
		return (List<MyData>)entityManager.createQuery("from MyData where name = " + name).getResultList();
	}

完璧に理解するにはもう少し時間がかかりそうやな

[SpringBoot2.4.3] DAO

データベースアクセスする際のオブジェクト
Data Access Object

Dao interface

package com.example.demo;

import java.io.Serializable;
import java.util.List;

public interface MyDataDao <T> extends Serializable {
	public List<T> getAll();
}

MyDataDaoImpl.java

package com.example.demo;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.Query;

import org.springframework.stereotype.Repository;

@Repository
public class MyDataDaoImpl implements MyDataDao<MyData> {
	private static final long serialVersionUID = 1L;
	
	private EntityManager entityManager;
	
	public MyDataDaoImpl() {
		super();
	}
	public MyDataDaoImpl(EntityManager manager) {
		this();
		entityManager = manager;
	}
	
	@Override
	public List<MyData> getAll(){
		Query query = entityManager.createQuery("from MyData");
		@SuppressWarnings("unchecked")
		List<MyData> list = query.getResultList();
		entityManager.close();
		return list;
	}

}

Controller

@Controller
public class HelloController {

	@Autowired
	MyDataRepository repository;
	
	@PersistenceContext
	EntityManager entityManager;
	
	MyDataDaoImpl dao;
	
	@PostConstruct
	public void init() {
		dao = new MyDataDaoImpl(entityManager);
		MyData d1 = new MyData();
		d1.setName("yamada");
		d1.setAge(20);
		d1.setMail("yamada@gmail.com");
		d1.setMemo("090999999");
		repository.saveAndFlush(d1);
		MyData d2 = new MyData();
		d2.setName("hanako");
		d2.setAge(18);
		d2.setMail("hanako@gmail.com");
		d2.setMemo("080888888");
		repository.saveAndFlush(d2);
		MyData d3 = new MyData();
		d3.setName("sachiko");
		d3.setAge(35);
		d3.setMail("sachiko@gmail.com");
		d3.setMemo("070777777");
		repository.saveAndFlush(d3);
	}	
	
	@RequestMapping(value="/", method=RequestMethod.GET)
	public ModelAndView index(
			@ModelAttribute("formModel") MyData mydata,
			ModelAndView mav) {
		mav.setViewName("index");
		mav.addObject("msg","this is sample content.");
		Iterable<MyData> list = dao.getAll();
		mav.addObject("datalist", list);
		return mav;
	}
// 省略

HTML

	<h1>Helo page</h1>
	<p th:text="${msg}"></p>
	<table>
		<form method="post" action="/" th:object="${formModel}">
		<tr>
			<th>ID</th><th>名前</th><th>メール</th><th>年齢</th><th>メモ(tel)</th>
		</tr>
		<tr th:each="obj: ${datalist}">
			<td th:text="${obj.id}"></td>
			<td th:text="${obj.name}"></td>
			<td th:text="${obj.mail}"></td>
			<td th:text="${obj.age}"></td>
			<td th:text="${obj.memo}"></td>
		</tr>
		</form>
	</table>

なんやとおおおおおおおおおおおおお

[SpringBoot2.4.3] JPAで自動生成可能なメソッド

findById(引数)
And
Or
Between
LessThan
GreaterThan
IsNull
IsNotNull, NotNull
Like
NotLike
OrdeBy … AscやDescをつける
Not
In
NotIn

Repository

package com.example.demo.repositories;

import com.example.demo.MyData;

import java.util.List;
import java.util.Optional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface MyDataRepository extends JpaRepository<MyData, Long> {
	
	public Optional<MyData> findById(Long name);
	public List<MyData> findByNameLike(String name);
	public List<MyData> findByIdIsNotNullOrderByIdDesc();
	public List<MyData> findByAgeGreaterThan(Integer age);
	public List<MyData> findByAgeBetween(Integer age1, Integer age2);
}

単純検索は自動生成で複雑な検索のみDAOを定義
ほう、SpringBoot JPAの仕組みがわかってきた。

[SpringBoot2.4.3] CRUDのdelete

html

		<form method="post" action="/delete" th:object="${formModel}">
		<input type="hidden" name="id" th:value="*{id}">
		<tr>
			<td><p th:text="|名前: *{name}|"></p></td>
		</tr>
		<tr>
			<td><p th:text="|年齢: *{age}|"></p></td>
		</tr>
		<tr>
			<td><p th:text="*{mail}"></p></td>
		</tr>
		<tr>
			<td><p th:text="*{memo}"></p></td>
		</tr>
		<tr>
			<td><input type="submit" value="delete"/></td>
		</tr>
		</form>

controller

	@RequestMapping(value="/delete/{id}", method=RequestMethod.GET)
	public ModelAndView delete(@PathVariable int id,
			ModelAndView mav) {
		mav.setViewName("delete");
		mav.addObject("title", "delete mydata.");
		Optional<MyData> data = repository.findById((long)id);
		mav.addObject("formModel",data.get());
		return mav;
	}
	
	@RequestMapping(value = "delete", method=RequestMethod.POST)
	@Transactional(readOnly=false)
	public ModelAndView remove(@RequestParam long id,
			ModelAndView mav) {
		repository.deleteById(id);
		return new ModelAndView("redirect:/");
	}

ほう、

[SpringBoot2.4.3] CRUDのEdit

Controller

	@RequestMapping(value = "edit/{id}", method=RequestMethod.GET)
	public ModelAndView edit(@ModelAttribute MyData mydata,
			@PathVariable int id, ModelAndView mav) {
		mav.setViewName("edit");
		mav.addObject("title","edit mydata.");
		Optional<MyData> data = repository.findById((long)id);
		mav.addObject("formModel", data.get());
		return mav;
	}
	
	@RequestMapping(value = "edit", method=RequestMethod.POST)
	@Transactional(readOnly=false)
	public ModelAndView update(@ModelAttribute MyData mydata,
			ModelAndView mav) {
		repository.saveAndFlush(mydata);
		return new ModelAndView("redirect:/");
	}

IDの値があるので、saveAndFlushでupdateされる

うお、Javaでやるとビビるな。なんでだろ〜

[SpringBoot2.4.3] HSQLDB/H2

データ初期化処理

import javax.annotation.PostConstruct;

@PostConstruct
public void init() {
	MyData d1 = new MyData();
	d1.setName("yamada");
	d1.setAge(20);
	d1.setMail("yamada@gmail.com");
	d1.setMemo("this is my data!");
	repository.saveAndFlush(d1);
	MyData d2 = new MyData();
	d2.setName("hanako");
	d2.setAge(18);
	d2.setMail("hanako@gmail.com");
	d2.setMemo("hi there!");
	repository.saveAndFlush(d2);
	MyData d3 = new MyData();
	d3.setName("sachiko");
	d3.setAge(35);
	d3.setMail("sachiko@gmail.com");
	d3.setMemo("work hard!");
	repository.saveAndFlush(d3);
}

template/edit.html

<h1>Edit page</h1>
	<table>
		<form method="post" action="/edit" th:object="${formModel}">
		<input type="hidden" name="id" th:value="*{id}">
		<tr>
			<td><label for="name">名前</label></td>
			<td><input type="text" name="name" th:value="*{name}"></td>
		</tr>
		<tr>
			<td><label for="age">年齢</label></td>
			<td><input type="text" name="age" th:value="*{age}"></td>
		</tr>
		<tr>
			<td><label for="mail">メール</label></td>
			<td><input type="text" name="mail" th:value="*{mail}"></td>
		</tr>
		<tr>
			<td><label for="memo">メモ</label></td>
			<td><textarea name="memo" th:text="*{memo}" cols="20" rows="5"></textarea></td>
		</tr>
		<tr>
			<td></td>
			<td><input type="submit"/></td>
		</tr>
		</form>
	</table>

### MyDataRepository

package com.example.demo.repositories;

import com.example.demo.MyData;

import java.util.Optional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface MyDataRepository extends JpaRepository<MyData, Long> {
	
	public Optional<MyData> findById(Long name);
}

なるほど、基礎的なことはわかってきた。

[SpringBoot2.4.3] JPA

エンティティクラスの作成

package com.example.demo;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="mydata")
public class MyData{
	
	@Id
	@GeneratedValue(strategry = GenerationType.AUTO)
	@Column
	private long id;
	
	@Column(length = 50, nullable = false)
	private String name;
	
	@Column(length = 200, nullable = true)
	private String mail;
	
	@Column(nullable = true)
	private Integer age;
	
	@Column(nullable = true)
	private String memo;
	
	public long getId() {
		return id;
	}
	public void setId(long id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getMail() {
		return mail;
	}
	public void setMail(String mail) {
		this.mail = mail;
	}
	public Integer getAge() {
		return age;
	}
	public void setAge(Integer age) {
		this.age = age;
	}
	public String getMemo() {
		return memo;
	}
	public void setMemo(String memo) {
		this.memo = memo;
	}
}

@Entity
エンティティクラスを示すアノテーション
@Table(name=”mydata”)
割り当てられるテーブルを指定
@Id
プライマリーキー
@GeneratedValue(strategry = GenerationType.AUTO)
プライマリーキーのフィールドに対し値を自動生成
@Column
カラム

Repository

package com.example.demo.repositories;

import com.example.demo.MyData;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface MyDataRepository extends JpaRepository<MyData, Long> {
	
}

controller

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

import com.example.demo.repositories.MyDataRepository;

@Controller
public class HelloController {
	
	@Autowired
	MyDataRepository repository;
	
	@RequestMapping("/")
	public ModelAndView index(ModelAndView mav) {
		mav.setViewName("index");
		mav.addObject("msg","this is sample content.");
		Iterable<MyData> list = repository.findAll();
		mav.addObject("data", list);
		return mav;
	}
}

html

	<table>
		<form method="post" action="/" th:object="${formModel}">
		<tr>
			<td><label for="name">名前</label></td>
			<td><input type="text" name="name" th:value="*{name}"></td>
		</tr>
		<tr>
			<td><label for="age">年齢</label></td>
			<td><input type="text" name="age" th:value="*{age}"></td>
		</tr>
		<tr>
			<td><label for="mail">メール</label></td>
			<td><input type="text" name="mail" th:value="*{mail}"></td>
		</tr>
		<tr>
			<td><label for="memo">メモ</label></td>
			<td><textarea name="memo" th:text="*{memo}" cols="20" rows="5"></textarea></td>
		</tr>
		<tr>
			<td></td>
			<td><input type="sbmit"></td>
		</tr>
		</form>
	</table>
	<hr>
	<table>
		<tr><th>ID</th><th>名前</th></tr>
		<tr th:each="obj : ${datalist}">
			<td th:text="${obj.id}"></td>
			<td th:text="${obj.name}"></td>
		</tr>
	</table>

Controller

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.servlet.ModelAndView;

import com.example.demo.repositories.MyDataRepository;

@Controller
public class HelloController {
	
	@Autowired
	MyDataRepository repository;
	
	@RequestMapping(value="/", method=RequestMethod.GET)
	public ModelAndView index(
			@ModelAttribute("formModel") MyData mydata,
			ModelAndView mav) {
		mav.setViewName("index");
		mav.addObject("msg","this is sample content.");
		Iterable<MyData> list = repository.findAll();
		mav.addObject("datalist", list);
		return mav;
	}
	
	@RequestMapping(value="/", method= RequestMethod.POST)
	@Transactional(readOnly=false)
	public ModelAndView form(
			@ModelAttribute("formModel") MyData mydata,
			ModelAndView mav) {
		repository.saveAndFlush(mydata);
		return new ModelAndView("redirect:/");
	}
}

なんやこれは。。。
HSQLDB/H2 はデフォルトでメモリ内にデータをキャッシュしている。

[SpringBoot2.4.2] jdbcTemplateの削除を実装する

index.html

<a th:href="'/test1/delete/' + ${list.id}"><button type="button" class="btn btn-danger">削除</button></a>

delete_complete.html

<h1>社員情報削除 完了</h1>
<div class="col-md-8">
<p>社員の削除が完了しました。</p>
<table class="table">
	<tr><td>名前</td><td th:text="${name}"></td></tr>
	<tr><td>所属</td><td th:text="${department}"></td></tr>
</table>
<button type="button" class="btn btn-primary" onclick="location.href='/test1/index'">一覧に戻る</button>
</div>

UserRepository.java

	public Users delete(Long id) throws DataAccessException {
        String sql1 = ""
            + "SELECT * FROM users WHERE id = ?";
        Map<String, Object> users = jdbcTemplate.queryForMap(sql1, id);
        Users user = new Users();
        user.setName((String)users.get("name"));
		user.setDepartment((String)users.get("department"));
		
		String sql2 = "DELETE FROM users WHERE id = ?";
	    jdbcTemplate.update(sql2, id);
        return user;
    }

MainController.java

	@GetMapping("delete/{userId}")
	public String deleteUser(@PathVariable("userId") long userId, Model model) {
		Users user = usersRepository.delete(userId);
        model.addAttribute("name", user.getName());
        model.addAttribute("department", user.getDepartment());
        return "test1/delete_complete";
	}

一度理解するとあとは早いな
とりあえずCRUD完
authに行きたいが、まずこれでVPSにデプロイしてみたい。