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

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

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

[sql] view plain copy print?

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

表格效果如下图：

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

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

整个Beans.xml配置文件如下所示：

[html] view plain copy print?

<?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="NetEase.SpringJDBCtest.StudentDaoImp">
<property name="datasource" ref="datasource" />
</bean>
</beans>

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

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

[java] view plain copy print?

package NetEase.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

[java] view plain copy print?

package NetEase.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 来完成数据库的读写操作。

我们的接口定义如下：

[java] view plain copy print?

package NetEase.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);
}

接口实现定义如下：

[java] view plain copy print?

package NetEase.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

[java] view plain copy print?

package NetEase.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("NetEase/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如下：

[html] view plain copy print?

<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>NetEase</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>

最终运行结果如下：

[plain] view plain copy print?

五月 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 [NetEase/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-------------

秒客网

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

相关文章