JDBCTemplate的使用

首先定义一个properties文件

driverClass=com.mysql.jdbc.Driver
jdbcUrl=jdbc:mysql://localhost:3306/mydb?characterEncoding=GBK
user=root
password=

minPoolSize=5
maxPoolSize=15
initialPoolSize=5

定义一个beans.xml文件调用properties文件构建连接池

<?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:aop="http://www.springframework.org/schema/aop"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.3.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd">
    
    <context:property-placeholder location="classpath:db.properties"/>
    
    <bean class="com.mchange.v2.c3p0.ComboPooledDataSource" id="dataSource">
        <property name="driverClass" value="${driverClass}"></property>
        <property name="jdbcUrl" value="${jdbcUrl}"></property>
        <property name="user" value="${user}"></property>
        <property name="password" value="${password}"></property>
        
        <property name="minPoolSize" value="${minPoolSize}"></property>
        <property name="maxPoolSize" value="${maxPoolSize}"></property>
        <property name="initialPoolSize" value="${initialPoolSize}"></property>
    </bean>
    <bean class="org.springframework.jdbc.core.JdbcTemplate" id="jdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>
    <bean class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate" id="namedJdbcTemplate">
        <constructor-arg ref="dataSource" ></constructor-arg>
    </bean>
</beans>

再根据你的数据库的信息定义一个实体类

package com.test;

public class Nation {
    private String code;
    private String name;
    public String getCode() {
        return code;
    }
    public void setCode(String code) {
        this.code = code;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    @Override
    public String toString() {
        return "Nation [code=" + code + ", name=" + name + "]";
    }
    
    
}

package com.test;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class Test {
    public static void main(String[] args) throws Exception {//查询code为n003的一行数据
        ApplicationContext context = new ClassPathXmlApplicationContext("beans.xml");//获取beans.xml文件
        DataSource ds = (DataSource)context.getBean("dataSource");//获取beans.xml文件下id为dataSource的bean类
        NamedParameterJdbcTemplate j= (NamedParameterJdbcTemplate)context.getBean("namedJdbcTemplate");
        String sql = "select * from nation where code=:code";
        
        Map<String, String> paramMap = new HashMap<String, String>();
        paramMap.put("code", "n003");
        RowMapper<Nation> rowMapper= new BeanPropertyRowMapper<Nation>(Nation.class);
        Nation n=j.queryForObject(sql, paramMap, rowMapper);
        System.out.println(n);
    }
    public static void main33333(String[] args) throws Exception {//查询code为n003的一行数据，通过直接配置连接池
        ComboPooledDataSource cd = new ComboPooledDataSource();
        cd.setDriverClass("com.mysql.jdbc.Driver");
        cd.setJdbcUrl("jdbc:mysql://localhost:3306/mydb");
        cd.setUser("root");
        cd.setPassword("");
        cd.setMinPoolSize(5);
        cd.setMaxPoolSize(10);
        
        NamedParameterJdbcTemplate j = new NamedParameterJdbcTemplate(cd);
        String sql = "select * from nation where code=:code";
        
        Map<String, String> paramMap = new HashMap<String, String>();
        paramMap.put("code", "n003");
        RowMapper<Nation> rowMapper= new BeanPropertyRowMapper<Nation>(Nation.class);
        Nation n=j.queryForObject(sql, paramMap, rowMapper);
        System.out.println(n);
    }
    public static void main23123(String[] args) throws Exception {//查询nation表中共有几条数据
        ApplicationContext context = new ClassPathXmlApplicationContext("beans.xml");
        DataSource d = (DataSource)context.getBean("dataSource");
        JdbcTemplate j = (JdbcTemplate) context.getBean("jdbcTemplate");
        
        String sql = "select count(*) from nation";
        long count = j.queryForObject(sql, long.class);//直接定义long类型通过long.class直接查询
        System.out.println(count);
    }
    public static void main909(String[] args) throws Exception {//查询nation表中所有数据，采用集合形式
        ApplicationContext context = new ClassPathXmlApplicationContext("beans.xml");
        DataSource d = (DataSource)context.getBean("dataSource");
        JdbcTemplate j = (JdbcTemplate) context.getBean("jdbcTemplate");
        
        String sql = "select * from nation";
        RowMapper<Nation> row = new BeanPropertyRowMapper<Nation>(Nation.class);
        List<Nation> list=j.query(sql, row);
        for(Nation data:list){
        System.out.println(data);
        }
    }    
    public static void main999(String[] args) throws Exception {//查询code为n003的一行数据，同理于第一个
        ApplicationContext context = new ClassPathXmlApplicationContext("beans.xml");
        DataSource d = (DataSource)context.getBean("dataSource");
        JdbcTemplate j = (JdbcTemplate) context.getBean("jdbcTemplate");
        
        String sql = "select * from nation where code=?";
        RowMapper<Nation> row = new BeanPropertyRowMapper<Nation>(Nation.class);
        Nation data=j.queryForObject(sql, row,"n003");
        System.out.println(data);
    }
    public static void main888(String[] args) throws Exception {//添加数据
        ApplicationContext context = new ClassPathXmlApplicationContext("beans.xml");
        DataSource d = (DataSource)context.getBean("dataSource");
        JdbcTemplate j = (JdbcTemplate) context.getBean("jdbcTemplate");
        
        String sql = "insert into nation values(?,?)";
        List<Object[]> list = new ArrayList<Object[]>();
        list.add(new Object[]{"n010","哈哈族"});
        list.add(new Object[]{"n011","哈哈2族"});
        j.batchUpdate(sql,list);
    }
    public static void main777(String[] args) throws Exception {//删除数据
        ApplicationContext context = new ClassPathXmlApplicationContext("beans.xml");
        DataSource d = (DataSource)context.getBean("dataSource");
        JdbcTemplate j = (JdbcTemplate) context.getBean("jdbcTemplate");
        
        String sql = "delete from nation where code=?";
        j.update(sql,"周3");
    }
    public static void main666(String[] args) throws Exception {
        ApplicationContext context = new ClassPathXmlApplicationContext("beans.xml");
        DataSource d = (DataSource)context.getBean("dataSource");
        JdbcTemplate j = (JdbcTemplate) context.getBean("jdbcTemplate");
        
        String sql = "insert into nation values(?,?)";
        j.update(sql,"周3","p009");
    }
    public static void main444(String[] args) throws Exception {//修改数据
        ApplicationContext context = new ClassPathXmlApplicationContext("beans.xml");
        DataSource d = (DataSource)context.getBean("dataSource");
        JdbcTemplate j = (JdbcTemplate) context.getBean("jdbcTemplate");
        
        String sql = "update info set name=? where code=?";
        j.update(sql,"周2","p002");
    }
        
    

}