springboot---连接mysql数据库

本文主要是介绍springboot---连接mysql数据库，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

1. 准备工作

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;
   

2. 添加配置文件，可用使用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
   

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

   public class Author {
       private Long id;
       private String realName;
       private String nickName;
   
       // SET和GET方法略
   }
   

2. 方式一：与JdbcTemplate集成

1. 引入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>
   

2. 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();
   }
   

3. 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;
       }
   }
   

4. 通过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("插入成功！");
       }
   }
   

5. 注意，Application类所在的包必须是其他包的父包，@SpringBootApplication这个注解继承了@ComponentScan，其默认情况下只会扫描Application类所在的包及子包，结构图：
   
   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集成

1. 引入依赖

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

2. 实体类
   对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方法略
   }
   

3. 实现类
   需要继承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);
   }
   

4. 测试代码

   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集成

1. 依赖及配置文件

   <!-- 引入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
   

2. 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>
   

3. 测试类代码

   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文件。

这篇关于springboot---连接mysql数据库的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！