数据库连接(二) JdbcTemplate

使用spring框架中的JdbcTemplate 来连接处理数据库, JdbcTemplate接收来自 druid 的 DataSource

环境配置 spring的包

libs/commons-logging-1.2.jar
libs/druid-1.1.20.jar
libs/hamcrest-core-1.3.jar   //junit的依赖
libs/junit-4.12.jar
libs/mysql-connector-java-8.0.21.jar
libs/spring-beans-5.2.5.RELEASE.jar
libs/spring-context-5.2.5.RELEASE.jar
libs/spring-core-5.2.5.RELEASE.jar
libs/spring-expression-5.2.5.RELEASE.jar
libs/spring-jdbc-5.2.5.RELEASE.jar    //jdbcTemple所在包
libs/spring-tx-5.2.5.RELEASE.jar

.queryForMap   

/*
     *  .queryForMap
     * 只能查询结果为1 条,否则报错 org.springframework.dao.IncorrectResultSizeDataAccessException: Incorrect result size: expected 1, actual 2
     */
    @Test
    public void Test() throws Exception {
        Properties properties = new Properties();
        properties.load(ClassLoader.getSystemResourceAsStream("resources/db_server.properties"));
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        String sql="select id,NameSpace from NameSpace where id between ? and ?";
        Map<String, Object> stringObjectMap = jdbcTemplate.queryForMap(sql,1,1);
        System.out.println(stringObjectMap);
    }

.queryForList

 /*
     *  .queryForList
     *  可以查出多个结果 list包含map
     */
    @Test
    public void Test2() throws Exception {
        Properties properties = new Properties();
        properties.load(ClassLoader.getSystemResourceAsStream("resources/db_server.properties"));
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        String sql="select id,NameSpace from NameSpace where id between ? and ?";
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql, 2, 7);
        System.out.println(maps);
    }

.queryForObject  聚合查询

/*
     *  .queryForObject
     *  查询出聚合操作
     */
    @Test
    public void Test3() throws Exception {
        Properties properties = new Properties();
        properties.load(ClassLoader.getSystemResourceAsStream("resources/db_server.properties"));
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        String sql="select count(1) from NameSpace";
        Integer integer = jdbcTemplate.queryForObject(sql, Integer.class);
        System.out.println(integer);
    }

.query 查询结果封装成对象

/*
     *  .query
     *  查询出的结果 封装成对象 需要重写new RowMapper<>() 自定义RowMapper<>
     */
    @Test
    public void Test4() throws Exception {
        Properties properties = new Properties();
        properties.load(ClassLoader.getSystemResourceAsStream("resources/db_server.properties"));
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        String sql="select id,NameSpace from NameSpace where id between ? and ?";
        //jdbcTemplate.queryForObject()
        List<NameSpace> query = jdbcTemplate.query(sql,new Integer[]{2,5} , new RowMapper<NameSpace>() {
            @Override
            public NameSpace mapRow(ResultSet resultSet, int i) throws SQLException {
                Integer id = resultSet.getInt("id");
                String nameSpace1 = resultSet.getString("NameSpace");
                NameSpace nameSpace = new NameSpace(id,nameSpace1);
                return nameSpace;
            }
        });
        System.out.println(query);
    }

.query 查询结果封装成对象 BeanPropertyRowMapper<>

/*
     *  .query
     *  查询出的结果 封装成对象 需要重写new RowMapper<>(),
     *  使用new BeanPropertyRowMapper<>(NameSpace.class) 代替 RowMapper
     *  其中分装的对象类 必须要有 误参构造方法 否则报错
     *  No default constructor found; nested exception is java.lang.NoSuchMethodException:
     */
    @Test
    public void Test5() throws Exception {
        Properties properties = new Properties();
        properties.load(ClassLoader.getSystemResourceAsStream("resources/db_server.properties"));
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        String sql="select id,NameSpace from NameSpace where id between ? and ?";
        List<NameSpace> query=jdbcTemplate.query(sql,new Integer[]{2,5},new BeanPropertyRowMapper<>(NameSpace.class));
        System.out.println(query);
    }