【Spring学习】使用Spring的jdbcTemplate简化JDBC操作

applicationContext.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"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">

    <bean id="springDSN"
        class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName"
            value="com.microsoft.jdbc.sqlserver.SQLServerDriver">
        </property>
        <property name="url"
            value="jdbc:microsoft:sqlserver://localhost:1433;databasename=bbs">
        </property>
        <property name="username" value="sa"></property>
        <property name="password" value="sa"></property>
    </bean>

    <bean id="jdbcTemplate"
        class="org.springframework.jdbc.core.JdbcTemplate" abstract="false"
        lazy-init="false" autowire="default" dependency-check="default">
        <property name="dataSource">
            <ref bean="springDSN" />
        </property>
    </bean>
</beans>

SpringUtil类：

package com.r.dao;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;


public final class SpringUtil {

    //读取applicationContext.xml配置文件
    private static ApplicationContext  ctx = new ClassPathXmlApplicationContext("applicationContext.xml");

    public static Object getBean(String beanName){
         return ctx.getBean(beanName);
    }    
}

BookDao.java

package com.hollycrm.hollyuniproxy.opration.svcmonitor.mvc;

import java.awt.print.Book;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;

import com.hollycrm.hollybeacon.basic.util.SpringUtil;
import com.itextpdf.text.pdf.PdfStructTreeController.returnType;

@Controller
public class Test {

    private static final Log logger = LogFactory.getLog(Test.class);
    private JdbcTemplate jdbcTemplate = (JdbcTemplate) SpringUtil.getBean("jdbcTemplate");

    public List findAll(){
        String sql = "select * from BookInfo ";
        return jdbcTemplate.queryForList(sql);
    }

    public List<Book> findBookByName(String bookName){
        List bookList = new ArrayList();//存放要返回的book集合
        String sql = "select * from BookInfo where bookName = ? ";
        List params = new ArrayList<>();
        params.add(bookName);//sql语句参数
        List list = jdbcTemplate.queryForList(sql, params.toArray());
        Iterator iterator = list.iterator();
        Book book = null;
        //遍历查回的book集合
        for (iterator.hasNext()) {
            Map map4book = (Map)iterator.next();

            book = null;
            book.setBid((Integer)map4book.get("bid"));
            book.setBookName(bookName);
            book.setBookType((Integer)map4book.get("bookType"));
            book.setBookAuthor((Integer)map4book.get("bookAuthor"));
            book.setBookCount((Integer)map4book.get("bookCount"));

            bookList.add(book);
        }
        return bookList;
    }

    public int delete(String bid){
        String sql = "delete * from BookInfo where bid = ? ";
        return jdbcTemplate.update(sql, new Object[]{bid});
    }

    public static void main(String[] args) {        
        List<Book> books = BookDao().findBookbyName(String bookName);
        for(Book book:books){
            System.out.println(book.getBid()+","+book.getBookName()+","+book.getBookType());
        }
    }

}

JdbcTemplate的实例中有一系列的方法如：queryForXXX,update,delete大大简化了JDBC操作。

—————————————————————————--

可以进一步的优化一下,就是通过依赖注入，直接把jdbcTemplate注入到dao类的jdbcTemplate字段。

applicationContext.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"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">

    <bean id="springDSN"
        class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName"
            value="com.microsoft.jdbc.sqlserver.SQLServerDriver">
        </property>
        <property name="url"
            value="jdbc:microsoft:sqlserver://localhost:1433;databasename=bbs">
        </property>
        <property name="username" value="sa"></property>
        <property name="password" value="sa"></property>
    </bean>

    <bean id="jdbcTemplate"
        class="org.springframework.jdbc.core.JdbcTemplate" abstract="false"
        lazy-init="false" autowire="default" dependency-check="default">
        <property name="dataSource">
            <ref bean="springDSN" />
        </property>
    </bean>

    <bean id="bookDao" class="com.yy.struts.dao.BookDao">
       <property name="jdbcT">
          <ref bean="jdbcTemplate" />
       </property>
    </bean>
</beans>

BookDao.java

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.JdbcTemplate;

import com.r.vo.Book;

public class BookDao {

    private JdbcTemplate jdbcT;

    public List findALL() {
        String sql = "select * from BookInfo";
        return jdbcT.queryForList(sql);        
    }

    public List<Book> findALLBooks() {
        List<Book> books = new ArrayList<Book>();;
        String sql = "select * from BookInfo";
        List list = jdbcT.queryForList(sql); 
        Iterator iterator = list.iterator();
        Book book = null;
        while (iterator.hasNext()) {
            Map map4book = (Map) iterator.next();
            book = new Book();
            book.setBid((Integer) map4book.get("bid"));
            book.setBookName((String)map4book.get("bookName"));
            book.setBookType((String)map4book.get("bookType"));        
            book.setBookPic(((BigDecimal)map4book.get("bookPic")).doubleValue() );            
            book.setCount((Integer) map4book.get("count"));
            books.add(book);
        }
        return books;
    }    
    public int delete(int bid){
        String sql = "delete from BookInfo where bid =?";
        return jdbcT.update(sql, new Object[]{bid});
    }     
    public static void main(String[] args) {        
        List<Book> books = new BookDao().findALLBooks();;
        for(Book book:books){
            System.out.println(book.getBid()+","+book.getBookName()+","+book.getBookType());
        }
    }
}

通过依赖注入，对象之间的关系通过spring来维护，这样可以减少类与类之间的耦合度。

——————————————————————————

当然,还可以再进一步的优化一下,就是通过自动注入，直接把jdbcTemplate自动注入到dao类的jdbcTemplate字段。

applicationContext.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"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">

    <!--跟依赖注入相比只多加了这一句-->
    <context:component-scan base-package="********.BookDao" />

    <bean id="springDSN"
        class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName"
            value="com.microsoft.jdbc.sqlserver.SQLServerDriver">
        </property>
        <property name="url"
            value="jdbc:microsoft:sqlserver://localhost:1433;databasename=bbs">
        </property>
        <property name="username" value="sa"></property>
        <property name="password" value="sa"></property>
    </bean>

    <bean id="jdbcTemplate"
        class="org.springframework.jdbc.core.JdbcTemplate" abstract="false"
        lazy-init="false" autowire="default" dependency-check="default">
        <property name="dataSource">
            <ref bean="springDSN" />
        </property>
    </bean>

    <bean id="bookDao" class="com.yy.struts.dao.BookDao">
       <property name="jdbcT">
          <ref bean="jdbcTemplate" />
       </property>
    </bean>
</beans>

BookDao.java

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.JdbcTemplate;

import com.r.vo.Book;

public class BookDao {

    @Autowired
    protected JdbcTemplate jdbcTemplate;

    public List findALL() {
        String sql = "select * from BookInfo";
        return jdbcT.queryForList(sql);        
    }

    public List<Book> findALLBooks() {
        List<Book> books = new ArrayList<Book>();;
        String sql = "select * from BookInfo";
        List list = jdbcT.queryForList(sql); 
        Iterator iterator = list.iterator();
        Book book = null;
        while (iterator.hasNext()) {
            Map map4book = (Map) iterator.next();
            book = new Book();
            book.setBid((Integer) map4book.get("bid"));
            book.setBookName((String)map4book.get("bookName"));
            book.setBookType((String)map4book.get("bookType"));        
            book.setBookPic(((BigDecimal)map4book.get("bookPic")).doubleValue() );            
            book.setCount((Integer) map4book.get("count"));
            books.add(book);
        }
        return books;
    }    
    public int delete(int bid){
        String sql = "delete from BookInfo where bid =?";
        return jdbcT.update(sql, new Object[]{bid});
    }     
    public static void main(String[] args) {        
        List<Book> books = new BookDao().findALLBooks();;
        for(Book book:books){
            System.out.println(book.getBid()+","+book.getBookName()+","+book.getBookType());
        }
    }
}