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

来源：互联网发布：锐捷交换机ip与mac绑定编辑：程序博客网时间：2024/05/15 07:15

目前我的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: /springbootdemosecurity:  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;@Entitypublic class SinotransOrg {@Id@GeneratedValueprivate 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 +@ResponseBodypublic 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"下的所有controllerpublic 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。首先在火狐中配置了这个插件。怎么配置百度就可以了。

然后打开这个：

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

0 0