JdbcTemplate配置连接池
org.springframework.jdbc.core.JdbcTemplate
类方便执行SQL语句
public JdbcTemplate(DataSource dataSource)
创建JdbcTemplate对象,方便执行SQL语句
public void execute(final String sql)
execute可以执行所有SQL语句,因为没有返回值,一般用于执行DDL语句。
JdbcTemplate使用步骤
- 准备DruidDataSource连接池
- 导入依赖的jar包
spring-beans-4.1.2.RELEASE.jar
spring-core-4.1.2.RELEASE.jar
spring-jdbc-4.1.2.RELEASE.jar
spring-tx-4.1.2.RELEASE.jar
com.springsource.org.apache.commons.logging-1.1.1.jar
- 创建
JdbcTemplate
对象,传入Druid
连接池 - 调用
execute、update、queryXxx
等方法
示例:
public class Demo04 {
public static void main(String[] args) {
// 创建表的SQL语句
String sql = "CREATE TABLE product("
+ "pid INT PRIMARY KEY AUTO_INCREMENT,"
+ "pname VARCHAR(20),"
+ "price DOUBLE"
+ ");";
JdbcTemplate jdbcTemplate = new JdbcTemplate(DataSourceUtils.getDataSource());
jdbcTemplate.execute(sql);
}
}
JdbcTemplate实现增删改查
Spring bean配置
<?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:context="http://www.springframework.org/schema/context"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx.xsd">
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/selection_course?useUnicode=true&characterEncoding=utf-8"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>
<context:component-scan base-package="com.weon.sc"/>
</beans>
增删改查操作
- execute方法
- update与batchUpdate方法
- query与queryXXX方法
1. update方法–对数据进行增删改操作
int update(String sql, Object[] args)
int update(String sql, Object... args
2. batchUpdate方法–批量增删改操作
int[] batchUpdate(String[] sql)
int[] batchUpdate(String sql, List<Object[]> args)
3. 查询简单数据项
–获取一个
T queryForObject(String sql, Class<T> type)
T queryForObject(String sql, Object[] args, Class<T> type)
T queryForObject(String sql, Class<T> type,Object... arg)
–获取多个
List<T> queryForList(String sql , Class<T> type)
List<T> queryForList(String sql , Object[] args , Class<T> type)
List<T> queryForList(String sql , Class<T> type,Object... arg)
4. 查询复杂对象(封装为Map)
–获取一个
Map queryForMap(String sql)
Map queryForMap(String sql , Object[] args)
Map queryForMap(String sql , Object... arg)
–获取多个
List<Map<String,Object>> queryForList(String sql) List<Map<String,Object>> queryForList(String sql , Object[] args)
List<Map<String,Object>> queryForList(String sql , Object... arg)
5. 查询复杂对象(封装为实体对象)–RowMapper接口
–获取一个
T queryForObject(String sql,RowMapper<T> mapper)
T queryForObject(String sql,Object[] args,RowMapper<T> mapper)
T queryForObject(String sql,RowMapper<T> mapper,Object... arg)
–获取多个
List<T> query(String sql,RowMapper<T> mapper)
List<T> query(String sql,Object[] args,RowMapper<T> mapper)
List<T> query(String sql,RowMapper<T> mapper,Object... arg)
JDBC Template持久层示例
需要@Repository
和@Autowired
注解 记得在bean的xml文件中声明<context:component-scan base-package="com.weon.sc"/>
@Repository
public class CourseDaoImpl implements CourseDao {
@Autowired
private JdbcTemplate jdbcTemplate;
public void insert(Course course) {
String sql = "insert into course(name,score) values(?,?)";
jdbcTemplate.update(sql,course.getName(),course.getScore());
}
public void update(Course course) {
String sql = "update course set name=?,score=? where id=?";
jdbcTemplate.update(sql,course.getName(),course.getScore(),course.getId());
}
public void delete(int id) {
String sql = "delete from course where id=?";
jdbcTemplate.update(sql,id);
}
public Course select(int id) {
String sql = "select * from course where id=?";
return jdbcTemplate.queryForObject(sql,new CourseRowMapper(),id);
}
public List<Course> selectAll() {
String sql = "select * from course";
return jdbcTemplate.query(sql,new CourseRowMapper());
}
private class CourseRowMapper implements RowMapper<Course> {
public Course mapRow(ResultSet resultSet, int i) throws SQLException {
Course course = new Course();
course.setId(resultSet.getInt("id"));
course.setName(resultSet.getString("name"));
course.setScore(resultSet.getInt("score"));
return course;
}
}
}