利用springboot对oracle中数据库表的增删改查crud

最新推荐文章于 2022-11-01 14:29:55 发布

是金子早晚要花光

最新推荐文章于 2022-11-01 14:29:55 发布

阅读量1.6w

点赞数 4

分类专栏： spring cloud

本文链接：https://blog.csdn.net/shunzi1046/article/details/58132178

版权

spring cloud 专栏收录该内容

17 篇文章 0 订阅

订阅专栏

目前我的oracle数据库中有个sinotrans_org表

首先是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>

    <groupId>com.jxust</groupId>
    <artifactId>spirngbootdemo2</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>

    <name>spirngbootdemo</name>
    <description>Demo project for Spring Boot</description>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.4.2.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
    </properties>

    <dependencies>
        <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>
        <dependency>
    		<groupId>org.springframework.boot</groupId>
    		<artifactId>spring-boot-configuration-processor</artifactId>
    		<optional>true</optional>
		</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-data-rest</artifactId>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.15</version>
        </dependency>
        <!-- <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency> -->
        <dependency>
			<groupId>com.oracle</groupId>
			<artifactId>ojdbc6</artifactId>
			<version>11.1.0.7.0</version>
		</dependency>
		<dependency>  
        	<groupId>org.springframework.boot</groupId>  
        	<artifactId>spring-boot-starter-security</artifactId>  
    	</dependency> 
    	<dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.2.8</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis-spring</artifactId>
            <version>1.2.2</version>
         </dependency>
    </dependencies>

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

springboot的优点就是集中式配置。所以几乎是没有烦人的配置文件。

这里是我们的集中配置文件：在resources目录下的文件

application.yml文件：

spring:
  profiles:
    active: a
  datasource:
      driver-class-name: oracle.jdbc.OracleDriver
      url: jdbc:oracle:thin:@ip:1521:xxx
      username: xxx
      password: xxx

  jpa:
    hibernate:
      ddl-auto: update
    show-sql: true

以及application-a.yml，对应上个yml文件中的profiles配置

server:
  port: 8084
  context-path: /springbootdemo
security:
  user: 
      name: admin   
      password: admin  
      
#mybatis:
   #typeAliasesPackage: com.firstboot.entity  
   #mapperLocations: classpath:mapper/*.xml

因为我在pom文件中配置了springboot的安全jar。所以需要设置用户名密码，否则你进入不了页面。下面是整体结构，很简单

然后是具体类：

实体类：sinotransOrg.java

package com.firstboot.entity;

import java.util.List;

import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.OneToMany;

import com.alibaba.fastjson.annotation.JSONField;

@Entity
public class SinotransOrg {
	@Id
	@GeneratedValue
	private Integer orgId;
	private String orgPathCode;
	private String shortNameCn;
	private String fullNameCn;
	private String shortNameEn;
	private String fullNameEn;
	private String mnemonicCode;
	private String govOrgCode;
	private String city;
	private String address;
	private String postCode;
	private Integer parentOrg;
	@JSONField(format="yyyy-MM-dd HH:mm")
	private java.sql.Timestamp createTime=null;
	private java.lang.Integer creator=null;
	private java.lang.Integer modifier=null;
	@JSONField(format="yyyy-MM-dd HH:mm")
	private java.sql.Timestamp modifyTime=null;
	
	/*@OneToMany(fetch = FetchType.LAZY,mappedBy = "sinotransOrg")  
    private List<Department> departments;  */
	
	//必须有构造
	public SinotransOrg() {
	}
	
	/*public List<Department> getDepartments() {
		return departments;
	}

	public void setDepartments(List<Department> departments) {
		this.departments = departments;
	}*/

	public Integer getOrgId() {
		return orgId;
	}
	public void setOrgId(Integer orgId) {
		this.orgId = orgId;
	}
	public String getOrgPathCode() {
		return orgPathCode;
	}
	public void setOrgPathCode(String orgPathCode) {
		this.orgPathCode = orgPathCode;
	}
	public String getShortNameCn() {
		return shortNameCn;
	}
	public void setShortNameCn(String shortNameCn) {
		this.shortNameCn = shortNameCn;
	}
	public String getFullNameCn() {
		return fullNameCn;
	}
	public void setFullNameCn(String fullNameCn) {
		this.fullNameCn = fullNameCn;
	}
	public String getShortNameEn() {
		return shortNameEn;
	}
	public void setShortNameEn(String shortNameEn) {
		this.shortNameEn = shortNameEn;
	}
	public String getFullNameEn() {
		return fullNameEn;
	}
	public void setFullNameEn(String fullNameEn) {
		this.fullNameEn = fullNameEn;
	}
	public String getMnemonicCode() {
		return mnemonicCode;
	}
	public void setMnemonicCode(String mnemonicCode) {
		this.mnemonicCode = mnemonicCode;
	}
	public String getGovOrgCode() {
		return govOrgCode;
	}
	public void setGovOrgCode(String govOrgCode) {
		this.govOrgCode = govOrgCode;
	}
	public String getCity() {
		return city;
	}
	public void setCity(String city) {
		this.city = city;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	
	public String getPostCode() {
		return postCode;
	}
	public void setPostCode(String postCode) {
		this.postCode = postCode;
	}
	public Integer getParentOrg() {
		return parentOrg;
	}
	public void setParentOrg(Integer parentOrg) {
		this.parentOrg = parentOrg;
	}
	public java.sql.Timestamp getCreateTime() {
		return createTime;
	}
	public void setCreateTime(java.sql.Timestamp createTime) {
		this.createTime = createTime;
	}
	public java.lang.Integer getCreator() {
		return creator;
	}
	public void setCreator(java.lang.Integer creator) {
		this.creator = creator;
	}
	public java.lang.Integer getModifier() {
		return modifier;
	}
	public void setModifier(java.lang.Integer modifier) {
		this.modifier = modifier;
	}
	public java.sql.Timestamp getModifyTime() {
		return modifyTime;
	}
	public void setModifyTime(java.sql.Timestamp modifyTime) {
		this.modifyTime = modifyTime;
	}
	
	
	
}

dao类 :SinotransOrgRepository接口

package com.firstboot.dao;
import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;

import com.firstboot.entity.SinotransOrg;  

public interface SinotransOrgRepository extends JpaRepository<SinotransOrg,Integer>{
	//通过shortNameCn查询，中文简称
	public List<SinotransOrg> findByShortNameCn(String shortNameCn);
	//通过fullNameCn查询，中文全称
	public List<SinotransOrg> findByFullNameCn(String fullNameCn);
	//通过shortNameEn查询，英文简称
	public List<SinotransOrg> findByShortNameEn(String shortNameEn);
	//通过fullNameEn查询，英文全称
	public List<SinotransOrg> findByFullNameEn(String fullNameEn);
	//通过mnemonicCode查询，助记码
	public List<SinotransOrg> findByMnemonicCode(String mnemonicCode);
	
	
	
	
}

控制器：

SinotransOrgController.java

package com.firstboot.controller;

import java.sql.Timestamp;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;

import com.firstboot.dao.SinotransOrgRepository;
import com.firstboot.entity.SinotransOrg;


@RestController  //same as @Controller +@ResponseBody
public class SinotransOrgController {

    
    @Autowired
    private SinotransOrgRepository sinotransOrgRepository;
    
    
    /** 
     * 请求内容是一个json串,spring会自动把他和我们的参数bean对应起来,不过要加@RequestBody注解 
     *  
     * @param sinotransOrg 
     * @return 
     */  
    @RequestMapping(value = "/sinotransOrg3", method = { RequestMethod.POST, RequestMethod.GET })  
    public SinotransOrg sinotransOrgAdd(@RequestBody SinotransOrg sinotransOrg) { 
    		return sinotransOrgRepository.save(sinotransOrg);
    }  
    //===========================================================
    /**
     * 查询所有
     * @return
     */
    @GetMapping(value = "/sinotransOrg")
    @ResponseBody
    public List<SinotransOrg> sinotransOrgList(){
    	return sinotransOrgRepository.findAll();
    }
    
    /**
     * 增加一个组织机构
     */
    @PostMapping(value = "/sinotransOrg")
    public SinotransOrg sinotransOrgAdd(@RequestParam("orgPathCode") String orgPathCode,
                        @RequestParam("shortNameCn") String shortNameCn,
                        @RequestParam("fullNameCn") String fullNameCn,
                        @RequestParam("shortNameEn") String shortNameEn,
                        @RequestParam("fullNameEn") String fullNameEn,
                        @RequestParam("mnemonicCode") String mnemonicCode,
                        @RequestParam("govOrgCode") String govOrgCode,
                        @RequestParam("city") String city,
                        @RequestParam("address") String address,
                        @RequestParam("postCode") String postCode,
                        @RequestParam("parentOrg") Integer parentOrg,
                        @RequestParam("createTime") Timestamp createTime,
                        @RequestParam("creator") Integer creator,
                        @RequestParam("modifier") Integer modifier,
                        @RequestParam("modifyTime") Timestamp modifyTime) {
    	SinotransOrg sinotransOrg = new SinotransOrg();
    	
    	sinotransOrg.setOrgPathCode(orgPathCode);
    	sinotransOrg.setShortNameCn(shortNameCn);
    	sinotransOrg.setFullNameCn(fullNameCn);
    	sinotransOrg.setShortNameEn(shortNameEn);
    	sinotransOrg.setFullNameEn(fullNameEn);
    	sinotransOrg.setMnemonicCode(mnemonicCode);
    	sinotransOrg.setGovOrgCode(govOrgCode);
    	sinotransOrg.setCity(city);
    	sinotransOrg.setAddress(address);
    	sinotransOrg.setPostCode(postCode);
    	sinotransOrg.setParentOrg(parentOrg);
    	sinotransOrg.setCreateTime(createTime);
    	sinotransOrg.setCreator(creator);
    	sinotransOrg.setModifier(modifier);
    	sinotransOrg.setModifyTime(modifyTime);

        return sinotransOrgRepository.save(sinotransOrg);
    }
	
    /**
     * 更新一个组织机构
     * @return
     */
    @PutMapping(value = "/sinotransOrg/{orgId}")
    public SinotransOrg sinotransOrgUpdate(@PathVariable("orgId") Integer orgId,
		    		@RequestParam("orgPathCode") String orgPathCode,
		            @RequestParam("shortNameCn") String shortNameCn,
		            @RequestParam("fullNameCn") String fullNameCn,
		            @RequestParam("shortNameEn") String shortNameEn,
		            @RequestParam("fullNameEn") String fullNameEn,
		            @RequestParam("mnemonicCode") String mnemonicCode,
		            @RequestParam("govOrgCode") String govOrgCode,
		            @RequestParam("city") String city,
		            @RequestParam("address") String address,
		            @RequestParam("postCode") String postCode,
		            @RequestParam("parentOrg") Integer parentOrg,
		            @RequestParam("createTime") Timestamp createTime,
		            @RequestParam("creator") Integer creator,
		            @RequestParam("modifier") Integer modifier,
		            @RequestParam("modifyTime") Timestamp modifyTime) {
    	SinotransOrg sinotransOrg = new SinotransOrg();
    	
    	sinotransOrg.setOrgId(orgId);
    	sinotransOrg.setOrgPathCode(orgPathCode);
    	sinotransOrg.setShortNameCn(shortNameCn);
    	sinotransOrg.setFullNameCn(fullNameCn);
    	sinotransOrg.setShortNameEn(shortNameEn);
    	sinotransOrg.setFullNameEn(fullNameEn);
    	sinotransOrg.setMnemonicCode(mnemonicCode);
    	sinotransOrg.setGovOrgCode(govOrgCode);
    	sinotransOrg.setCity(city);
    	sinotransOrg.setAddress(address);
    	sinotransOrg.setPostCode(postCode);
    	sinotransOrg.setParentOrg(parentOrg);
    	sinotransOrg.setCreateTime(createTime);
    	sinotransOrg.setCreator(creator);
    	sinotransOrg.setModifier(modifier);
    	sinotransOrg.setModifyTime(modifyTime);

        return sinotransOrgRepository.save(sinotransOrg);
    }
    /**
     * 删除一个组织机构
     * @param id
     */
    @DeleteMapping(value = "/sinotransOrg/{orgId}")
    public void sinotransOrgDelete(@PathVariable("orgId") Integer orgId) {
    	sinotransOrgRepository.delete(orgId);
    }
    
    /**
     * 通过shortNameCn查询，中文简称
     * @param shortNameCn
     * @return
     */
    @GetMapping(value = "/sinotransOrg/shortNameCn/{shortNameCn}")
    public List<SinotransOrg> sinotransOrgListByShortNameCn(
    		@PathVariable("shortNameCn") String shortNameCn) {
        return sinotransOrgRepository.findByShortNameCn(shortNameCn);
    }
    /**
     * 通过fullNameCn查询，中文全称
     * @param fullNameCn
     * @return
     */
    @GetMapping(value = "/sinotransOrg/fullNameCn/{fullNameCn}")
    public List<SinotransOrg> sinotransOrgListByFindByFullNameCn(
    		@PathVariable("fullNameCn") String fullNameCn) {
    	return sinotransOrgRepository.findByFullNameCn(fullNameCn);
    }
    /**
     * 通过shortNameEn查询，英文简称
     * @param shortNameEn
     * @return
     */
    @GetMapping(value = "/sinotransOrg/shortNameEn/{shortNameEn}")
    public List<SinotransOrg> sinotransOrgListByShortNameEn(
    		@PathVariable("shortNameEn") String shortNameEn) {
    	return sinotransOrgRepository.findByShortNameEn(shortNameEn);
    }
    /**
     * 通过fullNameEn查询，英文全称
     * @param fullNameEn
     * @return
     */
    @GetMapping(value = "/sinotransOrg/fullNameEn/{fullNameEn}")
    public List<SinotransOrg> sinotransOrgListByFullNameEn(
    		@PathVariable("fullNameEn") String fullNameEn) {
    	return sinotransOrgRepository.findByFullNameEn(fullNameEn);
    }
    /**
     * 通过mnemonicCode查询，助记码
     * @param mnemonicCode
     * @return
     */
    @GetMapping(value = "/sinotransOrg/mnemonicCode/{mnemonicCode}")
    public List<SinotransOrg> sinotransOrgListByMnemonicCode(
    		@PathVariable("mnemonicCode") String mnemonicCode) {
    	return sinotransOrgRepository.findByMnemonicCode(mnemonicCode);
    }
    
    
}

入口类Application.java类：

package com.firstboot;


import java.util.List;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.http.converter.HttpMessageConverter;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurerAdapter;

import com.alibaba.fastjson.serializer.SerializerFeature;
import com.alibaba.fastjson.support.config.FastJsonConfig;
import com.alibaba.fastjson.support.spring.FastJsonHttpMessageConverter;


/**
 * @EnableAutoConfiguration:spring boot的注解，一般只用于主类，
 * 是无xml配置启动的关键部分,明确指定了扫描包的路径为其修饰的主类的包（这也就是为什么主类要放在根包路径下的原因）
 * 
 * @ComponentScan 进行包的扫描，扫描路径由@EnableAutoConfiguration指定了
 * 
 * 主类要位于根包路径下，方便之后的扫描(We generally recommend that you locate your main application class in a root package above other classes.)
 */
@SpringBootApplication  //相当于 @Configuration+@EnableAutoConfiguration+@ComponentScan
//启动swagger注解启动该注解使得用在controller中的swagger注解生效，覆盖的范围由@ComponentScan的配置来指定，这里默认指定为根路径"com.firstboot"下的所有controller
public class Application extends WebMvcConfigurerAdapter {
	/**
	* spring boot的入口，在整个子项目在内，
	* 只能有一个main方法，否则spring boot启动不起来
	*/
    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }
    /**
     *  1.需要先定义一个convert转换消息的对象；
		2.添加fastJson的配置信息，比如：是否要格式化返回的json数据；
		3.在convert中添加配置信息
		4.将convert添加到converters中
     */
    @Override
    public void configureMessageConverters(List<HttpMessageConverter<?>> converters) {
    	// TODO Auto-generated method stub
    	super.configureMessageConverters(converters);
    	//1.需要先定义一个convert转换消息的对象；
    	FastJsonHttpMessageConverter fastConverter = new FastJsonHttpMessageConverter();
    	//2.添加fastJson的配置信息，比如：是否要格式化返回的json数据；
    	FastJsonConfig fastJsonConfig = new FastJsonConfig();
    	fastJsonConfig.setSerializerFeatures(SerializerFeature.PrettyFormat);
    	//3.在convert中添加配置信息
    	fastConverter.setFastJsonConfig(fastJsonConfig);
    	//4.将convert添加到converters中
    	converters.add(fastConverter);
    	
    }
    
    
    
}

代码就这么点。就可以对数据库crud了。注意的是，我的数据库中的字段名称实际是带下划线的。比如parent_org，对于这样的字段，springboot中约定你必须在

实体类中设置为 private String parentOrg；就是第一个小写。第二个首字母大写。这样就自动匹配上了你的数据库字段。否则你还需要在实体类中的每个字段上

配置@Column（..），显然没必要。

接下来是测试，直接在Application.java中run as ---java application,即可，因为springboot内部依赖了tomcat，不用你再启动tomcat服务：

然后使用一个比浏览器更好的工具。火狐浏览器的插件Httprequester。首先在火狐中配置了这个插件。怎么配置百度就可以了。

然后打开这个：

相信你知道下来怎么做了。这个插件很好用的

是金子早晚要花光

关注

4
点赞
踩
12

收藏

觉得还不错? 一键收藏
5
评论
利用springboot对oracle中数据库表的增删改查crud

目前我的oracle数据库中有个sinotrans_org表首先是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/
复制链接

扫一扫