\nSo, let’s start Spring JDBC Framework Tutorial.<\/p>\n
![\"Spring](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/06\/Spring-JDBC-Framework-01.jpg\")
<\/a>Spring JDBC Framework – JDBCTemplate with Eclipse IDE<\/p><\/div>\n
2. Spring JDBC Framework<\/h2>\n
Spring JDBC gives several approaches and different classes to use it with the database. So, you just have to define the connection parameters. Also, specify the SQL statement along with doing required work for each iteration while fetching the data. You will now see how to configure the DataSource with an example. Assuming that you are able to work with MySQL database or any database in which you can change DLL and SQL queries accordingly. Create a database TEST having table STUDENT inside it.<\/p>\n The next step is to supply DataSource to JdbcTemplate class so it can configure itself for database access. You can do that in XML file with the following code:<\/p>\n This will configure your DataSource. Data Access Object also called DAO is commonly used for database interaction. It provides a means to read\/write data to a database. They should use this functionality through an interface using which rest of application will access them. Now you will see how to perform Create, Read, Update and Delete operations on database tables using SQL and JdbcTemplate object.<\/p>\n Read About Integration of Spring Logging with log4j \u2013 Eclipse IDE Coding<\/a><\/strong><\/p>\n You can use execute() from JdbcTemplate to execute any SQL or DDL statements. See the below example to use CREATE statement for table creation:<\/p>\n Here let\u2019s define an example for getting a better understanding of Spring JDBC framework. Eclipse IDE is used for coding. The Student table which was created above will be used for explanation. Let’s Learn Integration of MVC with Spring Framework<\/a><\/strong><\/p>\n For the DAO interface StudentDAO use the following implementation class file StudentJDBCTemplate.java:<\/p>\n Finally, if you get all the things correctly then you will get the following as your output: Hence, in this Spring JDBC Tutorial, you learned about the Spring JDBC Framework and how it takes care of all the details. You saw a working example using Eclipse IDE with details for every step. You also saw how Spring JDBC gives several approaches and different classes to use it with the database. If you have any query, feel free to ask in the comment section. 1. Objective In our last tutorial, we saw Spring MVC Framework. In this Spring JDBC Tutorial, we are going to discuss Spring JDBCTemplate with Eclipse IDE.\u00a0Using plain old JDBC for working with a database,...<\/p>\n","protected":false},"author":5,"featured_media":19418,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[64],"tags":[2872,3274,4111,4451,4453,4516,13322,13337,13338,13339,13340,13395],"class_list":["post-19408","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-spring","tag-configuration-of-datasource","tag-data-access-object","tag-eclipse-ide","tag-executing-ddl-statements","tag-executing-sql-statements","tag-f-jdbctemplate","tag-spring-framework-jdbc","tag-spring-jdbc","tag-spring-jdbc-framework","tag-spring-jdbc-framework-tutorial","tag-spring-jdbc-tutorial","tag-springdao"],"yoast_head":"\n
\nHere in this Spring JDBC tutorial, you will be seeing the most popular approach which uses JDBC Template class. It is a central framework class which manages all database communication such as executing SQL, updating statements etc. Also, it catches exceptions of JDBC and translates them to generic exception hierarchy defined in package org.framework.dao.
\nLets Explore Spring Framework Architecture with 4 Modules<\/a><\/strong>
\nThe instances of JdbcTemplate are threaded safe once they are configured. Therefore, you can configure a single instance and then safely inject his into multiple DAOs. A common practice that is done while using Jdbctemplate class is to configure DataSource in Spring config file. Then dependency-inject that into your DAO classes and the JdbcTemplate is created in setter for DataSource.<\/p>\na. Configuration of DataSource<\/h3>\n
CREATE TABLE Student(\r\n ID INT NOT NULL AUTO_INCREMENT,\r\n NAME VARCHAR(20) NOT NULL,\r\n AGE INT NOT NULL,\r\n PRIMARY KEY (ID)\r\n);<\/pre>\n
<bean id = \"dataSource\"\r\nclass = \"org.springframework.jdbc.datasource.DriverManagerDataSource\">\r\n<property name = \"driverClassName\" value = \"com.mysql.jdbc.Driver\"\/>\r\n<property name = \"url\" value = \"jdbc:mysql:\/\/localhost:3306\/TEST\"\/>\r\n<property name = \"username\" value = \"root\"\/>\r\n<property name = \"password\" value = \"password\"\/>\r\n<\/bean><\/pre>\n
\nRead About Spring Framework Environment Setup<\/a><\/strong><\/p>\nb. Data Access Object (DAO)<\/h3>\n
\nThis DAO support in Spring makes it easy to work with technologies like JDBC, Hibernate etc which are used for data access.<\/p>\nc. Executing SQL Statements<\/h3>\n
\n
String SQL = \"select count(*) from Student\";\r\nint rowCount = jdbcTemplateObject.queryForInt( SQL );<\/pre>\n
\n
String SQL = \"select name from Student where id = ?\";\r\nString name = jdbcTemplateObject.queryForObject(SQL, new object[]{10}, String.class);<\/pre>\n\n
String SQL = \"select count(*) from Student\";\r\nlong rowCount = jdbcTemplateObject.queryForLong( SQL );<\/pre>\n
\n
String SQL = \"select * from Student where id = ?\";\r\nStudent student = jdbcTemplateObject.queryForObject(\r\nSQL, new Object[]{10}, new StudentMapper());\r\npublic class StudentMapper implements RowMapper<Student> {\r\npublic Student mapRow(ResultSet rs, int rowNum) throws SQLException {\r\nStudent student = new Student();\r\nstudent.setID(rs.getInt(\"id\"));\r\nstudent.setName(rs.getString(\"name\"));\r\nstudent.setAge(rs.getInt(\"age\"));\r\nreturn student;\r\n}\r\n}<\/pre>\n\n
String SQL = \"select * from Student\";\r\nList<Student> students = jdbcTemplateObject.query(\r\nSQL, new StudentMapper());\r\npublic class StudentMapper implements RowMapper<Student> {\r\npublic Student mapRow(ResultSet rs, int rowNum) throws SQLException {\r\nStudent student = new Student();\r\nstudent.setID(rs.getInt(\"id\"));\r\nstudent.setName(rs.getString(\"name\"));\r\nstudent.setAge(rs.getInt(\"age\"));\r\nreturn student;\r\n}\r\n}<\/pre>\n\n
String SQL = \"insert into Student (name, age) values (?, ?)\";\r\njdbcTemplateObject.update( SQL, new Object[]{\"ABC\", 41} );<\/pre>\n\n
String SQL = \"update Student set name = ? where id = ?\";\r\njdbcTemplateObject.update( SQL, new Object[]{\"ABC\", 41} );<\/pre>\n\n
String SQL = \"delete Student where id = ?\";\r\njdbcTemplateObject.update( SQL, new Object[]{10} );<\/pre>\nd. Executing DDL Statements<\/h3>\n
String SQL = \"CREATE TABLE Student( \" +\r\n\"ID INT NOT NULL AUTO_INCREMENT, \" +\r\n\"NAME VARCHAR(20) NOT NULL, \" +\r\n\"AGE INT NOT NULL, \" +\r\n\"PRIMARY KEY (ID));\"\r\njdbcTemplateObject.execute( SQL );<\/pre>\n
e. Working Example for JDBC Template<\/h3>\n
\nBefore defining let\u2019s see the steps that are required:<\/p>\n\n
\nFollowing is code for StudentDAO.java file:<\/p>\npackage com.example;\r\nimport java.util.List;\r\nimport javax.sql.DataSource;\r\npublic interface StudentDAO {\r\n\/**\r\n* This is to be used to initialize\r\n* databaseconnection.\r\n*\/\r\npublic void setDataSource(DataSource ds);\r\n\/**\r\n* This is to be used to create\r\n* a record in Student table.\r\n*\/\r\npublic void create(String name, Integer age);\r\n\/**\r\n* This is to be used to list down\r\n* a record from the Student table corresponding\r\n* to a passed student id.\r\n*\/\r\npublic Student getStudent(Integer id);\r\n\/**\r\n* This is to be used to list down\r\n* all the records from the Student table.\r\n*\/\r\npublic List<Student> listStudents();\r\n\/**\r\n* This is to be used to delete\r\n* a record from the Student table corresponding\r\n* to a passed student id.\r\n*\/\r\npublic void delete(Integer id);\r\n\/**\r\n* This is to be used to update\r\n* a record into the Student table.\r\n*\/\r\npublic void update(Integer id, Integer age);\r\n}<\/pre>\ni. Now write the below Student.Java:<\/h4>\n
package com.example;\r\npublic class Student {\r\nprivate Integer age;\r\nprivate String name;\r\nprivate Integer id;\r\npublic void setAge(Integer age) {\r\nthis.age = age;\r\n}\r\npublic Integer getAge() {\r\nreturn age;\r\n}\r\npublic void setName(String name) {\r\nthis.name = name;\r\n}\r\npublic String getName() {\r\nreturn name;\r\n}\r\npublic void setId(Integer id) {\r\nthis.id = id;\r\n}\r\npublic Integer getId() {\r\nreturn id;\r\n}\r\n}<\/pre>\nii. Following is the code for StudentMapper.Java:<\/h4>\n
package com.example;\r\nimport java.sql.ResultSet;\r\nimport java.sql.SQLException;\r\nimport org.springframework.jdbc.core.RowMapper;\r\npublic class StudentMapper implements RowMapper<Student> {\r\npublic Student mapRow(ResultSet rs, int rowNum) throws SQLException {\r\nStudent student = new Student();\r\nstudent.setId(rs.getInt(\"id\"));\r\nstudent.setName(rs.getString(\"name\"));\r\nstudent.setAge(rs.getInt(\"age\"));\r\nreturn student;\r\n}\r\n}<\/pre>\niii. Implement this class file<\/h4>\n
package com.example;\r\nimport java.util.List;\r\nimport javax.sql.DataSource;\r\nimport org.springframework.jdbc.core.JdbcTemplate;\r\npublic class StudentJDBCTemplate implements StudentDAO {\r\nprivate DataSource dataSource;\r\nprivate JdbcTemplate jdbcTemplateObject;\r\npublic void setDataSource(DataSource dataSource) {\r\nthis.dataSource = dataSource;\r\nthis.jdbcTemplateObject = new JdbcTemplate(dataSource);\r\n}\r\npublic void create(String name, Integer age) {\r\nString SQL = \"insert into Student (name, age) values (?, ?)\";\r\njdbcTemplateObject.update( SQL, name, age);\r\nSystem.out.println(\"Created Record Name = \" + name + \" Age = \" + age);\r\nreturn;\r\n}\r\npublic Student getStudent(Integer id) {\r\nString SQL = \"select * from Student where id = ?\";\r\nStudent student = jdbcTemplateObject.queryForObject(SQL,\r\nnew Object[]{id}, new StudentMapper());\r\nreturn student;\r\n}\r\npublic List<Student> listStudents() {\r\nString SQL = \"select * from Student\";\r\nList <Student> students = jdbcTemplateObject.query(SQL, new StudentMapper());\r\nreturn students;\r\n}\r\npublic void delete(Integer id) {\r\nString SQL = \"delete from Student where id = ?\";\r\njdbcTemplateObject.update(SQL, id);\r\nSystem.out.println(\"Deleted Record with ID = \" + id );\r\nreturn;\r\n}\r\npublic void update(Integer id, Integer age){\r\nString SQL = \"update Student set age = ? where id = ?\";\r\njdbcTemplateObject.update(SQL, age, id);\r\nSystem.out.println(\"Updated Record with ID = \" + id );\r\nreturn;\r\n}\r\n}<\/pre>\niv.\u00a0 Now the code for MainApp.java is as shown:<\/h4>\n
package com.example;\r\nimport java.util.List;\r\nimport org.springframework.context.ApplicationContext;\r\nimport org.springframework.context.support.ClassPathXmlApplicationContext;\r\nimport com.example.StudentJDBCTemplate;\r\npublic class MainApp {\r\npublic static void main(String[] args) {\r\nApplicationContext context = new ClassPathXmlApplicationContext(\"Beans.xml\");\r\nStudentJDBCTemplate studentJDBCTemplate =\r\n(StudentJDBCTemplate)context.getBean(\"studentJDBCTemplate\");\r\nSystem.out.println(\"------Records Creation--------\" );\r\nstudentJDBCTemplate.create(\"Hari\", 11);\r\nstudentJDBCTemplate.create(\"Nuha\", 2);\r\nstudentJDBCTemplate.create(\"Ayan\", 15);\r\nSystem.out.println(\"------Listing Multiple Records--------\" );\r\nList<Student> students = studentJDBCTemplate.listStudents();\r\nfor (Student record: students) {\r\nSystem.out.print(\"ID : \" + record.getId() );\r\nSystem.out.print(\", Name : \" + record.getName() );\r\nSystem.out.println(\", Age : \" + record.getAge());\r\n}\r\nSystem.out.println(\"----Updating Record with ID = 2 -----\" );\r\nstudentJDBCTemplate.update(2, 20);\r\nSystem.out.println(\"----Listing Record with ID = 2 -----\" );\r\nStudent student = studentJDBCTemplate.getStudent(2);\r\nSystem.out.print(\"ID : \" + student.getId() );\r\nSystem.out.print(\", Name : \" + student.getName() );\r\nSystem.out.println(\", Age : \" + student.getAge());\r\n}\r\n}<\/pre>\nv. Now you will write the Beans.xml:<\/h4>\n
<?xml version = \"1.0\" encoding = \"UTF-8\"?>\r\n<beans xmlns = \"http:\/\/www.springframework.org\/schema\/beans\"\r\nxmlns:xsi = \"http:\/\/www.w3.org\/2001\/XMLSchema-instance\"\r\nxsi:schemaLocation = \"http:\/\/www.springframework.org\/schema\/beans\r\nhttp:\/\/www.springframework.org\/schema\/beans\/spring-beans-3.0.xsd \">\r\n<!-- Initialization for data source -->\r\n<bean id=\"dataSource\"\r\nclass = \"org.springframework.jdbc.datasource.DriverManagerDataSource\">\r\n<property name = \"driverClassName\" value = \"com.mysql.jdbc.Driver\"\/>\r\n<property name = \"url\" value = \"jdbc:mysql:\/\/localhost:3306\/TEST\"\/>\r\n<property name = \"username\" value = \"root\"\/>\r\n<property name = \"password\" value = \"password\"\/>\r\n<\/bean>\r\n<!-- Definition for studentJDBCTemplate bean -->\r\n<bean id = \"studentJDBCTemplate\"\r\nclass = \"com.example.StudentJDBCTemplate\">\r\n<property name = \"dataSource\" ref = \"dataSource\" \/>\r\n<\/bean>\r\n<\/beans><\/pre>\n
\n——Records Creation——–<\/strong>
\nCreated Record Name = Hari Age = 11<\/strong>
\nCreated Record Name = Nuha Age = 2<\/strong>
\nCreated Record Name = Ayan Age = 15<\/strong>
\n——Listing Multiple Records——–<\/strong>
\nID : 1, Name : Hari, Age : 11<\/strong>
\nID : 2, Name : Nuha, Age : 2<\/strong>
\nID : 3,<\/strong> Name: Ayan, Age: 15<\/strong>
\n—-Updating Record with ID = 2 —–<\/strong>
\nUpdated Record with ID = 2<\/strong>
\n—-Listing Record with ID = 2 —–<\/strong>
\nID : 2, Name : Nuha, Age : 20<\/strong>
\nSo, this was all about Spring JDBC Framework Tutorial. Hope you like our explanation.<\/p>\n3. Conclusion<\/h2>\n
\nRelated Topic-\u00a0How to Configure Spring Beans<\/strong><\/a>
\nFor reference<\/a><\/strong><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"