---
tags: Spring MVC
---
# 存取資料庫資料
Spring鼓勵藉由介面而不透過類別寫程式，讓程式更加彈性。
因此準備Service介面、Service、DAO介面、DAO以及Controller進行控制(前提，必須準備好bean，如Bookbeab、CompanyBean、MemberBean)

## Service介面(把所有要做的方法列出來):

public interface ProductService {

	List<BookBean> getAllProduct();
	
	void updateAllStocks();
	
	List<String> getAllCategories();
	
	List<BookBean> getProductByCategory(String category);
	
	public BookBean getProductById(int productId);
	
	void  addProduct(BookBean product);
	
	CompanyBean  getCompanyById(int companyId);
	
	List<CompanyBean>  getCompanyList();
}

## Service.java

1. 註釋@Service
2. 要宣告session工廠，並透過@Autowired建構子注入
3. 每個方法都是一個交易，因此要註釋@Transactional，代表方法執行時，會自動開交易，結束會自動關交易，有錯誤會自動rollback

@Service
public class ProductServiceImpl implements ProductService {

	SessionFactory factory;
	ProductDao productDao;//這是ProductDao 介面，不是類別(也就是對介面操作，不要對類別操作)
	
	
	@Autowired
	public ProductServiceImpl(SessionFactory factory, ProductDao productDao) {
		this.factory = factory;
		this.productDao = productDao;
	}

/*
	@Transactional
	@Override
	public List<BookBean> getAllProduct() {
		Session session = factory.getCurrentSession();
		Transaction tx=null;
		List<BookBean> list=null;
		try {
			tx=session.beginTransaction();
			list = productDao.getAllProduct();
			tx.commit();
		} catch (Exception e) {
			if(tx!=null) {
				tx.rollback();
			}
			throw new RuntimeException(e);
		}
		return list;
	}
 */
	
	
	//select All Product
	@Transactional
	@Override
	public List<BookBean> getAllProduct() {
		List<BookBean> list=productDao.getAllProduct();
		return list;
	
	}

	//update By Id
	@Transactional
	@Override
	public void updateAllStocks() {
	    List<BookBean> allProducts = productDao.getAllProduct();
	    for(BookBean bb : allProducts) {
	        if (bb.getStock() != null && bb.getStock() < 50) {
	        	productDao.updateStock(bb.getBookId(), bb.getStock() + 50);
	        }
	    }
	}

	//select All Categories
	@Transactional
	@Override
	public List<String> getAllCategories() {
		// TODO Auto-generated method stub
		return productDao.getAllCategories();
	}

	
	//select by Category
	@Transactional
	@Override
	public List<BookBean> getProductByCategory(String category) {
		// TODO Auto-generated method stub
		return productDao.getProductByCategory(category);
	}

	//select Product By Id
	@Transactional
	@Override
	public BookBean getProductById(int productId) {
		
		return productDao.getProductById(productId);
	}
	
	//Insert Product
	@Transactional
	@Override
	public void addProduct(BookBean product) {
		// TODO Auto-generated method stub
		productDao.addProduct(product);
	}
	
	//select company by id
	@Transactional
	@Override
	public CompanyBean getCompanyById(int companyId) {
		// TODO Auto-generated method stub
		return productDao.getCompanyById(companyId);
	}
	
	// select All company
	@Transactional
	@Override
	public List<CompanyBean> getCompanyList() {
		// TODO Auto-generated method stub
		return productDao.getCompanyList();
	}

}

## DAO介面(把所有要做的方法列出來):

public interface ProductDao {

	//介面把所有要做的方法列出來
	
	List<BookBean> getAllProduct();
	
	void updateStock(int productId,int newQuantity);
	
	List<String> getAllCategories();
	
	List<BookBean> getProductByCategory(String category);
	
	public BookBean getProductById(int productId);
	
	
	void  addProduct(BookBean product);
	
	CompanyBean  getCompanyById(int companyId);
	
	List<CompanyBean>  getCompanyList();
}

## DAO:
1. 註釋@Repository
2. 要宣告session工廠，並透過@Autowired建構子注入

@Repository
public class ProductDaoImpl implements ProductDao {
	SessionFactory factory;
	// RootAppConfig.java已經註冊了，所以直接透過下面的建構子@Autowired

	@Autowired
	public ProductDaoImpl(SessionFactory factory) {
		this.factory = factory;
	}

//select All
	@Override
	public List<BookBean> getAllProduct() {
		Session session = factory.getCurrentSession();
		String hql = "FROM BookBean";
		List<BookBean> resultList = session.createQuery(hql, BookBean.class).getResultList();
		return resultList;
	}

//update By Id
	@Override
	public void updateStock(int productId, int newQuantity) {
		Session session = factory.getCurrentSession();
		String hql = "UPDATE BookBean bb SET bb.stock= :qty WHERE bb.bookId = :id";
		session.createQuery(hql).setParameter("qty", newQuantity).setParameter("id", productId).executeUpdate();
	}

//select All Categories
	@Override
	public List<String> getAllCategories() {
		Session session = factory.getCurrentSession();
		String hql = "SELECT DISTINCT bb.category FROM BookBean bb";
		return session.createQuery(hql, String.class).getResultList();
	}

//select by Category
	@Override
	public List<BookBean> getProductByCategory(String category) {
		Session session = factory.getCurrentSession();
		String hql = "FROM BookBean bb WHERE bb.category = :cate";
		return session.createQuery(hql, BookBean.class).setParameter("cate", category).getResultList();
	}

//select Product By Id
	@Override
	public BookBean getProductById(int productId) {
		Session session = factory.getCurrentSession();
		BookBean bb = session.get(BookBean.class, productId);
		if (bb == null) {
			throw new ProductNotFoundException("產品編號：" + productId + "找不到", productId);
		}
		return bb;
	}

//Insert Product
	@Override
	public void addProduct(BookBean product) {
		Session session = factory.getCurrentSession();
		CompanyBean cb = getCompanyById(product.getCompanyId());
		product.setCompanyBean(cb);
		session.save(product);
	}

//select company by id
	@Override
	public CompanyBean getCompanyById(int companyId) {

		Session session = factory.getCurrentSession();
		CompanyBean cb = session.get(CompanyBean.class, companyId);
		return cb;
	}

	// select All company
	@Override
	public List<CompanyBean> getCompanyList() {
		Session session = factory.getCurrentSession();
		String hql = "FROM CompanyBean";
		List<CompanyBean> list = session.createQuery(hql, CompanyBean.class).getResultList();
		return list;
	}

}

## Controller
1. 註釋@Controller
2. 要宣告session工廠，並透過@Autowired建構子注入

@Controller
public class ProductController {

	
	ProductService productService;
    ServletContext servletContext;//後面抓圖片要用的
    
    @Autowired
	public ProductController(ProductService productService, ServletContext servletContext) {
		this.productService = productService;
		this.servletContext = servletContext;
	}
    
    @GetMapping("/products")
	public String list(Model model) {
		
		List<BookBean> beans = productService.getAllProduct();
		model.addAttribute("products",beans);
		
		return "products";	
		
	}
    //後面可以有更多請求
    
    }

架構
DAO處理資料庫相關功能
Service除了資料庫相關方法(所以import DAO介面操作)，還有商業邏輯的方法。
Control透過Service進行方法執行