JDBC Template基本使用

JdbcTemplate配置连接池

org.springframework.jdbc.core.JdbcTemplate类方便执行SQL语句

public JdbcTemplate(DataSource dataSource)
创建JdbcTemplate对象，方便执行SQL语句

public void execute(final String sql)
execute可以执行所有SQL语句，因为没有返回值，一般用于执行DDL语句。

JdbcTemplate使用步骤

1. 准备DruidDataSource连接池
2. 导入依赖的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

3. 创建JdbcTemplate对象，传入Druid连接池
4. 调用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&amp;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;
        }
    }
}