Spring JDBC入门

1. Spring JDBC 模板

Spring 是EE的一站式开发框架，对持久层同样提供了支持：ORM模块和JDBC模板

Spring 提供了很多模板简化了开发

• spring中提供了一个可以操作数据库的对象，对象封装了jdbc技术。
• 与DBUtils中QueryRunner非常相似

2. JDBC模板的使用

基本使用方法

1. 创建数据库

   mysql> show create table account;
   
   | Table   | Create Table| account | 
   CREATE TABLE `account` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `name` char(10) DEFAULT NULL,
     `money` double DEFAULT NULL,
     PRIMARY KEY (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8

2. public class JDBCTemplateTest {
    @Test
    public void testadd(){
        // 创建连接池
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/spring");
        dataSource.setUsername("cyp");
        dataSource.setPassword("cyp");
   
        // 创建JDBC 模板
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.update("insert into account values(null,?,?)", "陈亚平",2000);
   
    }
   
   }

3. mysql> select *from account;
   +----+-----------+-------+
   | id | name      | money |
   +----+-----------+-------+
   |  1 | 陈亚平    |  2000 |
   +----+-----------+-------+
   1 row in set (0.00 sec)

整合Spring（spring管理dataSource和JdbcTemplate）

1. Spring 配置文件配置

   <bean name="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
            <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
            <property name="url" value="jdbc:mysql://localhost:3306/spring"></property>
            <property name="username" value="cyp"></property>
            <property name="password" value="cyp"></property>
   </bean>
   <bean name="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
       <property name="dataSource" ref="dataSource"></property>
   </bean>

2. 测试类

   @RunWith(SpringJUnit4ClassRunner.class)
   @ContextConfiguration("classpath:applicationContext.xml")
   public class JDBCTemplateTestWithSpring {
    @Resource(name="jdbcTemplate")
    private JdbcTemplate jdbcTemplate;
   
    @Test
    public void test1(){
        jdbcTemplate.update("insert into account values(null,?,?)", "王红琳",2000);
    }
   }

c3p0 连接池的使用

1. 导入包

   c3p0.jar

2. Spring中配置连接池

   <bean name="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
            <property name="driverClass" value="com.mysql.jdbc.Driver"></property>
            <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/spring"></property>
            <property name="user" value="cyp"></property>
            <property name="password" value="cyp"></property>
        </bean>

3. 显示结果

使用属性文件配置连接池

1. 创建属性文件jdbc.properteis

   jdbc.driver=com.mysql.jdbc.Driver
   jdbc.url=jdbc:mysql://localhost:3306/spring
   jdbc.user=cyp
   jdbc.password=cyp

2. 配置文件中配置属性文件

   <!-- 配置属性文件 -->
   <!-- 第一种方式：配置一个bean  ,较少使用 -->
   <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
       <property name="location" value="classpath:jdbc.properteis"/>        
    </bean> 
   <!-- 第二种方式  使用context 标签-->
   <context:property-placeholder location="classpath:jdbc.properteis"/><!-- 配置属性文件 -->

3. 使用配置

   <!-- 使用c3p0 连接池-->
   <bean name="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
       <property name="driverClass" value="${jdbc.driver}"></property>
       <property name="jdbcUrl" value="${jdbc.url}"></property>
       <property name="user" value="${jdbc.user}"></property>
       <property name="password" value="${jdbc.password}"></property>
   </bean>

3. 模板的 CRUD操作

1. 编写bean

   public class Account {
    private int id;
    private String name;
    private double money;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public double getMoney() {
        return money;
    }
    public void setMoney(double money) {
        this.money = money;
    }
    @Override
    public String toString() {
        return "Account [id=" + id + ", name=" + name + ", money=" + money + "]";
    }
   
   
   }

2. 编写AccountDaoImpl实现类,实现基本操作

   public class AccountDaoImpl implements AccountDao{
   
    private JdbcTemplate jdbcTemplate;
   
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
   
    @Override
    public void add(Account account) {
   
        String sql = "insert into account values(null,?,?)";
        jdbcTemplate.update(sql, account.getName(),account.getMoney());
    }
   
    @Override
    public void delete(Integer id) {
   
        String sql = "delete from account where id = ?";
        jdbcTemplate.update(sql, id);
    }
   
    @Override
    public void update(Account account) {
        String sql = "update account set name=? ,money=? where id=?";
        jdbcTemplate.update(sql, account.getName(),account.getMoney(),account.getId());
   
    }
   
    @Override
    public Account find(Integer id) {
        String sql = "select * from account where id = ?";
        return jdbcTemplate.queryForObject(sql, new RowMapper<Account>(){
            @Override
            public Account mapRow(ResultSet rs, int arg1) throws SQLException {
                Account account = new Account();
                account.setId(rs.getInt("id"));
                account.setName(rs.getString("name"));
                account.setMoney(rs.getDouble("money"));
                return account;
            }
        }, id);
   
    }
   
    @Override
    public int getTotalCount() {
        String sql="select count(*) from account";
           Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
           return count;
   
    }
   
   }

3. 配置文件

   <context:property-placeholder location="classpath:jdbc.properteis"/>
   <!-- 使用c3p0 连接池-->
   <bean name="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
       <property name="driverClass" value="${jdbc.driver}"></property>
       <property name="jdbcUrl" value="${jdbc.url}"></property>
       <property name="user" value="${jdbc.user}"></property>
       <property name="password" value="${jdbc.password}"></property>
   </bean>
   
   <!-- 配置JdbcTemplate -->
   <bean name="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
       <property name="dataSource" ref="dataSource"></property>
   </bean>
   
   <!-- 配置UserDao -->
   <bean name="accountDao" class="com.ranger.spring.jdbc.dao.impl.AccountDaoImpl">
       <property name="jdbcTemplate" ref="jdbcTemplate"></property>
   </bean>

4. 编写测试类和测试方法

   @RunWith(SpringJUnit4ClassRunner.class)
   @ContextConfiguration("classpath:applicationContext.xml")
   public class AccountDaoTest {
    @Resource(name="accountDao")
    private AccountDao accountDao;
    @Test
    public void add(){
        Account account = new Account();
        account.setName("小风");
        account.setMoney(2000);
        accountDao.add(account);
    } 
   
    @Test
    public void delete(){
   
        accountDao.delete(2);
    } 
   
    @Test
    public void update(){
        Account account = new Account();
        account.setId(4);
        account.setName("小王");
        account.setMoney(2000);
        accountDao.update(account);
    }
   
    @Test
    public void find(){
   
        Account account = accountDao.find(1);
        System.out.println(account);
    }
   
    @Test
    public void getTotal(){
   
        int count = accountDao.getTotalCount();
        System.out.println(count);
    }
   }