spring中jdbcTemplate的应用 二
来源:互联网 发布:深圳谷歌优化 编辑:程序博客网 时间:2024/04/29 07:20
Spring中jdbcTemplate常用方法使用
【注意:】jdbcTemplate 中的sql均是用“?”做占位符的
domain User:
java代码public class User { private int id; private String username; private String password; private String sex; //setter和getter方法省略…… }
UserServiceImpl :
如果采用第三种方式,则下面的用法中将方法中的 jdbcTemplate 换成 this.getJdbcTemplate()即可。其中
batchUpdate适合于批量增、删、改操作;update(…):使用于增、删、改操作;
execute():执行一个独立的sql语句,包括ddl语句;
queryForInt :查询出一个整数值
java代码
创建表 public void create(String tableName){ jdbcTemplate.execute("create table "+tableName +" (id integer,user_name varchar2(40),password varchar2(40))"); }
//jdbcTemplate.update适合于insert 、update和delete操作; public void save3(User user) { Assert.isNull(user, "user is not null"); jdbcTemplate.update("insert into tb_test1(name,password) values(?,?)", new Object[]{user.getUsername(),user.getPassword()}); } public void save(User user) { jdbcTemplate.update("insert into tb_test1(name,password) values(?,?)", new Object[]{user.getUsername(),user.getPassword()},new int[]{java.sql.Types.VARCHAR,java.sql.Types.VARCHAR}); }
//避免sql注入 public void save2(final User user) { jdbcTemplate.update("insert into tb_test1(name,password) values(?,?)",new PreparedStatementSetter(){ @Override public void setValues(PreparedStatement ps) throws SQLException { ps.setString(1, user.getUsername()); ps.setString(2, user.getPassword()); }}); } public void save4(User user) { jdbcTemplate.update("insert into tb_test1(name,password) values(?,?)", new Object[]{user.getUsername(),user.getPassword()}); }
//返回插入的主键 public List save5(final User user) { KeyHolder keyHolder = new GeneratedKeyHolder(); jdbcTemplate.update(new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { PreparedStatement ps = connection.prepareStatement("insert into tb_test1(name,password) values(?,?)", new String[] {"id"}); ps.setString(1, user.getUsername()); ps.setString(2, user.getPassword()); return ps; } }, keyHolder); return keyHolder.getKeyList(); } public void update(final User user) { jdbcTemplate.update("update tb_test1 set name=?,password=? where id = ?", new PreparedStatementSetter(){ public void setValues(PreparedStatement ps) throws SQLException { ps.setString(1, user.getUsername()); ps.setString(2, user.getPassword()); ps.setInt(3, user.getId()); } } ); } public void delete(User user) { jdbcTemplate.update("delete from tb_test1 where id = ?",new Object[]{user.getId()},new int[]{java.sql.Types.INTEGER}); }
@Deprecated //因为没有查询条件,所以用处不大 public int queryForInt1(){ return jdbcTemplate.queryForInt("select count(0) from tb_test1"); } public int queryForInt2(User user){ return jdbcTemplate.queryForInt("select count(0) from tb_test1 where username = ?" , new Object[]{user.getUsername()}); } //最全的参数3个 public int queryForInt3(User user){ return jdbcTemplate.queryForInt("select count(0) from tb_test1 where username = ?" , new Object[]{user.getUsername()}, new int[]{java.sql.Types.VARCHAR}); } //可以返回是一个基本类型的值 @Deprecated //因为没有查询条件,所以用处不大 public String queryForObject1(User user) { return (String) jdbcTemplate.queryForObject("select username from tb_test1 where id = 100", String.class); } //可以返回值是一个对象 @Deprecated //因为没有查询条件,所以用处不大 public User queryForObject2(User user) { return (User) jdbcTemplate.queryForObject("select * from tb_test1 where id = 100", User.class); //class是结果数据的java类型 } @Deprecated //因为没有查询条件,所以用处不大 public User queryForObject3(User user) { return (User) jdbcTemplate.queryForObject("select * from tb_test1 where id = 100", new RowMapper(){ @Override public Object mapRow(ResultSet rs, int rowNum)throws SQLException { User user = new User(); user.setId(rs.getInt("id")); user.setUsername(rs.getString("username")); user.setPassword(rs.getString("password")); return user; } } ); } public User queryForObject4(User user) { return (User) jdbcTemplate.queryForObject("select * from tb_test1 where id = ?", new Object[]{user.getId()}, User.class); //class是结果数据的java类型 实际上这里是做反射,将查询的结果和User进行对应复制 } public User queryForObject5(User user) { return (User) jdbcTemplate.queryForObject( "select * from tb_test1 where id = ?", new Object[]{user.getId()}, new RowMapper(){ @Override public Object mapRow(ResultSet rs,int rowNum)throws SQLException { User user = new User(); user.setId(rs.getInt("id")); user.setUsername(rs.getString("username")); user.setPassword(rs.getString("password")); return user; } }); //class是结果数据的java类型 } @Override public User queryForObject(User user) { //方法有返回值 return (User) jdbcTemplate.queryForObject("select * from tb_test1 where id = ?", new Object[]{user.getId()}, new int[]{java.sql.Types.INTEGER}, new RowMapper() { @Override public Object mapRow(ResultSet rs, int rowNum) throws SQLException { User user = new User(); user.setId(rs.getInt("id")); user.setUsername(rs.getString("username")); user.setPassword(rs.getString("password")); return user; } } ); } @SuppressWarnings("unchecked") public List<User> queryForList1(User user) { return (List<User>) jdbcTemplate.queryForList("select * from tb_test1 where username = ?", new Object[]{user.getUsername()}, User.class); } @SuppressWarnings("unchecked") public List<String> queryForList2(User user) { return (List<String>) jdbcTemplate.queryForList("select username from tb_test1 where sex = ?", new Object[]{user.getSex()}, String.class); } @SuppressWarnings("unchecked") //最全的参数查询 public List<User> queryForList3(User user) { return (List<User>) jdbcTemplate.queryForList("select * from tb_test1 where username = ?", new Object[]{user.getUsername()}, new int[]{java.sql.Types.VARCHAR}, User.class); } //通过RowCallbackHandler对Select语句得到的每行记录进行解析,并为其创建一个User数据对象。实现了手动的OR映射。 public User queryUserById4(String id){ final User user = new User(); //该方法返回值为void this.jdbcTemplate.query("select * from tb_test1 where id = ?", new Object[] { id }, new RowCallbackHandler() { @Override public void processRow(ResultSet rs) throws SQLException { User user = new User(); user.setId(rs.getInt("id")); user.setUsername(rs.getString("username")); user.setPassword(rs.getString("password")); } }); return user; } @SuppressWarnings("unchecked") @Override public List<User> list(User user) { return jdbcTemplate.query("select * from tb_test1 where username like '%?%'", new Object[]{user.getUsername()}, new int[]{java.sql.Types.VARCHAR}, new RowMapper(){ @Override public Object mapRow(ResultSet rs, int rowNum) throws SQLException { User user = new User(); user.setId(rs.getInt("id")); user.setUsername(rs.getString("username")); user.setPassword(rs.getString("password")); return user; } }); } //批量操作 适合于增、删、改操作 public int[] batchUpdate(final List users) { int[] updateCounts = jdbcTemplate.batchUpdate( "update tb_test1 set username = ?, password = ? where id = ?", new BatchPreparedStatementSetter() { @Override public void setValues(PreparedStatement ps, int i) throws SQLException { ps.setString(1, ((User)users.get(i)).getUsername()); ps.setString(2, ((User)users.get(i)).getPassword()); ps.setLong(3, ((User)users.get(i)).getId()); } @Override public int getBatchSize() { return users.size(); } } ); return updateCounts; } //调用存储过程 public void callProcedure(int id){ this.jdbcTemplate.update("call SUPPORT.REFRESH_USERS_SUMMARY(?)", new Object[]{Long.valueOf(id)}); }
0 0
- spring中jdbcTemplate的应用 二
- Spring中jdbcTemplate的应用 一
- Spring中JdbcTemplate的使用
- spring中JDBCTemplate的使用
- spring 中JdbcTemplate的使用
- spring中JdbcTemplate的使用
- Spring中jdbcTemplate的用法
- Spring中JdbcTemplate的使用
- Spring JdbcTemplate应用
- Spring JdbcTemplate 应用
- Spring JdbcTemplate 应用
- Spring jdbcTemplate 应用
- Spring中jdbcTemplate的用法实例
- Spring中jdbcTemplate的用法实例
- Spring中jdbcTemplate的用法实例
- Spring中JdbcTemplate 和DataSource的联系
- JDBC:Spring框架中JDBCTemplate的使用
- Spring中Jdbctemplate的用法实例
- mybatis入门程序
- Ajax请求时,如何解释json数据
- C++_Primer_chapter18 4.嵌套类
- 【Linux】初识Linux操作系统
- 使用通知 Notification
- spring中jdbcTemplate的应用 二
- 如何将Win7做为NTP服务器
- 全手写struts用户登录系统
- Swift - 使用PhotoKit获取照片1(获取所有照片缩略图、原图及其信息)
- 从用户体验的角度说明产品的五个层次
- CSS选择器速查
- es
- Retrofit2 本地模拟API数据
- Swift - 使用PhotoKit获取照片2(列出所有相簿、并选择)