你的位置:首页 > Java教程

[Java教程]使用Spring JdbcTemplate实现数据库操作


一:Spring JdbcTemplate是干啥的呢?

JdbcTemplate是core包的核心类。它替我们完成了资源的创建以及释放工作,从而简化了我们对JDBC的使用。它还可以帮助我们避免一些常见的错误,比如忘记关闭数据库连接。JdbcTemplate将完成JDBC核心处理流程,比如SQL语句的创建、执行,而把SQL语句的生成以及查询结果的提取工作留给我们的应用代码。它可以完成SQL查询、更新以及调用存储过程,可以对ResultSet进行遍历并加以提取。

二:首先我们先了解的知识

  1. Spring JDBC由4个部分组成,即core、datasource、object、support。

  (1)core包:提供JDBC模板类,其中JdbcTemplate是core包的核心类。

  (2)datesource包:提供简化访问JDBC数据源工具类,并提供一些DataSource简单实现类,从而使这些DataSource获取的连接能自动得到Spring事务管理的支持。

  (3)object包:提供关系数据的对象表示形式,如MappingSqlQuery、SqlUpdate、SqlCall、SqlFunction等。

  (4)support包:提供将JDBC异常转换为DAO非检查异常的转化类和一些工具类。

2. JdbcTemplate主要提供以下五类方法:

  • execute方法:可以用于执行任何SQL语句,一般用于执行DDL语句;

  • update方法及batchUpdate方法:update方法用于执行新增、修改、删除等语句;batchUpdate方法用于执行批处理相关语句;

  • query方法及queryForXXX方法:用于执行查询相关语句;

  • call方法:用于执行存储过程、函数相关语句。

     想要了解大家可以去查API,这里就不做多的解释了

三:几个使用经典案例

1.使用之前在保证Spring框架的jar包齐全的情况下引入

    spring-jdbc-4.2.0.RELEASE.jar

2.Book.java

package cn.book.beans;//Book实体类(Bean)public class Book {    private Integer bookId;    private String bookName;    private Integer bookPrice;  public Integer getBookId() {    return bookId;  }  public void setBookId(Integer bookId) {    this.bookId = bookId;  }  public String getBookName() {    return bookName;  }  public void setBookName(String bookName) {    this.bookName = bookName;  }  public Integer getBookPrice() {    return bookPrice;  }  public void setBookPrice(Integer bookPrice) {    this.bookPrice = bookPrice;  }  }

View Code

3.BookDao.java

package cn.book.dao;//dao接口import java.util.List;import cn.book.beans.Book;public interface BookDao {  /**   * 新增图书   * @param book 图书对象编号   * @return 受影响行数   */  public int addBook(Book book);  /**   * 删除图书信息   * @param book 要删除的图书对象编号   * @return 受影响行数   */  public int deleteBook(Book book);  /**   * 更改图书信息   * @param book 要更改的图书对象编号   * @return 受影响行数   */  public int updateBook(Book book);  /**   * 查询所有图书集合   * @return 图书集合   */  public List<Book> booklist();  /**   * 根据编号查询图书名   * @param book 图书对象编号   * @return 图书名   */  public String bookNameOfId(Book book);    /**   * 据编号查询图书对象   * @param book 图书对象编号   * @return 图书对象   */  public Book bookOfId(Book book);}

View Code

4.BookDaoImpl.java(继承了JdbcDaoSupport类--使用getJdbcTemplate()方法)

package cn.book.dao.impl;//dao接口实现类import java.util.List;import org.springframework.jdbc.core.BeanPropertyRowMapper;import org.springframework.jdbc.core.support.JdbcDaoSupport;import cn.book.beans.Book;import cn.book.dao.BookDao;import cn.book.util.MyRowMapper;//继承了JdbcDaoSupport类--使用getJdbcTemplate()方法public class BookDaoImpl extends JdbcDaoSupport implements BookDao {  public int addBook(Book book) {    // 增删改用update    String sql = "insert into book(bookname,bookPrice) values(?,?)";    int count = this.getJdbcTemplate().update(sql, book.getBookName(),        book.getBookPrice());    return count;  }  public List<Book> booklist() {    String sql = "select * from book";    List<Book> list = this.getJdbcTemplate().query(sql, new MyRowMapper());    return list;  }  public int updateBook(Book book) {    // 增删改用update    String sql = "update book set bookname=?,bookPrice=? where bookId=?";    int count = this.getJdbcTemplate().update(sql, book.getBookName(),        book.getBookPrice(), book.getBookId());    return count;  }  public int deleteBook(Book book) {    // 增删改用update    String sql = "delete from book where bookId=?";    int count = this.getJdbcTemplate().update(sql, book.getBookId());    return count;  }  public String bookNameOfId(Book book) {    String sql = "select * from book where bookId=?";    List<Book> list = this.getJdbcTemplate().query(sql, new MyRowMapper(),        book.getBookId());    return list.get(0).getBookName();  }  public Book bookOfId(Book book) {    String sql = "select * from book where bookId=?";    Book mybook = this.getJdbcTemplate().queryForObject(sql,        new BeanPropertyRowMapper<Book>(Book.class), book.getBookId());    return mybook;  }}

View Code

5.BookService.java

package cn.book.service;//service接口import java.util.List;import cn.book.beans.Book;public interface BookService {  /**   * 新增图书   * @param book 图书对象   * @return 受影响行数   */  public int addBook(Book book);  /**   * 删除图书信息   * @param book 要删除的图书对象   * @return 受影响行数   */  public int deleteBook(Book book);  /**   * 更改图书信息   * @param book 要更改的图书对象   * @return 受影响行数   */  public int updateBook(Book book);    /**   * 查询所有图书集合   * @return 图书集合   */  public List<Book> booklist();  /**   * 根据编号查询图书名   * @param book 图书集合   * @return 图书名   */  public String bookNameOfId(Book book);  /**   * 据编号查询图书对象   * @param book 图书对象编号   * @return 图书对象   */  public Book bookOfId(Book book);}

View Code

6.BookServiceImpl.java

package cn.book.service.impl;//service接口实现类import java.util.List;import cn.book.beans.Book;import cn.book.dao.BookDao;import cn.book.service.BookService;public class BookServiceImpl implements BookService{   //植入dao接口  private BookDao dao;    //调用dao中的方法  public int addBook(Book book) {    // TODO Auto-generated method stub    return dao.addBook(book);  }  public List<Book> booklist() {        return dao.booklist();  }  public int updateBook(Book book) {    return dao.updateBook(book);  }  public int deleteBook(Book book) {    return dao.deleteBook(book);  }  public String bookNameOfId(Book book) {    // TODO Auto-generated method stub    return dao.bookNameOfId(book);  }  public Book bookOfId(Book book) {    // TODO Auto-generated method stub    return dao.bookOfId(book);  }    //set和get方法  public BookDao getDao() {    return dao;  }  public void setDao(BookDao dao) {    this.dao = dao;  }}

View Code

7.MyRowMapper.java

package cn.book.util;///RowMapper可以将数据中的每一行封装成用户定义的类,//在数据库查询中,如果返回的类型是用户自定义的类型则需要包装,则可以包装成你想要的类import java.sql.ResultSet;import java.sql.SQLException;import org.springframework.jdbc.core.RowMapper;import cn.book.beans.Book;public class MyRowMapper implements RowMapper<Book> {  /**   * 封装成Book类并返回   * rs:代表单行数据   * rowNum:行号   */  public Book mapRow(ResultSet rs, int rowNum) throws SQLException {    Book book=new Book();    book.setBookId(rs.getInt("bookId"));    book.setBookName(rs.getString("bookName"));    book.setBookPrice(rs.getInt("bookPrice"));    return book;  }}

View Code

8.applicationContext.

<??><beans ="http://www.springframework.org/schema/beans"  ="http://www.w3.org/2001/ ="http://www.springframework.org/schema/aop"  ="http://www.springframework.org/schema/p" ="http://www.springframework.org/schema/context"  ="http://www.springframework.org/schema/tx"  xsi:schemaLocation="    http://www.springframework.org/schema/beans     http://www.springframework.org/schema/beans/spring-beans.xsd     http://www.springframework.org/schema/aop     http://www.springframework.org/schema/aop/spring-aop-4.1.xsd     http://www.springframework.org/schema/tx     http://www.springframework.org/schema/tx/spring-tx.xsd     http://www.springframework.org/schema/context     http://www.springframework.org/schema/context/spring-context.xsd    ">  <bean id="dao" class="cn.book.dao.impl.BookDaoImpl">    <property name="jdbcTemplate" ref="myJDBCTemplate"></property>  </bean>  <bean id="service" class="cn.book.service.impl.BookServiceImpl">    <property name="dao" ref="dao"></property>  </bean>  <!-- 配置连接池 Spring 内置的 -->   <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">     <property name="driverClassName" value="${jdbc.driver}"></property> <property     name="url" value="${jdbc.url}"></property> <property name="username" value="${jdbc.username}"></property>     <property name="password" value="${jdbc.password}"></property> </bean>  <!-- *****注册Jdbc属性文件***** -->  <!-- 方式一: PropertyPlaceholderConfigurer -->  <bean    class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">    <property name="location" value="classpath:jdbc.properties"></property>  </bean>  <!-- 方式二: -->  <!-- <context:property-placeholder location="classpath:jdbc.properties"     /> -->  <!-- ********注册jdbcTemplate********* -->  <bean id="myJDBCTemplate" class="org.springframework.jdbc.core.JdbcTemplate">    <property name="dataSource" ref="dataSource"></property>  </bean></beans> 

View Code

9.jdbc.properties(连接池的配置信息)

jdbc.driver=com.mysql.jdbc.Driverjdbc.url=jdbc\:mysql\://localhost\:3306/mybookjdbc.username=rootjdbc.password=1234

View Code

10.log4j.properties(日志配置文件)

### direct log messages to stdout ###log4j.appender.stdout=org.apache.log4j.ConsoleAppenderlog4j.appender.stdout.Target=System.outlog4j.appender.stdout.layout=org.apache.log4j.PatternLayoutlog4j.appender.stdout.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n### direct messages to file mylog.log ###log4j.appender.file=org.apache.log4j.FileAppenderlog4j.appender.file.File=c\:mylog.loglog4j.appender.file.layout=org.apache.log4j.PatternLayoutlog4j.appender.file.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n### set log levels - for more verbose logging change 'info' to 'debug' ###log4j.rootLogger=info, stdout

View Code

11.MyTest.java(测试类)

package cn.book.test;//测试类import java.util.List;import org.junit.Test;import org.springframework.context.ApplicationContext;import org.springframework.context.support.ClassPathimport cn.book.beans.Book;import cn.book.service.BookService;public class MyTest {  /*   * 新增   */  @Test  public void TestOne() {    ApplicationContext ctx = new ClassPath"applicationContext.);    BookService service = (BookService)ctx.getBean("service");    Book book=new Book();    book.setBookName("西游记");    book.setBookPrice(100);    int addBook = service.addBook(book);    if (addBook>0) {      System.out.println("新增成功!");    }      }  /*   * 删除   */  @Test  public void TestTwo() {    ApplicationContext ctx = new ClassPath"applicationContext.);    BookService service = (BookService)ctx.getBean("service");    Book book=new Book();    book.setBookId(3);    int count = service.deleteBook(book);    if (count>0) {      System.out.println("删除成功");    }      }  /*   * 修改   */  @Test  public void TestThree(){    ApplicationContext ctx = new ClassPath"applicationContext.);    BookService service = (BookService)ctx.getBean("service");    Book book=new Book();    book.setBookId(3);    book.setBookName("水浒传");    book.setBookPrice(300);    int count = service.updateBook(book);    if (count>0) {      System.out.println("更改成功");    }  }  /*   * 查询所有图书信息   */  @Test  public void TestFour() {    ApplicationContext ctx = new ClassPath"applicationContext.);    BookService service = (BookService)ctx.getBean("service");    List<Book> list = service.booklist();    for (int i = 0; i < list.size(); i++) {      System.out.println(list.get(i).getBookName());    }      }  /*   * 根据编号查询图书名   */  @Test  public void bookNameOfId() {    ApplicationContext ctx = new ClassPath"applicationContext.);    BookService service = (BookService)ctx.getBean("service");    Book book=new Book();    book.setBookId(2);    String nameOfId = service.bookNameOfId(book);    System.out.println(nameOfId);      }  /*   * 根据编号查询图书名   */  @Test  public void bookOfId() {    ApplicationContext ctx = new ClassPath"applicationContext.);    BookService service = (BookService)ctx.getBean("service");    Book book=new Book();    book.setBookId(2);    Book book2 = service.bookOfId(book);    System.out.println(book2.getClass());      }}

View Code