Spring的JdbcTemplate

#1 开发JDBCTemplate入门:

## 第一步:引入相应jar包:

* spring-tx-3.2.0.RELEASE.jar

* spring-jdbc-3.2.0.RELEASE.jar

* mysql驱动.

## 第二步:创建applicationContext.xml

## 第三步:编写一个测试类:

```

@RunWith(SpringJUnit4ClassRunner.class)

@ContextConfiguration("classpath:applicationContext.xml")

public class SpringTest1 {

@Autowired

@Qualifier("jdbcTemplate")

private JdbcTemplate jdbcTemplate;

@Test

public void demo2(){

jdbcTemplate.execute("create table user (id int primary key auto_increment,name varchar(20))");

}

@Test

public void demo1(){

// 创建连接池:

DriverManagerDataSource dataSource = new DriverManagerDataSource();

// 设置参数:

dataSource.setDriverClassName("com.mysql.jdbc.Driver");

dataSource.setUrl("jdbc:mysql:///spring3_day02");

dataSource.setUsername("root");

dataSource.setPassword("123");

// 使用JDBC的模板:

JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

jdbcTemplate.execute("create table user (id int primary key auto_increment,name varchar(20))");

}

}

```

#2 配置连接池:

##(1) Spring默认的连接池:

<!-- 配置Spring默认的连接池 -->

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">

    <property name="driverClassName" value="com.mysql.jdbc.Driver"/>

    <property name="url" value="jdbc:mysql:///spring3_day02"/>

    <property name="username" value="root"/>

    <property name="password" value="123"/>

</bean>

```

##(2) DBCP连接池:

* 导入jar包:

* com.springsource.org.apache.commons.dbcp-1.2.2.osgi.jar

* com.springsource.org.apache.commons.pool-1.5.3.jar

<!-- 配置DBCP连接池 -->

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">

    <property name="driverClassName" value="com.mysql.jdbc.Driver"/>

    <property name="url" value="jdbc:mysql:///spring3_day02"/>

    <property name="username" value="root"/>

    <property name="password" value="123"/>

</bean>

```

##(3) C3P0连接池:

* 导入jar包:

* com.springsource.com.mchange.v2.c3p0-0.9.1.2.jar

```

<!-- 配置c3p0连接池 -->

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">

    <property name="driverClass" value="com.mysql.jdbc.Driver"/>

    <property name="jdbcUrl" value="jdbc:mysql:///spring3_day02"/>

    <property name="user" value="root"/>

    <property name="password" value="123"/>

</bean>

```

##(4)   参数设置到属性文件中:

* 在src下创建jdbc.properties

```

jdbc.driver = com.mysql.jdbc.Driver

jdbc.url = jdbc:mysql:///spring3_day02

jdbc.user = root

jdbc.password = 123

```

* 需要在applicationContext.xml 中使用属性文件配置的内容.

* 第一种写法:

```

<bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">

          <property name="location" value="classpath:jdbc.properties"></property>

</bean>

```

* 第二种写法:(常用方式，来加载jdbc的配置文件)

```

<context:property-placeholder location="classpath:jdbc.properties"/>

```

```

<span style="color: #cc7832">jdbc.driver</span><span style="color: #808080">=</span><span style="color: #6a8759">com.mysql.jdbc.Driver<br></span><span style="color: #cc7832">jdbc.url</span><span style="color: #808080">=</span><span style="color: #6a8759">jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8<br></span><span style="color: #cc7832">jdbc.username</span><span style="color: #808080">=</span><span style="color: #6a8759">root<br></span><span style="color: #cc7832">jdbc.password</span><span style="color: #808080">=</span><span style="color: #6a8759">123</span>

```

* 定义配置文件properties

 - 利用标签加载jdbc.properties

 - 利用spring提供的ComboPooledDataSource类来产生dataSource这个类里面主要是传入jdbc连接数据库的四个参数

 - 利用spring提供的JdbcTemplate类，这个类里面提供了很多直接操纵数据库的很多方法

```

<span style="color: #e8bf6a"><?</span><span style="color: #bababa">xml version</span><span style="color: #6a8759">="1.0" </span><span style="color: #bababa">encoding</span><span style="color: #6a8759">="UTF-8"</span><span style="color: #e8bf6a">?><br></span><span style="color: #e8bf6a"><beans </span><span style="color: #bababa">xmlns</span><span style="color: #6a8759">="http://www.springframework.org/schema/beans"<br></span><span style="color: #6a8759">       </span><span style="color: #bababa">xmlns:</span><span style="color: #9876aa">xsi</span><span style="color: #6a8759">="http://www.w3.org/2001/XMLSchema-instance"<br></span><span style="color: #6a8759">       </span><span style="color: #bababa">xmlns:</span><span style="color: #9876aa">context</span><span style="color: #6a8759">="http://www.springframework.org/schema/context"<br></span><span style="color: #6a8759">       </span><span style="color: #9876aa">xsi</span><span style="color: #bababa">:schemaLocation</span><span style="color: #6a8759">="<br></span><span style="color: #6a8759">http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd<br></span><span style="color: #6a8759">http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd"</span><span style="color: #e8bf6a">><br></span><span style="color: #e8bf6a"><br></span><span style="color: #e8bf6a">    <</span><span style="color: #9876aa">context</span><span style="color: #e8bf6a">:property-placeholder </span><span style="color: #bababa">location</span><span style="color: #6a8759">="classpath:jdbc.properties"</span><span style="color: #e8bf6a">/><br></span><span style="color: #e8bf6a"><br></span><span style="color: #e8bf6a">    </span><span style="color: #808080"><!--配置c3p0--><br></span><span style="color: #808080">    </span><span style="color: #e8bf6a"><bean </span><span style="color: #bababa">id</span><span style="color: #6a8759">="dataSource" </span><span style="color: #bababa">class</span><span style="color: #6a8759">="com.mchange.v2.c3p0.ComboPooledDataSource"</span><span style="color: #e8bf6a">><br></span><span style="color: #e8bf6a">        <property </span><span style="color: #bababa">name</span><span style="color: #6a8759">="driverClass" </span><span style="color: #bababa">value</span><span style="color: #6a8759">="${jdbc.driver}"</span><span style="color: #e8bf6a">/><br></span><span style="color: #e8bf6a">        <property </span><span style="color: #bababa">name</span><span style="color: #6a8759">="jdbcUrl" </span><span style="color: #bababa">value</span><span style="color: #6a8759">="${jdbc.url}"</span><span style="color: #e8bf6a">/><br></span><span style="color: #e8bf6a">        <property </span><span style="color: #bababa">name</span><span style="color: #6a8759">="user" </span><span style="color: #bababa">value</span><span style="color: #6a8759">="${jdbc.username}"</span><span style="color: #e8bf6a">/><br></span><span style="color: #e8bf6a">        <property </span><span style="color: #bababa">name</span><span style="color: #6a8759">="password" </span><span style="color: #bababa">value</span><span style="color: #6a8759">="${jdbc.password}"</span><span style="color: #e8bf6a">/><br></span><span style="color: #e8bf6a">    </bean><br></span><span style="color: #e8bf6a"><br></span><span style="color: #e8bf6a">    <bean </span><span style="color: #bababa">id</span><span style="color: #6a8759">="jdbcTemplate" </span><span style="color: #bababa">class</span><span style="color: #6a8759">="org.springframework.jdbc.core.JdbcTemplate"</span><span style="color: #e8bf6a">><br></span><span style="color: #e8bf6a">        <property </span><span style="color: #bababa">name</span><span style="color: #6a8759">="dataSource" </span><span style="color: #bababa">ref</span><span style="color: #6a8759">="dataSource"</span><span style="color: #e8bf6a">/><br></span><span style="color: #e8bf6a">    </bean><br></span><span style="color: #e8bf6a"></beans></span>

```

* 测试，直接注入jdbcTemplate来直接操纵数据库

```

<span style="color: #bbb529">@RunWith</span>(SpringJUnit4ClassRunner.<span style="color: #cc7832">class</span>)<br><span style="color: #bbb529">@ContextConfiguration</span>(<span style="color: #6a8759">"classpath:applicationContext.xml"</span>)<br><span style="color: #cc7832">public class </span>SpringTest1 {<br>    <span style="color: #bbb529">@Autowired<br></span><span style="color: #bbb529">    @Qualifier</span>(<span style="color: #6a8759">"jdbcTemplate"</span>)<br>    <span style="color: #cc7832">private </span>JdbcTemplate <span style="color: #9876aa">jdbcTemplate</span><span style="color: #cc7832">;<br></span><span style="color: #cc7832"><br></span><span style="color: #cc7832">    </span><span style="color: #bbb529">@Test<br></span><span style="color: #bbb529">    </span><span style="color: #cc7832">public void </span><span style="color: #ffc66d">demo</span>() {<br>        <span style="color: #9876aa">jdbcTemplate</span>.execute(<span style="color: #6a8759">"create table user (</span><span style="color: #9876aa">id</span><span style="color: #6a8759"> int primary key auto_increment,</span><span style="color: #9876aa">name</span><span style="color: #6a8759"> varchar(20))"</span>)<span style="color: #cc7832">;<br></span><span style="color: #cc7832">    </span>}<br>}

```

#4 JdbcTemplate的CRUD的操作:

* Spring框架中提供了对持久层技术支持的类:

 - JDBC         :   org.springframework.jdbc.core.support.JdbcDaoSupport

 - Hibernate 3.0   :   org.springframework.orm.hibernate3.support.HibernateDaoSupport

 - iBatis      :   org.springframework.orm.ibatis.support.SqlMapClientDaoSupport

<span style="color: #cc7832">public class </span>User {<br>    <span style="color: #cc7832">private </span>Integer <span style="color: #9876aa">id</span><span style="color: #cc7832">;<br></span><span style="color: #cc7832">    private </span>String <span style="color: #9876aa">name</span><span style="color: #cc7832">;<br></span><span style="color: #cc7832">    public </span>Integer <span style="color: #ffc66d">getId</span>() {<br>        <span style="color: #cc7832">return </span><span style="color: #9876aa">id</span><span style="color: #cc7832">;<br></span><span style="color: #cc7832">    </span>}<br>    <span style="color: #cc7832">public void </span><span style="color: #ffc66d">setId</span>(Integer id) {<br>        <span style="color: #cc7832">this</span>.<span style="color: #9876aa">id </span>= id<span style="color: #cc7832">;<br></span><span style="color: #cc7832">    </span>}<br>    <span style="color: #cc7832">public </span>String <span style="color: #ffc66d">getName</span>() {<br>        <span style="color: #cc7832">return </span><span style="color: #9876aa">name</span><span style="color: #cc7832">;<br></span><span style="color: #cc7832">    </span>}<br>    <span style="color: #cc7832">public void </span><span style="color: #ffc66d">setName</span>(String name) {<br>        <span style="color: #cc7832">this</span>.<span style="color: #9876aa">name </span>= name<span style="color: #cc7832">;<br></span><span style="color: #cc7832">    </span>}<br>    <span style="color: #bbb529">@Override<br></span><span style="color: #bbb529">    </span><span style="color: #cc7832">public </span>String <span style="color: #ffc66d">toString</span>() {<br>        <span style="color: #cc7832">return </span><span style="color: #6a8759">"User [id=" </span>+ <span style="color: #9876aa">id </span>+ <span style="color: #6a8759">", name=" </span>+ <span style="color: #9876aa">name </span>+ <span style="color: #6a8759">"]"</span><span style="color: #cc7832">;<br></span><span style="color: #cc7832">    </span>}<br>}<br>

<!-- 配置c3p0连接池 -->

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">

<property name="driverClass" value="${jdbc.driver}"/>

<property name="jdbcUrl" value="${jdbc.url}"/>

<property name="user" value="${jdbc.user}"/>

<property name="password" value="${jdbc.password}"/>

</bean>

<!-- 定义jdbctemplate -->

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">

<property name="dataSource" ref="dataSource"/>

</bean>

<bean id="userDao" class="cn.itcast.spring3.demo2.UserDao">

<property name="jdbcTemplate" ref="jdbcTemplate"/>

</bean>

* ## 编写DAO的时候:

Public class UserDao extends JdbcDaoSupport

 - 原因是因为我们操纵数据库的时候都是通过JdbcTemplemate来直接操纵的，所以如果你不继承他，那么你需要在userDao中还要定义JdbcTemplemate，然后在配置文件中再把这个注入进userDao中，但是直接继承他之后，它里面是有JdbcTemplemate

```

public class UserDao extends JdbcDaoSupport{

public void add(User user){

String sql = "insert into user values (null,?)";

this.getJdbcTemplate().update(sql, user.getName());

}

public void update(User user){

String sql = "update user set name = ? where id = ?";

this.getJdbcTemplate().update(sql, user.getName(),user.getId());

}

public void delete(User user){

String sql = "delete from user where id = ?";

this.getJdbcTemplate().update(sql, user.getId());

}

public int findCount(){

String sql = "select count(*) from user";

return this.getJdbcTemplate().queryForInt(sql);

}

public String findNameById(int id){

String sql = "select name from user where id = ?";

return this.getJdbcTemplate().queryForObject(sql, String.class, id);

}

public User findById(int id){

String sql = "select * from user where id = ?";

User user = this.getJdbcTemplate().queryForObject(sql, new UserRowMapper(), id);

return user;

}

public List<User> findAll(){

String sql = "select * from user";

return this.getJdbcTemplate().query(sql, new UserRowMapper());

}

class UserRowMapper implements RowMapper<User>{

/**

* rs:结果集.

* rowNum:行号

*/

public User mapRow(ResultSet rs, int rowNum) throws SQLException {

User user = new User();

user.setId(rs.getInt("id"));

user.setName(rs.getString("name"));

return user;

}

}

}

```

* 进行CRUD的操作;

 -  保存:update(String sql,Object... args)

 -  修改:update(String sql,Object... args)

 -  删除:update(String sql,Object... args)

* 查询:

 -  ### 简单查询:

  * select count(*) from user;            --- queryForInt(String sql);

  * select name from user where id = ?;   --- queryForObject(String sql,Class clazz,Object... args);

 - ### 复杂查询:(返回对象,和对象集合)

  * select * from user where id = ?       --- queryForObjectString sql,RowMapper<T> rowMapper,Object... args);

  * select * from user;                   --- query(String sql,RowMapper<T> rowMapper,Object... args);