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的内容

@Servicepublic 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 为使用对象

#配置jspspring:  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.Driverspring.datasource.url=jdbc:mysql://114.215.138.13:3306/xxdbspring.datasource.username=xuxingspring.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.Driverspring.datasource.url=jdbc:mysql://xxxxx:3306/xxxx_devspring.datasource.username=xxxspring.datasource.password=xxx

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

spring.datasource.driver-class-name=com.mysql.jdbc.Driverspring.datasource.url=jdbc:mysql://xxxxx:3306/xxxx_prodspring.datasource.username=xxxspring.datasource.password=xxx

• application.properties
  datasource配置去掉
  增加

spring.profiles.active=dev

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

代码

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