[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后提供