首页 > 代码库 > Spring3 JDBC

Spring3 JDBC

 

                                         Spring3 JDBC

 

 

Spring JDBC抽象框架

  1. 定义数据库连接参数
  2. 打开数据库连接
  3. 声明SQL语句
  4. 预编译并执行SQL语句
  5. 遍历查询结果(如果需要的话)
  6. 处理每一次遍历操作
  7. 处理抛出的任何异常
  8. 处理事务
  9. 关闭数据库连接

配置数据源

  • 数据库连接

  • 数据库连接池

  • 数据源

  1. 由JDBC驱动程序定义的数据源
  2. 基于连接池的JNDI数据源
  3. 基于连接池的本地数据源
  • 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()+"\t"
+emp.getEname()+"\t"
+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()+"\t"+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()+"\t"+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()+"\t"+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()+"\t"+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