Spring-JdbcTemplatey模板

一、JdbcTemplatey实例化对象

TestSQL类
 private DataSource dataSource;
    public DataSource getDataSource() {
        return dataSource;
    }
    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }
    public void queryTable(){
    //这种的JdbcTemplate是实例化对象的，不是注入的
        JdbcTemplate template = new JdbcTemplate(this.dataSource);
        String sql  = "select * from user";
        List<Map<String,Object>> dataList = template.queryForList(sql);
        for(Map<String,Object> rowData :dataList){
            System.out.println(rowData.get("userId")+"\t"+rowData.get("userName"));
        }
    }
    public static void main(String[] args) {
        TestSQL testSQL = (TestSQL)SpringUtil.getBean("TestSQL");
        testSQL.queryTable();
    }
    
spring配置文件
需要注入dataSource
<bean id="TestSQL" class="com.test.TestSQL">
	<property name="dataSource" ref="dataSource"></property>
</bean>

二、JdbcTemplatey用Spring管理

TestSQL类
  //JdbcTemplatey用spring注入
    private JdbcTemplate template ;
    public JdbcTemplate getTemplate() {
        return template;
    }
    public void setTemplate(JdbcTemplate template) {
        this.template = template;
    }
    public void queryTable() {
        String sql = "select * from user";
        List<Map<String, Object>> dataList = this.template.queryForList(sql);
        for (Map<String, Object> rowData : dataList) {
            System.out.println(rowData.get("userId") + "\t" +rowData.get("userName"));
        }
    }
    public static void main(String[] args) {
        TestSQL2 testSQL = (TestSQL2) SpringUtil.getBean("TestSQL2");
        testSQL.queryTable();
    }
spring配置文件   
 <!--注入template-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <!--当没有注入dataSource的时候，将无法使用jdbcTemplate，因为还没有给数据源赋值-->
        <property name="dataSource" ref="dataSource"></property>
    </bean>
    <bean id="TestSQL2" class="com.test.TestSQL2">
        <property name="template" ref="jdbcTemplate"></property>
    </bean>

三、执行DDL语句

3.1创建temp表

 TestSQL类
 //JdbcTemplatey用spring注入
    private JdbcTemplate template ;
    public JdbcTemplate getTemplate() {
        return template;
    }
    public void setTemplate(JdbcTemplate template) {
        this.template = template;
    }
    public void create() {
        String sql = "create table temp (tempId int,tempName varchar(20))";
         this.template.execute(sql);
    }
     public static void main(String[] args) {
        TestSQL_DDL testSQL = (TestSQL_DDL) SpringUtil.getBean("TestSQL_DDL");
        testSQL.create();
        //执行成功后会创建temp表 字段为int tempId和varchar类型的tempName
    }
    spring配置文件
     <!--注入template-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>
     <bean id="TestSQL_DDL" class="com.test.TestSQL_DDL">
        <property name="template" ref="jdbcTemplate"></property>
    </bean>

3.2修改temp表

	 public void alter() {
        String sql = "ALTER table temp add password varchar(10) ";
        this.template.execute(sql);
    }
     public static void main(String[] args) {
        TestSQL_DDL testSQL = (TestSQL_DDL) SpringUtil.getBean("TestSQL_DDL");
        testSQL.alter();
        //执行成功后会修改temp表，增加字段为varchar password
    }

3.3删除temp表

  public void drop() {
        String sql = "drop table temp ";
        this.template.execute(sql);
    }
 public static void main(String[] args) {
        TestSQL_DDL testSQL = (TestSQL_DDL) SpringUtil.getBean("TestSQL_DDL");
        testSQL.drop();
        //执行成功后会删除temp表 
    }

四、执行DML语句

4.1insert语句—statement对象

TestSQL类
    private JdbcTemplate template ;
    public JdbcTemplate getTemplate() {
        return template;
    }
    public void setTemplate(JdbcTemplate template) {
        this.template = template;
    }
    //相当于jdbc的statement对象的操作
    public void insert() {
        String sql = "insert into admin (userName,password) value ('aaa','bbb')";
         this.template.update(sql);
    }
    public static void main(String[] args) {
        TestSQL_DML testSQL = (TestSQL_DML) SpringUtil.getBean("TestSQL_DML");
        testSQL.insert();
        //执行后会在admin表插入userName为aaa和password为bbb
    }
spring配置文件
     <bean id="TestSQL_DML" class="com.test.TestSQL_DML">
        <property name="template" ref="jdbcTemplate"></property>
    </bean>

4.2insert语句—Preparedstatemen对象（建议）

     /*
      相当于jdbc的Preparedstatement对象的操作,创建一个匿名内部类PreparedStatementSetter对象(建议)
     */
 public void insert_2() {
        String sql = "insert into admin (userName,password) value (?,?)";
        this.template.update(sql, new PreparedStatementSetter() {
        //匿名内部类的写法
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setString(1,"abc");
                ps.setString(2,"bbb");
            }
        });
    }

4.3insert语句—Bean对象（不建议）

	/*
      相当于jdbc的Preparedstatement对象的操作,创建并返回PreparedStatementSetter对象(不建议)
      */
    public void insert_3() {
        String sql = "insert into admin (userName,password) value (?,?)";
        this.template.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement statement = connection.prepareStatement(sql);
                statement.setString(1,"test1");
                statement.setString(2,"testpwd");
                return statement;
            }
        });
    }

4.4使用Object数组对象（建议）

  /*
   相当于jdbc的Preparedstatement对象的操作,使用object数组对象(建议)
   */
    public void insert_4() {
        String sql = "insert into admin (userName,password) value (?,?)";
        Object[] paramArray = {"aabc","dcee"};
        this.template.update(sql,paramArray);
    }

4.5使用Map作为参数执行insert语句

 public void insertParameter() {
        String sql = "insert into admin (userName,password) value (:userName,:password)";
        NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(this.template.getDataSource());
        Map<String,Object> map = new HashMap<>();
        //map中的key要和参数一致,map中的数据可以比value中指定的参数多，但不能少
        map.put("userName","test2");
        map.put("password","testpwd2");
        template.update(sql,map);
    }

4.6使用Bean作为参数执行insert语句

public void insertBean(){
        String sql = "insert into admin (userName,password) value (:userName,:password)";
        NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(this.template.getDataSource());
        /*
          Bean的属性要和value中参数的属性一致，可以多不可以少
         */
        User user = new User();
        user.setUserName("abbvc");
        user.setPassword("avvv");
        user.setUserId(123);
        BeanPropertySqlParameterSource propertySqlParameterSource = new BeanPropertySqlParameterSource(user);
        template.update(sql,propertySqlParameterSource);
    }

五、执行Query查询语句

5.1 queryForList（返回Map）

默认返回的是map，返回的是多行数据

public void queryForList() {
    String sql = "select * from admin";
    List<Map<String, Object>> dataList = template.queryForList(sql);
    for (Map<String, Object> map : dataList) {
      System.out.println(map.get("userId") + "\t" + map.get("userName"));
    }
  }
测试输出
1	admin
2	user
3	adb

5.2 queryForList的注意点

直接使用queryForList不能成功返回bean类型 只能返回一个字段

public void queryForListBean() {
    /*错误的写法
    String sql = "select * from admin";
    List<User>dataList = template.queryForList(sql,User.class);
     for(User user : dataList){
        System.out.println(user.getUserId()+"\t"+user.getUserName()+"\t"+user.getPassword());
     }
     */
    String sql = "select userName from admin";
    List<String> dataList = template.queryForList(sql, String.class);
    for (String str : dataList) {
      System.out.println(str);
    }
  }
错误的写法执行会报返回多个字段的错误，错误为Exception in thread "main" org.springframework.jdbc.IncorrectResultSetColumnCountException: Incorrect column count: expected 1, actual 5
正确返回数据为：
 admin
 user
 adb

5.3 queryForMap（返回单行数据）

public void queryForMap() {
    String sql = "select * from admin where userId = 2";
    Map<String, Object> row = template.queryForMap(sql);
    if (row != null) {
      System.out.println(row.get("userId") + "\t" + row.get("userName"));
    }
  }
测试输出：
2	user

5.4 queryForObject（返回聚合函数）

public void queryForObject() {
    String sql = "select count(1) from admin";
    Long userCount = template.queryForObject(sql, long.class);
    System.out.println("userCount=" + userCount);
  }
  测试输出：userCount=9

5.5 query返回所有数据

返回所有数据使用query函数，参数是sql语句+BeanPropertyRowMapper

public void query() {
    String sql = "select * from admin";
    /*	注意这个和BeanPropertySqlParameterSource对象的区别
        BeanPropertyRowMapper将数据映射成一个对象
        BeanPropertySqlParameterSource将Bean对象中的值赋值给命名参数
    */
    BeanPropertyRowMapper<User> rowMapper = new BeanPropertyRowMapper<>(User.class);
    List<User> dataList = template.query(sql, rowMapper);
    for (User user : dataList) {
      System.out.println(user.getUserId() + "\t" + user.getUserName());
    }
  }
  测试输出：
  1	admin
  2	user
  3	adb

5.6 query_Pre（预编译）

查询语句中包过预编译对象，？形式

public void query_Pre() {
    String sql = "select * from admin where userName like ? and password like ? ";
    String[] Strarray = {"%a%", "%1%"};
    BeanPropertyRowMapper<User> rowMapper = new BeanPropertyRowMapper<>(User.class);
    List<User> dataList = template.query(sql, rowMapper, Strarray);
    for (User user : dataList) {
      System.out.println(user.getUserId() + "\t" + user.getUserName());
    }
  }
  测试输出：
 1	admin
 3	adb

5.7 queryNamePre（查询带命名参数）

使用：形式

public void queryNamePre() {
    String sql = "select * from admin where userName like :userName and password like :password ";
    NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(template.getDataSource());
    User user = new User();
    user.setUserName("%a%");
    user.setPassword("%1%");
    BeanPropertySqlParameterSource propertySqlParameterSource =
        new BeanPropertySqlParameterSource(user);
    BeanPropertyRowMapper<User> rowMapper = new BeanPropertyRowMapper<User>(User.class);
    List<User> dataList = namedTemplate.query(sql, propertySqlParameterSource, rowMapper);
    for (User row : dataList) {
      System.out.println(row.getUserId() + "\t" + row.getUserName());
    }
  }
 测试输出：
 1	admin
 3	adb