SpringJDBC 从手动配置到自动装配状态实例
第一种方式:原生状态的springboot连接数据库进行查询
新建一个maven工程
-
1引入相关依赖
<parent> <artifactId>spring-boot-starter-parent</artifactId> <groupId>org.springframework.boot</groupId> <version>2.3.2.RELEASE</version> </parent> <dependencies> <!--不包含autoconfig--> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-thymeleaf</artifactId> </dependency> <dependency> <groupId>net.sourceforge.nekohtml</groupId> <artifactId>nekohtml</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> </dependencies>
1.2 手动创建Start
@SpringBootApplication public class Start { public static void main(String[] args) { SpringApplication.run(Start.class, args); } }
1.3 创建和数据库对应的实体类(此处省略构造器和get set 方法)
public class Student { private int id; private String name; private String sex; private Date birth; private String department; private String address; }
1.4 创建controller层
@Controller public class DemoController { @RequestMapping("/showdata") @ResponseBody public List<Student> showdata(){ DriverManagerDataSource ds = new DriverManagerDataSource(); ds.setDriverClassName("com.mysql.cj.jdbc.Driver"); ds.setUrl("jdbc:mysql://localhost:3306/mydemo?useUnicode=true&characterEncoding=UTF-8&useSSL=false&allowMultiQueries=true&serverTimezone=Asia/Shanghai"); ds.setUsername("root"); ds.setPassword("****"); JdbcTemplate jdbcTpl = new JdbcTemplate(); jdbcTpl.setDataSource(ds); List<Student> list=jdbcTpl.query("select * from student",new BeanPropertyRowMapper<>(Student.class)); return list; } }
1.5 创建配置类
#server server.port=8000 server.undertow.io-threads=16 server.undertow.worker-threads=256 server.undertow.direct-buffers=true #view spring.thymeleaf.mode=LEGACYHTML5
在浏览器输入http://localhost:8000/showdata 查询出数据库数据
第二种方式:使用spring-app.xml配置数据库连接
2.1 建立一个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"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd" >
<bean class="org.springframework.jdbc.datasource.DriverManagerDataSource" id="dataSource">
<property name="driverClassName" value="com.mysql.cj.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql://localhost:3306/mydemo?useUnicode=true&characterEncoding=UTF-8&useSSL=false&allowMultiQueries=true&serverTimezone=Asia/Shanghai"></property>
<property name="username" value="root"></property>
<property name="password" value="****"></property>
</bean>
<bean class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
</beans>
2.2 在Start类上加注解
@ImportResource("classpath:spring-app.xml")
@SpringBootApplication
public class Start {
public static void main(String[] args) {
SpringApplication.run(Start.class, args);
}
}
2.3 更改 controller
@Controller
public class DemoController {
@Autowired
private DataSource dataSource;
@RequestMapping("/showdata")
@ResponseBody
public List<Student> showdata(){
JdbcTemplate jdbcTemplate = new JdbcTemplate();
jdbcTemplate.setDataSource(dataSource);
String sql="select * from student";
List<Student> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Student.class));
return list;
}
}
在浏览器输入http://localhost:8000/showdata 查询出数据库数据
第三种方式:建立一个Config类,从配置类读取配置信息
3.1 建立一个数据连接配置类并删除spring-app.xml
@Configuration
public class JdbcConfig {
@Bean
public DataSource dataSource(){
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost:3306/mydemo?useUnicode=true&characterEncoding=UTF-8&useSSL=false&allowMultiQueries=true&serverTimezone=Asia/Shanghai");
dataSource.setUsername("root");
dataSource.setPassword("***");
return dataSource;
}
@Bean
public JdbcTemplate jdbcTemplate(){
JdbcTemplate jdbcTemplate = new JdbcTemplate();
jdbcTemplate.setDataSource(dataSource());
return jdbcTemplate;
}
}
3.2 去除Start上的注解
@SpringBootApplication
public class Start {
public static void main(String[] args) {
SpringApplication.run(Start.class, args);
}
}
在浏览器输入http://localhost:8000/showdata 查询出数据库数据
第四种方式:在propertise配置数据库信息
4.1 在配置文件propertise添加数据库配置 (aaa这个字段可以随便写)
#persist
aaa.driver=com.mysql.cj.jdbc.Driver
aaa.url=jdbc:mysql://localhost:3306/mydemo?useUnicode=true&characterEncoding=UTF-8&useSSL=false&allowMultiQueries=true&serverTimezone=Asia/Shanghai
aaa.username=root
aaa.password=****
4.2 更改配置类
@Configuration
public class JdbcConfig {
@Value("${aaa.driver}")
private String dirver;
@Value("${aaa.url}")
private String url;
@Value("${aaa.username}")
private String username;
@Value("${aaa.password}")
private String password;
@Bean
public DataSource dataSource(){
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName(dirver);
dataSource.setUrl(url);
dataSource.setUsername(username);
dataSource.setPassword(password);
return dataSource;
}
@Bean
public JdbcTemplate jdbcTemplate(){
JdbcTemplate jdbcTemplate = new JdbcTemplate();
jdbcTemplate.setDataSource(dataSource());
return jdbcTemplate;
}
}
在浏览器输入http://localhost:8000/showdata 查询出数据库数据
第四种方式之二:在propertise配置数据库信息,用静态代码块读取
@Configuration
public class JdbcConfig {
private static String driver;
private static String url;
private static String username;
private static String password;
static{
InputStream resourceAsStream = JdbcConfig.class.getClassLoader().getResourceAsStream("application.properties");
Properties properties = new Properties();
try {
properties.load(resourceAsStream);
driver=properties.getProperty("aaa.driver");
url= properties.getProperty("aaa.url");
username=properties.getProperty("aaa.username");
password= properties.getProperty("aaa.password");
} catch (IOException e) {
e.printStackTrace();
}
}
@Bean
public DataSource dataSource(){
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName(driver);
dataSource.setUrl(url);
dataSource.setUsername(username);
dataSource.setPassword(password);
return dataSource;
}
@Bean
public JdbcTemplate jdbcTemplate(){
JdbcTemplate jdbcTemplate = new JdbcTemplate();
jdbcTemplate.setDataSource(dataSource());
return jdbcTemplate;
}
}
第五种方式:用springboot的自动配置
5.1 导入pom依赖
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
5.2 删除config
5.3 改写controller
@Controller
public class DemoController {
@Autowired
private JdbcTemplate jdbcTemplate;
@RequestMapping("/showdata")
@ResponseBody
public List<Student> showdata(){
String sql="select * from student";
List<Student> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Student.class));
return list;
}
}
5.4 改写application.properties
#persist
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/mydemo?useUnicode=true&characterEncoding=UTF-8&useSSL=false&allowMultiQueries=true&serverTimezone=Asia/Shanghai
spring.datasource.username=root
spring.datasource.password=****
在浏览器输入http://localhost:8000/showdata 查询出数据库数据
PS:new BeanPropertyRowMapper 到底干了什么
我们在使用BeanPropertyRowMapper时,是给query()方法传递一个BeanPropertyRowMapper对象,让JdbcTemplate帮我们把查询结果集ResultSet的每一行结果都使用BeanPropertyRowMapper.mapRow()方法,转化成我们想要的Java类对象。从BeanPropertyRowMapper名称上也能够看出来,它是用来映射Java对象的属性和MySQL表的字段名称的。但是,在映射的过程中,如果不注意Java对象的属性名的规范,很可能就得不到我们想要的结果。
https://blog.csdn.net/qq_22339269/article/details/82978717
可以自行封装一个查询方法如下: