一、SpringJDBC基本使用
package com.aitiman.test;
import java.beans.PropertyVetoException;
import org.junit.Test;
import org.springframework.jdbc.core.JdbcTemplate;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class Test01 {
@Test
public void function1() throws PropertyVetoException {
//演示jdbcTemplate运行过程
ComboPooledDataSource dataSource = new ComboPooledDataSource();
dataSource.setDriverClass("com.mysql.jdbc.Driver");
dataSource.setJdbcUrl("jdbc:mysql:///day01");
dataSource.setUser("root");
dataSource.setPassword("123456");
JdbcTemplate jt = new JdbcTemplate();
jt.setDataSource(dataSource);
String sql = "insert into t_user value(null,'rose')";
jt.update(sql);
}
}
二、进阶学习,先导包,需要以下jar包支持:
spring必备4+2包:
Spring包:spring beans+spring context+spring core+spring expression+spring aop+spring-aspects
apache commons logging+
apache log4j+
其他:spring-test spring-aop Junit测试类库(一般eclipse集成了,方法名上@Test 按提示导入即可)
spring-jdbc spring-tx(事务)
三、准备数据库 t_user
id列 int (10)
name列varchar (255)
四、书写dao,分为UserDao接口 UserDaoImpl实现类
package com.aitiman.dao;
import java.util.List;
import com.aitiman.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();
}
package com.aitiman.dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import com.aitiman.bean.User;
public class UserDaoImpl implements UserDao {
private JdbcTemplate jt;
@Override
public void save(User u) {
String sql = "insert into t_user values(null,?)";
jt.update(sql, u.getName());
}
@Override
public void delete(Integer id) {
String sql = "delete from t_user where id = ?";
jt.update(sql, id);
}
@Override
public void update(User u) {
String sql = "update t_user set name = ? where id = ?";
jt.update(sql,u.getName(),u.getId());
}
@Override
public User getById(Integer id) {
String sql = "select * from t_user where id = ?";
User user = jt.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);
return user;
}
@Override
public int getTotalCount() {
String sql = "select count(*) from t_user";
Integer count = jt.queryForObject(sql, Integer.class);
return count;
}
@Override
public List<User> getAll() {
String sql = "select * from t_user";
List<User> list = jt.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;
}
public void setJt(JdbcTemplate jt) {
this.jt = jt;
}
}
五、书写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" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.2.xsd ">
<!-- 三步完成spring中配置依赖关系 -->
<!-- 1.将连接池放入Spring容器 -->
<bean name="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="jdbcUrl" value="jdbc:mysql:///day01"></property>
<property name="driverClass" value="com.mysql.jdbc.Driver"></property>
<property name="user" value="root"></property>
<property name="password" value="123456"></property>
</bean>
<!-- 2.将JDBCTemplete放入Spring容器 -->
<bean name="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
<!-- 3.将userdao放入spring容器 -->
<bean name="userDaoImpl" class="com.aitiman.dao.UserDaoImpl">
<property name="jt" ref="jdbcTemplate"></property>
</bean>
</beans>
六、测试
package com.aitiman.test;
import java.util.List;
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 com.aitiman.bean.User;
import com.aitiman.dao.UserDao;
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class Test02 {
@Resource(name="userDaoImpl")//将spring容器中配置好的userDaoImpl注入ud
private UserDao ud;
@Test
public void function1() {
User u = new User();
u.setName("zhangsan");
ud.save(u);
}
@Test
public void function2() {
User u = new User();
u.setId(1);
u.setName("lucy");
ud.update(u);
}
@Test
public void function3() {
ud.delete(1);
}
@Test
public void function4() {
User u = ud.getById(2);
System.out.println(u);
}
@Test
public void function5() {
int totalCount = ud.getTotalCount();
System.out.println(totalCount);
}
@Test
public void function6() {
List<User> all = ud.getAll();
System.out.println(all);
}
}
七、进阶内容:JDBCDaoSupport
让UserDaoImpl继承JDBCDaoSupport
作用:可以让UserdaoImpl不再依赖jdbctemplate,减少了一层依赖关系(用不用都可以)
原理:让userdaoImple继承jdbcdaoSupport,因为jdbcdaoSupport中包含了根据连接池创建jdbcTemplate的方法,所以userdaoImpl不需要再依赖jdbcTemplate,只需要依赖连接池dataSOurce即可
UserdaoImpl中只需要使用 super.getJDBCTemplate即可获得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" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.2.xsd ">
<!-- 三步完成spring中配置依赖关系 -->
<!-- 1.将连接池放入Spring容器 -->
<bean name="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="jdbcUrl" value="jdbc:mysql:///day01"></property>
<property name="driverClass" value="com.mysql.jdbc.Driver"></property>
<property name="user" value="root"></property>
<property name="password" value="123456"></property>
</bean>
<!-- 2.将JDBCTemplete放入Spring容器 -->
<!-- <bean name="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean> -->
<!-- 3.将userdao放入spring容器 -->
<bean name="userDaoImpl" class="com.aitiman.dao.UserDaoImpl">
<!-- <property name="jt" ref="jdbcTemplate"></property>
--> <property name="dataSource" ref="dataSource"></property>
</bean>
</beans>
九、修改UserDaoImpl
package com.aitiman.dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
/*import org.springframework.jdbc.core.JdbcTemplate;
*/import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import com.aitiman.bean.User;
public class UserDaoImpl extends JdbcDaoSupport implements UserDao {
// private JdbcTemplate jt;
@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, id);
}
@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 = ?";
User user = 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);
return user;
}
@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;
}
/* public void setJt(JdbcTemplate jt) {
this.jt = jt;
}*/
}
十、扩展:读取外部properties配置文件
案例:将数据库连结配置的内容写在外部db.properties文件中,通过Spring读取
jdbc.jdbcUrl=jdbc:mysql:///day01
jdbc.driverClass=com.mysql.jdbc.Driver
jdbc.user=root
jdbc.password=123456
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: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读取properties配置
--> <context:property-placeholder location="classpath:db.properties"/>
<!-- 三步完成spring中配置依赖关系 -->
<!-- 1.将连接池放入Spring容器 -->
<!-- <bean name="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="jdbcUrl" value="jdbc:mysql:///day01"></property>
<property name="driverClass" value="com.mysql.jdbc.Driver"></property>
<property name="user" value="root"></property>
<property name="password" value="Hwj5251648!"></property>
</bean> -->
<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.将JDBCTemplete放入Spring容器 -->
<!-- <bean name="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean> -->
<!-- 3.将userdao放入spring容器 -->
<bean name="userDaoImpl" class="com.aitiman.dao.UserDaoImpl">
<!-- <property name="jt" ref="jdbcTemplate"></property>
--> <property name="dataSource" ref="dataSource"></property>
</bean>
</beans>