# CategoryDAO CategoryDAO 這個類比起后面的DAO比較單純，基本上就是提供數據庫相關的CRUD操作： ``` package com.dodoke.dao.inter; import java.util.List; import com.dodoke.bean.Category; public interface CategoryDao { /** * 增加 * * @param category */ public void add(Category category); /** * 刪除 * * @param id */ public void delete(int id); /** * 修改 * * @param bean */ public void update(Category category); /** * 根據id獲取 * * @param id * @return */ public Category get(int id); /** * 分頁查詢 * * @param start * @param count * @return */ public List<Category> list(int start, int count); /** * 查詢所有 * * @return */ public List<Category> list(); /** * 獲取總數 * * @return */ public int getTotal(); } ``` # 實現CategoryDaoImpl ``` package com.dodoke.dao.impl; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import com.dodoke.bean.Category; import com.dodoke.dao.inter.CategoryDao; import com.dodoke.util.DBUtil; public class CategoryDaoImpl implements CategoryDao { @Override public void add(Category category) { String sql = "insert into t_category values(null,?)"; try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) { ps.setString(1, category.getName()); ps.executeUpdate(); ResultSet rs = ps.getGeneratedKeys(); if (rs.next()) { int id = rs.getInt(1); category.setId(id); } } catch (SQLException e) { e.printStackTrace(); } } @Override public void delete(int id) { String sql = "delete from t_category where id = " + id; try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) { ps.executeUpdate(sql); } catch (SQLException e) { e.printStackTrace(); } } @Override public void update(Category category) { String sql = "update t_category set name= ? where id = ?"; try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) { ps.setString(1, category.getName()); ps.setInt(2, category.getId()); ps.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } } @Override public Category get(int id) { Category category = null; String sql = "select * from t_category where id = " + id; try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) { ResultSet rs = ps.executeQuery(sql); if (rs.next()) { category = new Category(); String name = rs.getString("name"); category.setName(name); category.setId(id); } } catch (SQLException e) { e.printStackTrace(); } return category; } @Override public List<Category> list(int start, int count) { List<Category> categorys = new ArrayList<Category>(); String sql = "select * from t_category order by id desc limit ?,? "; try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) { ps.setInt(1, start); ps.setInt(2, count); ResultSet rs = ps.executeQuery(); while (rs.next()) { Category category = new Category(); int id = rs.getInt("id"); String name = rs.getString("name"); category.setId(id); category.setName(name); categorys.add(category); } } catch (SQLException e) { e.printStackTrace(); } return categorys; } @Override public List<Category> list() { // 利用現有的分頁方法，獲取所有數據。至于short.MAX_VALUE數值足夠大，不夠再說 return list(0, Short.MAX_VALUE); } @Override public int getTotal() { int total = 0; String sql = "select count(*) from t_category"; try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) { ResultSet rs = ps.executeQuery(); while (rs.next()) { total = rs.getInt(1); } } catch (SQLException e) { e.printStackTrace(); } return total; } } ```