JdbcTemplatey的学习

字数统计: 2k字   |   阅读时长≈ 10分

这篇是学习JdbcTemplatey的过程分享，如果有问题请联系我进行修改！

一、JdbcTemplatey实例化对象

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
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管理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
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表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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表
1
2
3
4
5
6
7
8
9
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表
1
2
3
4
5
6
7
8
9
 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对象
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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对象（建议）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
    /*
     相当于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对象（不建议）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
/*
     相当于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数组对象（建议）
1
2
3
4
5
6
7
8
/*
 相当于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语句
1
2
3
4
5
6
7
8
9
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语句
1
2
3
4
5
6
7
8
9
10
11
12
13
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，返回的是多行数据

1
2
3
4
5
6
7
8
9
10
11
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类型 只能返回一个字段

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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（返回单行数据）
1
2
3
4
5
6
7
8
9
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（返回聚合函数）
1
2
3
4
5
6
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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（预编译）

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

1
2
3
4
5
6
7
8
9
10
11
12
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（查询带命名参数）

使用：形式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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
赏

谢谢你请我吃糖果

支付宝
微信

tag:

    缺失模块。
    1、请确保node版本大于6.2
    2、在博客根目录（注意不是yilia-plus根目录）执行以下命令：
    npm i hexo-generator-json-content --save

    3、在根目录_config.yml里添加配置： 

      jsonContent:
    meta: false
    pages: false
    posts:
      title: true
      date: true
      path: true
      text: false
      raw: false
      content: false
      slug: false
      updated: false
      comments: false
      link: false
      permalink: false
      excerpt: false
      categories: false
      tags: true

一个写BUG的工具人罢了