spring操作数据库(JDBC)

spring操作数据库(JDBC)概述

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

配置文件beans.xml配置步骤

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

参考代码如下：

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

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

参考代码如下：

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

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

1<!-- 配置Dao层 -->
2<bean id="bankAccountDao" class="www.enfp.lx_03_jdbc.lx_02_crud.BankAccountDaoImpl">
3	<property name="jdbcTemplate" ref="jdbcTemplate"></property>
4</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

参考代码如下：

1//增
2{
3	String sql = "insert into bankaccount(accountname,balance) value(?,?)";
4	Object[] args = { account.getAccountName(), account.getBalance() };
5	this.getJdbcTemplate().update(sql, args);
6}
7
8//删
9{
10	String sql = "delete from bankaccount where accountname=?";
11	Object[] args = { accountName };
12	this.getJdbcTemplate().update(sql, args);
13
14}
15
16//改
17{
18	String sql = "update bankaccount set balance=? where accountname=?";
19	Object[] args = { account.getBalance(), account.getAccountName() };
20	this.getJdbcTemplate().update(sql, args);
21}

2.查

(1).查询多条记录

public <T> List<T> query(String sql, RowMapper<T> 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)

参考代码如下：

1String sql = "select accountname,balance from bankaccount";
2RowMapper rowMapper = new RowMapper()
3{
4
5	@Override
6	public Object mapRow(ResultSet rs, int rowNum) throws SQLException
7	{
8		BankAccount account = new BankAccount();
9		account.setAccountName(rs.getString("accountname"));
10		account.setBalance(rs.getDouble("balance"));
11		return account;
12	}
13
14};
15List<BankAccount> bankAccountList = this.getJdbcTemplate().query(sql,rowMapper);

(2).查询单条记录

public <T> T queryForObject(String sql, Object[] args, RowMapper<T> 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

参考代码如下：

1String sql = "select accountname,balance from bankaccount where accountname=?";
2Object[] args = { accountName };
3RowMapper rowMapper = new RowMapper()
4{
5
6	@Override
7	public Object mapRow(ResultSet rs, int rowNum) throws SQLException
8	{
9		BankAccount account = new BankAccount();
10		account.setAccountName(rs.getString("accountname"));
11		account.setBalance(rs.getDouble("balance"));
12		return account;
13	}
14
15};
16BankAccount account = this.getJdbcTemplate().queryForObject(sql, rowMapper, args);

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

1.beans.xml配置文件

参考代码如下：beans.xml

1<?xml version="1.0" encoding="UTF-8"?>
2<beans xmlns="http://www.springframework.org/schema/beans"
3	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
4	xmlns:aop="http://www.springframework.org/schema/aop"
5	xsi:schemaLocation="http://www.springframework.org/schema/beans
6           http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
7           http://www.springframework.org/schema/context
8           http://www.springframework.org/schema/context/spring-context-3.0.xsd
9           http://www.springframework.org/schema/aop
10           http://www.springframework.org/schema/aop/spring-aop-3.0.xsd">
11
12	<!-- 连接池基本配置信息 -->
13	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
14		<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
15		<property name="url" value="jdbc:mysql://localhost:3306/springdb?useUnicode=true&amp;characterEncoding=utf8"></property>
16		<property name="username" value="root"></property>
17		<property name="password" value="liuxin950326"></property>
18		
19		<!-- 连接池启动时的初始值 -->
20		<property name="initialSize" value="10"></property>
21		<!-- 连接池的最大值 -->
22		<property name="maxActive" value="80"></property>
23		<!-- 最大空闲值.经过一个高峰时间，连接池可将已用不到连接慢慢释放一部分，一直减少到maxIdle为止 -->
24		<property name="maxIdle" value="5"></property>
25		<property name="minIdle" value="2"></property>
26	</bean>
27	
28	
29	<!-- 配置JDBC模板 -->
30	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
31		<property name="dataSource" ref="dataSource"></property>
32	</bean>
33	
34	
35	<!-- 配置Dao层 -->
36	<bean id="bankAccountDao" class="www.enfp.lx_03_jdbc.lx_02_crud.BankAccountDaoImpl">
37		<property name="jdbcTemplate" ref="jdbcTemplate"></property>
38	</bean>
39	
40	
41</beans>      

2.pojo(银行账户)

参考代码如下：BankAccount.java

1package www.enfp.lx_03_jdbc.lx_02_crud;
2
3public class BankAccount
4{
5	private String accountName = null;
6	private double balance = 0;
7
8	@Override
9	public String toString()
10	{
11		return "accountName:" + this.accountName + "\t balance:" + this.balance;
12	}
13
14	public String getAccountName()
15	{
16		return accountName;
17	}
18
19	public void setAccountName(String accountName)
20	{
21		this.accountName = accountName;
22	}
23
24	public double getBalance()
25	{
26		return balance;
27	}
28
29	public void setBalance(double balance)
30	{
31		this.balance = balance;
32	}
33
34	public BankAccount(String accountName, double balance)
35	{
36		super();
37		this.accountName = accountName;
38		this.balance = balance;
39	}
40
41	public BankAccount()
42	{
43		super();
44	}
45
46}

3.Dao类

参考代码如下:

IBankAccountDao.java接口

1package www.enfp.lx_03_jdbc.lx_02_crud;
2
3import java.util.List;
4
5public interface IBankAccountDao
6{
7	public void addBankAccount(BankAccount account);// 增
8
9	public void deleteBankAccount(String accountName);// 删
10
11	public void updateBankAccount(BankAccount account);// 改
12
13	// 查
14	public List<BankAccount> queryAllBankAccount();
15
16	public BankAccount queryBankAccountByAccountName(String accountName);
17
18}

BankAccountDaoImpl.java类

1package www.enfp.lx_03_jdbc.lx_02_crud;
2
3import java.sql.ResultSet;
4import java.sql.SQLException;
5import java.util.List;
6
7import org.springframework.jdbc.core.JdbcTemplate;
8import org.springframework.jdbc.core.RowMapper;
9
10public class BankAccountDaoImpl implements IBankAccountDao
11{
12	private JdbcTemplate jdbcTemplate = null;
13
14	public JdbcTemplate getJdbcTemplate()
15	{
16		return jdbcTemplate;
17	}
18
19	public void setJdbcTemplate(JdbcTemplate jdbcTemplate)
20	{
21		this.jdbcTemplate = jdbcTemplate;
22	}
23
24	@Override
25	public void addBankAccount(BankAccount account)
26	{
27		String sql = "insert into bankaccount(accountname,balance) value(?,?)";
28		Object[] args = { account.getAccountName(), account.getBalance() };
29		this.getJdbcTemplate().update(sql, args);
30	}
31
32	@Override
33	public void deleteBankAccount(String accountName)
34	{
35		String sql = "delete from bankaccount where accountname=?";
36		Object[] args = { accountName };
37		this.getJdbcTemplate().update(sql, args);
38
39	}
40
41	@Override
42	public void updateBankAccount(BankAccount account)
43	{
44		String sql = "update bankaccount set balance=? where accountname=?";
45		Object[] args = { account.getBalance(), account.getAccountName() };
46		this.getJdbcTemplate().update(sql, args);
47	}
48
49	@Override
50	public List<BankAccount> queryAllBankAccount()
51	{
52		String sql = "select accountname,balance from bankaccount";
53		RowMapper rowMapper = new RowMapper()
54		{
55
56			@Override
57			public Object mapRow(ResultSet rs, int rowNum) throws SQLException
58			{
59				BankAccount account = new BankAccount();
60				account.setAccountName(rs.getString("accountname"));
61				account.setBalance(rs.getDouble("balance"));
62				return account;
63			}
64
65		};
66		List<BankAccount> bankAccountList = this.getJdbcTemplate().query(sql,
67				rowMapper);
68		return bankAccountList;
69	}
70
71	@Override
72	public BankAccount queryBankAccountByAccountName(String accountName)
73	{
74		String sql = "select accountname,balance from bankaccount where accountname=?";
75		Object[] args = { accountName };
76		RowMapper rowMapper = new RowMapper()
77		{
78
79			@Override
80			public Object mapRow(ResultSet rs, int rowNum) throws SQLException
81			{
82				BankAccount account = new BankAccount();
83				account.setAccountName(rs.getString("accountname"));
84				account.setBalance(rs.getDouble("balance"));
85				return account;
86			}
87
88		};
89		BankAccount account = this.getJdbcTemplate().queryForObject(sql,
90				rowMapper, args);
91		return account;
92	}
93
94}

4.测试

参考代码如下：test.java

1package www.enfp.lx_03_jdbc.lx_02_crud;
2
3import org.springframework.context.ApplicationContext;
4import org.springframework.context.support.ClassPathXmlApplicationContext;
5
6public class Test
7{
8
9	public static void main(String[] args)
10	{
11		ApplicationContext container = new ClassPathXmlApplicationContext(
12				"www/enfp/lx_03_jdbc/lx_02_crud/beans.xml");
13
14		IBankAccountDao accountDao = (IBankAccountDao) container
15				.getBean("bankAccountDao");
16		// 添加
17		// accountDao.addBankAccount(new BankAccount("zhangsan", 1000));
18		// accountDao.addBankAccount(new BankAccount("lisi", 1000));
19		// accountDao.addBankAccount(new BankAccount("wangxiaojian", 1000));
20
21		// 删除
22		// accountDao.deleteBankAccount("zhangsan");
23
24		// 修改
25		// accountDao.updateBankAccount(new BankAccount("lisi", 2000));
26
27		// 按accountName查询
28		// System.out.println(accountDao.queryBankAccountByAccountName("lisi"));
29
30		// 查询所有
31		for (BankAccount account : accountDao.queryAllBankAccount())
32		{
33			System.out.println(account);
34		}
35
36	}
37}