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&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
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
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&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);
}
}
}
