JdbcTemplate

环境搭建

依赖

        <!-- Oracle11g 连接驱动依赖。这里是个坑，官方提供的不能用 -->
        <dependency>
            <groupId>cn.easyproject</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>12.1.0.2.0</version>
        </dependency>

        <!-- jdbcTemplate -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

yml 

server:
  port: 7100

spring:
  datasource:
    driver-class-name: oracle.jdbc.OracleDriver
    url: jdbc:oracle:thin:@127.0.0.1:1521:syh
    username: SYH
    password: syh

表结构

oracle与java数据类型映射关系

java类型	oracle类型
Timestamp 或者  Date	TIMESTAMP
BigDecimal	NUMBER

更新操作

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Test
    void update() {
        // 插入
        String sql = "insert into JDBC_TEMPLATE(ID, NAME, BIRTHDAY)values(?, ?, ?)";
        Date date = new Date();   // Timestamp timestamp = new Timestamp(System.currentTimeMillis());
        int insert = jdbcTemplate.update(sql, 10, "4", date);

        // 更新
        String sql1 = "UPDATE JDBC_TEMPLATE SET NAME = ? WHERE ID = ?";
        int update = jdbcTemplate.update(sql1, "护臂", 2);

        // 删除
        String sql2 = "DELETE FROM JDBC_TEMPLATE WHERE id = ?";
        int delete = jdbcTemplate.update(sql2, 1);
    }

查询操作

查询一条

    // 查询一条
    @Test
    void select1() {
        String sql = "select * from JDBC_TEMPLATE WHERE ID = 2";  
        // 必须查询出一条，否则报错 Incorrect result size: expected 1, actual 0
        Map<String, Object> map = jdbcTemplate.queryForMap(sql);

        BigDecimal id = (BigDecimal) map.get("ID");  // oracle
        int i = id.intValue();
        String name = (String) map.get("NAME");
        Date date = (Date) map.get("BIRTHDAY");
        System.out.println(i + name + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date));
    }

查询多条

    // 查询多条
    @Test
    void select2() {
        String sql = "select * from JDBC_TEMPLATE";
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);

        for (Map<String, Object> map : maps) {
            BigDecimal id = (BigDecimal) map.get("ID");  // oracle
            int i = id.intValue();
            String name = (String) map.get("NAME");
            Date date = (Date) map.get("BIRTHDAY");
            System.out.println(i + name + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date));
        }
    }

自定义类型映射器

    // 自定义类型映射器
    @Test
    void select3() {
        String sql = "select * from JDBC_TEMPLATE WHERE ID = ?";
        // sql  参数  映射
        User result = jdbcTemplate.queryForObject(sql, new Object[]{2}, new YourRowMapper());
        System.out.println(result);  // User(id=2, name=护臂, birthday=2023-12-16)
    }

    class YourRowMapper implements RowMapper<User> {
        @Override
        public User mapRow(ResultSet rs, int rowNum) throws SQLException {
            User entity = new User();
            // 字段映射
            entity.setId(rs.getInt("ID"));
            entity.setName(rs.getString("NAME"));
            entity.setBirthday(rs.getDate("BIRTHDAY"));
            return entity;
        }
    }