Spring Boot 连接MySql数据库

Spring Boot 以后也许会成为入门Spring的首选!

记一下Spring Boot 成功连接Mysql数据库的方法步骤！

一、新建Maven工程，不全Maven所需文件夹，在pom.xml引入SpringBoot的依赖包！可以参照：http://www.cnblogs.com/liangblog/p/5207855.html

二、有两种方法与数据库建立连接，一种是集成Mybatis，另一种用JdbcTemplate

(1)、用JdbcTemplate

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

　添加配置文件配置数据库和其他参数

　　在resource文件夹下添加application.properties配置文件并输入数据库参数，如下：

　新建Controller类测试数据库连接

package com.lgp.SpringBoot;

import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

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

@RestController
@RequestMapping("/mydb")
public class DbController {

    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    @RequestMapping("/getUsers")
    public List<Map<String, Object>> getDbType(){
        String sql = "select * from appuser";
        List<Map<String, Object>> list =  jdbcTemplate.queryForList(sql);
        for (Map<String, Object> map : list) {
            Set<Entry<String, Object>> entries = map.entrySet( );
                if(entries != null) {
                    Iterator<Entry<String, Object>> iterator = entries.iterator( );
                    while(iterator.hasNext( )) {
                    Entry<String, Object> entry =(Entry<String, Object>) iterator.next( );
                    Object key = entry.getKey( );
                    Object value = entry.getValue();
                    System.out.println(key+":"+value);
                }
            }
        }
        return list;
    }
    
    @RequestMapping("/user/{id}")
    public Map<String,Object> getUser(@PathVariable String id){
        Map<String,Object> map = null;
        
        List<Map<String, Object>> list = getDbType();
        
        for (Map<String, Object> dbmap : list) {
            
            Set<String> set = dbmap.keySet();
            
            for (String key : set) {
                if(key.equals("id")){    
                    if(dbmap.get(key).equals(id)){
                        map = dbmap;
                    }
                }
            }
        }
        
        if(map==null)
            map = list.get(0);
        return map;
    }
    
}

运行App 输入地址输出数据库数据。。。。。。

2)、集成Mybatis

添加mybatis依赖

 <dependency>
                <groupId>org.mybatis.spring.boot</groupId>
                <artifactId>mybatis-spring-boot-starter</artifactId>
                <version>1.0.0</version>
        </dependency>

版本号可能有更新！

在配置文件中添加配置信息：

spring.datasource.url=jdbc:mysql://127.0.0.1:3306/test
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.max-idle=10
spring.datasource.max-wait=10000
spring.datasource.min-idle=5
spring.datasource.initial-size=5

server.port=8011
server.session.timeout=10
server.tomcat.uri-encoding=UTF-8


# mybatis.config= classpath:mybatis-config.xml
mybatis.mapperLocations=classpath:mappers/*.xml
# domain object's package 
mybatis.typeAliasesPackage=com.lgp.SpringBoot.bean
# handler's package
# mybatis.typeHandlersPackage=
# check the mybatis configuration exists
# mybatis.check-config-location= 
# mode of execution. Default is SIMPLE
# mybatis.executorType=

依次添加mapper的接口类和xml文件

主要代码：

package com.lgp.SpringBoot.mapper;

import java.util.List;

import com.lgp.SpringBoot.bean.AppMessage;

public interface AppMessageMapper {

    int deleteByPrimaryKey(String id);

    int insert(AppMessage record);

    int insertSelective(AppMessage record);

    AppMessage selectByPrimaryKey(String id);

    int updateByPrimaryKeySelective(AppMessage record);

    int updateByPrimaryKey(AppMessage record);
    
    List<AppMessage> selectAll();

    List<AppMessage> getMessById(String 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.lgp.SpringBoot.mapper.AppMessageMapper" >

  <resultMap id="BaseResultMap" type="com.lgp.SpringBoot.bean.AppMessage" >
    <id column="id" property="id" jdbcType="VARCHAR" />
    <result column="message" property="message" jdbcType="VARCHAR" />
    <result column="senddate" property="senddate" jdbcType="TIMESTAMP" />
  </resultMap>
  
  <sql id="Base_Column_List" >
    id, message, senddate
  </sql>
  <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.String" >
    select 
    <include refid="Base_Column_List" />
    from appuser_message
    where id = #{id,jdbcType=VARCHAR}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.String" >
    delete from appuser_message
    where id = #{id,jdbcType=VARCHAR}
  </delete>
  <insert id="insert" parameterType="com.lgp.SpringBoot.bean.AppMessage" >
    insert into appuser_message (id, message, senddate
      )
    values (#{id,jdbcType=VARCHAR}, #{message,jdbcType=VARCHAR}, #{senddate,jdbcType=TIMESTAMP}
      )
  </insert>
  <insert id="insertSelective" parameterType="com.lgp.SpringBoot.bean.AppMessage" >
    insert into appuser_message
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        id,
      </if>
      <if test="message != null" >
        message,
      </if>
      <if test="senddate != null" >
        senddate,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        #{id,jdbcType=VARCHAR},
      </if>
      <if test="message != null" >
        #{message,jdbcType=VARCHAR},
      </if>
      <if test="senddate != null" >
        #{senddate,jdbcType=TIMESTAMP},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.lgp.SpringBoot.bean.AppMessage" >
    update appuser_message
    <set >
      <if test="message != null" >
        message = #{message,jdbcType=VARCHAR},
      </if>
      <if test="senddate != null" >
        senddate = #{senddate,jdbcType=TIMESTAMP},
      </if>
    </set>
    where id = #{id,jdbcType=VARCHAR}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.lgp.SpringBoot.bean.AppMessage" >
    update appuser_message
    set message = #{message,jdbcType=VARCHAR},
      senddate = #{senddate,jdbcType=TIMESTAMP}
    where id = #{id,jdbcType=VARCHAR}
  </update>
  
  <select id="selectAll" resultMap="BaseResultMap">
    select 
         id, message, senddate
    from appuser_message
    order by senddate asc
  </select>
  
  <select id="getMessById" resultMap="BaseResultMap" parameterType="java.lang.String">
   select 
           id, message, senddate
   from 
        appuser_message  
       where id = #{id,jdbcType=VARCHAR}
    order by senddate asc  
  </select>
  
</mapper>

package com.lgp.SpringBoot.bean;

import java.util.Date;

public class AppMessage {
    private String id;

    private String message;

    private Date senddate;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id == null ? null : id.trim();
    }

    public String getMessage() {
        return message;
    }

    public void setMessage(String message) {
        this.message = message == null ? null : message.trim();
    }

    public Date getSenddate() {
        return senddate;
    }

    public void setSenddate(Date senddate) {
        this.senddate = senddate;
    }
}

package com.lgp.SpringBoot.service;

import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.lgp.SpringBoot.bean.AppMessage;
import com.lgp.SpringBoot.mapper.AppMessageMapper;

@Service
public class AppMessageService {
    
    @Autowired
    private AppMessageMapper mapper;
    
    public List<AppMessage> getMessage(){
         List<AppMessage> list = new ArrayList<AppMessage>();
         list.add(mapper.selectByPrimaryKey("xtt"));
         //list = mapper.selectAll();
         return list;
    }
    
    public List<AppMessage> getAllMessage(){
         List<AppMessage> list = new ArrayList<AppMessage>();
         list = mapper.selectAll();
         return list;
    }

    public int addMessage(AppMessage appMessage) {
        return mapper.insert(appMessage);
    }

    public List<AppMessage> getMessageById(String id) {
        return mapper.getMessById(id);
    }

    public int delMessage(String id) {
        return mapper.deleteByPrimaryKey(id);
    }
    
    
    
}

package com.lgp.SpringBoot.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import com.lgp.SpringBoot.bean.AppMessage;
import com.lgp.SpringBoot.service.AppMessageService;

@RestController
@RequestMapping("/appmessage")
public class APPMessageController {

    @Autowired
    private AppMessageService service;

    @RequestMapping("/getThree")
    public List<AppMessage> getThreeForMessage(){
        
        List<AppMessage> list = service.getMessage();        
        return list;
    }
    
    @RequestMapping("/getAll")
    public List<AppMessage> getAllMessage(){
        
        List<AppMessage> list = service.getAllMessage();
        int num = list.size();
        if(null!=list && num>3){
            for (int i = 0; i < num-3; i++) {
                list.remove(0);
            }
        }
        return list;
    }

    @RequestMapping("/getByID")
    public List<AppMessage> getMessageById(@RequestParam("id") String id){
        List<AppMessage> list = service.getMessageById(id);
        int num = list.size();
        if(null!=list && num>5){
            for (int i = 0; i < num-5; i++) {
                list.remove(0);
            }
        }
        return list;
    }
    
    @RequestMapping(value = "/add",method = RequestMethod.POST)
    public int addMessage(@RequestBody AppMessage appMessage){
        return service.addMessage(appMessage);
    }
    
    @RequestMapping(value="/delMessageById",method=RequestMethod.POST)
    public int delMessageById(@RequestParam("id") String id){
            return service.delMessage(id);
    }
}

运行App 输入地址测试，获取数据库数据......

扩展如果出现如下异常或警告：

远程mysql_java.sql.SQLException: null, message from server: "Host 'xxx' is not allowed to connect

或者：

Mon Apr 04 15:43:00 CST 2016 WARN: Establishing SSL connection without server‘s identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn‘t set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to ‘false‘. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

转接：

http://blog.csdn.net/lu1024188315/article/details/78710338

原链接：https://www.cnblogs.com/liangblog/p/5228548.html

版权声明：本文来源CSDN，感谢博主原创文章，遵循 CC 4.0 by-sa 版权协议，转载请附上原文出处链接和本声明。
原文链接：https://blog.csdn.net/lu1024188315/article/details/78710280
站方申明：本站部分内容来自社区用户分享，若涉及侵权，请联系站方删除。

发表于 2020-03-08 11:41:55
阅读 ( 1136 )
分类：数据库

Spring Boot 连接MySql数据库

你可能感兴趣的文章

精选的优质文章

0 条评论

官方社群

GO教程

推荐文章

猜你喜欢

随便看看