Spring数据库连接

用户8447427

发布于 2022-08-18 16:26:45

9070

发布于 2022-08-18 16:26:45

文章被收录于专栏：userlyz学习记录userlyz学习记录

数据库连接操作

创建好项目之后进行bean测试之后

再database_context.xml添加数据库依赖

<dependencies>
    <!-- https://mvnrepository.com/artifact/commons-dbcp/commons-dbcp -->
    <dependency>
        <groupId>commons-dbcp</groupId>
        <artifactId>commons-dbcp</artifactId>
        <version>1.4</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.25</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>${spring.version}</version>
    </dependency>

</dependencies>

以下是代码部分

**源文件链接：传送门提取码：qwer **

SaleService

package com.spring.www.Business;

import com.spring.www.DAO.BookDAO;
import com.spring.www.Model.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class SaleService {
    BookDAO bookDAO;

    @Autowired
    @Qualifier(value = "bookDaoImpl")
    public void setBookDAO(BookDAO bookDAO) {
        this.bookDAO = bookDAO;
    }

    public void insert(Book book){
        bookDAO.insertBook(book);
    }

    public void onSale(float sale){
        List<Book> bookList=bookDAO.queryAll();
        for (Book b :bookList){
            b.setPrice(b.getPrice()*sale);
            bookDAO.modifyBook(b);
        }
    }
}

AbstractBookDAO

package com.spring.www.DAO;

import com.spring.www.Model.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.annotation.Resource;
import java.util.List;

public abstract class AbstractBookDAO implements BookDAO{
    List<Book> books;
    JdbcTemplate jdbcTemplate;

    @Resource(name="list")
    public void setBooks(List<Book> books) {
        this.books = books;
    }

    @Autowired
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public void insertBook(Book book) {

    }

    @Override
    public void modifyBook(Book book) {

    }

    @Override
    public List<Book> queryAll() {
        return null;
    }

    @Override
    public void insertAll(List<Book> bookList) {

    }

    @Override
    public void deleteBook(Book book) {

    }

    @Override
    public void deleteAll(List<Book> bookList) {

    }

    @Override
    public void deleteById(String id) {

    }

    @Override
    public Book queryOneBook(Book book) {
        return null;
    }

    @Override
    public Book queryOneBookById(String id) {
        return null;
    }
}

BookDAO

package com.spring.www.DAO;

import com.spring.www.Model.Book;

import java.util.List;

public interface BookDAO {
    //定义了在数据库上的所有增删改查操作
    void insertBook(Book book);
    void modifyBook(Book book);

    List<Book> queryAll();
    void insertAll(List<Book> bookList);

    void deleteBook(Book book);
    void deleteAll(List<Book> bookList);
    void deleteById(String id);
    Book queryOneBook(Book book);
    Book queryOneBookById(String id);
}

BookDAOImpl

package com.spring.www.DAO;

import com.spring.www.Model.Book;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.util.List;


@Repository(value = "bookDaoImpl")
public class BookDAOImpl extends AbstractBookDAO{
    @Override
    public void insertBook(Book book) {
        super.insertBook(book);
        String INSERT_BOOK="INSERT INTO book (isdn,name,price) VALUES(?,?,?)";//预处理方式
        Object[] args={book.getIsdn(),book.getName(),book.getPrice()};
        jdbcTemplate.update(INSERT_BOOK,args);
    }

    @Override
    public void modifyBook(Book book) {
        super.modifyBook(book);
        String MODIFY_BOOK="UPDATE book SET name=?,price=? where isdn=?";
        Object[] args={ book.getName(),book.getPrice(),book.getIsdn()};
        jdbcTemplate.update(MODIFY_BOOK,args);
    }

    @Override
    public List<Book> queryAll() {
        String QUERY_ALL="SELECT * FROM `book`";
        RowMapper<Book> rowMapper=new BeanPropertyRowMapper<>(Book.class);
        books=jdbcTemplate.query(QUERY_ALL,rowMapper);
        return books;
    }
}

Book

package com.spring.www.Model;


import org.springframework.stereotype.Component;

@Component
public class Book {
    private String name;
    private String isdn;

    @Override
    public String toString() {
        return "Book{" +
                "name='" + name + '\'' +
                ", isdn='" + isdn + '\'' +
                ", price=" + price +
                '}';
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getIsdn() {
        return isdn;
    }

    public void setIsdn(String isdn) {
        this.isdn = isdn;
    }

    public float getPrice() {
        return price;
    }

    public void setPrice(float price) {
        this.price = price;
    }

    private float price;
}

database-context.xml

<?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:p="http://www.springframework.org/schema/p"
       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">
    <context:component-scan base-package="com.spring.www"/>
    <!--配置集合对象-->
    <bean id="list" class="java.util.ArrayList" scope="prototype"/>
    <!--加载数据库访问配置-->
    <context:property-placeholder location="mysql.properties"/>

    <!--配置数据源-->
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
          destroy-method="close"
          p:driverClassName="${driverClassName}"
          p:url="${mysql.url}"
          p:username="${mysql.yourName}"
          p:password="${mysql.password}"/>

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"
          p:dataSource-ref="dataSource"/>
</beans>

javaee.sql

creat database javaee
create table book
(
    isdn  varchar(15)  not null
        primary key,
    name  varchar(100) null,
    price float        null
);

mysql.properties

driverClassName=com.mysql.jdbc.Driver
mysql.url=jdbc:mysql://localhost:3306/javaee?useSSL=false
mysql.yourName=root
mysql.password=root

Beantest

import com.spring.www.Business.SaleService;
import com.spring.www.Model.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.testng.AbstractTestNGSpringContextTests;
import org.testng.annotations.Test;

import static org.testng.AssertJUnit.assertTrue;

@ContextConfiguration("classpath*:database-context.xml")
public class BeanTest extends AbstractTestNGSpringContextTests {


    @Autowired
    Book book;


    @Autowired
    SaleService service;

    @Test
    public void beantest(){
        book.setName("java ee");
        assertTrue(book!=null);
    }


    @Test
    public void insert(){
        book.setIsdn("890");
        book.setName("123");
        book.setPrice((float) 12.23);
        service.insert(book);
    }

    @Test
    public void onsale(){
        service.onSale(0.5f);
    }



}