Spring JdbcTemplate基础学习

来源：互联网发布：手机修改mac地址软件编辑：程序博客网时间：2024/06/01 07:49

采用构造子注入

context-atp-base-sessionFactory.xml

<bean id="atp-base-seashell-jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">        <constructor-arg>            <ref bean="seashellDS" />        </constructor-arg></bean>

database.xml

<import resource="context-atp-base-orm.xml"/>    <import resource="context-atp-base-sessionFactory.xml"/>    <import resource="context-atp-base-domain.xml"/>    <bean id="seashellDS" class="org.apache.commons.dbcp.BasicDataSource"        destroy-method="close">        <property name="driverClassName" value="oracle.jdbc.OracleDriver" />        <property name="url" value="jdbc:oracle:thin:seashell/seashell@192.168.5.100:1530:billdb" />        <property name="username" value="seashell" />        <property name="password" value="seashell" /></bean>

update操作，此处涉及hibernate和jdbctemplate混用

public class AtpMemberRiskDaoImpl extends BaseDaoAdapter<AtpMemberRisk, Long> implements AtpMemberRiskDao{private JdbcTemplate jdbcTemplate;public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {this.jdbcTemplate = jdbcTemplate;}@Overridepublic AtpMemberRisk getByMemberRisk(String memberCode) {// 查询条件不能为空if(!StringUtil.isEmpty(memberCode)) {Criterion cond = Restrictions.eq("memberCode", memberCode);List<AtpMemberRisk> result = find(cond);if(result!=null && result.size()>0) {return result.get(0);}}return null;}public int forUpdateLevel(String memberCode, int oldLevel, int newLevel) {String sql = "UPDATE T_ATP_MEMBER_RISK t SET t.RISK_LEVEL=?,t.UPD_TIME=sysdate where t.MEMBER_CODE=? and t.RISK_LEVEL=? ";Object[] paramArr = new Object[] { newLevel, memberCode, oldLevel };return jdbcTemplate.update(sql, paramArr);}}

配置文件

<bean id="atpMemberRiskDao" class="com.bill99.seashell.technicalservice.dao.atp.product.impl.AtpMemberRiskDaoImpl"><property name="sessionFactory" ref="atp-base-seashell-SessionFactory" /><property name="jdbcTemplate" ref="atp-base-seashell-jdbcTemplate" /></bean>

实现方式此处摘自：http://lehsyh.iteye.com/blog/1579737

  /**        * 创建表       */       public void create(String tableName){ //tb_test1          jdbcTemplate.execute("create table "+tableName +" (id integer,user_name varchar2(40),password varchar2(40))");      }            //jdbcTemplate.update适合于insert 、update和delete操作；      /**        * 第一个参数为执行sql        * 第二个参数为参数数据        */       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()});      }            /**        * 第一个参数为执行sql        * 第二个参数为参数数据        * 第三个参数为参数类型        */       @Override      public void save(User user) {          Assert.isNull(user, "user is not null");          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) {          Assert.isNull(user, "user is not null");                    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) {          Assert.isNull(user, "user is not null");          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() {                                                        @Override                                  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();      }            @Override      public void update(final User user) {          jdbcTemplate.update(                  "update tb_test1 set name=？,password=？ where id = ?",                   new PreparedStatementSetter(){                      @Override                      public void setValues(PreparedStatement ps) throws SQLException {                          ps.setString(1, user.getUsername());                          ps.setString(2, user.getPassword());                          ps.setInt(3, user.getId());                      }                  }          );      }        @Override      public void delete(User user) {          Assert.isNull(user, "user is not null");          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)});  }

其中，batchUpdate适合于批量增、删、改操作；

update(…)：使用于增、删、改操作；

execute（）：执行一个独立的sql语句，包括ddl语句；

queryForInt ：查询出一个整数值

0 0