[Spring学习笔记 6 ] Spring JDBC 详解

 项目使用maven管理，pom.xml和项目组织如下：

<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/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.amos.spring</groupId>
    <artifactId>Lspring_JDBC</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>

    <name>Lspring_JDBC</name>
    <url>http://maven.apache.org</url>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

    <dependencies>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.2</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>3.2.4.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>3.2.4.RELEASE</version>
        </dependency>
        <dependency>
            <groupId> org.aspectj</groupId>
            <artifactId> aspectjweaver</artifactId>
            <version> 1.6.11</version>
        </dependency>
        <dependency>
            <groupId>commons-dbcp</groupId>
            <artifactId>commons-dbcp</artifactId>
            <version>1.2.2</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.24</version>
        </dependency>

    </dependencies>
</project>

逻辑步骤如下：

1.直接看代码：传统操作数据库方式

package com.amos.spring.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;

/** 
* @ClassName: 传统的操作数据库的方式  
* @Description: TODO
* @author: amosli
* @email:amosli@infomorrow.com
* @date Nov 28, 2013 2:03:23 AM  
*/
public class DbUtil {
    private static DataSource datasource;
    static {
        // 初始化连接池
        BasicDataSource dSource = new BasicDataSource();
        // 设置连接池的属性
        dSource.setDriverClassName("com.mysql.jdbc.Driver");
        dSource.setUrl("jdbc:mysql:///spring_learn");
        dSource.setUsername("root");
        dSource.setPassword("root");
        datasource = dSource;
    }

    public static Connection getConn() throws SQLException {
        return datasource.getConnection();
    }

    public static void close(ResultSet rs, Statement stmt, Connection conn) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

2.定义一个person接口

package com.amos.spring.dao;

import java.util.List;

import com.amos.spring.model.Person;

/** 
* @ClassName: IpersonDao 
* @Description: TODO
* @author: amosli
* @email:amosli@infomorrow.com
* @date Nov 27, 2013 12:35:48 AM  
*/
public interface IpersonDao {
    void save(Person p);

    void update(Long id, Person p);

    void delete(Long id);

    List<Person> loadAll();

}

3.使用最原始的操作数据库方式

package com.amos.spring.impl;

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

import com.amos.spring.dao.DbUtil;
import com.amos.spring.dao.IpersonDao;
import com.amos.spring.model.Person;

/** 
* @ClassName: PersonDaoImplJdbcOld 
* @Description: 最原始的操作
* @author: amosli
* @email:amosli@infomorrow.com
* @date Nov 28, 2013 12:15:20 AM  
*/
public class PersonDaoImplJdbcOld implements IpersonDao {

    public void save(Person p) {
        // 获得连接
        // 获得一个连接Connection
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = DbUtil.getConn();
            // 开取事务
            conn.setAutoCommit(false);
            // 初始化相关的Statment对象
            stmt = conn.createStatement();
            String sql = "insert into person(name,age) values('" + p.getName() + "'," + p.getAge() + ")";
            stmt.executeUpdate(sql);
            // 提交事务
            conn.commit();
        } catch (Exception e) {
            try {
                if (conn != null)
                    conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            DbUtil.close(null, stmt, conn);
        }
    }

    public void update(Long id, Person p) {
        // 获得一个连接Connection
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = DbUtil.getConn();
            // 开取事务
            conn.setAutoCommit(false);
            // 初始化相关的Statment对象
            stmt = conn.createStatement();
            String sql = "update person set name = '" + p.getName() + "',age='" + p.getAge() + "' where id='" + p.getId() + "'";
            stmt.executeUpdate(sql);
            // 提交事务
            conn.commit();
        } catch (Exception e) {
            try {
                if (conn != null)
                    conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            DbUtil.close(null, stmt, conn);
        }
    }

    public void delete(Long id) {
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = DbUtil.getConn();
            // 开取事务
            conn.setAutoCommit(false);
            // 初始化相关的Statment对象
            stmt = conn.createStatement();
            String sql = "delete person where id='"+id+"'";
            stmt.executeUpdate(sql);
            // 提交事务
            conn.commit();
        } catch (Exception e) {
            try {
                if (conn != null)
                    conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            DbUtil.close(null, stmt, conn);
        }
    }

    public List<Person> loadAll() {
        // TODO Auto-generated method stub
        return null;
    }

}

4.发现有很多冗余的地方，关于数据库的开启关闭都是相同的，怎么抽象出来共有的地方？？？

定义一个接口，使用内部类实现这个接口，然后调用这个方法。

package com.amos.spring.impl;

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

import com.amos.spring.dao.DbUtil;
import com.amos.spring.dao.IpersonDao;
import com.amos.spring.model.Person;

/**
 * @ClassName: PersonDaoImplJdbc
 * @Description: 把相同的操作封装成一个接口，用内部 类实现接口
 * @author: amosli
 * @email:amosli@infomorrow.com
 * @date Nov 27, 2013 11:37:50 PM
 */
public class PersonDaoImplJdbc implements IpersonDao {

    interface UpdateOperation {
        /**
         * 把各种各样的操作封装成一个接口
         * 
         * @param stmt
         * @throws SQLException
         */
        void execute(Statement stmt) throws SQLException;
    }

    /**
     * 执行数据库操作
     */
    public void excuteUpdate(UpdateOperation operation) {
        // 获得一个连接Connection
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = DbUtil.getConn();
            // 开取事务
            conn.setAutoCommit(false);
            // 初始化相关的Statment对象
            stmt = conn.createStatement();
            // 执行具体的操作
            operation.execute(stmt);
            // 提交事务
            conn.commit();
        } catch (Exception e) {
            try {
                if (conn != null)
                    conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            DbUtil.close(null, stmt, conn);
        }
    }

    public void save(final Person p) {
        // save中就只有核心的业务代码了
        excuteUpdate(new UpdateOperation() {
            public void execute(Statement stmt) throws SQLException {
                String sql = "insert into person(name,age) values('" + p.getName() + "'," + p.getAge() + ")";
                stmt.executeUpdate(sql);
            }
        });
    }

    public void update(final Long id, final Person p) {
        excuteUpdate(new UpdateOperation() {
            public void execute(Statement stmt) throws SQLException {
                String sql = "update person set name = '" + p.getName() + "',age='" + p.getAge() + "' where id='" + id + "'";
                stmt.executeUpdate(sql);
            }
        });
    }

    public void delete(final Long id) {
        excuteUpdate(new UpdateOperation() {
            public void execute(Statement stmt) throws SQLException {
                String sql = "delete person where id='" + id + "'";
                stmt.executeUpdate(sql);
            }
        });

    }

    public List<Person> loadAll() {
        return null;
    }

}

5.怎么才能进一步优化代码？？？那就把刚才核心代码抽象成一个类，不仅person可以使用其他类也可以使用

如下代码：

package com.amos.spring.impl;

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

import com.amos.spring.dao.DbUtil;
import com.amos.spring.dao.IpersonDao;
import com.amos.spring.model.Person;

/**
 * @ClassName: PersonDaoImplJdbcTemplate
 * @Description: 代码继续优化,把实现接口的方法提取出来
 * @author: amosli 
 * @email:amosli@infomorrow.com
 * @date Nov 27, 2013 11:51:06 PM
 */
public class PersonDaoImplJdbcTemplate implements IpersonDao {

    interface UpdateOperation {
        /**
         * 把各种各样的操作封装成一个接口
         * 
         * @param stmt
         * @throws SQLException
         */
        void execute(Statement stmt) throws SQLException;
    }

    /**
     * 执行数据库操作
     */
    public void excuteUpdate(UpdateOperation operation) {
        // 获得一个连接Connection
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = DbUtil.getConn();
            // 开取事务
            conn.setAutoCommit(false);
            // 初始化相关的Statment对象
            stmt = conn.createStatement();
            // 执行具体的操作
            operation.execute(stmt);
            // 提交事务
            conn.commit();
        } catch (Exception e) {
            try {
                if (conn != null)
                    conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            DbUtil.close(null, stmt, conn);
        }
    }

    public void excuteSql(final String sql) {
        excuteUpdate(new UpdateOperation() {
            public void execute(Statement stmt) throws SQLException {
                stmt.executeUpdate(sql);
            }
        });
    }

    public void save(final Person p) {
        // save中就只有核心的业务代码了
        String sql = "insert into person(name,age) values('" + p.getName() + "'," + p.getAge() + ")";
        excuteSql(sql);
    }

    public void update(final Long id, final Person p) {
        String sql = "update person set name = '" + p.getName() + "',age='" + p.getAge() + "' where id='" + id + "'";
        excuteSql(sql);
    }

    public void delete(final Long id) {
        String sql = "delete person where id='" + id + "'";
        excuteSql(sql);
    }

    public List<Person> loadAll() {
        return null;
    }

}

6.写个testcase测试下吧：

package com.amos.spring.dao;

import com.amos.spring.impl.PersonDaoImplJdbcTemplateBest;
import com.amos.spring.model.Person;

/** 
* @ClassName: PersonDaoTest 
* @Description: 把关于数据库操作的类封装起来进行调用
* @author: amosli
* @email:amosli@infomorrow.com
* @date Nov 28, 2013 1:59:04 AM  
*/
public class PersonDaoTest {
    private static IpersonDao dao;

    public static void main(String args[]) {
        dao = new PersonDaoImplJdbcTemplateBest();
        Person person = new Person();
        person.setName("运哥");
        person.setAge(29);
        dao.save(person);
    }
}

然后去查看数据库即可。

数据库非常简单，person数据库生成代码：

DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(1000) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=gbk;

-- ----------------------------
-- Records of person
-- ----------------------------
INSERT INTO `person` VALUES ('1', '运哥', '23');

7.使用spring 又该如何呢？使用JdbcTemplate模板

package com.amos.spring.impl;

import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;

import com.amos.spring.dao.IpersonDao;
import com.amos.spring.model.Person;

/** 
* @ClassName: SpringJdbcTemplateBest 
* @Description: 使用spring 框架进行操作数据库
* @author: amosli
* @email:amosli@infomorrow.com
* @date Nov 28, 2013 1:01:27 AM  
*/
public class SpringJdbcTemplateBest implements IpersonDao {
    private JdbcTemplate jdbcTemplate;
    public List<Person> loadAll() {
        return null;
    }

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

    public void save(Person p) {
        String sql = "insert into person(name,age) values('" + p.getName() + "'," + p.getAge() + ")";
        System.out.println("save:" + sql);
        jdbcTemplate.update(sql);
    }

    public void update(Long id, Person p) {
        String sql = "update person set name = '" + p.getName() + "',age='" + p.getAge() + "' where id='" + id + "'";
        jdbcTemplate.update(sql);
    }

    public void delete(Long id) {
        String sql = "delete person where id='" + id + "'";
        jdbcTemplate.update(sql);
    }

}

8.配置bean.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:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
               http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
               http://www.springframework.org/schema/context
               http://www.springframework.org/schema/context/spring-context-3.2.xsd
               ">
    <context:property-placeholder location="db.properties" />
    <!-- 配置连接池 -->
    <bean id="mydataSource" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="${db.driverClassName}"></property>
        <property name="url" value="${db.url}"></property>
        <property name="username" value="${db.username}"></property>
        <property name="password" value="${db.password}"></property>
    </bean>
    <!-- 配置jdbctemplate -->
    <bean id="myjdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="mydataSource"></property>
    </bean>
    <bean id="personDao" class="com.amos.spring.impl.SpringJdbcTemplateBest">
        <property name="jdbcTemplate" ref="myjdbcTemplate"></property>
    </bean>
</beans>

db.properties:

db.driverClassName=com.mysql.jdbc.Driver
db.url=jdbc:mysql:///spring_learn
db.username=root
db.password=root

9.写个testcase测试一下：

package com.amos.spring.dao;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.amos.spring.model.Person;

public class SpringPersonDaoTest {
    private static IpersonDao dao;
//    @Test
    public static void main(String args[]){
        ApplicationContext acx = new ClassPathXmlApplicationContext("bean.xml");
        dao = acx.getBean(IpersonDao.class);
        Person person = new Person();
        person.setName("运哥");
        person.setAge(33);
        dao.save(person);
    }
}
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/amosli/p/3446915.html