[TOC] # 编码 一个小的实例 ~~~ // 1. 创建一个数据库连接池 DriverManagerDataSource dataSource = new DriverManagerDataSource(); // 2. 设置datasource相关的属性(driverclass url username password) dataSource.setDriverClassName("com.mysql.jdbc.Driver"); dataSource.setUrl("jdbc:mysql:///app"); dataSource.setUsername("root"); dataSource.setPassword("root"); // 3.创建一个JdbcTemplate来操作 JdbcTemplate jdbcTemplate = new JdbcTemplate(); jdbcTemplate.setDataSource(dataSource); // execute方法可执行任意sql jdbcTemplate.execute("update t_user set name='张三' where id =1"); ~~~ 表 ~~~ CREATE TABLE `t_user` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; ~~~ user类 ~~~ package bean; public class User { private Integer id; private String name; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } } ~~~ 接下来看 **UserDao类** ~~~ package jdbctemplate; import java.util.List; import bean.User; public interface UserDao { // 增 void save(User u); // 删 void delete(Integer id); // 改 void update(User u); // 查 User getById(Integer id); // 查 int getTotalCount(); // 查 List<User> getAll(); } ~~~ **UserDaoImpl类** ~~~ package jdbctemplate; import java.sql.ResultSet; import java.sql.SQLException; import java.util.List; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.support.JdbcDaoSupport; import bean.User; //使用jdbc模板实现增删改查 public class UserDaoImpl extends JdbcDaoSupport implements UserDao { @Override public void save(User u) { String sql = "insert into t_user values(null,?)"; super.getJdbcTemplate().update(sql, u.getName()); } @Override public void delete(Integer id) { String sql = "delete from t_user where id = ?"; super.getJdbcTemplate().update(sql); } @Override public void update(User u) { String sql = "update t_user set name=? where id=?"; super.getJdbcTemplate().update(sql, u.getName(), u.getId()); } @Override public User getById(Integer id) { String sql = "select * from t_user where id = ?"; return super.getJdbcTemplate().queryForObject(sql, new RowMapper<User>() { @Override public User mapRow(ResultSet rs, int arg1) throws SQLException { User u = new User(); u.setId(rs.getInt("id")); u.setName(rs.getString("name")); return u; } }, id); } @Override public int getTotalCount() { String sql = "select count(*) from t_user"; Integer count = super.getJdbcTemplate().queryForObject(sql, Integer.class); return count; } @Override public List<User> getAll() { String sql = "select * from t_user"; List<User> list = super.getJdbcTemplate().query(sql, new RowMapper<User>() { @Override public User mapRow(ResultSet rs, int arg1) throws SQLException { User u = new User(); u.setId(rs.getInt("id")); u.setName(rs.getString("name")); return u; } }); return list; } } ~~~ 我们把数据库连接信息放在 **db.properties** ~~~ jdbc.jdbcUrl=jdbc:mysql:///app jdbc.driverClass=com.mysql.jdbc.Driver jdbc.user=root jdbc.password=root ~~~ **applicationContext.xml** ~~~ <?xml version="1.0" encoding="UTF-8"?> <beans xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.springframework.org/schema/beans" xmlns:p="http://www.springframework.org/schema/p" xmlns:context="http://www.springframework.org/schema/context" xmlns:aop="http://www.springframework.org/schema/aop" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.2.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.2.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.2.xsd "> <!-- 指定spring读取db.properties配置 --> <context:property-placeholder location="classpath:db.properties" /> <!-- 1.将连接池放入spring容器 --> <bean name="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" > <property name="jdbcUrl" value="${jdbc.jdbcUrl}" ></property> <property name="driverClass" value="${jdbc.driverClass}" ></property> <property name="user" value="${jdbc.user}" ></property> <property name="password" value="${jdbc.password}" ></property> </bean> <!-- 2.将JDBCTemplate放入spring容器 --> <bean name="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate" > <property name="dataSource" ref="dataSource" ></property> </bean> <!-- 3.将UserDao放入spring容器 --> <bean name="userDao" class="jdbctemplate.UserDaoImpl" > <!-- <property name="jt" ref="jdbcTemplate" ></property> --> <property name="dataSource" ref="dataSource" ></property> </bean> </beans> ~~~ # JdbcTemplate CRUD ## insert update delete  ## select ### 简单数据返回   ### 复杂数据返回  注意：如果只返回一个domain对象，可以使用queryForObject方法，如果返回的是List<?>对象，可以使用query方法，但是都需要使用RowMapper来对ResultSet进行处理。    RowMapper它有一个实现类叫BeanPropertyRowMapper 如果使用BeanPropertyRowmapper，实体类必须提供一个无参数的public构造方法，类中的bean属性名称与表中的列要对应 注意：这个类是在spring2.5后提供