spring5—使用JdbcTemplate操作数据库
JdbcTemplate的使用
1. JdbcTemplate的概念及使用
- Spring框架对JDBC进行封装,使用JdbcTemplate完成对数据库的操作
- 创建xml配置文件,并配置数据库连接池
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close"> <property name="url" value="jdbc:mysql:///userdb" /> <property name="username" value="root" /> <property name="password" value="root" /> <property name="driverClassName" value="com.mysql.jdbc.Driver" /></bean>
- 配置JdbcTemplate对象,注入DataSource
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource"></property></bean>
- 开启组件扫描,也可以单独写成配置文件
<context:component-scan base-package="com.spring"></context:component-scan>
2. JdbcTemplate操作数据库(CRUD)
2.1 普通操作
以添加为例
整体结构
- 创建service、dao类,在dao注入JdbcTemplate对象
@Servicepublic class UserService { //注入 dao @Autowired private UserDao userDao;}@Repositorypublic class UserDaoImpl implements UserDao { //注入 JdbcTemplate @Autowired private JdbcTemplate jdbcTemplate;}
-
创建数据库userDB,创建数据表t_user
-
根据数据库创建对应实体类User
package com.spring.entity;public class User { private String userId; private String username; private String ustatus; public String getUserId() { return userId; } public void setUserId(String userId) { this.userId = userId; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getUstatus() { return ustatus; } public void setUstatus(String ustatus) { this.ustatus = ustatus; } @Override public String toString() { return "User{" + "userId='" + userId + '\'' + ", username='" + username + '\'' + ", ustatus='" + ustatus + '\'' + '}'; }}
- 编写UserService和UserDao
@Servicepublic class UserService { //注入dao @Autowired private UserDao userDao; //调用数据库添加的方法 public void addUser(User user) { userDao.add(user); }}
@Repositorypublic class UserDaoImpl implements UserDao { //注入jdbcTemplate @Autowired private JdbcTemplate jdbcTemplate; @Override public void add(User user) { String sql = "insert into t_user values(?,?,?)"; Object[] args = {user.getUserId(), user.getUsername(), user.getUstatus()}; int update = jdbcTemplate.update(sql, args); System.out.println(update); }}
- 测试类
public class TestUser { @Test public void test() { ApplicationContext context = new ClassPathXmlApplicationContext("database.xml"); UserService userService = context.getBean("userService", UserService.class); User user = new User(); user.setUserId("1"); user.setUsername("码云"); user.setUstatus("离职"); //增加 userService.addUser(user); }}
删除和修改同添加,使用:jdbcTemplate.update(sql, args);
根据id查询使用:jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper(User.class), id);
2.2 批量操作
UserService.java
//批量添加public void batchAdd(List<Object[]> batchArgs){ userDao.batchAddUser(batchArgs);}
UserDaoImpl.java
@Overridepublic void batchAddUser(List<Object[]> batchArgs) { String sql = "insert into t_user values(?,?,?)"; int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs); System.out.println(Arrays.toString(ints));}
测试
批量添加List<Object[]> batchArgs = new ArrayList<>();Object[] o1 = {"1", "李彦宏", "在职"};Object[] o2 = {"2", "李斯", "在职"};Object[] o3 = {"3", "王为民", "离职"};batchArgs.add(o1);batchArgs.add(o2);batchArgs.add(o3);userService.batchAdd(batchArgs);
批量修改和批量删除同添加。