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

矫情吗；* 2022-08-19 12:28 165阅读 0赞

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配置文件：

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());
            }
        }
    }