Spring4.x学习笔记——JdbcTemplate基础操作_综合

一、相关概念

spring是一个一站式的框架，对不同持久层的技术都进行了封装（处理数据库技术，基本的crud操作即增删改查），如JDBC、Hibermate、MaBatis、JPA等。

二、spring对JDBC的封装使用步骤大体如下：

导入jar包 spring-jdbc、spring-tx
创建DriveManagerDataSource对象，对数据库进行配置
- className
- url
- username
- password
创建JdbcTemplate对象
- 注入dataSource对象
调用JdbcTemplate封装的方法进行sql操作
- update(String sql , Object…arr) //增删改
- queryForObject(String sql , RowMapper rowMapper , Object…arr); //查询，单个值或对象
- query(String sql , RowMapper rowMapper , Object…arr); //查询，list
- 查询方法的形参需要一个实现RowMapper接口的类，对数据进行封装，详见以下实例

三、利用spring的jdbcTemplate实现简单的sql操作实例

1、创建工程，引入相关jar包

核心jar包

jdbcTemplate
这里写图片描述

2、创建spring配置文件并写必要约束

这里写图片描述

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xmlns:context="http://www.springframework.org/schema/context"xmlns:aop="http://www.springframework.org/schema/aop"xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsdhttp://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsdhttp://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd">
</beans>

3、建表、配置数据库及jdbcTemplate

创建一个简单的book表
创建DataSource.properties

#DataSource.properitiesdriverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/spring_jdbc_template_test?characterEncoding=UTF-8&useOldAliasMetadataBehavior=true
username=root
password=123456

spring配置：

<!-- 扫描包下的所有注解 --><context:component-scan base-package="com.jdbctemplate.book"></context:component-scan><context:property-placeholder location="resource/DataSource.properties"></context:property-placeholder><!-- 创建DataSource对象 --><bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"><property name="driverClassName" value="${jdbc.driverClassName}"></property><property name="url" value="${jdbc.url}"></property><property name="username" value="${jdbc.username}"></property><property name="password" value="${jdbc.password}"></property></bean><!-- 创建JdbcTemplate对象 --><bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"><constructor-arg name="dataSource" ref="dataSource"></constructor-arg></bean>

4、创建Book实例类，保存从数据库取出的数据

public class Book {
    private String name;private float prive;public Book() {super();}public Book(String name, float prive) {super();this.name = name;this.prive = prive;}public String getName() {return name;}public void setName(String name) {this.name = name;}public float getPrive() {return prive;}public void setPrive(float prive) {this.prive = prive;}@Overridepublic String toString() {return "Book [name=" + name + ", prive=" + prive +  "]";}
}

4、写dao层接口

public interface BookDao {
    /*** 添加图书* @param name* @param price* @return*/public int addBook(String name , float price);/*** 删除图书* @param name* @return*/public int delBook(String name);/*** 修改价格* @param name* @param price* @return*/public int updateBook(String name,float price);/*** 返回所有Book信息* @return*/public List<Book> getAllBook();/*** 返回指定书名的图书信息* @param name* @return*/public Book getBook(String name);/*** 返回指定书名的价格* @param name* @return*/public float getPrice(String name);
}

5、写BookDao的实现类，并配置

public class BookDaoImpl implements BookDao{// jdbc模板@Resource(name="jdbcTemplate")private  JdbcTemplate jdbcTemplate;public int addBook(String name, float price) {return jdbcTemplate.update("insert into book value(?,?)",name,price);}public int delBook(String name) {return jdbcTemplate.update("delete from book where name=?",name);}public int updateBook(String name, float price) {return jdbcTemplate.update("update book set price=? where name=?",price,name);}public List<Book> getAllBook() {return jdbcTemplate.query("select * from book", new BookRowMapper());}public Book getBook(String name) {return jdbcTemplate.queryForObject("select * from book where name=?", new BookRowMapper(),name);}public float getPrice(String name) {return jdbcTemplate.queryForObject("select price from book where name=?", Float.class);}private class BookRowMapper implements RowMapper<Book>{@Overridepublic Book mapRow(ResultSet re, int arg1) throws SQLException {return new Book(re.getString("name"),re.getShort("price"));}}
}

<bean id="bookDaoImpl" class="com.jdbctemplate.book.dao.impl.BookDaoImpl"></bean>

6、写BookService类

public class BookService {@Resource(name="bookDaoImpl")private BookDaoImpl bookDaoImpl;public List<Book> getAll() {return bookDaoImpl.getAllBook();}public Book get(String name) {return bookDaoImpl.getBook(name);}public float getPrice(String name) {return bookDaoImpl.getPrice(name);}public int add(String name,float price) {return bookDaoImpl.addBook(name, price);}public int delet(String name) {return bookDaoImpl.delBook(name);}
}

    <!-- 创建BookService对象 --><bean id="bookService" class="com.jdbctemplate.book.service.BookService"></bean>

7、写测试类

public class Test {public static void main(String[] args) {ApplicationContext context = new ClassPathXmlApplicationContext("resource/spring.xml");BookService bookService = (BookService) context.getBean("bookService");bookService.add("数学", 23.5F);bookService.add("语文", 20.5F);System.out.println(bookService.getAll());}
}

这里写图片描述

四、在过程中@Resource无法使用解决

在过程中注解@Resource无法使用，import javax.annotation.Resource;也一直红线无法引入，改变JDK版本后奇怪的就好了，但项目中没有错误，项目上总有个红叉。解决：右键项目–properties–project Facets–java对于的版本改统一即可