springboot使用(三)

springboot 和 jdbc,mysql profiles结合使用

spring-boot 接上数据库,使用springjdbc

如果只是测试，spring boot 可以直接使用嵌入式db,即不用你搭下mysql来测试
这里参照https://spring.io/guides/gs/relational-data-access/

• pom.xml 增加这两个依赖

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
</dependency>

• 增加一个bean

public class Customer {
    private long id;
    private String firstName, lastName;


    @Override
    public String toString() {
        return String.format(
                "Customer[id=%d, firstName='%s', lastName='%s']",
                id, firstName, lastName);
    }

    //....get和set方法
}

• 增加一个CustomerService
  这是访问db的内容

@Service
public class CustomerService {

    private final static Logger log = LoggerFactory.getLogger(CustomerService.class);

    public String retMsg() {
        log.info("hello");
        return findAll().toString();
    }

    @Autowired
    JdbcTemplate jdbcTemplate;


    public List<Customer> findAll() {
     //jdbcTemplate.execute("DROP table  if EXISTS customers");
     jdbcTemplate.execute("create table IF NOT EXISTS  customers(" +
        "id SERIAL, first_name VARCHAR(255), last_name VARCHAR(255))");

        // Split up the array of whole names into an array of first/last names
        List<Object[]> splitUpNames = Arrays.asList("John Woo", "Jeff Dean", "Josh Bloch", "Josh Long").stream()
                .map(name -> name.split(" "))
                .collect(Collectors.toList());

        // Use a Java 8 stream to print out each tuple of the list
        splitUpNames.forEach(name -> log.info(String.format("Inserting customer record for %s %s", name[0], name[1])));

        // Uses JdbcTemplate's batchUpdate operation to bulk load data
        jdbcTemplate.batchUpdate("INSERT INTO customers(first_name, last_name) VALUES (?,?)", splitUpNames);

        log.info("Querying for customer records where first_name = 'Josh':");
        return jdbcTemplate.query(
                "SELECT id, first_name, last_name FROM customers WHERE first_name = ?", new Object[]{"Josh"}, new RowMapper<Customer>() {
                    @Override
                    public Customer mapRow(ResultSet rs, int i) throws SQLException {
                        Customer tmpc = new Customer();
                        tmpc.setId(rs.getLong("id"));
                        tmpc.setFirstName(rs.getString("first_name"));
                        tmpc.setLastName(rs.getString("last_name"));
                        return tmpc;

                    };
                });
//lambda
//        return jdbcTemplate.query(
//                "SELECT id, first_name, last_name FROM customers WHERE first_name = ?", new Object[]{"Josh"},
//                (rs, rowNum) -> new Customer(rs.getLong("id"), rs.getString("first_name"), rs.getString("last_name"))
//        );
    }
}

• 说明
  官网的例子中，jdbcTemplate的一个回调 接口 RowMapper使用了lambda 语法,java8是支持的，这里把它转为普通的语法。

spring-boot 和mysql的接入

在上面工程的基础上,增加mysql的包，然后增加下配置文件即可
* pom.xml的h2db的配置注释掉

       <dependency>
           <groupId>org.springframework.boot</groupId>
           <artifactId>spring-boot-starter-jdbc</artifactId>
       </dependency>
<!--       <dependency>
           <groupId>com.h2database</groupId>
           <artifactId>h2</artifactId>
       </dependency>-->

       <dependency>
           <groupId>mysql</groupId>
           <artifactId>mysql-connector-java</artifactId>
       </dependency>

• 增加配置文件
  把mysql的配置加上,application.yml改为,datasource 为使用对象

#配置jsp
spring:
  mvc:
    view:
      prefix: /
      suffix: .jsp
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql:/xxxxx:3306/xxx
    username: xxx
    password: xxx

上面的datasource配置也可以用application.properties来配置

spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://114.215.138.13:3306/xxdb
spring.datasource.username=xuxing
spring.datasource.password=xuxing

• 建表语句

CREATE TABLE customers(id SERIAL, first_name VARCHAR(255), last_name VARCHAR(255));

profiles的使用

一个项目有多个环境是 还是很常见的,这里分dev和prod环境的话，数据库配置也要不一样

• 增加application-dev.properties,加上dev的 datasrouce 配置

spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://xxxxx:3306/xxxx_dev
spring.datasource.username=xxx
spring.datasource.password=xxx

• 增加application-prod.properties,加上prod的 datasrouce 配置

spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://xxxxx:3306/xxxx_prod
spring.datasource.username=xxx
spring.datasource.password=xxx

• application.properties
  datasource配置去掉
  增加

spring.profiles.active=dev

spring.profiles.active 根据dev或是prod来切换

代码

https://github.com/huawumingguo/springbootsample/tree/master/cjdbc