前言
什么是Jdbc？
什么是JDBC?这篇文章告诉你在web开发中，不可避免的地要使用数据库来存储和管理数据。为了在java语言中提供数据库访问的支持，Sun公司于1996年提供了—套访问数据的标准Java类库，即JDBC。JDBC的全称是Java数据库连接(Java Databaseconnect)，它是—套用于执行SQL语句的Java APl。应用程序可通过这套API连接到关系数据库，并使用SQL语句来完成对数据库中数据增删改查
什么是JdbcTemplate?
Spring框架对JDBC进行封装，使用JdbcTemplate方便实现对数据库操作
准备工作
（1）引入相关jar包
（2）在spring配置文件配置数据库连接池
（3）注入JdbcTemplate对象，注入DataSource
代码示例：
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:context="http://www.springframework.org/schema/context" xmlns:aop="http://www.springframework.org/schema/aop" 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.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd"> <!--组件扫描--> <context:component-scan base-package="com.aop"></context:component-scan> <!--数据库连接池--> <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close"> <property name="url" value="jdbc:mysql:///user_db"/> <property name="username" value="root"/> <property name="password" value="2022"/> <property name="driverClassName" value="com.mysql.jdbc.Driver"/> </bean> <!--JdbcTemplate对象--> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <!--注入dataSource--> <property name="dataSource" ref="dataSource"></property> </bean> </beans>
（4）创建service类,在service类注入dao接口，创建dao类，在dao注入jdbcTemplate对象，及实体类
假设我们要操作这个t_book数据库：
代码示例（创建BookService 类，BookDao接口，BookDaoImpl类）：
BookService:
public class BookService { //注入dao @Autowired private BookDao bookDao; }
BookDao:
public interface BookDao {}
BookDaoImpl:
@Repository public class BookDaoImpl implements BookDao{ //注入jdbcTemplate @Autowired private JdbcTemplate jdbcTemplate; }
Book:
public class Book { private String userId; private String username; private String ustatus; public String getUserId() { return userId; } public String getUsername() { return username; } public String getUstatus() { return ustatus; } public void setUserId(String userId) { this.userId = userId; } public void setUsername(String username) { this.username = username; } public void setUstatus(String ustatus) { this.ustatus = ustatus; } @Override public String toString() { return "Book{" + "userId='" + userId + '\'' + ", username='" + username + '\'' + ", ustatus='" + ustatus + '\'' + '}'; } }
JdbcTemplate增加操作
单个增加
BookService:
public class BookService { //注入dao @Autowired private BookDao bookDao; //添加的方法 public void addBook(Book book){ bookDao.add(book); } }
BookDao:
public interface BookDao { //添加的方法 void add(Book book); }
BookDaoImpl:
@Repository public class BookDaoImpl implements BookDao{ //注入jdbcTemplate @Autowired private JdbcTemplate jdbcTemplate; //添加的方法 @Override public void add(Book book) { //1.创建sql语句 String sql = "insert into t_book values(?,?,?)"; //2.调用方法实现 Object[] args = {book.getUserId(),book.getUsername(),book.getUstatus()}; int update = jdbcTemplate.update(sql,args); System.out.println(update); } }
测试：
public class TestBook { @Test public void testJdbcTemplate(){ ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml"); BookService bookService = context.getBean("bookService", BookService.class); //添加 Book book = new Book(); book.setUserId("1"); book.setUsername("java"); book.setUstatus("a"); bookService.addBook(book); } }
运行结果：
批量增加
BookService:
public class BookService { //注入dao @Autowired private BookDao bookDao; //批量添加 public void batchAdd(List<Object[]> batchArgs){ bookDao.batchAddBook(batchArgs); } }
BookDao:
public interface BookDao { //批量添加 void batchAddBook(List<Object[]> batchArgs); }
BookDaoImpl:
@Repository public class BookDaoImpl implements BookDao{ //注入jdbcTemplate @Autowired private JdbcTemplate jdbcTemplate; //批量添加 @Override public void batchAddBook(List<Object[]> batchArgs) { String sql = "insert into t_book values(?,?,?)"; int[] ints = jdbcTemplate.batchUpdate(sql,batchArgs); System.out.println(Arrays.toString(ints)); } }
JdbcTemplate删除操作
单个删除
BookService:
public class BookService { //注入dao @Autowired private BookDao bookDao; //删除的方法 public void deleteBook(String id){ bookDao.delete(id); } }
BookDao:
public interface BookDao { //删除的方法 void delete(String id); }
BookDaoImpl:
@Repository public class BookDaoImpl implements BookDao{ //注入jdbcTemplate @Autowired private JdbcTemplate jdbcTemplate; //删除的方法 @Override public void delete(String id) { String sql = "delete from t_book where user_id=?"; int update = jdbcTemplate.update(sql,id); System.out.println(update); } }
批量删除
BookService:
public class BookService { //注入dao @Autowired private BookDao bookDao; //批量删除 public void batchDelete(List<Object[]> batchArgs){ bookDao.batchDeleteBook(batchArgs); } }
BookDao:
public interface BookDao { //批量删除 void batchDeleteBook(List<Object[]> batchArgs); }
BookDaoImpl:
@Repository public class BookDaoImpl implements BookDao{ //注入jdbcTemplate @Autowired private JdbcTemplate jdbcTemplate; //批量删除 @Override public void batchDeleteBook(List<Object[]> batchArgs) { String sql = "delete from t_book where user_id=?"; int[] ints = jdbcTemplate.batchUpdate(sql,batchArgs); System.out.println(Arrays.toString(ints)); } }
