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 }