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 {
/**
* 根据ID查询
* @param aid
* @return
*/
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” />