博客

一、添加依赖

<!--oracle-->
<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc6</artifactId>
    <version>11.2.0.4</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.15</version>
</dependency>

二、jdbc工具类

package com.thk.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class OracleJDBC {
        private String user;
        private String pass;
        private String url;

        private Connection conn = null;//连接对象
        private ResultSet rs = null;//结果集对象
        private Statement sm = null;


        /**
         * 构造函数获得数据库用户名和密码
         *
         * @param user
         * @param pass
         */
        public OracleJDBC(String user, String pass) {
            this.user = 数据库连接用户名;//数据库连接用户名
            this.pass = 数据库连接密码;//数据库连接密码
            this.url = "jdbc:oracle:thin:@数据库ip地址:1521:orcl";//数据库ip地址  orcl 为数据库名
        }

        /**
         * 连接数据库
         *
         * @return
         */
        public Connection createConnection() {

            String sDBDriver = "oracle.jdbc.driver.OracleDriver";

            try {
                Class.forName (sDBDriver).newInstance ();
                conn = DriverManager.getConnection (url, user, pass);
            } catch (Exception e) {
                System.out.println ("数据库连接失败");
                e.printStackTrace ();
            }
            return conn;
        }

        /**
         * 关闭数据库
         *
         * @param conn
         */
        public void closeConnection(Connection conn) {
            try {
                if (conn != null) {
                    conn.close ();
                }
            } catch (Exception e) {
                System.out.println ("数据库关闭失败");
                e.printStackTrace ();
            }
        }

        /**
         * 插入数据
         *
         * @param insert 插入语句
         * @return
         */
        public int insert(String insert) {
            conn = createConnection ();
            int re = 0;
            try {
                conn.setAutoCommit (false);//事物开始

                sm = conn.createStatement ();
                re = sm.executeUpdate (insert);
                if (re < 0) {               //插入失败
                    conn.rollback ();      //回滚
                    sm.close ();
                    closeConnection (conn);
                    return re;
                }
                conn.commit ();            //插入正常
                System.out.println ("插入成功");
                sm.close ();
                closeConnection (conn);
                return re;
            } catch (Exception e) {
                e.printStackTrace ();
            }
            closeConnection (conn);
            return 0;

        }

        /**
         * 查询语句
         * 返回结果集
         *
         * @param select
         * @return
         */
        public ResultSet selectSql(String select) {
            conn = createConnection ();
            try {
                sm = conn.createStatement ();
                rs = sm.executeQuery (select);
                return rs;
            } catch (Exception e) {
                e.printStackTrace ();
            }

            return null;
        }

    }

三、测试实体类

package com.thk.domain;

import lombok.Data;

@Data
//测试实体类
public class TestDomain {
    private String ITEMNO;
    private String NO;
    private String ASUSER11;
}

四、测试controller

package com.thk.controller;

import com.thk.domain.TestDomain;
import com.thk.utils.AjaxResult;
import com.thk.utils.OracleJDBC;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

@RestController
public class Acontroller {

    @GetMapping("/test")
    public AjaxResult test1() throws Exception {
        //创建OracleJDBC工具类对象
        OracleJDBC jDBC = new OracleJDBC (null,null);
        List<TestDomain> as = new ArrayList<TestDomain> ();
        //SQL语句
        String sql = "    SELECT b.ITEMNO,b.NO,b.ASUSER11 FROM MBOM a" +
                "        LEFT JOIN MPART b ON a.CID=b.ID" +
                "        WHERE b.ITEMNO LIKE 'S%' AND b.ITEMGROUP='分总成'" +
                "        AND a.DEL !=1  AND a.WKAID!=3" +
                "        GROUP  BY b.ITEMNO,b.NO,b.ASUSER11";
        //调用查询方法
        ResultSet rs = jDBC.selectSql (sql);
        //遍历ResultSet
        while(rs.next()){
           //创建实体类对象
            TestDomain a = new TestDomain ();
            //设置值
            a.setITEMNO (rs.getString(1));
            a.setNO (rs.getString(2));
            a.setASUSER11 (rs.getString(3));
            //添加到list中
            as.add (a);
        }
        System.out.println (as);
        return AjaxResult.success (as);
    }
}

五、测试结果