一、Xml方式访问数据库
1.1 Spring的JdbcTemplate
Spring的JdbcTemplate(了解会用) 在Spring中提供了一个可以操作数据库的对象org.springframework.jdbc.core.JdbcTemplate,对象封装了jdbc技术,JDBC的模板对象与DBUtils中的QueryRunner非常相似.
使用:
需要在pom.xml中导入依赖
<dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>5.0.2.RELEASE</version> </dependency>
案例:
我的数据库中的表:
创建数据表的sql语句:
/*
SQLyog Ultimate v13.1.1 (64 bit)
MySQL - 5.7.29
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
create table `t_user` (
`id` int (11),
`name` varchar (150),
`password` varchar (150)
);
insert into `t_user` (`id`, `name`, `password`) values('1','jack','123456');
insert into `t_user` (`id`, `name`, `password`) values('2','tom','456');
insert into `t_user` (`id`, `name`, `password`) values('6','张无忌','989898');
insert into `t_user` (`id`, `name`, `password`) values('7','张三三','987654');
insert into `t_user` (`id`, `name`, `password`) values('8','张无极','252525');
insert into `t_user` (`id`, `name`, `password`) values('14','韩信','666666');
insert into `t_user` (`id`, `name`, `password`) values('15','刘备','222222');
insert into `t_user` (`id`, `name`, `password`) values('16','赵敏','131313');
我的项目结构:
1.创建一个maven项目,导入依赖,下面是我的pom.xml文件
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.ayit</groupId>
<artifactId>spring-10</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.3.10</version>
</dependency>
<!--连接数据库-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.3.10</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.7</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.26</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.20</version>
</dependency>
</dependencies>
</project>
2.创建连接数据库的配置文件(db.properties)(我这里用的是Druid数据库连接池)
db.username = root
db.password = 123456
db.url = jdbc:mysql://localhost:3306/contact_system?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF8
db.driverClassName = com.mysql.cj.jdbc.Driver
3.编写实体类
package com.ayit.pojo;
import lombok.Data;
@Data
public class User {
public Integer id;
private String name;
private String password;
}
4.编写Dao层(Mapper层)
package com.ayit.mapper;
import com.ayit.pojo.User;
import java.util.List;
public interface UserMapper {
List<User> findAll();
}
package com.ayit.mapper.impl;
import com.ayit.mapper.UserMapper;
import com.ayit.pojo.User;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import java.util.List;
public class UserMapperImpl implements UserMapper {
private JdbcTemplate jdbcTemplate;
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
@Override
public List<User> findAll() {
String sql = "select * from t_user";
List<User> userList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class));
return userList;
}
}
5.编写service层
package com.ayit.service;
import com.ayit.pojo.User;
import java.util.List;
public interface UserService {
List<User> findAll();
}
package com.ayit.service.impl;
import com.ayit.mapper.UserMapper;
import com.ayit.pojo.User;
import com.ayit.service.UserService;
import java.util.List;
public class UserServiceImpl implements UserService {
private UserMapper userMapper;
public void setUserMapper(UserMapper userMapper) {
this.userMapper = userMapper;
}
@Override
public List<User> findAll() {
return userMapper.findAll();
}
}
6.编写controller层
package com.ayit.controller;
import com.ayit.pojo.User;
import com.ayit.service.UserService;
import java.util.List;
public class UserController {
private UserService userService;
public void setUserService(UserService userService) {
this.userService = userService;
}
public List<User> findAll(){
return userService.findAll();
}
}
7.创建applicationContext.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" 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"> <!-- bean definitions here -->
<!--创建properties的bean对象-->
<context:property-placeholder location="classpath:db.properties"></context:property-placeholder>
<!--配置数据源-->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
<property name="username" value="${db.username}"></property>
<property name="password" value="${db.password}"></property>
<property name="url" value="${db.url}"></property>
<property name="driverClassName" value="${db.driverClassName}"></property>
</bean>
<!--创建JdbcTemplate的bean对象-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
<!--mapper层-->
<bean id="userMapper" class="com.ayit.mapper.impl.UserMapperImpl">
<property name="jdbcTemplate" ref="jdbcTemplate"></property>
</bean>
<!--service层-->
<bean id="userService" class="com.ayit.service.impl.UserServiceImpl">
<property name="userMapper" ref="userMapper"></property>
</bean>
<!--controller层-->
<bean id="userController" class="com.ayit.controller.UserController">
<property name="userService" ref="userService"></property>
</bean>
</beans>
8.编写测试类:
import com.ayit.controller.UserController;
import com.ayit.pojo.User;
import org.junit.Test;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import java.util.List;
public class SpringTest {
@Test
public void test(){
ClassPathXmlApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
UserController userController = (UserController) applicationContext.getBean("userController");
List<User> userList = userController.findAll();
for (User user : userList) {
System.out.println(user);
}
}
}
9.结果:
二、注解方式访问数据库
2.1 Spring中的注解
@Configuration 作用:指定当前类是一个配置类 细节:当配置类作为AnnotationConfigApplicationContext对象创建的参数时,该注解可以不写。
@ComponentScan 作用:用于通过注解指定spring在创建容器时要扫描的包 属性:value:它和basePackages的作用是一样的,都是用于指定创建容器时要扫描的包。 等同于xml中: <context:component-scan base-package="com.qf"/>
@PropertySource 作用:用于指定properties文件的位置 属性:value:指定文件的名称和路径。 关键字:classpath,表示类路径下
等同于xml中: <context:property-placeholder location="classpath:jdbc.properties"/>
@Bean 作用:用于把当前方法的返回值作为bean对象存入spring的ioc容器中 属性:name:用于指定bean的id。当不写时,默认值是当前方法的名称 细节:当我们使用注解配置方法时,如果方法有参数,在参数前加:@Qualifier("@Bean注解中name的值"),spring框架会去容器中查找有没有可用的bean对象查找的方式和Autowired注解的作用是一样的。
@Import 作用:用于导入其他的配置类 属性:value:用于指定其他配置类的字节码。 当我们使用Import的注解之后,有Import注解的类就父配置类,而导入的都是子配置类
等同于xml中: <import resource="xxx.xml"></import>
案例:
目录结构
1.导入依赖(pom.xml文件)
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.ayit</groupId>
<artifactId>spring-04</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.3.10</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.3.10</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>5.3.10</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.7</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.26</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.20</version>
</dependency>
</dependencies>
</project>
2.创建db.properties数据库连接配置文件(和上个案例的配置文件一样)
3.编写实体类(和上个案例的实体类代码一样)
4.编写dao(mapper)层
package com.qf.mapper;
import com.qf.pojo.User;
public interface UserMapper {
User findById(Integer id);
}
package com.qf.mapper.impl;
import com.qf.mapper.UserMapper;
import com.qf.pojo.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
@Repository
public class UserMapperImpl implements UserMapper {
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public User findById(Integer id) {
//准备一个sql语句
String sql = "select * from t_user where id = ?";
return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(User.class),id).get(0);
}
}
5. 编写service层
package com.qf.service;
import com.qf.pojo.User;
public interface UserService {
User findById(Integer id);
}
package com.qf.service.impl;
import com.qf.mapper.UserMapper;
import com.qf.pojo.User;
import com.qf.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service
public class UserServiceImpl implements UserService {
@Autowired
private UserMapper userMapper;
@Override
public User findById(Integer id) {
return userMapper.findById(id);
}
}
6.编写controller层
package com.qf.service.impl;
import com.qf.mapper.UserMapper;
import com.qf.pojo.User;
import com.qf.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service
public class UserServiceImpl implements UserService {
@Autowired
private UserMapper userMapper;
@Override
public User findById(Integer id) {
return userMapper.findById(id);
}
}
7 使用Spring整合junit测试
7.1 导入spring整合junit坐标(上面的的pom.xml文件已经导入这个依赖了)
<dependency> <groupId>org.springframework</groupId> <artifactId>spring-test</artifactId> <version>5.0.2.RELEASE</version> </dependency>
7.2 使用Junit提供的一个注解把原有的main方法替换了,替换成spring提供的
@RunWith(SpringJUnit4ClassRunner.class)
7.3 告知spring的运行器,spring和ioc创建是基于xml还是注解的,并且说明位置
@ContextConfiguration(locations = "classpath:applicationContext.xml") locations:指定xml文件的位置,classpath关键字表示在类路径下
@ContextConfiguration(classes = SpringConfiguration.class) classes:指定注解配置类(需要手动编写配置类)
注意:当我们使用spring 5.x版本的时候,要求junit的jar必须是4.12及以上,spring版本必须保持一致
8、 编写注解配置类
package com.qf.config;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.jdbc.core.JdbcTemplate;
import javax.sql.DataSource;
import java.util.Properties;
@Configuration//设置当前类为配置类,优先于其它类执行,相当于applicationContext.xml文件
@PropertySource(value = "classpath:db.properties")//指定db.properties配置文件的位置
@ComponentScan("com.qf")//扫描包下面的注解
public class SpringConfiguration {
@Value("${db.username}")
private String username;
@Value("${db.password}")
private String password;
@Value("${db.url}")
private String url;
@Value("${db.driverClassName}")
private String driverClassName;
@Bean
public JdbcTemplate getJdbcTemplate(){
//创建Properties文件对象
Properties properties = new Properties();
properties.setProperty("username",username);
properties.setProperty("password",password);
properties.setProperty("url",url);
properties.setProperty("driverClassName",driverClassName);
//创建DataSource数据源对象
try {
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
//创建Jdbc模板对象
return new JdbcTemplate(dataSource);
} catch (Exception e) {
e.printStackTrace();
}
throw new RuntimeException("连接数据库异常!");
}
}
9.编写测试类进行测试:
import com.qf.config.SpringConfiguration;
import com.qf.controller.UserController;
import com.qf.pojo.User;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
@RunWith(SpringJUnit4ClassRunner.class)//集成JUnit
@ContextConfiguration(classes = SpringConfiguration.class)//引入配置类
public class SpringTest {
@Autowired
private UserController userController;
@Test
public void testFindById(){
User user = userController.findById(6);
System.out.println(user);
}
}
10、结果:(红色字体是日志信息)