思享工具箱

Spring Boot 连接MySql数据库

摘要：
SpringBoot以后也许会成为入门Spring的首选!记一下SpringBoot成功连接Mysql数据库的方法步骤！可以参照：http://www.cnblogs.com/liangblog/p/5207855.html二、有两种方法与数据库建立连接，一种是集成Mybatis，另一种用JdbcTemplate、用JdbcTemplatemysqlmysql-connector-javaorg.springframework.bootspring-boot-starter-jdbc添加数据库依赖添加配置文件配置数据库和其他参数在resource文件夹下添加application.properties配置文件并输入数据库参数，如下：spring.datasource.url=jdbc:mysql://127.0.0.1:3306/testspring.datasource.username=rootspring.datasource.password=123456spring.datasource.driver-class-name=com.mysql.jdbc.Driverspring.datasource.max-idle=10spring.datasource.max-wait=10000spring.datasource.min-idle=5spring.datasource.initial-size=5server.port=8011server.session.timeout=10server.tomcat.uri-encoding=UTF-8application.properties新建Controller类测试数据库连接packagecom.lgp.SpringBoot;importjava.util.Iterator;importjava.util.List;importjava.util.Map;importjava.util.Map.Entry;importjava.util.Set;importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.jdbc.core.JdbcTemplate;importorg.springframework.web.bind.annotation.PathVariable;importorg.springframework.web.bind.annotation.RequestMapping;importorg.springframework.web.bind.annotation.RestController;@RestController@RequestMappingpublicclassDbController{@AutowiredprivateJdbcTemplatejdbcTemplate;@RequestMappingpublicList˂Map˃getDbType(){Stringsql="select*fromappuser";List˂Map˃list=jdbcTemplate.queryForList;for{Set˂Entry˃entries=map.entrySet();if(entries!=null){Iterator˂Entry˃iterator=entries.iterator();while{Entryentry=iterator.next();Objectkey=entry.getKey();Objectvalue=entry.getValue();System.out.println;}}}returnlist;}@RequestMappingpublicMapgetUser{Mapmap=null;List˂Map˃list=getDbType();for{Setset=dbmap.keySet();for{if{if{map=dbmap;}}}}ifmap=list.get;returnmap;}}DbController.java运行App输入地址输出数据库数据。。。。。。

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配置文件并输入数据库参数，如下：

spring.datasource.url=jdbc:mysql://127.0.0.1:3306/test
spring.datasource.username=root
spring.datasource.password=123456spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.max-idle=10spring.datasource.max-wait=10000spring.datasource.min-idle=5spring.datasource.initial-size=5
server.port=8011server.session.timeout=10server.tomcat.uri-encoding=UTF-8
application.properties

新建Controller类测试数据库连接

packagecom.lgp.SpringBoot;

importjava.util.Iterator;
importjava.util.List;
importjava.util.Map;
importjava.util.Map.Entry;
importjava.util.Set;

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

@RestController
@RequestMapping("/mydb")
public classDbController {

    @Autowired
    privateJdbcTemplate 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);
                }
            }
        }
        returnlist;
    }
    
    @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);
        returnmap;
    }
    
}
DbController.java

运行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=123456spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.max-idle=10spring.datasource.max-wait=10000spring.datasource.min-idle=5spring.datasource.initial-size=5
server.port=8011server.session.timeout=10server.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文件

Spring Boot 连接MySql数据库第1张

主要代码：

packagecom.lgp.SpringBoot.mapper;

importjava.util.List;

importcom.lgp.SpringBoot.bean.AppMessage;

public interfaceAppMessageMapper {

    intdeleteByPrimaryKey(String id);

    intinsert(AppMessage record);

    intinsertSelective(AppMessage record);

    AppMessage selectByPrimaryKey(String id);

    intupdateByPrimaryKeySelective(AppMessage record);

    intupdateByPrimaryKey(AppMessage record);
    
    List<AppMessage>selectAll();

    List<AppMessage>getMessById(String id);
}
AppMessageMapper.java
<?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>
AppMessageMapper.xml
packagecom.lgp.SpringBoot.bean;

importjava.util.Date;

public classAppMessage {
    privateString id;

    privateString message;

    privateDate senddate;

    publicString getId() {
        returnid;
    }

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

    publicString getMessage() {
        returnmessage;
    }

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

    publicDate getSenddate() {
        returnsenddate;
    }

    public voidsetSenddate(Date senddate) {
        this.senddate =senddate;
    }
}
AppMessage.java
packagecom.lgp.SpringBoot.service;

importjava.util.ArrayList;
importjava.util.List;

importorg.springframework.beans.factory.annotation.Autowired;
importorg.springframework.stereotype.Service;

importcom.lgp.SpringBoot.bean.AppMessage;
importcom.lgp.SpringBoot.mapper.AppMessageMapper;

@Service
public classAppMessageService {
    
    @Autowired
    privateAppMessageMapper mapper;
    
    public List<AppMessage>getMessage(){
         List<AppMessage> list = new ArrayList<AppMessage>();
         list.add(mapper.selectByPrimaryKey("xtt"));
         //list = mapper.selectAll();
         returnlist;
    }
    
    public List<AppMessage>getAllMessage(){
         List<AppMessage> list = new ArrayList<AppMessage>();
         list =mapper.selectAll();
         returnlist;
    }

    public intaddMessage(AppMessage appMessage) {
        returnmapper.insert(appMessage);
    }

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

    public intdelMessage(String id) {
        returnmapper.deleteByPrimaryKey(id);
    }
    
    
    
}
AppMessageService.java
packagecom.lgp.SpringBoot.controller;

importjava.util.List;

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

importcom.lgp.SpringBoot.bean.AppMessage;
importcom.lgp.SpringBoot.service.AppMessageService;

@RestController
@RequestMapping("/appmessage")
public classAPPMessageController {

    @Autowired
    privateAppMessageService service;

    @RequestMapping("/getThree")
    public List<AppMessage>getThreeForMessage(){
        
        List<AppMessage> list =service.getMessage();        
        returnlist;
    }
    
    @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);
            }
        }
        returnlist;
    }

    @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);
            }
        }
        returnlist;
    }
    
    @RequestMapping(value = "/add",method =RequestMethod.POST)
    public intaddMessage(@RequestBody AppMessage appMessage){
        returnservice.addMessage(appMessage);
    }
    
    @RequestMapping(value="/delMessageById",method=RequestMethod.POST)
    public int delMessageById(@RequestParam("id") String id){
            returnservice.delMessage(id);
    }
}
APPMessageController.java

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

标签：#string#varchar#select#test#mysql#mysql数据库#mapper | 浏览：273 | 发布日期：

免责声明：文章转载自《Spring Boot 连接MySql数据库》仅用于学习参考。如对内容有疑问，请及时联系本站处理。

上篇iOS Assertion failure in -[UITableView _configureCellForDisplay:forIndexPath:], /BuildRoot/Library/Caches/com.apple.xbs/Sources/UIKit_Sim/UIKit-3512.30.14/UITableView.m:7962linux每日命令(39)：lsof命令下篇

宿迁高防，2C2G15M，22元/月；香港BGP，2C5G5M，25元/月 雨云优惠码：MjYwNzM=

相关文章

不安装office的情况下如何实现对excel的导入导出

答： 方法一、对于07/10及以后的版本，可以采用openxml sdk，因为07/10都是open xml标准的，利用open xml sdk可以不用安装office而对office文件进行操作。而且速度快。 若03不安装office，可以使用NPOI 。 可以考虑将数据发送到服务端，从服务端生成excel文件，然后再传输会客户端。这样客户端就不需要安装...

Protobuf API安全测试启示 admin

ProtoBuf介绍 protocol buffers 是一种语言无关、平台无关、可扩展的序列化结构数据的方法，它可用于（数据）通信协议、数据存储等。 Protocol Buffers 是一种灵活，高效，自动化机制的结构数据序列化方法－可类比 XML，但是比 XML 更小（3 ~ 10倍）、更快（20 ~ 100倍）、更为简单。 因此具有以下特点： 语言...

springboot整合amazonS3,封装上传文件接口

1.在pom.xml中引入amazonS3的依赖。 <dependency> <groupId>com.amazonaws</groupId> <artifactId>aws-java-sdk-s3</artifactId>...

游标（隐式游标与显示游标）

游标的概念:    游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。游标有两种类型：显式游标和隐式游标。在前述程序中用到的SE...

MySql的join（连接）查询 （三表 left join 写法）

1、内连接：将两个表中存在连结关系的字段符合连接条件的记录形成记录集 Select A.name,B.name from A inner join B on A.id=B.id和 Select A.name,B.name from A,B where A.id=B.id结果是一样的（内连接的inner关键字可省略）； 2、外连接：分为左外连接和右外连接 左...

MySQL的limit分页性能测试加优化

日常我们分页时会用到MySQL的limit字段去处理，那么使用limit时，有什么需要优化的地方吗？我们来做一个试验来看看limit的效率问题：环境：CentOS 6 & MySQL 5.71、建议一个实验表： collect(id[主键], title[varchar], info[text], vtype[tinyint]); Engine:...

最新文章

随机推荐

思享工具箱导航