(6)spring boot下使用jdbcTemplate操作数据库

操作步骤:

1）pom.xml文件中添加mysql-connector-java,spring-boot-starter-jdbc

但是如果已经添加spring-boot-starter-data-jpa依赖，则无需添加spring-boot-starter-jdbc依赖。

2）配置application.properties(包括数据库等配置)

3）创建一个实体类，比如Cat

4）创建一个Dao,包含jdbcTemplate详细使用，比如CatDao

5）创建一个service，比如CatService

6）创建一个Controller,比如CatController

7）测试

实例:

项目结构:

1）. pom.xml加入依赖

<!-- 添加spring-data-jpa依赖 --><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-data-jpa</artifactId></dependency><!-- 添加spring boot jdbcTemplate依赖，如果已经添加了spring-boot-starter-data-jpa依赖，则无需添加spring-boot-starter-jdbc依赖。--><!-- <dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-jdbc</artifactId></dependency> -->

关于mysql,spring boot相关依赖pom.xm完整配置文件:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">  <modelVersion>4.0.0</modelVersion>  <groupId>com.lanhuigu</groupId>  <artifactId>spring-boot</artifactId>  <version>0.0.1-SNAPSHOT</version>  <packaging>jar</packaging>  <name>spring-boot</name>  <url>http://maven.apache.org</url>  <parent>    <groupId>org.springframework.boot</groupId>    <artifactId>spring-boot-starter-parent</artifactId>    <version>1.4.1.RELEASE</version>  </parent>    <properties>    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>    <!-- 配置jdk版本，默认为1.6 -->    <java.version>1.8.0_66</java.version>  </properties>    <!-- spring boot热部署插件springloaded -->  <build>  <plugins>  <plugin>  <groupId>org.springframework.boot</groupId>  <artifactId>spring-boot-maven-plugin</artifactId>  <dependencies>  <dependency>  <groupId>org.springframework</groupId>  <artifactId>springloaded</artifactId>  <version>1.2.4.RELEASE</version>  </dependency>  </dependencies>  <executions>  <execution>  <goals>  <goal>repackage</goal>  </goals>  <configuration>  <classifier>exec</classifier>  </configuration>  </execution>  </executions>  </plugin>  </plugins>  </build>    <dependencies>    <dependency>      <groupId>junit</groupId>      <artifactId>junit</artifactId>      <version>3.8.1</version>      <scope>test</scope>    </dependency>    <!-- spring boot引入相关web开发jar包，包括spring,spring mvc,spring boot,json框架等等 -->    <dependency>  <groupId>org.springframework.boot</groupId>  <artifactId>spring-boot-starter-web</artifactId>  <!-- 这个地方不需要指定jar包版本，根据parent配置自动选择 -->  <!-- <version>1.4.1.RELEASE</version> --></dependency><dependency>  <groupId>com.alibaba</groupId>  <artifactId>fastjson</artifactId>  <version>1.2.15</version></dependency><!-- 添加MySQL数据库驱动依赖 --><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId></dependency><!-- 添加spring-data-jpa依赖 --><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-data-jpa</artifactId></dependency><!-- 添加spring boot jdbcTemplate依赖，如果已经添加了spring-boot-starter-data-jpa依赖，则无需添加spring-boot-starter-jdbc依赖。--><!-- <dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-jdbc</artifactId></dependency> -->  </dependencies></project>

2）配置application.properties(包括数据库等配置)

# spring boot绑定默认端口为8080，这里修改为9000server.port=9000###################################################datasource --指定MySQL数据库连接信息#############################################spring.datasource.url = jdbc:mysql://192.168.200.222:3306/dongxihui_webspring.datasource.username = rootspring.datasource.password = 123456spring.datasource.driverClassName = com.mysql.jdbc.Driverspring.datasource.max-active = 20spring.datasource.max-idle = 8spring.datasource.min-idle = 8spring.datasource.initial-size = 10###################################################Java Persistence Api -- Spring jpa 的配置信息############################################## 指定数据库管理系统spring.jpa.database = MYSQL# 是否打印sql到控制台spring.jpa.show-sql = true# Hibernate ddl autospring.jpa.hibernate.ddl-auto = update# 指定命名策略#[org.hibernate.cfg.ImprovedNamingStrategy #org.hibernate.cfg.Default]spring.jpa.hibernate.naming-strategy = org.hibernate.cfg.ImprovedNamingStrategy# 指定hibernate方言spring.jpa.propeties.hibernate.dialect = org.hibernate.dialect.MYSQL

3）创建一个实体类，比如Cat

package com.lanhuigu.demo.bean;import javax.persistence.Entity;import javax.persistence.GeneratedValue;import javax.persistence.GenerationType;import javax.persistence.Id;/** * 创建一个实体 *  * 1、如何持久化？ * 使用@Entity注解进行实体类的持久化操作，当JPA检测到我们的实体类有 * @Entity注解的时候，会在数据库表中生成对应的表结构信息。 *  * 2、如何指定主键以及主键的生成策略？ * 使用@Id指定主键 */@Entitypublic class Cat {/** * 主键: * 使用@Id指定主键。 *  * 生成策略: * 使用代码@GeneratedValue(strategy=GenerationType.AUTO) * 指定主键的生成策略,MySQL默认为自增长。 */@Id @GeneratedValue(strategy=GenerationType.AUTO)private int id;// 主键private String catName;// 姓名，默认生成字段名cat_nameprivate int catAge;// 年龄，默认生成字段名cat_agepublic int getId() {return id;}public void setId(int id) {this.id = id;}public String getCatName() {return catName;}public void setCatName(String catName) {this.catName = catName;}public int getCatAge() {return catAge;}public void setCatAge(int catAge) {this.catAge = catAge;}}

4）创建一个Dao,包含jdbcTemplate详细使用，比如CatDao

package com.lanhuigu.demo.dao;import javax.annotation.Resource;import org.springframework.jdbc.core.BeanPropertyRowMapper;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.jdbc.core.RowMapper;import org.springframework.stereotype.Repository;import com.lanhuigu.demo.bean.Cat;/** * 通过@Repository注解标注该类为持久化操作对象 */@Repositorypublic class CatDao {// 通过@Resource注解引入JdbcTemplate对象@Resourceprivate JdbcTemplate jdbcTemplate;/** * 使用JdbcTemplate查询数据步骤: * 1. 定义一个sql语句 * 2. 定义一个RowMapper * 3. 执行查询方法 */public Cat queryByCatName(String catName) {// 1. 定义一个sql语句String querySQL = "select * from cat where cat_name = ?";// 2. 定义一个RowMapperRowMapper<Cat> rowMapper = new BeanPropertyRowMapper<Cat>(Cat.class);// 3. 执行查询方法Cat cat = jdbcTemplate.queryForObject(querySQL, new Object[]{catName}, rowMapper);return cat;}}

5）创建一个service，比如CatService

package com.lanhuigu.demo.service;import javax.annotation.Resource;import javax.transaction.Transactional;import org.springframework.stereotype.Service;import com.lanhuigu.demo.bean.Cat;import com.lanhuigu.demo.dao.CatDao;import com.lanhuigu.demo.repository.CatRepository;@Servicepublic class CatService {@Resourceprivate CatDao catDao;public Cat queryByCatName(String catName) {return catDao.queryByCatName(catName);}}

6）创建一个Controller,比如CatController

package com.lanhuigu.demo.controller;import javax.annotation.Resource;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RestController;import com.lanhuigu.demo.bean.Cat;import com.lanhuigu.demo.service.CatService;@RestController@RequestMapping("/cat")public class CatController {@Resourceprivate CatService catService;// jdbcTemplate查询数据使用@RequestMapping("/queryByCatName")public Cat queryByCatName(String catName) {return catService.queryByCatName(catName);}}

7）测试（启动应用类）

package com.lanhuigu;import org.springframework.boot.SpringApplication;import org.springframework.boot.autoconfigure.SpringBootApplication;import org.springframework.web.servlet.config.annotation.WebMvcConfigurerAdapter;/** * Hello world! * 启动类 */@SpringBootApplicationpublic class App extends WebMvcConfigurerAdapter {    public static void main( String[] args ) {        /*System.out.println( "Hello World!" );*/    SpringApplication.run(App.class, args);    }}

8）访问服务（默认端口为8080）

http://localhost:9000/cat/queryByCatName?catName=tom
浏览器效果: