Spring JdbcTemplate用法整理

Spring JdbcTemplate用法整理：

xml:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.springframework.org/schema/beans" xmlns:p="http://www.springframework.org/schema/p" xmlns:util="http://www.springframework.org/schema/util"
    xmlns:context="http://www.springframework.org/schema/context" xmlns:aop="http://www.springframework.org/schema/aop"
    xsi:schemaLocation="http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.1.xsd
        http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
        http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.1.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.1.xsd">
    <bean id="dbcpds" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="oracle.jdbc.OracleDriver"></property>
        <property name="url" value="jdbc:oracle:thin:@10.1.7.148:1521:ora11"></property>
        <property name="username" value="tds1ml1"></property>
        <property name="password" value="tds1ml1"></property>
        <property name="initialSize" value="5"></property>
        <property name="maxActive" value="10"></property>
    </bean>
    <bean id="sprds" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"></property>
        <property name="url" value="jdbc:oracle:thin:@10.1.7.148:1521:ora11"></property>
        <property name="username" value="tds1ml1" />
        <property name="password" value="tds1ml1" />
    </bean>
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <constructor-arg ref="dbcpds"></constructor-arg>
    </bean>
    <bean id="namedJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
        <constructor-arg ref="dbcpds"></constructor-arg>
    </bean>
    <bean id="jdbcSpitterDAO" class="com.stono.sprjdbc.JdbcSpitterDAO">
        <property name="template" ref="jdbcTemplate"></property>
        <property name="namedJdbcTemplate" ref="namedJdbcTemplate"></property>
    </bean>
    <bean id="jdbcSpitter2DAO" class="com.stono.sprjdbc.JdbcSpitter2Dao">
        <property name="dataSource" ref="dbcpds"></property>
    </bean>
</beans>

 

DAO Interface:

package com.stono.sprjdbc;
import java.util.Map;
public interface SpitterDAO {
    void insertSpitter(SpitterObj obj);
    void namedInsertSpitter(Map<String, Object> map);
    void updateSpitter(SpitterObj obj);
    void deleteSpitter(SpitterObj obj);
    SpitterObj getSpitter(String ljdm);
}

 

POJO:

package com.stono.sprjdbc;
public class SpitterObj {
    private String ljdm;
    private String ljpym;
    public String getLjdm() {
        return ljdm;
    }
    public void setLjdm(String ljdm) {
        this.ljdm = ljdm;
    }
    public String getLjpym() {
        return ljpym;
    }
    public void setLjpym(String ljpym) {
        this.ljpym = ljpym;
    }
}

 

Use JdbcTemplate:

package com.stono.sprjdbc;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Map;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
public class JdbcSpitterDAO implements SpitterDAO {
    private JdbcTemplate template;
    public void setTemplate(JdbcTemplate template) {
        this.template = template;
    }
    private NamedParameterJdbcTemplate namedJdbcTemplate;
    public void setNamedJdbcTemplate(NamedParameterJdbcTemplate namedJdbcTemplate) {
        this.namedJdbcTemplate = namedJdbcTemplate;
    }
    private static String INSERT_SQL = "insert into ljzdtemp(ljdm,ljpym) values(?,?)";
    private static String GET_SQL = "select * from ljzdtemp where ljdm = ?";
    private static String UPDATE_SQL = "update ljzdtemp set ljpym=? where ljdm = ?";
    private static String DEL_SQL = "delete ljzdtemp where ljdm = ?";
    private static String NAMED_INSERT_SQL = "insert into ljzdtemp(ljdm,ljpym) values(:ljdm,:ljpym)";
    @Override
    public void insertSpitter(SpitterObj obj) {
        template.update(INSERT_SQL, obj.getLjdm(), obj.getLjpym());
    }
    @Override
    public void updateSpitter(SpitterObj obj) {
        template.update(UPDATE_SQL, "aa", "a");
    }
    @Override
    public void deleteSpitter(SpitterObj obj) {
        template.update(DEL_SQL, "a");
    }
    @Override
    public SpitterObj getSpitter(String ljdm) {
        return template.queryForObject(GET_SQL, new RowMapper<SpitterObj>() {
            @Override
            public SpitterObj mapRow(ResultSet rs, int rowNum) throws SQLException {
                SpitterObj obj = new SpitterObj();
                obj.setLjdm(rs.getString(4));
                obj.setLjpym(rs.getString(5));
                return obj;
            }
        }, 'a');
    }
    @Override
    public void namedInsertSpitter(Map<String, Object> map) {
        namedJdbcTemplate.update(NAMED_INSERT_SQL, map);
    }
}

 

Extends NamedParameterJdbcDaoSuport:

package com.stono.sprjdbc;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Map;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcDaoSupport;
public class JdbcSpitter2Dao extends NamedParameterJdbcDaoSupport implements SpitterDAO {
    private static String INSERT_SQL = "insert into ljzdtemp(ljdm,ljpym) values(?,?)";
    private static String GET_SQL = "select * from ljzdtemp where ljdm = ?";
    private static String UPDATE_SQL = "update ljzdtemp set ljpym=? where ljdm = ?";
    private static String DEL_SQL = "delete ljzdtemp where ljdm = ?";
    private static String NAMED_INSERT_SQL = "insert into ljzdtemp(ljdm,ljpym) values(:ljdm,:ljpym)";
    @Override
    public void insertSpitter(SpitterObj obj) {
        getJdbcTemplate().update(INSERT_SQL, obj.getLjdm(), obj.getLjpym());
    }
    @Override
    public void namedInsertSpitter(Map<String, Object> map) {
        getNamedParameterJdbcTemplate().update(NAMED_INSERT_SQL, map);
    }
    @Override
    public void updateSpitter(SpitterObj obj) {
        getJdbcTemplate().update(UPDATE_SQL, "aa", "a");
    }
    @Override
    public void deleteSpitter(SpitterObj obj) {
        getJdbcTemplate().update(DEL_SQL, "a");
    }
    @Override
    public SpitterObj getSpitter(String ljdm) {
        return getJdbcTemplate().queryForObject(GET_SQL, new RowMapper<SpitterObj>() {
            @Override
            public SpitterObj mapRow(ResultSet rs, int rowNum) throws SQLException {
                SpitterObj obj = new SpitterObj();
                obj.setLjdm(rs.getString(4));
                obj.setLjpym(rs.getString(5));
                return obj;
            }
        }, 'a');
    }
}

 

AppBean:

package com.stono.sprjdbc;
import java.sql.Connection;
import java.util.HashMap;
import java.util.Map;
import org.apache.commons.dbcp.BasicDataSource;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
public class AppBeans14 {
    public static void main(String[] args) throws Exception {
        @SuppressWarnings("resource")
        ApplicationContext context = new ClassPathXmlApplicationContext("appbeans14.xml");
        BasicDataSource dataSource = (BasicDataSource) context.getBean("dbcpds");
        Connection connection = dataSource.getConnection();
        System.out.println(connection);
        DriverManagerDataSource sprDataSource = (DriverManagerDataSource) context.getBean("sprds");
        Connection connection2 = sprDataSource.getConnection();
        System.out.println(connection2);
        // 普通sql查询
        // String sql = "select * from ljzdtemp";
        // PreparedStatement prepareStatement = connection.prepareStatement(sql);
        // PreparedStatement prepareStatement = connection2.prepareStatement(sql);
        // ResultSet resultSet = prepareStatement.executeQuery();
        // while(resultSet.next()){
        // System.out.println(resultSet.getString(1));
        // }
        SpitterDAO dao = (SpitterDAO) context.getBean("jdbcSpitterDAO");
        // JdbcTemplate 插入
        // SpitterObj obj = new SpitterObj();
        // obj.setLjdm("a");
        // obj.setLjpym("a");
        // dao.insertSpitter(obj);
        // Map<String, Object> map = new HashMap<String, Object>();
        // map.put("ljdm", "a");
        // map.put("ljpym", "b");
        // dao.namedInsertSpitter(map);
        SpitterDAO dao2 = (SpitterDAO) context.getBean("jdbcSpitter2DAO");
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("ljdm", "b");
        map.put("ljpym", "c");
        dao2.namedInsertSpitter(map);
    }
}

 dbcp连接池需要的jar包：

commons-dbcp-1.4.jar commons-pool-1.6.jar