springboot连接mysql数据库测试

springboot连接mysql数据库

pom文件依赖

<dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.13</version>
        </dependency>

yml文件配置：

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    url: jdbc:mysql://localhost:3306/test?characterEncoding=utf-8&serverTimezone=GMT%2B8
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver
  thymeleaf:
    prefix: classpath:/templates/
    suffix: .html
    encoding: utf-8
server:
  port: 8080
  servlet:
    context-path: /mavendemo1

java测试连接

package com.zzl.demo;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

@Controller
@RequestMapping("jdbc")
public class JdbcController {
    @Resource
    private JdbcTemplate jdbcTemplate;
    @RequestMapping("/userlist")
    @ResponseBody
    public List<User> getUserList(ModelMap map){
        String sql = "SELECT * FROM user";
        List<User> userList = jdbcTemplate.query(sql,new RowMapper<User>(){
            User user = null;
            public User mapRow(ResultSet rs,int rowNum)throws SQLException{
                user =  new User();
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("name"));
                user.setSex(rs.getString("sex"));
                user.setPhone(rs.getInt("phone"));
                return user;
            }});
        System.out.println("Id - 姓名 - 性别 - 手机号");
        for (User user:userList){
            System.out.println(user.getId()+" - "+user.getName()+" - "+user.getSex()+" - "+user.getPhone());
        }
        map.addAttribute("users",userList);
        return userList;

    }

}

实体类

package com.zzl.demo;

public class User {
    private Integer id;
    private String name;
    private String sex;
    private Integer phone;

    public Integer getId() {
        return id;
    }

    public void setId(Integer 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;
    }

    public Integer getPhone() {
        return phone;
    }

    public void setPhone(Integer phone) {
        this.phone = phone;
    }
}