Spring boot + jdbc学习笔记

pom.xml:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.my.app</groupId>
    <artifactId>spring-boot-jdbc-demo</artifactId>
    <packaging>war</packaging>
    <version>1.0-SNAPSHOT</version>
    <name>spring-boot-test Maven Webapp</name>
    <url>http://maven.apache.org</url>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <!-- Inherit defaults from Spring Boot -->
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.4.1.BUILD-SNAPSHOT</version>
    </parent>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <!-- MYSQL -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>org.apache.tomcat</groupId>
            <artifactId>tomcat-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
        </dependency>

        <!-- gson -->
        <dependency>
            <groupId>com.google.code.gson</groupId>
            <artifactId>gson</artifactId>
            </dependency>
        <!-- guava -->
        <dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>16.0.1</version>
        </dependency>
    </dependencies>

    <build>
        <finalName>spring-boot-jdbc-demo</finalName>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <dependencies>
                    <dependency>
                        <groupId>org.springframework</groupId>
                        <artifactId>springloaded</artifactId>
                        <version>1.2.6.RELEASE</version>
                    </dependency>
                </dependencies>
            </plugin>
        </plugins>
    </build>

    <!-- Add Spring repositories -->
    <!-- (you don't need this if you are using a .RELEASE version) -->
    <repositories>
        <repository>
            <id>spring-snapshots</id>
            <url>http://repo.spring.io/snapshot</url>
            <snapshots><enabled>true</enabled></snapshots>
        </repository>
        <repository>
            <id>spring-milestones</id>
            <url>http://repo.spring.io/milestone</url>
        </repository>
    </repositories>
    <pluginRepositories>
        <pluginRepository>
            <id>spring-snapshots</id>
            <url>http://repo.spring.io/snapshot</url>
        </pluginRepository>
        <pluginRepository>
            <id>spring-milestones</id>
            <url>http://repo.spring.io/milestone</url>
        </pluginRepository>
    </pluginRepositories>
</project>

这里我们需要star：spring-boot-starter-jdbc，这样才能使用jdbc
因为使用的mysql，所以需要依赖：mysql
jdbc还需要：tomcat-jdbc，spring-jdbc

下面插件springloaded的作用是用来热部署的，当我们修改文件，是热部署了的。但是如果新增或者修改了配置文件，是不会热部署的。

gson和guava是工具类。

下面我们需要jdbc的配置文件：
application.properties:

spring.datasource.url=jdbc:mysql://localhost:3306/spring_boot_test
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

daoImpl:

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import com.google.common.collect.Lists;
import com.my.app.dao.StudentDao;
import com.my.app.domain.Student;

@Repository
public class StudentDaoImpl extends BasicDaoImpl implements StudentDao{

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * java8 save
     */
    @Override
    public int save(String name) {
        String sql = "insert into student (name) values (?)";
        KeyHolder key = new GeneratedKeyHolder();
        this.jdbcTemplate.update(con -> {
            PreparedStatement preState =null;
            try {
                preState = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                preState.setString(1, name);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return preState;
        }, key);
        return key.getKey().intValue();
    }

    /**
     * java8 query
     */
    @Override
    public Student findById(int id) {
        String sql = "select * from student where id = ? ";
        List<Student> students = jdbcTemplate.query(sql, new Object[] {id}, (rs, rowNum)->{
            return this.initStudent(rs);
        });
        return students != null && !students.isEmpty() ? students.get(0) : null;
    }

    /**
     * 非java8的 query
     */
    @Override
    public Student findByName(String name){
        String sql = "select * from student where name = ?";
        List<Map<String, Object>> studentList = jdbcTemplate.queryForList(sql, new Object[]{name});
        List<Student> students = Lists.newArrayList(); 
        for (Map<String, Object> stuMap : studentList) {
            Student student = initStudent(stuMap);
            students.add(student);
        }
        return students != null && !students.isEmpty() ? students.get(0) : null;
    }

    private Student initStudent(Map<String, Object> stuMap) {
        if(stuMap==null){
            return null;
        }
        Student s = new Student();
        s.setId(converInt(stuMap.get("id")));
        s.setName(String.valueOf(stuMap.get("name")));
        return s;
    }

    private Student initStudent(ResultSet rs) throws SQLException {
        Student student = new Student();
        student.setId(rs.getInt("id"));
        student.setName(rs.getString("name"));
        return student;
    }

}

具体项目代码，git：
https://github.com/sunhuwh/spring-boot-jdbc-demo