Spring对数据库的操作是对jdbc的封装,类似Mybatis,说白了就是jdbc的Spring增强版,使用spring的注入功能,可以把DataSource注册到JdbcTemplate中。
JdbcTemplate是Spring提供的一个JDBC模板类,是对JDBC的封装,jdbc的Spring增强版,简化JDBC代码。
可以让Spring集成其他的ORM框架:Mybatis、Hibernate等
环境:JDK17+IDEA+Maven+Spring6+JdbcTemplate
环境准备
所需依赖
Spring context依赖
Spring jdbc依赖
mysql-connector-java依赖
junit依赖
druid依赖
准备数据库表
新建模板 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>
<!-- 引入德鲁伊连接池-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.15</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配置
如有错误请指正,谢谢