spring boot查询数据库实例

一.创建一个maven工程

二、添加相应配置

pom.xml

<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>
	<artifactId>bee-starter-demo</artifactId>
	<version>1.9.5</version>
<!-- 	<version>1.9.8</version> -->

	<parent>
		<groupId>org.teasoft</groupId>
		<artifactId>bee-spring-boot-starter</artifactId>
		<version>1.9.5</version>
<!--      <version>1.9.8</version> -->
	</parent>
	
	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<spring.version>1.5.16.RELEASE</spring.version>
<!-- 		<spring.version>2.4.2</spring.version> -->
	</properties>

	<dependencies>

		<dependency>
			<groupId>com.automvc</groupId>
			<artifactId>comm</artifactId>
			<version>1.0.2-SNAPSHOT</version>
			<scope>system</scope>
			<systemPath>${project.basedir}/src/main/webapp/WEB-INF/lib/automvc-comm-basic2.jar</systemPath>
		</dependency>

		<!-- druid -->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid</artifactId>
			<version>1.1.23</version>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
			<version>${spring.version}</version>
		</dependency>

		<!-- if in spring-boot use datasoure,need this dependency -->
		<!-- 直接使用DriverManager.getConnection获取连接,不用设置 -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
			<version>${spring.version}</version>
		</dependency>
		
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.47</version>
			<scope>runtime</scope>
		</dependency>
		
		 <!-- log4j -->
		<dependency>
			<groupId>log4j</groupId>
			<artifactId>log4j</artifactId>
			<version>1.2.17</version>
			<scope>provided</scope>
		</dependency>

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

</project>

application.properties 数据库等配置：

bee.db.dbName=MySQL
bee.db.driverName = com.mysql.jdbc.Driver
bee.db.url =jdbc:mysql://localhost:3306/bee?characterEncoding=UTF-8
bee.db.username = root
bee.db.password =

bee.osql.naming.translateType=3
bee.osql.showSQL=true

bee.osql.showSql.showType=true
bee.osql.showSql.showExecutableSql=true

bee.osql.showSql.donotPrintCurrentDate=true

#log4j>slf4j>log4j2>systemLogger>fileLogger>noLogging>jdkLog>commonsLog
bee.osql.loggerType=systemLogger

三、添加相应Java类

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.teasoft.bee.osql.BeeSQLException;
import org.teasoft.bee.osql.service.ObjSQLRichService;
import org.teasoft.bee.osql.service.ObjSQLService;

import com.automvc.common.jquery.Result;
import com.automvc.enet.order.entity.Orderhistory;
import com.automvc.moreds.RwDsTest;

/**
 * @author AiTeaSoft.com
 * @since  1.0
 * Create on 2019-04-16 11:48:24
 */
@RestController
@RequestMapping("/orderhistory")
public class OrderhistoryRest {
	@Autowired
	ObjSQLService objSQLService;

	@Autowired
	ObjSQLRichService objSQLRichService;

	@RequestMapping("/list")
	public Result list(Orderhistory orderhistory, @RequestParam(value = "page", defaultValue = "1", required = false) int page,
			@RequestParam(value = "rows", defaultValue = "20", required = false) int rows) {
		Result result = new Result();
		try {
			int total = objSQLRichService.count(orderhistory);
			List<Orderhistory> list = objSQLRichService.select(orderhistory, (page - 1) * rows, rows);
			result.setRows(list);
			result.setTotal(total);
		} catch (BeeSQLException e) {
			System.err.println(e.getMessage());
			result.setErrorMsg(e.getMessage());
		}catch (Exception e) {
			System.err.println(e.getMessage());
			result.setErrorMsg(e.getMessage());
		}

		return result;
	}

}

查询数据主要代码是：

int total = objSQLRichService.count(orderhistory);
List<Orderhistory> list = objSQLRichService.select(orderhistory, (page - 1) * rows, rows);

此处查询数据库较为简单，主要是用了轻量级的ORM框架Bee. 此框架简单易用，功能强大，十分钟就可以上手使用。

四、运行并访问工程

右击Application类，选择：

查看返回数据

在浏览器访问：

http://localhost:8080/orderhistory/list

可以看到有json数据返回。

日志log

[Bee] select fun SQL : select count(*) from orderhistory   [values]: 
[Bee] select fun SQL :  ( ExecutableSql )
select count(*) from orderhistory
2021-11-03 15:03:12.912  INFO 6324 --- [nio-8080-exec-1] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} inited
[Bee] select(entity,start,size) SQL: select id,name,orderNo,seqNO,num,numFlag,type,detail,datetime,remark from orderhistory limit ?,?   [values]: 0(Integer),20(Integer)
[Bee] select(entity,start,size) SQL:  ( ExecutableSql )
select id,name,orderNo,seqNO,num,numFlag,type,detail,datetime,remark from orderhistory limit 0,20
[Bee]  | <--  select rows: 20

更详细增删改查,请查看源码，地址：

https://gitee.com/automvc/bee-starter-demo

https://github.com/automvc/bee-starter-demo