Spring深入浅出(十四),JDBC,Oracle数据库基本CRUD操作,以及获得插入记录的主键值
2021/7/15 19:08:15
本文主要是介绍Spring深入浅出(十四),JDBC,Oracle数据库基本CRUD操作,以及获得插入记录的主键值,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
写一个简单示例,阐述Spring环境、Oracle数据库之下,实体表的CRUD 操作;包括获得插入记录的主键值。
首先,需要在项目中引用JDBC包,包括JDBC包和事务驱动包:
- spring-jdbc-5.2.3.RELEASE.jar
- spring-tx-5.2.3.RELEASE.jar
一、创建表,创建序列(Oracle数据库,序列用于自增长主键)
CREATE TABLE STUDENT ( ID NUMBER NOT NULL , NAME VARCHAR2(20 BYTE) NOT NULL , AGE NUMBER NOT NULL , CONSTRAINT STUDENT_PK PRIMARY KEY (ID) ); create sequence SEQ_STUDENT;
二、创建实体类
package com.clzhang.spring.demo.jdbc; public class Student { private Integer age; private String name; private Integer id; public void setAge(Integer age) { this.age = age; } public Integer getAge() { return age; } public void setName(String name) { this.name = name; } public String getName() { return name; } public void setId(Integer id) { this.id = id; } public Integer getId() { return id; } }
三、创建数据访问对象接口文件(DAO)
package com.clzhang.spring.demo.jdbc; 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 create(String name, Integer age); // 插入一条记录,并返回主键值 public int insert(String name, Integer age); /** * This is the method to be used to list down a record from the Student table * corresponding to a passed student id. */ public Student getStudent(Integer id); /** * This is the method to be used to list down all the records from the Student * table. */ public List<Student> listStudents(); /** * This is the method to be used to delete a record from the Student table * corresponding to a passed student id. */ public void delete(Integer id); /** * This is the method to be used to update a record into the Student table. */ public void update(Integer id, Integer age); }
四、创建Mapper,Sping中的RowMapper可以将数据中的每一行数据封装成用户定义的类。
package com.clzhang.spring.demo.jdbc; 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.getInt("id")); student.setName(rs.getString("name")); student.setAge(rs.getInt("age")); return student; } }
五、定义的 DAO 接口的实现类
package com.clzhang.spring.demo.jdbc; import java.util.List; import javax.sql.*; import java.sql.*; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.PreparedStatementCreator; import org.springframework.jdbc.support.GeneratedKeyHolder; import org.springframework.jdbc.support.KeyHolder; public class StudentJDBCTemplate implements StudentDAO { private DataSource dataSource; private JdbcTemplate jdbcTemplateObject; public void setDataSource(DataSource dataSource) { this.dataSource = dataSource; this.jdbcTemplateObject = new JdbcTemplate(dataSource); } // 不需要返回主键值的方法 public void create(String name, Integer age) { String SQL = "insert into Student (id, name, age) values (SEQ_STUDENT.nextval, ?, ?)"; jdbcTemplateObject.update( SQL, name, age); System.out.println("Created Record Name = " + name + " Age = " + age); return; } // 需要返回主键值的方法 public int insert(final String name, final Integer age) { final String SQL = "insert into Student (id, name, age) values (SEQ_STUDENT.nextval, ?, ?)"; KeyHolder keyHolder = new GeneratedKeyHolder(); jdbcTemplateObject.update(new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection conn) throws SQLException { PreparedStatement ps = conn.prepareStatement(SQL,new String[] {"id"}); ps.setString(1, name); ps.setInt(2, age); return ps; } },keyHolder); return keyHolder.getKey().intValue(); } public Student getStudent(Integer id) { String SQL = "select * from Student where id = ?"; Student student = jdbcTemplateObject.queryForObject(SQL, new Object[] { id }, new StudentMapper()); return student; } public List<Student> listStudents() { String SQL = "select * from Student"; List<Student> students = jdbcTemplateObject.query(SQL, new StudentMapper()); return students; } public void delete(Integer id) { String SQL = "delete from Student where id = ?"; jdbcTemplateObject.update(SQL, id); System.out.println("Deleted Record with ID = " + id); return; } public void update(Integer id, Integer age) { String SQL = "update Student set age = ? where id = ?"; jdbcTemplateObject.update(SQL, age, id); System.out.println("Updated Record with ID = " + id); return; } }
六、创建主程序
package com.clzhang.spring.demo.jdbc; import java.util.List; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; public class MainApp { public static void main(String[] args) { ApplicationContext context = new ClassPathXmlApplicationContext("Beans.xml"); StudentJDBCTemplate studentJDBCTemplate = (StudentJDBCTemplate) context.getBean("studentJDBCTemplate"); System.out.println("------Insert three records--------"); studentJDBCTemplate.create("张三", 11); studentJDBCTemplate.create("李四", 12); int id = studentJDBCTemplate.insert("赵五", 13); System.out.println("New Student id: " + id); System.out.println("------Query--------"); List<Student> students = studentJDBCTemplate.listStudents(); for (Student record : students) { System.out.print("ID : " + record.getId()); System.out.print(", Name : " + record.getName()); System.out.println(", Age : " + record.getAge()); } System.out.println("----Update one record-----"); studentJDBCTemplate.update(id, 20); System.out.println("----Query again-----"); Student student = studentJDBCTemplate.getStudent(id); System.out.print("ID : " + student.getId()); System.out.print(", Name : " + student.getName()); System.out.println(", Age : " + student.getAge()); System.out.println("----Delete-----"); studentJDBCTemplate.delete(id); // 此句将引起异常 // student = studentJDBCTemplate.getStudent(id); } }
七、创建配置文件
<?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="oracle.jdbc.driver.OracleDriver"/> <property name="url" value="jdbc:oracle:thin:@127.0.0.1:1521:orcl"/> <property name="username" value="mytest"/> <property name="password" value="test001"/> </bean> <!-- Definition for studentJDBCTemplate bean --> <bean id="studentJDBCTemplate" class="com.clzhang.spring.demo.jdbc.StudentJDBCTemplate"> <property name="dataSource" ref="dataSource" /> </bean> </beans>
八、运行
------Insert three records-------- Created Record Name = 张三 Age = 11 Created Record Name = 李四 Age = 12 New Student id: 4 ------Query-------- ID : 2, Name : 张三, Age : 11 ID : 3, Name : 李四, Age : 12 ID : 4, Name : 赵五, Age : 13 ----Update one record----- Updated Record with ID = 4 ----Query again----- ID : 4, Name : 赵五, Age : 20 ----Delete----- Deleted Record with ID = 4
本文参考:
https://www.w3cschool.cn/wkspring/iuck1mma.html
这篇关于Spring深入浅出(十四),JDBC,Oracle数据库基本CRUD操作,以及获得插入记录的主键值的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-11-04数据结构与算法教程:初学者必备指南
- 2024-11-04搜索算法教程:初学者必看指南
- 2024-11-04算法八股文教程:新手入门指南
- 2024-11-04算法复杂度教程:入门与实践指南
- 2024-11-04算法高级教程:新手入门与初级提升指南
- 2024-11-04初学者的算法教程:轻松入门算法世界
- 2024-11-04算法面试教程:零基础入门与实战技巧
- 2024-11-04算法设计思路教程:新手入门详解
- 2024-11-04优先队列进阶:从入门到初步掌握
- 2024-11-04DP优化进阶:从入门到初级提升指南