Spring MVC + DbUtils 整合實戰：輕量級資料庫操作零樣板碼

專案建立與依賴

<dependencies>
  <dependency><groupId>org.springframework</groupId><artifactId>spring-webmvc</artifactId><version>5.3.25</version></dependency>
  <dependency><groupId>commons-dbutils</groupId><artifactId>commons-dbutils</artifactId><version>1.7</version></dependency>
  <dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.33</version></dependency>
  <dependency><groupId>com.zaxxer</groupId><artifactId>HikariCP</artifactId><version>5.0.1</version></dependency>
</dependencies>

Spring 配置 applicationContext.xml

<context:component-scan base-package="com.example"/>

<bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource">
  <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC"/>
  <property name="username" value="root"/>
  <property name="password" value="root"/>
</bean>

<bean id="queryRunner" class="org.apache.commons.dbutils.QueryRunner">
  <constructor-arg ref="dataSource"/>
</bean>

<bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
  <property name="prefix" value="/WEB-INF/views/"/>
  <property name="suffix" value=".jsp"/>
</bean>

DAO 層：DbUtils 封裝

@Repository
public class UserDao {
    @Autowired private QueryRunner qr;

    public User findById(Long id) throws SQLException {
        return qr.query("SELECT * FROM user WHERE id=?", new BeanHandler<>(User.class), id);
    }

    public List findAll() throws SQLException {
        return qr.query("SELECT * FROM user", new BeanListHandler<>(User.class));
    }

    public int insert(User u) throws SQLException {
        return qr.update("INSERT INTO user(name,age) VALUES(?,?)", u.getName(), u.getAge());
    }

    public int update(User u) throws SQLException {
        return qr.update("UPDATE user SET name=?,age=? WHERE id=?", u.getName(), u.getAge(), u.getId());
    }

    public int deleteById(Long id) throws SQLException {
        return qr.update("DELETE FROM user WHERE id=?", id);
    }
}

Service 層：事務管理

@Service
@Transactional
public class UserService {
    @Autowired private UserDao userDao;

    public void register(User u) {
        userDao.insert(u);
        // 業務邏輯...
    }
}

Controller 層

@Controller
@RequestMapping("/users")
public class UserController {
    @Autowired private UserService userService;

    @GetMapping("/{id}")
    public String detail(@PathVariable Long id, Model model) throws SQLException {
        model.addAttribute("user", userDao.findById(id));
        return "user/detail";
    }

    @PostMapping
    public String create(@ModelAttribute User u) throws SQLException {
        userService.register(u);
        return "redirect:/users/" + u.getId();
    }
}

DbUtils 核心 Handler 速查

優缺點總結

• ✅ 零樣板碼、無 XML、學習成本極低
• ✅ 適合中小專案、微服務單表操作
• ❌ 複雜關聯查詢、動態 SQL、分頁需手寫 SQL
• ❌ 無二級快取、無延遲載入、非全功能 ORM

建議：單表 CRUD 用 DbUtils，複雜報表/關聯查詢上 MyBatis，兩者共存無衝突。