Home | 簡體中文 | 繁體中文 | 雜文 | 打賞(Donations) | ITEYE 博客 | OSChina 博客 | Facebook | Linkedin | 知乎專欄 | Search | Email

8.3. Spring Data JPA

8.3.1. @Entity

8.3.1.1. @NotNull 不能為空聲明

@NotNull
public String username;			
			

8.3.1.2. 處理日期時間

public java.sql.Date createdate; 創建日期 YYYY-MM-DD 
public java.util.Date finisheddate; 創建日期時間 YYYY-MM-DD HH:MM:SS
			

Json預設為 yyyy-MM-ddTHH:mm:ss 注意日期與時間中間的T,修改日期格式將T去掉

@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")			
			

8.3.2. Query

		
package api.repository.oracle;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import api.domain.oracle.Member;

@Repository
public interface MemberRepository extends CrudRepository<Member, Long> {
	public Page<Member> findAll(Pageable pageable);

	// public Member findByBillno(String billno);

	public Member findById(String id);

	@Query("SELECT m FROM Member m WHERE m.status = 'Y' AND m.id = :id")
	public Member findFinishById(@Param("id") String id);

}
		
		

8.3.3. Repository

		
	@Autowired
	private ArticleRepository articleRepository;

	@RequestMapping("/mysql")
	@ResponseBody
	public String mysql() {
		articleRepository.save(new Article("Neo", "Chen"));
		for (Article article : articleRepository.findAll()) {
			System.out.println(article);
		}
		Article tmp = articleRepository.findByTitle("Neo");
		return tmp.getTitle();
	}

	@RequestMapping("/search")
	@ResponseBody
	public String search() {

		
		for (Article article : articleRepository.findBySearch(1)) { System.out.println(article); }
		 
		List<Article> tmp = articleRepository.findBySearch(1L);

		tmp.forEach((temp) -> {
			System.out.println(temp.toString());
		});

		return tmp.get(0).getTitle();
	}
		
		

8.3.4. JdbcTemplate

8.3.4.1. execute

jdbcTemplate.execute("CREATE TABLE USER (id integer, name varchar(100))");  			
			

8.3.4.2. queryForInt

int count = jdbcTemplate.queryForInt("SELECT COUNT(*) FROM USER");			
			

8.3.4.3. queryForObject

	private static final Logger log = LoggerFactory.getLogger(ScheduledTasks.class);
	private static final SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-mm-dd HH:mm:ss");
	
	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Scheduled(initialDelay = 1000, fixedRate = 60000)
	public void currentDate() {
		Date date = jdbcTemplate.queryForObject("select sysdate from dual", Date.class);
		log.info("The oracle sysdate is {}", dateFormat.format(date));
	}			
			
			
	@Autowired
	private JdbcTemplate jdbcTemplate;

	@RequestMapping(value = "/article")
	public @ResponseBody String dailyStats(@RequestParam Integer id) {
		String query = "SELECT id, title, content from article where id = " + id;

		return jdbcTemplate.queryForObject(query, (resultSet, i) -> {
			System.out.println(resultSet.getLong(1) + "," + resultSet.getString(2) + "," + resultSet.getString(3));
			return (resultSet.getLong(1) + "," + resultSet.getString(2) + "," + resultSet.getString(3));
		});

	}			
			
			
Integer age = queryForObject("select age from emp", Integer.class);
String name = queryForObject("select name from emp",String.class);
			
8.3.4.3.1. 查詢 Double 類型資料庫
				
	private double getSumByMemberId(int memberId) {
		double result = 0.0d;
		String sql = "SELECT sum(o.price::NUMERIC) as total FROM public.order o group by member_id ="+ memberId;
		try {
			result = jdbcTemplate.queryForObject(sql, Double.class);
		} catch (org.springframework.dao.EmptyResultDataAccessException e) {
			log.info("{} {}", MemberId, e.toString());
		}
		return result;
	}
				
				

8.3.4.4. queryForList

			
List rows = jdbcTemplate.queryForList("SELECT * FROM USER");  
Iterator it = rows.iterator();  
while(it.hasNext()) {  
    Map userMap = (Map) it.next();  
    System.out.print(userMap.get("id") + "\t");  
    System.out.print(userMap.get("name") + "\t");  
    System.out.print(userMap.get("sex") + "\t");  
    System.out.println(userMap.get("age") + "\t");  
}