当前位置：首页 > news >正文

【Java+Springboot】------ 通过JDBC+GetMapping方法进行数据select查询、多种方式传参、最简单的基本示例!

news 2025/8/23 18:16:45

一、JDBC如何使用、PostGresql数据库

1、在pom.xml 先引用jdbc组件。

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

2、在pom.xml 再引用postgresql组件。

  <dependency><groupId>org.postgresql</groupId><artifactId>postgresql</artifactId><version>42.2.8</version></dependency>

3、示例图如下：

4、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 https://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.RELEASE</version><relativePath/> <!-- lookup parent from repository --></parent><groupId>com.dcs</groupId><artifactId>lybdemo</artifactId><version>0.0.1-SNAPSHOT</version><name>lybdemo</name><description>Demo project for Spring Boot</description><properties><java.version>1.8</java.version></properties><dependencies><!--jdbc--><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-jdbc</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-test</artifactId><scope>test</scope></dependency><!--StringUtils 引用--><dependency><groupId>org.apache.commons</groupId><artifactId>commons-lang3</artifactId><version>3.12.0</version> <!-- 请使用最新的版本 --></dependency><!--PostGresql数据库--><dependency><groupId>org.postgresql</groupId><artifactId>postgresql</artifactId><version>42.2.8</version></dependency><!--数据库连接池--><dependency><groupId>com.alibaba</groupId><artifactId>druid</artifactId><version>1.1.12</version></dependency><!--阿里巴巴--><dependency><groupId>com.alibaba</groupId><artifactId>fastjson</artifactId><version>1.2.70</version></dependency></dependencies><build><plugins><plugin><groupId>org.springframework.boot</groupId><artifactId>spring-boot-maven-plugin</artifactId></plugin></plugins></build></project>

二、连接池参数设置

1、在application.properties中加上连接池配置
spring.datasource.url=jdbc:postgresql://192.168.1.18:8088/dcs_xg_test
spring.datasource.username=postgres
spring.datasource.password=KzKj@20230331
2、效果如下：

三、查询数据库中数据【提示：postgresql数据库中如果表字段名使用大写方式、sql语句的字段名需要使用“\"” 引用起来】

1、创建Controller控制器类

2、调用JDBC连接类

private final JdbcTemplate jdbcTemplate;//连接变量@Autowiredpublic testController(JdbcTemplate jdbcTemplate) {this.jdbcTemplate = jdbcTemplate;//赋予权限}

3、无参数查询数据方式。

 /*** 无参数获取数据方式* **/@GetMapping("/getselect")public IMXResponse  getselect()throws Exception {IMXResponse ret=new IMXResponse();List<Map<String,Object>> list= jdbcTemplate.queryForList("SELECT \"CNAME\",\"ID\",\"STORE_PATH\",\"PUBLISH_TIME\" FROM \"PUB_FILES\" ORDER BY \"PUBLISH_TIME\" DESC LIMIT 5 OFFSET 0 ");//查询数据ret.setSuccess(1);ret.setJson(list);return ret;}

在浏览器地址栏输入如下地址显示结果如下：

4、单个参数查询数据方式。

 /*** 单个参数获取数据方式* **/@GetMapping("/getselectId")public IMXResponse  getselectId(@RequestParam(name = "id", defaultValue = "") String id)throws Exception {System.out.print(id);//f36264bd-b68f-4183-8aac-9de96b0f832aIMXResponse ret=new IMXResponse();String v_id=id.trim();if(v_id.equals("")==false){List<Map<String,Object>> list= jdbcTemplate.queryForList("SELECT \"CNAME\",\"ID\",\"STORE_PATH\",\"PUBLISH_TIME\" FROM \"PUB_FILES\" WHERE \"ID\"='"+v_id+"' ORDER BY \"PUBLISH_TIME\" DESC LIMIT 5 OFFSET 0 ");ret.setSuccess(1);ret.setJson(list);}else{ret.setSuccess(0);ret.setJson("");}return ret;}

在浏览器地址栏输入如下地址显示结果如下：

4、多个参数获取数据方式

/*** 多个参数获取数据方式* **/@GetMapping("/getselectMulitId")public IMXResponse  getselectMulitId(@RequestParam String id,@RequestParam String billid)throws Exception {//http://127.0.0.1:8080/getselectMulitId?id=2211091436481667975808d1d5cc3&billid=13e9c0f11ddd4c1c81fe222a73364252System.out.print(id);//IMXResponse ret=new IMXResponse();String v_id=id.trim();String v_billid=billid.trim();if(v_id.equals("")==false){List<Map<String,Object>> list= jdbcTemplate.queryForList("SELECT \"CNAME\",\"ID\",\"STORE_PATH\",\"PUBLISH_TIME\" FROM \"PUB_FILES\" WHERE \"ID\"='"+v_id+"' AND \"BILL_ID\"='"+v_billid+"' ORDER BY \"PUBLISH_TIME\" DESC LIMIT 5 OFFSET 0 ");ret.setSuccess(1);ret.setJson(list);}else{ret.setSuccess(0);ret.setJson("");}return ret;}

5、json格式数据传参方式获取数据。这种数据格式需要实现ajax方法调用

 /*** 传object参数获取数据方式* **/@GetMapping("/getselectObject")public IMXResponse  getselectObject(@RequestParam String whereJson)throws Exception {//http://127.0.0.1:8080/getselectObject?whereJson={id:2211091436481667975808d1d5cc3,billid:13e9c0f11ddd4c1c81fe222a73364252}//此地址参数需要JSON.stringify() 编译一下System.out.print(whereJson);//IMXResponse ret=new IMXResponse();String v_whereJson=whereJson.trim();if(v_whereJson.equals("")==false){JSONObject jsonObject=JSONObject.parseObject(v_whereJson);String v_id=jsonObject.getString("id");String v_billid=jsonObject.getString("billid");List<Map<String,Object>> list= jdbcTemplate.queryForList("SELECT \"CNAME\",\"ID\",\"STORE_PATH\",\"PUBLISH_TIME\" FROM \"PUB_FILES\" WHERE \"ID\"='"+v_id+"' AND \"BILL_ID\"='"+v_billid+"' ORDER BY \"PUBLISH_TIME\" DESC LIMIT 5 OFFSET 0 ");ret.setSuccess(1);ret.setJson(list);}else{ret.setSuccess(0);ret.setJson("");}return ret;}

四、testController 完整代码：

package com.dcs.lybdemo.controller;import com.alibaba.fastjson.JSONObject;
import com.dcs.lybdemo.common.IMXResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;import java.util.List;
import java.util.Map;@RestController
public class testController {private final JdbcTemplate jdbcTemplate;@Autowiredpublic testController(JdbcTemplate jdbcTemplate) {this.jdbcTemplate = jdbcTemplate;}/*** 无参数获取数据方式* **/@GetMapping("/getselect")public IMXResponse  getselect()throws Exception {IMXResponse ret=new IMXResponse();List<Map<String,Object>> list= jdbcTemplate.queryForList("SELECT \"CNAME\",\"ID\",\"STORE_PATH\",\"PUBLISH_TIME\" FROM \"PUB_FILES\" ORDER BY \"PUBLISH_TIME\" DESC LIMIT 5 OFFSET 0 ");ret.setSuccess(1);ret.setJson(list);return ret;}/*** 单个参数获取数据方式* **/@GetMapping("/getselectId")public IMXResponse  getselectId(@RequestParam(name = "id", defaultValue = "") String id)throws Exception {System.out.print(id);//f36264bd-b68f-4183-8aac-9de96b0f832aIMXResponse ret=new IMXResponse();String v_id=id.trim();if(v_id.equals("")==false){List<Map<String,Object>> list= jdbcTemplate.queryForList("SELECT \"CNAME\",\"ID\",\"STORE_PATH\",\"PUBLISH_TIME\" FROM \"PUB_FILES\" WHERE \"ID\"='"+v_id+"' ORDER BY \"PUBLISH_TIME\" DESC LIMIT 5 OFFSET 0 ");ret.setSuccess(1);ret.setJson(list);}else{ret.setSuccess(0);ret.setJson("");}return ret;}/*** 多个参数获取数据方式* **/@GetMapping("/getselectMulitId")public IMXResponse  getselectMulitId(@RequestParam String id,@RequestParam String billid)throws Exception {//http://127.0.0.1:8080/getselectMulitId?id=2211091436481667975808d1d5cc3&billid=13e9c0f11ddd4c1c81fe222a73364252System.out.print(id);//IMXResponse ret=new IMXResponse();String v_id=id.trim();String v_billid=billid.trim();if(v_id.equals("")==false){List<Map<String,Object>> list= jdbcTemplate.queryForList("SELECT \"CNAME\",\"ID\",\"STORE_PATH\",\"PUBLISH_TIME\" FROM \"PUB_FILES\" WHERE \"ID\"='"+v_id+"' AND \"BILL_ID\"='"+v_billid+"' ORDER BY \"PUBLISH_TIME\" DESC LIMIT 5 OFFSET 0 ");ret.setSuccess(1);ret.setJson(list);}else{ret.setSuccess(0);ret.setJson("");}return ret;}/*** 传object参数获取数据方式* **/@GetMapping("/getselectObject")public IMXResponse  getselectObject(@RequestParam String whereJson)throws Exception {//http://127.0.0.1:8080/getselectObject?whereJson={id:2211091436481667975808d1d5cc3,billid:13e9c0f11ddd4c1c81fe222a73364252}//此地址参数需要JSON.stringify() 编译一下System.out.print(whereJson);//IMXResponse ret=new IMXResponse();String v_whereJson=whereJson.trim();if(v_whereJson.equals("")==false){JSONObject jsonObject=JSONObject.parseObject(v_whereJson);String v_id=jsonObject.getString("id");String v_billid=jsonObject.getString("billid");List<Map<String,Object>> list= jdbcTemplate.queryForList("SELECT \"CNAME\",\"ID\",\"STORE_PATH\",\"PUBLISH_TIME\" FROM \"PUB_FILES\" WHERE \"ID\"='"+v_id+"' AND \"BILL_ID\"='"+v_billid+"' ORDER BY \"PUBLISH_TIME\" DESC LIMIT 5 OFFSET 0 ");ret.setSuccess(1);ret.setJson(list);}else{ret.setSuccess(0);ret.setJson("");}return ret;}
}

需要注意的如下图：红框里面的代码不能丢了很重要不然系统找不到入口.