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;}@Overridepublic 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;}@Overridepublic void save(Account account) {mapper.save(account);}

controller

类：获取service层数据

public void setService(IAccountService service) {this.service = service;}@Overridepublic 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();}@Testpublic void show1(){controller.save(new Account("小李",2000));controller.save(new Account("大李",2000));}@Testpublic 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{//操作数据库的核心类@AutowiredQueryRunner queryRunner;

service

@Service
public class AccountServiceImp implements IAccountService{@AutowiredIAccountMapper mapper;

controller

@Controller("controller")
public class AccountControllerImp implements IAccountController {@AutowiredIAccountService 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;@Beanpublic 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;}@Beanpublic QueryRunner queryRunner(){return  new QueryRunner(dataSource());}