spring boot 开发（五）mariadb mysql

创建表

heidisql建表

CREATE TABLE myjdbctest (
id INT(8) NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NULL DEFAULT NULL,
sex VARCHAR(2) NULL DEFAULT NULL,
PRIMARY KEY (id)
)
ENGINE=InnoDB
;

添加自增，且需要设置为主键，heidisql 右键–创建新索引–PRIMARY

添加pom

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

    <dependency>
      <groupId>org.mariadb.jdbc</groupId>
      <artifactId>mariadb-java-client</artifactId>
    </dependency>
<!--数据源依赖-->
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid</artifactId>
      <version>1.1.12</version>
    </dependency>

配置数据源

通过自定义配置文件配置数据源

通过@propertySource注解读取配置文件

spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.url=jdbc:mariadb://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
spring.datasource.driver-class-name=org.mariadb.jdbc.Driver
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource

配置文件数据库信息

创建myConfigure.properties

jdbc.userName=root
jdbc.password=root
jdbc.url=jdbc:mariadb://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false
jdbc.driverClassName=com.mysql.jdbc.Driver

创建配置类

package org.example.config;


import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;

import javax.sql.DataSource;

@Configuration
@PropertySource("classpath:/myConfiguration.properties")//加载指定的配置文件
//@ConfigurationProperties(prefix = "jdbc")//spring boot的注解，不能读取其他的配置文件，只能读取spring boot的aplication
public class JdbcConfiguration {

    @Value("${jdbc.userName}")
    private String userName;
    @Value("${jdbc.password}")
    private String password;

    @Value("${jdbc.url}")
    private String url;
    @Value("${jdbc.driverClassName}")
    private String driverClassName;

    /**
     * 实例化Druid
     * @return
     */
    @Bean
    public DataSource getDataSource(){
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUsername(userName);
        dataSource.setPassword(password);
        dataSource.setUrl(url);
        dataSource.setDriverClassName(driverClassName);
        return dataSource;
    }



}

创建controller测试

package org.example.controller;

import org.example.bean.Demo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.sql.DataSource;


//import javax.sql.DataSource;

@Controller
public class HelloController2 {


    @Autowired
    private DataSource dataSource;

    @GetMapping("/showinfo")//spring 2.0新的注解
    public String showInfo(){

        return "OK";
    }

}

debuge查看bean是否已经加载
访问：http://localhost:8080/showinfo

通过@ConfigurationProperties配置文件

spring boot的注解，只能读取spring boot的application配置文件，不能读取其他的配置文件

创建实体类

package org.example.config;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;

/**
 * jdbc配置属性类
 */
@ConfigurationProperties(prefix = "jdbc")
public class JdbcProperties {
    public String getUserName() {
        return userName;
    }

    public String getPassword() {
        return password;
    }

    public String getUrl() {
        return url;
    }

    public String getDriverClassName() {
        return driverClassName;
    }

    private String userName;

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public void setDriverClassName(String driverClassName) {
        this.driverClassName = driverClassName;
    }

    private String password;

    private String url;
    private String driverClassName;


}

调整controller

package org.example.config;


import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;

import javax.sql.DataSource;

@Configuration
//@PropertySource("classpath:/myConfiguration.properties")//加载指定的配置文件
//@ConfigurationProperties(prefix = "jdbc")//spring boot的注解，不能读取其他的配置文件，只能读取spring boot的aplication
@EnableConfigurationProperties(JdbcProperties.class)
public class JdbcConfiguration {

//    @Value("${jdbc.userName}")
//    private String userName;
//    @Value("${jdbc.password}")
//    private String password;
//
//    @Value("${jdbc.url}")
//    private String url;
//    @Value("${jdbc.driverClassName}")
//    private String driverClassName;

    @Autowired
    private JdbcProperties jdbcProperties;
    /**
     * 实例化Druid
     * @return
     */
    @Bean
    public DataSource getDataSource(){
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUsername(this.jdbcProperties.getUserName());
        dataSource.setPassword(this.jdbcProperties.getPassword());
        dataSource.setUrl(this.jdbcProperties.getUrl());
        dataSource.setDriverClassName(this.jdbcProperties.getDriverClassName());
        return dataSource;
    }



}

同样方式，debug，访问，也可以看到DataSource已经获取信息

@ConfigurationProperties(prefix = “jdbc”)也可以注解在方法上

添加用户

1、创建pojo

package org.example.bean;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
//
//import javax.persistence.Entity;
//import javax.persistence.GeneratedValue;
//import javax.persistence.Id;

//@Entity
@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class Demo {

//    @Id@GeneratedValue
    private int id;

    private String name;

    private String sex;
}

创建页面

创建html

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
        "http://www.w3.org/TR/html4/loose.dtd">
<html xmlns:th="http://www.thymeleaf.org">
<head>
    <title>你好</title>
</head>
<body>

<form th:action="@{/user/addUser}" method="post">
    <input type="text" name="name"><br/>
    <input type ="text" name="sex"><br/>
    <input type="submit" value="OK"/>
</form>
</body>
</html>

创建controller

package org.example.controller;

import org.example.bean.Demo;
//import org.example.service.DemoService;
import org.example.service.DemoService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;


@Controller
@RequestMapping("/user")
public class Demo2Controller {
    @Autowired
    private DemoService demoService;

    @PostMapping("/addUser")
    public String save(Demo demo){
        try {

            this.demoService.addUser(demo);
        }catch (Exception e0){
            return "error";
        }
        return "redict:/sucess";
    }

    /**
     * 查询全部用户
     */
    @GetMapping("/findUserAll")
    public String findUserAll(Model model){
        List<Demo> list = null;
        try {

            list = this.demoService.findUserAll();
            model.addAttribute("list",list);
        }catch (Exception e0){
            System.out.println(list);
            e0.printStackTrace();
            return "error";
        }
        return "showUsers";
    }
}

持久层

package org.example.dao.impl;

import org.example.bean.Demo;
import org.example.dao.DemoDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class DemoDaoImpl implements DemoDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public void insertUser(Demo demo) {
        String sql = "insert into myjdbctest(name,sex) values(?,?)";
        this.jdbcTemplate.update(sql,demo.getName(),demo.getSex());

    }

    @Override
    public List<Demo> findUserAll() {
        String sql = "select * from myjdbctest";

        return this.jdbcTemplate.query(sql, new RowMapper<Demo>() {
            /**
             * 做结果集的映射
             * @param resultSet
             * @param i
             * @return
             * @throws SQLException
             */
            @Override
            public Demo mapRow(ResultSet resultSet, int i) throws SQLException {
                Demo demo = new Demo();
                demo.setId(resultSet.getInt("id"));
                demo.setName(resultSet.getString("name"));
                demo.setSex(resultSet.getString("sex"));
                return demo;
            }
        });
    }
}

服务层

package org.example.service.impl;

import org.example.bean.Demo;
import org.example.dao.DemoDao;
import org.example.service.DemoService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class DemoServiceImpl implements DemoService {


    @Autowired
    private DemoDao demoDao;

    @Override
    @Transactional
    public void addUser(Demo demo) {

        this.demoDao.insertUser(demo);
    }
    @Override
    public List<Demo> findUserAll() {
        return this.demoDao.findUserAll();
    }
}

测试

访问localhost:8080/addUser.html

查询所有用户

showUser.html

</table>

id	姓名	性别	操作
			修改 删除

# 错误 ## pom引入spring-boot-starter-jdbc配置，但是工程中没有关于dataSource相关的配置信息，当spring创建dataSource bean因缺少相关的信息就会报错。 Action:

Consider the following:
If you want an embedded database (H2, HSQL or Derby), please put it on the classpath.
If you have database settings to be loaded from a particular profile you may need to activate it (no profiles are currently active).

@Controller @RestController