Spring 基于JDBC的数据源

 

     Spring里能够配置的最简单数据源是通过JDBC驱动定义的。

 

    一、Sprging默认的jar包

          1.commons-dbcp.jar

          2.commons-io-2.0.1.jar

          3.commons-logging.jar

          4.commons-pool.jar

          5.spring.jar

          6.spring-aop.jar

          7.spring-beans.jar        

          8.spring-Context.jar

          9.spring-Context-support.jar

         10.spring-core.jar

         11.spring-jdbc.jar

         12.spring-tx.jar

     二、配置applicationContext.xml

 

         

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">

<beans>
 <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
   <property name="driverClassName">
     <value>com.microsoft.sqlserver.jdbc.SQLServerDriver</value>
   </property>
   <property name="url">
     <value>jdbc:sqlserver://localhost:1433;DatabaseName=master</value>
   </property>
   <property name="username">
     <value>Ton</value>
   </property>
   <property name="password">
     <value>123456</value>
   </property>
 </bean>
 <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
   <property name="dataSource">
     <ref bean="dataSource"/>
   </property>
 </bean>

<bean id="personDAO" class="SJDBCsupport.wData.PersonDAO">
  <property name="jdbcTemplate">
    <ref bean="jdbcTemplate"/>
  </property>
</bean>
</beans>

      三、建立JavaPackage：SJDBCsupport.wData

            1.  Person.java

    =========================================================

             

import java.util.List;

public class Person {
 
   private String name;
   private String password;
   private String address;
   public Person(){}
   public Person(String name,String password,String address){
       this.name=name;
       this.password=password;
       this.address=address;
   }

 public String getName() {
  return name;
 }
 public void setName(String name) {
  this.name = name;
 }
 public String getPassword() {
  return password;
 }
 public void setPassword(String password) {
  this.password = password;
 }
 public String getAddress() {
  return address;
 }
 public void setAddress(String address) {
  this.address = address;
 }

 

}

 

     =============================================================

       2.PersonDAO.java

   ==============================================================

     

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;

import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

public class PersonDAO {
 
 private Person person;
 
 private JdbcTemplate jdbcTemplate;
 
 public int deleteper(){
  String sql="delete from login ";
  
  return jdbcTemplate.update(sql);
 }
 

 public Person getPerson(String pname){
  String sql="select * from login where uname=?";
    
  Person person=(Person)jdbcTemplate.queryForObject(sql, new Object[]{pname},new PersonRowMap());
  
  return person;
  
 }
 
 public List<Person> getPersons(){
  
  String sql="select * from login ";
  List persons=jdbcTemplate.query(sql, new PersonRowMap());
  return persons;
  
 }
 
 
 public JdbcTemplate getJdbcTemplate() {
  return jdbcTemplate;
 }

 public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
  this.jdbcTemplate = jdbcTemplate;
 }

 public int insertPersonUseUpdate(Person person){
     String sql="insert into login values(?,?,?)";
     Object[] params=new Object[]{
             person.getName(),
             person.getPassword(),
             person.getAddress()
     };
     return this.getJdbcTemplate().update(sql,params);
 }
 public int insertPersonUseExecute(Person person){
     String sql="insert into login values(?,?,?)";
     Object[] params=new Object[]{
             person.getName(),
             person.getPassword(),
             person.getAddress()
     };
     int[] types=new int[]{
             Types.VARCHAR,
             Types.VARCHAR,
             Types.VARCHAR
     };
     return this.getJdbcTemplate().update(sql,params,types);
 }
 public int[] updatePersonUseBatchUpdate( final List persons){
     String sql="insert into login values(?,?,?)";
     BatchPreparedStatementSetter setter=null;
     setter=new BatchPreparedStatementSetter(){
         public int getBatchSize(){
             return persons.size();
         }
         public void setValues(PreparedStatement ps,int index) throws SQLException{
             Person person=(Person)persons.get(index);
             ps.setString(1,person.getName());
             ps.setString(2,person.getPassword());
             ps.setString(3,person.getAddress());
         }
     };
     return this.getJdbcTemplate().batchUpdate(sql,setter);
 }

 

}

 

   ==============================================================

       3.TestJDBCTemplate.java

    ==============================================================

import java.io.File;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.BeanFactory;
import org.springframework.beans.factory.xml.XmlBeanFactory;
import org.springframework.core.io.FileSystemResource;

public class TestJDBCTemplate {

 /**
  * @param args
  */
 public static String filePath="";
    public static BeanFactory factory=null;
    public static void main(String[] args) {
     /**第一种方式；
       // filePath=System.getProperty("user.dir")+File.separator+"SpringJDBCSupport"+File.separator+"WriteData"+File.separator+"applicationContext-core.xml";
     filePath=System.getProperty("user.dir")+File.separator+"applicationContext-core.xml";
     
     factory=new XmlBeanFactory(new FileSystemResource(filePath));
     **/
     
     /**第二种方式**/
     factory=new XmlBeanFactory(new FileSystemResource("applicationContext-core.xml"));
     
     
        PersonDAO personDAO=(PersonDAO)factory.getBean("personDAO");
       
        /**//*
         * 准备数据
         */
        Person p1=new Person("test1","111","test1");
        Person p2=new Person("test2","222","test2");
        Person p3=new Person("test3","333","test3");
        Person p4=new Person("test4","444","test4");
        Person p5=new Person("test5","555","test5");
        List persons=new ArrayList();
        persons.add(p3);
        persons.add(p4);
        persons.add(p5);
       
        personDAO.deleteper();
       
        //使用jdbcTemplate.update方式
       
       
       
        personDAO.insertPersonUseUpdate(p1);
    
        //使用jdbcTemplate.execute方式
        personDAO.insertPersonUseExecute(p2);
      
        //使用jdbcTemplate批处理方式
        personDAO.updatePersonUseBatchUpdate(persons);
       
       
        for(Person ps:personDAO.getPersons())
         System.out.println(ps.getName()+"  "+ps.getPassword()+"  "+ps.getAddress());
        Person pp=(Person)personDAO.getPerson("test5");
       
        System.out.println("dddd"+pp.getAddress()+"     "+pp.getPassword());
       
       
       
        System.out.println("Success");
              
       
       
      
       
    }
 

}

 

    ==============================================================

         4.PersonRowMap.java

   ==============================================================

       

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

import org.springframework.jdbc.core.RowMapper;

public class PersonRowMap implements RowMapper {

 @Override
 public Object mapRow(ResultSet rs, int index) throws SQLException {
  // TODO Auto-generated method stub
     Person person=new Person();
     person.setName(rs.getString("uname"));
     person.setAddress(rs.getString("uaddr"));
     person.setPassword(rs.getString("upwd"));
    
  return person;
 }

}

 

   ==============================================================

       四、查看本机数据库master

        table：login表中的数据变化.

 

      五、查询数据：

      1.查询单笔数据时候：

     public Person getPerson(String pname){
           String sql="select * from login where uname=?";
    
           Person person=(Person)jdbcTemplate.queryForObject(sql, new Object[]{pname},new PersonRowMap());
  
          return person;
  
         }

     ......

      测试：

              Person pp=(Person)personDAO.getPerson("test5");
       
                System.out.println("dddd"+pp.getAddress()+"     "+pp.getPassword());

 

      2.查询批量数据时：

              ................

            public List<Person> getPersons(){
  
               String sql="select * from login ";
               List persons=jdbcTemplate.query(sql, new PersonRowMap());
                return persons;
  
              }

.............

测试：

  for(Person ps:personDAO.getPersons())
  System.out.println(ps.getName()+"  "+ps.getPassword()+"  "+ps.getAddress());