spring操作数据库(JDBC)

spring操作数据库(JDBC)概述

        spring为了简化JDBC开发操作,避免一下常见错误,提供了一个类JdbcTemplate,使用这个类前需要传入一个数据库连接池(BasicDataSource对象)。所以在配置JdbcTemplate前,需要配置数据库连接池BasicDataSource。

配置文件beans.xml配置步骤

第一步:配置数据库连接池(beans.xml)

参考代码如下:

<!-- 连接池基本配置信息 -->
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
    <property name="url" value="jdbc:mysql://localhost:3306/springdb?useUnicode=true&amp;characterEncoding=utf8"></property>
    <property name="username" value="root"></property>
    <property name="password" value="liuxin950326"></property>
    
    <!-- 连接池启动时的初始值 -->
    <property name="initialSize" value="10"></property>
    <!-- 连接池的最大值 -->
    <property name="maxActive" value="80"></property>
    <!-- 最大空闲值.经过一个高峰时间,连接池可将已用不到连接慢慢释放一部分,一直减少到maxIdle为止 -->
    <property name="maxIdle" value="5"></property>
    <property name="minIdle" value="2"></property>
</bean>

第二步:配置JDBC模板JdbcTemplate类(beans.xml)

参考代码如下:

<!-- 配置JDBC模板 -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <property name="dataSource" ref="dataSource"></property>
</bean>

第三步:配置Dao层(beanx,xml)

<!-- 配置Dao层 -->
<bean id="bankAccountDao" class="www.enfp.lx_03_jdbc.lx_02_crud.BankAccountDaoImpl">
    <property name="jdbcTemplate" ref="jdbcTemplate"></property>
</bean>

JdbcTemplate操作数据库(增删改查)

1.增

public int update(String sql, Object... args) throws DataAccessException

Description copied from interface: JdbcOperations

Issue a single SQL update operation (such as an insert, update or delete statement) via a prepared statement, binding the given arguments.

Specified by:

update in interface JdbcOperations

Parameters:

sql - SQL containing bind parameters

args - arguments to bind to the query (leaving it to the PreparedStatement to guess the corresponding SQL type); may also contain SqlParameterValue objects which indicate not only the argument value but also the SQL type and optionally the scale

Returns:

the number of rows affected

Throws:

DataAccessException - if there is any problem issuing the update

参考代码如下:

//增
{
    String sql = "insert into bankaccount(accountname,balance) value(?,?)";
    Object[] args = { account.getAccountName(), account.getBalance() };
    this.getJdbcTemplate().update(sql, args);
}

//删
{
    String sql = "delete from bankaccount where accountname=?";
    Object[] args = { accountName };
    this.getJdbcTemplate().update(sql, args);

}

//改
{
    String sql = "update bankaccount set balance=? where accountname=?";
    Object[] args = { account.getBalance(), account.getAccountName() };
    this.getJdbcTemplate().update(sql, args);
}

2.查

(1).查询多条记录

public  List query(String sql, RowMapper rowMapper) throws DataAccessException

Description copied from interface: JdbcOperations

Execute a query given static SQL, mapping each row to a Java object via a RowMapper.

Uses a JDBC Statement, not a PreparedStatement. If you want to execute a static query with a PreparedStatement, use the overloaded query method with null as argument array.

Specified by:

query in interface JdbcOperations

Parameters:

sql - SQL query to execute

rowMapper - object that will map one object per row

Returns:

the result List, containing mapped objects

Throws:

DataAccessException - if there is any problem executing the query

See Also:

JdbcOperations.query(String, Object[], RowMapper)

注意:RowMapper是一个接口,在JdbcTemplate中用于映射查询的结果集ResultSet中的每一行,实际使用中必须实现这个接口中的mapRow(ResultSet rs, int rowNum)方法。

T mapRow(ResultSet rs, int rowNum) throws SQLException

Implementations must implement this method to map each row of data in the ResultSet. This method should not call next()on the ResultSet; it is only supposed to map values of the current row.

Parameters:

rs - the ResultSet to map (pre-initialized for the current row)

rowNum - the number of the current row

Returns:

the result object for the current row

Throws:

[SQLException](https://www.oschina.net/action/GoToLink?url=http%3A%2F%2Fdocs.oracle.com%2Fjavase%2F8%2Fdocs%2Fapi%2Fjava%2Fsql%2FSQLException.html%3Fis-external%3Dtrue) - if a SQLException is encountered getting column values (that is, there's no need to catch SQLException)

参考代码如下:

String sql = "select accountname,balance from bankaccount";
RowMapper rowMapper = new RowMapper()
{

    @Override
    public Object mapRow(ResultSet rs, int rowNum) throws SQLException
    {
        BankAccount account = new BankAccount();
        account.setAccountName(rs.getString("accountname"));
        account.setBalance(rs.getDouble("balance"));
        return account;
    }

};
List<BankAccount> bankAccountList = this.getJdbcTemplate().query(sql,rowMapper);

(2).查询单条记录

public  T queryForObject(String sql, Object[] args, RowMapper rowMapper) throws DataAccessException

Description copied from interface: JdbcOperations

Query given SQL to create a prepared statement from SQL and a list of arguments to bind to the query, mapping a single result row to a Java object via a RowMapper.

Specified by:

queryForObject in interface JdbcOperations

Parameters:

sql - SQL query to execute

args - arguments to bind to the query (leaving it to the PreparedStatement to guess the corresponding SQL type); may also contain SqlParameterValue objects which indicate not only the argument value but also the SQL type and optionally the scale

rowMapper - object that will map one object per row

Returns:

the single mapped object

Throws:

IncorrectResultSizeDataAccessException - if the query does not return exactly one row

DataAccessException - if the query fails

参考代码如下:

String sql = "select accountname,balance from bankaccount where accountname=?";
Object[] args = { accountName };
RowMapper rowMapper = new RowMapper()
{

    @Override
    public Object mapRow(ResultSet rs, int rowNum) throws SQLException
    {
        BankAccount account = new BankAccount();
        account.setAccountName(rs.getString("accountname"));
        account.setBalance(rs.getDouble("balance"));
        return account;
    }

};
BankAccount account = this.getJdbcTemplate().queryForObject(sql, rowMapper, args);

案例:spring实现数据库增删改查

1.beans.xml配置文件

参考代码如下:beans.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:context="http://www.springframework.org/schema/context"
    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.0.xsd
           http://www.springframework.org/schema/context
           http://www.springframework.org/schema/context/spring-context-3.0.xsd
           http://www.springframework.org/schema/aop
           http://www.springframework.org/schema/aop/spring-aop-3.0.xsd">

    <!-- 连接池基本配置信息 -->
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
        <property name="url" value="jdbc:mysql://localhost:3306/springdb?useUnicode=true&amp;characterEncoding=utf8"></property>
        <property name="username" value="root"></property>
        <property name="password" value="liuxin950326"></property>
        
        <!-- 连接池启动时的初始值 -->
        <property name="initialSize" value="10"></property>
        <!-- 连接池的最大值 -->
        <property name="maxActive" value="80"></property>
        <!-- 最大空闲值.经过一个高峰时间,连接池可将已用不到连接慢慢释放一部分,一直减少到maxIdle为止 -->
        <property name="maxIdle" value="5"></property>
        <property name="minIdle" value="2"></property>
    </bean>
    
    
    <!-- 配置JDBC模板 -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>
    
    
    <!-- 配置Dao层 -->
    <bean id="bankAccountDao" class="www.enfp.lx_03_jdbc.lx_02_crud.BankAccountDaoImpl">
        <property name="jdbcTemplate" ref="jdbcTemplate"></property>
    </bean>
    
    
</beans>      

2.pojo(银行账户)

参考代码如下:BankAccount.java

package www.enfp.lx_03_jdbc.lx_02_crud;

public class BankAccount
{
    private String accountName = null;
    private double balance = 0;

    @Override
    public String toString()
    {
        return "accountName:" + this.accountName + "\t balance:" + this.balance;
    }

    public String getAccountName()
    {
        return accountName;
    }

    public void setAccountName(String accountName)
    {
        this.accountName = accountName;
    }

    public double getBalance()
    {
        return balance;
    }

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

    public BankAccount(String accountName, double balance)
    {
        super();
        this.accountName = accountName;
        this.balance = balance;
    }

    public BankAccount()
    {
        super();
    }

}

3.Dao类

参考代码如下:

IBankAccountDao.java接口

package www.enfp.lx_03_jdbc.lx_02_crud;

import java.util.List;

public interface IBankAccountDao
{
    public void addBankAccount(BankAccount account);// 增

    public void deleteBankAccount(String accountName);// 删

    public void updateBankAccount(BankAccount account);// 改

    // 查
    public List<BankAccount> queryAllBankAccount();

    public BankAccount queryBankAccountByAccountName(String accountName);

}

BankAccountDaoImpl.java类

package www.enfp.lx_03_jdbc.lx_02_crud;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

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

public class BankAccountDaoImpl implements IBankAccountDao
{
    private JdbcTemplate jdbcTemplate = null;

    public JdbcTemplate getJdbcTemplate()
    {
        return jdbcTemplate;
    }

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

    @Override
    public void addBankAccount(BankAccount account)
    {
        String sql = "insert into bankaccount(accountname,balance) value(?,?)";
        Object[] args = { account.getAccountName(), account.getBalance() };
        this.getJdbcTemplate().update(sql, args);
    }

    @Override
    public void deleteBankAccount(String accountName)
    {
        String sql = "delete from bankaccount where accountname=?";
        Object[] args = { accountName };
        this.getJdbcTemplate().update(sql, args);

    }

    @Override
    public void updateBankAccount(BankAccount account)
    {
        String sql = "update bankaccount set balance=? where accountname=?";
        Object[] args = { account.getBalance(), account.getAccountName() };
        this.getJdbcTemplate().update(sql, args);
    }

    @Override
    public List<BankAccount> queryAllBankAccount()
    {
        String sql = "select accountname,balance from bankaccount";
        RowMapper rowMapper = new RowMapper()
        {

            @Override
            public Object mapRow(ResultSet rs, int rowNum) throws SQLException
            {
                BankAccount account = new BankAccount();
                account.setAccountName(rs.getString("accountname"));
                account.setBalance(rs.getDouble("balance"));
                return account;
            }

        };
        List<BankAccount> bankAccountList = this.getJdbcTemplate().query(sql,
                rowMapper);
        return bankAccountList;
    }

    @Override
    public BankAccount queryBankAccountByAccountName(String accountName)
    {
        String sql = "select accountname,balance from bankaccount where accountname=?";
        Object[] args = { accountName };
        RowMapper rowMapper = new RowMapper()
        {

            @Override
            public Object mapRow(ResultSet rs, int rowNum) throws SQLException
            {
                BankAccount account = new BankAccount();
                account.setAccountName(rs.getString("accountname"));
                account.setBalance(rs.getDouble("balance"));
                return account;
            }

        };
        BankAccount account = this.getJdbcTemplate().queryForObject(sql,
                rowMapper, args);
        return account;
    }

}

4.测试

参考代码如下:test.java

package www.enfp.lx_03_jdbc.lx_02_crud;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class Test
{

    public static void main(String[] args)
    {
        ApplicationContext container = new ClassPathXmlApplicationContext(
                "www/enfp/lx_03_jdbc/lx_02_crud/beans.xml");

        IBankAccountDao accountDao = (IBankAccountDao) container
                .getBean("bankAccountDao");
        // 添加
        // accountDao.addBankAccount(new BankAccount("zhangsan", 1000));
        // accountDao.addBankAccount(new BankAccount("lisi", 1000));
        // accountDao.addBankAccount(new BankAccount("wangxiaojian", 1000));

        // 删除
        // accountDao.deleteBankAccount("zhangsan");

        // 修改
        // accountDao.updateBankAccount(new BankAccount("lisi", 2000));

        // 按accountName查询
        // System.out.println(accountDao.queryBankAccountByAccountName("lisi"));

        // 查询所有
        for (BankAccount account : accountDao.queryAllBankAccount())
        {
            System.out.println(account);
        }

    }
}

评论

暂无评论