Spring Boot高级教程之Spring Boot连接MySql数据库

Spring Boot高级教程之Spring Boot连接MySql数据库

Spring Boot可以大大简化持久化任务，几乎不需要写SQL语句，在之前章节“Spring Boot 构建框架”中我们新建了一个Spring Boot应用程序，本章在原有的工程中与数据库建立连接。

Spring Boot有两种方法与数据库建立连接，一种是使用JdbcTemplate，另一种集成Mybatis，下面分别为大家介绍一下如何集成和使用这两种方式。

1. 使用JdbcTemplate

mysql

mysql-connector-java

org.springframework.boot

spring-boot-starter-jdbc

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

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=1000

spring.datasource.min-idle=5

spring.datasource.initial-size=5

server.port=8012

server.session.timeout=10

server.tomcat.uri-encoding=UTF-8

新建Controller类测试数据库连接，实例如下：

package com.example.demo;

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

String sql = "select * from appuser";

List

for (Map map : list) {

Set> entries = map.entrySet( );

if(entries != null) {

Iterator> iterator = entries.iterator( );

while(iterator.hasNext( )) {

Entry entry =(Entry) iterator.next( );

Object key = entry.getKey( );

Object value = entry.getValue();

System.out.println(key+":"+value);

}

}

}

return list;

}

@RequestMapping("/user/{id}")

public Map getUser(@PathVariable String id){

Map map = null;

List

for (Map dbmap : list) {

Set 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依赖，在pom.xml文件中增加如下：

org.mybatis.spring.boot

mybatis-spring-boot-starter

1.0.0

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

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=1000

spring.datasource.min-idle=5

spring.datasource.initial-size=5

server.port=8012

server.session.timeout=10

server.tomcat.uri-encoding=UTF-8

依次添加mapper的接口类和xml文件，类分别如下：

AppMessageMapper.java

package com.example.demo.mapper;

import java.util.List;

import com.example.demo.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 selectAll();

List getMessById(String id);

}

AppMessageMapper.xml

id, message, senddate

select

from appuser_message

where id = #{id,jdbcType=VARCHAR}

delete from appuser_message

where id = #{id,jdbcType=VARCHAR}

insert into appuser_message (id, message, senddate

)

values (#{id,jdbcType=VARCHAR}, #{message,jdbcType=VARCHAR}, #{senddate,jdbcType=TIMESTAMP}

)

insert into appuser_message

id,

message,

senddate,

#{id,jdbcType=VARCHAR},

#{message,jdbcType=VARCHAR},

#{senddate,jdbcType=TIMESTAMP},

update appuser_message

message = #{message,jdbcType=VARCHAR},

senddate = #{senddate,jdbcType=TIMESTAMP},

where id = #{id,jdbcType=VARCHAR}

update appuser_message

set message = #{message,jdbcType=VARCHAR},

senddate = #{senddate,jdbcType=TIMESTAMP}

where id = #{id,jdbcType=VARCHAR}

select

id, message, senddate

from appuser_message

order by senddate asc

select

id, message, senddate

from

appuser_message

where id = #{id,jdbcType=VARCHAR}

order by senddate asc

AppMessage.java

package com.example.demo.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;

}

}

AppMessageService.java

package com.example.demo.service;

import java.util.ArrayList;

import java.util.List;

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

import org.springframework.stereotype.Service;

import com.example.demo.bean.AppMessage;

import com.example.demo.mapper.AppMessageMapper;

@Service

public class AppMessageService {

@Autowired

private AppMessageMapper mapper;

public List getMessage(){

List list = new ArrayList();

list.add(mapper.selectByPrimaryKey("xtt"));

//list = mapper.selectAll();

return list;

}

public List getAllMessage(){

List list = new ArrayList();

list = mapper.selectAll();

return list;

}

public int addMessage(AppMessage appMessage) {

return mapper.insert(appMessage);

}

public List getMessageById(String id) {

return mapper.getMessById(id);

}

public int delMessage(String id) {

return mapper.deleteByPrimaryKey(id);

}

}

APPMessageController.java

package com.example.demo.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.example.demo.bean.AppMessage;

import com.example.demo.service.AppMessageService;

@RestController

@RequestMapping("/appmessage")

public class APPMessageController {

@Autowired

private AppMessageService service;

@RequestMapping("/getThree")

public List getThreeForMessage(){

List list = service.getMessage();

return list;

}

@RequestMapping("/getAll")

public List getAllMessage(){

List 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 getMessageById(@RequestParam("id") String id){

List 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)

// 或者采用@PostMapping("/add")方法，更加节省代码的编写量

public int addMessage(@RequestBody AppMessage appMessage){

return service.addMessage(appMessage);

}

@RequestMapping(value="/delMessageById",method=RequestMethod.POST)

// 或者采用@PostMapping("/delMessageById")方法，更加节省代码的编写量

public int delMessageById(@RequestParam("id") String id){

return service.delMessage(id);

}

}

问题描述？

SpringBoot扫描包提示找不到mapper的问题，异常信息：

Consider defining a bean of type in your configuration

分析原因

Spring Boot项目的Bean装配默认规则是根据Application类所在的包位置从上往下扫描，“Application类”是指Spring Boot项目入口类。如果Application类所在的包为：com.yoodb.blog，则只会扫描com.yoodb.blog包及其所有子包，如果service或dao所在包不在com.yoodb.blog及其子包下，则不会被扫描。

解决方法

方式一：使用注解@ComponentScan(value=”com.yoodb.blog”)，其中，com.yoodb.blog为包路径。

方式二：将启动类Application放在上一级包中，注意的是Application启动类必须要保证在包的根目录下。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。