Spring中jdbcTemplate的用法实例

1 /**   
2     * 创建表  
3     */   
4    public void create(String tableName){ //tb_test1  
5        jdbcTemplate.execute("create table "+tableName +" (id integer,user_name varchar2(40),password varchar2(40))");  
6    }  
7      
8    //jdbcTemplate.update适合于insert 、update和delete操作；  
9    /**   
10     * 第一个参数为执行sql   
11     * 第二个参数为参数数据   
12     */   
13    public void save3(User user) {  
14        Assert.isNull(user, "user is not null");  
15        jdbcTemplate.update("insert into tb_test1(name,password) values(?,?)",   
16                new Object[]{user.getUsername(),user.getPassword()});  
17    }  
18      
19    /**   
20     * 第一个参数为执行sql   
21     * 第二个参数为参数数据   
22     * 第三个参数为参数类型   
23     */   
24    @Override  
25    public void save(User user) {  
26        Assert.isNull(user, "user is not null");  
27        jdbcTemplate.update(  
28                "insert into tb_test1(name,password) values(?,?)",   
29                new Object[]{user.getUsername(),user.getPassword()},   
30                new int[]{java.sql.Types.VARCHAR,java.sql.Types.VARCHAR}  
31                );  
32    }  
33  
34    //避免sql注入  
35    public void save2(final User user) {  
36        Assert.isNull(user, "user is not null");  
37          
38        jdbcTemplate.update("insert into tb_test1(name,password) values(?,?)",   
39                new PreparedStatementSetter(){  
40              
41                    @Override  
42                    public void setValues(PreparedStatement ps) throws SQLException {  
43                        ps.setString(1, user.getUsername());  
44                        ps.setString(2, user.getPassword());  
45                    }  
46        });  
47          
48    }  
49      
50    public void save4(User user) {  
51        Assert.isNull(user, "user is not null");  
52        jdbcTemplate.update("insert into tb_test1(name,password) values(?,?)",   
53                             new Object[]{user.getUsername(),user.getPassword()});  
54    }  
55      
56    //返回插入的主键  
57    public List save5(final User user) {  
58          
59        KeyHolder keyHolder = new GeneratedKeyHolder();  
60  
61        jdbcTemplate.update(new PreparedStatementCreator() {  
62                      
63                                @Override  
64                                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {  
65                                    PreparedStatement ps = connection.prepareStatement("insert into tb_test1(name,password) values(?,?)", new String[] {"id"});  
66                                    ps.setString(1, user.getUsername());  
67                                    ps.setString(2, user.getPassword());  
68                                    return ps;  
69                                }  
70                            },  
71                keyHolder);  
72          
73        return keyHolder.getKeyList();  
74    }  
75      
76    @Override  
77    public void update(final User user) {  
78        jdbcTemplate.update(  
79                "update tb_test1 set name=？,password=？ where id = ?",   
80                new PreparedStatementSetter(){  
81                    @Override  
82                    public void setValues(PreparedStatement ps) throws SQLException {  
83                        ps.setString(1, user.getUsername());  
84                        ps.setString(2, user.getPassword());  
85                        ps.setInt(3, user.getId());  
86                    }  
87                }  
88        );  
89    }  
90  
91    @Override  
92    public void delete(User user) {  
93        Assert.isNull(user, "user is not null");  
94        jdbcTemplate.update(  
95                "delete from tb_test1 where id = ?",   
96                new Object[]{user.getId()},   
97                new int[]{java.sql.Types.INTEGER});  
98    }  
99  
100    @Deprecated //因为没有查询条件，所以用处不大  
101    public int queryForInt1(){  
102        return jdbcTemplate.queryForInt("select count(0) from tb_test1");  
103    }  
104      
105    public int queryForInt2(User user){  
106        return jdbcTemplate.queryForInt("select count(0) from tb_test1 where username = ?" ,  
107                new Object[]{user.getUsername()});  
108    }  
109      
110    //最全的参数3个  
111    public int queryForInt3(User user){  
112        return jdbcTemplate.queryForInt("select count(0) from tb_test1 where username = ?" ,  
113                new Object[]{user.getUsername()},  
114                new int[]{java.sql.Types.VARCHAR});  
115    }  
116      
117    //可以返回是一个基本类型的值  
118    @Deprecated  //因为没有查询条件，所以用处不大  
119    public String queryForObject1(User user) {  
120        return (String) jdbcTemplate.queryForObject("select username from tb_test1 where id = 100",  
121                                                    String.class);  
122    }  
123      
124    //可以返回值是一个对象  
125    @Deprecated //因为没有查询条件，所以用处不大  
126    public User queryForObject2(User user) {  
127        return (User) jdbcTemplate.queryForObject("select * from tb_test1 where id = 100", User.class); //class是结果数据的java类型  
128    }  
129      
130    @Deprecated //因为没有查询条件，所以用处不大  
131    public User queryForObject3(User user) {  
132        return (User) jdbcTemplate.queryForObject("select * from tb_test1 where id = 100",   
133                    new RowMapper(){  
134      
135                        @Override  
136                        public Object mapRow(ResultSet rs, int rowNum)throws SQLException {  
137                            User user  = new User();  
138                            user.setId(rs.getInt("id"));  
139                            user.setUsername(rs.getString("username"));  
140                            user.setPassword(rs.getString("password"));  
141                            return user;  
142                        }  
143                    }  
144        );   
145    }  
146      
147    public User queryForObject4(User user) {  
148        return (User) jdbcTemplate.queryForObject("select * from tb_test1 where id = ?",   
149                                                    new Object[]{user.getId()},  
150                                                    User.class); //class是结果数据的java类型  实际上这里是做反射，将查询的结果和User进行对应复制  
151    }  
152      
153    public User queryForObject5(User user) {  
154        return (User) jdbcTemplate.queryForObject(  
155                "select * from tb_test1 where id = ?",   
156                new Object[]{user.getId()},  
157                new RowMapper(){  
158  
159                    @Override  
160                    public Object mapRow(ResultSet rs,int rowNum)throws SQLException {  
161                        User user  = new User();  
162                        user.setId(rs.getInt("id"));  
163                        user.setUsername(rs.getString("username"));  
164                        user.setPassword(rs.getString("password"));  
165                        return user;  
166                    }  
167              
168        }); //class是结果数据的java类型  
169    }  
170      
171    @Override  
172    public User queryForObject(User user) {  
173        //方法有返回值  
174        return (User) jdbcTemplate.queryForObject("select * from tb_test1 where id = ?",  
175                new Object[]{user.getId()},  
176                new int[]{java.sql.Types.INTEGER},   
177                new RowMapper() {  
178              
179                    @Override  
180                    public Object mapRow(ResultSet rs, int rowNum) throws SQLException {  
181                        User user  = new User();  
182                        user.setId(rs.getInt("id"));  
183                        user.setUsername(rs.getString("username"));  
184                        user.setPassword(rs.getString("password"));  
185                        return user;  
186                    }  
187                }  
188        );  
189    }  
190  
191    @SuppressWarnings("unchecked")  
192    public List<User> queryForList1(User user) {  
193        return (List<User>) jdbcTemplate.queryForList("select * from tb_test1 where username = ?",   
194                            new Object[]{user.getUsername()},  
195                            User.class);  
196    }  
197  
198    @SuppressWarnings("unchecked")  
199    public List<String> queryForList2(User user) {  
200        return (List<String>) jdbcTemplate.queryForList("select username from tb_test1 where sex = ?",   
201                            new Object[]{user.getSex()},  
202                            String.class);  
203    }  
204      
205    @SuppressWarnings("unchecked")  
206    //最全的参数查询  
207    public List<User> queryForList3(User user) {  
208        return (List<User>) jdbcTemplate.queryForList("select * from tb_test1 where username = ?",  
209                            new Object[]{user.getUsername()},  
210                            new int[]{java.sql.Types.VARCHAR},  
211                            User.class);  
212    }  
213  
214    //通过RowCallbackHandler对Select语句得到的每行记录进行解析，并为其创建一个User数据对象。实现了手动的OR映射。  
215    public User queryUserById4(String id){  
216        final User user  = new User();  
217          
218        //该方法返回值为void  
219        this.jdbcTemplate.query("select * from tb_test1 where id = ?",   
220                new Object[] { id },   
221                new RowCallbackHandler() {     
222              
223                    @Override    
224                    public void processRow(ResultSet rs) throws SQLException {     
225                        User user  = new User();  
226            user.setId(rs.getInt("id"));  
227            user.setUsername(rs.getString("username"));  
228            user.setPassword(rs.getString("password"));    
229                    }     
230        });   
231          
232        return user;     
233    }  
234      
235    @SuppressWarnings("unchecked")  
236    @Override  
237    public List<User> list(User user) {  
238        return jdbcTemplate.query("select * from tb_test1 where username like '%?%'",   
239                new Object[]{user.getUsername()},   
240                new int[]{java.sql.Types.VARCHAR},   
241                new RowMapper(){  
242              
243                    @Override  
244                    public Object mapRow(ResultSet rs, int rowNum) throws SQLException {  
245                        User user  = new User();  
246                        user.setId(rs.getInt("id"));  
247                        user.setUsername(rs.getString("username"));  
248                        user.setPassword(rs.getString("password"));  
249                        return user;  
250                    }  
251        });  
252    }  
253  
254    //批量操作    适合于增、删、改操作  
255    public int[] batchUpdate(final List users) {  
256          
257        int[] updateCounts = jdbcTemplate.batchUpdate(  
258                "update tb_test1 set username = ?, password = ? where id = ?",  
259                new BatchPreparedStatementSetter() {  
260                      
261                        @Override  
262                        public void setValues(PreparedStatement ps, int i) throws SQLException {  
263                            ps.setString(1, ((User)users.get(i)).getUsername());  
264                            ps.setString(2, ((User)users.get(i)).getPassword());  
265                            ps.setLong(3, ((User)users.get(i)).getId());  
266                        }  
267                          
268                        @Override  
269                        public int getBatchSize() {  
270                            return users.size();  
271                        }  
272                }   
273        );  
274          
275        return updateCounts;  
276    }  
277      
278    //调用存储过程  
279    public void callProcedure(int id){  
280        this.jdbcTemplate.update("call SUPPORT.REFRESH_USERS_SUMMARY(?)", new Object[]{Long.valueOf(id)});  
281}