Spring JDBC入门

2022-09-14 13:47:10

Spring将替我们完成所有使用JDBC API进行开发的单调乏味的、底层细节处理工作。

操作JDBC时Spring可以帮我们做这些事情：

定义数据库连接参数，打开数据库连接，处理异常，关闭数据库连接

我们仅需要关注：

声明SQL语句，处理每一次得到的结果

 

一个较为简单的例子与讲解

JdbcTemplate类 

JdbcTemplate是core包的核心类。它替我们完成了资源的创建以及释放工作，从而简化了我们对JDBC的使用。它还可以帮助我们避免一些常见的错误，比如忘记关闭数据库连接。JdbcTemplate将完成JDBC核心处理流程，比如SQL语句的创建、执行，而把SQL语句的生成以及查询结果的提取工作留给我们的应用代码。它可以完成SQL查询、更新以及调用存储过程，可以对ResultSet进行遍历并加以提取。它还可以捕获JDBC异常并将其转换成org.springframework.dao包中定义的，通用的，信息更丰富的异常。

代码分为三部分

第一部是利用Spring处理业务，第二部分创建Spring相关的XML，第三部分将XML和Context结合到一起。

package com.jiaozg.dao;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.JdbcTemplate;

import com.jiaozg.model.Dept;
import com.jiaozg.util.SpringUtil;

主要类：
public class DeptDao {
    
    private JdbcTemplate jdbcT;
    
    public void setJdbcT(JdbcTemplate jdbcT) {
        this.jdbcT = jdbcT;
    }

    public  List findALL() {
        String sql = "select * from dept";
        return jdbcT.queryForList(sql);
    }
    
    public List<Dept> findALLDepts() {
        List<Dept> depts = new ArrayList<Dept>();;
        String sql = "select * from Dept";
        List list = jdbcT.queryForList(sql); 
        Iterator iterator = list.iterator();
        Dept dept = null;
        while (iterator.hasNext()) {
            Map map4dept = (Map) iterator.next();
            dept = new Dept();       
            dept.setDeptNo(((BigDecimal) map4dept.get("DEPTNO")).intValue());
            dept.setDName((String)map4dept.get("DNAME"));
            dept.setLoc((String)map4dept.get("LOC"));        
            depts.add(dept);
        }
        return depts;
    }    
    public int delete(int bid){
        String sql = "delete from DeptInfo where bid =?";
        return jdbcT.update(sql, new Object[]{bid});
    }     
    
    public static void main(String[] args) {      
        DeptDao dao = (DeptDao) SpringUtil.getBean("deptDao");
        List<Dept> depts = dao.findALLDepts();;
        for(Dept dept:depts){
            System.out.println(dept.getDeptNo()+","+dept.getDName()+","+dept.getLoc());
        }
        System.out.println("---------------------------------");
        
        List list = dao.findALL();
        for(Iterator it = list.iterator(); it.hasNext(); ) {
            System.out.println(it.next());
        }
    }
}
public class SpringUtil {
     private static ApplicationContext  ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
        public static Object getBean(String beanName){
             return ctx.getBean(beanName);
        }    
}
<?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-2.5.xsd">

    <bean id="springDSN"
        class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName"
            value="oracle.jdbc.driver.OracleDriver">
        </property>
        <property name="url"
            value="jdbc:oracle:thin:@127.0.0.1:1521:orcl">
        </property>
        <property name="username" value="scott"></property>
        <property name="password" value="Qwer1234"></property>
    </bean>

    <bean id="jdbcTemplate"
        class="org.springframework.jdbc.core.JdbcTemplate" abstract="false"
        lazy-init="false" autowire="default" dependency-check="default">
        <property name="dataSource">
            <ref bean="springDSN" />
        </property>
    </bean>
    
    <bean id="deptDao" class="com.jiaozg.dao.DeptDao">
       <property name="jdbcT">
          <ref bean="jdbcTemplate" />
       </property>
    </bean>
    
</beans>

总结：

JAVA逻辑部分还能看懂，但XML就有些迷糊：XML第一部分是配置到数据库的连接信息，第二三部分就不知道了。

还有DAO是什么意思？

应该还缺少数据库中表的定义，这个定义应该对应着DEBT的javabean.

 

一个更加详细的例子

CREATE TABLE Student(
   ID   INT NOT NULL AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE  INT NOT NULL,
   PRIMARY KEY (ID)
);

Student表，有ID, NAME, AGE三项

对应的JAVABEAN

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

   public void setAge(Integer age) {
      this.age = age;
   }
   public Integer getAge() {
      return age;
   }

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

   public void setId(Integer id) {
      this.id = id;
   }
   public Integer getId() {
      return id;
   }
}

StudentMapper.java的内容

public class StudentMapper implements RowMapper<Student> {
   public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
      Student student = new Student();
      student.setId(rs.getInt("id"));
      student.setName(rs.getString("name"));
      student.setAge(rs.getInt("age"));
      return student;
   }
}

将搜索结果ResultSet转化为Object

StudentJDBCTemplate.java

public class StudentJDBCTemplate implements StudentDAO {
   private DataSource dataSource;
   private JdbcTemplate jdbcTemplateObject;
   
   public void setDataSource(DataSource dataSource) {
      this.dataSource = dataSource;
      this.jdbcTemplateObject = new JdbcTemplate(dataSource);
   }

   public void create(String name, Integer age) {
      String SQL = "insert into Student (name, age) values (?, ?)";
      
      jdbcTemplateObject.update( SQL, name, age);
      System.out.println("Created Record Name = " + name + " Age = " + age);
      return;
   }

   public Student getStudent(Integer id) {
      String SQL = "select * from Student where id = ?";
      Student student = jdbcTemplateObject.queryForObject(SQL, 
                        new Object[]{id}, new StudentMapper());
      return student;
   }

   public List<Student> listStudents() {
      String SQL = "select * from Student";
      List <Student> students = jdbcTemplateObject.query(SQL, 
                                new StudentMapper());
      return students;
   }

   public void delete(Integer id){
      String SQL = "delete from Student where id = ?";
      jdbcTemplateObject.update(SQL, id);
      System.out.println("Deleted Record with ID = " + id );
      return;
   }

   public void update(Integer id, Integer age){
      String SQL = "update Student set age = ? where id = ?";
      jdbcTemplateObject.update(SQL, age, id);
      System.out.println("Updated Record with ID = " + id );
      return;
   }

}

Spring和JAVABEAN的配置文件

<?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-3.0.xsd ">

   <!-- Initialization for data source -->
   <bean id="dataSource" 
      class="org.springframework.jdbc.datasource.DriverManagerDataSource">
      <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
      <property name="url" value="jdbc:mysql://localhost:3306/TEST"/>
      <property name="username" value="root"/>
      <property name="password" value="password"/>
   </bean>

   <!-- Definition for studentJDBCTemplate bean -->
   <bean id="studentJDBCTemplate" 
      class="com.yiibai.StudentJDBCTemplate">
      <property name="dataSource"  ref="dataSource" />    
   </bean>
      
</beans>

main.java

public class MainApp {
   public static void main(String[] args) {
      ApplicationContext context = 
             new ClassPathXmlApplicationContext("Beans.xml");

      StudentJDBCTemplate studentJDBCTemplate = 
      (StudentJDBCTemplate)context.getBean("studentJDBCTemplate");
      
      System.out.println("------Records Creation--------" );
      studentJDBCTemplate.create("Zara", 11);
      studentJDBCTemplate.create("Nuha", 2);
      studentJDBCTemplate.create("Ayan", 15);

      System.out.println("------Listing Multiple Records--------" );
      List<Student> students = studentJDBCTemplate.listStudents();
      for (Student record : students) {
         System.out.print("ID : " + record.getId() );
         System.out.print(", Name : " + record.getName() );
         System.out.println(", Age : " + record.getAge());
      }

      System.out.println("----Updating Record with ID = 2 -----" );
      studentJDBCTemplate.update(2, 20);

      System.out.println("----Listing Record with ID = 2 -----" );
      Student student = studentJDBCTemplate.getStudent(2);
      System.out.print("ID : " + student.getId() );
      System.out.print(", Name : " + student.getName() );
      System.out.println(", Age : " + student.getAge());
      
   }
}

 

还有一个带有用法的main.java，我就不贴了

总结

1. 将JAVA的类与数据库中的表项绑定到一起，这样更加方便操作

2. 表到类的转换过程需要rawMapper,它起作用体现在query或queryForObject

3. SQL语句用到了(name, age) (?, ?)的东西，这个倒是有点像C++11中的bind用法，QueryForObject也用到了类似的写法，但是稍微复杂点

queryForObject(SQL, new Object[]{id}, new StudentMapper()}; 这里的id被填充到一个数组中，数组为SQL提供数据

4. Context读取XML文件，Template依靠Context获得Bean,这个bean不是数据的定义，而是操作的定义。

 

Spring JDBC入门,布布扣,bubuko.com

Spring JDBC入门

上一篇：MySQL工具汇总


下一篇：Sentinel