JDBC 连接数据库

private static String url = "jdbc:mysql://localhost:3306/database?useSSL=false";
private static String username = "user01";
private static String password = "pass01";

public static void testMySQL() throws Exception {
  // 加载驱动
  Class.forName("com.mysql.jdbc.Driver");

  // 创建连接
  Connection conn = DriverManager.getConnection(url, username, password);

  // 发送 SQL 语句
  Statement stmt = conn.createStatement();
  ResultSet rs = stmt.executeQuery("SELECT 'Hello'");
  while (rs.next()) {
    // rs.getObject(1);
    String message = rs.getString(1);
    System.out.println(message);
  }

  // 关闭连接
  conn.close();
}

package io.github.jeanhwea.app09_jdbc.beans;

import javax.sql.DataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

@Configuration
public class DataSourceConfig {

  @Bean
  public DataSource getDataSource() {
    DriverManagerDataSource dataSource = new DriverManagerDataSource();
    dataSource.setDriverClassName("com.mysql.jdbc.Driver");
    dataSource.setUrl("jdbc:mysql://localhost:3306/db01");
    dataSource.setUsername("user01");
    dataSource.setPassword("pass01");
    return dataSource;
  }
}

package io.github.jeanhwea.app09_jdbc.beans;

public class Employee {
  private Long id;
  private String name;

  public Employee(Long id, String name) {
    this.id = id;
    this.name = name;
  }

    @Override
    public String toString() {
        return "Employee[" + this.id +"]";
    }
}

package io.github.jeanhwea.app09_jdbc.beans;

import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;

public class EmployeeRowMapper implements RowMapper<Employee> {
  @Override
  public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
    Employee empl = new Employee(rs.getLong("G_ID"), rs.getString("EMPL_NAME"));
    return empl;
  }
}

////////////////////////////////////////////////////////////////////////////////
// 服务接口
////////////////////////////////////////////////////////////////////////////////
package io.github.jeanhwea.app09_jdbc.beans;

import java.util.List;
import org.springframework.stereotype.Component;

@Component
public interface EmployeeService {
  List<Employee> getAll();
}

////////////////////////////////////////////////////////////////////////////////
// 实现类
////////////////////////////////////////////////////////////////////////////////
package io.github.jeanhwea.app09_jdbc.beans;

import java.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

@Component
public class EmployeeServiceImpl implements EmployeeService {

  private JdbcTemplate jdbcTemplate;

  public void setDataSource(DataSource dataSource) {
    this.jdbcTemplate = new JdbcTemplate(dataSource);
  }

  public EmployeeServiceImpl(DataSource dataSource) {
    this.setDataSource(dataSource);
  }

  @Override
  @SuppressWarnings("unchecked")
  public List<Employee> getAll() {
    List<Employee> employees =
        jdbcTemplate.query("select * from TB_BASE_EMPL", new EmployeeRowMapper());
    return employees;
  }
}

package io.github.jeanhwea.app09_jdbc;

import io.github.jeanhwea.app09_jdbc.beans.Employee;
import io.github.jeanhwea.app09_jdbc.beans.EmployeeService;
import java.util.List;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;

public class MyApp09 {

  public static void main(String[] args) {
    String pkgname = MyApp09.class.getPackage().getName();
    System.out.println(pkgname);
    AnnotationConfigApplicationContext ctx = new AnnotationConfigApplicationContext(pkgname);
    EmployeeService employeeService = ctx.getBean("employeeServiceImpl", EmployeeService.class);
    List<Employee> employeeList = employeeService.getAll();
    System.out.println(employeeList);
  }
}

org.springframework.jdbc.core
Class JdbcTemplate

    java.lang.Object
        org.springframework.jdbc.support.JdbcAccessor
            org.springframework.jdbc.core.JdbcTemplate

    All Implemented Interfaces:
        org.springframework.beans.factory.InitializingBean, JdbcOperations

doGetConnection:104, DataSourceUtils (org.springframework.jdbc.datasource)
getConnection:79, DataSourceUtils (org.springframework.jdbc.datasource)
execute:371, JdbcTemplate (org.springframework.jdbc.core)
query:452, JdbcTemplate (org.springframework.jdbc.core)
query:462, JdbcTemplate (org.springframework.jdbc.core)
getAll:25, EmployeeServiceImpl (io.github.jeanhwea.app09_jdbc.beans)
main:15, MyApp09 (io.github.jeanhwea.app09_jdbc)

/**
 * Actually obtain a JDBC Connection from the given DataSource.
 * Same as {@link #getConnection}, but throwing the original SQLException.
 * <p>Is aware of a corresponding Connection bound to the current thread, for example
 * when using {@link DataSourceTransactionManager}. Will bind a Connection to the thread
 * if transaction synchronization is active (e.g. if in a JTA transaction).
 * <p>Directly accessed by {@link TransactionAwareDataSourceProxy}.
 * @param dataSource the DataSource to obtain Connections from
 * @return a JDBC Connection from the given DataSource
 * @throws SQLException if thrown by JDBC methods
 * @see #doReleaseConnection
 */
public static Connection doGetConnection(DataSource dataSource) throws SQLException {
  Assert.notNull(dataSource, "No DataSource specified");

  ConnectionHolder conHolder = (ConnectionHolder) TransactionSynchronizationManager.getResource(dataSource);
  if (conHolder != null && (conHolder.hasConnection() || conHolder.isSynchronizedWithTransaction())) {
    conHolder.requested();
    if (!conHolder.hasConnection()) {
      logger.debug("Fetching resumed JDBC Connection from DataSource");
      conHolder.setConnection(fetchConnection(dataSource));
    }
    return conHolder.getConnection();
  }
  // Else we either got no holder or an empty thread-bound holder here.

  logger.debug("Fetching JDBC Connection from DataSource");
  Connection con = fetchConnection(dataSource);

  // 当前线程是否支持同步
  if (TransactionSynchronizationManager.isSynchronizationActive()) {
    try {
      // Use same Connection for further JDBC actions within the transaction.
      // Thread-bound object will get removed by synchronization at transaction completion.
      // 在同一事务中，JDBC 的连接需要保持一致
      ConnectionHolder holderToUse = conHolder;
      if (holderToUse == null) {
        holderToUse = new ConnectionHolder(con);
      }
      else {
        holderToUse.setConnection(con);
      }
      // 这里记录数据连接的引用数量
      holderToUse.requested();
      TransactionSynchronizationManager.registerSynchronization(
                                                                new ConnectionSynchronization(holderToUse, dataSource));
      holderToUse.setSynchronizedWithTransaction(true);
      if (holderToUse != conHolder) {
        TransactionSynchronizationManager.bindResource(dataSource, holderToUse);
      }
    }
    catch (RuntimeException ex) {
      // Unexpected exception from external delegation call -> close Connection and rethrow.
      releaseConnection(con, dataSource);
      throw ex;
    }
  }

  return con;
}

//-------------------------------------------------------------------------
// Methods dealing with static SQL (java.sql.Statement)
//-------------------------------------------------------------------------

@Override
@Nullable
public <T> T execute(StatementCallback<T> action) throws DataAccessException {
  Assert.notNull(action, "Callback object must not be null");

  Connection con = DataSourceUtils.getConnection(obtainDataSource());
  Statement stmt = null;
  try {
    // 执行 SQL, 并返回结果
    stmt = con.createStatement();
    applyStatementSettings(stmt);
    T result = action.doInStatement(stmt);
    handleWarnings(stmt);
    return result;
  }
  catch (SQLException ex) {
    // Release Connection early, to avoid potential connection pool deadlock
    // in the case when the exception translator hasn't been initialized yet.
    String sql = getSql(action);
    JdbcUtils.closeStatement(stmt);
    stmt = null;
    DataSourceUtils.releaseConnection(con, getDataSource());
    con = null;
    throw translateException("StatementCallback", sql, ex);
  }
  finally {
    JdbcUtils.closeStatement(stmt);
    DataSourceUtils.releaseConnection(con, getDataSource());
  }
}

PreparedStatement pstmt = con.prepareStatement("UPDATE EMPLOYEES SET SALARY = ? WHERE ID = ?");
pstmt.setBigDecimal(1, 153833.00);
pstmt.setInt(2, 110592);

protected int update(final PreparedStatementCreator psc, @Nullable final PreparedStatementSetter pss)
  throws DataAccessException {

  logger.debug("Executing prepared SQL update");

  return updateCount(execute(psc, ps -> {
        try {
          if (pss != null) {
            // 设置 PreparedStatement 的实际值
            pss.setValues(ps);
          }
          // 调用更新语句
          int rows = ps.executeUpdate();
          if (logger.isTraceEnabled()) {
            logger.trace("SQL update affected " + rows + " rows");
          }
          return rows;
        }
        finally {
          if (pss instanceof ParameterDisposer) {
            ((ParameterDisposer) pss).cleanupParameters();
          }
        }
      }));
}