摘要:本文是springboot工程使用 JDBC Template连接 mysql 数据库,实现增删改查的实例,及在搭建过程中碰到的几个问题,前几篇介绍怎么搭建 Spring Boot工程,接下来直接入正题
什么是 jdbc ?
JDBC(Java DataBase Connectivity,java数据库连接)是一种用于执行SQL语句的 Java API,可以为多种关系数据库提供统一访问,它由一组用Java语言编写的类和接口组成。
JDBC的原理就是通过Connection这个类获取数据库的连接,然后通过PreparedStatement类处理SQL语句,再通过它的.setObject方法传入数据,最后通过方法.executeUpdate()和.executeQuery()执行更新
JdbcTemplate
template,是模板的意思,是Spring框架为我们提供的,JdbcTemplate是Spring框架自带的对JDBC操作的封装,目的是提供统一的模板方法使对数据库的操作更加方便、友好,效率也不错。但是功能还是不够强大(比如不支持级联属性),在实际应用中还需要和hibernate、mybaties等框架混合使用。
JdbcTemplate类主要提供以下四类方法
- execute方法:用于执行任何SQL语句,一般用于执行DDL语句;
- update方法及batchUpdate方法:update方法用于执行新增、修改、删除等语句,batchUpdate方法用于执行批处理相关语句;
- query方法及queryForXXX方法:用于执行查询相关语句;
- call方法:用于执行存储过程、函数相关语句
具体实现
- 需要的jar包依赖
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency>
- mysql的驱动jar包
<dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency>
- application.properties配置文件
spring.application.name=test server.port=8088 spring.datasource.url=jdbc:mysql://127.0.0.1:3306/db1 spring.datasource.username=root spring.datasource.password=root #说明一下,注释掉容易犯错的地方(后边会说明这个错误) #spring.datasource.driver-class-name=com.mysql.jdbc.Driver spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
- sql语句
/* Navicat MySQL Data Transfer Source Server : localhost_3306 Source Server Version : 50520 Source Host : localhost:3306 Source Database : db1 Target Server Type : MY sql Target Server Version : 50520 File Encoding : 65001 Date: 2019-03-04 22:36:54 */SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for `user` -- ---------------------------- DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `id` varchar (32) NOT NULL, `name` varchar(20) DEFAULT NULL, `sex` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of user -- ---------------------------- INSERT INTO `user` VALUES ('06170ace6fa94ac5aec51a4b30572fd0', 'Spring', 'Frame'); INSERT INTO `user` VALUES ('1', '刘能', '男的'); INSERT INTO `user` VALUES ('2', '王五', 'woman'); INSERT INTO `user` VALUES ('3d0bd85cd40f4a8584d352accfaf6db6', 'Spring', 'Frame'); INSERT INTO `user` VALUES ('a7ff791e54f648d9bc24dd4c472c1f84', 'Spring', 'Frame');
- Dao数据接口
package com.mundo.Test.service; import java.util.List; import com.mundo.Test.pojo.User; import com.mundo.Test.pojo.UserRowMapper; public interface UserService { List<User> queryAll(String sql, UserRowMapper userRowMapper, Object... objects); User selectById(String sql, UserRowMapper userRowMapper, String id); int updateById(String sql, Object... objects); int insert(String sql, Object... objects); int deleteById(String sql, String id); }
- 接口实现
/**
*
*/package com.mundo.Test.service.impl;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import com.mundo.Test.pojo.User;
import com.mundo.Test.pojo.UserRowMapper;
import com.mundo.Test.service.UserService;
/**
* @ClassName: UserServiceImpl
* @Description: TODO
* @author Meng.Xu
* @date 2019年3月4日 下午5:32:43
*/@Service
public class UserServiceImpl implements UserService {
@Autowired
private JdbcTemplate jdbcTemplate;
@ Override
public List<User> queryAll(String sql, UserRowMapper userRowMapper, Object... objects) {
return jdbcTemplate.query(sql, userRowMapper, objects);
}
@Override
public User selectById(String sql, UserRowMapper userRowMapper, String id) {
return jdbcTemplate.queryForObject(sql, userRowMapper, id);
}
@Override
public int updateById(String sql, Object...objects) {
return jdbcTemplate.update(sql, objects);
}
@Override
public int insert(String sql, Object... objects) {
return jdbcTemplate.update(sql, objects);
}
@Override
public int deleteById(String sql, String id) {
return jdbcTemplate.update(sql, id);
}
}
用的Junit 单元测试
- 需要在pom.xml中加入
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency>
- 单元测试增删改查
package com.mundo.Test;
import java.util.List;
import javax.annotation. Resource ;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.junit4.SpringRunner;
import com.alibaba.fastjson.JSONObject;
import com.mundo.Test.pojo.User;
import com.mundo.Test.pojo.UserRowMapper;
import com.mundo.Test.service.UserService;
import com.mundo.Test.util.UUIDUtil;
@RunWith(SpringRunner.class)
@SpringBootTest
public class TestApplicationTests {
@Resource
private JdbcTemplate jdbcTemplate;
private Logger logger = LoggerFactory.getLogger(TestApplicationTests.class);
@Resource
private UserService userService;
@Test
public void contextLoads() {
String sql = "select * from user";
List<User> queryAll = userService.queryAll(sql, new UserRowMapper());
System.out.println("返回对象集合:" + JSONObject.toJSONString(queryAll));
System.out.println("n");
sql = "select * from user where id = ?";
User user = userService.selectById(sql, new UserRowMapper(), "1");
System.out.println("返回对象:" + JSONObject.toJSONString(user));
System.out.println("n");
sql = "update user set name = ?, sex = ? where id = ?";
int updateById = userService.updateById(sql, "王五", "woman", "2");
System.out.println("更新成功");
System.out.println("n");
sql = "insert into user(id,name,sex)values(?,?,?)";
int insert = userService.insert(sql, UUIDUtil.getUUID(), "Spring", "Frame");
System.out.println("插入成功");
System.out.println("n");
sql = "delete from user where id = ?";
int deleteById = userService.deleteById(sql, "3");
System.out.println("删除成功");
}
}
- 实体类
package com.mundo.Test.pojo; /** * @ClassName: User * @Description: TODO * @author Meng.Xu * @date 2019年3月4日 下午9:09:22 */public class User { private String id; private String name; private String sex; public String getId() { return id; } public void setId(String id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } }
- 数据封装类(返回自定义类型根据需要),这个是比较简单易懂,感兴趣的可以自己利用泛型,反射写一个通用的(项目中实体类肯定很多)
package com.mundo.Test.pojo; import java.sql.ResultSet; import java.sql.SQLException; import org.springframework.jdbc.core.RowMapper; /** * @ClassName: UserRowMapper * @Description: TODO * @author Meng.Xu * @date 2019年3月4日 下午10:02:29 */public class UserRowMapper implements RowMapper<User> { User user = null; @Override public User mapRow(ResultSet rs, int rowNum) throws SQLException { System.out.println(rowNum); // 先从结果集里把数据得到 // 然后把数据封装到对象里 user = new User(); user.setId(rs.getString("id")); user.setName(rs.getString("name")); user.setSex(rs.getString("sex")); return user; } }
Jdbctemplate到这里简单的增删改查就告一段落了,接下来将介绍SpringBoot+Mybatis连接数据库,敬请期待!
每天进步一点点,前进不止一小点, 你侬我侬不如码农 ,欢迎关注转发评论!