springboot与mybatis连接数据库操作

Dear 丶 2022-08-30 04:23 188阅读 0赞

一导入依赖：

<?xml version="1.0" encoding="UTF-8"?>
    <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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
        <modelVersion>4.0.0</modelVersion>
        <parent>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-parent</artifactId>
            <version>2.5.2</version>
            <relativePath/> 
        </parent>
        <groupId>com.yang</groupId>
        <artifactId>spingboot-demo-04</artifactId>
        <version>0.0.1-SNAPSHOT</version>
        <name>spingboot-demo-04</name>
        <description>Demo project for Spring Boot</description>
        <properties>
            <java.version>1.8</java.version>
        </properties>
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-data-jdbc</artifactId>
            </dependency>
    
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
    
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>6.0.6</version>
            </dependency>
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <optional>true</optional>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <scope>test</scope>
            </dependency>
        </dependencies>
    
        <build>
            <plugins>
                <plugin>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-maven-plugin</artifactId>
                    <configuration>
                        <excludes>
                            <exclude>
                                <groupId>org.projectlombok</groupId>
                                <artifactId>lombok</artifactId>
                            </exclude>
                        </excludes>
                    </configuration>
                </plugin>
            </plugins>
        </build>
    
    </project>

2 数据库配置：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NDYzMTc1_size_16_color_FFFFFF_t_70]

spring:  
datasource:  
username: root  
password: mysql  
\#url: jdbc:mysql://locahost:3306/mybatis?useUnicode=true&chracterEncoding=utf-8&useSSL=false&serverTimezone=GMT  
url: jdbc:mysql://localhost:3306/mybatis?useUnicode=true&chracterEncoding=utf-8  
driver-class-name: com.mysql.cj.jdbc.Driver

\#\#下面这两个配置，可以在每次连接的时候判断一些连接是否有效  
\#spring.datasource.druid.test-on-borrow=true  
\#spring.datasource.druid.test-while-idle=true

3 打印一下信息：

package com.yang.spingbootdemo04;
    
    import org.junit.jupiter.api.Test;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.boot.test.context.SpringBootTest;
    
    import javax.sql.DataSource;
    import java.sql.Connection;
    import java.sql.SQLException;
    
    @SpringBootTest
    class SpingbootDemo04ApplicationTests { 
    
        @Autowired
        DataSource dataSource;
    
        @Test
        void contextLoads() throws SQLException { 
            //查看一下默认的数据源
            System.out.println(dataSource.getClass());
    
    
            //获取连接
            Connection connection = dataSource.getConnection();
            System.out.println(connection);
    
            //关闭数据源
            connection.close();
        }
    
    }

4 写一个简单的controller

package com.yang.spingbootdemo04.controller;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.PathVariable;
    import org.springframework.web.bind.annotation.RestController;
    
    import java.util.List;
    import java.util.Map;
    
    @RestController
    public class JDBCController { 
        @Autowired
        JdbcTemplate jdbcTemplate;
    
        //查询数据库的所以信息
        //现在没有实体类
    @GetMapping("/userList")
        public List<Map<String,Object>> userList(){ 
            String sql = "select * from user";
        System.out.println("=============================");
            List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
            return  maps;
        }
    
        @GetMapping("/addUser")
        public String addUser(){ 
        String sql = "insert into user(id,name,pwd) values(101,'yang','xxxxxxxxxxx')";
            jdbcTemplate.update(sql);
            String s = "update-ok";
            return s;
        }
    
        @GetMapping("/updateUser/{id}")
        public String updateUser(@PathVariable("id") int id){ 
            String sql = "update user set name = ?, pwd=? where id= "+id;
    //封装
            Object[] objects = new Object[2];
            objects[0] = "小明2";
            objects[1] = "zxxxxxxxxxxxx";
    
            jdbcTemplate.update(sql,objects);
            String s = "update-ok";
            return s;
        }
    
        @GetMapping("/deleteUser/{id}")
        public String deleteUser(@PathVariable("id") int id){ 
            //String sql = "insert into user(id,name,pwd) values()";
            String sql = "delete from user where id = 1";
            jdbcTemplate.update(sql);
            String s = "deleteUser-ok";
            return s;
        }
    
    }

package com.yang.spingbootdemo04.controller;

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

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

@RestController  
public class JDBCController \{  
@Autowired  
JdbcTemplate jdbcTemplate;

//查询数据库的所以信息
    //现在没有实体类

@GetMapping("/userList")  
public List<Map<String,Object>> userList()\{  
String sql = “select \* from user”;  
System.out.println("=============================");  
List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);  
return maps;  
\}

@GetMapping("/addUser")
    public String addUser(){
    String sql = "insert into user(id,name,pwd) values(101,'yang','xxxxxxxxxxx')";
        jdbcTemplate.update(sql);
        String s = "update-ok";
        return s;
    }
    
    @GetMapping("/updateUser/{id}")
    public String updateUser(@PathVariable("id") int id){
        String sql = "update user set name = ?, pwd=? where id= "+id;

//封装  
Object\[\] objects = new Object\[2\];  
objects\[0\] = “小明2”;  
objects\[1\] = “zxxxxxxxxxxxx”;

jdbcTemplate.update(sql,objects);
        String s = "update-ok";
        return s;
    }
    
    @GetMapping("/deleteUser/{id}")
    public String deleteUser(@PathVariable("id") int id){
        //String sql = "insert into user(id,name,pwd) values()";
        String sql = "delete from user where id = 1";
        jdbcTemplate.update(sql);
        String s = "deleteUser-ok";
        return s;
    }

5 结果：  
查询  
![在这里插入图片描述][20210719093213182.png]  
删除：  
![在这里插入图片描述][2021071909325259.png]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NDYzMTc1_size_16_color_FFFFFF_t_70]: /images/20220829/52afe82b168c4cae905c922ebdec4407.png
[20210719093213182.png]: /images/20220829/7fd69ec7a8b5468f934a3a65ab9b82a9.png
[2021071909325259.png]: /images/20220829/27915b9f56ae45579b3acbcabff65b6c.png