JdbcTemplate是Spring提供的一个JDBC模板类，是对JDBC的封装，简化JDBC代码。
可以让Spring集成其他的ORM框架:Mybatis、Hibernate等
环境：JDK17+IDEA+Maven+Spring6+JdbcTemplate
环境准备
准备数据库表 新建模板 New Module Maven项目
pom.xml配置 <!-- 仓库地址Spring io--> <repositories> <!-- Spring6里程碑版本的仓库--> <repository> <id>repository.Spring.milestone</id> <name>Spring Milestone Repository</name> <url>https://repo.spring.io/milestone</url> </repository> </repositories> <dependencies> <!-- Spring context依赖--> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>6.0.0-M2</version> </dependency> <!-- Spring jdbc依赖--> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>6.0.3</version> </dependency> <!-- mysql驱动--> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.17</version> </dependency> <!--junit依赖--> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.13.2</version> <scope>test</scope> </dependency> </dependencies>
Bean
数据源 //只要实现了DataSource接口都是数据源。 //数据源存在的目的是为了提供Connection对象 // dbcp连接池，C3p0连接池，都实现了DataSource接口 自己写的driver、url、name、password，需提供set方法，set注入
SpringConfig.xml配置 <!--配置数据源--> <bean id="ds" class="com.bean.DateSource.DateSourcedate"> <property name="driver" value="com.mysql.cj.jdbc.Driver"/> <property name="url" value="jdbc:mysql://ip:3306/mysql?"/> <property name="username" value="root"/> <property name="password" value="111111"/> </bean> <!-- 也可以集成其他开发的数据源c3p0 dbcp等--> <!-- 配置JabcTemplate--> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="ds"></property>
测试类
JdbcTemplate-insert
在JdbcTemplate环境准备好的情况下 测试类 @Test public void JdbcInsertTest(){ ApplicationContext applicationContext =new ClassPathXmlApplicationContext("SpringConfig.xml"); JdbcTemplate jabcTemplate = applicationContext.getBean("jdbcTemplate", JdbcTemplate.class); //insert String sql ="INSERT INTO TEST1 VALUES(?,?,?,?,?)"; //在JdbcTemplate模板中只要是增删改都是update int update = jabcTemplate.update(sql, 26, "李四", "南京", "2023-01-01", "女"); System.out.println("影响了:"+update+"行"); }
JdbcTemplate修改和删除
修改
删除
JdbcTemplate查一个对象
@Test public void JdbcSelectTest(){ ApplicationContext applicationContext =new ClassPathXmlApplicationContext("SpringConfig.xml"); JdbcTemplate jabcTemplate = applicationContext.getBean("jdbcTemplate", JdbcTemplate.class); String sql ="SELECT p_id,p_name,address,birth,gender FROM TEST1 WHERE p_id=?"; User user = jabcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(User.class),25); System.out.println(user.toString()); }
JdbcTemplate查多个对象
@Test public void JdbcSelectAllTest(){ ApplicationContext applicationContext =new ClassPathXmlApplicationContext("SpringjdbcConfig.xml"); JdbcTemplate jabcTemplate = applicationContext.getBean("jdbcTemplate", JdbcTemplate.class); //执行sql String sql ="SELECT p_id,p_name,address,birth,gender FROM TEST1 "; List<User> query = jabcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class)); query.forEach(e-> System.out.println(e.toString())); }
JdbcTemplate查一个值
@Test public void selectOneValueTest(){ ApplicationContext applicationContext =new ClassPathXmlApplicationContext("SpringjdbcConfig.xml"); JdbcTemplate jabcTemplate = applicationContext.getBean("jdbcTemplate", JdbcTemplate.class); String sql ="SELECT COUNT(*) FROM TEST1 WHERE gender='男'"; Integer integer = jabcTemplate.queryForObject(sql, Integer.class); System.out.println(integer); }
JdbcTemplate批量添加
public void batchInsertTest(){ ApplicationContext applicationContext =new ClassPathXmlApplicationContext("SpringjdbcConfig.xml"); JdbcTemplate jabcTemplate = applicationContext.getBean("jdbcTemplate", JdbcTemplate.class); String sql ="INSERT INTO TEST1 VALUES(?,?,?,?,?)"; Object[] objects1 ={27, "小妮", "南京", "2023-01-01", "女"}; Object[] objects2 ={28, "小红", "上海", "2023-01-01", "女"}; Object[] objects3 ={29, "小月", "南宁", "2023-01-01", "女"}; Object[] objects4 ={30, "小欣", "深圳", "2023-01-01", "女"}; List<Object[]> list = new ArrayList<>(); list.add(objects1); list.add(objects2); list.add(objects3); list.add(objects4); //在JdbcTemplate中只要是增删改都是update int[] count = jabcTemplate.batchUpdate(sql, list); System.out.println(count); }
JdbcTemplate批量更新
@Test public void batchUpdateTest(){ ApplicationContext applicationContext =new ClassPathXmlApplicationContext("SpringjdbcConfig.xml"); JdbcTemplate jabcTemplate = applicationContext.getBean("jdbcTemplate", JdbcTemplate.class); String sql ="UPDATE TEST1 SET gender=? WHERE p_id=?"; Object[] objects1 ={"女",27}; Object[] objects2 ={"女",28}; List<Object[]> list = new ArrayList<>(); list.add(objects1); list.add(objects2); //在JdbcTemplate中只要是增删改都是update int[] count = jabcTemplate.batchUpdate(sql, list); System.out.println(count); }
JdbcTemplate批量删除
@Test public void batchDeleteTest(){ ApplicationContext applicationContext =new ClassPathXmlApplicationContext("SpringjdbcConfig.xml"); JdbcTemplate jabcTemplate = applicationContext.getBean("jdbcTemplate", JdbcTemplate.class); String sql ="DELETE FROM TEST1 WHERE p_id=?"; Object[] objects1 ={27}; Object[] objects2 ={28}; List<Object[]> list = new ArrayList<>(); list.add(objects1); list.add(objects2); int[] count = jabcTemplate.batchUpdate(sql, list); System.out.println(count.length); }
JdbcTemplate回调函数
写jdbc代码，可以使用回调函数 @Test public void callBackTest(){ //写jdbc代码，可以使用回调函数 ApplicationContext applicationContext =new ClassPathXmlApplicationContext("SpringjdbcConfig.xml"); JdbcTemplate jabcTemplate = applicationContext.getBean("jdbcTemplate", JdbcTemplate.class); String sql ="SELECT p_id,p_name,gender FROM TEST1 WHERE p_id=?"; //注册回调函数，执行execute方法时会执行doInPreparedStatement() User execute = jabcTemplate.execute(sql, new PreparedStatementCallback<User>() { @Override public User doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException { User user = null; ps.setInt(1, 26); ResultSet resultSet = ps.executeQuery(); if (resultSet.next()) { int id = resultSet.getInt("p_id"); String name = resultSet.getString("p_name"); String gender = resultSet.getString("gender"); user = new User(); user.setP_id(id); user.setP_name(name); user.setGender(gender); } return user; } }); System.out.println(execute.toString()); }
JdbcTemplate回调函数整合德鲁伊连接池
德鲁伊连接池(阿里巴巴开发) Pom.xml <!-- 引入德鲁伊连接池--> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.2.15</version> </dependency>
SpringjdbcConfig.xml配置
