Spring基础篇-JdbcTemplate

传统jdbc开发，有很多模板化代码，而为了简化，将模板代码封装起来，就是用了AOP思想，开发者只需要管住数据的关键操作，但是，与MyBatis相比，在一对一映射，一对多映射，以及动态SQL等方法，还是不足。

使用JdbcTemplate

需要先加入依赖

 <dependencies>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.3.13</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.3.27</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.29</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.11</version>
        </dependency>
    </dependencies>

加入jdbc文件

db.username=root
db.password=123456
db.url=jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Shanghai

注入java配置中

@Configurable
@PropertySource("classpath:db.properties")
public class JavaConfig {

    @Value("${db.username}")
    String username;

    @Value("${db.password}")
    String password;

    @Value("${db.url}")
    String url;

    @Bean
    DataSource dataSource(){
        DruidDataSource druidDataSource = new DruidDataSource();

        druidDataSource.setUsername(username);
        druidDataSource.setPassword(password);
        druidDataSource.setUrl(url);

        return druidDataSource;
    }

    @Bean
    JdbcTemplate jdbcTemplate(){
        return new JdbcTemplate(dataSource());
    }
}

如此，便开始开始测试

 public static void main(String[] args) {
        AnnotationConfigApplicationContext annotationConfigApplicationContext = new AnnotationConfigApplicationContext(JavaConfig.class);

        JdbcTemplate bean = annotationConfigApplicationContext.getBean(JdbcTemplate.class);
        //DDL操作
        //  bean.execute();
        //批处理
        // bean.batchUpdate();
        //增删改
        //  bean.update();
        //查询
        //  bean.query();
        //  bean.queryForList();
        // bean.queryForObject();
        //增加
        // add(bean);
        //修改
        // update(bean);
        // delete(bean);
        // query(bean);
        //  query2(bean);
        // 列名称必须和对象属性名称保持一致
        // query3(bean);
        //  query4(bean);
        //查询一列
        // query5(bean);
        //一列多行查询
        query6(bean);
    }

private static void query6(JdbcTemplate bean) {
        List<String> list = bean.queryForList("SELECT USERNAME FROM USER ", String.class);
        for (String s : list) {
            System.out.println(s);
        }

    }

    private static void query5(JdbcTemplate bean) {
        String s = bean.queryForObject("SELECT USERNAME FROM USER WHERE ID=?", String.class, 2);
        System.out.println(s);
    }

    private static void query4(JdbcTemplate bean) {
        User users = bean.queryForObject("SELECT * FROM USER WHERE id=?", new BeanPropertyRowMapper<>(User.class), 3);
        System.out.println(users);
    }

    private static void query3(JdbcTemplate bean) {
        List<User> users = bean.query("SELECT * FROM USER WHERE ADDRESS=?", new BeanPropertyRowMapper<>(User.class), "深圳");
        for (User user : users) {
            System.out.println(user);
        }

    }

    private static void query2(JdbcTemplate bean) {
        List<User> users = bean.query("SELECT * FROM USER WHERE ADDRESS=?", new RowMapper<User>() {

            /**
             *
             * @param rs
             * @param rowNum
             * @return
             * @throws SQLException
             */
            @Override
            public User mapRow(ResultSet rs, int rowNum) throws SQLException {
                User user = new User();
                user.setAddress(rs.getString("address"));
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                return user;
            }
        }, "深圳");

        for (User user : users) {
            System.out.println(user);
        }
    }

    private static void query(JdbcTemplate bean) {
        bean.query("SELECT * FROM USER WHERE ADDRESS=?", new RowCallbackHandler() {
            /**
             * rs rs代表一行数据，直接获取即可
             * @param
             * @throws SQLException
             */
            @Override
            public void processRow(ResultSet rs) throws SQLException {
                int id = rs.getInt("ID");
                String username = rs.getString("username");
                String address = rs.getString("address");
                System.out.println(id + "-->" + username + "-->" + address);
            }
        }, "深圳");
    }

    private static void delete(JdbcTemplate bean) {

        int i = bean.update("DELETE FROM USER WHERE ID=?", 1);
        System.out.println(i);
    }

    private static void update(JdbcTemplate bean) {
        int i = bean.update("update user set address =? where id =?;", "广州", 1);
        System.out.println(i);
    }

    private static void add(JdbcTemplate bean) {
        int i = bean.update("insert into  user (username,address) value(?,?)", "张三", "深圳");
        System.out.println(i);
    }