JDBC与Spring JDBC模板-优快云博客

传统方式访问数据库

JDBC
Spring JDBCTemplate
弊端分析

1）新建一Maven项目

maven工程的目录结构
        添加依赖
            <!--mysql依赖-->
            <dependency>
              <groupId>mysql</groupId>
              <artifactId>mysql-connector-java</artifactId>
              <version>5.1.44</version>
            </dependency>

            <!--junit依赖-->
            <dependency>
              <groupId>junit</groupId>
              <artifactId>junit</artifactId>
              <version>4.12</version>
              <scope>test</scope>
            </dependency>

JDBC

主要设计到【Connection、Statement、ResultSet】，以一个testCase进行讲解：

数据表准备
CREATE DATABASE spring_data;

USE spring_data;

CREATE TABLE student (
  id   INT         NOT NULL AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL,
  age  INT         NOT NULL,
  PRIMARY KEY (id)
);

DESC student;

INSERT INTO student (name, age) VALUES ("zhangsan", 20);
INSERT INTO student (name, age) VALUES ("lisi", 21);
INSERT INTO student (name, age) VALUES ("wangwu", 22);

SELECT * FROM student;

MySQLUtil.java工具类的设计

package com.imooc.util;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;

/**
 * Created by zghgchao 2017/12/29 13:56
 * JDBCUtil工具类：
 * 1）获取Connection
 * 2）释放资源
 */
public class JDBCUtil {

//    private static final String USERNAME = "root";
//    private static final String PASSWORD = "root";
//    private static final String DRIVERCLASS = "com.mysql.jdbc.Driver";
//    private static final String URL = "jdbc:mysql://172.17.66.51:3306/spring_data";


    /**
     * 数据库连接
     * 1）获取Connection
     *
     * @return
     */
    public static Connection getConnection() throws Exception {
        InputStream inputStream = JDBCUtil.class.getClassLoader().getResourceAsStream("db.properties");
        Properties properties = new Properties();
        properties.load(inputStream);

        String USERNAME = properties.getProperty("jdbc.user");
        String PASSWORD = properties.getProperty("jdbc.password");
        String DRIVERCLASS = properties.getProperty("jdbc.driver");
        String URL = properties.getProperty("jdbc.url");

        Connection connection = null;

        try {
            Class.forName(DRIVERCLASS);

            connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);

        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("--------------数据库连接异常-------------");
        }
        System.out.println("--------------数据库连接成功-------------");
        return connection;
    }

    /**
     * 释放资源
     *
     * @param connection
     * @param patmt
     * @param rs
     */
    public static void release(Connection connection, PreparedStatement patmt, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
                System.out.println("--------------ResultSet关闭异常-------------");
            }
        }
        if (patmt != null) {
            try {
                patmt.close();
            } catch (Exception e) {
                e.printStackTrace();
                System.out.println("--------------PreparedStatement关闭异常-------------");
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (Exception e) {
                e.printStackTrace();
                System.out.println("--------------Connection关闭异常-------------");
            }
        }
        System.out.println("--------------数据库资源已关闭-------------");
    }
}

db.properties

jdbc.url=jdbc:mysql://172.17.66.51:3306/spring_data
jdbc.user=root
jdbc.password=root
jdbc.driver=com.mysql.jdbc.Driver

测试MySQLUtil工具类：

package com.imooc.util;

import org.junit.Assert;
import org.junit.Test;

import java.sql.Connection;

import static org.junit.Assert.*;

/**
 * Created by zghgchao 2017/12/29 14:00
 */
public class JDBCUtilTest {
    @Test
    public void getConnection() throws Exception {
        Connection connection = JDBCUtil.getConnection();
        JDBCUtil.release(connection,null,null);
        Assert.assertNotNull(connection);
    }

}

DAO的实现

package com.imooc.dao.impl;

import com.imooc.dao.StudentDAO;
import com.imooc.domain.Student;
import com.imooc.util.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by zghgchao 2017/12/29 14:54
 * StudentDAO访问接口实现类：通过最原始的JDBC的方式操作
 */
public class StudentDAOImpl implements StudentDAO {

    public List<Student> query() {

        List<Student> students = new ArrayList<Student>();

        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        String sql = "select id,name,age from student";

        try {
            connection = JDBCUtil.getConnection();
            pstmt = connection.prepareStatement(sql);
            rs = pstmt.executeQuery();

            Student student = null;
            while (rs.next()) {
                student = new Student(rs.getInt("id"), rs.getString("name"), rs.getInt("age"));
                students.add(student);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(connection, pstmt, rs);
        }

        return students;
    }

    public void save(Student student) {

        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        String sql = "INSERT INTO student (name, age) VALUES (?, ?)";
        try {
            connection = JDBCUtil.getConnection();
            pstmt = connection.prepareStatement(sql);
            pstmt.setString(1,student.getName());
            pstmt.setInt(2,student.getAge());
            pstmt.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.release(connection,pstmt,rs);
        }
    }

}

DAO测试

package com.imooc.dao.impl;

import com.imooc.dao.StudentDAO;
import com.imooc.domain.Student;
import org.junit.Test;

import java.util.List;

import static org.junit.Assert.*;

/**
 * Created by zghgchao 2017/12/29 15:07
 */
public class StudentDAOImplTest {
    StudentDAO studentDAO = new StudentDAOImpl();

    @Test
    public void save() throws Exception {
        Student student = new Student();
        student.setName("Tom");
        student.setAge(25);
        studentDAO.save(student);
    }

    @Test
    public void query() throws Exception {

        List<Student> list = studentDAO.query();
        for (Student student : list) {
            System.out.println(student.toString());
        }
    }

}

Spring JdbcTemplate

Maven依赖

添加依赖
        <dependency>
          <groupId>org.springframework</groupId>
          <artifactId>spring-jdbc</artifactId>
          <version>4.3.11.RELEASE</version>
        </dependency>

        <dependency>
          <groupId>org.springframework</groupId>
          <artifactId>spring-context</artifactId>
          <version>4.3.11.RELEASE</version>
        </dependency>

配置beans.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"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans.xsd">

    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
        <property name="url" value="jdbc:mysql://172.17.66.51:3306/spring_data"/>
    </bean>

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

    <bean id="studentDAO" class="com.imooc.dao.impl.StudentDAOSpringJdbcImpl">
        <property name="jdbcTemplate" ref="jdbcTemplate"/>
    </bean>
</beans>

测试DataBase，jdbcTemplate

package com.imooc;

import org.junit.After;
import org.junit.Assert;
import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;

/**
 * Created by zghgchao 2017/12/29 15:36
 * DataSour测试类
 */
public class DataSourceTest {
    private ApplicationContext ctx = null;

    @Before
    public void setup() {
        ctx = new ClassPathXmlApplicationContext("beans.xml");
        System.out.println("------------------setup---------------------");
    }

    @After
    public void tearDown() {
        ctx = null;
        System.out.println("------------------tearDown---------------------");
    }

    @Test
    public void testDataSource() {
        System.out.println("------------------testDataSource---------------------");
        DataSource dataSource = (DataSource) ctx.getBean("dataSource");
        Assert.assertNotNull(dataSource);
    }

    @Test
    public void testJdbcTemplate() {
        System.out.println("------------------testJdbcTemplate---------------------");
        JdbcTemplate jdbcTemplate = (JdbcTemplate) ctx.getBean("jdbcTemplate");
        Assert.assertNotNull(jdbcTemplate);
    }
}

Dao接口实现

package com.imooc.dao.impl;

import com.imooc.dao.StudentDAO;
import com.imooc.domain.Student;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;

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

/**
 * Created by zghgchao 2017/12/29 15:52
 * StudentDAO访问接口实现类：通过Spring jdbc的方式操作
 */
public class StudentDAOSpringJdbcImpl implements StudentDAO{

    private JdbcTemplate jdbcTemplate;

    public List<Student> query() {
        final List<Student> students = new ArrayList<Student>();
        String sql = "select id,name,age from student";

        jdbcTemplate.query(sql, new RowCallbackHandler() {
            public void processRow(ResultSet rs) throws SQLException {
                Student student = new Student(rs.getInt("id"), rs.getString("name"), rs.getInt("age"));
                students.add(student);
            }
        });
        return students;
    }

    public void save(Student student) {
        String sql = "INSERT INTO student (name, age) VALUES (?, ?)";
        jdbcTemplate.update(sql,new Object[]{student.getName(),student.getAge()});
    }

    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

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

Dao测试

package com.imooc.dao.impl;

import com.imooc.dao.StudentDAO;
import com.imooc.domain.Student;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import java.util.List;

import static org.junit.Assert.*;

/**
 * Created by zghgchao 2017/12/29 16:05
 */
public class StudentDAOSpringJdbcImplTest {

    private ApplicationContext ctx = null;

    private StudentDAO studentDAO = null;

    @Before
    public void setup() {
        ctx = new ClassPathXmlApplicationContext("beans.xml");
        studentDAO = (StudentDAO) ctx.getBean("studentDAO");
        System.out.println("------------------setup---------------------");
    }

    @After
    public void tearDown() {
        ctx = null;
        System.out.println("------------------tearDown---------------------");
    }

    @Test
    public void query() throws Exception {
        List<Student> list = studentDAO.query();
        for (Student student : list) {
            System.out.println(student.toString());
        }
    }

    @Test
    public void save() throws Exception {
        Student student = new Student();
        student.setName("Jock");
        student.setAge(26);
        studentDAO.save(student);
    }

}

总结----传统方式访问数据库的弊端

1. DAO has many many code Dao层有许多的代码
2. DAOImpl has many duplicate code DaoImpl(dao层接口实现类)有许多冗余重复的代码
3. Develop the page and other functions 不利于页面开发和其他的功能函数开发

【https://gitee.com/robei/SpringDataProject】