spring与数据库的连接配置
这里我们开始使用spring管理来配置数据的操作了
1、首先在xml文件中配置数据的连接
MySQL
<bean id="myDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<!-- results in a setDriverClassName(String) call -->
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mydb"/>
<property name="username" value="root"/>
<property name="password" value="masterkaoli"/>
</bean>
oracle
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
<property name="url" value="jdbc:oracle:thin:@rj-t42:1521:elvis"/>
<property name="username" value="scott"/>
<property name="password" value="tiger"/>
</bean>
2、我们先在数据库中建立一个表
create database cutter_point;
create table `cutter_point`.`person`
(
`id` int not null auto_increment,
`name` varchar(20) not null,
primary key(`id`)
)ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
3、相应的bean类
/**
* 功能:实现spring与jdbc的连接
* 时间:2015年3月26日21:09:20
* author:cutter_point
*/
package cn.cutter_point.bean;
public class Person
{
private Integer id;
private String name;
public Person() {} //默认构造函数
public Person(String name)
{
this.name = 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;
}
}
4、然后我们相对数据库操作的业务bean
1)接口类
/**
* 功能:实现spring与jdbc的连接
* 时间:2015年3月26日21:09:20
* author:cutter_point
*/
package cn.cutter_point.service;
import java.util.List;
import cn.cutter_point.bean.Person;
public interface PersonService
{
/**
* 保存person
* @param person
*/
public void save(Person person);
/**
* 更新person
* @param person
*/
public void update(Person person);
/**
* 根据id获取person
* @param personid
* @return
*/
public Person getPerson(Integer personid);
/**
* 获取所有的person
* @return
*/
public List<Person> getPersons();
/**
* 删除指定的person根据id号
* @param personid
*/
public void delete(Integer personid);
}
2)业务bean实现接口PersonService
/**
* 功能:实现spring与jdbc的连接
* 时间:2015年3月26日21:09:20
* author:cutter_point
*/
package cn.cutter_point.service.impl;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import cn.cutter_point.bean.Person;
import cn.cutter_point.service.PersonService;
public class PersonServiceBean implements PersonService
{
// private DataSource dataSource;
//这里我们使用spring里面的一个类容器
JdbcTemplate jdbcTemplate;
public PersonServiceBean() {}
/**
* 设置数据源
* @param dataSource
*/
public void setDataSource(DataSource dataSource)
{
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
@Override
public void save(Person person)
{
// TODO Auto-generated method stub
jdbcTemplate.update("insert into person(name) values (?)", new Object[]{person.getName()}, new int[]{java.sql.Types.VARCHAR});
}
@Override
public void update(Person person)
{
// TODO Auto-generated method stub
jdbcTemplate.update("update person set name=? where id = ?", new Object[]{person.getName(), person.getId()},
new int[]{java.sql.Types.VARCHAR, java.sql.Types.INTEGER});
}
@Override
public Person getPerson(Integer personid)
{
// TODO Auto-generated method stub
return (Person) jdbcTemplate.queryForObject("select * from person where id = ?", new Object[]{personid},
new int[]{java.sql.Types.INTEGER}, new PersonRowMapper());
}
@Override
public List<Person> getPersons()
{
return (List<Person>)jdbcTemplate.query("select * from person", new PersonRowMapper());
}
@Override
public void delete(Integer personid)
{
jdbcTemplate.update("delete from person where id = ?", new Object[]{personid}, new int[]{java.sql.Types.INTEGER});
}
}
3)实现查询数据的时候会有回调函数的使用就是把数据一个一个地取出来
/**
* 功能:实现spring与jdbc的连接
* 时间:2015年3月26日21:09:20
* author:cutter_point
*/
package cn.cutter_point.service.impl;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;
import cn.cutter_point.bean.Person;
public class PersonRowMapper implements RowMapper
{
@Override
//这个类在调用的时候外面已经做了
//类似if(rs.next)的操作了,所以这里就不用这么做了
public Object mapRow(ResultSet rs, int index) throws SQLException
{
//这里面我们把查询到的结果返回
Person person = new Person(rs.getString("name"));
person.setId(rs.getInt("id"));
return person;
}
}
5、使用映射文件配置xml
jdbc.properties
driverClassName=org.gjt.mm.mysql.Driver
url=jdbc\:mysql\://localhost\:3306/cutter_point?useUnicode\=true&characterEncoding\=UTF-8
username=root
password=xiaofeng2015
initialSize=1
maxActive=500
maxIdle=2
minIdle=1
最后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"
xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-2.5.xsd
http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.5.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.5.xsd">
<context:property-placeholder location="classpath:jdbc.properties"/>
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="${driverClassName}" />
<property name="url" value="${url}"/><!-- ?useUnicode=true&characterEncoding=UTF-8 -->
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
<!-- 连接池启动时的初始值 -->
<property name="initialSize" value="${initialSize}" />
<!-- 连接池的最大值 -->
<property name="maxActive" value="${maxActive}"/>
<!-- 最大空闲值.当经过一个高峰时间后,连接池可以慢慢将已经用不到的连接慢慢释放一部分,一直减少到maxIdle为止 -->
<property name="maxIdle" value="${maxIdle}"/>
<!-- 最小空闲值.当空闲的连接数少于阀值时,连接池就会预申请去一些连接,以免洪峰来时来不及申请 -->
<property name="minIdle" value="${minIdle}"/>
</bean>
<bean id="personService" class="cn.cutter_point.service.impl.PersonServiceBean" >
<property name="dataSource" ref="dataSource" />
</bean>
</beans>
6、建立单元测试
package junit.test;
import static org.junit.Assert.*;
import org.junit.BeforeClass;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import cn.cutter_point.bean.Person;
import cn.cutter_point.service.PersonService;
public class PersonServiceTest
{
private static PersonService personService;
@BeforeClass
public static void setUpBeforeClass() throws Exception
{
try
{
ApplicationContext cxt = new ClassPathXmlApplicationContext("beans.xml");
personService = (PersonService) cxt.getBean("personService");
}
catch (Exception e)
{
e.printStackTrace();
}
}
@Test
public void save()
{
for(int i = 0; i < 5; ++i)
{
personService.save(new Person("xiaofeng"+i));
}
}
@Test
public void get()
{
Person person = personService.getPerson(1);
System.out.println(person.getName());
}
@Test
public void update()
{
Person person = personService.getPerson(1);
person.setName("肖X");
personService.update(person);
}
@Test
public void delete()
{
personService.delete(1);
}
@Test
public void getPersons()
{
for(Person person : personService.getPersons())
{
System.out.println(person.getName());
}
}
@Test
public void test()
{
// fail("Not yet implemented");
}
}
结果
中途有点波折,注意xml文件里面的ref属性的值一定要用拷贝的方式,真是TMD别相信自己,我被这个玩意搞了一天,请原谅我这个英语渣渣!!!