Spring之JDBC Template

 

时间:2017-2-5 18:16

 

——Spring对不同持久化技术之支撑

Spring为各种支持的持久化技术都提供了大概操作的模版与回调。

ORM持久化技术:
    JDBC:
        org.springframework.jdbc.core.JdbcTemplate
 

    Hibernate3.0:
        org.springframework.orm.hibernate3.HibernateTemplate
 

    IBatis(MyBatis):
        org.springframework.orm.ibatis.SqlMapClientTemplate
 

    JPA:
        org.springframework.orm.jpa.JpaTemplate

 

——JDBC Template

1、Spring JDBC是Spring提供的持久层技术。
2、简化JDBC API的付出,使用上和Apache公司的DBUtils框架非常相像。
3、导入必要jar包到工程目录。
4、导入Spring核心开发包
    >   spring-beans-3.2.0.RELEASE.jar

    >   spring-context-3.2.0.RELEASE.jar

    >   spring-core-3.2.0.RELEASE.jar

    >   spring-expression-3.2.0.RELEASE.jar

5、导入commons-logging日志包

    >   commons-logging-1.1.1.jar

6、导入JDBC模板开发包
    >   spring-jdbc-3.2.0.RELEASE.jar

        JDBC工具。

    >   spring-tx-3.2.0.RELEASE.jar
        事务管理。

7、导入MySQL驱动

——JDBC Template入门

1、导入相应jar包
    *   spring-jdbc-3.2.0.RELEASE.jar
    *   spring-tx-3.2.0.RELEASE.jar
    *   mysql-connector-java-5.1.35-bin.jar
    *   mchange-commons-java-0.2.3.4.jar

2、创建applicationContext.xml配置文件

3、编写一个测试接近

import org.junit.Test;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.datasource.DriverManagerDataSource;

 

public class SpringDemo1 {

 

    @Test

    public void fun1(){

        // 创建连接池

        DriverManagerDataSource dataSource = new
DriverManagerDataSource();

        // 设置参数

        dataSource.setDriverClassName(“com.mysql.jdbc.Driver”);

        dataSource.setUrl(“jdbc:mysql:///spring3_day02”);

        dataSource.setUsername(“root”);

        dataSource.setPassword(“Admin123”);

 

        // 使用JDBC的模板

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        // 执行SQL语句

        jdbcTemplate.execute(“create table User (id int primary key
auto_increment, name varchar(20))”);

    }

}

——使用Spring默认连接池

于正的言传身教中既见到,Spring
JDBC模板的采用,必须依赖DataSource数据库连接池。

当实际上付出被,通常以Spring配置文件来配置JDBC Template。

常用数据源:
    1)Spring数据源实现类:DriverManagerDataSource
    2)DBCP数据源:BasicDataSource
    3)C3P0数据源:ComboPooledDataSource

1、创建applicationContext.xml配置文件

<?xml version=”1.0″ encoding=”UTF-8″?>

<beans xmlns=”http://www.springframework.org/schema/beans”

    xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”
xmlns:aop=”http://www.springframework.org/schema/aop”

    xsi:schemaLocation=”

http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd”>

 

    <!– Spring默认连接池 –>

    <bean id=”dataSource”
class=”org.springframework.jdbc.datasource.DriverManagerDataSource”>

        <property name=”url” value=”jdbc:mysql:///spring3_day02″
/>

        <property name=”username” value=”root” />

        <property name=”password” value=”Admin123″ />

    </bean>
 

 

    <!– 定义JDBCTemplate类 –>

    <!– 为JDBC模板注入连接池属性 –>

    <bean id=”jdbcTemplate”
class=”org.springframework.jdbc.core.JdbcTemplate” >

        <property name=”dataSource” ref=”dataSource” />

    </bean>

</beans>

2、测试代码:

    @Test

    public void fun(){

        ApplicationContext context = new
ClassPathXmlApplicationContext(“applicationContext.xml”);

        jdbcTemplate.execute(“create table User3 (id int primary key
auto_increment, name varchar(20))”);

    }

——DBCP连接池

1、导入相应jar包
    *   com.springsource.org.apache.commons.dbcp-1.2.2.osgi.jar
    *   com.springsource.org.apache.commons.pool-1.5.3.jar

2、创建布局文件

<?xml version=”1.0″ encoding=”UTF-8″?>

<beans xmlns=”http://www.springframework.org/schema/beans”

    xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”
xmlns:aop=”http://www.springframework.org/schema/aop”

    xsi:schemaLocation=”

http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd”>

 

    <!– 配置DBCP连接池 –>

    <bean id=”dataSource”
class=”org.apache.commons.dbcp.BasicDataSource”>

        <property name=”driverClassName”
value=”com.mysql.jdbc.Driver” />

        <property name=”url” value=”jdbc:mysql:///spring3_day02″
/>

        <property name=”username” value=”root” />

        <property name=”password” value=”Admin123″ />

    </bean>

    <!– 定义JDBCTemplate类 –>

    <!– 为JDBC模板注入连接池属性 –>

    <bean id=”jdbcTemplate”
class=”org.springframework.jdbc.core.JdbcTemplate” >

        <property name=”dataSource” ref=”dataSource” />

    </bean>

 

</beans>

3、测试代码

    @Test

    public void fun(){

        ApplicationContext context = new
ClassPathXmlApplicationContext(“applicationContext.xml”);

        jdbcTemplate.execute(“create table User4 (id int primary key
auto_increment, name varchar(20))”);

    }

——C3P0连接池

1、导入相应jar包
    *   com.springsource.com.mchange.v2.c3p0-0.9.1.2.jar

2、创建布局文件

<?xml version=”1.0″ encoding=”UTF-8″?>

<beans xmlns=”http://www.springframework.org/schema/beans”

    xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”
xmlns:aop=”http://www.springframework.org/schema/aop”

    xsi:schemaLocation=”

http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd”>

    <!– 配置C3P0连接池 –>

    <bean id=”dataSource”
class=”com.mchange.v2.c3p0.ComboPooledDataSource”>

        <property name=”driverClass” value=”com.mysql.jdbc.Driver”
/>

        <property name=”jdbcUrl” value=”jdbc:mysql:///spring3_day02″
/>

        <property name=”user” value=”root” />

        <property name=”password” value=”Admin123″ />

 

    </bean>

 

    <!– 定义JDBCTemplate类 –>

    <!– 为JDBC模板注入连接池属性 –>

    <bean id=”jdbcTemplate”
class=”org.springframework.jdbc.core.JdbcTemplate” >

        <property name=”dataSource” ref=”dataSource” />

    </bean>
 

</beans>

 

3、测试代码

    public void fun(){

        ApplicationContext context = new
ClassPathXmlApplicationContext(“applicationContext.xml”);

        jdbcTemplate.execute(“create table User5 (id int primary key
auto_increment, name varchar(20))”);

    }

——将参数配置到性文件中

1、在src下创造jdbc.properties属性文件
    jdbc.driver = com.mysql.jdbc.Driver

    jdbc.url = jdbc:mysql:///spring3_day02

    jdbc.username = root

    jdbc.password = Admin123

2、在applicationContext.xml中应用性能文件配置的内容
    1)第一栽写法
        <?xml version=”1.0″ encoding=”UTF-8″?>

        <beans xmlns=”http://www.springframework.org/schema/beans”

            xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”
xmlns:aop=”http://www.springframework.org/schema/aop”

            xmlns:context=”http://www.springframework.org/schema/context”

            xsi:schemaLocation=”http://www.springframework.org/schema/beanshttp://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/contexthttp://www.springframework.org/schema/context/spring-context.xsd”>

 

            <bean

                class=”org.springframework.beans.factory.config.PropertyPlaceholderConfigurer”>

                <property name=”location”
value=”classpath:jdbc.properties” />

            </bean>

 

 

            <!– 配置C3P0连接池 –>

            <bean id=”dataSource”
class=”com.mchange.v2.c3p0.ComboPooledDataSource”>

                <property name=”driverClass” value=”${jdbc.driver}”
/>

                <property name=”jdbcUrl” value=”${jdbc.url}” />

                <property name=”user” value=”${jdbc.username}” />

                <property name=”password” value=”${jdbc.password}”
/>

            </bean>

            <!– 定义JDBCTemplate类 –>

            <!– 为JDBC模板注入连接池属性 –>

            <bean id=”jdbcTemplate”
class=”org.springframework.jdbc.core.JdbcTemplate”>

                <property name=”dataSource” ref=”dataSource” />

            </bean>

 

        </beans>

    2)第二种写法
        <?xml version=”1.0″ encoding=”UTF-8″?>

        <beans xmlns=”http://www.springframework.org/schema/beans”

            xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”
xmlns:aop=”http://www.springframework.org/schema/aop”

            xmlns:context=”http://www.springframework.org/schema/context”

            xsi:schemaLocation=”http://www.springframework.org/schema/beanshttp://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/contexthttp://www.springframework.org/schema/context/spring-context.xsd”>

 

            <context:property-placeholder location=”classpath:jdbc.properties”/>

 

            <!– 配置C3P0连接池 –>

            <bean id=”dataSource”
class=”com.mchange.v2.c3p0.ComboPooledDataSource”>

                <property name=”driverClass” value=”${jdbc.driver}”
/>

                <property name=”jdbcUrl” value=”${jdbc.url}” />

                <property name=”user” value=”${jdbc.username}” />

                <property name=”password” value=”${jdbc.password}”
/>

            </bean>

 

            <!– 定义JDBCTemplate类 –>

            <!– 为JDBC模板注入连接池属性 –>

            <bean id=”jdbcTemplate”
class=”org.springframework.jdbc.core.JdbcTemplate”>

                <property name=”dataSource” ref=”dataSource” />

            </bean>

        </beans>

 

——JDBCTemplate的CRUD操作

1、为了有利于Dao中注入JDBC
Template,Spring为各个一个持久化技术还提供了支撑类。
    1)JDBC:
        org.springframework.jdbc.core.support.JdbcDaoSupport

    2)Hibernate 3.0:
        org.springframework.orm.hibernate3.support.HibernateDaoSupport

    3)iBatis:
        org.springframework.orm.ibatis.support.SqlMapClientDaoSupport

2、编写Dao,并继承JdbcDaoSupport类。
    JdbcDaoSupport中提供了JDBCTemplate实例,所以当团结之Dao中未欲还定义JDBCTemplate并且提供注入了,可以一直以getJdbcTemplate()方法来抱实例。

3、进行CUD操作
    1)保存:update(String sql, Object … args)
    2)修改:update(String sql, Object … args)
    3)删除:update(String sql, Object … args)

4、查询操作
    1)简单询问
        *   select count(*) from user
            >   queryForLong(String sql)
            >   返回一个简单易行类型 
        *   select name from user where id = ?
            >   queryForObject(String sql, Class, Object…args)
            >   返回单个对象 

    2)复杂查询
        返回对象要集合。
        *   select * from user where id = ?
            >   queryForObject(String sql, RowMapper<T>
rowMapper, Object…args)
            >   返回单个对象 
        *   select * from user
            >   query(String sql, RowMapper<T> rowMapper,
Object … args)
            >   返回多独目标 

5、示例代码

User实体类:

public class User {

    private Integer id;

    private String name;

 

    public Integer getId() {

        return id;

    }

 

    public void setId(Integer id) {

        this.id = id;

    }

 

    public String getName() {

        return name;

    }

 

    public void setName(String name) {

        this.name = name;

    }

 

    @Override

    public String toString() {

        return “User [id=” + id + “, name=” + name + “]”;

    }

}


UserDao:

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.List;

 

import org.springframework.jdbc.core.RowMapper;

import org.springframework.jdbc.core.support.JdbcDaoSupport;

 

public class UserDao extends JdbcDaoSupport {

 

 

    public void add(User user) {

        String sql = “insert into user values (null, ?)”;
        // 因为JdbcDaoSupport中提供了JdbcTemplate,所以可以直接get获取

        getJdbcTemplate().update(sql, user.getName());

    }

 

    public void update(User user) {

        String sql = “update user set name = ? where id = ?”;

        getJdbcTemplate().update(sql, user.getName(), user.getId());

    }

 

    public void delete(User user) {

        String sql = “delete from user where id = ?”;

        getJdbcTemplate().update(sql, user.getId());

    }

 

    public long findCount(){

        String sql = “select count(*) from user”;

        return getJdbcTemplate().queryForLong(sql);

    }

 

    public String findNameById(int id){

        String sql = “select name from user where id = ?”;

        return getJdbcTemplate().queryForObject(sql, String.class, id);

    }

 

    public User findById(int id){

        String sql = “select * from user where id = ?”;

        return (User) getJdbcTemplate().queryForObject(sql,new
UserRowMapper(), id);

    }

 

    class UserRowMapper implements RowMapper<User>{

        /**

         * rs:结果集

         * rowNum:行号
         * 需要自己提供RowMapper实现,用来以记录封装成对象 

         */

        public User mapRow(ResultSet rs, int rowNum) throws SQLException
{

            User user = new User();

            user.setId(rs.getInt(“id”));

            user.setName(rs.getString(“name”));

            return user;

        }

    }

 

    public List<User> findAll(){

        String sql = “select * from user”;

        return getJdbcTemplate().query(sql, new UserRowMapper());

    }

}


XML配置文件:

<?xml version=”1.0″ encoding=”UTF-8″?>

<beans xmlns=”http://www.springframework.org/schema/beans”

    xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”
xmlns:aop=”http://www.springframework.org/schema/aop”

    xmlns:context=”http://www.springframework.org/schema/context”

    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”>

 

    <context:property-placeholder
location=”classpath:jdbc.properties” />

 

 

    <!– 配置C3P0连接池 –>

    <bean id=”dataSource”
class=”com.mchange.v2.c3p0.ComboPooledDataSource”>

        <property name=”driverClass” value=”${jdbc.driver}” />

        <property name=”jdbcUrl” value=”${jdbc.url}” />

        <property name=”user” value=”${jdbc.username}” />

        <property name=”password” value=”${jdbc.password}” />

    </bean>

 

    <!– 定义JDBCTemplate类 –>

    <!– 为JDBC模板注入连接池属性 –>

    <bean id=”jdbcTemplate”
class=”org.springframework.jdbc.core.JdbcTemplate”>

        <property name=”dataSource” ref=”dataSource” />

    </bean>

 

    <bean id=”userDao” class=”com.wyc.spring3.demo2.UserDao”>

        <property name=”jdbcTemplate” ref=”jdbcTemplate” />

    </bean>

 

</beans>

 


测试代码:

import java.util.List;

 

import org.junit.Test;

import org.junit.runner.RunWith;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.beans.factory.annotation.Qualifier;

import org.springframework.test.context.ContextConfiguration;

import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

 

@RunWith(SpringJUnit4ClassRunner.class)

@ContextConfiguration(“classpath:applicationContext.xml”)

public class SpringDemo2 {

 

    @Autowired

    @Qualifier(“userDao”)

    private UserDao userDao;

 

    @Test

    // 添加

    public void fun1() {

        User user = new User();

        user.setName(“王五”);

        userDao.add(user);

    }

 

    @Test

    // 修改

    public void fun2() {

        User user = new User();

        user.setId(1);

        user.setName(“李四”);

        userDao.update(user);

    }

 

    @Test

    // 删除

    public void fun3() {

        User user = new User();

        user.setId(1);

        userDao.delete(user);

    }

 

    @Test

    // 查询记录数据

    public void fun4() {

        System.out.println(userDao.findCount());

    }

 

    @Test

    // 查询姓名

    public void fun5() {

        System.out.println(userDao.findNameById(2));

    }

 

    @Test

    // 查询单个对象

    public void fun6() {

        System.out.println(userDao.findById(3));

    }

 

    @Test

    // 查询任何目标

    public void fun7() {

        List<User> list = userDao.findAll();

        for (User user : list) {

            System.out.println(user);

        }

    }

}

——总结

1、配置连接池:
     1)默认连接池
    2)DBCP连接池
    3)C3P0连接池
        *   提取properties文件
2、JdbcTemplate的CRUD操作 

   
Dao继承JdbcDaoSupport之后,可以无在Dao中注入JdbcTemplate对象了,因为JdbcDaoSupport中已经定义了JdbcTemplate实例。

网站地图xml地图