SpringBoot使用JdbcTemplate访问操作数据库
入门小站 人气:0Spring
对数据库的操作在jdbc上s面做了深层次的封装,使用spring的注入功能,可以把DataSource
注册到JdbcTemplate
之中。
JdbcTemplate
在Spring-jdbc
包下面,还需要Spring-tx
包支持,里面包含事务和异常控制.
一、建一个rumenz_springboot库
创建user表:
create table user( id int primary key auto_increment, name varchar(100) not null default '', domain varchar(100) not null default '' )engine=innodb default charset=utf8;
二、加入pom的依赖
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency>
三、SpringBoot配置文件
application.properties
spring.datasource.url=jdbc:mysql://localhost:3306/rumenz_springboot spring.datasource.username=root spring.datasource.password=root1234 spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
四、创建User实体类
@Builder @Data @AllArgsConstructor public class User implements RowMapper { private Integer id; private String name; private String domain; @Override public Object mapRow(ResultSet rs, int rowNum) throws SQLException { User user=new User(); user.setId(rs.getInt("id")); user.setName(rs.getString("name")); user.setDomain(rs.getString("domain")); return user; } }
五、Service接口
UserService.java
package com.rumenz.lession14.controller.service; import com.rumenz.lession14.controller.entity.User; import java.util.List; /** * @className: UserService * @description: TODO 类描述 * @author: 入门小站 rumenz.com * @date: 2021/12/13 **/ public interface UserService { Integer save(User user); List<User> list(); Integer update(User user); Integer batchSave(); }
六、Service接口实现类
UserServiceImpl.java
package com.rumenz.lession14.controller.service.Impl; import com.fasterxml.jackson.core.JsonProcessingException; import com.fasterxml.jackson.databind.ObjectMapper; import com.fasterxml.jackson.databind.json.JsonMapper; import com.rumenz.lession14.controller.entity.User; import com.rumenz.lession14.controller.service.UserService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.BeanPropertyRowMapper; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Service; import java.util.ArrayList; import java.util.List; /** * @className: UserServiceImpl * @description: TODO 类描述 * @author: 入门小站 rumenz.com * @date: 2021/12/13 **/ @Service public class UserServiceImpl implements UserService { @Autowired private JdbcTemplate jdbcTemplate; @Override public Integer save(User user) { int reint = jdbcTemplate.update("insert into user(name,domain) values (?,?)", user.getName(), user.getDomain()); return reint; } @Override public Integer batchSave() { String sql="insert into user(name,domain) values(?,?)"; List<Object[]> par=new ArrayList<>(); for (int i = 0; i < 10; i++) { String[] s=new String[2]; s[0]="入门小站"+i; s[1]="https://rumenz.com/"+i; par.add(s); } int[] ints = jdbcTemplate.batchUpdate(sql, par); System.out.println(ints.toString()); return 0; } @Override public List<User> list() { //User实现RowMapper接口,实现接口里的mapRow方法。 List<User> list = jdbcTemplate.query("select * from user",new User()); return list; } @Override public Integer update(User user) { Integer reint=jdbcTemplate.update("update user set name=?,domain=? where id=?", user.getName(),user.getDomain(),user.getId()); // return reint; } }
七、Controller测试
RumenzController.java
package com.rumenz.lession14.controller; import com.fasterxml.jackson.core.JsonProcessingException; import com.fasterxml.jackson.databind.ObjectMapper; import com.rumenz.lession14.controller.entity.User; import com.rumenz.lession14.controller.service.UserService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import java.util.List; /** * @className: RumenzController * @description: TODO 类描述 * @author: 入门小站 rumenz.com * @date: 2021/12/13 **/ @RestController @RequestMapping("/rumenz") public class RumenzController { @Autowired private UserService userService; //添加数据 @GetMapping("/save") public String save(){ User user=User.builder().name("入门小站").domain("https://rumenz.com").build(); Integer reint = userService.save(user); return reint.toString(); } //批量添加数据 @GetMapping("/batchSave") public String batchSave(){ Integer reint = userService.batchSave(); return reint.toString(); } //查询数据 @GetMapping("/list") public String list() throws JsonProcessingException { List<User> list = userService.list(); ObjectMapper objectMapper=new ObjectMapper(); String val = objectMapper.writeValueAsString(list); return val; } //更新数据 @GetMapping("/update") public String update() throws JsonProcessingException { User user=User.builder().id(1).name("入门小站-修改").domain("https://tooltt.com").build(); Integer reint = userService.update(user); return reint.toString(); } }
八、总结
常用CURD操作大致使用以下三个方法:
- 1.execute方法,用于直接执行SQL语句
- 2.update方法,用户新增修改删除操作
- 3.query方法,用于查询方法
加载全部内容