Spring_Spring与DAO_Spring的Jdbc模板
一、导入Jar包
二、定义实体类与DB表
1 public class Student { 2 private Integer id; 3 private String name; 4 private int age; 5 6 7 public Student() { 8 super(); 9 } 10 11 public Student( String name, int age) { 12 super(); 13 this.name = name; 14 this.age = age; 15 } 16 17 public Integer getId() { 18 return id; 19 } 20 21 public void setId(Integer id) { 22 this.id = id; 23 } 24 25 public String getName() { 26 return name; 27 } 28 29 public void setName(String name) { 30 this.name = name; 31 } 32 33 public int getAge() { 34 return age; 35 } 36 37 public void setAge(int age) { 38 this.age = age; 39 } 40 41 @Override 42 public String toString() { 43 return "Student [id=" + id + ", name=" + name + ", age=" + age + "]"; 44 } 45 46 }
Student
三、定义Service
1 import java.util.List; 2 3 import com.jmu.beans.Student; 4 //service业务 5 public interface IStudentService { 6 void addStudent(Student student); 7 void removeById(int id); 8 void modifyStudent(Student student); 9 10 List<String> findAllStudentsNames(); 11 String findStudentNameById(int id); 12 13 List<Student> findAllStudents(); 14 Student findStudentById(int id); 15 }
IStudentService
1 import java.util.List; 2 3 import com.jmu.beans.Student; 4 import com.jmu.dao.IStudentDao; 5 6 public class StudentServiceImpl implements IStudentService { 7 private IStudentDao dao; 8 9 public void setDao(IStudentDao dao) { 10 this.dao = dao; 11 } 12 13 @Override 14 public void addStudent(Student student) { 15 // TODO Auto-generated method stub 16 dao.insertStudent(student); 17 18 } 19 20 @Override 21 public void removeById(int id) { 22 // TODO Auto-generated method stub 23 dao.deleteById(id); 24 } 25 26 @Override 27 public void modifyStudent(Student student) { 28 // TODO Auto-generated method stub 29 dao.updateStudent(student); 30 } 31 32 @Override 33 public List<String> findAllStudentsNames() { 34 // TODO Auto-generated method stub 35 return dao.selectAllStudentsNames(); 36 } 37 38 @Override 39 public String findStudentNameById(int id) { 40 // TODO Auto-generated method stub 41 return dao.selectStudentNameById(id); 42 } 43 44 @Override 45 public List<Student> findAllStudents() { 46 // TODO Auto-generated method stub 47 return dao.selectAllStudents(); 48 } 49 50 @Override 51 public Student findStudentById(int id) { 52 // TODO Auto-generated method stub 53 return dao.selectStudentById(id); 54 } 55 56 }
StudentServiceImpl
四、定义Dao
1 import java.util.List; 2 3 import com.jmu.beans.Student; 4 5 //Dao增删改查 6 public interface IStudentDao { 7 void insertStudent(Student student); 8 void deleteById(int id); 9 void updateStudent(Student student); 10 11 List<String> selectAllStudentsNames(); 12 String selectStudentNameById(int id); 13 14 List<Student> selectAllStudents(); 15 Student selectStudentById(int id); 16 }
IStudentDao
1 import java.util.List; 2 3 import org.springframework.jdbc.core.support.JdbcDaoSupport; 4 5 import com.jmu.beans.Student; 6 7 public class StudentDaoImpl extends JdbcDaoSupport implements IStudentDao { 8 9 @Override 10 public void insertStudent(Student student) { 11 // TODO Auto-generated method stub 12 String sql="insert into student(name,age) value(?,?)"; 13 this.getJdbcTemplate().update(sql, student.getName(),student.getAge()); 14 } 15 16 @Override 17 public void deleteById(int id) { 18 // TODO Auto-generated method stub 19 String sql="delete from student where id=?"; 20 this.getJdbcTemplate().update(sql, id); 21 } 22 23 @Override 24 public void updateStudent(Student student) { 25 // TODO Auto-generated method stub 26 String sql="update student set name=?,age=? where id=?"; 27 this.getJdbcTemplate().update(sql, student.getName(),student.getAge(),student.getId()); 28 29 } 30 31 @Override 32 public List<String> selectAllStudentsNames() { 33 // TODO Auto-generated method stub 34 String sql="select name from student"; 35 return this.getJdbcTemplate().queryForList(sql, String.class); 36 } 37 38 @Override 39 public String selectStudentNameById(int id) { 40 // TODO Auto-generated method stub 41 String sql="select name from student where id=?"; 42 return this.getJdbcTemplate().queryForObject(sql, String.class,id); 43 } 44 45 @Override 46 public List<Student> selectAllStudents() { 47 // TODO Auto-generated method stub 48 String sql="select id,name,age from student"; 49 return this.getJdbcTemplate().query(sql, new StudentRowMapper()); 50 } 51 52 @Override 53 public Student selectStudentById(int id) { 54 // TODO Auto-generated method stub 55 String sql="select id,name,age from student where id=?"; 56 return this.getJdbcTemplate().queryForObject(sql, new StudentRowMapper(),id); 57 } 58 59 }
StudentDaoImpl
1 import java.sql.ResultSet; 2 import java.sql.SQLException; 3 4 import org.springframework.jdbc.core.RowMapper; 5 6 import com.jmu.beans.Student; 7 8 public class StudentRowMapper implements RowMapper<Student> { 9 10 //rs:当查询出总的结果集后,框架会自动遍历这个结果集,每一次遍历的一行数据,都会被存放到这个方法的rs参数中,也就是说,这里的rs代表的是一行数据,并非所有查询结果,换个角度,只要能执行这个方法,就说明这里的rs不会是空的 11 @Override 12 public Student mapRow(ResultSet rs, int rowNum) throws SQLException { 13 // TODO Auto-generated method stub、 14 Student student=new Student(); 15 student.setId(rs.getInt("id")); 16 student.setName(rs.getString("name")); 17 student.setAge(rs.getInt("age")); 18 return student; 19 } 20 21 }
StudentRowMapper
五、相应的注册
1 <?xml version="1.0" encoding="UTF-8"?> 2 <beans xmlns="http://www.springframework.org/schema/beans" 3 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" 4 xsi:schemaLocation=" 5 http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd 6 http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd"> 7 8 <!--注册数据源:Spring内置连接池 --> 9 <!-- <bean id="myDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> 10 <property name="driverClassName" value="com.mysql.jdbc.Driver"/> <property 11 name="url" value="jdbc:mysql://127.0.0.1:3306/test1"/> <property name="username" 12 value="root"/> <property name="password" value="123456"/> </bean> --> 13 14 <!--注册数据源:DBCP --> 15 <!-- <bean id="myDataSource" class="org.apache.commons.dbcp.BasicDataSource"> 16 <property name="driverClassName" value="com.mysql.jdbc.Driver"/> <property 17 name="url" value="jdbc:mysql://127.0.0.1:3306/test1"/> <property name="username" 18 value="root"/> <property name="password" value="123456"/> </bean> --> 19 <!--注册数据源:C3P0 --> 20 <!-- <bean id="myDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"> 21 <property name="driverClass" value="com.mysql.jdbc.Driver"/> <property name="jdbcUrl" 22 value="jdbc:mysql://127.0.0.1:3306/test1"/> <property name="user" value="root"/> 23 <property name="password" value="123456"/> </bean> --> 24 <!--注册数据源:C3P0 --> 25 <bean id="myDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"> 26 <property name="driverClass" value="${jdbc.driver}" /> 27 <property name="jdbcUrl" value="${jdbc.url}" /> 28 <property name="user" value="${jdbc.user}" /> 29 <property name="password" value="${jdbc.password}" /> 30 </bean> 31 32 <!-- 注册属性文件:方式一 --> 33 <!-- <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"> 34 <property name="location" value="classpath:jdbc.properties"></property> </bean> --> 35 <context:property-placeholder location="classpath:jdbc.properties" /> 36 37 <!--注册JdbcTemplate(模板) --> 38 <!-- <bean id="myJdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> 39 <property name="dataSource" ref="myDataSource" /> </bean> 40 注册Dao 41 <bean id="studentDao" class="com.jmu.dao.StudentDaoImpl"> 42 <property name="jdbcTemplate" ref="myJdbcTemplate" /> 43 </bean> --> 44 <!--注册Dao --> 45 <bean id="studentDao" class="com.jmu.dao.StudentDaoImpl"> 46 <property name="dataSource" ref="myDataSource" /> 47 48 </bean> 49 50 <!-- 注册Service --> 51 <bean id="studentService" class="com.jmu.service.StudentServiceImpl"> 52 <property name="dao" ref="studentDao" /> 53 </bean> 54 </beans>
applicationContext
六、从属性文件读取DB四要素
1 jdbc.driver=com.mysql.jdbc.Driver 2 jdbc.url=jdbc:mysql://127.0.0.1:3306/test1 3 jdbc.user=root 4 jdbc.password=123456
jdbc.properties
七、定义测试类
1 import java.util.List; 2 3 import org.junit.Before; 4 import org.junit.Test; 5 import org.springframework.context.ApplicationContext; 6 import org.springframework.context.support.ClassPathXmlApplicationContext; 7 8 import com.jmu.beans.Student; 9 import com.jmu.service.IStudentService; 10 11 public class MyTest { 12 13 private IStudentService service; 14 15 @Before 16 public void before() { 17 //创建容器对象 18 String resource = "applicationContext.xml"; 19 ApplicationContext ac=new ClassPathXmlApplicationContext(resource); 20 service = (IStudentService) ac.getBean("studentService"); 21 } 22 23 @Test 24 public void test01() { 25 Student student=new Student("张三", 23); 26 service.addStudent(student); 27 } 28 29 @Test 30 public void test02() { 31 service.removeById(2); 32 } 33 34 @Test 35 public void test03() { 36 Student student=new Student("王意义", 23); 37 student.setId(3); 38 service.modifyStudent(student); 39 } 40 @Test 41 public void test04() { 42 List<String> names = service.findAllStudentsNames(); 43 System.out.println(names); 44 } 45 @Test 46 public void test05() { 47 String names = service.findStudentNameById(3); 48 System.out.println(names); 49 } 50 51 @Test 52 public void test06() { 53 List<Student> students=service.findAllStudents(); 54 for (Student student : students) { 55 System.out.println(student); 56 } 57 } 58 59 @Test 60 public void test07() { 61 Student student=service.findStudentById(3); 62 System.out.println(student); 63 } 64 65 66 }
MyTest