目录一、JdbcTemplate二、实战2.1 引入依赖2.2 配置连接池2.3 配置JdbcTemplate 对象，注入 DataSource2.4 扫描注解2.5 创建 service 类，创建 dao 类，在 dao 注入 jdbcTemplate 对象三、操作(CRUD)3.1 添加3.2 修改3.3 删除四、查询4.1 查询总记录数 jdbcTemplate.queryForObject4.2 查询返回对象4.3 查询返回集合五、批量操作 jdbcTemplate.batchUpdate5.1 添加5.2 修改5.3 删除
一、JdbcTemplate
Spring 框架对 JDBC 进行封装，使用 JdbcTemplate 方便实现对数据库操作
二、实战
2.1 引入依赖
<dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.24</version> </dependency> <!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>5.3.6</version> </dependency> <!-- https://mvnrepository.com/artifact/org.springframework/spring-orm --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-orm</artifactId> <version>5.3.6</version> </dependency> <!-- https://mvnrepository.com/artifact/org.springframework/spring-tx --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-tx</artifactId> <version>5.3.6</version> </dependency>
2.2 配置连接池
<!--引入外部属性文件 --> <context:property-placeholder location="classpath:jdbc.properties" /> <!-- 数据库连接池 --> <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close"> <property name="url" value="${prop.url}" /> <property name="username" value="${prop.userName}" /> <property name="password" value="${prop.password}" /> <property name="driverClassName" value="${prop.driverClass}" /> </bean>
2.3 配置JdbcTemplate 对象，注入 DataSource
<!-- JdbcTemplate 对象 --> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <!--注入 dataSource --> <property name="dataSource" ref="dataSource"></property> </bean>
2.4 扫描注解
<!-- 开启注解扫描 --> <context:component-scan base-package="cn.zj.aop.an"></context:component-scan>
2.5 创建 service 类，创建 dao 类，在 dao 注入 jdbcTemplate 对象
@Repositorypublic class UserDaoImpl implements UserDao { //注入 JdbcTemplate @Autowired private JdbcTemplate jdbcTemplate;}@Servicepublic class UserService { // 注入 dao @Autowired private UserDao userDao; }
三、操作(CRUD)
实体类
public class User { private String userId; private String username; private String ustatus; @Override public String toString() { return "User [userId=" + userId + ", username=" + username + ", ustatus=" + 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; }}
3.1 添加
service//添加 public void addUser(User user) { userDao.add(user); } dao@Override public void add(User user) { // 1 创建 sql 语句 String sql = "insert into t_user values(?,?,?)"; // 2 调用方法实现 Object[] args = { user.getUserId(), user.getUsername(), user.getUstatus() }; int update = jdbcTemplate.update(sql, args); System.out.println(update); } 测试@Test public void test1() { ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml"); UserService userService = context.getBean("userService", UserService.class); User user = new User(); user.setUserId("1"); user.setUsername("java"); user.setUstatus("a"); userService.addUser(user); }
结果
3.2 修改
service//修改 public void updateUser(User user) { userDao.updateUser(user); }dao@Override public void updateUser(User user) { // TODO Auto-generated method stub String sql = "update t_user set username=?,ustatus=? where userId=?"; // 2 调用方法实现 Object[] args = { user.getUsername(), user.getUstatus() ,user.getUserId()}; int update = jdbcTemplate.update(sql, args); System.out.println(update); }测试@Test public void test2() { ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml"); UserService userService = context.getBean("userService", UserService.class); User user = new User(); user.setUserId("1"); user.setUsername("javaScrip"); user.setUstatus("abc"); userService.updateUser(user); }
3.3 删除
// 删除 public void deleteUser(String id) { userDao.deleteUser(id); }@Override public void deleteUser(String id) { String sql="delete from t_user where userId=?"; int update=jdbcTemplate.update(sql, id); System.out.println(update); }@Test public void test3() { ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml"); UserService userService = context.getBean("userService", UserService.class); userService.deleteUser("1"); }
四、查询
4.1 查询总记录数 jdbcTemplate.queryForObject
@Test public void test4() { ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml"); UserService userService = context.getBean("userService", UserService.class); int count = userService.selectUserCount(); System.out.println("数据库中共有记录:"+count); }//查询记录数 public int selectUserCount() { return userDao.selectCount(); } @Override public int selectCount() { String sql = "select count(0) from t_user"; Integer count = jdbcTemplate.queryForObject(sql, Integer.class); return count; }
4.2 查询返回对象
@Test public void test5() { ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml"); UserService userService = context.getBean("userService", UserService.class); User user=userService.findUserInfo("1"); System.out.println(user); }//查询对象 public User findUserInfo(String id) { // TODO Auto-generated method stub return userDao.findUserInfo(id); }@Override public User findUserInfo(String id) { String sql = "select userId,username,ustatus from t_user where userId=?"; User user = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<User>(User. class),id); return user; }
4.3 查询返回集合
@Test public void test6() { ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml"); UserService userService = context.getBean("userService", UserService.class); List<User> list=userService.findAllUser(); System.out.println(list); } //查询返回集合 public List<User> findAllUser(){ return userDao.findAllUser(); }@Override public List<User> findAllUser() { String sql = "select userId,username,ustatus from t_user"; List<User> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<User>(User. class)); return list; }
五、批量操作 jdbcTemplate.batchUpdate
5.1 添加
@Test public void test7() { ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml"); UserService userService = context.getBean("userService", UserService.class); List<Object[]> list = new ArrayList<>(); Object[] o1 = { "11", "易语言", "中文"}; Object[] o2 = { "12", "c++", "cc"}; Object[] o3 = { "13", "MySQL", "数据库"}; list.add(o1); list.add(o2); list.add(o3); userService.batchAdd(list); }//批量添加 public void batchAdd(List<Object[]> list){ userDao.batchAdd(list); }@Override public void batchAdd(List<Object[]> list) { String sql = "insert into t_user values(?,?,?)"; int[] ints = jdbcTemplate.batchUpdate(sql, list); System.out.println(ints); }
5.2 修改
@Test public void test8() { ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml"); UserService userService = context.getBean("userService", UserService.class); List<Object[]> list = new ArrayList<>(); Object[] o1 = { "易语言易", "中文语言","11"}; Object[] o2 = { "c++c", "ccccc","12"}; Object[] o3 = {"MySQL+ORACle", "数据库数据", "13"}; list.add(o1); list.add(o2); list.add(o3); userService.batchUpdate(list); } //批量修改 public void batchUpdate(List<Object[]> list) { userDao.batchUpdate(list); }@Override public void batchUpdate(List<Object[]> list) { String sql = "update t_user set username=?,ustatus=? where userId=?"; int[] ints = jdbcTemplate.batchUpdate(sql, list); System.out.println(ints); }
5.3 删除
@Test public void test9() { ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml"); UserService userService = context.getBean("userService", UserService.class); List<Object[]> list = new ArrayList<>(); Object[] o1 = { "11"}; Object[] o2 = { "12"}; Object[] o3 = { "13"}; list.add(o1); list.add(o2); list.add(o3); userService.batchDelete(list); } //批量删除 public void batchDelete(List<Object[]> list) { userDao.batchDelete(list); }@Override public void batchDelete(List<Object[]> list) { String sql = "delete from t_user where userId=?"; int[] ints = jdbcTemplate.batchUpdate(sql, list); System.out.println(ints); }
到此这篇关于Java Spring5学习之JdbcTemplate详解的文章就介绍到这了,更多相关Java Spring5之JdbcTemplate内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！
享受每一刻的感觉，欣赏每一处的风景，这就是人生。
