Spring--JDBC

Spring jdbc对基本的数据访问提供了很多方便，我们再也不用去管什么异常处理，资源打开，关闭之类的操作，只需要关心核心的处理即可。

spring提供的jdbc工具：

JdbcTemplate：这是最基本的JDBC实现方式

NamedParameter Jdbc Template：是对JdbcTemplate的一个再次封装，可以提供命名参数

SimpleJdbcInsert and SimpleJdbcCall:

RDBMS：

首先建表

1、使用JdbcTemplate的实现实例

 1 package com.fuwh.model;
 2 //model类
 3 public class Student {
 4 
 5     private int id;
 6     private String name;
 7     private int age;
 8     
 9 
10     public Student(int id, String name, int age) {
11         super();
12         this.id = id;
13         this.name = name;
14         this.age = age;
15     }
16     public Student(String name, int age) {
17         super();
18         this.name = name;
19         this.age = age;
20     }
21     public int getId() {
22         return id;
23     }
24     public void setId(int id) {
25         this.id = id;
26     }
27     public String getName() {
28         return name;
29     }
30     public void setName(String name) {
31         this.name = name;
32     }
33     public int getAge() {
34         return age;
35     }
36     public void setAge(int age) {
37         this.age = age;
38     }
39     
40     
41 }

 1 package com.fuwh.service;
 2 
 3 import java.util.List;
 4 import java.util.Map;
 5 
 6 import com.fuwh.model.Student;
 7 //dao：crud接口
 8 public interface StuDao {
 9 
10     public abstract int addStudent(Student stu);
11     
12     public abstract int delStudent(int id);
13     
14     public abstract int updateStudent(Student stu);
15     
16     public abstract List<Map<String, Object>> queryStudent();
17 }

 1 package com.fuwh.service.impl;
 2 
 3 import java.util.List;
 4 import java.util.Map;
 5 
 6 import org.springframework.jdbc.core.JdbcTemplate;
 7 
 8 import com.fuwh.model.Student;
 9 import com.fuwh.service.StuDao;
10 //crud具体实现类
11 public class StuDaoImpl implements StuDao {
12 
13     //注入JdbcTemplate
14     private JdbcTemplate jdbcTemplate;
15     
16     public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
17         this.jdbcTemplate = jdbcTemplate;
18     }
19 
20     @Override
21     public int addStudent(Student stu) {
22         // TODO Auto-generated method stub
23         String sql="insert into t_student values(null,?,?)";
24         Object args[]=new Object[]{stu.getName(),stu.getAge()};
25         return jdbcTemplate.update(sql, args);
26     }
27 
28     @Override
29     public int delStudent(int id) {
30         // TODO Auto-generated method stub
31         String sql="delete from t_student where id=?";
32         return jdbcTemplate.update(sql, id);
33     }
34 
35     @Override
36     public int updateStudent(Student stu) {
37         // TODO Auto-generated method stub
38         String sql="update t_student set name=?,age=? where id=?";
39         Object args[]=new Object[]{stu.getName(),stu.getAge(),stu.getId()};
40         return jdbcTemplate.update(sql, args);
41     }
42 
43     @Override
44     public List<Map<String,Object>> queryStudent() {
45         // TODO Auto-generated method stub
46         String sql="select * from t_student";
47         return jdbcTemplate.queryForList(sql) ;
48     }
49 
50 }

 1 package com.fuwh.test;
 2 
 3 
 4 import java.util.List;
 5 import java.util.Map;
 6 
 7 import org.junit.Before;
 8 import org.springframework.context.ApplicationContext;
 9 import org.springframework.context.support.ClassPathXmlApplicationContext;
10 
11 import com.fuwh.model.Student;
12 import com.fuwh.service.StuDao;
13 
14 //Junit测试类
15 public class Test {
16 
17     private ApplicationContext ac;    
18 
19     @Before
20     public void setUp() throws Exception {
21         ac=new ClassPathXmlApplicationContext("beans.xml");
22     }
23 
24     @org.junit.Test
25     public void testInsert() {
26         StuDao sd=(StuDao)ac.getBean("stuDao");
27         int retVal=sd.addStudent(new Student("fuwh",27));
28         if(retVal==1){
29             System.out.println("添加成功");
30         }
31     }
32     
33     @org.junit.Test
34     public void testDelete(){
35         StuDao sd=(StuDao)ac.getBean("stuDao");
36         int retVal=sd.delStudent(2);
37         if(retVal==1){
38             System.out.println("删除成功");
39         }
40     }
41     
42     @org.junit.Test
43     public void testUpdate(){
44         StuDao sd=(StuDao)ac.getBean("stuDao");
45         int retVal=sd.updateStudent(new Student(3,"fengzi",18));
46         if(retVal==1){
47             System.out.println("修改成功");
48         }
49     }
50     
51     @org.junit.Test
52     public void testQuery(){
53         StuDao sd=(StuDao)ac.getBean("stuDao");
54         List<Map<String, Object>> stuList=sd.queryStudent();
55         for(Map<String,Object> stuMap:stuList){
56             for(Map.Entry<String, Object> me:stuMap.entrySet()){
57                 System.out.println(me.getKey()+"-->"+me.getValue());
58             }
59         }
60     }
61 }

 1 <?xml version="1.0" encoding="UTF-8"?>
 2 
 3 <beans xmlns="http://www.springframework.org/schema/beans"
 4     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 5     xmlns:aop="http://www.springframework.org/schema/aop"
 6     xmlns:context="http://www.springframework.org/schema/context"
 7     xsi:schemaLocation="http://www.springframework.org/schema/beans
 8         http://www.springframework.org/schema/beans/spring-beans.xsd
 9         http://www.springframework.org/schema/aop
10         http://www.springframework.org/schema/aop/spring-aop.xsd
11         http://www.springframework.org/schema/context
12         http://www.springframework.org/schema/context/spring-context.xsd">
13 
14     <!-- 配置JdbcTemplate 
15          需要注意的是，必须要为JdbcTemplate注入一个DataSource
16      -->
17     <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
18         <property name="dataSource" ref="dataSource"></property>
19     </bean>
20     <bean id="stuDao" class="com.fuwh.service.impl.StuDaoImpl">
21         <property name="jdbcTemplate" ref="jdbcTemplate"></property>
22     </bean>
23     <!-- 配置DataSource
24          这里使用的是apache的dbcp连接池
25          需要引入apache的dbcp连接池jarbao
26          commons-dbcp2-2.1.1.jar 和 commons-pool2-2.4.2.jar
27      -->    
28     <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">
29         <property name="driverClassName" value="${jdbc.driverClassName}"/>
30         <property name="url" value="${jdbc.url}"/>
31         <property name="username" value="${jdbc.username}"/>
32         <property name="password" value="${jdbc.password}"/>
33     </bean>
34     
35     <context:property-placeholder location="jdbc.properties"/>
36 </beans>

#jdbc连接配置属性文件
jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/db_spring
jdbc.username=root
jdbc.password=mysqladmin

使用JdbcDaoSupport 继续简化上述操作

 1 package com.fuwh.service.impl;
 2 
 3 import java.util.List;
 4 import java.util.Map;
 5 
 6 import org.springframework.jdbc.core.support.JdbcDaoSupport;
 7 
 8 import com.fuwh.model.Student;
 9 import com.fuwh.service.StuDao;
10 
11 public class StuDaoImpl extends JdbcDaoSupport implements StuDao {
12 
13     
14     @Override
15     public int addStudent(Student stu) {
16         // TODO Auto-generated method stub
17         String sql="insert into t_student values(null,?,?)";
18         Object args[]=new Object[]{stu.getName(),stu.getAge()};
19         return this.getJdbcTemplate().update(sql, args);
20     }
21 
22     @Override
23     public int delStudent(int id) {
24         // TODO Auto-generated method stub
25         String sql="delete from t_student where id=?";
26         return this.getJdbcTemplate().update(sql, id);
27     }
28 
29     @Override
30     public int updateStudent(Student stu) {
31         // TODO Auto-generated method stub
32         String sql="update t_student set name=?,age=? where id=?";
33         Object args[]=new Object[]{stu.getName(),stu.getAge(),stu.getId()};
34         return this.getJdbcTemplate().update(sql, args);
35     }
36 
37     @Override
38     public List<Map<String,Object>> queryStudent() {
39         // TODO Auto-generated method stub
40         String sql="select * from t_student";
41         return this.getJdbcTemplate().queryForList(sql) ;
42     }
43 
44 }

 1 <?xml version="1.0" encoding="UTF-8"?>
 2 
 3 <beans xmlns="http://www.springframework.org/schema/beans"
 4     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 5     xmlns:aop="http://www.springframework.org/schema/aop"
 6     xmlns:context="http://www.springframework.org/schema/context"
 7     xsi:schemaLocation="http://www.springframework.org/schema/beans
 8         http://www.springframework.org/schema/beans/spring-beans.xsd
 9         http://www.springframework.org/schema/aop
10         http://www.springframework.org/schema/aop/spring-aop.xsd
11         http://www.springframework.org/schema/context
12         http://www.springframework.org/schema/context/spring-context.xsd">
13 
14     
15     <bean id="stuDao" class="com.fuwh.service.impl.StuDaoImpl">
16         <property name="dataSource" ref="dataSource"></property>
17     </bean>
18     <!-- 配置DataSource
19          这里使用的是apache的dbcp连接池
20          需要引入apache的dbcp连接池jarbao
21          commons-dbcp2-2.1.1.jar 和 commons-pool2-2.4.2.jar
22      -->    
23     <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">
24         <property name="driverClassName" value="${jdbc.driverClassName}"/>
25         <property name="url" value="${jdbc.url}"/>
26         <property name="username" value="${jdbc.username}"/>
27         <property name="password" value="${jdbc.password}"/>
28     </bean>
29     
30     <context:property-placeholder location="jdbc.properties"/>
31 </beans>

再继续使用NamedParameterJdbcTemplate改写以上操作

 1 package com.fuwh.service.impl;
 2 
 3 import java.util.List;
 4 import java.util.Map;
 5 
 6 import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
 7 import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
 8 
 9 import com.fuwh.model.Student;
10 import com.fuwh.service.StuDao;
11 
12 public class StuDaoImpl implements StuDao {
13 
14     private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
15     
16     public void setNamedParameterJdbcTemplate(NamedParameterJdbcTemplate namedParameterJdbcTemplate) {
17         this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;
18     }
19 
20     @Override
21     public int addStudent(Student stu) {
22         // TODO Auto-generated method stub
23         String sql="insert into t_student values(null,:name,:age)";
24         MapSqlParameterSource sqs=new MapSqlParameterSource("name",stu.getName());
25         sqs.addValue("age", stu.getAge());
26         return namedParameterJdbcTemplate.update(sql, sqs);
27     }
28 
29     @Override
30     public int delStudent(int id) {
31         // TODO Auto-generated method stub
32         String sql="delete from t_student where id=:id";
33         MapSqlParameterSource sqs=new MapSqlParameterSource("id",id);
34         return namedParameterJdbcTemplate.update(sql, sqs);
35     }
36 
37     @Override
38     public int updateStudent(Student stu) {
39         // TODO Auto-generated method stub
40         String sql="update t_student set name=?,age=? where id=?";
41         MapSqlParameterSource sqs=new MapSqlParameterSource("name",stu.getName());
42         sqs.addValue("age", stu.getAge());
43         return namedParameterJdbcTemplate.update(sql, sqs);
44     }
45 
46     @Override
47     public List<Map<String,Object>> queryStudent() {
48         // TODO Auto-generated method stub
49         String sql="select * from t_student";
50         return namedParameterJdbcTemplate.queryForList(sql,new MapSqlParameterSource()) ;
51     }
52 
53 }

 1 <?xml version="1.0" encoding="UTF-8"?>
 2 
 3 <beans xmlns="http://www.springframework.org/schema/beans"
 4     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 5     xmlns:aop="http://www.springframework.org/schema/aop"
 6     xmlns:context="http://www.springframework.org/schema/context"
 7     xsi:schemaLocation="http://www.springframework.org/schema/beans
 8         http://www.springframework.org/schema/beans/spring-beans.xsd
 9         http://www.springframework.org/schema/aop
10         http://www.springframework.org/schema/aop/spring-aop.xsd
11         http://www.springframework.org/schema/context
12         http://www.springframework.org/schema/context/spring-context.xsd">
13 
14     
15     <bean id="namedParameterJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
16         <constructor-arg ref="dataSource"></constructor-arg>
17     </bean>
18     <bean id="stuDao" class="com.fuwh.service.impl.StuDaoImpl">
19         <property name="namedParameterJdbcTemplate"  ref="namedParameterJdbcTemplate"></property>
20     </bean>
21     <!-- 配置DataSource
22          这里使用的是apache的dbcp连接池
23          需要引入apache的dbcp连接池jarbao
24          commons-dbcp2-2.1.1.jar 和 commons-pool2-2.4.2.jar
25      -->    
26     <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">
27         <property name="driverClassName" value="${jdbc.driverClassName}"/>
28         <property name="url" value="${jdbc.url}"/>
29         <property name="username" value="${jdbc.username}"/>
30         <property name="password" value="${jdbc.password}"/>
31     </bean>
32     
33     <context:property-placeholder location="jdbc.properties"/>
34 </beans>

posted @ 2017-01-14 11:23 Ouka傅阅读(298) 评论(0) 编辑收藏举报

刷新页面返回顶部

Ouka傅

Spring--JDBC

公告