Spring整合JDBC

首先配置数据源：

jdbc.properties

1
2
3
4
5
6
7
8
driverClassName=com.mysql.jdbc.Driver
url=jdbc\:mysql\://localhost\:3306/test?useUnicode\=true&characterEncoding\=UTF-8
username=root
password=root
initialSize=1
maxActive=500
maxIdle=2
minIdle=1

beans.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
         <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}"/>
        <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>

 

配置事务管理器：

1
2
3
<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
       <property name="dataSource" ref="dataSource"/>
</bean>

采用@Transactional注解方式使用事务：

1
<tx:annotation-driven transaction-manager="txManager"/>

 

bean类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
package cn.lpq.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;
    }
}

接口PersonService.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
package cn.itcast.service;

import java.util.List;

import cn.lpq.bean.Person;

public interface PersonService {
    /**
     * 保存person
     * @param person
     */
    public void save(Person person);
    /**
     * 更新person
     * @param person
     */
    public void update(Person person);
    /**
     * 获取person
     * @param personid
     * @return
     */
    public Person getPerson(Integer personid);
    /**
     * 获取所有person
     * @return
     */
    public List<Person> getPersons();
    /**
     * 删除指定id的person
     * @param personid
     */
    public void delete(Integer personid);
}

接口实现类PersonServiceBean.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
package cn.lpq.service.impl;

import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.transaction.annotation.Transactional;

import cn.lpq.bean.Person;
import cn.lpq.service.PersonService;

@Transactional
public class PersonServiceBean implements PersonService {
    private JdbcTemplate jdbcTemplate;
   
    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    public void delete(Integer personid) {
        jdbcTemplate.update("delete from person where id=?", new Object[]{personid},
                new int[]{java.sql.Types.INTEGER});
    }

    public Person getPerson(Integer personid) {    
        return (Person)jdbcTemplate.queryForObject("select * from person where id=?", new Object[]{personid},
                new int[]{java.sql.Types.INTEGER}, new PersonRowMapper());
    }

    @SuppressWarnings("unchecked")
    public List<Person> getPersons() {
        return (List<Person>)jdbcTemplate.query("select * from person", new PersonRowMapper());
    }

    public void save(Person person) {
        jdbcTemplate.update("insert into person(name) values(?)", new Object[]{person.getName()},
                new int[]{java.sql.Types.VARCHAR});
    }

    public void update(Person person) {
        jdbcTemplate.update("update person set name=? where id=?", new Object[]{person.getName(), person.getId()},
                new int[]{java.sql.Types.VARCHAR, java.sql.Types.INTEGER});
    }
}

PersonRowMapper.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
package cn.lpq.service.impl;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

import cn.lpq.bean.Person;

public class PersonRowMapper implements RowMapper {

    public Object mapRow(ResultSet rs, int index) throws SQLException {
        Person person = new Person(rs.getString("name"));
        person.setId(rs.getInt("id"));
        return person;
    }
}

配置bean

1
2
3
<bean id="personService" class="cn.lpq.service.impl.PersonServiceBean">
        <property name="dataSource" ref="dataSource"/>
</bean>

Junit测试类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
package junit.test;


import org.junit.BeforeClass;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import cn.lpq.bean.Person;
import cn.lpq.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 (RuntimeException e) {
            e.printStackTrace();
        }
    }

    @Test public void save(){
        for(int i=0; i<5; i++)
            personService.save(new Person("传智播客"+ i));
    }
   
    @Test public void getPerson(){
        Person person = personService.getPerson(1);
        System.out.println(person.getName());
    }
   
    @Test public void update(){
        Person person = personService.getPerson(1);
        person.setName("张xx");
        personService.update(person);
    }
   
    @Test public void delete(){
        personService.delete(1);
    }
   
    @Test public void getBeans(){
        for(Person person : personService.getPersons()){
            System.out.println(person.getName());
        }
    }
}

 

除非注明，Coder文章均为原创，转载请以链接形式标明本文地址

本文地址：http://www.alonemonkey.com/spring-jdbc.html

本文链接：http://www.alonemonkey.com/spring-jdbc.html