梦入琼楼寒有月，行过石树冻无烟

Java Spring JDBC

2022-04-21

Java Spring JDBC即互联网数据库开发的基础，Spring框架提供了JDBC的模板模式，即JDBCTemplate，可简化很多的沉于代码，但是在实际情况中jdbcTemeplate并不常用，但这对我们学习和理解Spring 和之后的微服务都会起到非常重要的作用，可容易我们以后理解微服务的流程、过程等，本书主要参考了tutorialspoint内的文档而成.
在正式开始学习之前，需要使用以下Jar外部依赖包:

aspectjweaver-1.9.2.jar
commons-logging-1.2.jar
mysql-connector-java-8.0.22.jar
org.springframework.transaction-3.1.0.release.jar
spring-aop-5.1.4.RELEASE.jar
spring-beans-5.1.4.RELEASE.jar
spring-context-5.1.4.RELEASE.jar
spring-core-5.1.4.RELEASE.jar
spring-expression-5.1.4.RELEASE.jar
spring-jdbc-5.1.4.RELEASE.jar
spring-tx-5.1.4.RELEASE.jar

以上jar外部部分依赖包可下载spring框架来引入，而另一部分依赖包需要自行进行查找和下载，可通过https://www.findjar.com/ jar包搜索来下载想要的jar包

数据访问接口

TestuserDAO.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
package Dao;
import java.util.List;
import javax.sql.DataSource;

public interface TestuserDAO {

	// 用于初始化数据库连接
	public void setDaotaSource(DataSource ds);
	
	// 用于初始化数据库连接
	public void create(String name, int age);
	
	// 用于列出方法的记录
	public Student getStudent(int id);
	
	// 用于列出表中的所有记录
	public List<Student> listStudents();
	
	// 用于删除的方法
	public void delete(int id);
	
	// 用于更新方法即学生表中的记录
	public void update(int id, int age);
}

Student.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
package Dao;

public class Student {
	private int age;
	private int id;
	private String name;
	
	// age
	public void setAge(int age) {
		this.age = age;
	}
	public int getage() {
		return age;
	}
	
	// id
	public void setId(int id) {
		this.id = id;
	}
	public int getid() {
		return id;
	}
	
	// name
	public void setName(String name) {
		this.name = name;
	}
	public String getname() {
		return name;
	}
}

StudentMapper.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
package Dao;

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 s = new Student();
			s.setId(rs.getInt("id"));
			s.setAge(rs.getInt("age"));
			s.setName(rs.getString("name"));
		return s;
	}
}

实现类

StudentJDBCTemplate.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
package Dao;

import java.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;

public class StudentJDBCTemplate implements TestuserDAO {
	private DataSource dataSource;
	private JdbcTemplate jdbcTemplateObject;

		public void setDataSource(DataSource dataSource) {
			// TODO Auto-generated method stub
			this.dataSource = dataSource;
			this.jdbcTemplateObject = new JdbcTemplate(dataSource);
		}


	public void create(String name, int age) {
		// TODO Auto-generated method stub
		String SQL = "insert into testuser (name,age) values (?,?)";
		jdbcTemplateObject.update(SQL,name,age);
		System.out.println("Name:" + name + "Age:" + age);
		return;
	}


	public Student getStudent(int id) {
		// TODO Auto-generated method stub
		String SQL = "select * from testuser where id = ?";
		Student student = jdbcTemplateObject.queryForObject(SQL,
				new Object[] {id},new StudentMapper());
		return student;
	}


	public List<Student> listStudents() {
		// TODO Auto-generated method stub
		String SQL = "select * from testuser";
		List <Student> students = jdbcTemplateObject.query(SQL, new StudentMapper());
		return students;
	}


	public void delete(int id) {
		// TODO Auto-generated method stub
		String SQL = "delete from testuser where id = ?";
		jdbcTemplateObject.update(SQL, id);
		System.out.println("Delete id:" + id);
		return;
	}


	public void update(int id, int age) {
		// TODO Auto-generated method stub
		String SQL = "update testuser set age = ? where id = ?";
		jdbcTemplateObject.update(SQL,age,id);
		System.out.println("Update id:" + id);
		return;
	}


	@Override
	public void setDaotaSource(DataSource ds) {
		// TODO Auto-generated method stub
		
	}

}

MainApp.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
package Dao;

import java.util.List;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import Dao.StudentJDBCTemplate;
public class MainApp {
	public static void main(String args[]) {
		ApplicationContext application = new ClassPathXmlApplicationContext("Beans.xml");
		StudentJDBCTemplate s = 
				(StudentJDBCTemplate)application.getBean("StudentJDBCTemplate");
		
		System.out.println("_______________)s.create");
			s.create("sunone", 21);
			s.create("sunsun", 22);
		
		System.out.println("_______________)List<Student> student");
			List<Student> student = s.listStudents();
			for (Student record: student) {
				System.out.println("age:" + record.getage());
				System.out.println("id:" + record.getid());
				System.out.println("name:" + record.getname());
			}
		
		System.out.println("________________)s.update");
			s.update(520,1314);
		
		System.out.println("________________)Student student");
			Student students = s.getStudent(2);
			System.out.println("id:" + students.getid());
			System.out.println("age:" + students.getage());
			System.out.println("name:" + students.getname());
	}
}

src

Beans.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<?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.cj.jdbc.Driver"/>
      <property name = "url" value = "jdbc:mysql://localhost:3306/testsp"/>
      <property name = "username" value = "username"/>
      <property name = "password" value = "password"/>
   </bean>

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

以上code可能会运行出错为：
Exception in thread “main” java.lang.NoSuchMethodError: org.springframework.dao.support.DataAccessUtils.nullableSingleResult(Ljava/util/Collection;)Ljava/lang/Object;
at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:784)
at Dao.StudentJDBCTemplate.getStudent(StudentJDBCTemplate.java:30)
at Dao.MainApp.main(MainApp.java:30)
细心的读者可通过搜索引擎自行解决，这个报错可能是spring与maven内的依赖版本过高、也可是jar依赖包导入顺序造成的。

⬅️ Go back