JDBCTemplate · JAVA

[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 jdbc.initPoolSize=5 jdbc.minPoolSize=2 jdbc.maxPoolSize=10 ~~~ **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 ">  <context:property-placeholder location="classpath:db.properties" />  <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> <property name="initialPoolSize" value="${jdbc.initPoolSize}"></property> <property name="minPoolSize" value="${jdbc.minPoolSize}"></property> <property name="maxPoolSize" value="${jdbc.maxPoolSize}"></property> </bean>  <bean name="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate" > <property name="dataSource" ref="dataSource" ></property> </bean>  <bean name="userDao" class="jdbctemplate.UserDaoImpl" >  <property name="dataSource" ref="dataSource" ></property> </bean> </beans> ~~~ # JDBCTemplate CRUD ## insert/update/delete ~~~ this.jdbcTemplate.update( "insert into t_actoe (first_name, last_name) values (?, ?)", "Leonor", "watling"); ~~~ ~~~ this.jdbcTemplate.update( "update t_actor set last_name = ? where id = ?", "Bangjo", 5276L): ~~~ ~~~ this.jdbcTemplate.update( "delete from actor where id = ?" Long.valueOf(actorId) ); ~~~ 只需要使用JDBCTemplate的update方法就可以執行insert/update/delete操作 ~~~ jdbcTemplate.update("update t_user set name=? where id=?", "tom", 1); ~~~ ~~~ jdbcTemplate.update("insert into t_user values(null, ?, ?, ?)", "張三", 30, "男"); ~~~ ~~~ jdbcTemplate.update("delete from t_user where id = ?", 4); ~~~ ## select ### 簡單數據返回 ~~~ int rowCount = this.jdbcTemplate.queryForObject("select count(*) from t_actor", Integer.class); ~~~ ~~~ int countOfActorsNamedJoe = this.jdbcTemplate.queryForObject( "select count(*) from t_actor where first_name = ?", Integer.class, "joe"); ~~~ ~~~ String lastName = this.jdbcTemplate.queryForObject( "select last_name from t_actor where id = ?", new Object[]{1212L}, String.class); ~~~ ### 復雜數據返回 ~~~ User user = jdbcTemplate.queryForObject("select * from t_user where id = ?", new RowMapper<User>() { @Override public User mapRow(ResultSet rs, int rowNum) throws SQLException { //完成結果集封裝成user對象操作 User user = new User(); user.setAge(rs.getInt("age")); user.setId(rs.getInt("id")); return user; } }, 1); ~~~ ~~~ List<User> user = jdbcTemplate.query("select * from t_user", new RowMapper<User>() { @Override public User mapRow(ResultSet rs, int rowNum) throws SQLException { //完成結果集封裝成user對象操作 User user = new User(); user.setAge(rs.getInt("age")); user.setId(rs.getInt("id")); return user; } }); ~~~ ~~~ Interface RowMapper<T> ~~~ RowMapper它有一個實現類叫BeanPropertyRowMapper 如果使用BeanPropertyRowmapper，實體類必須提供一個無參數的public構造方法，類中的bean屬性名稱與表中的列要對應注意：這個類是在spring2.5后提供 ~~~ User user = jdbcTemplate.queryForObject("select * from t_user where id=?", new BeanPropertyRowMapper<User>(User.class), 1); ~~~ ~~~ List<User> user = jdbcTemplate.query("select * from t_user", new BeanPropertyRowMapper<User>(User.class)); ~~~