整合JDBC数据源 1、新建项目 spring-boot-06-data-jdbc
WEB
Mysql
JDBC
SpringBoot1.5
2、编写配置文件appliction.yml
1 2 3 4 5 6 spring: datasource: username: root password: Welcome_1 url: jdbc:mysql://192.168.179.131:3306/jdbc driver-class-name: com.mysql.jdbc.Driver
3、编写测试类测试
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 (SpringRunner.class) @SpringBootTest public class { @Autowired DataSource dataSource; @Test public void contextLoads () throws SQLException { System.out.println(dataSource.getClass()); Connection connection = dataSource.getConnection(); System.out.println(connection); connection.close(); } }
4、测试结果
1 2 class org.apache.tomcat.jdbc.pool.DataSource ProxyConnection[PooledConnection[[email protected] ]]
数据源相关配置都在DataSourceProperties属性里
自动配置原理
E:DevelopMaven_Repoorgspringframeworkbootspring-boot-autoconfigure1.5.13.RELEASEspring-boot-autoconfigure-1.5.13.RELEASE.jar!orgspringframeworkbootautoconfigurejdbc
DataSource 参考DataSourceConfiguration,根据配置创建数据源,默认是使用tomcat连接池,可以使用spring.datasource.type指定自定义的数据源
SpringBoot默认支持 1 2 3 4 Tomcat数据源 HikariDataSource dbcp.BasicDataSource dbcp2.BasicDataSource
自定义数据源 1 2 3 4 5 6 7 8 9 10 11 12 */ @ConditionalOnMissingBean (DataSource.class)@ConditionalOnProperty (name = "spring.datasource.type" )static class Generic { @Bean public DataSource dataSource (DataSourceProperties properties) { return properties.initializeDataSourceBuilder().build(); } }
运行sql建表 在DataSourceAutoConfiguration中DataSourceInitializer 类
监听器
作用:
1)、postConstruct -》runSchemaScript 运行建表sql文件
2)、runDataScript运行插入数据的sql语句;
默认只需要将文件命名为:
1 2 schema-*.sql data-*.sql 默认规则:schema.sql ,schema-all.sql;
举个栗子
创建department表
1、department.sql
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 SET FOREIGN_KEY_CHECKS=0 ;DROP TABLE IF EXISTS `department` ;CREATE TABLE `department` ( `id` int (11 ) NOT NULL AUTO_INCREMENT, `departmentName` varchar (255 ) DEFAULT '' , PRIMARY KEY (`id` ) ) ENGINE =InnoDB DEFAULT CHARSET =utf8;
2、将department.sql命名为schema-all.sql
3、运行测试类
自定义sql的文件名,department.sql在配置文件中
1 2 schema: - classpath: department.sql
操作JdbcTemplate FBI warning :将department.sql删除或者改名,因为运行文件会将表中数据清除
新建一个Controller 1 2 3 4 5 6 7 8 9 10 11 12 13 14 @Controller public class HelloController { @Autowired JdbcTemplate jdbcTemplate; @ResponseBody @GetMapping ("/hello" ) public Map<String ,Object> hello () { List<Map<String, Object>> list = jdbcTemplate.queryForList("select * from department" ); return list.get(0 ); } }
表中添加数据
访问请求查询数据
自定义数据源 导入Druid的依赖 1 2 3 4 5 6 <dependency > <groupId > com.alibaba</groupId > <artifactId > druid</artifactId > <version > 1.1.9</version > </dependency >
修改配置文件 1 2 3 4 5 6 7 8 9 10 11 spring: datasource: username: root password: Welcome_1 url: jdbc:mysql://192.168.179.131:3306/jdbc driver-class-name: com.mysql.jdbc.Driver type: com.alibaba.druid.pool.DruidDataSource server: port: 9000
已经替换了原来的tomcat数据源
配置Druid数据源配置 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 spring: datasource: username: root password: Welcome_1 url: jdbc:mysql://192.168.179.131:3306/jdbc driver-class-name: com.mysql.jdbc.Driver type: com.alibaba.druid.pool.DruidDataSource initialSize: 5 minIdle: 5 maxActive: 20 maxWait: 60000 timeBetweenEvictionRunsMillis: 60000 minEvictableIdleTimeMillis: 300000 validationQuery: SELECT 1 FROM DUAL testWhileIdle: true testOnBorrow: false testOnReturn: false poolPreparedStatements: true filters: stat,wall,log4j maxPoolPreparedStatementPerConnectionSize: 20 userGlobalDataSourceStat: true connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500 server: port: 9000
Druid配置监控 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 @Configuration public class DruidConfig { @ConfigurationProperties (prefix = "spring.datasource" ) @Bean public DataSource druid () { return new DruidDataSource(); } @Bean public ServletRegistrationBean statViewServlet () { ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(),"/druid/*" ); Map<String,String> initParams =new HashMap<>(); initParams.put("loginUsername" , "admin" ); initParams.put("loginPassword" , "123456" ); bean.setInitParameters(initParams); return bean; } @Bean public FilterRegistrationBean webstatFilter () { FilterRegistrationBean bean = new FilterRegistrationBean(); bean.setFilter(new WebStatFilter()); Map<String,String> initParams =new HashMap<>(); initParams.put("exclusions" , "*.js,*.css,/druid/*" ); bean.setInitParameters(initParams); bean.setUrlPatterns(Arrays.asList("/*" )); return bean; } }
运行测试,访问 localhost:9000/druid
输入刚才调好的用户名密码即可访问
整合Mybatis 1、新建工程,SpringBoot1.5+web+JDBC+Mysql
导入依赖
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 <dependency > <groupId > org.mybatis.spring.boot</groupId > <artifactId > mybatis-spring-boot-starter</artifactId > <version > 1.3.2</version > </dependency > <dependency > <groupId > com.alibaba</groupId > <artifactId > druid</artifactId > <version > 1.1.9</version > </dependency > <dependency > <groupId > mysql</groupId > <artifactId > my 大专栏 Spring Boot 数据访问 sql-connector-java</artifactId > <scope > runtime</scope > </dependency > <dependency > <groupId > org.springframework.boot</groupId > <artifactId > spring-boot-starter-jdbc</artifactId > </dependency >
2、导入配置文件中关于Druid的配置
2.1、导入依赖
2.2、配置文件application.yml(指定用户名密码…配置Druid的配置参数,修改sql文件加载的默认名)
2.3、将Druid组件加入到容器中(监控)重点
具体同上
3、创建数据表department和employee表
3.1、根据sql文件,新建两张表
3.2、修改加载的sql名(默认为schema.sql和schema-all.sql)
1 2 3 4 5 spring: datasource: schema: - classpath: sql/department.sql - classpath: sql/employeee.sql
3.3、运行程序检查数据库是否创建成功
4、创建数据库对应的JavaBean (驼峰命名,getter/setter toString/注释掉schema防止重复创建)
在配置文件中修改驼峰命名开启 ,不写配置文件就写配置类
1 2 3 mybatis: configuration: map-underscore-to-camel-case: true
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 @org .springframework.context.annotation.Configurationpublic class MyBatisConfig { @Bean public ConfigurationCustomizer configurationCustomizer () { return new ConfigurationCustomizer() { @Override public void customize (Configuration configuration) { configuration.setMapUnderscoreToCamelCase(true ); } }; } }
注解方式 5、新建mapper
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 //指定是一个mapper @Mapper public interface DepartmentMapper { @Insert("insert into department(dept_name) value(#{deptName})") public int insertDept(Department department); @Delete("delete from department where id=#{id}") public int deleteDeptById(Integer id); @Update("update department set dept_Name=#{deptName} where id=#{id}") public int updateDept(Department department); @Select("select * from department where id=#{id}") public Department getDeptById(Integer id); }
6、编写controller测试
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 @RestController public class DeptController { @Autowired DepartmentMapper departmentMapper; @RequestMapping ("/getDept/{id}" ) public Department getDepartment (@PathVariable("id" ) Integer id) { return departmentMapper.getDeptById(id); } @RequestMapping ("/delDept/{id}" ) public int delDept (@PathVariable("id" ) Integer id) { return departmentMapper.deleteDeptById(id); } @RequestMapping ("/update/{id}" ) public int updateDept (@PathVariable("id" ) Integer id) { return departmentMapper.updateDept(new Department(id, "开发部" )); } @GetMapping ("/insert" ) public int insertDept (Department department) { return departmentMapper.insertDept(department); } }
问题:
mapper文件夹下有多个mapper文件,加麻烦,可以直接扫描整个mapper文
件夹下的mapper
1 2 @MapperScan (value = "com.wdjr.springboot.mapper" )
配置文件方式 1、新建文件
2、新建mybatis的配置文件
1 2 3 4 5 6 7 8 9 <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration > <settings > <setting name ="mapUnderscoreToCamelCase" value ="true" /> </settings > </configuration >
3、新建Employee的接口方法
1 2 3 4 5 6 public interface EmployeeMapper { public Employee getEmpById (Integer id) ; public void insetEmp (Employee employee) ; }
4、新建Employee的mapper.xml的映射文件
1 2 3 4 5 6 7 8 9 10 11 12 13 <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace ="com.wdjr.springboot.mapper.EmployeeMapper" > <select id ="getEmpById" resultType ="com.wdjr.springboot.bean.Employee" > select * from employee where id=#{id} </select > <insert id ="insetEmp" > INSERT INTO employee(last_name,email,gender,d_id) VALUES (#{lastName},#{email},#{gender},#{dId}) </insert > </mapper >
5、修改application.yml配置文件
1 2 3 mybatis: config-location: classpath:mybatis/mybatis-config.xml mapper-locations: classpath:mybatis/mapper/*.xml
6、新建一个Controller访问方法
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 @RestController public class EmployeeController { @Autowired EmployeeMapper employeeMapper; @RequestMapping ("/getEmp/{id}" ) public Employee getEmp (@PathVariable("id" ) Integer id) { return employeeMapper.getEmpById(id); } @GetMapping ("/insertEmp" ) public Employee insertEmp (Employee employee) { employeeMapper.insetEmp(employee); return employee; } }
JPA数据访问 新建工程 springBoot1.5+Web+JPA+MYSQL+JDBC
目录结构
1、新建一个实体类User
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 @Entity @Table (name="tbl_user" ) public class User { @Id @GeneratedValue (strategy = GenerationType.IDENTITY) private Integer id ; @Column (name="last_name" ,length = 50 ) private String lastName; @Column private String email; @Column public Integer getId () { return id; } public void setId (Integer id) { this .id = id; } public String getLastName () { return lastName; } public void setLastName (String lastName) { this .lastName = lastName; } public String getEmail () { return email; } public void setEmail (String email) { this .email = email; } }
2、新建一个UserRepository来继承jpa的绝大多数功能
1 2 3 4 public interface UserRepository extends JpaRepository <User ,Integer > {}
3、编写配置文件application.yml
1 2 3 4 5 6 7 8 9 10 11 spring: datasource: url: jdbc:mysql://192.168.179.131/jpa username: root password: Welcome_1 driver-class-name: com.mysql.jdbc.Driver jpa: hibernate: ddl-auto: update show-sql: true
4、编写Controller测试
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 @RestController public class UserController { @Autowired UserRepository userRepository; @GetMapping ("/user/{id}" ) public User getUser (@PathVariable("id" ) Integer id) { User user = userRepository.findOne(id); return user; } @GetMapping ("/insert" ) public User insertUser (User user) { User user1 = userRepository.save(user); return user1; } }
#