springboot入门_数据库访问_jdbcTemplate
本文记录下在springboot项目中使用jdbcTemplate访问数据库。
创建springboot项目，添加依赖，pom文件如下：
1 <!--web 依赖--> 2 <dependency> 3 <groupId>org.springframework.boot</groupId> 4 <artifactId>spring-boot-starter-web</artifactId> 5 </dependency> 6 <!--spring jdbc依赖--> 7 <dependency> 8 <groupId>org.springframework.boot</groupId> 9 <artifactId>spring-boot-starter-jdbc</artifactId> 10 </dependency> 11 <!--mysql 依赖--> 12 <dependency> 13 <groupId>mysql</groupId> 14 <artifactId>mysql-connector-java</artifactId> 15 </dependency> 16 17 <!-- fastjson --> 18 <dependency> 19 <groupId>com.alibaba</groupId> 20 <artifactId>fastjson</artifactId> 21 <version>1.2.47</version> 22 </dependency>
要访问数据库，我们必须配置数据库连接信息，application.properties中添加数据库链接信息。springboot中默认使用的是Hikari数据库连接池
1 #数据源配置 2 spring.datasource.url=jdbc:mysql://localhost:3306/test 3 spring.datasource.username=root 4 spring.datasource.password=123456 5 #驱动可以不配置，不配置时会从url中解析 6 spring.datasource.driver-class-name=com.mysql.jdbc.Driver
如果我们不想使用Hikari，比如想使用阿里的德鲁伊，我们可以修改
1 spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
数据库创建表：
1 CREATE TABLE `t_city` ( 2 `id` BIGINT(11) NOT NULL AUTO_INCREMENT, 3 `cityName` VARCHAR(50) NOT NULL, 4 `cityCode` VARCHAR(50) NOT NULL, 5 PRIMARY KEY (`id`) 6 ) ENGINE=INNODB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8
创建与数据库表对应的实体类：
1 public class City { 2 private Long id; 3 private String cityName; 4 private String cityCode; 5 6 public City(){} 7 8 public City(String cityName, String cityCode){ 9 this.cityName = cityName; 10 this.cityCode = cityCode; 11 } 12 //省略get和set方法 13 }
定义dao层接口及实现
1 public interface CityDAO { 2 3 int add(City city) throws Exception; 4 5 int delete(Long id) throws Exception; 6 7 int update(City city) throws Exception; 8 9 List<City> findAllCity() throws Exception; 10 11 City findById(Long id) throws Exception; 12 13 }
1 @Service("cityDAO") 2 public class CityDAOImpl implements CityDAO { 3 4 @Autowired 5 private JdbcTemplate jdbcTemplate; 6 7 @Override 8 public int add(City city) { 9 String sql = "insert into t_city (cityCode, cityName) values (?, ?)"; 10 return jdbcTemplate.update(sql, city.getCityCode(), city.getCityName()); 11 } 12 13 @Override 14 public int delete(Long id) { 15 String sql = "delete from t_city where id = ?"; 16 return jdbcTemplate.update(sql, id); 17 } 18 19 @Override 20 public int update(City city) { 21 String sql = "update t_city set cityCode = ?, cityName = ? where id = ?"; 22 return jdbcTemplate.update(sql, city.getCityCode(), city.getCityName(), city.getId()); 23 } 24 25 @Override 26 public List<City> findAllCity() { 27 String sql = "select * from t_city"; 28 return jdbcTemplate.query(sql, new Object[]{}, new BeanPropertyRowMapper(City.class)); 29 } 30 31 @Override 32 public City findById(Long id) { 33 String sql = "select * from t_city where id = ?"; 34 List<City> cityList = jdbcTemplate.query(sql, new Object[]{id}, new BeanPropertyRowMapper(City.class)); 35 if(cityList != null && cityList.size()>0){ 36 return cityList.get(0); 37 } 38 return null; 39 } 40 }
定义service层接口及实现
1 public interface CityService { 2 3 int add(City city) throws Exception; 4 5 int delete(Long id) throws Exception; 6 7 int update(City city) throws Exception; 8 9 List<City> findAllCity() throws Exception; 10 11 City findById(Long id) throws Exception; 12 13 }
1 @Service("cityService") 2 public class CityServiceImpl implements CityService { 3 4 @Autowired 5 private CityDAO cityDAO; 6 7 @Override 8 public int add(City city) throws Exception { 9 return cityDAO.add(city); 10 } 11 12 @Override 13 public int delete(Long id) throws Exception { 14 return cityDAO.delete(id); 15 } 16 17 @Override 18 public int update(City city) throws Exception { 19 return cityDAO.update(city); 20 } 21 22 @Override 23 public List<City> findAllCity() throws Exception { 24 return cityDAO.findAllCity(); 25 } 26 27 @Override 28 public City findById(Long id) throws Exception { 29 return cityDAO.findById(id); 30 } 31 }
此处service和dao之间的代码看似可以省略一层，但是一般我们在dao层只做与数据库的交互，在service层处理相应的业务逻辑，所以还是需要做分层的。
为了看出代码执行效果，定义几个简单的restful api做测试。
controller代码如下
1 @RestController 2 @RequestMapping("/datas/city/jdbc") 3 public class CityController { 4 5 @Autowired 6 private CityService cityService; 7 8 @PutMapping 9 public String add(@RequestBody JSONObject jsonObject){ 10 String cityCode = jsonObject.getString("cityCode"); 11 String cityName = jsonObject.getString("cityName"); 12 City city = new City(cityName, cityCode); 13 try { 14 cityService.add(city); 15 return "success"; 16 } catch (Exception e) { 17 e.printStackTrace(); 18 return "fail"; 19 } 20 } 21 22 @DeleteMapping("/{id}") 23 public String delete(@PathVariable Long id){ 24 try { 25 cityService.delete(id); 26 return "success"; 27 } catch (Exception e) { 28 e.printStackTrace(); 29 return "fail"; 30 } 31 } 32 33 @PostMapping 34 public String update(@RequestBody JSONObject jsonObject){ 35 Long id = jsonObject.getLong("id"); 36 try { 37 City city = cityService.findById(id); 38 if(city != null){ 39 String cityCode = jsonObject.getString("cityCode"); 40 city.setCityCode(cityCode); 41 String cityName = jsonObject.getString("cityName"); 42 city.setCityName(cityName); 43 cityService.update(city); 44 return "success"; 45 } 46 } catch (Exception e) { 47 e.printStackTrace(); 48 } 49 return "fail"; 50 } 51 52 @GetMapping("/list") 53 public String list(){ 54 try { 55 List<City> cityList = cityService.findAllCity(); 56 return cityList.toString(); 57 } catch (Exception e) { 58 e.printStackTrace(); 59 } 60 return null; 61 } 62 }
如果我们不想使用springboot提供的默认配置信息，想要自己配置数据源信息也是可以的。自己配置就不需要在application.properties文件中配置datasource相关的信息了，我们可以创建自己的配置文件，在配置勒种读取并设置即可，
创建dbSource.properties
1 #datasource config 2 jdbc.driverClassName=com.mysql.jdbc.Driver 3 jdbc.url=jdbc:mysql://localhost:3306/test 4 jdbc.username=root 5 jdbc.password=123456
创建配置类
1 @Configuration 2 @PropertySource("classpath:dbSource.properties") 3 public class DataSourceConfig { 4 5 @Autowired 6 private Environment env; 7 8 @Bean 9 public JdbcTemplate jdbcTemplate(){ 10 JdbcTemplate jdbcTemplate = new JdbcTemplate(); 11 jdbcTemplate.setDataSource(dataSource()); 12 return jdbcTemplate; 13 } 14 15 @Bean 16 public DataSource dataSource(){ 17 System.out.println("初始化数据源start。。。"); 18 //HikariDataSource dataSource = new HikariDataSource(); 19 DruidDataSource dataSource = new DruidDataSource(); 20 String driver = env.getProperty("jdbc.driverClassName").trim(); 21 String url = env.getProperty("jdbc.url").trim(); 22 String username = env.getProperty("jdbc.username").trim(); 23 String password = env.getProperty("jdbc.password").trim(); 24 dataSource.setDriverClassName(driver); 25 //dataSource.setJdbcUrl(url);//Hikar url 26 dataSource.setUrl(url); 27 dataSource.setUsername(username); 28 dataSource.setPassword(password); 29 System.out.println("初始化数据源end。。。"); 30 return dataSource; 31 } 32 33 }
启动项目可以看到其中日志中如下
