四、JdbcTemplate的配置

一、新建Java项目(Spring4)

1.导入jar包：

除了5个jar包外，需要添加

  • spring-aop 
  • c3p0
  • c3p0-oracle-thin-extras
  • mchange-commons-java
  • mysql-connector-java
  • Spring-jdbc
  • spring-tx
  • commons-lang(Apache 常用的工具包)

jar包图片：

 

2.创建数据库连接信息文件

在类路径(即 src 路径下)下创建数据库连接信息文件db.properties

1 jdbc.user=root
2 jdbc.password=
3 jdbc.driverClass=com.mysql.jdbc.Driver
4 jdbc.jdbcUrl=jdbc:mysql:///test
5 
6 
7 jdbc.initPoolSize =5
8 jdbc.maxPoolSize = 10
9 jdbc.maxStatements=0
View Code

二、JdbcTemplate配置

1.导入资源文件（数据库连接信息文件）需要额外引入context命名空间

2.配置c3p0数据源

3.配置JdbcTemplate（applicationcontext.xml）

 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="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-4.0.xsd">
 7 
 8 <!-- 导入资源文件 -->
 9 <context:property-placeholder location="classpath:db.properties"/>
10 <!-- 配置c3p0数据源 -->
11 <bean id="datasource" class="com.mchange.v2.c3p0.ComboPooledDataSource" >
12     <property name="user" value="${jdbc.user}"></property>
13     <property name="password" value="${jdbc.password}"></property>
14     <property name="driverClass" value="${jdbc.driverClass}"></property>
15     <property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property>
16     
17     <property name="initialPoolSize" value="${jdbc.initPoolSize}"></property>
18     <property name="maxPoolSize" value="${jdbc.maxPoolSize}"></property>
19         <property name="maxStatements" value="${jdbc.maxStatements}"></property>
20 </bean>
21 
22 
23 <!--配置spring的 jdbcTemplate -->
24 <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
25     <property name="dataSource" ref="datasource"></property>
26 </bean>
27 
28 </beans>

 4.测试数据库连接：

 1 package lixiuming.spring.jdbc;
 2 
 3 import java.sql.SQLException;
 4 
 5 import javax.sql.DataSource;
 6 
 7 import org.junit.Test;
 8 import org.springframework.context.ApplicationContext;
 9 import org.springframework.context.support.ClassPathXmlApplicationContext;
10 
11 public class JdbcTest {
12 
13     private ApplicationContext cxt = null;
14     {
15         cxt = new ClassPathXmlApplicationContext("applicationcontext.xml");
16     }
17 
18     // 测试数据库连接
19     @Test
20     public void testDataBase() throws SQLException {
21         DataSource dataSource = cxt.getBean(DataSource.class);
22         System.out.println(dataSource.getConnection());
23     }
24 
25 }

结果为：

com.mchange.v2.c3p0.impl.NewProxyConnection@50a638b5 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@1817d444]

表示连接成功；

 

三、JdbcTemplate的使用

1.相关附件：

contacts表sql脚本:

1 CREATE TABLE `contacts` (
2   `id` int(10) NOT NULL AUTO_INCREMENT,
3   `name` varchar(30) CHARACTER SET utf8 NOT NULL,
4   `address` varchar(30) CHARACTER SET utf8 NOT NULL,
5   `phone` varchar(30) CHARACTER SET utf8 NOT NULL,
6   PRIMARY KEY (`id`)
7 ) ;
View Code

 

contacts实体：

 1 package lixiuming.spring.jdbc;
 2 
 3 public class Contacts {
 4     private Integer id;
 5     private String name;
 6     private String address;
 7     private String phone;
 8     public Integer getId() {
 9         return id;
10     }
11     public void setId(Integer id) {
12         this.id = id;
13     }
14     public String getName() {
15         return name;
16     }
17     public void setName(String name) {
18         this.name = name;
19     }
20     public String getAddress() {
21         return address;
22     }
23     public void setAddress(String address) {
24         this.address = address;
25     }
26     public String getPhone() {
27         return phone;
28     }
29     public void setPhone(String phone) {
30         this.phone = phone;
31     }
32     @Override
33     public String toString() {
34         return "Context [id=" + id + ", name=" + name + ", address=" + address + ", phone=" + phone + "]";
35     }
36     public Contacts(Integer id, String name, String address, String phone) {
37         super();
38         this.id = id;
39         this.name = name;
40         this.address = address;
41         this.phone = phone;
42     }
43     public Contacts() {
44         super();
45     }
46     
47 
48 }
View Code

 

2.插入数据

1.insert 插入单条数据

 1 package lixiuming.spring.jdbc;
 2 
 3 import org.junit.Test;
 4 import org.springframework.context.ApplicationContext;
 5 import org.springframework.context.support.ClassPathXmlApplicationContext;
 6 import org.springframework.jdbc.core.JdbcTemplate;
 7 
 8 public class JdbcTest {
 9 
10     private ApplicationContext cxt = null;
11     private JdbcTemplate jdbcTemplate;
12 
13     {
14         cxt = new ClassPathXmlApplicationContext("applicationcontext.xml");
15         jdbcTemplate = (JdbcTemplate) cxt.getBean("jdbcTemplate");
16 
17     }
18 
19     @Test
20     public void testInsert() {
21         String sql = "insert into contacts (name,address,phone) values(?,?,?)";
22         jdbcTemplate.update(sql, "a", "aa", "aaa");
23     }
24 
25 }

运行结果：

 

2.批量插入数据：

 1 package lixiuming.spring.jdbc;
 2 
 3 import java.util.ArrayList;
 4 import java.util.List;
 5 
 6 import org.junit.Test;
 7 import org.springframework.context.ApplicationContext;
 8 import org.springframework.context.support.ClassPathXmlApplicationContext;
 9 import org.springframework.jdbc.core.JdbcTemplate;
10 
11 public class JdbcTest {
12 
13     private ApplicationContext cxt = null;
14     private JdbcTemplate jdbcTemplate;
15 
16     {
17         cxt = new ClassPathXmlApplicationContext("applicationcontext.xml");
18         jdbcTemplate = (JdbcTemplate) cxt.getBean("jdbcTemplate");
19 
20     }
21 
22     @Test
23     public void testBatchUpdate() {
24         String sql = "insert into contacts (name,address,phone) values(?,?,?)";
25         List<Object[]> batchArgs = new ArrayList<>();
26         batchArgs.add(new Object[] { "aaa", "batchInsert", "123" });
27         batchArgs.add(new Object[] { "bbb", "batchInsert", "123" });
28         batchArgs.add(new Object[] { "ccc", "batchInsert", "123" });
29         batchArgs.add(new Object[] { "ddd", "batchInsert", "123" });
30         batchArgs.add(new Object[] { "eee", "batchInsert", "123" });
31         batchArgs.add(new Object[] { "fff", "batchInsert", "123" });
32         jdbcTemplate.batchUpdate(sql, batchArgs);
33     }
34 
35 }

运行结果：

 

3.使用具名参数插入单条数据；

  • applicationcontext.xml添加具名函数的配置
1 <!-- 配置 NamedParameterJdbcTemplate 该对象可以使用具名参数  他没有无参数的构造器，必须指定构造器参数-->
2 <bean id="namedParameterJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
3     <constructor-arg ref="datasource"></constructor-arg>
4 </bean>
  • 插入单条数据方式
 1 package lixiuming.spring.jdbc;
 2 
 3 import java.util.HashMap;
 4 import java.util.Map;
 5 
 6 import org.junit.Test;
 7 import org.springframework.context.ApplicationContext;
 8 import org.springframework.context.support.ClassPathXmlApplicationContext;
 9 import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
10 import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
11 import org.springframework.jdbc.core.namedparam.SqlParameterSource;
12 
13 public class JdbcTest {
14 
15     private ApplicationContext cxt = null;
16     private NamedParameterJdbcTemplate nameJdbcTemplate;
17 
18     {
19         cxt = new ClassPathXmlApplicationContext("applicationcontext.xml");
20         nameJdbcTemplate = (NamedParameterJdbcTemplate) cxt.getBean("namedParameterJdbcTemplate");
21     }
22 
23     /**
24      * 使用具名参数可以使用NamedParameterJdbcTemplate.update(String sql, SqlParameterSource
25      * paramSource)方法更新数据库 1.SQL语句中的参数名和类的属性一直 2.使用SqlParameterSource实现类作为开始，
26      * 
27      * 
28      * 
29      */
30     @Test
31     public void testNamedParameterJdbcTemplate() {
32         String sql = "insert into contacts (name,address,phone) values(:name,:address,:phone)";
33         Contacts contacts = new Contacts();
34         contacts.setName("123123123");
35         contacts.setAddress("kjsdlfkjslkdf");
36         contacts.setPhone("djlksjdfl");
37         SqlParameterSource paramterSource = new BeanPropertySqlParameterSource(contacts);
38         nameJdbcTemplate.update(sql, paramterSource);
39     }
40 
41     /**
42      * 使用具名参数，可以为参数取名字，参数多的话便于维护 1.好处:有多个参数，则不用对应位置，可以对应参数名 2.缺点:比较麻烦
43      */
44     @Test
45     public void testNameJdbcTemplate() {
46         String sql = "insert into contacts (name,address,phone) values(:name,:address,:phone)";
47         Map<String, Object> paramMap = new HashMap<String, Object>();
48         paramMap.put("name", "gggg");
49         paramMap.put("address", "ggggg");
50         paramMap.put("phone", "ggggg");
51         nameJdbcTemplate.update(sql, paramMap);
52     }
53 
54 }

 

 

4.使用具名函数批量插入数据

 1 package lixiuming.spring.jdbc;
 2 
 3 import org.junit.Test;
 4 import org.springframework.context.ApplicationContext;
 5 import org.springframework.context.support.ClassPathXmlApplicationContext;
 6 import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
 7 import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
 8 import org.springframework.jdbc.core.namedparam.SqlParameterSource;
 9 
10 public class JdbcTest {
11 
12     private ApplicationContext cxt = null;
13     private NamedParameterJdbcTemplate nameJdbcTemplate;
14 
15     {
16         cxt = new ClassPathXmlApplicationContext("applicationcontext.xml");
17         nameJdbcTemplate = (NamedParameterJdbcTemplate) cxt.getBean("namedParameterJdbcTemplate");
18     }
19 
20     @Test
21     public void testNamedParameterJdbcTemplateBatch() {
22         String sql = "insert into contacts (name,address,phone) values(:name,:address,:phone)";
23         SqlParameterSource[] arr = new SqlParameterSource[5];
24         for (int i = 0; i < 5; i++) {
25             Contacts contacts = new Contacts();
26             contacts.setName("nameParam" + i);
27             contacts.setAddress("kjsdlfkjslkdf");
28             contacts.setPhone("djlksjdfl");
29             SqlParameterSource paramterSource = new BeanPropertySqlParameterSource(contacts);
30             arr[i] = paramterSource;
31         }
32 
33         nameJdbcTemplate.batchUpdate(sql, arr);
34     }
35 
36 }

运行结果：

  

3.修改数据

修改单条数据

 1 package lixiuming.spring.jdbc;
 2 
 3 import org.junit.Test;
 4 import org.springframework.context.ApplicationContext;
 5 import org.springframework.context.support.ClassPathXmlApplicationContext;
 6 import org.springframework.jdbc.core.JdbcTemplate;
 7 import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
 8 
 9 public class JdbcTest {
10 
11     private ApplicationContext cxt = null;
12     private JdbcTemplate jdbcTemplate;
13     private NamedParameterJdbcTemplate nameJdbcTemplate;
14 
15     {
16         cxt = new ClassPathXmlApplicationContext("applicationcontext.xml");
17         jdbcTemplate = (JdbcTemplate) cxt.getBean("jdbcTemplate");
18         nameJdbcTemplate = (NamedParameterJdbcTemplate) cxt.getBean("namedParameterJdbcTemplate");
19     }
20 
21     // 数据修改
22     @Test
23     public void testUpdate() throws Exception {
24         String sql = "update contacts set name=? where id=?";
25         jdbcTemplate.update(sql, "tom", 5);
26 
27     }
28 
29 }

运行结果：

 

修改多条数据

 1 package lixiuming.spring.jdbc;
 2 
 3 import org.apache.commons.lang3.StringUtils;
 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 import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
 9 
10 public class JdbcTest {
11 
12     private ApplicationContext cxt = null;
13     private JdbcTemplate jdbcTemplate;
14     private NamedParameterJdbcTemplate nameJdbcTemplate;
15 
16     {
17         cxt = new ClassPathXmlApplicationContext("applicationcontext.xml");
18         jdbcTemplate = (JdbcTemplate) cxt.getBean("jdbcTemplate");
19         nameJdbcTemplate = (NamedParameterJdbcTemplate) cxt.getBean("namedParameterJdbcTemplate");
20     }
21 
22     // 数据修改
23     @Test
24     public void testUpdateBtch() throws Exception {
25         Integer[] ids = { 1, 2, 3, 4 };
26         String idsStr = StringUtils.join(ids, ",");
27         String sql = "update contacts set name=? where id in(" + idsStr + ")";
28         jdbcTemplate.update(sql, "jerry");
29 
30     }
31 
32 }

运行结果：

 

4.查询

查询单条数据

需要调用queryForObject(String sql, RowMapper<Contacts> rowMapper, Object... args) 其中放到RowMapper 指定如何去映射结果集的行，常用的实现类为

 BeanPropertyRowMapper ；使用SQL中列的别名完成列名和类的属性名的映射 ；不支持级联属性JdbcTemplate到底是一个JDBC的小工具，而不是ORM框架

1 @Test
2     public void testQueryForObject() {
3         String sql = " select id,name,address,phone  from contacts where id= ?";
4         RowMapper<Contacts> rowMapper = new BeanPropertyRowMapper<>(Contacts.class);
5         Contacts contacts = jdbcTemplate.queryForObject(sql, rowMapper, 1);
6         System.out.println(contacts);
7     }

打印结果：Context [id=1, name=jerry, address=aa, phone=aaa]

 

查询多条数据：

 1 /**
 2      * 查到实体类的集合
 3      */
 4     @Test
 5     public void testQueryForList() {
 6         String sql = "select * from contacts where id>?";
 7         RowMapper<Contacts> rowMapper = new BeanPropertyRowMapper<>(Contacts.class);
 8         List<Contacts> contactsList = jdbcTemplate.query(sql, rowMapper, 5);
 9         System.out.println(contactsList);
10     }

打印结果：[Context [id=6, name=eee, address=batchInsert, phone=123], Context [id=7, name=fff, address=batchInsert, phone=123], Context [id=8, name=gggg, address=ggggg, phone=ggggg], Context [id=9, name=123123123, address=kjsdlfkjslkdf, phone=djlksjdfl], Context [id=10, name=nameParam0, address=kjsdlfkjslkdf, phone=djlksjdfl], Context [id=11, name=nameParam1, address=kjsdlfkjslkdf, phone=djlksjdfl], Context [id=12, name=nameParam2, address=kjsdlfkjslkdf, phone=djlksjdfl], Context [id=13, name=nameParam3, address=kjsdlfkjslkdf, phone=djlksjdfl], Context [id=14, name=nameParam4, address=kjsdlfkjslkdf, phone=djlksjdfl]]

统计数据：

1 @Test
2     public void testQueryForObject2() {
3         String sql = " select count(id) from contacts ";
4         long count = jdbcTemplate.queryForObject(sql, Long.class);
5         System.out.println(count);
6     }

打印结果：14

5.删除

删除一条数据：

1   @Test
2     public void testDelete() throws Exception {
3         String sql = "delete from contacts where id=?";
4         jdbcTemplate.update(sql, 14);
5     }

运行结果：

 

删除多条数据示意：

1  @Test
2     public void testDeleteBatch() throws Exception {
3         String sql = "delete from contacts where id > ?";
4         jdbcTemplate.update(sql, 10);
5 
6     }

运行结果：

 

 

posted @ 2021-11-03 22:53  啄木鸟伍迪  阅读(730)  评论(0)  编辑  收藏  举报
//火箭 GenerateContentList();