[学习记录]Spring JDBC实例

Spring JDBC实例

在使用之前我已经创建了一个spring项目，教程连接CSDN.

原生态JDBC查询

1.在pom.xml中添加依赖

<dependency>
	<groupId>org.springframework</groupId>
	<artifactId>spring-jdbc</artifactId>
	<version>4.3.0.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.1</version>
</dependency>

2.配置spring-conf.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:context="http://www.springframework.org/schema/context"
       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
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context.xsd">

    <context:annotation-config/><!-- 开启注解扫描 -->

    <bean class="org.springframework.jdbc.datasource.DriverManagerDataSource" id="dataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
        <property name="url" value="jdbc:mysql://localhost:3306/test?characterEncoding=utf-8" />
        <property name="username" value="root" />
        <property name="password" value="root" />
    </bean>
    <bean class="com.spring.dao.impl.CustomerDaoImpl" id="customerDao" />

</beans>

4.创建一个Customer表,并插入两条数据

CREATE TABLE customer(
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NULL,
  `age` int(0) NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO `test`.`customer`(`name`, `age`) VALUES ('李四', 20);
INSERT INTO `test`.`customer`(`name`, `age`) VALUES ('张三', 24);

5.创建Customer实体类

package com.spring.entity;

public class Customer {
    private Integer id;
    private String name;
    private Integer age;

  	//此处省略get、set、toString方法
}

6.创建CustomerDao

package com.spring.dao;

import com.spring.entity.Customer;

public interface CustomerDao {
    public Customer findCustomer();
}

7.创建CustomerDaoImpl

package com.spring.dao.impl;

import com.spring.dao.CustomerDao;
import com.spring.entity.Customer;
import org.springframework.beans.factory.annotation.Autowired;

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;

public class CustomerDaoImpl implements CustomerDao {
    @Autowired
    private DataSource dataSource;

    public List<Customer> findCustomer() {
        //sql语句
        String sql = "select * from customer";
        Connection conn = null;
        try {
            //创建数据库连接
            conn = dataSource.getConnection();
            //执行sql语句
            PreparedStatement ps = conn.prepareStatement(sql);
            //用来存放Customer对象的数组
            List<Customer> list = new ArrayList<Customer>();
            //返回结果
            ResultSet rs = ps.executeQuery();
            while (rs.next()){
                Customer c = new Customer();
                c.setId(rs.getInt("id"));
                c.setName(rs.getString("name"));
                c.setAge(rs.getInt("age"));
                list.add(c);
            }
            return list;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {}
            }
        }

    }
}

8.测试结果

package com.spring.app;

import com.spring.dao.CustomerDao;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class SpringJDBCTest {
    public static void main(String[] args) {
        ApplicationContext ac =  new ClassPathXmlApplicationContext("spring-conf.xml");
        CustomerDao customerDao = (CustomerDao) ac.getBean("customerDao");
        System.out.println(customerDao.findCustomer());
    }
}

输出结果：

[Customer{id=1, name='张三', age=24}, Customer{id=2, name='李四', age=20}]

使用JdbcTemplate

1.创建CustomerDaoImpl2

import com.spring.dao.CustomerDao;
import com.spring.entity.Customer;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.ArrayList;
import java.util.List;

public class CustomerDaoImpl2 implements CustomerDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    public List<Customer> findCustomer() {
        String sql = "select * from customer";
        BeanPropertyRowMapper<Customer> rowMapper = new BeanPropertyRowMapper<Customer>(Customer.class);
        List<Customer> customers = jdbcTemplate.query(sql, rowMapper);
        return customers;
    }

2.spring-conf.xml

添加以下内容

<bean class="org.springframework.jdbc.core.JdbcTemplate" id="jdbcTemplate">
 	<property name="dataSource" ref="dataSource" />
</bean>
<bean class="com.spring.dao.impl.CustomerDaoImpl2" id="customerDao2" />

3.测试

package com.spring.app;

import com.spring.dao.CustomerDao;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class SpringJDBCTest {
    public static void main(String[] args) {
        ApplicationContext ac =  new ClassPathXmlApplicationContext("spring-conf.xml");
        CustomerDao customerDao = (CustomerDao) ac.getBean("customerDao2");
        System.out.println(customerDao.findCustomer());
    }
}

从上面看出来，使用jdbc模板语法会使代码精简很多，还是推荐使用JdbcTemplate

下面演示使用JdbcTemplate进行增删改

CustomerDao添加

    //增加
    public void insert(Customer customer);
    //修改
    public void update(Customer customer);
    //删除
    public void delete(Integer id);

增加数据

CustomerDaoImpl2

    public void insert(Customer customer) {
        String sql = "insert into customer(name ,age) values(?,?)";
        jdbcTemplate.update(sql,customer.getName(),customer.getAge());
    }

修改数据

    public void update(Customer customer) {
        String sql = "update customer set age = ? where id = ?";
        jdbcTemplate.update(sql,customer.getAge(),customer.getId());
    }

删除数据

    public void delete(Integer id) {
        String sql = "delete from customer where id = ?";
        jdbcTemplate.update(sql,id);
    }