用JdbcTemplate方式连接mysql

2021-10-28 09:41:34

首先新建一个springboot工程，然后在pom.xml文件中添加如下的依赖：

<dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

然后在application.properties文件中添加如下：

### mysql连接信息
spring.datasource.url=jdbc:mysql://lukairui.rwlb.rds.aliyuncs.com/test_db1?useSSL=false
### 用户名
spring.datasource.username=flink_admin
### 密码
spring.datasource.password=Sxxxx
### 驱动
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

设计表：

CREATE TABLE `ay_user` (
  `id` varchar(32) NOT NULL COMMENT ‘主键‘,
  `name` varchar(10) DEFAULT NULL COMMENT ‘用户名‘,
  `password` varchar(32) DEFAULT NULL COMMENT ‘密码‘,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

在表中插入数据：

INSERT INTO `test_db1`.`ay_user`(`id`, `name`, `password`) VALUES (‘1‘, ‘阿毅‘, ‘123456‘);
INSERT INTO `test_db1`.`ay_user`(`id`, `name`, `password`) VALUES (‘2‘, ‘阿兰‘, ‘123456‘);

最后，在项目的目录下/src/main/java/com.example.demo.model文件夹下新建一个类AyUser

代码如下：

package com.alibaba.springbootdemo2.model;


/**
 * 描述:用户表
 * @Author lukairui
 * @date 2021/03/16
 */
public class AyUser {
    //主键
    private String id;
    //用户名
    private String name;
    //密码
    private String password;

    public String getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public String getPassword() {
        return password;
    }

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

最后切换到/src/test/java/com/alibaba/springbootdemo2/Springbootdemo2ApplicationTests.java文件中

代码如下：

package com.alibaba.springbootdemo2;

import com.alibaba.springbootdemo2.model.AyUser;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

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

@SpringBootTest
class Springbootdemo2ApplicationTests {

    @Test
    void contextLoads() {
    }

    @Resource
    private JdbcTemplate jdbcTemplate;

    /**
     * MySQL集成SpringBoot简单测试
     */
    @Test
    public void mysqlTest(){
        String sql = "select id,name,password from ay_user";
        List<AyUser> userList = (List<AyUser>) jdbcTemplate.query(sql, new RowMapper<AyUser>() {

            @Override
            public AyUser mapRow(ResultSet rs, int rowNum) throws SQLException {
                AyUser user = new AyUser();
                user.setId(rs.getString("id"));
                user.setName(rs.getString("name"));
                user.setPassword(rs.getString("password"));
                return user;

            }
        });
        System.out.println("查询成功");
        for(AyUser user:userList){
            System.out.println("[id]:"+user.getId()+";[name]:"+user.getName());
        }
    }
}

最后运行测试类：打印如下：

查询成功
[id]:1;[name]:阿毅
[id]:2;[name]:阿兰

-----------------附件------------------

附上所有的pom.xml文件内容

<?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.4.3</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.alibaba</groupId>
    <artifactId>springbootdemo2</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>springbootdemo2</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-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

用JdbcTemplate方式连接mysql

码农公寓

相关文章