spring主要负责的是逻辑层Service,所以根据三层架构来说,spring主要与dao层进行交互,所以让spring与jdbc相结合获取Connection的对象,再进行对数据库的操作。
一定记得导入jar包
在Oracle数据库中进行下面的操作
建表
create table t_account(
id number primary key,
name varchar2(20) not null,
balance number
);
插入一行数据
insert into t_account(id,name,balance) values(1,'tom1',2000);
提交
commit;
导入jar包,并添加到环境变量中
oracle.properties文件中
driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@127.0.0.1:1521:XE
user=cao
password=cao
在jdbc.xml文件中加载:
<context:property-placeholder location=“classpath:oracle.properties” />
public interface AccountDao {
//更新余额
void update(int accountId,double balance);
}
import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
public class JdbcDao implements AccountDao {
// 数据源
private DataSource dataSorce;
public DataSource getDataSorce() {
return dataSorce;
}
public void setDataSorce(DataSource dataSorce) {
this.dataSorce = dataSorce;
}
@Override
public void update(int id, double balance) {
try {
Connection conn = dataSorce.getConnection();
System.out.println("conn = " + conn);
if (conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
方式一:基于jdk的规范数据源
<?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.2.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.2.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-3.2.xsd">
<!-- 读取这个资源文件oracle.properties, 读完之后下面就可以用${key}来去文件中的value值了 -->
<!-- 这种方式是我们第一节学习的那种配置方式方式的简写 -->
<context:property-placeholder location="classpath:oracle.properties" />
<!-- 基于jdk的规范数据源 -->
<bean name="dataSource1" class="oracle.jdbc.pool.OracleConnectionPoolDataSource">
<property name="networkProtocol">
<value>tcp</value>
</property>
<property name="databaseName">
<value>XE</value>
</property>
<property name="driverType">
<value>thin</value>
</property>
<property name="portNumber">
<value>1521</value>
</property>
<property name="user">
<value>${user}</value>
</property>
<property name="serverName">
<value>127.0.0.1</value>
</property>
<property name="password">
<value>${password}</value>
</property>
</bean>
<bean name="dao" class="com.briup.db.jdbc.JdbcDao">
<property name="dataSorce" ref="dataSource1"></property>
</bean>
</beans>
结果:
方式二:dbcp数据源
<?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.2.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.2.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-3.2.xsd">
<!-- 读取这个资源文件oracle.properties, 读完之后下面就可以用${key}来去文件中的value值了 -->
<!-- 这种方式是我们第一节学习的那种配置方式方式的简写 -->
<context:property-placeholder location="classpath:oracle.properties" />
<!-- dbcp数据源 -->
<bean id="dataSource2" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName">
<value>${driver}</value>
</property>
<property name="url">
<value>${url}</value>
</property>
<property name="username">
<value>${user}</value>
</property>
<property name="password">
<value>${password}</value>
</property>
<!-- 最大连接数 -->
<property name="maxActive">
<value>80</value>
</property>
<!-- 最大空闲连接数 -->
<property name="maxIdle">
<value>20</value>
</property>
<!-- 最大等待时间:当没有可用连接时,连接池等待连接被归还的最大时间 单位:毫秒 -->
<!-- 超过时间则抛出异常,如果设置为-1表示无限等待 -->
<property name="maxWait">
<value>3000</value>
</property>
</bean>
<bean name="dao" class="com.briup.db.jdbc.JdbcDao">
<property name="dataSorce" ref="dataSource2"></property>
</bean>
</beans>
结果:
方式三:spring提供的一种数据源
<?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.2.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.2.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-3.2.xsd">
<!-- 读取这个资源文件oracle.properties, 读完之后下面就可以用${key}来去文件中的value值了 -->
<!-- 这种方式是我们第一节学习的那种配置方式方式的简写 -->
<context:property-placeholder location="classpath:oracle.properties" />
<!-- spring提供的一种数据源 -->
<bean id="dataSource3" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName">
<value>${driver}</value>
</property>
<property name="url">
<value>${url}</value>
</property>
<property name="username">
<value>${user}</value>
</property>
<property name="password">
<value>${password}</value>
</property>
</bean>
<bean name="dao" class="com.briup.db.jdbc.JdbcDao">
<property name="dataSorce" ref="dataSource3"></property>
</bean>
</beans>
结果:
方式四:c3p0数据源
<?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.2.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.2.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-3.2.xsd">
<!-- 读取这个资源文件oracle.properties, 读完之后下面就可以用${key}来去文件中的value值了 -->
<!-- 这种方式是我们第一节学习的那种配置方式方式的简写 -->
<context:property-placeholder location="classpath:oracle.properties" />
<!-- c3p0数据源 -->
<bean id="dataSource4" class="com.mchange.v2.c3p0.ComboPooledDataSource"
destroy-method="close">
<property name="driverClass">
<value>${driver}</value>
</property>
<property name="jdbcUrl">
<value>${url}</value>
</property>
<property name="user">
<value>${user}</value>
</property>
<property name="password">
<value>${password}</value>
</property>
<!--连接池中保留的最小连接数。 -->
<property name="minPoolSize">
<value>5</value>
</property>
<!--连接池中保留的最大连接数。Default: 15 -->
<property name="maxPoolSize">
<value>30</value>
</property>
<!--初始化时获取的连接数,取值应在minPoolSize与maxPoolSize之间。Default: 3 -->
<property name="initialPoolSize">
<value>10</value>
</property>
<!--最大空闲时间,60秒内未使用则连接被丢弃。若为0则永不丢弃。Default: 0 -->
<property name="maxIdleTime">
<value>60</value>
</property>
<!--当连接池中的连接耗尽的时候c3p0一次同时获取的连接数。Default: 3 -->
<property name="acquireIncrement">
<value>5</value>
</property>
<!--每60秒检查所有连接池中的空闲连接。Default: 0 -->
<property name="idleConnectionTestPeriod">
<value>60</value>
</property>
<!--定义在从数据库获取新连接失败后重复尝试的次数。Default: 30 -->
<property name="acquireRetryAttempts">
<value>30</value>
</property>
</bean>
<bean name="dao" class="com.briup.db.jdbc.JdbcDao">
<property name="dataSorce" ref="dataSource4"></property>
</bean>
</beans>
结果:
上面的测试类都一样:
//知识点: spring中配置jdbc的数据源
@Test
public void jdbc_dataSource(){
try {
String path = "com/briup/db/jdbc/jdbc.xml";
ClassPathXmlApplicationContext container = new ClassPathXmlApplicationContext(path);
AccountDao dao = (AccountDao) container.getBean("dao");
dao.update(1, 1000);
container.destroy();
}catch(Exception e) {
e.printStackTrace();
}
}
结果:
jdk提供结果:
oracle.jdbc.driver.T4CConnection@66d18979
apache提供结果:(dbcp)
jdbc:oracle:thin:@127.0.0.1:1521:XE, UserName=CAO, Oracle JDBC driver
spring提供结果:
oracle.jdbc.driver.T4CConnection@4e91d63f
c3p0提供结果:
com.mchange.v2.c3p0.impl.NewProxyConnection@5b0abc94 [wrapping: oracle.jdbc.driver.T4CConnection@75c072cb]