6、spring数据库操作

浏览数：27 / 时间：2015年06月12日

1.目录结构:

2.数据表结构:

3.代码如下:

Person.java



package db.entity;

public 
class Person {

    private String username;

    
private String password;

  
  public Person(){}

  
  

    public 
Person(String username) {

        this.username 
= username;

    }

  
  

    public 
String getUsername() {

        return username;

    
}

    public void 
setUsername(String username) {

        this.username 
= username;

    }

  
  public String getPassword() 
{

        return 
password;

    }

    public void 
setPassword(String password) {

        this.password 
= password;

    
}

}

PersonService.java



package db.service;

import java.util.List;

import 
db.entity.Person;

public interface 
PersonService {

    public 
void save(Person person);

  
  public void update(Person person);

    
public Person getPerson(String 
username);

    public 
List<Person> 
getPersons();

    public 
void delete(String 
uersname);

}

PersonServiceBean.java



package db.service.impl;

import java.util.List;

import 
javax.sql.DataSource;

import 
org.springframework.jdbc.core.JdbcTemplate;

import 
org.springframework.transaction.annotation.Transactional;

import 
db.entity.Person;

import 
db.service.PersonService;

@Transactional

public class 
PersonServiceBean implements PersonService 
{

    private JdbcTemplate 
jdbcTemplate;

    

    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 s1 (username, password) values(?, ?)", new Object[] {person.getUsername(), 
person.getPassword()},

              
  new int[] {java.sql.Types.VARCHAR, 
java.sql.Types.VARCHAR});

    }



    
@Override

    public void update(Person person) {

    
    jdbcTemplate.update("update s1 set 
password=? where username=?", new 
Object[]{person.getPassword(), person.getUsername()},

    
            new int[]{java.sql.Types.VARCHAR, 
java.sql.Types.VARCHAR});

    }



    
@SuppressWarnings("unchecked")

    
@Override

    public Person 
getPerson(String username) {

        return 
(Person) jdbcTemplate.queryForObject("select * from s1 
where username=?", new 
Object[]{username},

              
  new int[] {java.sql.Types.VARCHAR}, new 
PersonRowManager());

    }



    
@SuppressWarnings("unchecked")

    
@Override

    public 
List<Person> 
getPersons() {

        // TODO 
Auto-generated method stub

        return (List<Person>) 
jdbcTemplate.query("select * from s1", new 
PersonRowManager());

    }



    
@Override

    public void delete(String uersname) {

  
      jdbcTemplate.update("delete from s1 
where username=?", new 
Object[]{uersname},

              
  new int[]{java.sql.Types.VARCHAR});

  
  }



}

PersonRowManager.java



package db.service.impl;

import java.sql.ResultSet;

import 
java.sql.SQLException;

import 
org.springframework.jdbc.core.RowMapper;

import db.entity.Person;



public class 
PersonRowManager implements 
RowMapper{



    @Override

    public Object mapRow(ResultSet rs, int 
index) throws SQLException {

  
      // TODO Auto-generated method 
stub

        Person person = 
new Person(rs.getString("username"));

  
      person.setPassword(rs.getString("password"));

        return person;

    
}

    

}



PersonServiceBeanTest.java
package db.junit;



import 
static org.junit.Assert.*;



import org.junit.After;

import 
org.junit.Before;

import 
org.junit.Test;

import 
org.springframework.context.ApplicationContext;

import 
org.springframework.context.support.ClassPathXmlApplicationContext;



import db.entity.Person;

import 
db.service.PersonService;



public class 
PersonServiceBeanTest {

    

    private PersonService 
personService;



    @Before

    public void 
setUp() throws Exception {

  
      try {

  
          ApplicationContext cxt = new 
ClassPathXmlApplicationContext("beans.xml");

          
  personService = (PersonService) 
cxt.getBean("personService");

    
    } catch (RuntimeException e) 
{

            
e.printStackTrace();

        }

    
}



    @After

    public void 
tearDown() throws Exception {

  
  }



    @Test

    public 
void testSetDataSource() {

  
      fail("Not yet 
implemented");

    }



    
@Test

    public void testSave() {

      
  Person person = new Person("wxt");

        
person.setPassword("3546832");

    
    System.out.println(person.getUsername() + "   " + 
person.getPassword());

        
personService.save(person);

    }



    
@Test

    public void testUpdate() {

      
  Person person = new Person("zmp");

        
person.setPassword("changed");

    
    personService.update(person);

    
}



    @Test

    public void 
testGetPerson() {

        Person person = 
personService.getPerson("zmp");

  
      System.out.println(person.getUsername());

  
  }



    @Test

    public 
void testGetPersons() {

    
    for(Person person : 
personService.getPersons()){

            
System.out.println(person.getUsername());

        
}

    }

    @Test

    public void 
testDelete() {

        personService.delete("zmpandzmp");

    
}

}

4.配置如下:

beans.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-3.0.xsd

  
         http://www.springframework.org/schema/context 
http://www.springframework.org/schema/context/spring-context-3.0.xsd

  
         http://www.springframework.org/schema/aop 
http://www.springframework.org/schema/aop/spring-aop-3.0.xsd

  
         http://www.springframework.org/schema/tx 
http://www.springframework.org/schema/tx/spring-tx-3.0.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}"/>

        
<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="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">

  
       <property name="dataSource" 
ref="dataSource"/>

    
</bean>

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

  
  

    <bean id="personService" 
class="db.service.impl.PersonServiceBean">

  
      <property name="dataSource" ref="dataSource"/>

    
</bean>

</beans>

jdbc.properties



driverClassName=org.gjt.mm.mysql.Driver

url=jdbc\:mysql\://localhost\:3306/spring?useUnicode\=true&characterEncoding\=UTF-8

username=root

password=123456

initialSize=1

maxActive=500

maxIdle=2

minIdle=1