今天开始学习我自己总结的 Java-学习路线 中的《Spring-JDBCTemplate》,小简从 0 开始学 Java 知识,并不定期更新所学笔记,期待一年后的蜕变吧!
Spring 框架对 JDBC 进行封装,使用 JdbcTemplate 方便实现对数据库操作
1、引入相关jar包
druid-1.2.9.jar
mysql-connector-java-8.0.28.jar
spring-jdbc-5.3.18.jar
spring-orm-5.3.18.jar
spring-tx-5.3.18.jar
2、在spring配置文件配置数据库连接池
<!--引入外部属性文件,需要配置context名称空间-->
<context:property-placeholder location="classpath:druid.properties"/>
<!--配置连接池-->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
<property name="driverClassName" value="${jdbc.driverClass}"></property>
<property name="url" value="${jdbc.url}"></property>
<property name="username" value="${jdbc.username}"></property>
<property name="password" value="${jdbc.password}"></property>
</bean>
druid.properties
jdbc.driverClass=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/jwt
jdbc.username=root
jdbc.password=root
3、配置JdbcTemplate对象,注入DataSource
<!-- JdbcTemplate对象 -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<!--注入dataSource-->
<property name="dataSource" ref="dataSource"/>
</bean>
4、创建service类,创建dao类
<!-- 组件扫描 -->
<context:component-scan base-package="com.jwt"></context:component-scan>
@Service
public class BookService {
//注入dao
@Autowired
private BookDao bookDao;
}
public interface BookDao {
}
@Repository
public class BookDaoImpl implements BookDao {
//注入JdbcTemplate
@Autowired
private JdbcTemplate jdbcTemplate;
}
1、先创建一个表
CREATE TABLE `book`(
id INT,
name VARCHAR (255),
status VARCHAR (255)
)CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB ;
2、创建表对应实体类
public class Book {
private String bookId;
private String bookName;
private String bookStatus;
public String getBookId() {
return bookId;
}
public void setBookId(String bookId) {
this.bookId = bookId;
}
public String getBookName() {
return bookName;
}
public void setBookName(String bookName) {
this.bookName = bookName;
}
public String getBookStatus() {
return bookStatus;
}
public void setBookStatus(String bookStatus) {
this.bookStatus = bookStatus;
}
}
3、编写service和dao
public interface BookDao {
void add(Book book);//添加的方法
}
@Repository
public class BookDaoImpl implements BookDao {
//注入JdbcTemplate
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public void add(Book book) {
// 创建sql语句
String sql = "INSERT INTO `book` VALUES (?, ?, ?)";
// 调用方法实现
int update = jdbcTemplate.update(sql, book.getBookId(), book.getBookName(), book.getBookStatus());
System.out.println(update);
}
}
@Service
public class BookService {
//注入dao
@Autowired
private BookDao bookDao;
public void add(Book book) {
bookDao.add(book);
}
}
4、测试
public class testJDBC {
@Test
public void testadd() {
ApplicationContext context =
new ClassPathXmlApplicationContext("jdbc.xml");
BookService bookService = context.getBean("bookService", BookService.class);
Book book = new Book();
book.setBookId("1");
book.setBookName("java");
book.setBookStatus("a");
bookService.add(book);
}
}
运行后表中就会成功增加一条数据
详细请参考添加操作
@Override
public void update(Book book) {
String sql = "update book set name=?,status=? where id=?";
Object[] args = {book.getBookName(), book.getBookStatus(),book.getBookId()};
int update = jdbcTemplate.update(sql, args);
System.out.println(update);
}
测试
@Test
public void testupdate() {
ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("jdbc.xml");
BookService bookService = context.getBean("bookService", BookService.class);
Book book = new Book();
book.setBookName("C++");
book.setBookStatus("b");
book.setBookId("1");
bookService.update(book);
}
@Override
public void delete(String id) {
String sql = "delete from book where id=?";
int update = jdbcTemplate.update(sql,id);
System.out.println(update);
}
测试
@Test
public void testdelete() {
ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("jdbc.xml");
BookService bookService = context.getBean("bookService", BookService.class);
bookService.delete("1");
}
@Override
public int select() {
String sql = "select count(*) from book";
Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
return count;
}
测试
@Test
public void testselect() {
ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("jdbc.xml");
BookService bookService = context.getBean("bookService", BookService.class);
int select = bookService.select();
System.out.println("select = " + select);
}
//查询返回对象
@Override
public Book selectObj(String id) {
String sql = "select * from book where `id`=?";
Book book = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Book>(Book.class), id);
return book;
}
测试
@Test
public void testselectObj() {
ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("jdbc.xml");
BookService bookService = context.getBean("bookService", BookService.class);
Book book = bookService.selectObj("1");
System.out.println("book = " + book);
}
数据库中表的字段名要和实体类Book中的字段名字一样,不然会返回null
@Override
public List<Book> selectCollection() {
String sql = "select * from book";
//调用方法
List<Book> bookList = jdbcTemplate.query(sql,new BeanPropertyRowMapper<Book>(Book.class));
return bookList;
}
测试
@Test
public void testselectCollection() {
ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("jdbc.xml");
BookService bookService = context.getBean("bookService", BookService.class);
List<Book> books = bookService.selectCollection();
System.out.println("books = " + books);
}
@Override
public void batchAdd(List<Object[]> batchArgs) {
String sql = "insert into book values(?,?,?)";
int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
System.out.println(Arrays.toString(ints));
}
测试
@Test
public void testbatchAdd() {
ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("jdbc.xml");
BookService bookService = context.getBean("bookService", BookService.class);
List<Object[]> batchArgs = new ArrayList<>();
Object[] o1 = {"3","java","a"};
Object[] o2 = {"4","c++","b"};
Object[] o3 = {"5","MySQL","c"};
batchArgs.add(o1);
batchArgs.add(o2);
batchArgs.add(o3);
//调用批量添加
bookService.batchAdd(batchArgs);
}
@Override
public void batchUpdate(List<Object[]> batchArgs) {
String sql = "update book set name=?,status=? where id=?";
int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
System.out.println(Arrays.toString(ints));
}
测试
@Test
public void testbatchUpdate() {
ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("jdbc.xml");
BookService bookService = context.getBean("bookService", BookService.class);
List<Object[]> batchArgs = new ArrayList<>();
Object[] o1 = {"java2","a3","3"};
Object[] o2 = {"c++2","b4","4"};
Object[] o3 = {"MySQL2","c5","5"};
batchArgs.add(o1);
batchArgs.add(o2);
batchArgs.add(o3);
bookService.batchUpdate(batchArgs);
}
@Override
public void batchDelete(List<Object[]> batchArgs) {
String sql = "delete from book where id=?";
int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
System.out.println(Arrays.toString(ints));
}
测试
@Test
public void testbatchDelete() {
ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("jdbc.xml");
BookService bookService = context.getBean("bookService", BookService.class);
List<Object[]> batchArgs = new ArrayList<>();
Object[] o1 = {"3"};
Object[] o2 = {"4"};
batchArgs.add(o1);
batchArgs.add(o2);
bookService.batchDelete(batchArgs);
}