springboot使用jdbc连接oracle数据库

implementation group: 'com.oracle', name: 'ojdbc6', version: '12.1.0.1-atlassian-hosted'

db:
  driverClassName: oracle.jdbc.driver.OracleDriver
  username: test
  password: test1234
  url: jdbc:oracle:thin:@192.168.1.112:1521:ORCL

　　

import cn.com.micro.es.config.DBConfig;
import cn.com.micro.es.dto.RoleDTO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

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


@Service
public class RoleService {

    @Autowired
    private DBConfig dbConfig;


    public List<RoleDTO> findRoles(){
        List<RoleDTO> list = new ArrayList<>();
        Connection conn = null;
        Statement statement = null;
        ResultSet rs = null;
        try {
            Class.forName(dbConfig.getDriverClassName());
            // 2.获得数据库链接
            conn = DriverManager.getConnection(dbConfig.getUrl(), dbConfig.getUsername(), dbConfig.getPassword());
            // 3.通过数据库的连接操作数据库，实现增删改查（使用Statement类）
            String sql = "SELECT id,code,name,systemic FROM MIS.ROLES ORDER BY ID DESC";
            statement = conn.createStatement();
            rs = statement.executeQuery(sql);
            while (rs.next()) {
                Long id = rs.getLong("id");
                String code = rs.getString("code");
                String name = rs.getString("name");
                Integer systemic = rs.getInt("systemic");
                RoleDTO roleDTO = new RoleDTO();
                roleDTO.setId(id);
                roleDTO.setCode(code);
                roleDTO.setName(name);
                roleDTO.setSystemic(systemic);
                list.add(roleDTO);
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(rs != null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(statement != null){
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return list;
    }
}

　

查询表的前10条数据

oracle 的rownum<=10 会取出该表的前10条数据。

一般情况:select * from test where rownum<=10

但是如果使用了order by 查询出来的就不是你想要的前十条

statedate 该字段为test表的时间字段，按时间排序时使用

错误:

select * from test where rownum<=10 order by statedate desc 该查询会先取出test表中的前10条，然后进行排序

 

正确

select * from (select * from test order by statedate desc ) where rownum<=10 该查询会先将test表进行排序再取出前10条

 

分页查询：

select rn,last_name,salary from(
       select rownum rn,last_name,salary from(
              select last_name,salary from employees order by salary desc
       )
) where rn>10 and rn<=20

因为rownum只能用<,<=，所以将rn当成真实的一列供外部查询语句查询。