spring 数据库操作之JDBC(05)

2017-05-18 本文已影响57人小Q逛逛

Spring JDBC模板

一. 单独使用jdbc

Java Database Connectivity(JDBC) 是Java api标准的一部分.是用来Java开发语言和多数数据库交换.JDBC是编程接口,供Java开发者用来访问数据库.JDBC提供了很多方法用来查询,更新,和删除数据库数据,JDBC库主要提供的接口使用步骤

创建数据库链接
创建SQL声明
执行SQL语句
查看或者数据结果集
关闭链接

下面是一个使用JDBC操作数据库的简单例子

创建数据库spring_jdbc和表employee

create table employee(
    id int primary key auto_increment,
    name varchar(32)
);

创建Java工程,导入连接MySQL的jar包:mysql-connector-java-5.1.7-bin.jar

Entity:

Employee.java

package com.it.entity;

public class Employee {
    private int id;
    private String name;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Employee(int id, String name) {
        super();
        this.id = id;
        this.name = name;
    }
    
    @Override
    public String toString() {
        // TODO Auto-generated method stub
        return this.id+"==>"+this.name;
    }
}

接口EmployeeDao.java

package com.it.dao;

import java.io.Serializable;
import com.it.entity.Employee;

public interface EmployeeDao {
    Employee getEmployeeById(Serializable id);
    //void createEmployee();
    void insertEmployee(Employee employee);
}

接口EmployeeDao的实现类:EmployeeDaoImpl.java

package com.it.dao.impl;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import com.it.dao.EmployeeDao;
import com.it.entity.Employee;

public class EmployeeDaoImpl implements EmployeeDao{
    private String url = "jdbc:mysql://localhost:3306/spring_jdbc?useUnicode=true&characterEncoding=utf-8";
    private String user = "root";
    private String password = "1l9o9v0e";
    private String className = "com.mysql.jdbc.Driver";
    
    @Override
    public Employee getEmployeeById(Serializable id) {
        // TODO Auto-generated method stub
        Connection connection = null;
        Employee employee = null;
        
        try {
            Class.forName(className);
            connection = DriverManager.getConnection(url, user, password);
            PreparedStatement statement = connection.prepareStatement(""
                    + "select id,name from employee where id=?");
            statement.setInt(1, (int) id);
            ResultSet rs = statement.executeQuery();
            if (rs.next()) {
                employee = new Employee(rs.getInt("id"), rs.getString("name"));
            }
            rs.close();
            statement.close();
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally {
            if (connection!=null) {
                try {
                    connection.close();
                } catch (Exception e2) {
                    // TODO: handle exception
                    throw new RuntimeException(e2);
                }
            }
        }
        
        return employee;
    }

    @Override
    public void insertEmployee(Employee employee) {
        // TODO Auto-generated method stub
        Connection connection = null;
        try {
            
            Class.forName(className);
            connection = DriverManager.getConnection(url, user, password);
            PreparedStatement statement = connection.prepareStatement("insert into employee(name) values(?)");
            
            statement.setString(1, employee.getName());
            statement.execute();
                    
            statement.close();
            
        } catch (Exception e) {
            // TODO: handle exception
            throw new RuntimeException(e);
        }finally {
            if (connection!=null) {
                try {
                    connection.close();
                } catch (Exception e2) {
                    // TODO: handle exception
                    throw new RuntimeException(e2);
                }
            }
        }
    }

}

测试JDBC操作数据库:JdbcDemo.java

package com.it.a_jdbc;

import org.junit.Test;

import com.it.dao.EmployeeDao;
import com.it.dao.impl.EmployeeDaoImpl;
import com.it.entity.Employee;

public class JdbcDemo {

    private EmployeeDao employeeDao = new EmployeeDaoImpl();
    
    @Test
    public void testJdbcInsert() {
        Employee employee = new Employee(2, "大佬");
        employeeDao.insertEmployee(employee);
        System.out.println("insert success!");
    }
    
    @Test
    public void testJdbcGet() {
        Employee emp = employeeDao.getEmployeeById(1);
        System.out.println(emp);
    
    }
    
}

运行测试方法,数据库会增加近name='大佬'的数据,同样可以获取ID为1的数据

二. Spring 对JDBC的支持

在前面的小例子,我们没有引入spring相关的方法,实现了一个Java类进行DAO操作:使用JDBC连接到数据库,进行数据操作.在下面的内容,将会学习spring框架通过去除重复代码,让我们的工作更加简单.

使用 spring JdbcTemplate查找数据库

2.1 引入spring-JDBC相关jar包

c3p0-0.9.1.2.jar
commons-logging-1.1.3.jar
mysql-connector-java-5.1.7-bin.jar
spring-aop-4.1.6.RELEASE.jar
spring-aspects-4.1.6.RELEASE.jar
spring-beans-4.1.6.RELEASE.jar
spring-context-4.1.6.RELEASE.jar
spring-core-4.1.6.RELEASE.jar
spring-expression-4.1.6.RELEASE.jar
spring-jdbc-4.1.6.RELEASE.jar
spring-tx-4.1.6.RELEASE.jar

2.2 src目录下创建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:p="http://www.springframework.org/schema/p"
    xmlns:context="http://www.springframework.org/schema/context"
    xmlns:tx="http://www.springframework.org/schema/tx"
    xmlns:aop="http://www.springframework.org/schema/aop"
    xmlns:mvc="http://www.springframework.org/schema/mvc"
    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
        http://www.springframework.org/schema/tx
        http://www.springframework.org/schema/tx/spring-tx.xsd
        http://www.springframework.org/schema/aop
        http://www.springframework.org/schema/aop/spring-aop.xsd
        http://www.springframework.org/schema/mvc
        http://www.springframework.org/schema/mvc/spring-mvc.xsd">
        
        
        
        <!-- 数据源对象:c3p0连接池 -->
        <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
            <property name="driverClass" value="com.mysql.jdbc.Driver"></property>
            <property name="jdbcUrl" value="jdbc:mysql:///spring_jdbc?useUnicode=true&characterEncoding=utf8"></property>
            <property name="user" value="root"></property>
            <property name="password" value="1l9o9v0e"></property>
            <property name="initialPoolSize" value="3"></property>
            <property name="maxPoolSize" value="10"></property>
            <property name="maxStatements" value="100"></property>
            <property name="acquireIncrement" value="2"></property>
        
        </bean>
        
        
        <!-- jdbcTemplate工具类实例 -->
        <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
            <property name="dataSource" ref="dataSource"></property>
        </bean>
        
        <bean id="userDao" class="com.it.dao.impl.UserDaoImpl">
            <property name="jdbcTemplate" ref="jdbcTemplate"></property>
        </bean>
        
        
</beans>

2.3 相关类的主要代码

创建Entity类:User.java

package com.it.entity;

public class User {
    private int id;
    private String name;
    private int age;
    
    public void setAge(int age) {
        this.age = age;
    }
    
    public int getAge() {
        return age;
    }
    
    public User() {
        // TODO Auto-generated constructor stub
        super();
    }
    
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public User(int id, String name,int age) {
        super();
        this.id = id;
        this.name = name;
        this.age = age;
    }
    
    @Override
    public String toString() {
        // TODO Auto-generated method stub
        return "id:"+this.id+"==>:name"+this.name+"==>age:"+this.age;
    }
}

接口 UserDao.java

package com.it.dao;

import com.it.entity.User;

public interface UserDao {
    //根据ID获取用户
    User getUserById(int id);
    //插入数据
    int insertUser(User user);
    //删除用户
    int deleteUserById(int id);
    //获取用户数量
    int getUserCount();
    //更新用户
    void updateUser(User user);
    
}

UserDao的实现,UserDaoImpl.java

package com.it.dao.impl;

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

import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import com.it.dao.UserDao;
import com.it.entity.User;

public class UserDaoImpl implements UserDao{

    private JdbcTemplate jdbcTemplate;
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
    //user_t
    @Override
    public User getUserById(int id) {
        // TODO Auto-generated method stub
        String sql = "select id,name,age from user_t where id=?";
        return jdbcTemplate.queryForObject(sql, new MyMapper(),id);
    }

    @Override
    public int insertUser(User user) {
        // TODO Auto-generated method stub
        String sql = "insert into user_t(name,age) values(?,?)";
        int affect = jdbcTemplate.update(sql, user.getName(),user.getAge());
        return affect;
    }

    @Override
    public int deleteUserById(int id) {
        String delQuery = "delete from user_t where id = ?";
        return jdbcTemplate.update(delQuery, new Object[] { id });
    }

    @Override
    public int getUserCount() {
        // TODO Auto-generated method stub
        String sql = "select count(id) from user_t";
        
        return jdbcTemplate.queryForObject(sql, null, Integer.class);
    }

    @Override
    public void updateUser(User user) {
        // TODO Auto-generated method stub
        User user2 = this.getUserById(user.getId());
        if (user2!=null) {
            String sql = "update user_t set name=?,age=? where id=?";
            jdbcTemplate.update(sql, user.getName(),user.getAge(),user.getId());
        }
    }
    
    
    public void insertUsers(final List<User> users) {
        String sql = "insert into user_t(name,age) values(?,?)";
        
        jdbcTemplate.batchUpdate(sql,
                new BatchPreparedStatementSetter() {
                    
                    @Override
                    public void setValues(PreparedStatement statement, int i) throws SQLException {
                        // TODO Auto-generated method stub
                        User user = users.get(i);
                        statement.setString(1, user.getName());
                        statement.setInt(2, user.getAge());
                        
                    }
                    
                    @Override
                    public int getBatchSize() {
                        // TODO Auto-generated method stub
                        return users.size();
                    }
                }
            );
    }
    
    public void batchUpdate() {
        jdbcTemplate.batchUpdate(new String[]{
                "update user_t set age=100 where id=5",
                "update user_t set age=100 where id=6",
                "update user_t set age=100 where id=7"
        });
    }
    
    class MyMapper implements RowMapper<User>{

        @Override
        public User mapRow(ResultSet rs, int index) throws SQLException {
            // TODO Auto-generated method stub
            User user = new User(rs.getInt("id"),rs.getString("name"), rs.getInt("age"));
            return user;
        }
    }
}

测试类:测试spring JDBCTemplate简单的CRUD

JdbcTemplateDemo.java

package com.it.b_jdbc_template;

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

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

import com.it.dao.UserDao;
import com.it.dao.impl.UserDaoImpl;
import com.it.entity.User;

public class JdbcTemplateDemo {
    
    private ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml");
    
    @Test
    public void getUserById() {
        UserDao userDao = (UserDao) ac.getBean("userDao");
        User user = userDao.getUserById(1);
        System.out.println(user);
    } 
    
    @Test
    public void insertUser(){
        UserDao userDao = (UserDao) ac.getBean("userDao");
        User user = new User(3, "小爱", 12);
        int count = userDao.insertUser(user);
        System.out.println("插入成功!:"+count);
    
    }
    
    @Test
    public void deleteUserById(){
        UserDao userDao = (UserDao) ac.getBean("userDao");
        int index = userDao.deleteUserById(2);
        System.out.println("删除成功!:"+index);
    }
    
    @Test
    public void getUserCount(){
        UserDao userDao = (UserDao) ac.getBean("userDao");
        int userCount = userDao.getUserCount();
        System.out.println("总的用户个数: "+userCount);
    }
    
    @Test
    public void updateUser(){
        UserDao userDao = (UserDao) ac.getBean("userDao");
        User user = new User(1, "大兵", 44);
        userDao.updateUser(user);
        System.out.println("更新成功: "+user.toString());
        
    }
    
    @Test
    public void batchOperation() {
        List<User> list = new ArrayList<>();
        for(int i = 0;i<10;i++){
            User user = new User(i+5, "Rose"+i+1, i+23);
            list.add(user);
        }
        
        UserDaoImpl userDao = (UserDaoImpl) ac.getBean("userDao");
        userDao.insertUsers(list);
        int userCount = userDao.getUserCount();
        System.out.println("总的用户数量: "+userCount);
    }
    
    @Test
    public void batchUpdate() {
        UserDaoImpl userDao = (UserDaoImpl) ac.getBean("userDao");
        userDao.batchUpdate();
        
    }
}

2.4 spring JDBC的批处理操作

批处理:单个可执行的操作单元组成的多步操作.如果需要进行多次同样的操作,JDBC驱动会表现更加流畅.此外,如果多个更新操作处理,可以限制运行的操作数量.

通常来说,运行程序的服务器和数据库服务器不在同一个位置.假设现在需要执行100次执行语句,
通常来说,我们会在程序将查询语句逐条的发送到数据库服务器并执行.这样,我们必须通过网络将查询语句发送到数据库服务器.这样会导致交流成本的消耗降低性能.所以,为了挺高性能和减少交流的消耗,使用JDBC的批处理操作.

批处理操作允许我们一次性提交多个SQL语句到服务器.JDBCTemplate支持JDBC 多个statement
或者多个prepareStatement的操作

jdbcTemplate有两个重载batchUpdate()方法

一个方法是执行多个JDBC statement语句 : public int[] batchUpdate(String[] sql) throws DataAccessException

比如:

jdbcTemplate.batchUpdate (new String [] {
"update emp set salary = salary * 1.5 where empId = 10101",
"update emp set salary = salary * 1.2 where empId = 10231", "update dept set location = 'Bangalore' where deptNo = 304"
});

另外一个方法是执行SQL statement语句多次,可以有不同的参数,使用preparestatement语句


public int[] batchUpdate(String sql, BatchPreparedStatementSetter bPSS) throws DataAccessException

主要例子看 UserDaoImpl.java的两个方法


    public void insertUsers(final List<User> users) {
        String sql = "insert into user_t(name,age) values(?,?)";
        
        jdbcTemplate.batchUpdate(sql,
                new BatchPreparedStatementSetter() {
                    
                    @Override
                    public void setValues(PreparedStatement statement, int i) throws SQLException {
                        // TODO Auto-generated method stub
                        User user = users.get(i);
                        statement.setString(1, user.getName());
                        statement.setInt(2, user.getAge());
                        
                    }
                    
                    @Override
                    public int getBatchSize() {
                        // TODO Auto-generated method stub
                        return users.size();
                    }
                }
            );
    }
    
    public void batchUpdate() {
        jdbcTemplate.batchUpdate(new String[]{
                "update user_t set age=100 where id=5",
                "update user_t set age=100 where id=6",
                "update user_t set age=100 where id=7"
        });
    }

spring 数据库操作之JDBC(05)

Spring JDBC模板

猜你喜欢

热点阅读