springboot连接mysql数据库，JdbcTemplate和spring JPA方式

SQL部分

CREATE TABLE test(
  id int(10) primary key,
  name varchar(50) not null,
  age int(10),
  address varchar(50)
);

insert into test values(1,'zs',20,'bj');
insert into test values(2,'ls',20,'sh');
insert into test values(3,'ww',20,'bj');

 

JdbcTemplate方式：

1.application.properties

spring.datasource.url=jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

server.port=8080
server.tomcat.uri-encoding=UTF-8

2.pom.xml

        <!--mysql依赖-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>

　　　　　　　<version>8.0.11</version>

        </dependency>
        <!--jdbc依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

 

pom.xml中有关artifactId找不到
1).file -> settings -> 搜索maven -> always update snapshots 打钩 -> OK
2).右下角选择 import...

 

3.JdbcDbController

package com.example.demo;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;
import java.util.Map;

@RestController
public class JdbcDbController {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @RequestMapping("/test")
    public String test(){
        StringBuffer str = new StringBuffer();
        String sql = "select * from test";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        for(Map<String, Object> map : list){
            String name = map.get("name").toString();
            str.append(name+" ");
            System.out.println(name);
        }
        return str.toString();
    }
}

访问地址

http://localhost:8080/test

 

 

 

spring JPA方式

1.application.properties同上

2.pom.xml增加JPA依赖

        <!--mysql依赖-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <!--jdbc依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <!--jpa依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

3.Test类

package com.example.demo;

import javax.persistence.Entity;
import javax.persistence.Id;

@Entity //不加会报错 Invocation of init method failed
public class Test {
    @Id //不加会报错 No identifier specified for entity
    private int id;
    private String name;
    private int age;
    private String address;

    public int getId() { return id; }

    public void setId(int id) { this.id = id; }

    public String getName() {
        return name;
    }

    public void setName(String name) { this.name = name; }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }
}

4.TestRepository 接口

package com.example.demo;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;

import java.util.List;

public interface TestRepository extends JpaRepository<Test, Integer>{

    //根据name查询
    List<Test> findByName(String name);

    //根据name和age查询
    List<Test> findByNameAndAge(String name,Integer age);

    //根据id查询
    Test findById(Integer id);

    //分页
    Page<Test> findAll(Pageable pageable);
}

5.JpaDbController

package com.example.demo;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class JpaDbController {

    @Autowired
    TestRepository testRepository;

    @RequestMapping("/findByNameAndAge")
    //根据name和age查询
    public String findByNameAndAge(){
        StringBuffer str = new StringBuffer();

        List<Test> list = testRepository.findByNameAndAge("zs",20);
        for(Test test : list){
            String name = test.getName();
            System.out.println(name);
            str.append(name+" ");
        }
        return str.toString();
    }

    @RequestMapping("/page")
    //第0页开始，每页1条数据
    public Page<Test> page(@RequestParam(value = "page", defaultValue = "0") Integer page,
                           @RequestParam(value = "size", defaultValue = "1") Integer pageSize){

        Pageable pageable = new PageRequest(page, pageSize);

        //根据id正序排序
        //Pageable pageable = new PageRequest(page, pageSize, Sort.Direction.ASC, "id");

        return testRepository .findAll(pageable);

        //浏览器访问地址，参数可加可不加
        //http://localhost:8080/page
        //http://localhost:8080/page?page=1&pageSize=1
        //默认页面显示内容{"content":[{"id":1,"name":"zs","age":20,"address":"bj"}],
        //"last":false,"totalPages":3,"totalElements":3,"number":0,"size":1,"sort":null,"first":true,"numberOfElements":1}
    }

}

 

 

mysql jdbc下载，官网https://dev.mysql.com/downloads/connector/j/

Connector/J
Select Operating System:
windows系统选择 platform independent
-> Platform Independent (Architecture Independent), ZIP Archive
-> download
-> No thanks, just start my download.

解压，找到 mysql-connector-java-8.0.15.jar

 

IDEA项目中引入JAR包
File -> Project Structure -> Modules -> 选中项目，例如demo
-> 3个选项卡中选择 Dependencies -> + JARs or Directories
-> 选中要导入的jar -> OK

转载于:https://www.cnblogs.com/songfei90/p/10646906.html