Spring操作資料庫

通過jdbc對MySQL資料庫進行操作

所需jar包

配置Spring容器（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-3.2.xsd
	http://www.springframework.org/schema/aop
	http://www.springframework.org/schema/aop/spring-aop-3.2.xsd">
    <!--擷取資料源-->
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
    <!--拿到連接配接資料庫的資訊-->
        <property name="url" value="jdbc:mysql://localhost:3306/account?useUnicode=true&amp;characterEncoding=utf-8"/>
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
        <property name="username" value="root" />
        <property name="password" value="root" />
    </bean>
    <!--Spring 提供操作資料庫的類-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource" />
    </bean>
           

<!--配置Dao層-->
    <bean id="accountDao" class="com.offcn.dao.impl.AccountDaoImpl">
        <property name="jdbcTemplate" ref="jdbcTemplate" />
    </bean>
        <!--配置Service層-->
    <bean id="accountService" class="com.offcn.service.impl.AccountServiceIml">
        <property name="accountDao" ref="accountDao" />

    </bean>
</beans>
           

Dao接口

public interface AccountDao {

public Account selectById(int aid);

/**
 * 查詢全部
 */
public List<Account> selectAll();

/**
 *修改一個使用者
 * @param account
 * @return
 */
public int updateById(Account account);

/**
 * 增加一個使用者
 * @param account
 * @return
 */
public int addAccount(Account account);

/**
 * 删除一個使用者
 * @param aid
 * @return
 */
public int deleteById(int aid);
           

}

Dao的實作類：

1.隻有查詢才用到這個對象RowMapper(傳回每一行得到記錄)

2.Spring提供JdbcTemplate來操作資料庫

3.所有操作不要忘了注入（也就是配置xml檔案）

public class AccountDaoImpl implements AccountDao{
    //Spring提供操作資料庫的類
    private JdbcTemplate jdbcTemplate;
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

        @Override
        public Account selectById(int aid) {
             String sql="select * from account where aid = ?";
             Object [] obj={aid};
            /**
             * 傳回的每一行資料都在和這個類裡，第一個參數是傳回的結果集，第二個參數是目前的索引
             */
            RowMapper<Account> rowMapper = new RowMapper<Account>() {
                @Override
                public Account mapRow(ResultSet resultSet, int i) throws SQLException {
                    Account account = new Account();
                    account.setAid(resultSet.getInt("aid"));
                    account.setAbalance(resultSet.getDouble("abalance"));
                    return account;
                }
            };
            Account account1 = jdbcTemplate.queryForObject(sql, obj, rowMapper);
            return account1;
        }

    @Override
    public List<Account> selectAll() {
        String sql = "select * from account";
        RowMapper<Account> accountRowMapper = new RowMapper<Account>() {
            @Override
            public Account mapRow(ResultSet resultSet, int i) throws SQLException {
                Account account = new Account();
                account.setAid(resultSet.getInt("aid"));
                account.setAbalance(resultSet.getDouble("abalance"));
                return account;
            }
        };
        List<Account> list = jdbcTemplate.query(sql, accountRowMapper);

        return list;
    }

    @Override
    public int updateById(Account account) {
        String sql = "update account set abalance=? where aid=?";
        Object [] obj = {account.getAbalance(),account.getAid()};
        int update = jdbcTemplate.update(sql, obj);
        return update;
    }

    @Override
    public int addAccount(Account account) {
        String sql="insert into account(abalance) values(?)";
        Object [] obj = {account.getAbalance()};
        int update = jdbcTemplate.update(sql, obj);
        return update;
    }

    @Override
    public int deleteById(int aid) {
        String sql="delete from account where aid = ?";
        Object [] obj = {aid};
        int update = jdbcTemplate.update(sql, obj);
        return update;
    }
}
           

測試結果：

注意：

① & 在xml檔案裡配置都是 &

②加載驅動與平時注入方式不同

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