SpringMVC 应用 JdbcTemplate 访问数据库

2015-11-11 22:53:47   最后更新: 2016-08-25 17:50:30   访问数量:667




Spring JDBC 包提供了 JdbcTemplate 和它的两个兄弟 SimpleJdbcTemplate和NamedParameterJdbcTemplate

JdbcTemplate 实现了对 JDBC 的初步封装,简化了数据库操作,让数据库操作更加便利和优雅

 

使用 JdbcTemplate 前,需要在 pom.xml 中引入依赖:

<dependency> <groupId>commons-dbcp</groupId> <artifactId>commons-dbcp</artifactId> <version>1.4</version> </dependency>

 

 

reimport 后就可以使用 JdbcTemplate 了

 

在 servlet-config.xml 中加入 JdbcTemplate 的配置:

<!-- dataSource --> <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName"> <value>com.mysql.jdbc.Driver</value> </property> <property name="url" value="jdbc:mysql://localhost:3306/oa?characterEncoding=gb2312" /> <property name="username" value="root" /> <property name="password" value="root" /> <property name="maxActive" value="100" /> <property name="maxIdle" value="30" /> <property name="maxWait" value="1000" /> <property name="defaultAutoCommit" value="true" /> <property name="removeAbandoned" value="true" /> </bean> <!-- jdbcTemplate --> <bean id="jdbc" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource"> <ref bean="dataSource" /> </property> </bean>

 

 

DAO implement

与之前一样,我们使用 CustomerDAO:

package com.demo.model; /** * Created by techlog on 15/11/9. */ public class Customer { int custId; String name; int age; public Customer(int cust_id, String name, int age) { this.age = age; this.name = name; this.custId = cust_id; } public int getCustId() { return custId; } public void setCustId(int custId) { this.custId = custId; } public String getName() { return name; } public void setName(String name) { this.name = name; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } }

 

 

然后实现实现类:

package com.demo.dao.implement; import com.demo.dao.CustomerDAO; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Repository; import javax.annotation.Resource; /** * Created by techlog on 15/11/10. */ @Repository public class JdbcCustomerDAO implements CustomerDAO { @Resource private JdbcTemplate jdbcTemplate; @Override public int getTotal() { return jdbcTemplate.queryForObject("select count(*) from customer", Integer.class); } }

 

 

这里通过 @Repository 注解声明他是一个 DAO,并且使用 @Resource 注解让 IOC 注入了 JdbcTemplate 类型的 bean,这样我们可以在方法中使用他

这个方法中,我们返回了表中的数据数

关于 JdbcTemplate 的方法,我们后面再进行介绍

 

Controller

实现了 DAO,我们就要在 Controller 中使用它了

 

package com.demo.controller; import com.demo.dao.CustomerDAO; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.servlet.ModelAndView; import javax.annotation.Resource; @Controller @RequestMapping(value = "/jdbc") public class HomeController { @Resource private CustomerDAO customerDAO; @RequestMapping(value = "/total") public ModelAndView insertandfind(ModelAndView modelAndView) { modelAndView.addObject("customer", customerDAO.getTotal()); modelAndView.setViewName("test"); return modelAndView; } }

 

 

通过 @Resource 注解让 IOC 注入了 CustomerDAO,如果 CustomerDAO 接口有多个实现类,可以通过 @Resource 的 name 参数指定类名,也可以通过 @Qualifier 注解的 value 参数指定

 

jsp

<%-- Created by IntelliJ IDEA. User: techlog Date: 15/11/10 Time: 下午7:06 To change this template use File | Settings | File Templates. --%> <%@ page contentType="text/html;charset=UTF-8" language="java" %> <html> <head> <title>Title</title> <h1>${customer}</h1> </head> <body> </body> </html>

 

 

通过访问 http://localhost:8080/jdbc/total 看到正确返回了数据库中 customer 表的行数

 

在 servlet-config.xml 中,我们写入了 database 的 url、username、password 等信息,这些信息如果放在配置文件中进行配置会更加便捷和清晰

在 servlet-config.xml 中加入配置:

<context:property-placeholder location="classpath:config.properties"/> <!-- 配置文件 -->

 

 

在 resources/config.properties 中写入:

dburl=jdbc:mysql://localhost:3306/test?characterEncoding=gb2312 username=root password=pswd

 

 

servlet-config.xml 配置就可以改为:

<!-- JDBC 配置 --> <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName"> <value>com.mysql.jdbc.Driver</value> </property> <property name="url" value="${dburl}" /> <property name="username" value="${username}" /> <property name="password" value="${password}" /> <property name="maxActive" value="100" /> <property name="maxIdle" value="30" /> <property name="maxWait" value="1000" /> <property name="defaultAutoCommit" value="true" /> <property name="removeAbandoned" value="true" /> </bean>

 

 

excute

最简单的用法当然是 public void execute(String sql),这个方法执行一条 SQL 语句,出错则抛出 DataAccessException,一般用来执行 DDL 语句

 

queryForObject

针对 select 语句,如果是 select 一个字符串或数字,使用 queryForObject 也是很方便的,比如 jdbcTemplate.queryForObject("select Count(*) from customer", Integer.class),他将返回一个 Integer 对象,但是如果 select 结果是一个多个成员的对象,jdbcTemplate 将无法处理,因为他无法理解列的对应关系,这时就需要使用下面的方法

 

ResultSetExtractor

可以使用这样的方式来实现接口

@Override public ArrayList<Customer> getCustomers() { return (ArrayList<Customer>) jdbcTemplate.query("select * from customer", new ResultSetExtractor() { @Override public Object extractData(ResultSet resultSet) throws SQLException, DataAccessException { ArrayList<Customer> customers = new ArrayList<Customer>(); while (resultSet.next()) { Customer customer = new Customer(); customer.setAge(resultSet.getInt("age")); customer.setCustId(resultSet.getInt("cust_id")); customer.setName(resultSet.getString("name")); customers.add(customer); } return customers; } }); }

 

 

RowCallbackHandler

也可以使用这样的方式:

@Override public ArrayList<Customer> getTotal() { final ArrayList<Customer> customers = new ArrayList<Customer>(); jdbcTemplate.query("select * from customer", new RowCallbackHandler() { @Override public void processRow(ResultSet resultSet) throws SQLException { Customer customer = new Customer(); customer.setAge(resultSet.getInt("age")); customer.setCustId(resultSet.getInt("cust_id")); customer.setName(resultSet.getString("name")); customers.add(customer); } }); return customers; }

 

 

RowMapper

@Override public ArrayList<Customer> getTotal() { return jdbcTemplate.query("select * from customer", new RowMapper() { @Override public Object mapRow(ResultSet resultSet, int i) throws SQLException { Customer customer = new Customer(); customer.setAge(resultSet.getInt("age")); customer.setCustId(resultSet.getInt("cust_id")); customer.setName(resultSet.getString("name")); return customer; } } }

 

 

Spring JDBC最佳实践(3) http://my.oschina.net/u/218421/blog/38598

Class JdbcTemplate https://docs.spring.io/spring/docs/current/javadoc-api/org/springframework/jdbc/core/JdbcTemplate.html

 






技术帖      sql      database      龙潭书斋      execute      query      spring      springmvc      jdbc      jdbctemplate      ddl     


京ICP备15018585号