Spring JDBC

1、JDBC

一文学会Spring JDBC 使用-LMLPHP

添加依赖

<properties>
        <maven.compiler.source>17</maven.compiler.source>
        <maven.compiler.target>17</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <spring.version>5.3.21</spring.version>
        <mysql.version>8.0.30</mysql.version>
</properties>

<dependencies>
    	<!--核心依赖-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
        </dependency>
		<!--spring-jdbc-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
        </dependency>
		<!--数据库依赖-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

    	<!--测试-->
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter-api</artifactId>
            <version>5.8.2</version>
        </dependency>

    	<!--日志依赖-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.24</version>
        </dependency>
     	<dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>1.7.25</version>
        </dependency>
		<!--日志依赖-->
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-log4j12</artifactId>
            <version>1.7.25</version>
        </dependency>
    </dependencies>

log4j.properties 日志配置文件（当启动程序，没有任何报错，但是没有信息打印时，需要配置日志）

#将等级为DEBUG的日志信息输出到console和file这两个目的地，console和file的定义在下面的代码
log4j.rootLogger=DEBUG,console,file

#控制台输出的相关设置
log4j.appender.console=org.apache.log4j.ConsoleAppender
log4j.appender.console.Threshold=DEBUG
log4j.appender.console.ImmediateFlush=true
log4j.appender.console.Target=System.err
log4j.appender.console.layout=org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=[%-5p] %d(%r) --> [%t] %l: %m %x %n


#文件输出的相关设置
log4j.appender.file = org.apache.log4j.RollingFileAppender
log4j.appender.file.File=./log/logFile.log
log4j.appender.file.MaxFileSize=10mb
log4j.appender.file.Threshold=DEBUG
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=[%p][%d{yy-MM-dd}][%c]%m%n

#日志输出级别
log4j.logger.org.mybatis=DEBUG
log4j.logger.java.sql=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.ResultSet=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG

2、使用

2.1、配置数据源：

2.1.1、注册数据源对像

创建数据源的配置类：（基于配置类的方式）

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import javax.sql.DataSource;
@Configuration
public class DataSourceConfig {
    @Bean
    public DataSource dataSource() {
        // 驱动数据源
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        // 加载驱动程序
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/yootk");
        dataSource.setUsername("root");
        dataSource.setPassword("317311");
        return dataSource;
    }
}

创建数据源的配置类：（基于xml的方式）

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:p="http://www.springframework.org/schema/p"
       xmlns:c="http://www.springframework.org/schema/c"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
    http://www.springframework.org/schema/beans/spring-beans.xsd">

    <!--数据源的配置-->
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/yootk"/>
        <property name="username" value="root"/>
        <property name="password" value="317311"/>
    </bean>
</beans>

2.1.2、测试：

import look.word.jdbc.config.DataSourceConfig;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.extension.ExtendWith;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit.jupiter.SpringExtension;
import javax.sql.DataSource;

@ContextConfiguration(classes = DataSourceConfig.class) //	两者二选一即可
//@ContextConfiguration(locations ={"classpath:data-source.xml"})
@ExtendWith(SpringExtension.class)
public class TestDataSource {
	// 日志工厂对象
    private static final Logger LOGGER = LoggerFactory.getLogger(TestDataSource.class);
    @Autowired
    private DataSource dataSource;
    @Test
    public void testConnection() throws Exception{
        LOGGER.info("【数据库连接对象】：{}",dataSource);
    }
}

// 执行结果 输入数据源对象，说明连接成功
// [INFO ] 2022-09-14 12:18:59,307(386) --> [main] look.word.test.TestDataSource.testConnection(TestDataSource.java:29): 【数据库连接对象】：org.springframework.jdbc.datasource.DriverManagerDataSource@535779e4

一文学会Spring JDBC 使用-LMLPHP

2.1.3、默认连接方式的缺点

2.2、HikariCP

宇节码更加的精简，这样可以在缓存中添加更多的程序代码;
实现了一个无锁集合，减少了并发访问造成的资源竞争问题;
使用了自定义数组类型(FastList)代替了ArrayList，提高了get()与remove()的操作性能;
针对CPU的时间片算法进行了优化，尽可能在一个时间片内完成所有处理操作。

2.2.1、使用

添加依赖：

			<dependency>
                <groupId>com.zaxxer</groupId>
                <artifactId>HikariCP</artifactId>
                <version>5.0.1</version>
            </dependency>

编写配置类：

一文学会Spring JDBC 使用-LMLPHP

yootk.database.driverClassName=com.mysql.cj.jdbc.Driver
yootk.database.jdbcUrl=jdbc:mysql://localhost:3306/yootk
yootk.database.username=root
yootk.database.password=317311
# 【Hikaricp】配置数据库连接超时时间 单位【毫秒】
yootk.database.connectionTimeOut=3000
# 【Hikaricp】一个连接最小维持的时间 单位【毫秒】
yootk.database.idleTimeOut=3000
# 【Hikaricp】一个连接最长存活的时间 单位【毫秒】
yootk.database.maxLifetime=6000
# 【Hikaricp】最大保存的数据库连接实例
yootk.database.maximumPoolSize=60
# 【Hikaricp】最小保存的数据库连接实例 (在没有任何用户访问时，最少维持的连接数量)
yootk.database.minimumIdle=20
# 【Hikaricp】是否为只读
yootk.database.readOnly=false

创建配置对象

@Configuration
//读取指定位置的资源文件
@PropertySource("classpath:config/database.properties")
public class HikariCpDataSourceConfig {
    /**
     * 绑定资源文件中的配置数据项
     */
    @Value("${yootk.database.driverClassName}")
    private String driverClassName;
    @Value("${yootk.database.jdbcUrl}")
    private String jdbcUrl;
    @Value("${yootk.database.username}")
    private String username;
    @Value("${yootk.database.password}")
    private String password;
    @Value("${yootk.database.connectionTimeOut}")
    private Long connectionTimeOut;
    @Value("${yootk.database.idleTimeOut}")
    private Long idleTimeOut;
    @Value("${yootk.database.maxLifetime}")
    private Long maxLifetime;
    @Value("${yootk.database.maximumPoolSize}")
    private Integer maximumPoolSize;
    @Value("${yootk.database.minimumIdle}")
    private Integer minimumIdle;
    @Value("${yootk.database.readOnly}")
    private boolean readOnly;

    @Bean("dataSource")
    public DataSource dataSource() {
        // Hikari连接池数据源
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setDriverClassName(driverClassName);
        dataSource.setJdbcUrl(jdbcUrl);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        dataSource.setPassword(password);
        // 超时时间
        dataSource.setConnectionTimeout(connectionTimeOut);
        // 空闲超时
        dataSource.setIdleTimeout(idleTimeOut);
        // 连接的最长时间
        dataSource.setMaxLifetime(maxLifetime);
        // 连接池最大数量
        dataSource.setMaximumPoolSize(maximumPoolSize);
        // 当没有连接时 最小保留的连接数量
        dataSource.setMinimumIdle(minimumIdle);
        // 是否只读数据库
        dataSource.setReadOnly(readOnly);
        return dataSource;
    }
}

测试类：

import look.word.jdbc.config.HikariCpDataSourceConfig;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.extension.ExtendWith;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit.jupiter.SpringExtension;

import javax.sql.DataSource;
@ContextConfiguration(classes = HikariCpDataSourceConfig.class)
@ExtendWith(SpringExtension.class)
public class TestDataSource {
    private static final Logger LOGGER = LoggerFactory.getLogger(TestDataSource.class);

    @Autowired
    private DataSource dataSource;

    @Test
    public void testConnection() throws Exception {
        LOGGER.info("【数据库连接对象】：{}", dataSource.getConnection());
    }
}

2.3、JdbcTempLate

2.3.1、增

添加配置类：

@Configuration
public class JdbcTempLateConfig {
    @Bean // 方法形参 会自动从容器中注入对象
    public JdbcTemplate jdbcTemplate(DataSource dataSource) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        return jdbcTemplate;
    }
}

编写测试类：

@ContextConfiguration(classes = {HikariCpDataSourceConfig.class, JdbcTempLateConfig.class})
@ExtendWith(SpringExtension.class)
public class TestJdbcTempLate {
    private static final Logger LOGGER = LoggerFactory.getLogger(TestJdbcTempLate.class);

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Test
    public void testConnection() throws Exception {
        String sql = "insert into book(title,author,price) values('java入门','李老师',99.90)";
        LOGGER.info("【插入执行结果】：{}", jdbcTemplate.update(sql));
    }
}

执行结果：

一文学会Spring JDBC 使用-LMLPHP

2.3.2、改

测试类

   @Test
    public void testUpdate() {
        String sql = "update yootk.book set title = ? where bid = ?";
        LOGGER.info("【插入执行结果】：{}", jdbcTemplate.update(sql, "Python入门", 2));
    }

2.3.3、删

测试类

    @Test
    public void testDelete() {
        String sql = "delete from yootk.book  where bid = ?";
        LOGGER.info("【插入执行结果】：{}", jdbcTemplate.update(sql, 2));
    }

2.3.4、增 (返回id)

测试类

    @Test
    public void testInsertReturnId() {
        String sql = "insert into yootk.book(title,author,price) values(?,?,?)";
        GeneratedKeyHolder keyHolder = new GeneratedKeyHolder(); // 获取KEY的处理信息
        int count = jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); // 对sql进行预处理
                ps.setString(1, "Springboot实战");
                ps.setString(2, "老李");
                ps.setDouble(3, 99.00);
                return ps;
            }
        }, keyHolder);
        LOGGER.info("【插入执行影响行数】：{}，当前插入数据的ID：{}", count, keyHolder.getKey());
    }
// 执行结果
// look.word.test.TestJdbcTempLate.testInsertReturnId(TestJdbcTempLate.java:61): 【插入执行影响行数】：1，当前插入数据的ID：4

2.3.5、批处理

一文学会Spring JDBC 使用-LMLPHP

测试类：

	@Test
    public void testInsertBatch() {
        List<String> titles = List.of("Springboot开发实战", "SSM开发案例", "Netty开发实战", "Redis开发实战");
        List<Double> prices = List.of(90.1, 98.9, 78.9, 98.9);
        String sql = "insert into yootk.book(title,author,price) values(?,?,?)";
        this.jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {  // 执行批量插入
             //@param i  集合索引
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setString(1, titles.get(i));
                ps.setString(2, "老李老师");
                ps.setDouble(3, prices.get(i));
            }
            @Override
            public int getBatchSize() {
                return titles.size(); //总长度
            }
        });
    }

@Test
    public void testInsertBatch2() {
        List<Object[]> params = List.of(
                new Object[]{"Spring开发实战", "11", 89.0},
                new Object[]{"Spring开发实战1", "11", 89.0},
                new Object[]{"Spring开发实战2", "11", 89.0},
                new Object[]{"Spring开发实战3", "11", 89.0}
        );
        String sql = "insert into yootk.book(title,author,price) values(?,?,?)";
        int[] result = jdbcTemplate.batchUpdate(sql, params);//批量插入
        System.out.println("result = " + result);
    }

2.3.4、查

一文学会Spring JDBC 使用-LMLPHP

查询单个

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Book {
    private Integer bid;
    private String title;
    private String author;
    private Double price;
}

测试类：

    // 查询单个
    @Test
    public void testQuery() {
        String sql = "select  bid, title, author, price from yootk.book  where bid = ?";
        Book book = jdbcTemplate.queryForObject(sql, new RowMapper<Book>() {
            @Override
            public Book mapRow(ResultSet rs, int rowNum) throws SQLException {
                Book book = new Book();
                book.setBid(rs.getInt(1));
                book.setTitle(rs.getString(2));
                book.setAuthor(rs.getString(3));
                book.setPrice(rs.getDouble(4));
                return book;
            }
        }, 3); // 这里的3 是对预处理数据的回填 多个需按照顺序编写
        System.out.println("【queryForObject 查询结果】book = " + book);
    }

查询多个

    // 查询所有
    @Test
    public void testQueryAll() {
        String sql = "select  bid, title, author, price from yootk.book ";
        List<Book> list = jdbcTemplate.query(sql, new RowMapper<Book>() {
            @Override
            public Book mapRow(ResultSet rs, int rowNum) throws SQLException {
                Book book = new Book();
                book.setBid(rs.getInt(1));
                book.setTitle(rs.getString(2));
                book.setAuthor(rs.getString(3));
                book.setPrice(rs.getDouble(4));
                return book;
            }
        });
        list.stream().forEach(System.out::println);
    }

分页查询

    // 分页
    @Test
    public void testQuerySpAll() {
        int current = 2; // 页数
        int size = 5;// 每页数量
        String sql = "select  bid, title, author, price from yootk.book limit ? ,?  ";
        List<Book> list = jdbcTemplate.query(sql, new RowMapper<Book>() {
            @Override
            public Book mapRow(ResultSet rs, int rowNum) throws SQLException {
                Book book = new Book();
                book.setBid(rs.getInt(1));
                book.setTitle(rs.getString(2));
                book.setAuthor(rs.getString(3));
                book.setPrice(rs.getDouble(4));
                return book;
            }
        }, (current - 1) * size, size);
        list.stream().forEach(System.out::println);
    }

统计行数

    // 查询行数
    @Test
    public void testQueryCount() {
        String sql = "select  count(*) from yootk.book where title like ?";
        long count = jdbcTemplate.queryForObject(sql, new RowMapper<Long>() {
            @Override
            public Long mapRow(ResultSet rs, int rowNum) throws SQLException {
                return rs.getLong(1);
            }
        }, "%Spring%");
        LOGGER.info("【数据库记录总行数】{}", count);
    }

look-word

一文学会Spring JDBC 使用

Spring JDBC

1、JDBC

2、使用

2.1、配置数据源：

2.1.1、注册数据源对像

2.1.2、测试：

2.1.3、默认连接方式的缺点

2.2、HikariCP

2.2.1、使用

2.3、JdbcTempLate

2.3.1、增

2.3.2、改

2.3.3、删

2.3.4、增 (返回id)

2.3.5、批处理

2.3.4、查

查询单个

查询多个

分页查询

统计行数