spring再学习之整合JDBC
一、JDBCTemplate JDBC模板
user类
package cn.itcast.bean; import java.util.Date; public class User { private Integer id; private String username; private Date birthday; private String sex; private String address; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username == null ? null : username.trim(); } public Date getBirthday() { return birthday; } public void setBirthday(Date birthday) { this.birthday = birthday; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex == null ? null : sex.trim(); } public String getAddress() { return address; } public void setAddress(String address) { this.address = address == null ? null : address.trim(); } }
准备数据库：
package cn.itcast.a_jdbctemplate; import java.beans.PropertyVetoException; import org.junit.Test; import org.springframework.jdbc.core.JdbcTemplate; import com.mchange.v2.c3p0.ComboPooledDataSource; //演示JDBC模板 public class Demo { @Test public void fun1() throws Exception { //0准备连接池C3p0 ComboPooledDataSource dataSource = new ComboPooledDataSource(); dataSource.setDriverClass("com.mysql.jdbc.Driver"); dataSource.setJdbcUrl("jdbc:mysql:///mybatis"); dataSource.setUser("root"); dataSource.setPassword("mysqladmin"); //1创建JDBC模板对象 JdbcTemplate it = new JdbcTemplate(); it.setDataSource(dataSource); //2、书写sql语句，并执行 String sql="insert into user(username) value('rose')"; it.update(sql); } }
结果：
二、UserDao用JDBCJDBCTemplate实现
public interface UserDao { //增 void save (User u); //删 void delete(Integer id); //改 void update(User u); //查 User getById(Integer id); //查 int getTotalCount(); //查 }
实现类：
手动注入JDBC模板
private JdbcTemplate it;
package cn.itcast.a_jdbctemplate; import java.sql.ResultSet; import java.sql.SQLException; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; import cn.itcast.bean.User; public class UserDaoImpl implements UserDao{ private JdbcTemplate it; @Override public void save(User u) { String sql="insert into user value(null,?)"; // TODO Auto-generated method stub it.update(sql, u.getUsername()); } public JdbcTemplate getIt() { return it; } public void setIt(JdbcTemplate it) { this.it = it; } @Override public void delete(Integer id) { // TODO Auto-generated method stub } @Override public void update(User u) { // TODO Auto-generated method stub } @Override public User getById(Integer id) { String sql = "select * from user where id=?"; return it.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.setUsername(rs.getString("name")); // TODO Auto-generated method stub return u; } }, id); // TODO Auto-generated method stub } @Override public int getTotalCount() { String sql = "select count(*) from user"; // TODO Auto-generated method stub return it.queryForObject(sql,Integer.class); } }
配置文件：
顺序：
db.properties配置文件
jdbc.jdbcUrl=jdbc:mysql:///mybatis
jdbc.driverClass=com.mysql.jdbc.Driver
jdbc.user=root
jdbc.password=*****
spring配置文件：
<?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"
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 "> <!--指定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="cn.itcast.a_jdbctemplate.UserDaoImpl"> <property name="it" ref="jdbcTemplate"></property> </bean> </beans>
测试：
package cn.itcast.a_jdbctemplate; import javax.annotation.Resource; import org.junit.Test; import org.junit.runner.RunWith; import org.springframework.test.context.ContextConfiguration; import org.springframework.test.context.junit4.SpringJUnit4ClassRunner; import cn.itcast.bean.User; //演示JDBC模板 @RunWith(SpringJUnit4ClassRunner.class) @ContextConfiguration("classpath:applicationContext.xml") public class Demo02 { @Resource(name="userDao") private UserDao ud; @Test public void fun() throws Exception{ int totalCount = ud.getTotalCount(); System.out.println(totalCount); } }
结果：
数据库资料：
控制台输出结果：
14
三、通过继承来准备JDBC模板
配置文件修改：
<?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"
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 "> <!--指定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.将UserDao放入spring容器 --> <bean name="userDao" class="cn.itcast.a_jdbctemplate.UserDaoImpl"> <!-- <property name="it" ref="jdbcTemplate"></property>--> <property name="dataSource" ref="dataSource"></property> </bean> </beans>
