JDBC在Spring中的应用

JDBC在Spring框架中的使用方法如下:

1.在applicationContext.xml中对数据源和JDBC模版配置如下:

  <!-- 配置数据源 -->
    <bean id="dataSource"
          class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/dangdang"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
    </bean>
    <!-- JDBC模版-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>
    <bean id="accountDao" class="com.itheima.jdbc.AccountDaoImpl">
        <property name="jdbcTemplate" ref="jdbcTemplate"/>
    </bean>

代码中定义了三个Bean,dataSource,jdbcTemplate,和需要注入类的bean.

2.JDBCTemplate中有大量的对数据库更新查询的操作

(1)execute()方法,下面代码用JDBCTemplate的execute()方法创建了account表.前提是applicationContext.xml已经配置好.

public class JdbcTemplateTest {
    public static void main(String[] args) {
        //加载配置文件
        ApplicationContext application =
                new ClassPathXmlApplicationContext("applicationContext.xml");
        //获取jdbcTemplate对象
        JdbcTemplate jdbcTemplate = (JdbcTemplate) application.getBean("jdbcTemplate");
        //用execute()执行sql语句,创建表account
        jdbcTemplate.execute("create table account(" +
                "id int PRIMARY  KEY  auto_increment," +
                "username VARCHAR (40))" +
                "balance double)");
    }
}

(2)update()方法,update()方法可以完成插入、更新和删除数据的操作。在JdbcTemplate类中，提供了一系列的update()方法，其常用方法下表所示：

以下是用update()方法对数据库操作的小例子:

1>先创建一个Account类(同时在数据库中创建好表与之对应)

package com.itheima.jdbc;

public class Account {
    private Integer id;
    private String username;
    private Double balance;

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public Double getBalance() {
        return balance;
    }

    public void setBalance(Double balance) {
        this.balance = balance;
    }

    public Integer getId() {
        return id;
    }

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

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", balance=" + balance +
                '}';
    }
}

2>再创建AccountDao以及实现类AccountDaoImpl

package com.itheima.jdbc;

import java.util.List;

public interface AccountDao {
    public int addAccount(Account account);
    public int updateAccount(Account account);
    public int deleteAccount(int id);
}

package com.itheima.jdbc;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.util.List;

public class AccountDaoImpl implements AccountDao {
    private JdbcTemplate jdbcTemplate;

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public int addAccount(Account account) {
        String sql = "insert into account(username,balance) values(?,?)";
        Object[] objects = new Object[]{
                account.getUsername(),
                account.getBalance()
        };
        int num = this.jdbcTemplate.update(sql, objects);
        return num;
    }

    @Override
    public int updateAccount(Account account) {
        String sql = "update account set username=?,balance=? where id=?";
        Object[] params = new Object[]{
                account.getUsername(),
                account.getBalance(),
                account.getId()

        };
        int num = this.jdbcTemplate.update(sql, params);
        return num;
    }

    @Override
    public int deleteAccount(int id) {
        String sql = "delete from account where id=?";
        int num = this.jdbcTemplate.update(sql, id);
        return num;
    }
}

3>xml中需要配置accountDao,将jdbcTemplate注入其中.

<bean id="accountDao" class="com.itheima.jdbc.AccountDaoImpl">
        <property name="jdbcTemplate" ref="jdbcTemplate"/>
    </bean>

4>编写测试类, 此例子为添加的代码,删除,修改同理

 ApplicationContext application =
                new ClassPathXmlApplicationContext("applicationContext.xml");
        AccountDao accountDao = (AccountDao) application.getBean("accountDao");
        Account account = new Account();
        account.setUsername("tom");
        account.setBalance(1000.00);
        int num = accountDao.addAccount(account);

3>query()方法 JdbcTemplate类中还提供了大量的query()方法来处理各种对数据库表的查询操作。其中，常用的几个query()方法如下表所示：

以下是用query()方法对数据库操作的小例子:

1>在AccountDao和 AccountImpl中添加以下代码

    public Account findAccountById(int id);
    
    public List<Account> findAllCount();

    @Override
    public Account findAccountById(int id) {
        String sql = "select * from account where id =?";
        RowMapper<Account> rowMapper = new
                BeanPropertyRowMapper<Account>(Account.class);
        return this.jdbcTemplate.queryForObject(sql,rowMapper,id);
    }

    @Override
    public List<Account> findAllCount() {
        String sql = "select * from account";
        RowMapper<Account> rowMapper = new
                BeanPropertyRowMapper<Account>(Account.class);
        return this.jdbcTemplate.query(sql,rowMapper);
    }

2> 测试类

        ApplicationContext application =
                new ClassPathXmlApplicationContext("applicationContext.xml");
        AccountDao accountDao = (AccountDao) application.getBean("accountDao");
        List<Account> accountList=accountDao.findAllCount();
        for (Account act : accountList) {
            System.out.println(act);
        }
    }

上面是用query()查询所有记录的测试,另一个方法findAccountById()同理.

上面代码中的RowMapper可以自动的将数据库中的表的数据映射到用户自定义的类(Account)中.