使用Spring JDBC框架连接并操作数据库
来源:互联网 发布:python 防止sql注入 编辑:程序博客网 时间:2024/06/05 15:41
在前一篇博文JAVA通过JDBC连接并操作MySQL数据库中,我们知道如何通过JDBC连接并操作数据库,但是请看程序,整个程序连接数据库和关闭数据库占了很大一部分代码量,而且每次我们执行一下数据库操作都得来这么一大段重复代码,这是很烦人的。而在Spring框架中同样提供了JDBC框架,以供我们操作数据库。spring中的JDBC框架则可以为我们省去连接和关闭数据库的代码,我们只要关注我们想对数据库进行的操作即可,下面开始介绍吧。
同样的,我们需要在MySQL中创建一个table,以供我们测试使用。
CREATE TABLE student(ID VARCHAR(5),name VARCHAR(20),age int(3),FM VARCHAR(1),PRIMARY KEY(ID))
表格效果如下图:
有了数据库之后,建好maven工程,然后需要配置数据源。在Spring的JDBC框架中,数据源配置在Beans.xml中,当然这个文件名可以随便取的。
整个Beans.xml配置文件如下所示:
<?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" /> <!--注意一下&characterEncoding要修改为&characterEncoding--><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
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
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 来完成数据库的读写操作。
我们的接口定义如下:
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);}
接口实现定义如下:
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
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如下:
<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> <!--spring transaction--><version>4.1.6.RELEASE</version></dependency> </dependencies></project>
最终运行结果如下:
五月 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 M2008 Wang 16 F2009 Hui 18 F2010 Yu 20 M2015 Yuan 22 M2018 Yang 21 F---------updatestudent-------------1990 Yuan 18 M2008 Wang 16 F2009 Hui 18 F2010 Yu 20 M2015 Yuan 22 M2018 Yang 21 F---------querystudentbyID-------------1990 Yuan 18 M---------querystudentbyname-------------1990 Yuan 18 M2015 Yuan 22 M---------querystudentbyage-------------2010 Yu 20 M---------delstudentbyage-------------1990 Yuan 18 M2008 Wang 16 F2009 Hui 18 F2010 Yu 20 M2015 Yuan 22 M---------delstudentbyname-------------1990 Yuan 18 M2008 Wang 16 F2010 Yu 20 M2015 Yuan 22 M---------delallstudent-------------
本文参考文献:
http://www.tutorialspoint.com/spring/spring_jdbc_framework.htm
http://www.tutorialspoint.com/spring/spring_jdbc_example.htm
- 使用Spring JDBC框架连接并操作数据库
- 使用Spring JDBC框架连接并操作数据库
- 使用JDBC连接并操作数据库
- spring框架jdbc连接数据库
- Spring使用JDBC操作数据库
- jdbc连接数据库并进行操作
- JDBC连接并使用mysql数据库
- java项目使用spring jdbc连接数据库
- 连接数据库jdbc操作
- JDBC连接数据库操作
- jdbc连接数据库操作
- jdbc 连接数据库并进行操作相关代码
- JAVA通过JDBC连接并操作MySQL数据库
- 如何利用JDBC连接并操作Oracle数据库
- Java 通过JDBC连接并操作Mysql数据库
- 《Spring JDBC 操作数据库》
- 使用spring jdbc template简化jdbc数据库操作实例代码
- 使用 OCILIB 连接并操作 Oracle 数据库
- 学习Zynq的好的入门博客
- CentOS安装hadoop2.6.0
- 算法之神奇的位运算
- 2-2
- 【8. HA模块】云跳板机服务系统设计及实现
- 使用Spring JDBC框架连接并操作数据库
- 请柬(invite)
- C语言输出菱形for循环
- 设计模式之Proxy模式(笔记)
- win git error init_cheap-VirtualAlloc pointer is null, Win32 error 487
- thinkphp 退出登陆
- 递归
- OC基础语法学习2:面向对象中的特点
- 树莓派 libcurl安装