springboot学习笔记(二):基于MySql数据库的JDBC操作

时间：2022-10-29 15:42:07

1 , 连接数据库

SpringBoot的Web应用中，基于MySql数据库的JDBC操作

  • JDBC 连接数据库主要配置

1 , 属性配置文件（application.properties）

spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

JNDI配置项

spring.datasource.jndi-name= # JNDI location of the datasource. Class, url, username & password are ignored when set.

POM配置Maven依赖

 <!-- MYSQL -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!-- Spring Boot JDBC -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
  • 实例

1 , 添加依赖

<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/maven-v4_0_0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>Demo</groupId>
<artifactId>SpringBoot</artifactId>
<packaging>war</packaging>
<version>0.0.1-SNAPSHOT</version>
<name>SpringBoot Maven Webapp</name>
<url>http://maven.apache.org</url>

<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>1.4.0.RELEASE</version>
</parent>

<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<java.version>1.7</java.version>
</properties>

<dependencies>

<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>

<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>

<!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-logging -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-logging</artifactId>
</dependency>

<!-- MYSQL -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!-- Spring Boot JDBC -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

</dependencies>

<build>
<finalName>SpringBoot</finalName>

<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>2.0.2</version>
<configuration>
<source>1.7</source>
<target>1.7</target>
</configuration>
</plugin>
</plugins>

</build>
</project>

2 , 实体类

package com.springboot.jdbc.entity;

/**
 * Created by nickzhang on 2016/9/1.
 */
public class UserInfo {

private String name;
private String age;
private String address;


public String getName() {
return name;
    }

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

public String getAge() {
return age;
    }

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

public String getAddress() {
return address;
    }

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

@Override
public String toString() {
return "UserInfo{" +
"name='" + name + '\'' +
", age='" + age + '\'' +
", address='" + address + '\'' +
'}';
    }
}

3 , Service

package com.springboot.jdbc.Service;

import com.springboot.jdbc.entity.UserInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * Created by nickzhang on 2016/9/1.
 */
@Service
public class UserInfoService {

@Autowired
private JdbcTemplate jdbcTemplate;


public List<UserInfo> query(){
        String sql = "select name,age,address from tb_userinfo";

        List<UserInfo> userinfoList = jdbcTemplate.query(sql, new RowMapper<UserInfo>() {
@Override
public UserInfo mapRow(ResultSet resultSet, int i) throws SQLException {
                UserInfo userinfo = new UserInfo();
                userinfo.setName(resultSet.getString("name"));
                userinfo.setAge(resultSet.getString("age"));
                userinfo.setAddress(resultSet.getString("address"));
return userinfo;
            }
        });

return userinfoList;
    }

}

4 , Controller

package com.springboot.jdbc;

import com.springboot.jdbc.Service.UserInfoService;
import com.springboot.jdbc.entity.UserInfo;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;

/**
 * Created by nickzhang on 2016/9/1.
 */
@RestController
@SpringBootApplication
public class SpringBootJDBCTest {

    private static final Logger logger = LoggerFactory.getLogger(SpringBootJDBCTest.class);

    @Autowired
    private UserInfoService userInfoService;

    @RequestMapping("/query")
    public List<UserInfo> query(){
        logger.info(">>>>从数据库查询userinfo数据>>>>");
        return userInfoService.query();
    }

    public static void main(String[] args) {
        SpringApplication.run(SpringBootJDBCTest.class,args);
    }
}

5 , application.properties配置信息

server.port=8011
spring.datasource.url=jdbc:mysql://localhost:3306/springbootdb
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

spring.datasource.max-idle=10
spring.datasource.max-wait=10000
spring.datasource.min-idle=5
spring.datasource.initial-size=5
spring.datasource.validation-query=SELECT 1
spring.datasource.test-on-borrow=false
spring.datasource.test-while-idle=true
spring.datasource.time-between-eviction-runs-millis=18800
spring.datasource.jdbc-interceptors=ConnectionState;SlowQueryReport(threshold=0)

目录结构

springboot学习笔记(二):基于MySql数据库的JDBC操作

6 , 数据库表

CREATE TABLE `tb_userinfo` (
`name` varchar(255) NOT NULL,
`age` varchar(255) NOT NULL,
`address` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

7 , logback.xml日志配置信息

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
<include resource="org/springframework/boot/logging/logback/base.xml"/>
<logger name="org.springframework.boot" level="DEBUG"/>
</configuration>

剩下的就是运行服务 , 浏览器访问http://localhost:8011/query

实测返回结果为:

[
    {
        "name": "张三",
        "age": "27",
        "address": "广东深圳"
},
    {
        "name": "李四",
        "age": "25",
        "address": "湖北武汉"
},
    {
        "name": "王五",
        "age": "29",
        "address": "首都北京"
}
]
标签：

相关文章