SpringJDBC模板的使用

spring对持久层也提供了解决方案
spring提供了很多的模板用于简化开发

JDBC模板的简单使用

1.导包
spring依赖文件+spring辅助文件+mysql相关+jdbc相关

<!-- spring依赖的核心文件 -->
        <!-- https://mvnrepository.com/artifact/org.springframework/spring-context -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.2.2.RELEASE</version>
        </dependency>
        <!-- spring的辅助文件 -->
        <!-- https://mvnrepository.com/artifact/commons-logging/commons-logging -->
        <dependency>
            <groupId>commons-logging</groupId>
            <artifactId>commons-logging</artifactId>
            <version>1.2</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>5.2.2.RELEASE</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.15</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>5.2.2.RELEASE</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.2.2.RELEASE</version>
        </dependency>

2.测试

public class Jdbcdemo1 {
    @Test
    public void demo1(){
        //创建连接词
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8");
        dataSource.setUsername("root");
        dataSource.setPassword("root");
        //创建JDBC模板
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.update("insert into users values(?,?,?)","10","dff","asd");    
    }
}

将连接池交给spring管理

在上文的基础上进行改进，将数据库的连接交给spring管理
1.新建xml文件

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <!-- 属性注入  8.几的版本 com.mysql.cj.jdbc.Driver-->
        <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
        <property name="url" value="jdbc:mysql://localhost:3306/test"></property>
        <property name="username" value="root"></property>
        <property name="password" value="root"></property>
    </bean>
    <!-- 配置Spring的JDBC的模板 -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>

2.测试

//将jdbc注入到数据中，也可以再xml文件里用bean配置 但是要有jdbcTemplate的set方法
@Resource(name="jdbcTemplate")
    private JdbcTemplate jdbcTemplate;
    @Test
    public void demo1(){
             //update执行增删改
        jdbcTemplate.update("insert into users values(?,?,?)","11","11","11");
    }

使用开源的数据库连接池

DBCP连接池

1.引入jar包

<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-dbcp2 -->
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-dbcp2</artifactId>
    <version>2.1.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-pool2 -->
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-pool2</artifactId>
    <version>2.4.2</version>
</dependency>

2.配置XML文件

<!-- 配置DBCP的连接池 -->
    <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
        <property name="url" value="jdbc:mysql://localhost:3306/test"></property>
        <property name="username" value="root"></property>
        <property name="password" value="ro"></property>
    </bean>

C3P0连接池

1.引入jar包

<!-- https://mvnrepository.com/artifact/com.mchange/c3p0 -->
        <dependency>
            <groupId>com.mchange</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.5.2</version>
        </dependency>

2.在XML文件中配置

<!-- 配置c3p0连接池 -->
        <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
            <property name="driverClass" value="com.mysql.jdbc.Driver"></property>
            <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/test"></property>
            <property name="user" value="root"></property>
            <property name="password" value="root"></property>
        </bean>

将参数配置到属性文件

以C3P0连接池为例
1.新建一个文件(jdbc.properties) 配置

jdbc.driverClass=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/test
jdbc.username=root
jdbc.password=ro

2.文件引入到xml文件中
第一种方式（较少）

<!-- 第一种方式 -->
<bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
    <property name="location" value="classpath:jdbc.properties"></property>
</bean>

第二种方式：

<!-- 第二种，通过context标签 -->
<context:property-placeholder location="classpath:jdbc.properties"/>    
<!-- 配置c3p0连接池 -->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
    <property name="driverClass" value="${jdbc.driverClass}"></property>
    <property name="jdbcUrl" value="${jdbc.url}"></property>
    <property name="user" value="${jdbc.username}"></property>
    <property name="password" value="${jdbc.password}"></property>
</bean>

对数据库的数据进行操作

以C3P0连接池为例

根据数据查询

@Test
public void demo2() {
    String name=jdbcTemplate.queryForObject("select name from users where id= ? ", String.class,10);
    System.out.println(name);
}

查询总数

@Test
public void demo3() {
    Long count = jdbcTemplate.queryForObject("select count(*) from users", Long.class);
    System.out.println(count);
}

单个查询

在返回值是自定义实体类时，需要使用RowMapper<自定义实体类>接口
1.编写对应实体类
2.在测试类中

class MyRowMapper implements RowMapper<Users>{
    @Override
    public Users mapRow(ResultSet rs, int arg1rowNum) throws SQLException {
        // TODO 自动生成的方法存根
        Users user = new Users();
        user.setId(rs.getString("id"));
        user.setName(rs.getString("name"));
        user.setPassword(rs.getString("password"));
        return user;
    }
}

3.测试

@Test
public void demo4() {
    Users user = jdbcTemplate.queryForObject("select * from users where id= ?",new MyRowMapper(),10);
    System.out.println(user);
}

如果不想采用上文接口的方式，可以在测试类中：

@Test
public void demo4() {
    RowMapper<User> rowMapper = new BeanPropertyRowMapper<User>(User.calss);
    Users user = jdbcTemplate.queryForObject("select * from users where id= ?",new MyRowMapper(),10);
    System.out.println(user);
}

全部查询

@Test
    public void demo5() {
        List<Users> list = jdbcTemplate.query("select * from users", new MyRowMapper());
        for(Users user:list) {
            System.out.println(user);
        }