Spring（七）JdbcTemplate

Spring（七）JdbcTemplate 

Spring自己提供了一个工具-JdbcTemplate

它封装了操作数据库的各种方法，该类包含一个dataSource属性（数据源），只有在初始化数据源的情况下才能调用JdbcTemplate的方法。

数据源为主流连接池的数据源对象（例如C3P0，DBCP数据库连接池），因此在使用JdbcTemplate前，要创建数据源对象。

使用spring集成jdbc包含如下几步： 导入集成包、连接池包；初始化连接池数据源对象；初始化JdbcTemplate对象； 调用JdbcTemplate的API接口完成数据库操作。

一、连接池

Spring 使用连接池进行数据库的连接
jdbc没有维持连接的能力，若采用C3P0连接池可以根据配置初始化N个数据库的连接

jdbc创建连接需要140毫秒左右，而连接池仅用10-20毫秒

连接池可以保证连接最新、最快。

二、ComboPooledDataSource--c3p0数据源（数据库连接池）

1、通过创建对象连接数据库、插入数据

public class JdbcDemo1 {
    public static void main(String[] args) throws PropertyVetoException {
        //创建连接池对象 c3p0 相当于数据源
        ComboPooledDataSource c3p0 = new ComboPooledDataSource();
        c3p0.setDriverClass("com.mysql.jdbc.Driver");
        c3p0.setJdbcUrl("jdbc:mysql://localhost:3306/ssm?characterEncoding=utf-8&useSSL=false");
        c3p0.setUser("root");
        c3p0.setPassword("123456");
        JdbcTemplate jdbcTemplate = new JdbcTemplate();
        jdbcTemplate.setDataSource(c3p0); //插入一个数据源

        jdbcTemplate.update("INSERT INTO USER (NAME, PASSWORD, sex, hobbys) VALUES (?, ?, ?, ?)", "Mary", "1234", "男", "游泳");
    }
}

 2、通过IOC、DI（工厂+配置文件+反射）实现：

public class JdbcDemoXml {
    public static void main(String[] args) {
        ApplicationContext context = new ClassPathXmlApplicationContext("jdbc.xml");
        JdbcTemplate contextBean  = (JdbcTemplate)context.getBean("jdbcTemplate");
        contextBean.update("INSERT INTO USER VALUES (0004, 'Linda', '12345678', '女', '看书')");
    }
}

.xml文件：

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:context = "http://www.springframework.org/schema/context"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
                          http://www.springframework.org/schema/beans/spring-beans.xsd
                          http://www.springframework.org/schema/context
                          http://www.springframework.org/schema/context/spring-context.xsd
                          ">
       <!--c3p0数据库连接池-->
<!--       <bean id="c3p0" class="com.mchange.v2.c3p0.ComboPooledDataSource">-->
<!--              <property name="driverClass" value="com.mysql.jdbc.Driver"></property>-->
<!--              <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/ssm?characterEncoding=utf-8&useSSL=false"></property>-->
<!--              <property name="user" value="root"></property>-->
<!--              <property name="password" value="123456"></property>-->
<!--       </bean>-->

       <bean id="c3p0" class="com.mchange.v2.c3p0.ComboPooledDataSource">
              <property name="driverClass" value="${driverClass}"></property>
              <property name="jdbcUrl" value="${jdbcUrl}"></property>
              <property name="user" value="${user}"></property>
              <property name="password" value="${password}"></property>
       </bean>

       <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
              <property name="dataSource" ref="c3p0"></property>
       </bean>
      <!-- <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
              <property name="location" value="jdbc.properties"></property>
       </bean>-->
       <context:property-placeholder location = "jdbc.properties"></context:property-placeholder>
</beans>

jdbc.properties文件：

driverClass=com.mysql.jdbc.Driver
jdbcUrl=jdbc:mysql://localhost:3306/ssm?characterEncoding=utf-8&useSSL=false
user=root
password=123456

三、BasicDataSource--DBCP数据源（数据库连接池）

1、通过创建对象连接数据库

public class JdbcDem3 {
    public static void main(String[] args) {
        BasicDataSource basicDataSource = new BasicDataSource();
        basicDataSource.setDriverClassName("com.mysql.jdbc.Driver");
        basicDataSource.setUrl("jdbc:mysql://localhost:3306/ssm?characterEncoding=utf-8&useSSL=false");
        basicDataSource.setUsername("root");
        basicDataSource.setPassword("root");

        JdbcTemplate jdbcTemplate = new JdbcTemplate();
        jdbcTemplate.setDataSource(basicDataSource);
        jdbcTemplate.update("INSERT INTO USER VALUES (0004, 'Linda', '12345678', '女', '看书')");
    }
}

2、通过IOC、DI（工厂+配置文件+反射）实现：

public class JdbcDemoXml {
    public static void main(String[] args) {
        ApplicationContext context = new ClassPathXmlApplicationContext("jdbc3.xml");
        JdbcTemplate contextBean  = (JdbcTemplate)context.getBean("jdbcTemplate");
        contextBean.update("INSERT INTO USER VALUES (0004, 'Linda', '12345678', '女', '看书')");
    }
}

.xml文件：

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:context = "http://www.springframework.org/schema/context"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
                           http://www.springframework.org/schema/beans/spring-beans.xsd
                           http://www.springframework.org/schema/context
                           http://www.springframework.org/schema/context/spring-context.xsd">
    <bean id="basicDataSource" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="#{driverClass}"></property>
        <property name="url" value="#{jdbcUrl}"></property>
        <property name="username" value="#{user}"></property>
        <property name="password" value="#{password}"></property>
    </bean>
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="basicDataSource"></property>
    </bean>
    <context:proterty-placeholder location = "jdbc.properties"></context:proterty-placeholder>
</beans>

jdbc.properties文件：

driverClass=com.mysql.jdbc.Driver
jdbcUrl=jdbc:mysql://localhost:3306/ssm?characterEncoding=utf-8&useSSL=false
user=root
password=123456

四、数据的查询

public class JdbcDemoSelect {
    public static void main(String[] args) {
        ClassPathXmlApplicationContext classPathXmlApplicationContext = new ClassPathXmlApplicationContext("jdbc.xml");
        JdbcTemplate jdbc = (JdbcTemplate) classPathXmlApplicationContext.getBean("jdbcTemplate");
        List<User> list = jdbc.query("select * from user", new RowMapper<User>() {

            @Override
            public User mapRow(ResultSet resultSet, int i) throws SQLException {
                User user = new User();
                user.setId(resultSet.getInt("id"));
                user.setName(resultSet.getString("name"));
                user.setPassword(resultSet.getString("password"));
                user.setSex(resultSet.getString("sex"));
                user.setHobbys(resultSet.getString("hobbys"));
                return user;
            }
        });
        for (User u : list) {
            System.out.println(u);
        }
        System.out.println("成功");
    }
}

User类：

public class User {
    private Integer id;
    private String name;
    private String password;
    private String sex;
    private String hobbys;

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

    public void setName(String name) {
        this.name = name;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public void setHobbys(String hobbys) {
        this.hobbys = hobbys;
    }
    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", password='" + password + '\'' +
                ", sex='" + sex + '\'' +
                ", hobbys='" + hobbys + '\'' +
                '}';
    }
}