ソフトウェアエンジニアの技術ブログ：Software engineer tech blog

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;
	}

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();
	}

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

データベースアクセスする際のオブジェクト
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>

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

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の仕組みがわかってきた。

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:/");
	}

ほう、

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でやるとビビるな。なんでだろ〜

データ初期化処理

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);
}

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