SpringJDBC模板的使用

spring對(duì)持久層也提供了解決方案
spring提供了很多的模板用于簡(jiǎn)化開(kāi)發(fā)

JDBC模板的簡(jiǎn)單使用

1.導(dǎo)包
spring依賴文件+spring輔助文件+mysql相關(guān)+jdbc相關(guān)

<!-- 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.測(cè)試

public class Jdbcdemo1 {
    @Test
    public void demo1(){
        //創(chuàng)建連接詞
        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");
        //創(chuàng)建JDBC模板
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.update("insert into users values(?,?,?)","10","dff","asd");    
    }
}

將連接池交給spring管理

在上文的基礎(chǔ)上進(jìn)行改進(jìn)，將數(shù)據(jù)庫(kù)的連接交給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.測(cè)試

//將jdbc注入到數(shù)據(jù)中，也可以再xml文件里用bean配置 但是要有jdbcTemplate的set方法
@Resource(name="jdbcTemplate")
    private JdbcTemplate jdbcTemplate;
    @Test
    public void demo1(){
             //update執(zhí)行增刪改
        jdbcTemplate.update("insert into users values(?,?,?)","11","11","11");
    }

使用開(kāi)源的數(shù)據(jù)庫(kù)連接池

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>

將參數(shù)配置到屬性文件

以C3P0連接池為例
1.新建一個(gè)文件(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>

第二種方式：

<!-- 第二種，通過(guò)context標(biāo)簽 -->
<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>

對(duì)數(shù)據(jù)庫(kù)的數(shù)據(jù)進(jìn)行操作

以C3P0連接池為例

根據(jù)數(shù)據(jù)查詢

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

查詢總數(shù)

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

單個(gè)查詢

在返回值是自定義實(shí)體類時(shí)，需要使用RowMapper<自定義實(shí)體類>接口
1.編寫對(duì)應(yīng)實(shí)體類
2.在測(cè)試類中

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

3.測(cè)試

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

如果不想采用上文接口的方式，可以在測(cè)試類中：

@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);
        }