浅析Spring的JdbcTemplate方法

浅析Spring的JdbcTemplate方法

spring对于数据访问层提供了多种的模板技术。如果直接使用JDBC,那么可以选择JdbcTemplate、如果使用的是对象关系映射框架,使用hibernate应该使用HibernateTemplate模板,使用JPA则应该使用JpaTemplate。

除此之外,Spring框架为每一项的持久层技术都提供了相应的帮助类来简化操作。对于Jdbc提供了JdbcDaoSupport类、对于Hibernate技术提供了HibernateDaoSupport类、对于MyBatis提供了SqlMapClientDaoSupport类。

本篇主要介绍Spring如何使用JdbcTemplate来访问关系型数据库。

1.首先引入使用Spring的jdbc模块时的jar文件(maven项目可引入对应的依赖)。

spring-beans-3.2.0.RELEASE.jar

spring-context-3.2.0.RELEASE.jar

spring-core-3.2.0.RELEASE.jar

spring-expression-3.2.0.RELEASE.jar

commons-logging-1.2.jar

spring-jdbc-3.2.0.RELEASE.jar

spring-tx-3.2.0.RELEASE.jar

对应的数据库驱动(这里采用mysql)

2.在src下引入两个文件:applicationContext.xml和log4j.xml

3.下面以连接两种数据库连接池的技术来介绍Spring关于JdbcTemplate的使用:

使用Spring内置的数据库连接池:

DriverManagerDataSource dataSource=new DriverManagerDataSource();

dataSource.setDriverClassName("com.mysql.jdbc.Driver");

dataSource.setUrl("jdbc:mysql:///springjdbc");

dataSource.setUsername("root");

dataSource.setPassword("1997WFY.....");

JdbcTemplate template=new JdbcTemplate();

template.setDataSource(dataSource);

template.execute("create table book(id int primary key auto_increment,name varchar(20) not null,author varchar(25))");

或者:

java代码使用:

/**

* @author BeautifulSoup

* 首先使用Spring内置的连接池

*/

@ContextConfiguration("classpath:applicationContext.xml")

@RunWith(SpringJUnit4ClassRunner.class)

public class SpringJdbcTest {

@Autowired

private JdbcTemplate template;

@Test

public void testDriverManagerDataSource() {

template.execute("create table book(id int primary key auto_increment,name varchar(20) not null,author varchar(25))");

}

}

使用世界上性能最好的Druid连接池:

<property name="testOnReturn" value="false" />

value="20" />

/**

* @author BeautifulSoup

* 首先使用Spring内置的连接池

*/

@ContextConfiguration("classpath:applicationContext.xml")

@RunWith(SpringJUnit4ClassRunner.class)

public class SpringJdbcTest {

@Autowired

private JdbcTemplate template;

@Test

public void testSpringJdbc() {

template.execute("create table book(id int primary key auto_increment,name varchar(20) not null,author varchar(25))");

}

}

4.使用得到的JdbcTemplate进行基本的增删改查:

首先创建实体类对象,

/**

* @author BeautifulSoup

* 创建实体类对象

*/

public class Book {

private Integer id;

private String name;

private String author;

public Integer getId() {

return id;

}

public void setId(Integer id) {

this.id = id;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public String getAuthor() {

return author;

}

public void setAuthor(String author) {

this.author = author;

}

@Override

public String toString() {

return "Book [id=" + id + ", name=" + name + ", author=" + author + "]";

}

}

在配置文件中配置bean:

Dao层进行持久层的开发:

/**

* @author BeautifulSoup 完成基本的增删改查

*/

public class BookDao extends JdbcDaoSupport {

public void add(Book book) {

String sql = "insert into book values(?,?,?)";

getJdbcTemplate().update(sql, book.getId(), book.getName(),

book.getAuthor());

}

public void update(Book book) {

String sql = "update book set name = ? , author = ? where id =?";

getJdbcTemplate().update(sql, book.getName(), book.getAuthor(),

book.getId());

}

public void delete(Book book) {

String sql = "delete from book where id =?";

getJdbcTemplate().update(sql, book.getId());

}

public int findCount() {

String sql = "select count(*) from book";

return getJdbcTemplate().queryForInt(sql);

}

public String findNameById(int id) {

String sql = "select name from book where id = ?";

return getJdbcTemplate().queryForObject(sql, String.class, id);

}

public Book findById(int id) {

String sql = "select * from book where id = ?";

return getJdbcTemplate().queryForObject(sql, new BookMapper(), id);

}

public List findAll(){

String sql="select * from book";

return getJdbcTemplate().query(sql, new BookMapper());

}

class BookMapper implements RowMapper {

public Book mapRow(ResultSet rs, int rowNum) throws SQLException {

Book book = new Book();

book.setId(rs.getInt("id"));

book.setName(rs.getString("name"));

book.setAuthor(rs.getString("author"));

return book;

}

}

}

单元测试,

/**

* @author BeautifulSoup

* 首先使用Spring内置的连接池

*/

@RunWith(SpringJUnit4ClassRunner.class)

@ContextConfiguration("classpath:applicationContext.xml")

public class SpringJdbcTest {

@Autowired

private BookDao bookDao;

@Test

public void jdbcTemplateAdd(){

Book book=new Book();

book.setId(1);

book.setName("SpringBoot实战");

book.setAuthor("Craig Walls");

bookDao.add(book);

}

}

value="20" />

/**

* @author BeautifulSoup

* 首先使用Spring内置的连接池

*/

@ContextConfiguration("classpath:applicationContext.xml")

@RunWith(SpringJUnit4ClassRunner.class)

public class SpringJdbcTest {

@Autowired

private JdbcTemplate template;

@Test

public void testSpringJdbc() {

template.execute("create table book(id int primary key auto_increment,name varchar(20) not null,author varchar(25))");

}

}

4.使用得到的JdbcTemplate进行基本的增删改查:

首先创建实体类对象,

/**

* @author BeautifulSoup

* 创建实体类对象

*/

public class Book {

private Integer id;

private String name;

private String author;

public Integer getId() {

return id;

}

public void setId(Integer id) {

this.id = id;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public String getAuthor() {

return author;

}

public void setAuthor(String author) {

this.author = author;

}

@Override

public String toString() {

return "Book [id=" + id + ", name=" + name + ", author=" + author + "]";

}

}

在配置文件中配置bean:

Dao层进行持久层的开发:

/**

* @author BeautifulSoup 完成基本的增删改查

*/

public class BookDao extends JdbcDaoSupport {

public void add(Book book) {

String sql = "insert into book values(?,?,?)";

getJdbcTemplate().update(sql, book.getId(), book.getName(),

book.getAuthor());

}

public void update(Book book) {

String sql = "update book set name = ? , author = ? where id =?";

getJdbcTemplate().update(sql, book.getName(), book.getAuthor(),

book.getId());

}

public void delete(Book book) {

String sql = "delete from book where id =?";

getJdbcTemplate().update(sql, book.getId());

}

public int findCount() {

String sql = "select count(*) from book";

return getJdbcTemplate().queryForInt(sql);

}

public String findNameById(int id) {

String sql = "select name from book where id = ?";

return getJdbcTemplate().queryForObject(sql, String.class, id);

}

public Book findById(int id) {

String sql = "select * from book where id = ?";

return getJdbcTemplate().queryForObject(sql, new BookMapper(), id);

}

public List findAll(){

String sql="select * from book";

return getJdbcTemplate().query(sql, new BookMapper());

}

class BookMapper implements RowMapper {

public Book mapRow(ResultSet rs, int rowNum) throws SQLException {

Book book = new Book();

book.setId(rs.getInt("id"));

book.setName(rs.getString("name"));

book.setAuthor(rs.getString("author"));

return book;

}

}

}

单元测试,

/**

* @author BeautifulSoup

* 首先使用Spring内置的连接池

*/

@RunWith(SpringJUnit4ClassRunner.class)

@ContextConfiguration("classpath:applicationContext.xml")

public class SpringJdbcTest {

@Autowired

private BookDao bookDao;

@Test

public void jdbcTemplateAdd(){

Book book=new Book();

book.setId(1);

book.setName("SpringBoot实战");

book.setAuthor("Craig Walls");

bookDao.add(book);

}

}

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。