01-03、JdbcTemplate · 淺入淺出Java Web

JdbcTemplate是Spring MVC內置的對JDBC的一個封裝。 ## [](https://github.com/someus/another-tutorial-about-java-web/blob/master/01-03.md#數據庫準備)數據庫準備 MySQL 5.6。 ~~~ --創建數據庫 CREATE DATABASE IF NOT EXISTS `test` DEFAULT CHARSET utf8 COLLATE utf8_general_ci; USE `test`; --創建table CREATE TABLE IF NOT EXISTS user ( `id` int AUTO_INCREMENT, `name` varchar(255), `email` varchar(255), `age` varchar(255), `passwd` varchar(255), PRIMARY KEY (`id`), UNIQUE KEY (`name`), UNIQUE KEY (`email`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ---插入若干數據 INSERT INTO user (`name`, `email`, `age`, `passwd`) VALUES ('user01', 'user01@163.com', 20, password('123')); INSERT INTO user (`name`, `email`, `age`, `passwd`) VALUES ('user02', 'user02@163.com', 20, password('456')); INSERT INTO user (`name`, `email`, `age`, `passwd`) VALUES ('用戶03', 'user03@163.com', 20, password('456')); ~~~ ## [](https://github.com/someus/another-tutorial-about-java-web/blob/master/01-03.md#示例1)示例1 繼續使用的上一節[01-02、使用Spring MVC構建Hello World](https://github.com/someus/another-tutorial-about-java-web/blob/master/01-02.md)中創建的項目。項目結構如下： [![](https://box.kancloud.cn/2015-10-17_5621d5e57e24e.png)](https://github.com/someus/another-tutorial-about-java-web/blob/master/img/01-03/01.png) 圖中紅線下的文件是新增或者修改的文件。 MySQL的JDBC封裝`mysql-connector-java-**.jar`別忘了放到Libraries里。 ### [](https://github.com/someus/another-tutorial-about-java-web/blob/master/01-03.md#源碼)源碼 **SelectController.java源碼：** ~~~ package me.letiantian.controller; import java.util.List; import java.util.Map; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.servlet.ModelAndView; import org.springframework.web.servlet.mvc.Controller; import org.springframework.jdbc.core.JdbcTemplate; import me.letiantian.service.UserService; public class SelectController implements Controller{ @Autowired private UserService userDao; @Autowired private JdbcTemplate jdbcTemplate; @Override public ModelAndView handleRequest(HttpServletRequest request, HttpServletResponse response) throws Exception { response.setContentType("text/html;charset=UTF-8"); ModelAndView mv = new ModelAndView(); List users = jdbcTemplate.queryForList("SELECT * FROM user"); mv.addObject("users", users); Map user1 = userDao.getUserById(1); mv.addObject("user1", user1); Map user2 = jdbcTemplate.queryForMap("SELECT * FROM user WHERE id=2"); mv.addObject("user2", user2); mv.addObject("message", "無錯誤信息"); mv.setViewName("select"); return mv; } } ~~~ **UserService.java源碼： ** ~~~ package me.letiantian.service; import java.util.Map; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Service; @Service public class UserService { @Autowired private JdbcTemplate jdbcTemplate; public Map getUserById(int id) { Map user = jdbcTemplate.queryForMap("SELECT * FROM user WHERE id=?", new Object[] {id}); return user; } } ~~~ **select.jsp源碼： ** ~~~ <%@page contentType="text/html" pageEncoding="UTF-8"%> <%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%> <!DOCTYPE html> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>JSP Page</title> </head> <body> <h1>Hello World!</h1> <c:if test="${not empty users}"> <ul> <c:forEach var="user" items="${users}"> <li> <c:forEach var="entry" items="${user}"> <c:out value="${entry.key}" /> : <c:out value="${entry.value}" /> </c:forEach> </li> </c:forEach> </ul> </c:if> <hr/> <c:if test="${not empty user1}"> <ul> <c:forEach var="entry" items="${user1}"> <li> <c:out value="${entry.key}" /> <c:out value="${entry.value}" /> </li> </c:forEach> </ul> </c:if> <hr/> <c:if test="${not empty user2}"> <ul> <c:forEach var="entry" items="${user2}"> <li> <c:out value="${entry.key}" /> <c:out value="${entry.value}" /> </li> </c:forEach> </ul> </c:if> <h2>${message}</h2> </body> </html> ~~~ **dispatcher-servlet.xml源碼：** ~~~ <?xml version='1.0' encoding='UTF-8' ?>  <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:p="http://www.springframework.org/schema/p" xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:mvc="http://www.springframework.org/schema/mvc" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.0.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-4.0.xsd"> <bean class="org.springframework.web.servlet.mvc.support.ControllerClassNameHandlerMapping"/> <bean class="org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor"/> <bean id="urlMapping" class="org.springframework.web.servlet.handler.SimpleUrlHandlerMapping"> <property name="mappings"> <props> <prop key="index">indexController</prop> <prop key="hello">helloController</prop> <prop key="select">selectController</prop> </props> </property> </bean> <bean id="viewResolver" class="org.springframework.web.servlet.view.InternalResourceViewResolver" p:prefix="/WEB-INF/jsp/" p:suffix=".jsp" /> <bean name="indexController" class="org.springframework.web.servlet.mvc.ParameterizableViewController" p:viewName="index" /> <bean name="helloController" class="me.letiantian.controller.HelloController" /> <bean name="selectController" class="me.letiantian.controller.SelectController" /> <mvc:resources mapping="/static/**" location="/static/"/> </beans> ~~~ 該文件中新增加了`selectController`，以及`<bean class="org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor"/>`以使得`@Autowired`能夠工作。 **applicationContext.xml源碼：** ~~~ <?xml version='1.0' encoding='UTF-8' ?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:p="http://www.springframework.org/schema/p" xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:context="http://www.springframework.org/schema/context" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.0.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd"> <context:component-scan base-package="me.letiantian.controller" /> <context:component-scan base-package="me.letiantian.service" /> <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource" p:driverClassName="com.mysql.jdbc.Driver" p:url="jdbc:mysql://localhost:3306/test" p:username="root" p:password="123456" /> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource"/> </bean> </beans> ~~~ `dataSource`設置程MySQL，并注入到`jdbcTemplate`。 **運行項目，瀏覽器訪問：** [![](https://box.kancloud.cn/2015-10-17_5621d5e58e2b5.png)](https://github.com/someus/another-tutorial-about-java-web/blob/master/img/01-03/02.png) ## [](https://github.com/someus/another-tutorial-about-java-web/blob/master/01-03.md#資料)資料 **JdbcTemplate中的有多種查詢方法，可以參考：** [JdbcTemplate 查詢](http://www.cnblogs.com/shitianzeng/articles/2318995.html) [Spring JdbcTemplate方法詳解](http://blog.csdn.net/dyllove98/article/details/7772463) **上面的JSP中用到了JSTL，以下幾篇文件可以看一下：** [在JSTL EL中處理java.util.Map，及嵌套List的情況](http://my.oschina.net/letiantian/blog/511920) [JSP 標準標簽庫（JSTL）](http://www.runoob.com/jsp/jsp-jstl.html) **JdbcTemplate也可以使用事務，有聲明式和編程式兩種方法：** [Spring Declarative Transactions](http://www.simplespringtutorial.com/springDeclarativeTransactions.html) [Spring Programmatic Transactions](http://www.simplespringtutorial.com/springProgrammaticTransactions.html) [Spring Programmatic Transaction Management](http://www.tutorialspoint.com/spring/programmatic_management.htm) [Spring JdbcTemplate 與事務管理](http://blog.sina.com.cn/s/blog_63f08ebf0100orev.html) [Transactions with JdbcTemplate](http://www.javacreed.com/transactions-with-jdbctemplate/) **如何使用連接池？** [JDBC Database connection pool in Spring FrameWork - How to SetUp Example](http://javarevisited.blogspot.jp/2012/06/jdbc-database-connection-pool-in-spring.html) [Setup Connection Pooling in Spring MVC](http://stackoverflow.com/questions/3552831/setup-connection-pooling-in-spring-mvc) **其他：** [Spring MVC with JdbcTemplate Example](http://www.codejava.net/frameworks/spring/spring-mvc-with-jdbctemplate-example) [Spring MVC and List Example](http://www.mkyong.com/spring-mvc/spring-mvc-and-list-example/)