学习：记录Spring-JDBC的简单使用

环境搭建:

在SpringBoot中使用Spring-JDBC。首先搭好SpringBoot， 1. pom.xml中的依赖

<dependencies> <!--springBoot的web启动器--> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> <version>1.5.8.RELEASE</version> </dependency> <!--阿里巴巴druid依赖--> <dependency> <groupId>com.alibaba.druid</groupId> <artifactId>druid-wrapper</artifactId> <version>0.2.8</version> </dependency> <!--spring的orm框架 spring-jdbc--> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>3.2.8.RELEASE</version> </dependency> <!--mysql数据驱动--> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.48</version> </dependency> <!--需要spring的事务相关依赖 spring-tx --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-tx</artifactId> <version>5.2.9.RELEASE</version> </dependency> </dependencies> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.3.4.RELEASE</version> <relativePath/> </parent>

其中spring-boot-starter-parent，通过继承此父级maven项目来获得一些基本的配置

parent的作用

2. springBoot启动类(项目父包下)

@SpringBootApplication public class SpringBootJDBCApplication { public static void main(String[] args) { SpringApplication.run(SpringBootJDBCApplication.class,args); } }

3. 配置文件 采用 application.yml

server: port: 8090 spring: datasource: driver-class-name: com.mysql.jdbc.Driver username: root password: root url: jdbc:mysql://localhost:3306/test?useSSL=false&characterEncoding=utf8&serverTimezone=GMT type: com.alibaba.druid.pool.DruidDataSource testOnBorrow: false testWhileIdle: true validationQuery: select 1

配置类 ConfigBean 用德鲁伊数据源替换默认的数据源

@Configuration public class ConfigBean { @ConfigurationProperties(prefix = "spring.datasource") @Bean public DataSource druidDataSource(){ return new DruidDataSource(); } }

---

代码编写:

1. dao层 dao的实现类，通过NamedParameterJdbcTemplate完成与数据库的交互。使用方法类似QueryRunner

@Repository public class AccountDaoImpl implements AccountDao { @Autowired private NamedParameterJdbcTemplate jdbcTemplate; @Override public int addAccount(Account account) { String sql = "insert into account(id,money) values(:id,:money)"; Map<String,Object> map = new HashMap<>(); map.put("id",account.getId()); map.put("money",account.getMoney()); return jdbcTemplate.update(sql,map); } @Override public int delete(Account account){ String sql = "delete from account where id = :id"; Map<String,Object> map = new HashMap<>(); map.put("id",account.getId()); return jdbcTemplate.update(sql,map); } @Override public int update(Account account){ String sql = "update account set money = :money where id = :id"; Map<String,Object> map = new HashMap<>(); map.put("money",account.getMoney()); map.put("id",account.getId()); return jdbcTemplate.update(sql,map); } @Override public List<Account> findAccount() { String sql = "select * from account"; return jdbcTemplate.query(sql,new AccountMapper()); } }

值得注意的是： (1) 在执行查询语句时需要传入一个AccountMapper类型的对象，该类继承了org.springframework.jdbc.core.RowMapper<T> 接口，实现mapRow方法，在该方法中完成对象的orm映射，类似QueryRunner的反射机制实现映射 AccountMapper

public class AccountMapper implements RowMapper<Account> { @Override public Account mapRow(ResultSet resultSet, int i) throws SQLException { int id = resultSet.getInt("id"); int money = resultSet.getInt("money"); Account account = new Account(); account.setId(id); account.setMoney(money); return account; } }

(2)在Spring-JDBC中，为防止sql注入问题使用占位符，与QueryRunner的占位符 " ? " 不同，Spring-JDBC使用了语义化更强的占位符 " :xxx "

String sql = "insert into account(id,money) values(:id,:money)";

而后，通过传递一个Map<String,Object>集合的方式，将占位符与参数对应起来。

controller和service层不是重点，省略。

以上代码，经测试可用