Springboot用idea写查询数据库程序（详细讲解）

默语

发布于 2024-11-20 00:06:22

15200

代码可运行

文章被收录于专栏：JAVAJAVA

运行总次数：0

代码可运行

先把代码给大家详细贴以下 首先是连接数据库的application.properties

root和15235068202 是我的账号和密码    8888是访问的端口号    school是我的数据库的 库名字

spring.datasource.url = jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8
spring.datasource.username = root
spring.datasource.password =  15235068202
spring.datasource.driverClassName = com.mysql.jdbc.Driver

server.port=8888
mybatis.mapper-locations=classpath:mapper/*.xml
#spring.mvc.static-path-pattern=/static/**
#logging.level.com.example.thecloudsystem.dao=debug
#logging.level.org.springframework.security=info
logging.level.mapper=DEBUG
#logging.level.com.example.syscloud.dao=DEBUG
# REDIS (RedisProperties)
# Redis数据库索引（默认为0）
spring.thymeleaf.enabled=true

需要引入的依赖pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.0.BUILD-SNAPSHOT</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>schools</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>schools</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>javax.servlet-api</artifactId>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>


        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>

        <!--<dependency>-->
        <!--<groupId>org.springframework.session</groupId>-->
        <!--<artifactId>spring-session</artifactId>-->
        <!--<version>1.3.1.RELEASE</version>-->
        <!--</dependency>-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.38</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>com.github.fge</groupId>
            <artifactId>json-schema-core</artifactId>
            <version>1.2.5</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.47</version>
        </dependency>
        <dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>27.0-jre</version>
        </dependency>
        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-core</artifactId>
            <version>2.9.7</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.16.20</version>
            <scope>provided</scope>
        </dependency>
        <!-- 验证码 -->
        <dependency>
            <groupId>com.github.penggle</groupId>
            <artifactId>kaptcha</artifactId>
            <version>2.3.2</version>
        </dependency>
        <!-- 缓存 -->
        <!--<dependency>-->
        <!--<groupId>org.springframework.boot</groupId>-->
        <!--<artifactId>spring-boot-starter-redis</artifactId>-->
        <!--<version>1.3.5.RELEASE</version>-->
        <!--</dependency>-->
        <!--<dependency>-->
        <!--<groupId>org.springframework.boot</groupId>-->
        <!--<artifactId>spring-boot-starter-data-redis</artifactId>-->
        <!--</dependency>-->
        <!-- https://mvnrepository.com/artifact/com.auth0/java-jwt -->
        <!-- token -->
        <dependency>
            <groupId>com.auth0</groupId>
            <artifactId>java-jwt</artifactId>
            <version>3.3.0</version>
        </dependency>
        <!-- quartz定时器 -->
        <dependency>
            <groupId>org.quartz-scheduler</groupId>
            <artifactId>quartz</artifactId>
            <version>${quartz.version}</version>
        </dependency>
        <dependency>
            <groupId>org.quartz-scheduler</groupId>
            <artifactId>quartz-jobs</artifactId>
            <version>${quartz.version}</version>
        </dependency>

        <!--<dependency>-->
        <!--<groupId>io.springfox</groupId>-->
        <!--<artifactId>springfox-swagger2</artifactId>-->
        <!--<version>2.7.0</version>-->
        <!--</dependency>-->
        <!--<dependency>-->
        <!--<groupId>io.springfox</groupId>-->
        <!--<artifactId>springfox-swagger-ui</artifactId>-->
        <!--<version>2.6.1</version>-->
        <!--</dependency>-->
        <dependency>
            <groupId>org.testng</groupId>
            <artifactId>testng</artifactId>
            <version>RELEASE</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

    <repositories>
        <repository>
            <id>spring-snapshots</id>
            <name>Spring Snapshots</name>
            <url>https://repo.spring.io/snapshot</url>
            <snapshots>
                <enabled>true</enabled>
            </snapshots>
        </repository>
        <repository>
            <id>spring-milestones</id>
            <name>Spring Milestones</name>
            <url>https://repo.spring.io/milestone</url>
        </repository>
    </repositories>
    <pluginRepositories>
        <pluginRepository>
            <id>spring-snapshots</id>
            <name>Spring Snapshots</name>
            <url>https://repo.spring.io/snapshot</url>
            <snapshots>
                <enabled>true</enabled>
            </snapshots>
        </pluginRepository>
        <pluginRepository>
            <id>spring-milestones</id>
            <name>Spring Milestones</name>
            <url>https://repo.spring.io/milestone</url>
        </pluginRepository>
    </pluginRepositories>

</project>

再简单的贴下我的数据库从数据直接导出的数据

/*
Navicat MySQL Data Transfer

Source Server         : localhost
Source Server Version : 50703
Source Host           : 127.0.0.1:3306
Source Database       : school

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

Date: 2019-06-19 17:45:01
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `password` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'root', '123456');
INSERT INTO `user` VALUES ('2', 'httl', '554952');
INSERT INTO `user` VALUES ('3', '刘俊', '3');

接下来给大家看下都需要那些类

实体类userBean get/set 方法

package com.example.demo4.userBean;
import javax.persistence.Entity;
import javax.persistence.Id;

//     @Entity   对实体注释。任何Hibernate映射对象都要有这个注释
//     @Id   声明此属性为主键。该属性值可以通过应该自身创建
//实体类
@Entity
public class userBean {

    @Id
    private Integer id;
    private String name;
    private String password;


    public userBean(Integer id, String name,String password) {
      this.id=id;
        this.name = name;
        this.password=password;
    }


    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }
}

mapper的数据库语句

<?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.example.demo4.userDao.UserDao">

    <resultMap id="userMap" type="com.example.demo4.userBean.userBean">

        <id column="id" property="id" jdbcType="INTEGER"/>
        <result column="name" property="name" />
        <result column="password" property="password" />

//man 中的id name password 只是优化数据库的作用
    </resultMap>
    <sql id="Man">id,name,password</sql>
<select id="select" resultMap="userMap" parameterType="com.example.demo4.userBean.userBean">

    select <include refid="Man"/> from user


</select>
</mapper>

userDao 数据源文件

package com.example.demo4.userDao;


import com.example.demo4.userBean.userBean;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;
import java.util.List;
import java.util.Map;


//数据源文件
@Repository
@Mapper
public interface UserDao {

    List<userBean> select(Map<String, Object> userMap);


}

service 业务层

package com.example.demo4.service;


import com.example.demo4.userBean.userBean;

import java.util.List;
import java.util.Map;

public interface service {
    List<userBean> select(Map<String, Object> userMap);

}

serviceimpl

业务实现层

package com.example.demo4.service;

import com.example.demo4.userBean.userBean;

import com.example.demo4.userDao.UserDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
import java.util.Map;

@Service
public class servielimpl implements service {

@Autowired
private UserDao userDao;


    @Override
    public List<userBean> select(Map<String, Object> userMap) {
        return userDao.select(userMap);
    }
}

testController 直接访问返回

package com.example.demo4.Controller;
import com.example.demo4.service.service;
import com.example.demo4.userBean.userBean;
import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletRequest;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

//@Controller
@RestController
public class testController {
    @Autowired
    private service service;
    @RequestMapping("/aaa")
    public String sayHello() {
        return "helloword";

    }

testController 直接查询数据库数据

package com.example.demo4.Controller;
import com.example.demo4.service.service;
import com.example.demo4.userBean.userBean;
import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletRequest;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

//@Controller
@RestController
public class testController {
    @Autowired
    private service service;
    @RequestMapping("/aaa")
    public String sayHello() {
        return "helloword";

    }


    @RequestMapping(value = "/fu")
    @ResponseBody
    public List<userBean> fuzzyCUser(HttpServletRequest request) {
       Map<String,Object> result = new HashMap<>();
        List<userBean>list =service.select(result);
      //  result.put("list",list);
        request.getSession().setAttribute("LIST",list);
      //  return result;
           return list;


    }

testController 访问list输出的数据

package com.example.demo4.Controller;
import com.example.demo4.service.service;
import com.example.demo4.userBean.userBean;
import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletRequest;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

//@Controller
@RestController
public class testController {
    @Autowired
    private service service;
    @RequestMapping("/aaa")
    public String sayHello() {
        return "helloword";

    }


//    @RequestMapping(value = "/fu")
//    @ResponseBody
//    public List<userBean> fuzzyCUser(HttpServletRequest request) {
//       Map<String,Object> result = new HashMap<>();
//        List<userBean>list =service.select(result);
//      //  result.put("list",list);
//        request.getSession().setAttribute("LIST",list);
//      //  return result;
//           return list;
//
//
//    }

    @RequestMapping(value = "/fuss")
    @ResponseBody
    public Map<String,Object> fuzzyCUser(HttpServletRequest request) {
        Map<String, Object> result = new HashMap<>();
        List<userBean> list = service.select(result);
        result.put("list", list);
        request.getSession().setAttribute("LIST", list);
        return result;
         
    }


}