SpringBoot之旅-數(shù)據(jù)訪問

<!--JDBC --><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-jdbc</artifactId></dependency><!--mysql 驅(qū)動--><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><scope>runtime</scope></dependency>

@Autowiredprivate DataSource dataSource;
@Testpublic void test() throws SQLException {    System.out.println(dataSource.getClass());    Connection connection = dataSource.getConnection();    System.out.println(connection);    connection.close();}

spring:datasource:schema:- classpath:department.sql

@AutowiredJdbcTemplate jdbcTemplate;
@Testpublic void jdbcTest(){    List<Map<String, Object>> mapList = jdbcTemplate.queryForList("select * from user ");    System.out.println(mapList.get(0));}

<!-- https://mvnrepository.com/artifact/com.alibaba/druid --><dependency><groupId>com.alibaba</groupId><artifactId>druid</artifactId><version>1.1.16</version></dependency>

type: com.alibaba.druid.pool.DruidDataSource#   數(shù)據(jù)源其他配置initialSize: 5minIdle: 5maxActive: 20maxWait: 60000timeBetweenEvictionRunsMillis: 60000minEvictableIdleTimeMillis: 300000validationQuery: SELECT 1 FROM DUALtestWhileIdle: truetestOnBorrow: falsetestOnReturn: falsepoolPreparedStatements: true#   配置監(jiān)控統(tǒng)計攔截的filters，去掉后監(jiān)控界面sql無法統(tǒng)計，'wall'用于防火墻filters: stat,wall,log4jmaxPoolPreparedStatementPerConnectionSize: 20useGlobalDataSourceStat: trueconnectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

@Configurationpublic class DruidConfig {
@ConfigurationProperties(prefix = "spring.datasource")@Beanpublic DataSource druid(){return new DruidDataSource();    }}

<!-- https://mvnrepository.com/artifact/log4j/log4j --><dependency><groupId>log4j</groupId><artifactId>log4j</artifactId><version>1.2.17</version></dependency>

//配置Druid的監(jiān)控//1、配置一個管理后臺的Servlet@Beanpublic ServletRegistrationBean statViewServlet(){    ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");    Map<String,String> initParams = new HashMap<>();
    initParams.put("loginUsername","admin");    initParams.put("loginPassword","123456");    initParams.put("allow","");//默認(rèn)就是允許所有訪問    initParams.put("deny","192.168.15.21");
    bean.setInitParameters(initParams);return bean;}

//2、配置一個web監(jiān)控的filter@Beanpublic 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;}

<dependency><groupId>org.mybatis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>1.3.1</version></dependency>

@Mapperpublic interface DepartmentMapper {@Select("select * from department where id=#{id}")Department getDeptById(Integer id);
@Delete("delete from department where id=#{id}")int deleteDeptById(Integer id);
@Options(useGeneratedKeys = true,keyProperty = "id")@Insert("insert into department(departmentName) values(#{departmentName})")int insertDept(Department department);
@Update("update department set departmentName=#{departmentName} where id=#{id}")int updateDept(Department department);}

@AutowiredUserMapper userMapper;
@AutowiredDepartmentMapper departmentMapper;
@Testpublic void mybatisTest(){    Department deptById = departmentMapper.getDeptById(1);    System.out.println(deptById);
}

@Mapperpublic interface UserMapper {User queryUserById(Integer id);}

<?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>
</configuration>

mybatis:  config-location: classpath:mybatis/SqlMapConfig.xml  mapper-locations: classpath:mybatis/mapper/*.xml

<?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.yuanqinnan.mapper.UserMapper"><select id="queryUserById" parameterType="int" resultType="com.yuanqinnan.model.User">    SELECT * FROM `user`where id=#{id}</select>
</mapper>

@Testpublic void mybatisTest(){    Department deptById = departmentMapper.getDeptById(1);    System.out.println(deptById);    User userById = userMapper.queryUserById(1);    System.out.println(userById);}

<!-- springdata jpa依賴 --><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-data-jpa</artifactId></dependency>

//使用JPA注解配置映射關(guān)系@Entity //告訴JPA這是一個實體類（和數(shù)據(jù)表映射的類）@Table(name = "order") //@Table來指定和哪個數(shù)據(jù)表對應(yīng);order；@Datapublic class Order {
@Id //這是一個主鍵@GeneratedValue(strategy = GenerationType.IDENTITY)//自增主鍵private Integer id;
@Column(name = "user_Id")private Integer userId;//這是和數(shù)據(jù)表對應(yīng)的一個列@Column(name="number",length = 32)private String number;// 訂單創(chuàng)建時間,省略默認(rèn)列名就是屬性名private Date createtime;// 備注private String note;}

@Repositorypublic interface OrderRepository extends JpaRepository<Order, Integer> {}

@AutowiredOrderRepository orderRepository;
@Testpublic void jpaTest(){    List<Order> all = orderRepository.findAll();    System.out.println(all);
}