【JavaEE框架】spring之配置数据源和JdbcTemplate

 

目录

一、数据准备

1、在pom.xml种导入依赖包

2、 建立数据库表

 二、测试数据源（操作数据库） 

三、在spring中使用数据源

 四、增删改查操作数据库

 五、测试

六、部分代码具体分析

---

一、数据准备

1、在pom.xml种导入依赖包

<dependency>
  		<groupId>org.springframework</groupId>
  		<artifactId>spring-jdbc</artifactId>
  		<version>5.3.23</version>
  	</dependency>
  	<dependency>
  		<groupId>com.mchange</groupId>
  		<artifactId>c3p0</artifactId>
  		<version>0.9.5.4</version>
  	</dependency>
  	<dependency>
  		<groupId>mysql</groupId>
  		<artifactId>mysql-connector-java</artifactId>
  		<version>8.0.27</version>
  	</dependency>

2、 建立数据库表

步骤：

 二、测试数据源（操作数据库） 

package cn.edu.aaa.dao;

import java.beans.PropertyVetoException;
import java.util.List;

import org.junit.Test;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import cn.edu.cqwu.entity.Student;
import com.mchange.v2.c3p0.ComboPooledDataSource;
//数据源操作数据库
public class StudentTest {
	@Test
	public void testDB() throws PropertyVetoException {
		ComboPooledDataSource dataSource=new ComboPooledDataSource();
		dataSource.setJdbcUrl("jdbc:mysql://localhost:3308/spring?useUnicode=true"
				+ "&characterEncoding=gbk&autoReconnect=true&failOverReadOnly=false");
		dataSource.setDriverClass("com.mysql.cj.jdbc.Driver");
		dataSource.setUser("root");
		dataSource.setPassword("root");
		JdbcTemplate jbdcTemplate=new JdbcTemplate();
		jbdcTemplate.setDataSource(dataSource);
		List<Student> students =jbdcTemplate.query("select * from student",
				new BeanPropertyRowMapper<Student>(Student.class));
		System.out.println(students);
		dataSource.close();

	}
}

三、在spring中使用数据源

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:p="http://www.springframework.org/schema/p"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:aop="http://www.springframework.org/schema/aop"
    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.xsd
   http://www.springframework.org/schema/context
   http://www.springframework.org/schema/context/spring-context.xsd
   http://www.springframework.org/schema/aop 
   http://www.springframework.org/schema/aop/spring-aop.xsd
   ">
   <!-- 在spring中使用数据源 -->
    <context:component-scan base-package="cn.edu.aaa.utils"></context:component-scan> 
   <context:component-scan base-package="cn.edu.aaa.dao"></context:component-scan>
   <context:component-scan base-package="cn.edu.aaa.biz"></context:component-scan>
   <!-- 属性文件的使用 xml配置-->
   <context:property-placeholder location="jdbc.properties"/>
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
   		<property name="jdbcUrl" value="${url}"></property>
   		<property name="driverClass" value="${driver}"></property>
   		<property name="user" value="${user}"></property>
   		<property name="password" value="${password}"></property>
   </bean> 
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
   		<property name="dataSource" ref="dataSource"></property>
   </bean> 

   
  <bean id="dao" class="cn.edu.aaa.dao.impl.StudentDaoImpl">
   		<property name="jdbcTemplate" ref="jdbcTemplate"></property>
   </bean>   
</beans>

 四、增删改查操作数据库

StudentDaoImpl.java

package cn.edu.aaa.dao.impl;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Scope;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Repository;

import cn.edu.aaa.dao.StudentDao;
import cn.edu.aaa.entity.Student;
@Repository
@Scope("singleton")
public class StudentDaoImpl implements StudentDao{
	@Autowired
	private JdbcTemplate jdbcTemplate;

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public List<Student> findAll() {
		// TODO Auto-generated method stub
		return jdbcTemplate.query("select * from student",
				new BeanPropertyRowMapper<Student>(Student.class));
	}

	public Student findByTd(int id) {
		// TODO Auto-generated method stub
		return jdbcTemplate.queryForObject("select * from student where student_id=?",
				new BeanPropertyRowMapper<Student>(Student.class),id);
	}

	public int save(Student student) {
		// TODO Auto-generated method stub
		return jdbcTemplate.update("insert into student values(null,?,?)",student.getName(),student.getAge());
	}

	public int update(Student student) {
		// TODO Auto-generated method stub
		return jdbcTemplate.update("update student set name=?,age=? where student_id=?",
				student.getName(),student.getAge(),student.getStudent_id());
	}

	public int deleteById(int id) {
		// TODO Auto-generated method stub
		return jdbcTemplate.update("delete from student where student_id=?",id);
	}

	
	
	
}

 五、测试

stuSqlTest .java

package cn.edu.aaa.test;

import javax.annotation.Resource;

import org.junit.Test;
import org.junit.experimental.theories.suppliers.TestedOn;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.PropertySource;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;


import cn.edu.aaa.dao.StudentDao;
import cn.edu.aaa.entity.Student;


@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:beans.xml")

public class stuSqlTest {
	@Autowired
	private StudentDao studentDao;
	
	@Test
	public void testQuery() {
		System.out.println(studentDao.findAll());
		System.out.println(studentDao.findByTd(1));
	}
	@Test
	public void testInsert() {
		Student student=new Student();
		student.setAge(19);
		student.setName("jerry");
		System.out.println(studentDao.save(student));
		
	}
	
	@Test
	public void testUpdate() {
		Student student=new Student();
		student.setAge(30);
		student.setName("杰瑞");
		student.setStudent_id(3);
		System.out.println(studentDao.update(student));
		testQuery();
	}
	
	@Test
	public void testDelete() {
		System.out.println(studentDao.deleteById(1));
		testQuery();
	}

}

六、部分代码具体分析

JdbcTemplate jbdcTemplate=new JdbcTemplate();
jbdcTemplate.setDataSource(dataSource);
List<Student> students =jbdcTemplate.query("select * from student",
new BeanPropertyRowMapper<Student>(Student.class));
System.out.println(students);
dataSource.close();

 1、创建JdbcTemplate实例

 JdbcTemplate jbdcTemplate = new JdbcTemplate();

这里创建了一个新的JdbcTemplate实例。

2、设置数据源

jbdcTemplate.setDataSource(dataSource);

将数据源dataSource设置到JdbcTemplate实例中。数据源通常包含了数据库连接池和连接数据库所需的信息（如URL、用户名、密码等）。

3、执行查询并映射结果

List<Student> students = jbdcTemplate.query("select * from student", new BeanPropertyRowMapper<Student>(Student.class));

 使用query方法执行SQL查询，并指定BeanPropertyRowMapper作为结果集的映射器。BeanPropertyRowMapper会根据SQL查询的结果自动将每一行数据映射到Student类的实例中。这里假设Student类中的字段与数据库表student的列名匹配，并且字段类型也是兼容的。