基于spring boot + MySQL 的简单网页查询 源码详解及服务器部署_mysql 检索网页

声明：源码是在原有代码中截取，没有实际跑，有问题请私信或者评论。
有不明白的也请留言，大家一起学习 (¬‿¬)

α 环境配置

1. 在eclipse中spring boot（框架）的环境搭建
   spring boot环境搭博客链接

控制台出现spring表示环境搭建完成

表示该spring boot程序正确启动

关于二次启动报错问题,端口被前一程序占用,将程序关闭后再次重启即可

2. win10环境Mysql的安装
   Mysql安装教程链接
   注意在Linux服务器上改用MySQL的分支MariaDB

3. 需要的jar
   jdbc --链接数据库
   选择平台不依赖的驱动 - - - jdbc下载地址

json --数据传输
从maven下载的教程

β 代码详解

1. 项目结构

com.test包下
Data_f 与 Data 为对象的类封装 , Database 为具体的逻辑实现 , SpringTestApplication 为程序入口

index.html是前端页面

pom.xml 和 application.properties 都是spring boot的配置文件

2. 执行流程

前端获取用户输入信息 , 将其传向后端 , 后台接收信息后进行处理 , 生成相应的SQL语句 , 在数据库进行查询并返回所查信息 , 对信息进行封装 , 将数据提交给前端 , 由前端进行数据展示.

3. 核心部分代码解释

• 前端获取用户输入

<input type="text" id="01">				//用户通过文本框输入
<input type="button" value="search" onclick="a()">	//点击事件 ,调用js函数
1
2

• 前端发送请求及接收返回数据
  在html文件中使用js需使用 <script type=“text/javascript”></script> 标签
  使用ajax需使用 <script src=“https://code.jquery.com/jquery-3.1.1.min.js”></script> 标签

function a()
{
 var words = document.getElementById("01").value    //通过id得到文本框的值,转为js变量
 alert(words);					    //验证js变量是否正确
 $.ajax({
  url:"/precise",				    //ajax发起请求的地址
  data:{"msg":words},				    //发送的请求信息,msg为信息名,可随意
  type:"POST",					    //请求方式
  dataType:"json",		      		    //传输数据格式(返回的)
  contentType:"application/json",		    //传输数据格式(发送的)
  success:function(da)				    //接收数据的函数(默认),da为接收的变量
  {
   var json = JSON.parse(da);
   document.write(json[0].id+"  "+json[0].author);
  }
 })
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

• 后端依赖及配置
  pom.xml中需添加的依赖

<dependencies>
 
 <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
  </dependency>
                
   <dependency>
       <groupId>mysql</groupId>
       <artifactId>mysql-connector-java</artifactId>
   </dependency>
   
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
   </dependency>
    
  <dependency>
  	 <groupId>org.springframework.boot</groupId>
  	 <artifactId>spring-boot-starter-web</artifactId>
  </dependency>
    
  <dependency>
   	<groupId>org.springframework.boot</groupId>
  	 <artifactId>spring-boot-starter-web-services</artifactId>
  </dependency>
  
  <dependency>
 	  <groupId>org.springframework.boot</groupId>
  	 <artifactId>spring-boot-starter-test</artifactId>
  	 <scope>test</scope>
  </dependency>
  
 </dependencies>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

application.properties中对于数据库和前端文件的配置

spring.mvc.view.prefix=/
spring.mvc.view.suffix=.html		//告诉spring前端是 任意开头 .html结尾的文件
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost/要用的库名?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone==UTC
	//此为新版jdbc驱动程序,即mysql-connector-java-6及以上版本
spring.datasource.username=数据库的用户名
spring.datasource.password=用户密码
spring.jpa.show-sql=true
1
2
3
4
5
6
7
8

• 数据库操作

  private String JDBC_DRIVE = "com.mysql.cj.jdbc.Driver";
  private String DB_URL = "jdbc:mysql://localhost/要用的库名?useSSL=false&serverTimezone=UTC";
  private String user = "数据库的用户名";
  private String pw = "用户密码";

  //进行连接，并返回连接实例
  public void getStatement() throws ClassNotFoundException, SQLException
  {
	   Class.forName(JDBC_DRIVE);
	   conn = DriverManager.getConnection(DB_URL, user, pw);
	   stmt = conn.createStatement();
  }
  
  //获取查询的对象列表
  public List<Data_f> getList_f() throws SQLException, ClassNotFoundException
  {
	   String sql = this.sql2;
	   List<Data_f> list = new ArrayList<Data_f>();
	   ResultSet resultSet = stmt.executeQuery(sql);
	
	   while(resultSet.next())
	   {
		    Data_f data_f =new Data_f();
		    String rec = resultSet.getString("msg");
		    data_f.setMsg(rec);
		    list.add(data_f);
	    }
  	    return list;
  }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

• 前后端数据交互

  @RequestMapping("precise")				//路由,即前端提交的地址
public String getMsg(@RequestBody String msg0)		//接收前端提交的数据
{ 
 this.msg = msg0;
 System.out.println(msg);
 return msg0;						//后台返回的数据
}

1
2
3
4
5
6
7
8

• 数据处理
  根据实际进行个性化处理

γ 源码

1. Data.java

package com.test;

public class Data_f {
 private String msg;
 public void setMsg(String msg0) {this.msg = msg0;}
 public String getMsg() {return msg;}
}
1
2
3
4
5
6
7

3. Database.java

package com.test;

import com.test.*;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class Database {
private String JDBC_DRIVE = "com.mysql.cj.jdbc.Driver";
private String DB_URL = "jdbc:mysql://localhost:3306/要用的库名?useSSL=false&serverTimezone=UTC";
private String user = "数据库的用户名";
private String pw = "用户密码";
private String msg;
private String sql0;

private Connection conn = null;
public Statement stmt = null;

//返回模糊查询信息
@RequestMapping("fuzzy")
public List<Data_f> getMsg0(@RequestBody String msg0) throws ClassNotFoundException, SQLException
{ 
     this.msg = msg0;
     System.out.println(msg0);
     getStatement();
     getSql_f();
     List<Data_f> list = new ArrayList<Data_f>();
     list = getList_f();
     return list;
    }

//进行连接，并返回连接
public void getStatement() throws ClassNotFoundException, SQLException
{
Class.forName(JDBC_DRIVE);
conn = DriverManager.getConnection(DB_URL, user, pw);
stmt = conn.createStatement();
}

//确定模糊查询语句
public void getSql_f() throws SQLException
{
String msg = this.msg;
   
String regex = "(.{1})";
     msg = msg.replaceAll (regex, "$1%");
     msg = '%'+msg;

   sql0 = "select * from books0 where msg like "+"'"+msg+"'";
   System.out.println("查询语句"+sql0);
}

//获取模糊查询的对象列表
public List<Data_f> getList_f() throws SQLException, ClassNotFoundException
{
String sql = this.sql0;
List<Data_f> list = new ArrayList<Data_f>();
ResultSet resultSet = stmt.executeQuery(sql);

while(resultSet.next())
{
 Data_f data_f =new Data_f();
 String rec = resultSet.getString("msg");
 data_f.setMsg(rec);
 list.add(data_f);
}
return list;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78

5. SpringTestApplication.java

package com.test;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class SpringTestApplication {

	public static void main(String[] args) {
	  SpringApplication.run(SpringTestApplication.class, args);
	  }
}

1
2
3
4
5
6
7
8
9
10
11
12
13

6. index.html

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>

<script src="https://code.jquery.com/jquery-3.1.1.min.js"></script>
<script type="text/javascript">

function b()
{
 var words = document.getElementById("02").value
 alert(words);
 //var json=json.stringify({"msg":"asdfsdfs"});
 $.ajax({
  //url:"/data/getjson",
  url:"/fuzzy",
  data:{"msg":words},
  type:"POST",
  dataType:"text",
  contentType:"application/json",
  success:function(daa)
  {
	  var json = JSON.parse(daa);
	   document.write(json[0].msg);
  }
 })
}
</script>
</head>

<body>
<input type="text" id="02">
<input type="button" value="search_f" onclick="b()">
</body>
</html>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

7. application.properties

spring.mvc.view.prefix=/
spring.mvc.view.suffix=.html  //告诉spring前端是 任意开头 .html结尾的文件
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost/要用的库名?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone==UTC
 //此为新版jdbc驱动程序,即mysql-connector-java-6及以上版本
spring.datasource.username=数据库的用户名
spring.datasource.password=用户密码
spring.jpa.show-sql=true
1
2
3
4
5
6
7
8

8. pom.xml须修改处

<dependencies>
 
 <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
  </dependency>
                
   <dependency>
       <groupId>mysql</groupId>
       <artifactId>mysql-connector-java</artifactId>
   </dependency>
   
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
   </dependency>
    
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
  </dependency>
    
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web-services</artifactId>
  </dependency>
  
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
  </dependency>
  
 </dependencies>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34