Spring dbcp连接池配置与示例（以及JDBCTemplate的使用）
Apache的DBCP
首先要导入的jar包：
dbpc需要的包：
除了Spring核心包之外的jar包：
我们要做的示例：（利用dbcp连接池实现对t_student表的增删改查）
废话不多少，上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" 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/aop http://www.springframework.org/schema/aop/spring-aop-4.3.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd"> <!-- 读取jdbc.properties文件 --> <context:property-placeholder location="jdbc.properties"/> <!-- Spring dbcp连接池配置，配置数据源 --> <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="${jdbc.driverClassName}"/> <property name="url" value="${jdbc.url}"/> <property name="username" value="${jdbc.username}"/> <property name="password" value="${jdbc.password}"/> </bean> <!-- 导入Springjdbc --> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <!-- 引入数据源，也就是连接池配置 --> <property name="dataSource" ref="dataSource"></property> </bean> <!-- 实例化两个接口 --> <bean id="studentDao" class="com.maya.daoImp.StudentDaoImpl"> <!-- 在dao类中注入Spring的jdbcTemplate --> <property name="jdbcTemplate" ref="jdbcTemplate"></property> </bean> <bean id="studentService" class="com.maya.serviceImp.StudentServiceImpl"> <property name="studentDao" ref="studentDao"></property> </bean> </beans>
jdbc.properties文件
jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/hibernate?characterEncoding=GBK
jdbc.username=root
jdbc.password=
model类
package com.maya.model; public class Student { private int id; private String name; private int age; public Student() { super(); // TODO Auto-generated constructor stub } public Student(String name, int age) { super(); this.name = name; this.age = age; } public Student(int id, String name, int age) { super(); this.id = id; this.name = name; this.age = age; } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } @Override public String toString() { return "Student [id=" + id + ", name=" + name + ", age=" + age + "]"; }
dao类接口就不在这里贴了，直接贴其的实现类
package com.maya.daoImp; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowCallbackHandler; import com.maya.dao.StudentDao; import com.maya.model.Student; public class StudentDaoImpl implements StudentDao{ //利用Spring的jdbc对其进行crud操作 private JdbcTemplate jdbcTemplate; public void setJdbcTemplate(JdbcTemplate jdbcTemplate) { this.jdbcTemplate = jdbcTemplate; } @Override public int addStudent(Student student) { String sql="insert into t_student values(null,?,?)"; //因为Spring的jdbc对原生jdbc进行了封装，在这里给出的是数组方式，当然其底层也是需要将数组进行遍历 Object []params=new Object[]{student.getName(),student.getAge()}; return jdbcTemplate.update(sql,params); } @Override public int updateStudent(Student student) { String sql="update t_student set name=?,age=? where id=?"; Object []params=new Object[]{student.getName(),student.getAge(),student.getId()}; //传入两个参数 return jdbcTemplate.update(sql,params); } @Override public int deleteStudent(int id) { String sql="delete from t_student where id=?"; Object []params=new Object[]{id}; return jdbcTemplate.update(sql,params); } @Override public List<Student> findStudents() { String sql="select * from t_student"; //这里的list对象必须是个final常量 final List<Student> studentList=new ArrayList<Student>(); //同样也是传入两个参数， //第二个参数：是一个回掉方法，我们要实现它的processRow方法 jdbcTemplate.query(sql, new RowCallbackHandler(){ @Override public void processRow(ResultSet rs) throws SQLException { //同jdbc一样也是要在其中把结果集遍历出来，赋值给新对象 Student student=new Student(); student.setId(rs.getInt("id")); student.setName(rs.getString("name")); student.setAge(rs.getInt("age")); studentList.add(student); } }); return studentList; } }
service的接口也不再这里贴了，直接贴其实现类
package com.maya.serviceImp; import java.util.List; import com.maya.dao.StudentDao; import com.maya.model.Student; import com.maya.service.StudentService; public class StudentServiceImpl implements StudentService{ private StudentDao studentDao; public void setStudentDao(StudentDao studentDao) { this.studentDao = studentDao; } //当调用该逻辑时，将其执行的结果条数返回 @Override public int addStudent(Student student) { return studentDao.addStudent(student); } @Override public int updateStudent(Student student) { return studentDao.updateStudent(student); } @Override public int deleteStudent(int id) { return studentDao.deleteStudent(id); } @Override public List<Student> findStudents() { return studentDao.findStudents(); } }
测试类
package com.maya.test; import java.util.List; import org.junit.Before; import org.junit.Test; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import com.maya.model.Student; import com.maya.service.StudentService; import junit.framework.TestCase; public class T extends TestCase { private ApplicationContext ac; @Before//利用前置通知执行 public void setUp() throws Exception { ac=new ClassPathXmlApplicationContext("beans.xml"); } @Test public void testaddStudent() { StudentService studentService=(StudentService)ac.getBean("studentService"); int addNums=studentService.addStudent(new Student("王五", 1)); if(addNums==1){ System.out.println("添加成功"); } } @Test public void testupdateStudent() { StudentService studentService=(StudentService)ac.getBean("studentService"); int updateNums=studentService.updateStudent(new Student(3,"王五2", 2)); if(updateNums==1){ System.out.println("更新成功"); } } @Test public void testdeleteStudent() { StudentService studentService=(StudentService)ac.getBean("studentService"); int deleteNums=studentService.deleteStudent(3); if(deleteNums==1){ System.out.println("删除成功"); } } @Test public void testfindStudents() { StudentService studentService=(StudentService)ac.getBean("studentService"); List<Student> studentList=studentService.findStudents(); for(Student student:studentList){ System.out.println(student); } } }
*****************************************************************************************************************
如何使用JDBCTemplate（在上面已经列出了其增删改查的使用方式，这里在介绍一下xml文件中的配置和查询单个对象，多个对象集合）
<!-- 配置JDBCTemplate --> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource"></property> </bean> <!-- 命名的JDBCTemplate --> <bean id="namedParameterJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate"> <constructor-arg name="dataSource" ref="dataSource"></constructor-arg> </bean>
查询
package com.itnba.maya.test; import java.sql.Connection; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import javax.sql.DataSource; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import org.springframework.jdbc.core.BeanPropertyRowMapper; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; import com.itnba.maya.entities.Nation; import com.mchange.v2.c3p0.ComboPooledDataSource; public class TestC3P0 { public static void main(String[] args) throws Exception{ ApplicationContext context = new ClassPathXmlApplicationContext("beans.xml"); DataSource ds = (DataSource) context.getBean("dataSource"); JdbcTemplate j = (JdbcTemplate)context.getBean("jdbcTemplate"); String sql = "select count(*) from nation"; //查询单行单列 long count = j.queryForObject(sql, Long.class); System.out.println(count); } public static void main77(String[] args) throws Exception{ ApplicationContext context = new ClassPathXmlApplicationContext("beans.xml"); DataSource ds = (DataSource) context.getBean("dataSource"); JdbcTemplate j = (JdbcTemplate)context.getBean("jdbcTemplate"); String sql = "select * from nation"; //查询多行 RowMapper<Nation> rowMapper = new BeanPropertyRowMapper<Nation>(Nation.class); List<Nation> list = j.query(sql, rowMapper); for(Nation data : list){ System.out.println(data); } } public static void main66(String[] args) throws Exception{ ApplicationContext context = new ClassPathXmlApplicationContext("beans.xml"); DataSource ds = (DataSource) context.getBean("dataSource"); JdbcTemplate j = (JdbcTemplate)context.getBean("jdbcTemplate"); String sql = "select * from nation where code=?"; //查询单个 RowMapper<Nation> rowMapper = new BeanPropertyRowMapper<Nation>(Nation.class); Nation data = j.queryForObject(sql,rowMapper,"n003"); System.out.println(data); } }
