spring--基础--07--JDBC框架

spring–基础–07–JDBC框架

---

代码地址

https://gitee.com/DanShenGuiZu/learnDemo.git

---

1、介绍

1. 使用普通的JDBC数据库时,要写很多与业务无关的代码来处理异常,打开和关闭数据库连接等。
2. Spring JDBC框架负责所有的低层细节,从开始打开连接,准备和执行SQL语句,处理异常,处理事务,到最后关闭连接。
3. 你所做的是定义连接参数,指定要执行的SQL语句,每次迭代完成所需的工作。

2、JdbcTemplate类

1. SQL查询
2. 更新语句和
3. 存储过程调用
4. 执行迭代结果集
5. 提取返回参数值。
6. 可捕获JDBC异常并转换它们到org.springframework.dao包中定义的通用类、更多的信息、异常层次结构。
7. JdbcTemplate类实例是线程安全配置的。所以你可以配置JdbcTemplate的单个实例,然后将这个共享的引用安全地注入到多个DAO中。
8. 使用JdbcTemplate类时常见的做法：
   1. 在你的Spring配置文件中配置数据源,然后共享数据源bean依赖注入到DAO类中
   2. 并在数据源的设值函数中创建了JdbcTemplate。

3、测试(mysql数据库)

3.1、表

CREATE TABLE Student(
  ID int(11)NOT NULL AUTO_INCREMENT,
  NAME varchar(20)DEFAULT NULL,
  AGE int(11)DEFAULT NULL,
  PRIMARY KEY(ID)
)ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8;

# 存储过程
DELIMITER $$
DROP PROCEDURE
IF EXISTS `test`.`getRecordById`$$

CREATE PROCEDURE `test`.`getRecordById`(
	IN in_id INTEGER,
	OUT out_name VARCHAR(20),
	OUT out_age INTEGER
)
BEGIN
	SELECT
		NAME,
		age INTO out_name,
		out_age
	FROM
		Student
	WHERE
		id = in_id ;
	END$$
DELIMITER ;

3.2、公共测试的代码

<?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">

	<bean id="dataSource"
		  class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
		<property name="url" value="jdbc:mysql://zhoufei.ali.db.com:3306/test"/>
		<property name="username" value="root"/>
		<property name="password" value="root"/>
	</bean>


	<!-- Definition for studentJDBCTemplate bean -->
	<bean id="studentJDBCTemplate"
		  class="com.example.demolearn.other.spring.demo5.StudentJDBCTemplate">
		<property name="dataSource" ref="dataSource"/>
	</bean>

</beans>

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

}

public interface StudentDAO {
	// 设置数据源
	void setDataSource(DataSource ds);

	// 增删改查
	void insert(String name, Integer age);
	Student getById(Integer id);
	List<Student> list();
	void delete(Integer id);
	void update(Integer id, Integer age);

	// 存储过程
	Student procedure01(Integer id);

}

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

	@Override
	// 设置数据源
	public void setDataSource(DataSource dataSource){
		this.dataSource = dataSource;
		this.jdbcTemplateObject = new JdbcTemplate(dataSource);
	}

	@Override
	public void insert(String name, Integer age){

		String SQL = "insert into Student(name, age)values(?, ?)";
		jdbcTemplateObject.update(SQL, name, age);
		return;
	}

	@Override
	public Student getById(Integer id){

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

	@Override
	public List<Student> list(){

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

	@Override
	public void delete(Integer id){

		String SQL = "delete from Student where id = ?";
		jdbcTemplateObject.update(SQL, id);
		return;
	}

	@Override
	public void update(Integer id, Integer age){

		String SQL = "update Student set age = ? where id = ?";
		jdbcTemplateObject.update(SQL, age, id);
		return;
	}

	@Override
	// 存储过程
	public Student procedure01(Integer id){
		SqlParameterSource in = new MapSqlParameterSource().addValue("in_id", id);

		SimpleJdbcCall jdbcCall = new SimpleJdbcCall(dataSource).withProcedureName("getRecordById");

		Map<String, Object> out = jdbcCall.execute(in);
		Student student = new Student();
		student.setId(id);
		student.setName((String)out.get("out_name"));
		student.setAge((Integer)out.get("out_age"));
		return student;

	}
}

public class StudentMapper implements RowMapper<Student> {
	@Override
	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;
	}
}

public class JDCB1 {
	public static void main(String[] args){
		ApplicationContext context = new ClassPathXmlApplicationContext("./other/JDBC.xml");
		StudentDAO dao =(StudentJDBCTemplate)context.getBean("studentJDBCTemplate");

		// System.out.println("数据插入");
		// dao.insert("xiao1", 11);
		// dao.insert("xiao2", 12);
		// dao.insert("xiao3", 13);

		System.out.println("获取列表");
		List<Student> students = dao.list();
		for(Student record : students){
			System.out.println("id : " + record.getId());
			System.out.println("nam : " + record.getName());
			System.out.println("age : " + record.getAge());
			System.out.println("-----");
		}

		System.out.println("更新数据");
		dao.update(22, 20);

		System.out.println("获取一个数据");
		Student student = dao.getById(22);
		System.out.println("id : " + student.getId());
		System.out.println("nam : " + student.getName());
		System.out.println("age : " + student.getAge());

		System.out.println("存储过程");
		Student student1 = dao.procedure01(23);
		System.out.println("id : " + student1.getId());
		System.out.println("nam : " + student1.getName());
		System.out.println("age : " + student1.getAge());

	}
}

3.3、测试增删改查

3.4、测试存储过程