springboot使用jdbc连接oracle数据库

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

?

1 2 3 4 5

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

　　

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73

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当成真实的一列供外部查询语句查询。