使用 Java 连接数据库的几个步骤：

• 创建全局DataSource实例，表示数据库连接池；
• 在需要读写数据库的方法内部，按如下步骤访问数据库：
  • 从全局DataSource实例获取Connection实例；
  • 通过Connection实例创建PreparedStatement实例；
  • 执行SQL语句，如果是查询，则通过ResultSet读取结果集，如果是修改，则获得int结果

在Spring使用JDBC

1. 我们通过IoC容器创建并管理一个DataSource实例
2. Spring提供了一个JdbcTemplate，可以方便地让我们操作JDBC，因此，通常情况下，我们会实例化一个JdbcTemplate。

package com.spring.jdbctest;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import javax.sql.DataSource;
/*
*  1. 配置文件读取配置
*  2. 创建一个 DataSource 实例。 其中涉及 url / userName / password / autoCommit / idleTimeout / connectionTimeout 
*  3. 创建 JdbcTemplate 实例来操作 SQL 语句
* */
@Component
public class AppConfig {
    @Value("${jdbc.url}")
    String jdbcUrl;
    @Value("${jdbc.username}")
    String jdbcUserName;
    @Value("${jdbc.password}")
    String jdbcPassword;
    @Bean
    DataSource createDataSource(){
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(jdbcUrl);
        config.setUsername(jdbcUserName);
        config.setPassword(jdbcPassword);
        config.addDataSourceProperty("autoCommit","true");
        config.addDataSourceProperty("idelTimeout","5");
        config.addDataSourceProperty("connectionTimeout","30");
        return new HikariDataSource(config);
    }
    @Bean
    JdbcTemplate jdbcTemplate(DataSource dataSource){
        return new JdbcTemplate(dataSource);
    }
}

JdbcTemplate 的四种用法

分别是：

1. execute((Connection conn) - > {})
2. execute(sql, (PrepareStatement pre) - >{} )
3. quertForObject(sql, new Object[]{ params1, params2}, (ResultSet rs, int rowNum) -> {} )
4. query(sql, new Object[]{params1, params2 })

execute((Connection conn) - > {})

public User getUserById(long id){
    return jdbcTemplate.execute((Connection conn) -> {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try{
            preparedStatement = conn.prepareStatement("SELECT * FROM USER WHERE id = ?");
            preparedStatement.setObject(1,id);
            resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                return new User(
                    resultSet.getLong("id"),
                    resultSet.getString("name"),
                    resultSet.getString("email")
                );
            }

        }finally {
            preparedStatement.close();
            resultSet.close();
        }
        throw new RuntimeException("user not found by id.");
    });
}

execute(sql, (PrepareStatement pre) - >{} )

public User getUserByName(String name){
    return jdbcTemplate.execute("",(PreparedStatement preparedStatement) -> {
        preparedStatement.setObject(1,name);
        ResultSet resultSet = null;
        try{
            resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                return new User(
                    resultSet.getLong("id"),
                    resultSet.getString("name"),
                    resultSet.getString("email")
                );
            }
        }finally {
            preparedStatement.close();
            resultSet.close();
        }
        throw new RuntimeException("User not found by name");
    });
}

quertForObject(sql, new Object[]{ params1, params2}, (ResultSet rs, int rowNum) -> {} )

public User getUserByEmail(String email){
    return jdbcTemplate.queryForObject("SELECT * FROM USER WHERE EMAIL=?", new Object[]{email},
                                       (ResultSet resultSet, int rowNum)->{
                                           return new User(
                                               resultSet.getLong("id"),
                                               resultSet.getString("name"),
                                               resultSet.getString(email)
                                           );
                                       });
}

query(sql, new Object[]{params1, params2 })

public List<User> getUsers(int indexPage){
    int limit = 100;
    int offset = limit * (indexPage - 1);

    return jdbcTemplate.query("SELECT * FROM users LIMIT ? OFFSET ?",
                              new Object[]{limit,offset},
                              new BeanPropertyRowMapper<>(User.class));
}

update

public void updateUser(User user){
        if (1 != jdbcTemplate.update("UPDATE USER SET name = ? WHERE id = ?",
                                     user.getUserName(),user.getId())){
            throw  new RuntimeException("User not found by Id");
        }
    }

keyHolder

public User register(String name, String email, String password){
    KeyHolder keyHolder = new GeneratedKeyHolder();
    if(1 != jdbcTemplate.update(
        (Connection conn) -> {
            PreparedStatement prepareStatement = conn.prepareStatement(
                "INSERT INTO user(name,email,password) SET VALUE(?,?,?)", Statement.RETURN_GENERATED_KEYS
            );
            prepareStatement.setObject(1,name);
            prepareStatement.setObject(2,email);
            prepareStatement.setObject(3,password);

            return prepareStatement;
        },
        //
        keyHolder
    )){
        throw new RuntimeException("");
    }

    return new User(keyHolder.getKey().longValue(),name,email);
}