springboot---连接mysql数据库

2022-12-24 07:57:02

1. 准备工作

建一个简单的数据库，名为springboot_db，在其下建一个表，名为t_author，脚本如下：

CREATE DATABASE /*!32312 IF NOT EXISTS*/`springboot_db` /*!40100 DEFAULT CHARACTER SET utf8 */;
 
USE `springboot_db`;
 
DROP TABLE IF EXISTS `t_author`;
 
CREATE TABLE `t_author` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `real_name` varchar(32) NOT NULL COMMENT '用户名称',
  `nick_name` varchar(32) NOT NULL COMMENT '用户匿名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

添加配置文件，可用使用yaml配置，即application.yml（与application.properties配置文件，没什么太大的区别）连接池的配置如下：

spring:
  datasource:
    url: jdbc:mysql://127.0.0.1:3306/springboot_db?useUnicode=true&characterEncoding=UTF-8&useSSL=false
    driverClassName: com.mysql.jdbc.Driver
    username: root
    password: root
    type: com.alibaba.druid.pool.DruidDataSource

建立与数据库对应的pojo类：

public class Author {
    private Long id;
    private String realName;
    private String nickName;

    // SET和GET方法略
}

2. 方式一：与JdbcTemplate集成

引入jdbc的依赖

通过JdbcTemplate来访问数据库，Spring boot提供了如下的starter来支撑：

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

再引入Junit测试Starter:

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

DAO接口类

package com.guxf.dao;

import java.util.List;

import com.guxf.domain.Author;

public interface AuthorDao {

    int add(Author author);

    int update(Author author);

    int delete(Long id);

    Author findAuthor(Long id);

    List<Author> findAuthorList();
}

DAO的实现类

package com.guxf.impl;

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

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import com.guxf.dao.AuthorDao;
import com.guxf.domain.Author;

@Repository
public class AuthorDaoJdbcTemplateImpl implements AuthorDao{
    
    @Autowired
    private NamedParameterJdbcTemplate jdbcTemplate;
    
    @Override
    public int add(Author author) {
        String sql = "insert into t_author(id,real_name,nick_name) " +
                "values(:id,:realName,:nickName)";
        Map<String, Object> param = new HashMap<>();
        param.put("id",author.getId());
        param.put("realName", author.getRealName());
        param.put("nickName", author.getNickName());
        
        return (int) jdbcTemplate.update(sql, param);
    }

    @Override
    public int update(Author author) {  
        return 0;
    }

    @Override
    public int delete(Long id) {    
        return 0;
    }

    @Override
    public Author findAuthor(Long id) {
        return null;
    }

    @Override
    public List<Author> findAuthorList() {  
          return null;
    }
}

通过JUnit来测试上面的代码(需根据自己的实际Application名稍作修改)：

package com.guxf.boot;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.guxf.BootApplication;
import com.guxf.dao.AuthorDao;
import com.guxf.domain.Author;

@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest(classes = BootApplication.class)
public class AuthorDaoTest {

    @Autowired
    private AuthorDao authorDao;

    @Test
    public void testInsert() {
        Author author = new Author();
        author.setId(1L);
        author.setRealName("莫言");
        author.setNickName("疯子");
        
        authorDao.add(author);
        System.out.println("插入成功！");
    }
}

注意，Application类所在的包必须是其他包的父包，@SpringBootApplication这个注解继承了@ComponentScan，其默认情况下只会扫描Application类所在的包及子包，结构图：
springboot---连接mysql数据库
Application代码示例：

	package com.guxf;
	
	import org.springframework.boot.SpringApplication;
	import org.springframework.boot.autoconfigure.SpringBootApplication;
	
	@SpringBootApplication
	public class BootApplication {
	
	    public static void main(String[] args) {
	        SpringApplication.run(BootApplication.class, args);
	    }
	}

3. 方式二：与JPA集成

引入依赖

<!-- 引入JPA -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

实体类
对POJO类增加Entity的注解，并指定表名（如果不指定，默认的表名为author），然后指定ID的及其生成策略，这些都是JPA的知识，与Spring boot无关，代码：

package com.guxf.domain;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

@Entity(name = "t_author")
public class Author {
    @Id
    @GeneratedValue
    private Long id;
    private String realName;
    private String nickName;

    // SET和GET方法略
}

实现类
需要继承JpaRepository这个类，这里我们实现了两个查询方法，第一个是符合JPA命名规范的查询，JPA会自动帮我们完成查询语句的生成，另一种方式是我们自己实现JPQL（JPA支持的一种类SQL的查询）：

package com.guxf.service;

import java.util.List;
import java.util.Optional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.guxf.domain.Author;

public interface AuthorRepository extends JpaRepository<Author, Long> {

    public Optional<Author> findById(Long userId);

    @Query("select au from com.guxf.domain.Author au where nick_name=:nickName")
    public List<Author> queryByNickName(@Param("nickName") String nickName);
}

测试代码

package com.guxf.boot;

import static org.junit.Assert.*;

import java.util.List;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.guxf.BootApplication;
import com.guxf.domain.Author;
import com.guxf.service.AuthorRepository;

@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest(classes = BootApplication.class)
public class AuthorDaoTestJPA {

    @Autowired
    private AuthorRepository authorRepository;

    @Test
    public void testQuery() {
        List<Author> authorList = authorRepository.queryByNickName("疯子");
        assertTrue(authorList.size() > 0);
        System.out.println("成功！");
    }
}

4. 方式三：与MyBatis集成

依赖及配置文件

<!-- 引入Mybatis -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.1.1</version>
        </dependency>

MyBatis一般可以通过XML或者注解的方式来指定操作数据库的SQL，首先，我们需要配置mapper的目录。我们在application.yml中进行配置：

spring:
  datasource:
    url: jdbc:mysql://127.0.0.1:3306/springboot_db?useUnicode=true&characterEncoding=UTF-8&useSSL=false
    driverClassName: com.mysql.jdbc.Driver
    username: root
    password: root
    type: com.alibaba.druid.pool.DruidDataSource

mybatis:
  #config-locations: mybatis/mybatis-config.xml
  mapper-locations: com/guxf/mapper/*.xml
  type-aliases-package: com.guxf.mapper.AuthorMapper

mapper接口和XML文件

package com.guxf.mapper;

import org.apache.ibatis.annotations.Mapper;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.guxf.domain.Author;
@Mapper
public interface AuthorMapper extends BaseMapper<Author> {

    public Long insertAuthor(Author author);

    public void updateAuthor(Author author);

    public Author queryById(Long id);
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.guxf.mapper.AuthorMapper">
    <!-- 此处需要注意的是，由于我们数据库定义的id存储类型为intbig,但是我们的Entity中Id是Long -->
    <!-- 前面的两种方式插入没问题，此处报了数据库类型异常 -->
    <!-- 所以数据库的ID类型改为了Varchar -->
    <resultMap id="authorMap" type="com.guxf.domain.Author">
        <id column="id" property="id" jdbcType="VARCHAR" />
        <result column="real_name" property="realName" jdbcType="VARCHAR" />
        <result column="nick_name" property="nickName" jdbcType="VARCHAR" />
    </resultMap>

    <sql id="base_column">
        id,real_name,nick_name
    </sql>

    <insert id="insertAuthor" parameterType="com.guxf.domain.Author">
        INSERT INTO
        t_author(
        <include refid="base_column" />
        )
        VALUE
        (#{id},#{realName},#{nickName})
    </insert>

    <update id="updateAuthor" parameterType="com.guxf.domain.Author">
        UPDATE t_author
        <set>
            <if test="realName != null">
                real_name = #{realName},
            </if>
            <if test="nickName != null">
                nick_name = #{nickName},
            </if>
        </set>
        WHERE id = #{id}
    </update>

    <select id="queryById" parameterType="Long" resultMap="authorMap">
        SELECT id,
        <include refid="base_column"></include>
        FROM t_author
        WHERE id = #{id}
    </select>

</mapper>

测试类代码

package com.guxf;

import static org.junit.Assert.*;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.guxf.BootApplication;
import com.guxf.domain.Author;
import com.guxf.mapper.AuthorMapper;

@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest(classes = BootApplication.class)
public class AuthorDaoTestMybatis {

    @Autowired
    private AuthorMapper mapper;

    @Test
    public void testInsert() {
        Author author = new Author();
        author.setId(4L);
        author.setRealName("唐钰");
        author.setNickName("小宝");
        mapper.insertAuthor(author);
        System.out.println("成功!");
    }

    @Test
    public void testMybatisQuery() {
        Author author = mapper.queryById(1L);
        assertNotNull(author);
        System.out.println(author);
    }

    @Test
    public void testUpdate() {
        Author author = mapper.queryById(2L);
        author.setNickName("月儿");
        author.setRealName("林月如");
        mapper.updateAuthor(author);
    }
}

5. 方式四：与mybatis-plus集成

其实mybatis和mybatis-plus同出一脉，就像是spring和springboot的关系，不是创新，而是优化，mybatis-plus中最重要的就是，BaseMapper和IService这两个接口，其实IService的接口调用的还是BaseMapper，最终使用后者进行和数据库的连接操作。
基本上一般的数据库操作，直接写在service中即可，不需要再写繁琐的XML文件。

码农公寓

1. 准备工作

2. 方式一：与JdbcTemplate集成

3. 方式二：与JPA集成

4. 方式三：与MyBatis集成

5. 方式四：与mybatis-plus集成

相关文章