Spring使用原生JDBC

Spring使用原生JDBC

为加深对Spring解耦的理解，本次实验学习用Spring连接JDBC

一、POM配置文件

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>fanghao</groupId>
    <artifactId>myspring</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>jar</packaging>

    <name>myspring</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>3.8.1</version>
            <scope>test</scope>
        </dependency>

        <!--Spring Context-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.0.5.RELEASE</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.9-rc</version>
        </dependency>

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

    </dependencies>
</project>

二、用户类

Customer.java

package newHello.customer;


public class Customer {
    private String name;
    private int age;

    public Customer(String name, int age) {
        this.name = name;
        this.age = age;

    }

    @Override
    public String toString() {
        return "Customer{" +
                "name=" + name +
                ", age=" + age +
                '}';
    }


    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }
}

三、用户DAO接口

CustomerDAO.java

package newHello.customer;

/**
 * DAO(Data Access Object)数据存取对象
 */
public interface CustomerDAO {
    void insert(Customer customer);

    Customer findByCustomerId(int custId);
}

四、用户DAO接口实现类

package newHello.customer;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JdbcCustomerDAO implements CustomerDAO {
    private DataSource dataSource;

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

    /**
     * 使用prepareStatement拼接SQL
     * @param customer 获取输入对象的属性
     */
    public void insert(Customer customer) {
        String sql = "insert into CUSTOMER " +
                "(NAME, AGE) VALUES(?, ?)";
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, customer.getName());
            ps.setInt(2, customer.getAge());
            ps.executeUpdate();
            ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null)
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
        }
    }

    public Customer findByCustomerId(int custId) {
        String sql = "select * from CUSTOMER where CUST_ID = ?";
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, custId);
            Customer customer = null;
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                customer = new Customer(
                        rs.getString("NAME"),
                        rs.getInt("Age")
                );
            }
            rs.close();
            ps.close();
            return customer;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return null;
    }
}

五、资源文件

资源文件主要做配置，管理java bean

➜  resources tree .
.
├── Spring-Customer.xml
├── Spring-Datasource.xml
└── Spring-Module.xml

0 directories, 3 files

5.1 Spring-Customer.xml

<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-2.5.xsd">

    <!--配置用户接口的实现类JdbcCustomerDAO-->
    <bean id="customerDAO" class="newHello.customer.JdbcCustomerDAO">
        <property name="dataSource" ref="dataSource" />
    </bean>
</beans>

5.2 Spring-Datasource.xml

<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-2.5.xsd">

    <!--配置数据库驱动-->
    <bean id="dataSource"
          class="org.springframework.jdbc.datasource.DriverManagerDataSource">

        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
        <property name="url" value="jdbc:mysql://localhost:3306/test" />
        <property name="username" value="root" />
        <property name="password" value="mysql123" />
    </bean>

</beans>

5.3 Spring-Module.xml

<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-2.5.xsd">

    <!--管理要导入的XML配置文件-->
    <import resource="Spring-Datasource.xml" />
    <import resource="Spring-Customer.xml" />
</beans>

六、应用启动程序

App.java

package newHello.customer;

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

public class App {
    public static void main(String[] args) {
        // 根据XML配置文件生成应用上下文
        ApplicationContext context =
                new ClassPathXmlApplicationContext("Spring-Module.xml");


        // 读取customerDAO的JavaBean，转型为customerDAO
        CustomerDAO customerDAO = (CustomerDAO) context.getBean("customerDAO");

        // 用封装好DAO进行类似ORM的操作
        customerDAO.insert(new Customer("Jack", 21));
        customerDAO.insert(new Customer("Tom", 24));
        customerDAO.insert(new Customer("Jane", 25));

        Customer customer = customerDAO.findByCustomerId(2);
        System.out.println(customer);
    }
}

七、运行结果

Customer{name=Tom, age=24}