Spring's JdbcTemplate (10)

The JdbcTemplate template is similar to the Dbutils tool class.

Spring support for persistent layer technology

• JDBC:org.springframework.jdbc.core.JdbcTemplate
• Hibernate3.0:org.springframework.orm.hibernate3.hibernatetemplate
• IBatis (MyBatis): org.springframework.orm.ibatis.SqlMapClientTemplate
• JPA:org.springframework.orm.jpa.JpaTemplate

Introduction to Development JdbcTemplate:

• First step: Introduce the appropriate JAR package:

  • Spring-tx-3.2.0.release.jar
  • Spring-jdbc-3.2.0.release.jar
  • MySQL driver.
• Traditional JDBC Connection Demo

package cn.spring3.demo1;import org.junit.Test;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.jdbc.datasource.DriverManagerDataSource;public class SpringTest1 {    @Test    public void demo1(){        //创建连接池        DriverManagerDataSource dateSource = new DriverManagerDataSource();        //设置参数        dateSource.setDriverClassName("com.mysql.jdbc.Driver");        //dateSource.setUrl("jdbc:mysql://172.16.30.189:3306/spring3_day02");        dateSource.setUrl("jdbc:mysql://192.168.0.120:3306/spring3_day02");        dateSource.setUsername("root");        dateSource.setPassword("123");        //使用jdbc的模板        JdbcTemplate jdbcTemplate = new JdbcTemplate(dateSource);        //这个是第二种方式：jdbcTemplate.setDataSource(dateSource);        String sql="create table user (id int primary key auto_increment,name varchar(20))";        jdbcTemplate.execute(sql);    }}

• First way: Create Applicationcontext.xml (Configure spring default connection pool)

<!-- 配置Spring默认连接池 -->    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>        <property name="url" value="jdbc:mysql://172.16.30.189:3306/spring3_day02"/>        <property name="username" value="root"/>        <property name="password" value="123"/>    </bean>    <!-- 定义jdbctmplate -->    <bean id="jdbcTmplate" class="org.springframework.jdbc.core.JdbcTemplate">        <property name="dataSource" ref="dataSource"></property>    </bean>

• Write a test class:

package cn.spring3.demo1;import org.junit.Test;import org.junit.runner.RunWith;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.beans.factory.annotation.Qualifier;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.test.context.ContextConfiguration;import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;@RunWith(SpringJUnit4ClassRunner.class)@ContextConfiguration(locations="classpath:applicationContext.xml")public class SpringTest2 {    //配置Spring默认连接池    @Autowired    @Qualifier("jdbcTmplate")    private JdbcTemplate jdbcTmplate;    @Test    public void demo1(){        jdbcTmplate.execute("create table user (id int primary key auto_increment,name varchar(20))");    }    }

• Second way: Create Applicationcontext.xml (Configure DBCP connection pool)

<!-- 配置DBCP连接池 -->    <bean id="dateSourceDbcp" class="org.apache.commons.dbcp.BasicDataSource">        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>        <property name="url" value="jdbc:mysql://172.16.30.189:3306/spring3_day02"/>        <property name="username" value="root"/>        <property name="password" value="123"/>    </bean>    <!-- 定义jdbctmplate DBCP -->    <bean id="jdbcTmplateDbcp" class="org.springframework.jdbc.core.JdbcTemplate">        <property name="dataSource" ref="dateSourceDbcp"></property>    </bean>

• To write a test class:

package cn.spring3.demo1;import org.junit.Test;import org.junit.runner.RunWith;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.beans.factory.annotation.Qualifier;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.test.context.ContextConfiguration;import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;@RunWith(SpringJUnit4ClassRunner.class)@ContextConfiguration(locations="classpath:applicationContext.xml")public class SpringTest2 {    //配置DBCP连接池     @Autowired    @Qualifier("jdbcTmplateDbcp")    private JdbcTemplate jdbcTmplateDbcp;    @Test    public void demo2(){        jdbcTmplateDbcp.execute("create table user (id int primary key auto_increment,name varchar(20))");    }    }

• Third Way: Create Applicationcontext.xml (Configure C3P0 connection pool)

<!-- 配置C3P0连接池 -->    <bean id="dateSourceC3p0" class="com.mchange.v2.c3p0.ComboPooledDataSource">        <property name="driverClass" value="com.mysql.jdbc.Driver"/>        <property name="jdbcUrl" value="jdbc:mysql://172.16.30.189:3306/spring3_day02"/>        <property name="user" value="root"/>        <property name="password" value="123"/>    </bean>    <!-- 定义jdbctmplate DBCP -->    <bean id="jdbcTmplateC3p0" class="org.springframework.jdbc.core.JdbcTemplate">        <property name="dataSource" ref="dateSourceC3p0"></property>    </bean>

• To write a test class:

package cn.spring3.demo1;import org.junit.Test;import org.junit.runner.RunWith;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.beans.factory.annotation.Qualifier;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.test.context.ContextConfiguration;import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;@RunWith(SpringJUnit4ClassRunner.class)@ContextConfiguration(locations="classpath:applicationContext.xml")public class SpringTest2 {    //配置C3p0连接池     @Autowired    @Qualifier("jdbcTmplateC3p0")    private JdbcTemplate jdbcTmplateC3p0;    @Test    public void demo3(){        jdbcTmplateC3p0.execute("create table user (id int primary key auto_increment,name varchar(20))");    }    }

Introduction to configuration file, configuring connection pooling (c3p0 example)

1. First step: New Jdbc.properties (name casual)

jdbc.driver = com.mysql.jdbc.Driverjdbc.url = jdbc:mysql://172.16.30.189:3306/spring3_day02jdbc.user = rootjdbc.password =123

2. Step Two: Configure Applicationcontext.xml (two ways to introduce)
2.1 The first configuration method:

<!-- 配置C3P0连接池 引入配置文件的方式  方法一-->    <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">          <property name="location" value="classpath:JDBC.properties"></property>    </bean>    <bean id="dateSourceC3p0ref" 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>    <!-- 定义jdbctmplate DBCP -->    <bean id="jdbcTmplateC3p0ref" class="org.springframework.jdbc.core.JdbcTemplate">        <property name="dataSource" ref="dateSourceC3p0ref"></property>    </bean>

To write a test class:

 package cn.spring3.demo1;import org.junit.Test;import org.junit.runner.RunWith;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.beans.factory.annotation.Qualifier;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.test.context.ContextConfiguration;import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;@RunWith(SpringJUnit4ClassRunner.class)@ContextConfiguration(locations="classpath:applicationContext.xml")public class SpringTest2 {//配置C3p0连接池  引入配置文件方式 方法一 第二种方法请见springtest3.java    @Autowired    @Qualifier("jdbcTmplateC3p0ref")    private JdbcTemplate jdbcTmplateC3p0ref;    @Test    public void demo4(){        jdbcTmplateC3p0ref.execute("create table user (id int primary key auto_increment,name varchar(20))");    }    }

2.2  第二种配置方式：

xmlns:context="http://www.springframework.org/schema/context"<!-- 配置C3P0连接池 引入配置文件的方式  方法二-->    <context:property-placeholder location="classpath:JDBC.properties"/>    <bean id="dateSourceC3p0ref" 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>    <!-- 定义jdbctmplate DBCP -->    <bean id="jdbcTmplateC3p0ref" class="org.springframework.jdbc.core.JdbcTemplate">        <property name="dataSource" ref="dateSourceC3p0ref"></property>    </bean>

To write a test class:

 package cn.spring3.demo1;import org.junit.Test;import org.junit.runner.RunWith;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.beans.factory.annotation.Qualifier;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.test.context.ContextConfiguration;import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;@RunWith(SpringJUnit4ClassRunner.class)@ContextConfiguration(locations="classpath:applicationContext1.xml")public class SpringTest3 {    //配置C3p0连接池  引入配置文件方式 方法二   方法一见springtest2.java    @Autowired    @Qualifier("jdbcTmplateC3p0ref")    private JdbcTemplate jdbcTmplateC3p0ref;    @Test    public void demo1(){        jdbcTmplateC3p0ref.execute("create table user (id int primary key auto_increment,name varchar(20))");    }}

* 总结以上：

在src下创建jdbc.propertiesjdbc.driver = com.mysql.jdbc.Driverjdbc.url = jdbc:mysql:///spring3_day02jdbc.user = rootjdbc.password = 123需要在applicationContext.xml 中使用属性文件配置的内容.* 第一种写法:<bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">          <property name="location" value="classpath:jdbc.properties"></property></bean>* 第二种写法:<context:property-placeholder location="classpath:jdbc.properties"/>

JdbcTemplate CRUD Operations:

Spring框架中提供了对持久层技术支持的类:JDBC            :   org.springframework.jdbc.core.support.JdbcDaoSupportHibernate 3.0   :   org.springframework.orm.hibernate3.support.HibernateDaoSupportiBatis      :   org.springframework.orm.ibatis.support.SqlMapClientDaoSupport编写DAO的时候:Public class UserDao extends JdbcDaoSupport{}进行CRUD的操作;* 保存:update(String sql,Object... args)* 修改:update(String sql,Object... args)* 删除:update(String sql,Object... args)

• Example:

User class

package cn.spring3.demo2;public class User {    private Integer id;    private String name;    public Integer getId() {        return id;    }    public void setId(Integer id) {        this.id = id;    }    public String getName() {        return name;    }    public void setName(String name) {        this.name = name;    }}

Userdao class

package cn.spring3.demo2;import org.springframework.jdbc.core.support.JdbcDaoSupport;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());    }}

Test class:

Package Cn.spring3.demo2;import Org.junit.test;import Org.junit.runner.runwith;import Org.springframework.beans.factory.annotation.autowired;import Org.springframework.beans.factory.annotation.qualifier;import Org.springframework.jdbc.core.jdbctemplate;import Org.springframework.test.context.contextconfiguration;import Org.springframework.test.context.junit4.SpringJUnit4ClassRunner, @RunWith (Springjunit4classrunner.class) @ Contextconfiguration (locations= "Classpath:applicationContext1.xml") public class StringTest1 {@Autowired @Qualifier    ("Userdao") private Userdao Userdao;        Add @Test public void Demo1 () {User user = new User ();        User.setname ("one");    Userdao.add (user);        }//Modify @Test public void Demo2 () {User user = new User ();        User.setid (2);        User.setname ("both");    Userdao.update (user);        }//delete @Test public void Demo3 () {User user = new User ();        User.setid (3);    Userdao.delete (user); }} 

Inquire:

• Simple query (single type query):

  • Select COUNT (*) from user; ---queryforint (String sql);
  • Select name from user where id =?; ---queryforobject (String sql,class clazz,object ... args);
• Complex queries: (Return objects, and collections of objects)
  • SELECT * from user where id =? ---queryforobjectstring sql,rowmapper<t> rowmapper,object ... args);
  • select * from user; ---query (String sql,rowmapper<t> rowmapper,object ... args);

Userdao class Add method

public int findCount(){        String sql = "select count(*) from user";        return this.getJdbcTemplate().queryForInt(sql);    }    public String findNameById(User user){        String sql = "select name from user where id= ?";        return this.getJdbcTemplate().queryForObject(sql, String.class, user.getId());    }

Test class Writing:

//简单查询1    @Test    public void demo4(){        System.out.println(userDao.findCount());    }    //简单查询2    @Test    public void demo5(){        User user = new User();        user.setId(2);        System.out.println(userDao.findNameById(user));    }

Complex queries
Userdao class Add method

class UserRowMapper implements RowMapper<User>{        /*         * rc:结果集         * 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;        }    }    public User findById(int id){        String sql = "select * from user where id= ?";        User user = this.getJdbcTemplate().queryForObject(sql,new UserRowMapper(), id);        return user;    }

Or in the form of an inner class:

public User findById(int id){        String sql = "select * from user where id = ?";        return this.getJdbcTemplate().queryForObject(sql, new RowMapper<User>(){            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;            }        }, id);    }    public List<User> find(){        String sql = "select * from user ";        return this.getJdbcTemplate().query(sql, new RowMapper<User>(){            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;            }        });    }

To write a test class:

//复杂查询    @Test    public void demo6(){        User user = userDao.findById(2);        System.out.println(user);    }

More than one query
Userdao

public List<User> findAll(){        String sql = "select * from user";        return this.getJdbcTemplate().query(sql,new UserRowMapper());    }

To write a test class:

// 复杂查询2    @Test    public void demo7() {        List<User> list = userDao.findAll();        for (User user : list) {            System.out.println(user);        }    }

Spring's JdbcTemplate (10)