一、Spring與JDBC模板 1、搭建環境 2、數據源的配置 3、從屬性文件讀取資料庫連接信息 4、配置JDBC模板 5、DAO實現類繼承JdbcDaoSupport類 6、對資料庫的增刪改操作 7、對資料庫的查詢操作 二、Spring的事務管理 ...
一、Spring與JDBC模板
1、搭建環境
首先導入需要的jar包:
以上jar中多導入了DBCP和C3P0的jar包,因為這裡需要演示怎麼配置多種數據源,所以導入了這兩個包,在實際開發中無需導入這兩個包。
2、數據源的配置
數據源的配置分為3中情況:
1、Spring內置的連接池DriverManagerDataSource;
2、DBCP數據源 BasicDataSource;
3、C3P0數據源 ComboPooledDataSource;
具體在XML中的配置如下:
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.5.xsd"> <!-- 註冊數據源:1、Spring內置連接池 --> <!--<bean id="myDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://127.0.0.1:3306/test"/> <property name="username" value="root"/> <property name="password" value="root"/> </bean>--> <!-- 註冊數據源:2、DBCP --> <!--<bean id="myDataSource" class="org.apache.commons.dbcp.BasicDataSource"> <property name="driverClassName" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://127.0.0.1:3306/test"/> <property name="username" value="root"/> <property name="password" value="root"/> </bean>--> <!-- 註冊數據源:3、C3P0 --> <bean id="myDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"> <property name="driverClass" value="com.mysql.jdbc.Driver"/> <property name="jdbcUrl" value="jdbc:mysql://127.0.0.1:3306/test"/> <property name="user" value="root"/> <property name="password" value="root"/> </bean> <!-- 註冊JdbcTemplate --> <bean id="myJdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="myDataSource"/> </bean> <!-- 註冊Dao --> <bean id="studentDao" class="com.ietree.spring.dao.basic.dao.StudentDaoImpl"> <property name="jdbcTemplate" ref="myJdbcTemplate"/> </bean> <!-- 註冊Service --> <bean id="studentService" class="com.ietree.spring.dao.basic.service.StudentServiceImpl"> <property name="dao" ref="studentDao"/> </bean> </beans>
3、從屬性文件讀取資料庫連接信息
創建db.properties資料庫配置文件:
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1:3306/test
jdbc.user=root
jdbc.password=root
配置Spring配置文件:
<!-- 註冊數據源:3、C3P0 --> <bean id="myDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"> <property name="driverClass" value="${jdbc.driver}"/> <property name="jdbcUrl" value="${jdbc.url}"/> <property name="user" value="${jdbc.user}"/> <property name="password" value="${jdbc.password}"/> </bean> <!--註冊屬性文件:方式一--> <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"> <property name="location" value="classpath:db.properties"/> </bean> <!--註冊屬性文件:方式二--> <context:property-placeholder location="classpath:db.properties"/>
4、配置JDBC模板
<bean id="myJdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="myDataSource"/> </bean> <!-- 註冊Dao --> <bean id="studentDao" class="com.ietree.spring.dao.basic.dao.StudentDaoImpl"> <property name="jdbcTemplate" ref="myJdbcTemplate"/> </bean>
5、DAO實現類繼承JdbcDaoSupport類,對資料庫的增刪改查操作
package com.ietree.spring.dao.basic.dao; import com.ietree.spring.dao.basic.bean.Student; import com.ietree.spring.dao.basic.bean.StudentRowMapper; import org.springframework.jdbc.core.support.JdbcDaoSupport; import java.util.List; /** * Created by Root on 2017/7/9. */ public class StudentDaoImpl extends JdbcDaoSupport implements IStudentDao { @Override public void insertStudent(Student student) { String sql = "INSERT INTO tbl_student(name,age) VALUES(?,?)"; this.getJdbcTemplate().update(sql,student.getName(),student.getAge()); } @Override public void deleteStudent(int id) { String sql = "DELETE FROM tbl_student WHERE id = ?"; this.getJdbcTemplate().update(sql,id); } @Override public void updateStudent(Student student) { String sql = "UPDATE tbl_student SET name=?,age=? WHERE id = ?;"; this.getJdbcTemplate().update(sql,student.getName(),student.getAge(),student.getId()); } @Override public List<String> selectAllStudentNames() { String sql = "SELECT name FROM tbl_student"; return this.getJdbcTemplate().queryForList(sql,String.class); } @Override public String selectStudentNameById(int id) { String sql = "SELECT name FROM tbl_student WHERE id = ?"; return this.getJdbcTemplate().queryForObject(sql,String.class,id); } @Override public List<Student> selectAllStudent() { String sql = "SELECT id,name,age FROM tbl_student"; return this.getJdbcTemplate().query(sql, new StudentRowMapper()); } @Override public Student selectStudentById(int id) { String sql = "SELECT id,name,age FROM tbl_student WHERE id = ?"; return this.getJdbcTemplate().queryForObject(sql, new StudentRowMapper(),id); } }
StudentRowMapper類:
package com.ietree.spring.dao.basic.bean; import org.springframework.jdbc.core.RowMapper; import java.sql.ResultSet; import java.sql.SQLException; /** * Created by Root on 2017/7/11. */ public class StudentRowMapper implements RowMapper<Student> { /** * 這裡的rs代表的是查詢出來的結果中的一行數據,並非代表所有數據。只要能執行到這個方法,就說明rs不可能為空 */ @Override public Student mapRow(ResultSet rs, int rowNum) throws SQLException { Student student = new Student(); student.setId(rs.getInt("id")); student.setName(rs.getString("name")); student.setAge(rs.getInt("age")); return student; } }
以上案例完整的配置文件如下:
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd"> <!-- 註冊數據源:1、Spring內置連接池 --> <!--<bean id="myDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://127.0.0.1:3306/test"/> <property name="username" value="root"/> <property name="password" value="root"/> </bean>--> <!-- 註冊數據源:2、DBCP --> <!--<bean id="myDataSource" class="org.apache.commons.dbcp.BasicDataSource"> <property name="driverClassName" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://127.0.0.1:3306/test"/> <property name="username" value="root"/> <property name="password" value="root"/> </bean>--> <!-- 註冊數據源:3、C3P0 --> <!--<bean id="myDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"> <property name="driverClass" value="com.mysql.jdbc.Driver"/> <property name="jdbcUrl" value="jdbc:mysql://127.0.0.1:3306/test"/> <property name="user" value="root"/> <property name="password" value="root"/> </bean>--> <!-- 註冊數據源:3、C3P0 --> <bean id="myDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"> <property name="driverClass" value="${jdbc.driver}"/> <property name="jdbcUrl" value="${jdbc.url}"/> <property name="user" value="${jdbc.user}"/> <property name="password" value="${jdbc.password}"/> </bean> <!--註冊屬性文件:方式一--> <!--<bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"> <property name="location" value="classpath:db.properties"/> </bean>--> <!--註冊屬性文件:方式二--> <context:property-placeholder location="classpath:db.properties"/> <!-- 根據JdbcDaoSupport類的源碼可以省略JdbcTemplate的註冊,將DataSource作為Dao的屬性 --> <!-- 註冊JdbcTemplate --> <bean id="myJdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="myDataSource"/> </bean> <!-- 註冊Dao --> <bean id="studentDao" class="com.ietree.spring.dao.basic.dao.StudentDaoImpl"> <property name="jdbcTemplate" ref="myJdbcTemplate"/> </bean> <!-- 註冊Dao --> <bean id="studentDao" class="com.ietree.spring.dao.basic.dao.StudentDaoImpl"> <property name="dataSource" ref="myDataSource"/> </bean> <!-- 註冊Service --> <bean id="studentService" class="com.ietree.spring.dao.basic.service.StudentServiceImpl"> <property name="dao" ref="studentDao"/> </bean> </beans>
二、Spring的事務管理
1、Spring事務管理API
2、使用Spring的事務代理工廠管理事務
3、使用Spring的事務註解管理事務
4、使用AspectJ的AOP配置管理事務