03. Spring JDBC
1、简介
Spring是个一站式框架:Spring 自身也提供了控制层的 SpringMVC 和 持久层的 Spring JdbcTemplate。
2、开发步骤
2.1 在pom.xml文件中添加Maven包
(1)下载Spring JdbcTemplate的jar包
<!-- spring-jdbc -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.2.2.RELEASE</version>
</dependency>
(2)导入阿里数据源
<!-- 阿里数据源 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.10</version>
</dependency>
(3)导入mysql驱动包
<!-- mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.20</version>
</dependency>
</dependencies>
2.2 在application.xml配置文件中
(1)导入事先写好的jdbc配置文件jdbc.properties。
<!-- 导入jdbc配置文件 -->
<context:property-placeholder location="jdbc.properties"/>
jdbc.properties文件内容格式如下
url=jdbc:mysql://127.0.0.1:3306/shop?characterEncoding=utf-8&serverTimezone=GMT
driver=com.mysql.cj.jdbc.Driver
jdbc_username=root
jdbc_password=123456
initialSize=10
minIdle=5
maxActive=20
(2)创建JdbcTemplate
<!-- spring管理阿里数据源对象的创建 ,由此数据源管理数据库管理对象-->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
<property name="driverClassName" value="${driver}"></property>
<property name="url" value="${url}"></property>
<property name="username" value="${jdbc_username}"></property>
<property name="password" value="${jdbc_password}"></property>
<property name="initialSize" value="${initialSize}"></property>
<property name="minIdle" value="${minIdle}"></property>
<property name="maxActive" value="${maxActive}"></property>
</bean>
(3)配置jdbc模板
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
2.3 如何使用 JdbcTemplate 对象
在类中获得 JdbcTemplate 对象,就可以直接使用。JdbcTemplate 可以直接当作已有的类来创建对象。
@Autowired
private JdbcTemplate jdbcTemplate;
3、JdbcTemplate中常用的方法
3.1 execute()
execute():无返回值,可执行ddl,增删改语句
public void createTable() {
// 利用execute执行sql语句
jdbcTemplate.execute("create table t_test(id int(2) PRIMARY KEY AUTO_INCREMENT, name varchar(255),age int(2))");
}
3.2 update()
update():执行新增、修改、删除语句;
public void insert() {
jdbcTemplate.update("insert into t_test(name,age) values(?,?)", "张三", 18);
}
public void update() {
jdbcTemplate.update("update t_test set name = ? ,age = ? where id = ?", "李四", 20, 2);
}
public void delete() {
jdbcTemplate.update("delete from t_test where id = ?", 3);
}
3.3 queryForXXX()
queryForXXX():执行查询相关语句;
方法一:通过RowMapper将结果集封装成UserPo对象
public <T> List<T> query(String sql, RowMapper<T> rowMapper, @Nullable Object… args) throws DataAccessException {}
public void findById() {
List<UserPo> userList = jdbcTemplate.query("select * from t_test where id = ?", new RowMapper() {
@Override //将查询结果封装到UserPo对象中
public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
UserPo userPo = new UserPo();
userPo.setId(rs.getInt("id"));
userPo.setName(rs.getString("name"));
userPo.setAge(rs.getInt("age"));
return userPo;
}
}, 1);
for (UserPo userPo : userList) {
System.out.println(userPo);
}
}
方法二:直接获取封装成列名映射map对象
public List<Map<String, Object>> queryForList(String sql, @Nullable Object… args) throws DataAccessException{}
public void findById() {
List<Map<String, Object>> list = jdbcTemplate.queryForList("select * from t_test where id = ?", 1);
for (Map<String, Object> map : list) {
System.out.println(map);
}
}
方法三:获取单个对象
public Map<String, Object> queryForMap(String sql, @Nullable Object… args) throws DataAccessException {}
public void findById() {
List<Map<String, Object>> list = jdbcTemplate.queryForList("select * from t_test where id = ?", 1);
for (Map<String, Object> map : list) {
System.out.println(map);
}
}
方法四:直接返回UserPo对象
public <T> T queryForObject(String sql, RowMapper<T> rowMapper, @Nullable Object… args) throws DataAccessException {}
public void findById() {
UserPo userPo = jdbcTemplate.queryForObject("select * from t_test where id = ?", new RowMapper<UserPo>() {
@Override //将查询结果封装到UserPo对象中
public UserPo mapRow(ResultSet rs, int rowNum) throws SQLException {
UserPo userPo = new UserPo();
userPo.setId(rs.getInt("id"));
userPo.setName(rs.getString("name"));
userPo.setAge(rs.getInt("age"));
return userPo;
}
}, 1);
System.out.println(userPo);
}