前言
JdbcTemplate简介
- JdbcTemplate是Spring框架自带的对JDBC操作的封装，目的是提供统一的模板方法使对数据库的操作更加方便、友好，效率也不错。但是功能还是不够强大（比如不支持级联属性），在实际应用中还需要和hibernate、mybaties等框架混合使用。
- 优点：运行期：高效、内嵌Spring框架中、支持基于AOP的声明式事务。
- 缺点：必须于Spring框架结合在一起使用、不支持数据库跨平台、默认没有缓存。
JdbcTemplate主要提供以下五类方法：
- execute方法：可以用于执行任何SQL语句，一般用于执行DDL语句；
- update方法及batchUpdate方法：update方法用于执行新增、修改、删除等语句；batchUpdate方法用于执行批处理相关语句；
- query方法及queryForXXX方法：用于执行查询相关语句；
- call方法：用于执行存储过程、函数相关语句。
一、pom.xml文件
添加JdbcTemplate依赖。
<dependencies>
<!--JdbcTemplate-->
<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>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
二、配置文件
spring:
# datasource config
datasource:
url: jdbc:mysql://localhost:3306/test?serverTimezone=GMT
username: root
password: 123456
driver-class-name: com.mysql.jdbc.Driver
三、实体类
User类实现了RowMapper类，重写了mapRow方法。
public class User implements RowMapper<User> {
private int id;
private String username;
private String password;
public User() {
}
public User(int id, String username, String password) {
this.id = id;
this.username = username;
this.password = password;
}
public User(String username, String password) {
this.username = username;
this.password = password;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public User mapRow(ResultSet resultSet, int i) throws SQLException {
User user = new User();
user.setId(resultSet.getInt(("id")));
user.setUsername(resultSet.getString("username"));
user.setPassword(resultSet.getString("password"));
return user;
}
}
四、Controller类
@RestController
public class UserController {
@Autowired
private JdbcTemplate jdbcTemplate;
//http://localhost:8080/createTable
@GetMapping("createTable")
public String createTable() {
String sql = "CREATE TABLE `user` (\n" +
" `id` int(11) NOT NULL AUTO_INCREMENT,\n" +
" `username` varchar(255) DEFAULT NULL,\n" +
" `password` varchar(255) DEFAULT NULL,\n" +
" PRIMARY KEY (`id`)\n" +
") ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;\n" +
"\n";
jdbcTemplate.execute(sql);
return "创建User表成功";
}
//http://localhost:8080/saveUserSql
@GetMapping("saveUserSql")
public String saveUserSql() {
String sql = "INSERT INTO USER (username,password) VALUES ('admin','123456')";
int rows = jdbcTemplate.update(sql);
return "执行成功，影响" + rows + "行";
}
//http://localhost:8080/saveUser?userName=lisi&passWord=111
@GetMapping("saveUser")
public String saveUser(String userName, String passWord) {
int rows = jdbcTemplate.update("INSERT INTO USER (username,password) VALUES (?,?)", userName, passWord);
return "执行成功，影响" + rows + "行";
}
//http://localhost:8080/updateUserPassword?id=1&passWord=111
@GetMapping("updateUserPassword")
public String updateUserPassword(int id, String passWord) {
int rows = jdbcTemplate.update("UPDATE USER SET password = ? WHERE ID = ?", passWord, id);
return "执行成功，影响" + rows + "行";
}
//http://localhost:8080/deleteUserById?id=1
@GetMapping("deleteUserById")
public String deleteUserById(int id) {
int rows = jdbcTemplate.update("DELETE FROM USER WHERE ID = ?", id);
return "执行成功，影响" + rows + "行";
}
//http://localhost:8080/batchSaveUserSql
@GetMapping("batchSaveUserSql")
public String batchSaveUserSql() {
String sql =
"INSERT INTO USER (username,password) VALUES (?,?)";
List<Object[]> paramList = new ArrayList<>();
for (int i = 0; i < 10; i++) {
String[] arr = new String[2];
arr[0] = "zhangsan" + i;
arr[1] = "password" + i;
paramList.add(arr);
}
jdbcTemplate.batchUpdate(sql, paramList);
return "执行成功";
}
//http://localhost:8080/getUserByUserName?userName=zhangsan0
@GetMapping("getUserByUserName")
public List getUserByUserName(String userName) {
String sql = "SELECT * FROM USER WHERE username = ?";
//写法很多种
//下面列举两种写法，都可以实现
//List<User> list= jdbcTemplate.query(sql,new Object[]{userName}, new BeanPropertyRowMapper(User.class));
List<User> list = jdbcTemplate.query(sql, new User(), new Object[]{userName});
return list;
}
//http://localhost:8080/getMapById?id=1
@GetMapping("getMapById")
public Map getMapById(Integer id) {
String sql = "SELECT * FROM USER WHERE ID = ?";
Map map = jdbcTemplate.queryForMap(sql, id);
return map;
}
//http://localhost:8080/getUserById?id=1
@GetMapping("getUserById")
public User getUserById(Integer id) {
String sql = "SELECT * FROM USER WHERE ID = ?";
User user = jdbcTemplate.queryForObject(sql, new User(), new Object[]{id});
return user;
}
}
