1 <?xml version="1.0" encoding="UTF-8"?>
 2 <project xmlns="http://maven.apache.org/POM/4.0.0"
 3          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 4          xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
 5     <modelVersion>4.0.0</modelVersion>
 6 
 7     <groupId>com.haifei</groupId>
 8     <artifactId>SSM5_spring_jdbc</artifactId>
 9     <version>1.0-SNAPSHOT</version>
10 
11 
12     <dependencies>
13         <!--mysql数据库驱动坐标-->
14         <dependency>
15             <groupId>mysql</groupId>
16             <artifactId>mysql-connector-java</artifactId>
17             <version>5.1.32</version>
18             <scope>runtime</scope>
19         </dependency>
20         <!--c3p0数据库连接池坐标-->
21         <dependency>
22             <groupId>c3p0</groupId>
23             <artifactId>c3p0</artifactId>
24             <version>0.9.1.2</version>
25         </dependency>
26         <!--druid数据库连接池坐标-->
27         <dependency>
28             <groupId>com.alibaba</groupId>
29             <artifactId>druid</artifactId>
30             <version>1.1.10</version>
31         </dependency>
32 
33         <!--spring坐标-->
34         <dependency>
35             <groupId>org.springframework</groupId>
36             <artifactId>spring-context</artifactId>
37             <version>5.0.5.RELEASE</version>
38         </dependency>
39         <!--spring-test坐标-->
40         <dependency>
41             <groupId>org.springframework</groupId>
42             <artifactId>spring-test</artifactId>
43             <version>5.0.5.RELEASE</version>
44         </dependency>
45 
46         <!--junit单元测试坐标-->
47         <dependency>
48             <groupId>junit</groupId>
49             <artifactId>junit</artifactId>
50             <version>4.12</version>
51             <scope>test</scope>
52         </dependency>
53 
54         <!--spring-jdbc坐标-->
55         <dependency>
56             <groupId>org.springframework</groupId>
57             <artifactId>spring-jdbc</artifactId>
58             <version>5.0.5.RELEASE</version>
59         </dependency>
60         <!--spring-tx坐标-->
61         <dependency>
62             <groupId>org.springframework</groupId>
63             <artifactId>spring-tx</artifactId>
64             <version>5.0.5.RELEASE</version>
65         </dependency>
66     </dependencies>
67 
68 
69     <build>
70         <plugins>
71             <!--jdk编译插件-->
72             <plugin>
73                 <groupId>org.apache.maven.plugins</groupId>
74                 <artifactId>maven-compiler-plugin</artifactId>
75                 <version>3.1</version>
76                 <configuration>
77                     <target>1.8</target>
78                     <source>1.8</source>
79                     <encoding>UTF-8</encoding> <!--防止sout内部输出中文乱码-->
80                 </configuration>
81             </plugin>
82             <!--tomcat7插件-->
83             <plugin>
84                 <groupId>org.apache.tomcat.maven</groupId>
85                 <artifactId>tomcat7-maven-plugin</artifactId>
86                 <version>2.1</version>
87                 <configuration>
88                     <port>8080</port>
89                     <path>/ssm4</path>
90                     <uriEncoding>UTF-8</uriEncoding> <!--防止get请求url中中文参数乱码-->
91                 </configuration>
92             </plugin>
93         </plugins>
94     </build>
95 
96 
97 </project>

 1 package com.haifei.domain;
 2 
 3 public class Account {
 4 
 5     private String name;
 6     private double money;
 7 
 8     public String getName() {
 9         return name;
10     }
11 
12     public void setName(String name) {
13         this.name = name;
14     }
15 
16     public double getMoney() {
17         return money;
18     }
19 
20     public void setMoney(double money) {
21         this.money = money;
22     }
23 
24     @Override
25     public String toString() {
26         return "Account{" +
27                 "name=‘" + name + ‘\‘‘ +
28                 ", money=" + money +
29                 ‘}‘;
30     }
31 }

 1 package com.haifei.test;
 2 
 3 import com.mchange.v2.c3p0.ComboPooledDataSource;
 4 import org.junit.Test;
 5 import org.springframework.context.ApplicationContext;
 6 import org.springframework.context.support.ClassPathXmlApplicationContext;
 7 import org.springframework.jdbc.core.JdbcTemplate;
 8 
 9 import java.beans.PropertyVetoException;
10 
11 /**
12  * 使用juint进行测试
13  */
14 public class JDBCTemplateTest {
15 
16 
17     /**
18      * JDBCTemplate开发步骤
19      *  在maven配置文件pom.xml中导入spring-jdbc和spring-tx坐标
20      * @throws PropertyVetoException
21      */
22     @Test
23     public void test1() throws PropertyVetoException {
24         //创建数据源
25         ComboPooledDataSource dataSource = new ComboPooledDataSource();
26         dataSource.setDriverClass("com.mysql.jdbc.Driver");
27         dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/hm_ssm");
28         dataSource.setUser("root");
29         dataSource.setPassword("root");
30 
31         //创建模板对象
32         JdbcTemplate template = new JdbcTemplate();
33         //设置数据源
34         template.setDataSource(dataSource);
35 
36         //执行操作（查询用template.query，增删改用template.query）
37         String sql = "insert into account (name, money) values (?,?);";
38         int row = template.update(sql, "tom", 5000);
39         System.out.println(row); //1
40     }
41 
42 
43     /**
44      * Spring 产生 JDBCTemplate对象
45      *
46      *      可以将JdbcTemplate的创建权交给Spring，
47      *      将数据源DataSource的创建权也交给Spring，
48      *      在Spring容器内部将数据源DataSource注入到JdbcTemplate模版对象中
49      *
50      *      在spring配置文件applicationContext.xml中加载外部的properties文件、配置数据源、配置spring jdbcTemplate
51      */
52     @Test
53     public void test2(){
54         ApplicationContext app = new ClassPathXmlApplicationContext("applicationContext.xml");
55         JdbcTemplate template = app.getBean(JdbcTemplate.class); //从spring容器中获得JdbcTemplate进行添加操作
56         String sql = "insert into account (name, money) values (?,?);";
57         int row = template.update(sql, "sam", 12000);
58         System.out.println(row); //1
59     }
60 
61 
62 }

 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"
 4        xmlns:context="http://www.springframework.org/schema/context"
 5        xsi:schemaLocation="
 6         http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
 7         http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">
 8     <!--配置contest命名空间及其约束-->
 9 
10 
11     <!--加载外部的properties文件-->
12     <context:property-placeholder location="classpath:jdbc.properties"/>
13     <!--配置数据源（数据库连接池）-->
14     <bean id="dataSource_c3p0" class="com.mchange.v2.c3p0.ComboPooledDataSource">
15         <property name="driverClass" value="${jdbc.driver}"></property>
16         <property name="jdbcUrl" value="${jdbc.url}"></property>
17         <property name="user" value="${jdbc.username}"></property>
18         <property name="password" value="${jdbc.password}"></property>
19     </bean>
20     <bean id="dataSource_druid" class="com.alibaba.druid.pool.DruidDataSource">
21         <property name="driverClassName" value="${jdbc.driver}"></property>
22         <property name="url" value="${jdbc.url}"></property>
23         <property name="username" value="${jdbc.username}"></property>
24         <property name="password" value="${jdbc.password}"></property>
25     </bean>
26     <!--配置spring jdbcTemplate-->
27     <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
28         <property name="dataSource" ref="dataSource_c3p0"/>  <!--数据源可选c3p0和druid-->
29     </bean>
30 
31 
32 </beans>

 1 package com.haifei.test;
 2 
 3 import com.haifei.domain.Account;
 4 import org.junit.Test;
 5 import org.junit.runner.RunWith;
 6 import org.springframework.beans.factory.annotation.Autowired;
 7 import org.springframework.jdbc.core.BeanPropertyRowMapper;
 8 import org.springframework.jdbc.core.JdbcTemplate;
 9 import org.springframework.test.context.ContextConfiguration;
10 import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
11 
12 import java.util.List;
13 
14 /**
15  * 使用spring-test集成juint进行测试
16  * CRUD：增加(Create)、检索(Retrieve)、更新(Update)和删除(Delete)
17  *  tips:要导航到抽象方法/抽象类/接口的【实现】，选中其，然后按【Ctrl+Alt+B】
18  */
19 @RunWith(SpringJUnit4ClassRunner.class)
20 @ContextConfiguration("classpath:applicationContext.xml")
21 public class JDBCTemplateCRUDTest {
22 
23     @Autowired //测谁注谁
24     private JdbcTemplate template;
25 
26     @Test
27     public void testUpdate(){
28         String sql = "update account set money=? where name=?;";
29         template.update(sql, 10000,"tom");
30     }
31 
32     @Test
33     public void testDelete(){
34         String sql = "delete from account where name=?;";
35         template.update(sql, "tom");
36     }
37 
38     @Test
39     public void testInsert(){
40         String sql = "insert into account (name, money) values (?,?);";
41         template.update(sql, "tom", 20000);
42     }
43 
44     @Test
45     public void testQueryAll(){
46         String sql = "select * from account;";
47         List<Account> accountList = template.query(sql, new BeanPropertyRowMapper<Account>(Account.class));
48         System.out.println(accountList); //[Account{name=‘sam‘, money=12000.0}, Account{name=‘tom‘, money=20000.0}]
49     }
50 
51     @Test
52     public void testQueryOne(){
53         String sql = "select * from account where name=?;";
54         Account account = template.queryForObject(sql, new BeanPropertyRowMapper<Account>(Account.class), "tom");
55         System.out.println(account); //Account{name=‘tom‘, money=20000.0}
56     }
57 
58     @Test
59     public void testQueryCount(){ //聚合查询
60         String sql = "select count(*) from account;";
61         Long count = template.queryForObject(sql, Long.class);
62         System.out.println(count); //2
63     }
64 
65 
66 }