使用Spring JDBC框架连接并操作数据库

你的名字 2022-08-05 01:24 220阅读 0赞

在前一篇博文[JAVA通过JDBC连接并操作MySQL数据库][JAVA_JDBC_MySQL]中，我们知道如何通过JDBC连接并操作数据库，但是请看程序，整个程序连接数据库和关闭数据库占了很大一部分代码量，而且每次我们执行一下数据库操作都得来这么一大段重复代码，这是很烦人的。而在Spring框架中同样提供了JDBC框架，以供我们操作数据库。spring中的JDBC框架则可以为我们省去连接和关闭数据库的代码，我们只要关注我们想对数据库进行的操作即可，下面开始介绍吧。

同样的，我们需要在MySQL中创建一个table，以供我们测试使用。

CREATE TABLE student(
    	ID VARCHAR(5),
    	name VARCHAR(20),
    	age int(3),
    	FM VARCHAR(1),
    	PRIMARY KEY(ID)
    )

表格效果如下图：

![SouthEast][]

有了数据库之后，建好maven工程，然后需要配置数据源。在Spring的JDBC框架中，数据源配置在Beans.xml中，当然这个文件名可以随便取的。

整个Beans.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" 
        xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans-4.1.xsd ">
    	
        <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/mysql?useUnicode=true&characterEncoding=utf-8"/>    	
        	<property name="username" value="root"/>
        	<property name="password" value="snow" />
    	</bean>   	
    	
    	<bean id="studentDaoImp"
    		class="SNOW.SpringJDBCtest.StudentDaoImp">
    		<property name="datasource" ref="datasource" />
    	</bean>    	
        	
    </beans>

在这里有一点要注意characterEncoding前面的 & 需要更换成 & ，否则会报错。

接下来我们为数据库student创建一个类student.java

package SNOW.SpringJDBCtest;
    
    public class Student{
    	private String ID;
    	private String name;
    	private int age;
    	private String FM;
    	
    	public Student(){		
    	}
    	
    	public Student(String ID,String name,int age, String FM){
    		this.ID = ID;
    		this.name = name;
    		this.age = age;
    		this.FM = FM;
    	}
    	
    	public void setID(String ID){
    		this.ID = ID;
    	}
    	
    	public String getID(){
    		return this.ID;
    	}
    
    	public void setname(String name){
    		this.name = name;
    	}
    	
    	public String getname(){
    		return this.name;
    	}
    
    	public void setage(int age){
    		this.age = age;
    	}
    	
    	public int getage(){
    		return age;
    	}
    	
    	public void setFM(String FM){
    		this.FM = FM;
    	}
    	
    	public String getFM(){
    		return this.FM;
    	}	
    	
    	public void display(){
    		System.out.println(ID + " " + name + " " + age + " " + FM);
    	}
    	
    	
    }

有了Student类之后，还需要一个将SQL数据与student对象映射的类StudentMapper.java

package SNOW.SpringJDBCtest;
    
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import org.springframework.jdbc.core.RowMapper;
    
    public class StudentMapper implements RowMapper<Student> {
    
    	public Student mapRow(ResultSet rs, int rownum) throws SQLException {
    		Student student = new Student();
    		student.setID(rs.getString("ID"));
    		student.setname(rs.getString("name"));
    		student.setage(rs.getInt("age"));
    		student.setFM(rs.getString("FM"));
    		
    		return student;		
    	}
    	
    }

Spring JDBC框架是通过DAO（Data Access Object）来实现对数据库的读写数据操作的，并且在实现过程中应该由应用程序implements interface 来完成数据库的读写操作。

我们的接口定义如下：

package SNOW.SpringJDBCtest;
    
    import java.util.List;
    import javax.sql.DataSource;
    
    
    public interface StudentDao{
    
    	/** 
    	 * This is the method to be used to initialize
    	 * database resources ie. connection.
    	 */	
    	public void setdatasource(DataSource ds);	
    	
    	public void addstudent(Student student);
    	
    	public void delstudentbyID(String ID);
    	
    	public void delstudentbyname(String name);
    	
    	public void delallstudent();
    	
    	public void updstudent(Student student);
    	
    	public List<Student> allstudent();
    	
    	public List<Student> querystudentbyID(String ID);
    	
    	public List<Student> querystudentbyname(String name);
    	
    	public List<Student> querystudentbyage(int age);
    	
    	
    }

接口实现定义如下：

package SNOW.SpringJDBCtest;
    
    import java.util.List;
    
    import javax.sql.DataSource;
    
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.jdbc.core.RowCallbackHandler;
    
    
    public class StudentDaoImp implements StudentDao{
    
    	private DataSource datasource;
    	private JdbcTemplate jdbcTemplateObject;
    	
    	
    	public void setdatasource(DataSource ds) {
    		this.datasource = ds;
    		this.jdbcTemplateObject = new JdbcTemplate(datasource);		
    	}
    
    	public void addstudent(Student student) {
    		String sql = "INSERT INTO class.student(ID,name,age,FM)VALUES(?,?,?,?)";
    		
    		jdbcTemplateObject.update(sql, student.getID(),
    				student.getname(),student.getage(),student.getFM());
    		return ;
    	}
    
    	public void delstudentbyID(String ID) {
    		String sql = "DELETE FROM class.student WHERE ID=?";
    		jdbcTemplateObject.update(sql,ID);
    		return ;
    	}
    
    	public void delstudentbyname(String name) {
    		String sql = "DELETE FROM class.student WHERE name=?";
    		jdbcTemplateObject.update(sql,name);
    		return ;		
    	}
    
    	public void delallstudent() {
    		String sql = "DELETE FROM class.student";
    		jdbcTemplateObject.update(sql);
    		return ;	
    	}
    
    	public void updstudent(Student student) {
    		String sql = "UPDATE class.student set name=?,age=?,FM=? WHERE ID=?";
    		jdbcTemplateObject.update(sql,student.getname(),
    				student.getage(),student.getFM(),student.getID());
    		return ;
    	}
    
    	public List<Student> allstudent() {
    		List<Student> students = null;
    		String sql = "SELECT * FROM class.student";
    		students = jdbcTemplateObject.query(sql, new StudentMapper());
    		return students;
    	}
    
    	public List<Student> querystudentbyID(String ID) {
    		List<Student> students = null;
    		String sql = "SELECT * FROM class.student WHERE ID=?";
    		students = jdbcTemplateObject.query(sql, new Object[]{ID}, new StudentMapper());
    		return students;
    	}
    
    	public List<Student> querystudentbyname(String name) {
    		List<Student> students = null;
    		String sql = "SELECT * FROM class.student WHERE name=?";
    		students = jdbcTemplateObject.query(sql, new Object[]{name}, new StudentMapper());
    		return students;
    	}
    
    	public List<Student> querystudentbyage(int age) {
    		List<Student> students = null;
    		String sql = "SELECT * FROM class.student WHERE age=?";
    		students = jdbcTemplateObject.query(sql, new Object[]{age}, new StudentMapper());
    		return students;
    	}
    	
    	public void displayall(){
    		List<Student> students = allstudent();
    		for(Student s : students){
    			s.display();
    		}
    	}
    	
    	
    }

实现了StudentDaoImp类之后需要装备到Beans.xml中，具体见最上面的Beans.xml代码。

写完以上代码就写完了主要的功能操作了，接下来我们写个测试程序Maintest.java

package SNOW.SpringJDBCtest;
    
    import java.util.List;
    
    import org.springframework.context.ApplicationContext;
    import org.springframework.context.support.ClassPathXmlApplicationContext;
    
    public class Maintest{
    	public static void main(String [] args){
    		ApplicationContext context = 
    				new ClassPathXmlApplicationContext("SNOW/SpringJDBCtest/Beans.xml");
    		StudentDaoImp studentDaoImp = (StudentDaoImp)context.getBean("studentDaoImp");
    		
    		
    		String[] ID = { "2008", "2009", "2010", "1990", "2015","2018" };
    		String[] name = { "Wang", "Hui", "Yu", "Yuan", "Yuan", "Yang"};
    		int[] age = { 16, 18, 20, 20, 22, 21 };
    		String[] FM = {"F", "F", "M", "M", "M", "F"};		
    		Student student = null;
    		List<Student> students = null;
    		
    		System.out.println("---------addstudent-------------");
    		for(int i=0; i<ID.length; i++){
    			student = new Student(ID[i],name[i],age[i],FM[i]);
    			studentDaoImp.addstudent(student);			
    		}
    		studentDaoImp.displayall();
    		
    		System.out.println("---------updatestudent-------------");
    		student = new Student("1990","Yuan",18,"M");
    		studentDaoImp.updstudent(student);
    		studentDaoImp.displayall();
    		
    		System.out.println("---------querystudentbyID-------------");
    		students = studentDaoImp.querystudentbyID("1990");
    		for(Student s : students){
    			s.display();
    		}
    		
    		System.out.println("---------querystudentbyname-------------");
    		students = studentDaoImp.querystudentbyname("Yuan");
    		for(Student s : students){
    			s.display();
    		}
    		
    		System.out.println("---------querystudentbyage-------------");
    		students = studentDaoImp.querystudentbyage(20);
    		for(Student s : students){
    			s.display();
    		}	
    		
    		System.out.println("---------delstudentbyage-------------");
    		studentDaoImp.delstudentbyID("2018");
    		studentDaoImp.displayall();
    		
    		System.out.println("---------delstudentbyname-------------");
    		studentDaoImp.delstudentbyname("Hui");
    		studentDaoImp.displayall();		
    		
    		System.out.println("---------delallstudent-------------");
    		studentDaoImp.delallstudent();	
    		
    		
    	}
    	
    }

因为创建的Maven项目，其中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>SNOW</groupId>
      <artifactId>SpringJDBCtest</artifactId>
      <version>0.0.1-SNAPSHOT</version>
      <packaging>jar</packaging>
    
      <name>SpringJDBCtest</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>
        
     	<dependency>
    		<groupId>mysql</groupId>
    		<artifactId>mysql-connector-java</artifactId>
    		<version>5.1.35</version>
    	</dependency>
    
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>4.1.6.RELEASE</version>
        </dependency>
    
    	<dependency>
    		<groupId>org.springframework</groupId>
    		<artifactId>spring-jdbc</artifactId>
    		<version>4.1.6.RELEASE</version>
    	</dependency>
    	
    	<dependency>
    		<groupId>org.springframework</groupId>
    		<artifactId>spring-tx</artifactId>  
    		<version>4.1.6.RELEASE</version>
    	</dependency>    
    	
      </dependencies>
    </project>

最终运行结果如下：

五月 29, 2015 8:51:29 下午 org.springframework.context.support.ClassPathXmlApplicationContext prepareRefresh
    信息: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@384e57ba: startup date [Fri May 29 20:51:29 CST 2015]; root of context hierarchy
    五月 29, 2015 8:51:30 下午 org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
    信息: Loading XML bean definitions from class path resource [SNOW/SpringJDBCtest/Beans.xml]
    五月 29, 2015 8:51:31 下午 org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName
    信息: Loaded JDBC driver: com.mysql.jdbc.Driver
    ---------addstudent-------------
    1990 Yuan 20 M
    2008 Wang 16 F
    2009 Hui 18 F
    2010 Yu 20 M
    2015 Yuan 22 M
    2018 Yang 21 F
    ---------updatestudent-------------
    1990 Yuan 18 M
    2008 Wang 16 F
    2009 Hui 18 F
    2010 Yu 20 M
    2015 Yuan 22 M
    2018 Yang 21 F
    ---------querystudentbyID-------------
    1990 Yuan 18 M
    ---------querystudentbyname-------------
    1990 Yuan 18 M
    2015 Yuan 22 M
    ---------querystudentbyage-------------
    2010 Yu 20 M
    ---------delstudentbyage-------------
    1990 Yuan 18 M
    2008 Wang 16 F
    2009 Hui 18 F
    2010 Yu 20 M
    2015 Yuan 22 M
    ---------delstudentbyname-------------
    1990 Yuan 18 M
    2008 Wang 16 F
    2010 Yu 20 M
    2015 Yuan 22 M
    ---------delallstudent-------------

本文参考文献：

[http://www.tutorialspoint.com/spring/spring\_jdbc\_framework.htm][http_www.tutorialspoint.com_spring_spring_jdbc_framework.htm]

[http://www.tutorialspoint.com/spring/spring\_jdbc\_example.htm][http_www.tutorialspoint.com_spring_spring_jdbc_example.htm]

[JAVA_JDBC_MySQL]: http://blog.csdn.net/wanghuiqi2008/article/details/46238457
[SouthEast]: /images/20220805/f7ace066306447c08a1041c35bbbfa30.png
[http_www.tutorialspoint.com_spring_spring_jdbc_framework.htm]: http://www.tutorialspoint.com/spring/spring_jdbc_framework.htm
[http_www.tutorialspoint.com_spring_spring_jdbc_example.htm]: http://www.tutorialspoint.com/spring/spring_jdbc_example.htm