Spring笔记13—JdbcTemplate(数据库)

Spring对数据库的操作在jdbc上面做了深层次的封装,使用spring的注入功能,可以把DataSource注册到JdbcTemplate之中

JdbcTemplate主要提供以下方法:

  1. execute方法:可以用于执行任何SQL语句,一般用于执行DDL语句;
    可执行任何sql语句,但返回值是void,所以一般用于数据库的新建、修改、删除和数据表记录的增删改
  2. update方法及batchUpdate方法:update方法用于执行新增、修改、删除等语句;batchUpdate方法用于执行批处理相关语句;
  3. query方法及queryForXXX方法:用于执行查询相关语句;
  4. call方法:用于执行存储过程、函数相关语句。
  5. setMaxRows(int max)    //设置返回的最大记录数
    setQueryTimeout(int  timeout)    //设置查询超时
    setDataSource(DataSource  dataSource)    //设置数据源

准备

<dependencies>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>5.1.9.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-context</artifactId>
        <version>5.1.9.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.17</version>
    </dependency>
</dependencies>

数据库:

CREATE DATABASE /*!32312 IF NOT EXISTS*/`test01` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

USE `test01`;

/*Table structure for table `user` */

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `address` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

准备一个实体类:

public class User {
    private Integer id;
    private String username;
    private String address;

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", address='" + address + '\'' +
                '}';
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }
}

Java 配置

@Configuration
public class JdbcConfig {
    @Bean
    DataSource dataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUsername("root");
        dataSource.setPassword("123");
        dataSource.setUrl("jdbc:mysql:///test01");
        return dataSource;
    }
    @Bean
    JdbcTemplate jdbcTemplate() {
        return new JdbcTemplate(dataSource());
    }
}

这里,提供两个 Bean一个是 DataSource 的 Bean,另一个是 JdbcTemplate 的 Bean,JdbcTemplate 的配置非常容易,只需要 new 一个 Bean 出来,然后配置一下 DataSource 就i可以。

public class Main {

    private JdbcTemplate jdbcTemplate;

    @Before
    public void before() {
        AnnotationConfigApplicationContext ctx = new AnnotationConfigApplicationContext(JdbcConfig.class);
        jdbcTemplate = ctx.getBean(JdbcTemplate.class);
    }

    @Test
    public void insert() {
        jdbcTemplate.update("insert into user (username,address) values (?,?);", "javaboy", "www.javaboy.org");
    }
    @Test
    public void update() {
        jdbcTemplate.update("update user set username=? where id=?", "javaboy123", 1);

    }
    @Test
    public void delete() {
        jdbcTemplate.update("delete from user where id=?", 2);
    }

    @Test
    public void select() {
        User user = jdbcTemplate.queryForObject("select * from user where id=?", new BeanPropertyRowMapper<User>(User.class), 1);
        System.out.println(user);
    }
}

在查询时,如果使用了 BeanPropertyRowMapper,要求查出来的字段必须和 Bean 的属性名一一对应。如果不一样,则不要使用 BeanPropertyRowMapper此时需要自定义 RowMapper 或者给查询的字段取别名。

1.给查询出来的列取别名:

@Test
public void select2() {
    User user = jdbcTemplate.queryForObject("select id,username as name,address from user where id=?", new BeanPropertyRowMapper<User>(User.class), 1);
    System.out.println(user);
}

2.自定义 RowMapper

@Test
public void select3() {
    User user = jdbcTemplate.queryForObject("select * from user where id=?", new RowMapper<User>() {
        public User mapRow(ResultSet resultSet, int i) throws SQLException {
            int id = resultSet.getInt("id");
            String username = resultSet.getString("username");
            String address = resultSet.getString("address");
            User u = new User();
            u.setId(id);
            u.setName(username);
            u.setAddress(address);
            return u;
        }
    }, 1);
    System.out.println(user);
}

查询多条记录,方式如下:

@Test
public void select4() {
    List<User> list = jdbcTemplate.query("select * from user", new BeanPropertyRowMapper<>(User.class));
    System.out.println(list);
}

XML 配置

以上配置,也可以通过 XML 文件来实现。通过 XML 文件实现只是提供 JdbcTemplate 实例,剩下的代码还是 Java 代码,就是 JdbcConfig 被 XML 文件代替而已。

<bean class="org.springframework.jdbc.datasource.DriverManagerDataSource" id="dataSource">
    <property name="username" value="root"/>
    <property name="password" value="123"/>
    <property name="url" value="jdbc:mysql:///test01?serverTimezone=Asia/Shanghai"/>
    <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
</bean>
<bean class="org.springframework.jdbc.core.JdbcTemplate" id="jdbcTemplate">
    <property name="dataSource" ref="dataSource"/>
</bean>

如果用 db.properties 来写数据库的内容(下面用了C3p0,需要导入C3p0的包):

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/lianxi?characterEncoding=utf-8
user=root
password=123
<context:property-placeholder location="classpath:/db.properties" />   
 <!-- 配置数据源(里面存放了若干个连接对象):数据库交互的。    数据源:c3p0,druid(阿里) -->
       <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
              <property name="user" value="${user}"/>
              <property name="password" value="${password}"/>
              <property name="driverClass" value="${driver}"/>
              <property name="jdbcUrl" value="${url}"/>
       </bean>
       
       <!-- 配置springJdbc的模板类 -->
       <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
              <property name="dataSource" ref="dataSource"></property>
       </bean>

配置完成后,加载该配置文件,并启动:

public class Main {

    private JdbcTemplate jdbcTemplate;

    @Before
    public void before() {
        ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
        jdbcTemplate = ctx.getBean(JdbcTemplate.class);
    }

    @Test
    public void insert() {
        jdbcTemplate.update("insert into user (username,address) values (?,?);", "javaboy", "www.javaboy.org");
    }
    @Test
    public void update() {
        jdbcTemplate.update("update user set username=? where id=?", "javaboy123", 1);

    }
    @Test
    public void delete() {
        jdbcTemplate.update("delete from user where id=?", 2);
    }

    @Test
    public void select() {
        User user = jdbcTemplate.queryForObject("select * from user where id=?", new BeanPropertyRowMapper<User>(User.class), 1);
        System.out.println(user);
    }
    @Test
    public void select4() {
        List<User> list = jdbcTemplate.query("select * from user", new BeanPropertyRowMapper<>(User.class));
        System.out.println(list);
    }

    @Test
    public void select2() {
        User user = jdbcTemplate.queryForObject("select id,username as name,address from user where id=?", new BeanPropertyRowMapper<User>(User.class), 1);
        System.out.println(user);
    }

    @Test
    public void select3() {
        User user = jdbcTemplate.queryForObject("select * from user where id=?", new RowMapper<User>() {
            public User mapRow(ResultSet resultSet, int i) throws SQLException {
                int id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                String address = resultSet.getString("address");
                User u = new User();
                u.setId(id);
                u.setName(username);
                u.setAddress(address);
                return u;
            }
        }, 1);
        System.out.println(user);
    }

}

补充:

在项目中,如果需要一次性插入或更新多条记录,当然可以简单地通过多次调用update()方法完成任务,但这不是最好的实现方案。更好的选择是使用JDBCTemplate批量数据更改的方法。

(很少用到这个)

  •   public int[] batchUpdate(String[] sql)

  多条SQL语句组成一个数组,注意此处的sql语句不能带参数,该方法以批量方式执行这些SQL语句。Spring在内部使用JDBC提供的批量更新API完成操作,如果底层的JDBC Driver不支持批量更新操作,Spring将采用逐条更新的方式模拟批量更新。

  •   int[] batchUpdate(String sql,BatchPreparedStatementSetter pss)

  使用本方法对于同一结构的带参SQL语句多次进行数据更新操作。通过BatchPreparedStatementSetter回调接口进行批量参数的绑定工作。BatchPreparedStatementSetter定义了两个方法:

  int getBatchSize():指定本批次的大小

  void setValues(PreparedStatement ps,int i):为给定的PreparedStatement设置参数

private void addUsers(final List<User> userList) {
        final String sql = "insert into user(username,age) values(?,?)";
        jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            
            public void setValues(PreparedStatement ps, int index) throws SQLException {
                User user = userList.get(index);
                ps.setString(1, user.getUsername());
                ps.setInt(2, user.getAge());
            }
            
            public int getBatchSize() {
                return userList.size();
            }
        });
}

需要注意的是BatchPreparedStatementSetter是一次性地批量提交数据,而不会分批提交,getBatchSize()是整批的大小。所以,如果希望将一个List中的数据通过BatchPreparedStatementSetter批量更新到数据库中,getBatchSize()就应该设置为List的大小。如果List非常大,希望分多次批量提交,则可分段读取这个大List并暂存到一个小的List中,再将这个小的List通过BatchPreparedStatemetSetter批量保存到数据库中。

例子2:

//批量更新
	public int[] updateAll(final List<Emp> list){
		String sql="update emp set name=?,age=?,birth=? where id=?";
		return jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
		
			public void setValues(PreparedStatement pr, int i) throws SQLException {
				// TODO Auto-generated method stub
				Emp e=list.get(i);
				pr.setString(1,e.getName());
				pr.setInt(2, e.getAge());
				pr.setDate(3, new Date(e.getBirth().getTime()));
				pr.setInt(4, e.getId());
			}
			
			public int getBatchSize() {
				// TODO Auto-generated method stub
				return list.size();
			}
		});
	}

测试:

//批量更新全部
	@Test
	public void updateAll(){
		List<Emp> list=new ArrayList<Emp>();
		for(int i=1;i<3;i++){
			Emp e=new Emp();
			e.setAge(20+i);
			e.setId(i);
			e.setBirth(new Date());
			e.setName("xjh"+i);
			list.add(e);
		}
		dao.updateAll(list);
		
	}