Spring4.0学习笔记(12) —— JDBCTemplate 操作数据库

整体配置

1、配置xml文件

<beans:beans xmlns="http://www.springframework.org/schema/mvc"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xmlns:beans="http://www.springframework.org/schema/beans"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc.xsd
        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-2.5.xsd"
    xmlns:tx="http://www.springframework.org/schema/tx">
    
    <context:property-placeholder location="classpath:db.properties"/>

    <!-- 配置基础数据源 -->
    <beans:bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <beans:property name="user" value="${user}"></beans:property>
        <beans:property name="password" value="${password}"></beans:property>
        <beans:property name="driverClass" value="${driverclass}"></beans:property>
        <beans:property name="jdbcUrl" value="${jdbcUrl}"></beans:property>
    </beans:bean>

    <!-- 配置Spring 的jdbcTemplate -->
    <beans:bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <beans:property name="dataSource" ref="dataSource"></beans:property>
    </beans:bean>

</beans:beans>

2、properties文件

user=root
password=admin
driverclass=com.mysql.jdbc.Driver
jdbcUrl=jdbc:mysql:///authority

3、测试类

package com.spring.jdbc;

import java.sql.SQLException;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;


public class jdbcTest {
    
    public static void main(String[] args) throws SQLException{
        String sql = "UPDATE person SET personName = ? WHERE Id = ?";
        
        ApplicationContext ctx = new ClassPathXmlApplicationContext("jdbcContext.xml");
        JdbcTemplate jdbcTemplate = (JdbcTemplate)ctx.getBean("jdbcTemplate");
        jdbcTemplate.update(sql, "GunnerChenk",1);
        System.out.println("");
    }
}

 注意点：

　　需要引入org.springframework-transaction.jar包。否则update 方法会，cannot find DataAccessException

BatchUpdate:

1、定义了一个操作数据库的公共方法类

package com.spring.jdbc;

import java.util.ArrayList;
import java.util.*;
import org.springframework.jdbc.core.JdbcTemplate;

public class jdbcOperation {
    
    private JdbcTemplate jdbcTemplate = null;
    
    public jdbcOperation(JdbcTemplate jdbcTemplate){
        this.jdbcTemplate = jdbcTemplate;
    }
    
    public void BatchUpdate(){
        String sql = "INSERT INTO person (personName,personSex,departmentId,departmentName,createtime,isDel) values" +
                "(?,?,?,?,?,?)";
        List<Object[]> batchArgs = new ArrayList<Object[]>();
        
        batchArgs.add(new Object[]{"test1",1,2,"programmer1",new Date(),1});
        batchArgs.add(new Object[]{"test2",1,2,"programmer2",new Date(),1});
        jdbcTemplate.batchUpdate(sql, batchArgs);
        
    }
    
    public void Update(){
        String sql = "UPDATE person SET personName = ? WHERE id = ?";
        jdbcTemplate.update(sql, "Arsenal",1);
    }
}

2、测试类

package com.spring.jdbc;

import java.sql.SQLException;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;


public class jdbcTest {
    
    public static void main(String[] args) throws SQLException{
        
        ApplicationContext ctx = new ClassPathXmlApplicationContext("jdbcContext.xml");
        jdbcOperation operation = new jdbcOperation((JdbcTemplate)ctx.getBean("jdbcTemplate"));
        //operation.Update();
        operation.BatchUpdate();
        System.out.println("");
    }
}

获取对象的方法

1、定义person 的bean文件

package com.spring.jdbc;

import java.util.Date;

public class Person {
    private Integer id;
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    private String personName;
    private Integer personSex;
    private Department department;
    private Date createtime;
    private Integer isDel;
    public Department getDepartment() {
        return department;
    }
    public void setDepartment(Department department) {
        this.department = department;
    }
    public String getPersonName() {
        return personName;
    }
    public void setPersonName(String personName) {
        this.personName = personName;
    }
    public Integer getPersonSex() {
        return personSex;
    }
    public void setPersonSex(Integer personSex) {
        this.personSex = personSex;
    }
    public Date getCreatetime() {
        return createtime;
    }
    public void setCreatetime(Date createtime) {
        this.createtime = createtime;
    }
    public Integer getIsDel() {
        return isDel;
    }
    @Override
    public String toString() {
        return "Person [createtime=" + createtime + ", department="
                + department + ", id=" + id + ", isDel=" + isDel
                + ", peronSex=" + personSex + ", personName=" + personName + "]";
    }
    public void setIsDel(Integer isDel) {
        this.isDel = isDel;
    }
    
    
}

2、定义department 的 bean文件

package com.spring.jdbc;

import java.util.Date;

public class Department {
    
    private Integer Id;
    
    private String departmentCode;
    
    private String departmentName;
    
    public Integer getId() {
        return Id;
    }

    @Override
    public String toString() {
        return "Department [Id=" + Id + ", createtime=" + createtime
                + ", departmentCode=" + departmentCode + ", departmentName="
                + departmentName + ", isDel=" + isDel + ", parentDepartmentId="
                + parentDepartmentId + "]";
    }

    public void setId(Integer id) {
        Id = id;
    }

    public String getDepartmentCode() {
        return departmentCode;
    }

    public void setDepartmentCode(String departmentCode) {
        this.departmentCode = departmentCode;
    }

    public String getDepartmentName() {
        return departmentName;
    }

    public void setDepartmentName(String departmentName) {
        this.departmentName = departmentName;
    }

    public Integer getParentDepartmentId() {
        return parentDepartmentId;
    }

    public void setParentDepartmentId(Integer parentDepartmentId) {
        this.parentDepartmentId = parentDepartmentId;
    }

    public Date getCreatetime() {
        return createtime;
    }

    public void setCreatetime(Date createtime) {
        this.createtime = createtime;
    }

    public Integer getIsDel() {
        return isDel;
    }

    public void setIsDel(Integer isDel) {
        this.isDel = isDel;
    }

    private Integer parentDepartmentId;
    
    private Date createtime;
    
    private Integer isDel;
}

3、定义获取实例的方法

    public Person GetEntity(){
        String sql = "SELECT id,personName,personSex,createtime,isDel FROM person WHERE id = 1";
        RowMapper<Person> personMapper = new BeanPropertyRowMapper<Person>(Person.class);
        return jdbcTemplate.queryForObject(sql, personMapper);
    }

4、定义测试类

package com.spring.jdbc;

import java.sql.SQLException;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;


public class jdbcTest {
    
    public static void main(String[] args) throws SQLException{
        
        ApplicationContext ctx = new ClassPathXmlApplicationContext("jdbcContext.xml");
        jdbcOperation operation = new jdbcOperation((JdbcTemplate)ctx.getBean("jdbcTemplate"));
        //operation.Update();
        //int[] executeSize =  operation.BatchUpdate();
        Person person = operation.GetEntity();
        System.out.println(person);
    }
}

在此处虽然定义了department的bean文件，但是通过hibernate的方式是无法获取到departmentId的，毕竟是jdbc 而不是ORM框架。