spring操作数据库

xml版

程序结构

配置文件

dbUtil-阿帕奇提供操作数据库的插件

核心类：QueryRunner

.query() 查询

.update() 增删改

<dependencies>
        <!-- spring -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.3.27</version>
        </dependency>
        <!-- 单元测试 -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
        <!-- spring测试包 -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>5.3.27</version>
        </dependency>
        <!-- dbutil -->
        <dependency>
            <groupId>commons-dbutils</groupId>
            <artifactId>commons-dbutils</artifactId>
            <version>1.4</version>
        </dependency>
        <!-- mysql -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.28</version>
        </dependency>
        <!-- 数据源 -->
        <dependency>
            <groupId>c3p0</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.1.2</version>
        </dependency>
        <!-- lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.26</version>
        </dependency>
    </dependencies>

实体类

实现Serializable接口

lombok插件

功能：对实体类自动，动态生成getset，无参有参.....

步骤：

1.idea安装插件(只做一次)

2.添加坐标

3.编写注解

@NoArgsConstructor
@AllArgsConstructor
@Data

dao

配坐标 数据注入（set）

接口：

    public void save(Account account);
    public Account findByName(String name);
    public List<Account> findAll();
    public void updateById(Account account);
    public void deleteById(int id);

类：sql语句，继承接口

//操作数据库的核心类
    QueryRunner queryRunner;
    public void setQueryRunner(QueryRunner queryRunner) {
        this.queryRunner = queryRunner;
    }
    @Override
    public void save(Account account) {
        try {
            queryRunner.update("insert into test(aname,amoney) value (?,?)",account.getAname(),account.getAmoney());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

service

类：获取dao层数据

IAccountMapper mapper;
 
    public void setMapper(IAccountMapper mapper) {
        this.mapper = mapper;
    }
    @Override
    public void save(Account account) {
        mapper.save(account);
    }

controller

类：获取service层数据

public void setService(IAccountService service) {
        this.service = service;
    }
    @Override
    public void save(Account account) {
        service.save(account);
    }

applicationContext.xml

链接数据库，层层传递数据

<!--    加载资源文件-->
    <context:property-placeholder location="classpath:jdbc.properties"/>
 
    <!--    注入数据源-->
    <bean id="dateSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="${msg1}"/>
        <property name="jdbcUrl" value="${msg2}"/>
        <property name="user" value="${msg3}"/>
        <property name="password" value="${msg4}"/>
    </bean>
 
    <!--    注入QueryRunner-->
    <bean id="queryRunner" class="org.apache.commons.dbutils.QueryRunner">
        <constructor-arg name="ds" ref="dateSource"/>
    </bean>
 
    <!--    注入dao-->
    <bean id="mapperImp" class="com.zhe.dao.AccountMapperImp">
        <property name="queryRunner" ref="queryRunner"/>
    </bean>
    <!--    注入service-->
    <bean id="service" class="com.zhe.service.AccountServiceImp">
        <property name="mapper" ref="mapperImp"/>
    </bean>
    <!--    注入controller-->
    <bean id="controller" class="com.zhe.controller.AccountControllerImp">
        <property name="service" ref="service"/>
    </bean>

junit测试

使用步骤：

1.坐标

2.注解(修饰方法)

@Test======>可以运行的方法

@Before====>@Test运行之前

@After=====>@Test运行之后

 
    ClassPathXmlApplicationContext applicationContext=null;
    IAccountController controller=null;
    @Before //在test前调用
    public void beforMethod(){
        applicationContext=new ClassPathXmlApplicationContext("applicationContext.xml");
        controller=(IAccountController)applicationContext.getBean("controller");
    }
    @After//在test后调用
    public void afterMethod(){
        applicationContext.close();
    }
    @Test
    public void show1(){
        controller.save(new Account("小李",2000));
        controller.save(new Account("大李",2000));
    }
    @Test
    public void show2(){
        List<Account> all=controller.findAll();
        for (int i=0;i<all.size();i++){
            Account account=all.get(i);
            System.out.println(account);
        }
    }

ann注解版

xml

将三层数据注入换为扫描

<!--    扫描-->
    <context:component-scan base-package="com.zhe"/>

dao

加@Repository标签，set方法换为@Autowired标签

@Repository
public class AccountMapperImp implements IAccountMapper{
    //操作数据库的核心类
    @Autowired
    QueryRunner queryRunner;

service

@Service
public class AccountServiceImp implements IAccountService{
    @Autowired
    IAccountMapper mapper;

controller

@Controller("controller")
public class AccountControllerImp implements IAccountController {
    @Autowired
    IAccountService service;

配置类版

添加config包

ApplicationConfig类

@Configuration//配置类注解
@ComponentScan(basePackages = "com.zhe")//氛围
@Import(DataConfig.class)//连接注解
public class ApplicationConfig {
}

DataConfig类

连接配置文件

@Configuration//配置类注解
@PropertySource(value = "classpath:jdbc.properties")//加载配置文件
public class DataConfig {
    @Value("${msg1}")
    private String driverClass;
    @Value("${msg2}")
    private String jdbcUrl;
    @Value("${msg3}")
    private String user;
    @Value("${msg4}")
    private String password;
    @Bean
    public DataSource dataSource(){
        try {
            ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
            comboPooledDataSource.setDriverClass(driverClass);
            comboPooledDataSource.setJdbcUrl(jdbcUrl);
            comboPooledDataSource.setUser(user);
            comboPooledDataSource.setPassword(password);
            return comboPooledDataSource;
        } catch (PropertyVetoException e) {
            e.printStackTrace();
        }
        return  null;
    }
    @Bean
    public QueryRunner queryRunner(){
        return  new QueryRunner(dataSource());
    }