【Spring】JdbcTemplate常用方法使用示例

忘是亡心i 2021-09-21 02:48 348阅读 0赞

### 文章目录 ###

*   *  update
     *  query
     *   *  queryForMap
         *  queryForObject
         *  queryForRowSet
         *  queryForList

--------------------

## update ##

update方法一般用来进行增删改.  
**常用的几个方法如下:**  
![在这里插入图片描述][20200209164542441.png]

//update(String sql) ==> int
            String sql = "update t_score set chiese = 95 where id =1";
            int n = template.update(sql);
    
    
            //update(String sql, Object... args) ==> int
            String sql2 = "update t_score set chiese = ? where id = ?";
            int n2 = template.update(sql2, 95, 1);
    
    
            //update(String,Object[] args, int[] argTypes) ==> int
            String sql3 = "update t_score set chinese = ? , math = ? where id = ? ";
            Object[] args = new Object[]{ 55, 80, 2};
            int[] argTypes = new int[]{ Types.INTEGER, Types.INTEGER, Types.INTEGER}; // java.sql.Types
            int n3 = template.update(sql3, args, argTypes);

## query ##

query方法主要用来查询, 在JdbcTemplate中常用的查询方法有个queryForMap、queryForObject、queryForRowSet、queryForList. 使用示例如下:

### queryForMap ###

//queryForMap(String sql) ==> Map<String, Object>
            Map<String, Object> score_map = template.queryForMap("select * from t_score where id=1");
            System.out.println(score_map);//{id=1, sid=20181515114, chinese=54, math=54, english=96}
    
    
            //queryForMap(String sql,Object... args);
            Map<String, Object> score_map2 = template.queryForMap("select * from t_score where id=?", 1);
            System.out.println(score_map2);//{id=1, sid=20181515114, chinese=54, math=54, english=96}
    
    
            //queryForMap(String sql ,Object[] args,int[] argTypes) ==> Map<String, Object>
            Object[] args = new Object[]{ 1};
            int[] argsType = new int[]{ Types.INTEGER};
            Map<String, Object> score_map3 = template.queryForMap("select * from t_score where id=?", args, argsType);
            System.out.println(score_map3);//{id=1, sid=20181515114, chinese=54, math=54, english=96}

### queryForObject ###

//queryForObject(String sql,class<T> requireType); ==> T
            int total = template.queryForObject("select (chinese+math+english) as total from t_score where id=1",Integer.class);
            System.out.println(total);//204
    
    
            //queryForObject(String sql,Object[] args,class<T> requireType); ==> T
            Object[] args = new Object[]{ 1};
            Integer total = template.queryForObject("select (chinese+math+english) as total from t_score where id=?", args, Integer.class);
            System.out.println(total);//204
    
    
            //queryForObject(String sql, Object[] args,int[] argTypes, class<T> requireType); ==> T
            Object[] args = new Object[]{ 1};
            int[] argTypes = new int[]{ Types.INTEGER};
            int total = template.queryForObject("select (chinese+math+english) as total from t_score where id=?", args, argTypes, Integer.class);
            System.out.println(total);//204

### queryForRowSet ###

//queryForRowSet(String sql); ==> SqlRowSet == ResultSet
            SqlRowSet sqlRowSet = template.queryForRowSet("select * from t_score");
            //sqlRowSet == ResultSet
            if(sqlRowSet.isFirst())sqlRowSet.previous();
            while (sqlRowSet.next()){ 
                System.out.println("id:"+sqlRowSet.getInt(1));
                //todo
            }
    
    
    // 使用方式同上
            template.queryForRowSet(String sql, Object...args);
            template.queryForRowSet(String sql,Object[] args,int[] argTypes);

### queryForList ###

//queryForList(String sql); ==> List<Map<String, Object>>
            String sql = "select * from t_score";
            List<Map<String, Object>> scoreMaps = template.queryForList(sql);
            scoreMaps.forEach(System.out::println);
            //输出
            { id=1, sid=20181515114, chinese=54, math=54, english=96}
            { id=2, sid=20181515101, chinese=55, math=80, english=99}
            { id=3, sid=20181515102, chinese=98, math=88, english=79}
            { id=4, sid=20181515105, chinese=80, math=80, english=78}
            { id=7, sid=20181515103, chinese=50, math=20, english=88}
    
    
            //queryForList(String sql, class<T> elementType); ==> List<T>
            List<Integer> total_score_list = template.queryForList("select (chinese+math+english) as total from t_score", Integer.class);
            for(Integer i:total_score_list) System.out.println(i);
            // 输出
            204
            234
            265
            238
            158
    
    
            //queryForList(String sql,Object... args) ==> List<Map<String, Object>>
            List<Map<String, Object>> maps = template.queryForList("select * from t_score where english>=?", 80);
            for (Map<String, Object> m : maps) System.out.println(m);
            //输出
            { id=1, sid=20181515114, chinese=54, math=54, english=96}
            { id=2, sid=20181515101, chinese=55, math=80, english=99}
            { id=7, sid=20181515103, chinese=50, math=20, english=88}
    
    
            //queryForList(String sql,Object[] args,int[] argTypes) => List<Map<String, Object>>
            Object[] args = new Object[]{ 80};
            int[] argTypes = new int[]{ Types.INTEGER};
            List<Map<String, Object>> maps = template.queryForList("select * from t_score where english>=?", args, argTypes);
            for (Map<String, Object> m : maps) System.out.println(m);
            //输出
            { id=1, sid=20181515114, chinese=54, math=54, english=96}
            { id=2, sid=20181515101, chinese=55, math=80, english=99}
            { id=7, sid=20181515103, chinese=50, math=20, english=88}
    
    
            //queryForList(String sql,Object[] args,int[] argTypes, class<T> elementType ) ==> List<T>
            Object[] args = new Object[]{ 80};
            int[] argTypes = new int[]{ Types.INTEGER};
            List<Integer> integers = template.queryForList("select (chinese + math + english) as total from t_score where english >= ?", args, argTypes, Integer.class);
            for (Integer i : integers) System.out.println(i);
            //输出
            204
            234
            158

Q&A 请指正!

[20200209164542441.png]: /images/20210920/63dc3d2946574ec3bed3a46b7a89d47e.png