实体类:
package cn.lfd.spring.jdbc;
/*
* 实体类,对象数据库中的departments表
*/
public class Department {
private Integer id;
private String name;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "Department [id=" + id + ", name=" + name + "]";
}
}
db.properties文件:
user=scott
password=system
driverClass=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@localhost:1521:orcl
initPoolSize=5
maxPoolSize=10
<?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:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd">
<!-- 读取db.properties文件 -->
<context:property-placeholder location="classpath:db.properties"/>
<!-- 配置c3p0数据源 -->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="user" value="${user}"></property>
<property name="password" value="${password}"></property>
<property name="driverClass" value="${driverClass}"></property>
<property name="jdbcUrl" value="${url}"></property>
<property name="initialPoolSize" value="${initPoolSize}"></property>
<property name="maxPoolSize" value="${maxPoolSize}"></property>
</bean>
<!-- 配置JdbcTemplate -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
</beans>
JDBCTest类:
package cn.lfd.spring.jdbc;
import java.util.ArrayList;
import java.util.List;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
public class JDBCTest {
private JdbcTemplate jdbcTemplate;
{
ApplicationContext ac = new ClassPathXmlApplicationContext("applicationContext.xml");
jdbcTemplate = (JdbcTemplate) ac.getBean("jdbcTemplate");//从ioc容器中取出JdbcTemplate bean
}
@Test
public void testConnection() {
System.out.println(jdbcTemplate.getDataSource());
}
//测试查询一条记录
@Test
public void testQueryForObject() {
String sql = "SELECT id,name FROM departments WHERE id = ?";
RowMapper<Department> rowMapper = new BeanPropertyRowMapper<Department>(Department.class);
Department department = jdbcTemplate.queryForObject(sql, rowMapper, 10);
System.out.println(department);
}
//测试更新操作
@Test
public void testUpdate() {
String sql = "UPDATE departments SET name = ? WHERE id = ?";
jdbcTemplate.update(sql, "lfd", 10);
}
//测试批量插入
@Test
public void testBatchUpdate() {
String sql = "INSERT INTO departments (id,name) VALUES(?,?)";
List<Object[]> list = new ArrayList<Object[]>();
list.add(new Object[]{50,"gzw"});
list.add(new Object[]{60,"aaa"});
list.add(new Object[]{70,"bbb"});
list.add(new Object[]{80,"ccc"});
jdbcTemplate.batchUpdate(sql,list);
}
//测试查询多条记录
@Test
public void QueryForList() {
String sql = "SELECT id,name FROM departments WHERE id > ?";
RowMapper<Department> rowMapper = new BeanPropertyRowMapper<Department>(Department.class);
List list = jdbcTemplate.query(sql, rowMapper, 10);
System.out.println(list);
}
}