springboot入门_数据库访问_jdbcTemplate-白红宇

springboot入门_数据库访问_jdbcTemplate

阅读量：6162 次

发布时间：2019-06-21

本文共 7944 字，大约阅读时间需要 26 分钟。

本文记录下在springboot项目中使用jdbcTemplate访问数据库。

创建springboot项目，添加依赖，pom文件如下：

1 
      2         
     
       3             
      
       org.springframework.boot
       4             
      
       spring-boot-starter-web
       5         
      6         
      7         
     
       8             
      
       org.springframework.boot
       9             
      
       spring-boot-starter-jdbc
      10         
     11         
     12         
     
      13             
      
       mysql
      14             
      
       mysql-connector-java
      15         
     16 17         
     18         
     
      19             
      
       com.alibaba
      20             
      
       fastjson
      21             
      
       1.2.47
      22

要访问数据库，我们必须配置数据库连接信息，application.properties中添加数据库链接信息。springboot中默认使用的是Hikari数据库连接池

1 #数据源配置2 spring.datasource.url=jdbc:mysql://localhost:3306/test3 spring.datasource.username=root4 spring.datasource.password=1234565 #驱动可以不配置，不配置时会从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
     
       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     @Override14     public int delete(Long id) {15         String sql = "delete from t_city where id = ?";16         return jdbcTemplate.update(sql, id);17     }18 19     @Override20     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     @Override26     public List
     
       findAllCity() {27         String sql = "select * from t_city";28         return jdbcTemplate.query(sql, new Object[]{}, new BeanPropertyRowMapper(City.class));29     }30 31     @Override32     public City findById(Long id) {33         String sql = "select * from t_city where id = ?";34         List
      
        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
     
       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     @Override13     public int delete(Long id) throws Exception {14         return cityDAO.delete(id);15     }16 17     @Override18     public int update(City city) throws Exception {19         return cityDAO.update(city);20     }21 22     @Override23     public List
     
       findAllCity() throws Exception {24         return cityDAO.findAllCity();25     }26 27     @Override28     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     @PostMapping34     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
     
       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 config2 jdbc.driverClassName=com.mysql.jdbc.Driver3 jdbc.url=jdbc:mysql://localhost:3306/test4 jdbc.username=root5 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     @Bean16     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 url26         dataSource.setUrl(url);27         dataSource.setUsername(username);28         dataSource.setPassword(password);29         System.out.println("初始化数据源end。。。");30         return dataSource;31     }32 33 }

启动项目可以看到其中日志中如下

转载于:https://www.cnblogs.com/wlzq/p/9669651.html

你可能感兴趣的文章