Oracle数据库入门，SpringBoot集成Oracle

Oracle数据库入门，SpringBoot集成Oracle

官网下载地址

一. 主键递增设置

基于navicat进行创建

1. 其他->序列->新建序列

2. 设置 递增1，最小1，保存，命名

3. 创建表，设置主键，非null，以及其他字段

4. 新建查询，输入以下命令创建触发器，主要修改四个参数

   CREATE TRIGGER AUTO_GROW_TRIGGER_BAK(定义触发器名) BEFORE
   insert ON T_USER(表名) FOR EACH ROW
   begin
   select ID_AUTO_GROW_SEQUENCE(序列名).nextval into:New.ID(主键名) from dual;
   end;
   

二. 项目搭建

1. 导入依赖

	<parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.8.RELEASE</version>
        <relativePath/> 
	</parent>
	
	<dependencies>
		<!--web包-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
		
        <!--oracle连接-->
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.1.0</version>
        </dependency>

        <!--mybatis plus-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>2.2.0</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.15</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
    </dependencies>

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

2. 配置yml

server:
  port: 2011
spring:
  datasource:
    url: jdbc:oracle:thin:@localhost:1521:XE
    username: QIU
    password: 123456
    driver-class-name: oracle.jdbc.driver.OracleDriver
mybatis-plus:
  mapper-locations: classespath:com/qiuming/hello/mapper/*.xml

3. 启动类

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.ConfigurableApplicationContext;

/**
 * 启动类
 **/
@SpringBootApplication
@MapperScan("com.qiuming.hello.mapper")
public class HelloApplication {

    public static void main(String[] args) {
        ConfigurableApplicationContext context = SpringApplication.run(HelloApplication.class);
    }
}

4. 创建实体类

import lombok.Data;

/**
 * 员工实体类
 * @author qiuming
 * @date 2021/03/18 17:29
 **/
@Data
public class Employee {
    private Long id;
    private String name;
    private Integer age;
    private Boolean sex;
    private String address;
}

5. 控制层提供接口

import com.qiuming.hello.domain.Employee;
import com.qiuming.hello.service.HelloService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
public class HelloController {

    @Autowired
    private HelloService helloService;

    @GetMapping("/listAll")
    public List<Employee> listAllEmps() {
        List<Employee> employees = helloService.listAllEmps();
        return employees;
    }

    @PostMapping("/saveEmp")
    public boolean saveEmp(@RequestBody Employee employee) {
        try {
            helloService.saveEmp(employee);
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    @GetMapping("/deleteEmp/{id}")
    public boolean deleteEmpByEmpId(@PathVariable("id")Long id){
        try {
            helloService.deleteEmpByEmpId(id);
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    @GetMapping("/getByEmpId/{id}")
    public Employee getByEmpId(@PathVariable("id") Long id){
        return helloService.getByEmpId(id);
    }
}

6. 服务层处理业务

import com.qiuming.hello.domain.Employee;

import java.util.List;

public interface HelloService {
    List<Employee> listAllEmps();

    void saveEmp(Employee employee);

    void deleteEmpByEmpId(Long id);

    Employee getByEmpId(Long id);
}

import com.qiuming.hello.domain.Employee;
import com.qiuming.hello.mapper.HelloMapper;
import com.qiuming.hello.service.HelloService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * 服务层
 **/
@Service
public class HelloServiceImpl implements HelloService {

    @Autowired
    private HelloMapper helloMapper;

    @Override
    public List<Employee> listAllEmps() {
        return helloMapper.listAllEmps();
    }

    @Override
    public void saveEmp(Employee employee) {
        if (employee.getId() == null) {
            helloMapper.saveEmp(employee);
        }else {
            updateEmp(employee);
        }

    }

    @Override
    public void deleteEmpByEmpId(Long id) {
        helloMapper.deleteEmpByEmpId(id);
    }

    @Override
    public Employee getByEmpId(Long id) {
        return helloMapper.getByEmpId(id);
    }

    /**
     * 修改方法
     * @param employee
     */
    private void updateEmp(Employee employee) {
        helloMapper.updateEmp(employee);
    }
}

7. 数据层交互数据

import com.qiuming.hello.domain.Employee;

import java.util.List;

/**
 * 数据层，负责数据的读写
 */
public interface HelloMapper {
    List<Employee> listAllEmps();

    void saveEmp(Employee employee);

    void updateEmp(Employee employee);

    void deleteEmpByEmpId(Long id);

    Employee getByEmpId(Long id);
}

<resultMap id="baseResultMap" type="com.qiuming.hello.domain.Employee">
    <id column="EMPLOYEE_ID" property="id"/>
    <result column="EMPLOYEE_NAME" property="name"/>
    <result column="EMPLOYEE_AGE" property="age"/>
    <result column="EMPLOYEE_SEX" property="sex"/>
    <result column="EMPLOYEE_ADDRESS" property="address"/>
</resultMap>

<insert id="saveEmp" parameterType="com.qiuming.hello.domain.Employee">
    insert into "T_EMPLOYEE" ("EMPLOYEE_NAME", "EMPLOYEE_AGE", "EMPLOYEE_SEX", "EMPLOYEE_ADDRESS")
    VALUES (#{name},
            #{age},
            #{sex},
            #{address})
</insert>

<update id="updateEmp" parameterType="com.qiuming.hello.domain.Employee">
    update T_EMPLOYEE
    set EMPLOYEE_NAME    = #{name},
        EMPLOYEE_AGE     = #{age},
        EMPLOYEE_SEX     = #{sex},
        EMPLOYEE_ADDRESS = #{address}
    where EMPLOYEE_ID = #{id}
</update>

<delete id="deleteEmpByEmpId">
    delete
    from T_EMPLOYEE
    where EMPLOYEE_ID = #{id}
</delete>
<select id="listAllEmps" resultMap="baseResultMap">
    select EMPLOYEE_ID,
           EMPLOYEE_NAME,
           EMPLOYEE_AGE,
           EMPLOYEE_SEX,
           EMPLOYEE_ADDRESS
    from T_EMPLOYEE
</select>

<select id="getByEmpId" resultMap="baseResultMap">
    select EMPLOYEE_ID,
           EMPLOYEE_NAME,
           EMPLOYEE_AGE,
           EMPLOYEE_SEX,
           EMPLOYEE_ADDRESS
    from T_EMPLOYEE
    where EMPLOYEE_ID = #{id}
</select>

三. 测试

使用postman测试，可以新建文本，重命名为hello oracle.postman_collection.json，拷贝进去导入即可

{
	"info": {
		"_postman_id": "a68ddc58-eb2e-4eeb-bdf9-7dd4da620684",
		"name": "hello oracle",
		"schema": "https://schema.getpostman.com/json/collection/v2.1.0/collection.json"
	},
	"item": [
		{
			"name": "查询所有",
			"request": {
				"method": "GET",
				"header": [],
				"url": {
					"raw": "http://localhost:2011/listAll",
					"protocol": "http",
					"host": [
						"localhost"
					],
					"port": "2011",
					"path": [
						"listAll"
					]
				}
			},
			"response": []
		},
		{
			"name": "添加数据",
			"request": {
				"method": "POST",
				"header": [],
				"body": {
					"mode": "raw",
					"raw": "{\r\n    \"name\":\"秋名\",\r\n    \"age\":21,\r\n    \"sex\":1,\r\n    \"address\":\"非洲\"\r\n}",
					"options": {
						"raw": {
							"language": "json"
						}
					}
				},
				"url": {
					"raw": "http://localhost:2011/saveEmp",
					"protocol": "http",
					"host": [
						"localhost"
					],
					"port": "2011",
					"path": [
						"saveEmp"
					]
				}
			},
			"response": []
		},
		{
			"name": "查询一条",
			"request": {
				"method": "GET",
				"header": [],
				"url": {
					"raw": "http://localhost:2011/getByEmpId/1",
					"protocol": "http",
					"host": [
						"localhost"
					],
					"port": "2011",
					"path": [
						"getByEmpId",
						"1"
					]
				}
			},
			"response": []
		},
		{
			"name": "删除数据",
			"request": {
				"method": "GET",
				"header": [],
				"url": {
					"raw": "http://localhost:2011/deleteEmp/1",
					"protocol": "http",
					"host": [
						"localhost"
					],
					"port": "2011",
					"path": [
						"deleteEmp",
						"1"
					]
				}
			},
			"response": []
		}
	]
}