SpringBoot连接MySQL数据库操作

全栈程序员站长

发布于 2022-09-12 20:34:03

7020

发布于 2022-09-12 20:34:03

文章被收录于专栏：全栈程序员必看全栈程序员必看

大家好，又见面了，我是你们的朋友全栈君。

首先，数据库名称：tp_kairui

表名称：course

mysql数据库代码：

/*
Navicat MySQL Data Transfer

Source Server         : mysql
Source Server Version : 50529
Source Host           : localhost:3306
Source Database       : tp_kairui

Target Server Type    : MYSQL
Target Server Version : 50529
File Encoding         : 65001

Date: 2021-05-29 12:43:23
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `cno` varchar(255) NOT NULL,
  `cname` varchar(255) DEFAULT NULL,
  `cpno` varchar(255) DEFAULT NULL,
  `ccredit` int(11) DEFAULT NULL,
  PRIMARY KEY (`cno`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('123', '刘德华', '432423145', '2147483647');
INSERT INTO `course` VALUES ('124', '郭富城', '432133125', '2147483647');

1、新建一个project

2、填写，Group id，Artifact id 和 Package name。

3、选择 web—》Spring web。

4、下一步

5、目录结构

6、分别导入mybatis依赖和mysql依赖，

7、导入依赖后，需要刷新才能生效，idea会自动下载jar包

8、分别创建以下包名，接口和类，对应代码分别为：

CourseController类：

package com.demo.controller;


import com.demo.dao.CourseMapper;
import com.demo.entity.Course;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
@RequestMapping("/course")
public class CourseController {

    @Autowired
    private CourseMapper courseMapper;

    @RequestMapping("/query")
    public List<Course> queryAll(){
        return courseMapper.queryAll();
    }

}

CourseMapper接口：

package com.demo.dao;


import com.demo.entity.Course;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;

import java.util.List;

@Mapper
@Repository
public interface CourseMapper {
    List<Course> queryAll();

    boolean insertRecord(Course course);
}

Course实体类：

package com.demo.entity;

public class Course {
    private String cno;
    private String cname;
    private String cpno;
    private int ccredit;

    public String getCno() {
        return cno;
    }

    public void setCno(String cno) {
        this.cno = cno;
    }

    public String getCname() {
        return cname;
    }

    public void setCname(String cname) {
        this.cname = cname;
    }

    public String getCpno() {
        return cpno;
    }

    public void setCpno(String cpno) {
        this.cpno = cpno;
    }

    public int getCcredit() {
        return ccredit;
    }

    public void setCcredit(int ccredit) {
        this.ccredit = ccredit;
    }
}

9、新建application.yml 文件夹

spring:
  datasource:
    url: jdbc:mysql://127.0.0.1/tp_kairui?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC&useSSL=true
    username: root
    password: root123
    driver-class-name: com.mysql.cj.jdbc.Driver

  application:
    name: springboot_helloworld

server:
  port: 8080

mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.demo0.entity

10、新建mapper文件夹，在其中新建CourseMapper.xml 文件，键入xml代码

<?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.demo.dao.CourseMapper">
    <select id="queryAll" resultType="com.demo.entity.Course">
        SELECT *
        FROM tp_kairui.course
    </select>

    <insert id="insertRecord" parameterType="com.demo.entity.Course">
        INSERT INTO tp_kairui.course
        VALUES (
        #{cno, jdbcType=VARCHAR},
        #{cname, jdbcType=VARCHAR},
        #{cpno, jdbcType=VARCHAR},
        #{ccredit, jdbcType=INTEGER}
        )
    </insert>
</mapper>