Spring3 JDBC

Spring JDBC抽象框架

定义数据库连接参数
打开数据库连接
声明SQL语句
预编译并执行SQL语句
遍历查询结果（如果需要的话）
处理每一次遍历操作
处理抛出的任何异常
处理事务
关闭数据库连接

配置数据源

数据库连接
数据库连接池
数据源

由JDBC驱动程序定义的数据源
基于连接池的JNDI数据源
基于连接池的本地数据源

DBCP数据库连接池

BasicDataSource
initialSize ：初始化连接数量，默认值为0
maxActive ：最大连接数,默认值为8
maxIdle：闲置连接的最大数量，默认为8
minIdle：闲置连接的最小数量，默认为0
maxWait：最大等待时间，以毫秒为单位。
minEvictableIdleTimeMillis ：闲置连接被逐出连接池的时间期限，以毫秒为单位，默认为30分钟
在Spring中使用基于连接池的本地数据源

直接在Spring应用容器内部以bean的形式定义基于连接池

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" scope="singleton">
<property name="driverClassName" value="oracle.jdbc.OracleDriver"/>
<property name="url" value="jdbc:oracle:thin:@localhost:1521:orcl" />
<property name="username" value="scott" />
<property name="password" value="tiger" />
<property name="initialSize" value="10"/>
<property name="maxActive" value="50"/>
<property name="maxIdle" value="10"/>
<property name="minIdle" value="5"/>
<property name="maxWait" value="10000"/>
</bean>

直接在非web应用程序中使用

mian方法中执行下列代码可测试数据源

@Test
public void test2() throws Exception
{
//通过spring容器获取dao
EmpDaoJdbc dao = (EmpDaoJdbc) applicationContext.getBean("empDaoJdbc");
//执行查询
List<Emp> list = dao.search();
for (Emp emp : list) {
System.out.println(emp.getEmpno()+"	"
+emp.getEname()+"	"
+emp.getDept().getDname());
}
}

使用Spring JDBC 模板

使用Spring对JDBC的Dao支持类

个人测试代码

层次图

pom文件

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>cn.yunhe</groupId>
  <artifactId>spring3</artifactId>
  <packaging>war</packaging>
  <version>1.0-SNAPSHOT</version>
  <name>spring3 Maven Webapp</name>
  <url>http://maven.apache.org</url>
  <dependencies>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-context</artifactId>
      <version>3.0.5.RELEASE</version>
    </dependency>

    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
    </dependency>

    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-core</artifactId>
      <version>3.0.5.RELEASE</version>
    </dependency>

    <dependency>
      <groupId>org.aspectj</groupId>
      <artifactId>aspectjrt</artifactId>
      <version>1.6.11</version>
    </dependency>

    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-aspects</artifactId>
      <version>3.0.5.RELEASE</version>
    </dependency>

    <dependency>
      <groupId>org.aspectj</groupId>
      <artifactId>aspectjweaver</artifactId>
      <version>1.6.11</version>
    </dependency>

    <dependency>
      <groupId>cglib</groupId>
      <artifactId>cglib</artifactId>
      <version>2.1</version>
    </dependency>

    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.6</version>
    </dependency>

    <dependency>
      <groupId>commons-dbcp</groupId>
      <artifactId>commons-dbcp</artifactId>
      <version>1.4</version>
    </dependency>

    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>3.0.5.RELEASE</version>
    </dependency>

  </dependencies>
  <build>
    <finalName>spring3</finalName>
  </build>
</project>

Dao

package cn.yunhe.dao;

import cn.yunhe.entity.User;

import java.sql.SQLException;
import java.util.List;

/**
 * Created by Administrator on 2017/7/14.
 */
public interface UserDao {
    List<User> search() throws SQLException;
    List<User> search(User user);
    User findById(int uid);
    void save(User user);
    void update(User user);
    void delete(int uid);
}

UserDao

package cn.yunhe.dao;

import cn.yunhe.entity.Dept;
import cn.yunhe.entity.User;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2017/7/14.
 */
public class UserDaoImpl implements UserDao {
    Connection conn=null;
    PreparedStatement ps=null;
    ResultSet rs=null;

    private DataSource dataSource;
    public List<User> search() throws SQLException {
        conn=dataSource.getConnection();
        String sql="select u.*,d.*  from t_user u inner join t_dept d on u.deptid=d.did";
         ps=conn.prepareStatement(sql);
        rs= ps.executeQuery();
        List<User> list=new ArrayList<User>();
        boolean flag=rs.next();
        System.out.println(flag+"=======================");
        while (rs.next()){
            User user=new User();
            user.setUid(rs.getInt("uid"));
            user.setUname(rs.getString("uname"));
            user.setUpassword(rs.getString("upassword"));
            user.setUage(rs.getInt("uage"));
            Dept dept=new Dept();
            dept.setDid(rs.getInt("did"));
            dept.setDname(rs.getString("dname"));
            user.setDept(dept);
            list.add(user);
        }
        return list;
    }

    public List<User> search(User user) {
        return null;
    }

    public User findById(int uid) {
        return null;
    }

    public void save(User user) {

    }

    public void update(User user) {

    }

    public void delete(int uid) {

    }

    public DataSource getDataSource() {
        return dataSource;
    }

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }
}

UserDaoImpl

package cn.yunhe.dao;

import cn.yunhe.entity.Dept;
import cn.yunhe.entity.User;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * Created by Administrator on 2017/7/17.
 *Spring 的 JdbcDaoSupport 就是用于编写基于JDBC 的DAO 类的基类
 自己的DAO类继承它即可

 *使用JdbcDaoSupport 来获取 JdbcTemplate
 */
public class UserDaoJdbcSupport extends JdbcDaoSupport implements UserDao{

    public static final RowMapper rowMapper=new ParameterizedRowMapper<User>() {
        public User mapRow(ResultSet rs, int rowNum) throws SQLException {
            User user=new User();
            user.setUid(rs.getInt("uid"));
            user.setUname(rs.getString("uname"));
            user.setUpassword(rs.getString("upassword"));
            user.setUage(rs.getInt("uage"));
            Dept dept=new Dept();
            dept.setDid(rs.getInt("did"));
            dept.setDname(rs.getString("dname"));
            user.setDept(dept);
            return user;
        }
    };

    public List<User> search() throws SQLException {

        String sql="select u.*,d.* from t_user u inner join t_dept d on u.deptid=d.did";
        List<User> list=this.getJdbcTemplate().query(sql,rowMapper);
        return list;
    }

    public List<User> search(User user) {
        return null;
    }

    public User findById(int uid) {
        return null;
    }

    public void save(User user) {

    }

    public void update(User user) {

    }

    public void delete(int uid) {

    }
}

UserDaoJdbcSupport

package cn.yunhe.dao;

import cn.yunhe.entity.User;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcDaoSupport;

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

/**
 * Created by Administrator on 2017/7/17.
 */
public class UserDaoNamedSupport extends NamedParameterJdbcDaoSupport implements UserDao {
    public List<User> search() throws SQLException {
        return null;
    }

    public List<User> search(User user) {
        return null;
    }

    public User findById(int uid) {
        return null;
    }

    public void save(User user) {
        String sql = "insert into t_user (uname,upassword,uage,deptid) values(:uname,:upassword,:uage,:deptid)";
        Map params = new HashMap();
        params.put("uname", user.getUname());
        params.put("upassword",user.getUpassword());
        params.put("uage",user.getUage());
        params.put("deptid",user.getDept().getDid());

       int flag= getNamedParameterJdbcTemplate().update(sql,params);
        System.out.println("================="+flag);
    }

    public void update(User user) {

    }

    public void delete(int uid) {

    }
}

UserDaoNamedSupport

package cn.yunhe.dao;

import cn.yunhe.entity.Dept;
import cn.yunhe.entity.User;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by Administrator on 2017/7/14.
 */
@Component
public class UserDaoNamedTemplate implements UserDao {

    @Resource
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public static final RowMapper rowMapper =new ParameterizedRowMapper() {
        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
            User user=new User();
            user.setUid(rs.getInt("uid"));
            user.setUname(rs.getString("uname"));
            user.setUpassword(rs.getString("upassword"));
            user.setUage(rs.getInt("uage"));
            Dept dept=new Dept();
            dept.setDid(rs.getInt("did"));
            dept.setDname(rs.getString("dname"));
            user.setDept(dept);
            return user;
        }
    };

    public List<User> search() throws SQLException {


        return null;
    }

    public List<User> search(User user) {
        String sql="select u.*,d.* from t_user u inner join t_dept d on u.deptid=d.did where u.uname like :uname";
        Map params=new HashMap();
        params.put("uname","%"+user.getUname()+"%");
        List<User> list= namedParameterJdbcTemplate.query(sql,params,rowMapper);
        return list;
    }

    public User findById(int uid) {
        return null;
    }

    public void save(User user) {

    }

    public void update(User user) {

    }

    public void delete(int uid) {

    }

    public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
        return namedParameterJdbcTemplate;
    }

    public void setNamedParameterJdbcTemplate(NamedParameterJdbcTemplate namedParameterJdbcTemplate) {
        this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;
    }
}

UserDaoNamedTemplate

package cn.yunhe.dao;

import cn.yunhe.entity.Dept;
import cn.yunhe.entity.User;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * Created by Administrator on 2017/7/14.
 */
@Component
public class UserDaoTemplate implements UserDao{

    @Resource
    private JdbcTemplate jdbcTemplate;

    public static final RowMapper rowMapper= new ParameterizedRowMapper<User>() {
        public User mapRow(ResultSet rs, int rowNum) throws SQLException {
            User user=new User();
            user.setUid(rs.getInt("uid"));
            user.setUname(rs.getString("uname"));
            user.setUpassword(rs.getString("upassword"));
            user.setUage(rs.getInt("uage"));
            Dept dept=new Dept();
            dept.setDid(rs.getInt("did"));
            dept.setDname(rs.getString("dname"));
            user.setDept(dept);
            return user;
        }
    };

    public List<User> search() throws SQLException {
        String sql="select u.*,d.* from t_user u inner join t_dept d on u.deptid=d.did";
        List<User> list= jdbcTemplate.query(sql,rowMapper);
        return list;
    }

    public List<User> search(User user) {
        return null;
    }

    public User findById(int uid) {
        return null;
    }

    public void save(User user) {

    }

    public void update(User user) {

    }

    public void delete(int uid) {

    }

    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
}

UserDaoTemplate

Entity

package cn.yunhe.entity;

/**
 * Created by Administrator on 2017/7/14.
 */
public class User {
    private int uid;
    private String uname;
    private String upassword;
    private int uage;
    private Dept dept;

    public int getUid() {
        return uid;
    }

    public void setUid(int uid) {
        this.uid = uid;
    }

    public String getUname() {
        return uname;
    }

    public void setUname(String uname) {
        this.uname = uname;
    }

    public String getUpassword() {
        return upassword;
    }

    public void setUpassword(String upassword) {
        this.upassword = upassword;
    }

    public int getUage() {
        return uage;
    }

    public void setUage(int uage) {
        this.uage = uage;
    }

    public Dept getDept() {
        return dept;
    }

    public void setDept(Dept dept) {
        this.dept = dept;
    }
}

User

package cn.yunhe.entity;

/**
 * Created by Administrator on 2017/7/14.
 */
public class Dept {
    private int did;
    private String dname;

    public int getDid() {
        return did;
    }

    public void setDid(int did) {
        this.did = did;
    }

    public String getDname() {
        return dname;
    }

    public void setDname(String dname) {
        this.dname = dname;
    }
}

Dept

spring.xml

<?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:p="http://www.springframework.org/schema/p"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
    http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
    http://www.springframework.org/schema/context
    http://www.springframework.org/schema/context/spring-context-3.0.xsd
    http://www.springframework.org/schema/aop
    http://www.springframework.org/schema/aop/spring-aop-3.0.xsd">


    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" scope="singleton" >
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/spring"/>
        <property name="username" value="root"/>
        <property name="password" value="1234"/>
        <!--初始化连接数-->
        <property name="initialSize" value="5"/>
        <!--最大连接数-->
        <property name="maxActive" value="200"/>
        <!--最大闲置连接数-->
        <property name="maxIdle" value="10"/>
        <!--最小闲置连接数-->
        <property name="minIdle" value="1"/>
        <!--最大等待时间-->
        <property name="maxWait" value="30000"/>
    </bean>

    <!--自动扫描-->
    <context:component-scan base-package="cn.yunhe"/>

    <bean id="userDaoImpl" class="cn.yunhe.dao.UserDaoImpl">
        <property name="dataSource" ref="dataSource"/>
    </bean>

    <!--使用jdbcTemplate-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>

    <!--使用NamedParameterJdbcTemplate-->
    <bean id="namedParameterJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
        <constructor-arg ref="dataSource"/>
    </bean>

    <!--使用JdbcDaoSupport：使用父类获得jdbcTemplate 这样就不用在每个实体类中创建jdbcTemplate属性了 -->
    <bean id="userDaoJdbcSupport" class="cn.yunhe.dao.UserDaoJdbcSupport">
        <!--其实是将jdbcTempate注入到了JdbcDaoSupport中-->
        <property name="jdbcTemplate" ref="jdbcTemplate"/>
    </bean>

    <!--使用NamedParameterJdbcDaoSupport： 及使用父类 ，有使用命名传参的方式-->
    <bean id="userDaoNamedSupport" class="cn.yunhe.dao.UserDaoNamedSupport">
        <!--其实是将jdbcTemplate注入到了NamedParameterJdbcDaoSupport中去了-->
        <property name="jdbcTemplate" ref="jdbcTemplate"/>
    </bean>

</beans>

spring.xml

Demo

package cn.yunhe.test;

import cn.yunhe.dao.*;
import cn.yunhe.entity.Dept;
import cn.yunhe.entity.User;
import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import java.sql.SQLException;
import java.util.List;

/**
 * Created by Administrator on 2017/7/14.
 */
public class Demo  {
    ApplicationContext ac=null;
    @Before
    public void init(){
        ac=new ClassPathXmlApplicationContext("spring.xml");
    }

    @Test//普通方式查询
    public void test1() throws SQLException {
        UserDaoImpl userDao= (UserDaoImpl) ac.getBean("userDaoImpl");
        List<User> list= userDao.search();
        for (User user:list){
            System.out.println(user.getUname()+"	"+user.getDept().getDname());
        }
    }

    @Test//jdbcTemplate方式查询
    public void test2() throws SQLException {
        UserDaoTemplate template= (UserDaoTemplate) ac.getBean("userDaoTemplate");
        List<User> list=template.search();
        for (User user:list){
            System.out.println(user.getUname()+"	"+user.getDept().getDname());
        }
    }

    @Test//namedParameterJdbcTemplate
    public void test3(){
        UserDaoNamedTemplate userDaoNamedTemplate= (UserDaoNamedTemplate) ac.getBean("userDaoNamedTemplate");
        User user=new User();
        user.setUname("飞");
        List<User> list=userDaoNamedTemplate.search(user);
        for (User u:list){
            System.out.println(u.getUname()+"	"+u.getDept().getDname());
        }
    }

    @Test //使用jdbcDaoSupport
    public void test4() throws SQLException {
        UserDaoJdbcSupport userDaoJdbcSupport= (UserDaoJdbcSupport) ac.getBean("userDaoJdbcSupport");
        List<User> list=userDaoJdbcSupport.search();
        for (User user:list){
            System.out.println(user.getUname()+"	"+user.getDept().getDname());
        }
    }

    @Test//使用NamedParameterJdbcDaoSupport方式

    public void test5(){
        UserDaoNamedSupport userDaoNamedSupport= (UserDaoNamedSupport) ac.getBean("userDaoNamedSupport");
        User user=new User();
        user.setUname("诸葛亮");
        user.setUpassword("1234");
        user.setUage(123);
        Dept dept =new Dept();
        dept.setDid(3);
        user.setDept(dept);
        userDaoNamedSupport.save(user);
    }
}

Demo

Spring3 JDBC

Spring3 JDBC

Spring JDBC抽象框架

配置数据源

数据库连接

数据库连接池

数据源

DBCP数据库连接池

BasicDataSource

initialSize ：初始化连接数量，默认值为0

maxActive ：最大连接数,默认值为8

maxIdle：闲置连接的最大数量，默认为8

minIdle：闲置连接的最小数量，默认为0

maxWait：最大等待时间，以毫秒为单位。

minEvictableIdleTimeMillis ：闲置连接被逐出连接池的时间期限，以毫秒为单位，默认为30分钟

在Spring中使用基于连接池的本地数据源

使用Spring JDBC 模板

使用Spring对JDBC的Dao支持类

个人测试代码

　　　　Spring3 JDBC