JdbcTemplate的使用大全

1、判断数据库是否获得连接

@Test
    public void test() throws SQLException {

        DataSource bean = ioc.getBean(DataSource.class);
        Connection connection = bean.getConnection();
        System.out.println(connection);
    }

2、sql 语句的按位更新、或插入

@Test
    public void test02(){
        //1、从容器中获取JdbcTemplate进行操作
        String sql = "update employee set salary=? where emp_id=?";
        int update = jdbcTemplate.update(sql, 1300,5);
        System.out.println(update);
    }

3、sql 语句的批量插入

 @Test
    public void test03(){
        String sql = "INSERT  INTO `employee`(`emp_name`,`salary`) VALUES (?,?)";
        List<Object[]> args = new ArrayList<>();

        args.add(new Object[]{"张三",9989.98});
        args.add(new Object[]{"李四",19989.98});
        jdbcTemplate.batchUpdate(sql,args);
    }

4、查询单个对象

• 数据字段与数据库相同

BeanPropertyRowmapper用来将javaBean属性和查出的数据一一映射

       @Test
        public void test04(){
            String sql = "select * from employee where emp_id=?";
             Employee employee = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Employee.class), 7);
        }

• 数据字段与数据库不同 需重写方法

 @Test
            public void test04(){
                String sql = "select * from employee where emp_id=?";
                 Employee employee = jdbcTemplate.queryForObject(sql,  new RowMapper<Employee >(){
                @Override
                 public Employee mapRow(ResultSet rs, int index)throws SQLException {
			Employee temp = new Employee ();
			temp.setAccountid(rs.getInt("id"));
			temp.setLoginname(rs.getString("name"));
                       return temp;     
                       }
                       		}, 7);
            }

4-2、查询集合对象

@Test
            public void test04(){
                String sql = "select * from employee where emp_id=?";
                 List<helgolandLog>  employees = jdbcTemplate.query(sql,  new RowMapper<Employee >(){
                @Override
                 public Employee mapRow(ResultSet rs, int index)throws SQLException {
			Employee temp = new Employee ();
			temp.setAccountid(rs.getInt("id"));
			temp.setLoginname(rs.getString("name"));
                       return temp;     
                       }
                       		}, 7);
            }

5、使用带有具名参数的SQL语句插入一条记录，并以Map形式传入参数值

@Test
    public void test07(){
        String sql = "INSERT  INTO `employee`(`emp_name`,`salary`) VALUES (:empName,:salary)";
        Map<String, Object> map = new HashMap<>();
        map.put("empName", "韩总");
        map.put("salary", 40000.0);
        //如果支持具名参数的功能。JdbcTemplate就不能用了。
        /*MapSqlParameterSource source = new MapSqlParameterSource(map);
        namedTemplate.update(sql, source);*/
        namedTemplate.update(sql, map);
    }

6、以SqlParameterSource形式传入参数值

@Test
    public void test08(){
        String sql = "INSERT  INTO `employee`(`emp_name`,`salary`) VALUES (:empName,:salary)";
        Employee employee = new Employee(null, "韩五", 499998.00);
        //如果支持具名参数的功能。JdbcTemplate就不能用了。
        //SqlParameterSource
        //BeanPropertySqlParameterSource
        //可以直接将Bean的属性映射参数进行处理
        namedTemplate.update(sql, new BeanPropertySqlParameterSource(employee));

    }

7、自动装配 JdbcTemplate 对象

 @Test
    public void test09(){
        EmployeeDao bean = ioc.getBean(EmployeeDao.class);
        Employee employee = new Employee(null, "韩五11", 499998.00);
        bean.insert(employee);
    }