SpringJDBC下操作数据源dataSource

1）SpringJDBC下操作数据源： 

为了学习TransactionManager，我们先学习一下SpringJDBC下操作数据源：org.apache.commons.dbcp.BasicDataSource。做以下实验时，要先导几个包。commons-collections-3.2.1.jar，commons-dbcp-1.4.jar，commons-pool-1.5.4.jar，mysql-connector-java-3.1.10-bin.jar，spring-jdbc-3.0.5.RELEASE.jar，spring-orm-3.0.5.RELEASE.jar，spring-tx-3.0.5.RELEASE.jar，

例 2.2.1

先在web.xml中加入下面的语句：
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" >
         <property name="driverClassName"
                      value="com.mysql.jdbc.Driver"></property>
         <property name="url" 
                      value="jdbc:mysql://localhost:3306/test"></property>
         <property name="username"
                      value="root"></property>
         <property name="password" 
                      value="1234"></property>
    </bean>
    <bean id="jt" class="org.springframework.jdbc.core.JdbcTemplate">
        <constructor-arg ref="dataSource" />
    </bean>



package service;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.annotation.Resource;
import org.springframework.jdbc.core.JdbcTemplate;
import com.NiutDAO;
import service.interfac.ILoginService;
import org.springframework.jdbc.core.RowCallbackHandler;

public class LoginServiceImpl implements ILoginService {
    @Resource
    private JdbcTemplate jt;
    

    public void login() {
        System.out.println("LoginServiceImpl");
        String sql = "SELECT COUNT(id) FROM register";
        Object[] params = new Object[] {};
        jt.query(sql, params, new RowCallbackHandler() {
            public void processRow(ResultSet rs) throws SQLException {
                System.out.println(rs.getInt(1)) ;
            }
        });
    }    
}


运行结果：

LoginServiceImpl
10
after loginServic.login()


后记：processRow只能处理一行，想处理多行参考下段。
        final List objList = new ArrayList();  
        jdbcTemplate.query(listSql, new RowCallbackHandler() {  
            @Override  
            public void processRow(ResultSet rs) throws SQLException {  
                Map row = new HashMap();  
                row.put(rs.getInt("id"), rs.getString("name"));  
                objList.add(row);  
            }  
        }); 

例 2.2.1_1:（simpleJdbcTemplate的项目【比JdbcTemplate更好使用】）：

TeacherPupil.java:

package com;

public class TeacherPupil  {
    public String toString() {  
        return "pname:"+pname+"tid:"+tid+"\nname:"+name+"\nage:"+age;  
    }  
    private String pname;
    public String getPname() {
        return pname;
    }
    public void setPname(String pname) {
        this.pname = pname;
    }
    private Integer tid;
    private String name;
    private Integer age;

    public Integer getTid() {
        return tid;
    }
    public void setTid(Integer tid) {
        this.tid = tid;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name == null ? null : name.trim();
    }
    public Integer getAge() {
        return age;
    }
    public void setAge(Integer age) {
        this.age = age;
    }
}

LoginServiceImpl.java:

package service;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import service.interfac.ILoginService;

import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;

import com.Register;
import com.TeacherPupil;




public class LoginServiceImpl implements ILoginService {
    @Resource
    private JdbcTemplate jt1;
    public void setJt1(JdbcTemplate jt) {
        this.jt1 = jt;
    }
    
    @Resource
    private SimpleJdbcTemplate simpleJdbcTemplate;

    public void setSimpleJdbcTemplate(SimpleJdbcTemplate simpleJdbcTemplate) {
        this.simpleJdbcTemplate = simpleJdbcTemplate;
    }
    
    public void login() {
        System.out.println("LoginServiceImpl1111");
        String sql = "SELECT COUNT(id) FROM register";
        Object[] params = new Object[] {};
        jt1.query(sql, params, new RowCallbackHandler() {
            public void processRow(ResultSet rs) throws SQLException {
                System.out.println(rs.getInt(1)) ;
            }
        });
        int res=this.simpleJdbcTemplate.queryForInt(sql);
        System.out.println("res is "+res) ;
//增        
        Register reg = new Register();
        reg.setId(61);
        reg.setName("abc");
        reg.setAge(28);
        
        String sqlInsert = "insert into register(id,name,age) values(:id,:name,:age)";
        simpleJdbcTemplate.update(sqlInsert.toString(), new BeanPropertySqlParameterSource(reg));
        System.out.println("insert ok ") ;
        

///改
        StringBuffer sqlgai = new StringBuffer();
        sqlgai.append("update register set name=? where id=?");
        simpleJdbcTemplate.update(sqlgai.toString(),"abc1", 61);
        System.out.println("改 ok ") ;
///改2
        StringBuffer sqlgai1 = new StringBuffer();
        sqlgai1.append("update register set name=? where id=?");
        simpleJdbcTemplate.update(sqlgai1.toString(),new Object[] { "abc2", 61 });
        System.out.println("改 ok1 ") ;        
///批量改
        StringBuffer sqlgaibatch = new StringBuffer();
        sqlgaibatch.append("update register set name=? where id=?");
        
        List<Object[]> parameters = new ArrayList<Object[]>();


        parameters.add(new Object[] { "qwe1",1 });
        parameters.add(new Object[] { "ert2",2 });
        simpleJdbcTemplate.batchUpdate(sqlgaibatch.toString(), parameters);
        System.out.println("批量改 ok ") ;
        
/查询
        String sqldan = "select *  from register where id=?";
        Register regi=simpleJdbcTemplate.queryForObject(sqldan,new BeanPropertyRowMapper(Register.class),1);
        System.out.println("一个 regi "+ regi.getName()) ;
        
        
///查询多个        
        String sqlselec = "select * from register";
        List<Register> regis = simpleJdbcTemplate.query(sqlselec, new BeanPropertyRowMapper(Register.class));
        for(Object obj :regis){
            System.out.println(obj);
        }
        System.out.println("成功查询多个");
/
/*一对多，专门做一个类TeacherPupil，接收.用底下的as 别名方法，就可以解决teacher和pupil表中都有同名的字段name的方法。这样就可以从不同的表中同时取值了。*/
        String sqlotm = "select pupil.name as pname, teacher.name,pupil.tid from pupil,teacher where tid=teacher.id and teacher.name='qixy';";
        List<TeacherPupil> teaPup = simpleJdbcTemplate.query(sqlotm, new BeanPropertyRowMapper(TeacherPupil.class));
        for(Object obj :teaPup){
            System.out.println(obj);
        }    
        System.out.println("成功一对多");
///查询多个带条件        
        String sqlselecc = "select * from register where id<:id";
        List<Register> regisc = simpleJdbcTemplate.query(sqlselecc, new BeanPropertyRowMapper(Register.class), new BeanPropertySqlParameterSource(reg));
        for(Object obj :regisc){
            System.out.println(obj);
        }    
//删
        String sqldel = " delete from register where id=?";
        simpleJdbcTemplate.update(sqldel, 61);

    }  
    
    public static void main(String[] args)
    {
        ClassPathXmlApplicationContext cp=  new ClassPathXmlApplicationContext("applicationContext.xml");
        ILoginService ls=(ILoginService)cp.getBean("loginService");
        ls.login();
    }  
}

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="loginService" class="service.LoginServiceImpl" >
        <property name="simpleJdbcTemplate">
            <ref bean="simpleJdbcTemplate" />
        </property>
    </bean>
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" >
         <property name="driverClassName"
                      value="com.mysql.jdbc.Driver"></property>
         <property name="url" 
                      value="jdbc:mysql://localhost:3306/test"></property>
         <property name="username"
                      value="root"></property>
         <property name="password" 
                      value="1234"></property>
 

更多请见下节：http://www.mark-to-win.com/tutorial/frame_Spring_ExampleBasicDataSource.html