{"id":3971,"date":"2020-07-08T15:08:50","date_gmt":"2020-07-08T09:38:50","guid":{"rendered":"https:\/\/www.h2kinfosys.com\/blog\/?p=3971"},"modified":"2020-07-08T15:08:52","modified_gmt":"2020-07-08T09:38:52","slug":"what-is-spring-jdbc","status":"publish","type":"post","link":"https:\/\/www.h2kinfosys.com\/blog\/what-is-spring-jdbc\/","title":{"rendered":"What is Spring JDBC?"},"content":{"rendered":"\n<p><a href=\"https:\/\/www.h2kinfosys.com\/blog\/spring-annotations-configuration\/\">Spring <\/a>allows handling database access with the help of the Spring JDBC Template. Spring JDBC allows cleaning up the resources automatically. Also, it converts the JDBC SQL Exceptions into <a href=\"https:\/\/docs.oracle.com\/javase\/7\/docs\/api\/java\/lang\/RuntimeException.html\" rel=\"nofollow noopener\" target=\"_blank\">Run-time Exceptions<\/a>. It provides flexibility to the programmers to react to the errors. It also converts the vendor-specific error messages into an easy and understandable message. There are several ways to query the database:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Using queryForList() \u2013 it returns a list of hashmaps.<\/li><li>Using ResultSetExtractor or RowMapper \u2013 it translates the SQL result directly into an object or a list of objects, respectively.<\/li><\/ul>\n\n\n\n<p>It uses JDBC API internally but eliminates various problems of JDBC API. Some of the major problems of the JDBC API are:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>It requires a lot of code writing before and after executing the query.<\/li><li>You need to handle the transaction.<\/li><li>The repetition of code becomes time-consuming.<\/li><li>You need to perform exception handling code on the database logic.<\/li><\/ul>\n\n\n\n<p>Spring JDBC resolves all the problems mentioned above. It contains a lot of methods while writing a query, which saves a lot of time.<\/p>\n\n\n\n<p>JDBC Template Class is a central class in Spring JDBC. It already takes care of the creation and release of resources, so there is no need to worry if you forgot to close the database connection.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Methods of JDBC Template Class:<\/strong><\/h2>\n\n\n\n<p>1) public int update(String query) &#8211; It is used to insert, update, and delete the records.<\/p>\n\n\n\n<p>2) public int update(String sqlquery, Object&#8230; args) \u2013 It is used to insert, update, and delete records using PreparedStatement using given arguments.<\/p>\n\n\n\n<p>3) public void execute(String query) \u2013 It is used to execute the DDL query.<\/p>\n\n\n\n<p>4) public T execute(String sqlquery, PreparedStatementCallback action) &#8211; It executes the query by using PreparedStatement callback.<\/p>\n\n\n\n<p>5) public T query(String sqlquery, ResultSetExtractor rse) \u2013 It is used to fetch records using ResultSetExtractor.<\/p>\n\n\n\n<p>6) public List query(String sqlquery, RowMapper rse) \u2013 It is used to fetch records using RowMapper.<\/p>\n\n\n\n<p>Given below is an example to demonstrate how to use JDBC Template Class in Spring.<\/p>\n\n\n\n<p><strong>Example:Step 1: <\/strong>Include Spring JDBC and Database drivers in the maven project. Let\u2019s name it as pom.xml<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">&lt;project xmlns=\"https:\/\/maven.apache.org\/POM\/4.0.0\" xmlns:xsi=\"https:\/\/www.w3.org\/2001\/XMLSchema-instance\"\nxsi:schemaLocation=\"https:\/\/maven.apache.org\/POM\/4.0.0 https:\/\/maven.apache.org\/xsd\/maven-4.0.0.xsd\"&gt;\n&lt;modelVersion&gt;4.0.0&lt;\/modelVersion&gt;\n&lt;groupId&gt;org.springframework.samples&lt;\/groupId&gt;\n&lt;artifactId&gt;SpringJDBCExample&lt;\/artifactId&gt;\n&lt;version&gt;0.0.1-SNAPSHOT&lt;\/version&gt;\n&lt;properties&gt;\n&lt;!-- Generic properties --&gt;\n&lt;java.version&gt;1.6&lt;\/java.version&gt;\n&lt;project.build.sourceEncoding&gt;UTF-8&lt;\/project.build.sourceEncoding&gt;\n&lt;project.reporting.outputEncoding&gt;UTF-8&lt;\/project.reporting.outputEncoding&gt;\n&lt;!-- Spring --&gt;\n&lt;spring-framework.version&gt;4.0.2.RELEASE&lt;\/spring-framework.version&gt;\n&lt;!-- Logging --&gt;\n&lt;logback.version&gt;1.0.13&lt;\/logback.version&gt;\n&lt;slf4j.version&gt;1.7.5&lt;\/slf4j.version&gt;\n&lt;\/properties&gt;\n&lt;dependencies&gt;\n&lt;!-- Spring and Transactions --&gt;\n&lt;dependency&gt;\n&lt;groupId&gt;org.springframework&lt;\/groupId&gt;\n&lt;artifactId&gt;spring-context&lt;\/artifactId&gt;\n&lt;version&gt;${spring-framework.version}&lt;\/version&gt;\n&lt;\/dependency&gt;\n&lt;dependency&gt;\n&lt;groupId&gt;org.springframework&lt;\/groupId&gt;\n&lt;artifactId&gt;spring-tx&lt;\/artifactId&gt;\n&lt;version&gt;${spring-framework.version}&lt;\/version&gt;\n&lt;\/dependency&gt;\n&lt;!-- Spring JDBC Support --&gt;\n&lt;dependency&gt;\n&lt;groupId&gt;org.springframework&lt;\/groupId&gt;\n&lt;artifactId&gt;spring-jdbc&lt;\/artifactId&gt;\n&lt;version&gt;${spring-framework.version}&lt;\/version&gt;\n&lt;\/dependency&gt;\n&lt;!-- MySQL Driver --&gt;\n&lt;dependency&gt;\n&lt;groupId&gt;mysql&lt;\/groupId&gt;\n&lt;artifactId&gt;mysql-connector-java&lt;\/artifactId&gt;\n&lt;version&gt;5.0.5&lt;\/version&gt;\n&lt;\/dependency&gt;\n&lt;!-- Logging with SLF4J &amp; LogBack --&gt;\n&lt;dependency&gt;\n&lt;groupId&gt;org.slf4j&lt;\/groupId&gt;\n&lt;artifactId&gt;slf4j-api&lt;\/artifactId&gt;\n&lt;version&gt;${slf4j.version}&lt;\/version&gt;\n&lt;scope&gt;compile&lt;\/scope&gt;\n&lt;\/dependency&gt;\n&lt;dependency&gt;\n&lt;groupId&gt;ch.qos.logback&lt;\/groupId&gt;\n&lt;artifactId&gt;logback-classic&lt;\/artifactId&gt;\n&lt;version&gt;${logback.version}&lt;\/version&gt;\n&lt;scope&gt;runtime&lt;\/scope&gt;\n&lt;\/dependency&gt;\n&lt;\/dependencies&gt;\n&lt;\/project&gt;\n<\/pre>\n\n\n\n<p><strong>Step 2: <\/strong>Create a table in the database.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">CREATE TABLE `Employee` (\n&nbsp;&nbsp;`id` int(11) unsigned NOT NULL,\n&nbsp;&nbsp;`name` varchar(20) DEFAULT NULL,\n&nbsp;&nbsp;`role` varchar(20) DEFAULT NULL,\n&nbsp;&nbsp;PRIMARY KEY (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8;\n<\/pre>\n\n\n\n<p><strong>Step 3: <\/strong>Now we will use DAO pattern so firstly we will create a Model class.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">public class Employee {\nprivate int id;\nprivate String name;\nprivate String role;\npublic int getId() {\nreturn id;\n}\npublic void setId(int id) {\nthis.id = id;\n}\npublic String getName() {\nreturn name;\n}\npublic void setName(String name) {\nthis.name = name;\n}\npublic String getRole() {\nreturn role;\n}\npublic void setRole(String role) {\nthis.role = role;\n}\n@Override\npublic String toString(){\nreturn \"{ID=\"+id+\",Name=\"+name+\",Role=\"+role+\"}\";\n}\n}\n<\/pre>\n\n\n\n<p><strong>Step 4: <\/strong>Create an interface where all the operations are declared.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">import java.util.List;&nbsp;\n\/\/CRUD operations\npublic interface EmployeeDAO {\n\/\/Create\npublic void save(Employee employee);\n\/\/Read\npublic Employee getById(int id);\n\/\/Update\npublic void update(Employee employee);\n\/\/Delete\npublic void deleteById(int id);\n\/\/Get All\npublic List&lt;Employee&gt; getAll();\n}\n-----------------------------------------------------\nimport java.sql.Connection;\nimport java.sql.PreparedStatement;\nimport java.sql.ResultSet;\nimport java.sql.SQLException;\nimport java.util.ArrayList;\nimport java.util.List;&nbsp;\nimport javax.sql.DataSource;&nbsp;\npublic class EmployeeDAOImpl implements EmployeeDAO {\nprivate DataSource dataSource;&nbsp;\npublic void setDataSource(DataSource dataSource) {\nthis.dataSource = dataSource;\n}\n@Override\npublic void save(Employee employee) {\nString query = \"insert into Employee (id, name, role) values (?,?,?)\";\nConnection con = null;\nPreparedStatement ps = null;\ntry{\ncon = dataSource.getConnection();\nps = con.prepareStatement(query);\nps.setInt(1, employee.getId());\nps.setString(2, employee.getName());\nps.setString(3, employee.getRole());\nint out = ps.executeUpdate();\nif(out !=0){\nSystem.out.println(\"Employee saved with id=\"+employee.getId());\n<\/pre>\n\n\n\n<p><strong>Step 5: <\/strong>Create a bean configuration file.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">&lt;?xml version=\"1.0\" encoding=\"UTF-8\"?&gt;\n&lt;beans xmlns=\"https:\/\/www.springframework.org\/schema\/beans\"\nxmlns:xsi=\"https:\/\/www.w3.org\/2001\/XMLSchema-instance\"\nxsi:schemaLocation=\"https:\/\/www.springframework.org\/schema\/beans https:\/\/www.springframework.org\/schema\/beans\/spring-beans.xsd\"&gt;\n&lt;bean id=\"employeeDAO\" class=\"com.spring.jdbc.dao.EmployeeDAOImpl\"&gt;\n&lt;property name=\"dataSource\" ref=\"dataSource\" \/&gt;\n&lt;\/bean&gt;\n&lt;bean id=\"data Source\" class=\"org.springframework.jdbc.datasource.DriverManagerDataSource\"&gt;\n&lt;property name=\"driverClassName\" value=\"com.mysql.jdbc.Driver\" \/&gt;\n&lt;property name=\"url\" value=\"jdbc:mysql:\/\/localhost:8080\/TestDB\" \/&gt;\n&lt;property name=\"username\" value=\"ABC\" \/&gt;\n&lt;property name=\"password\" value=\"ABC123\" \/&gt;\n&lt;\/bean&gt;\n&lt;\/beans&gt;\n<\/pre>\n\n\n\n<p><strong>Step 6: <\/strong>Create a Test Class.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">import java.util.List;\nimport java.util.Random;&nbsp;\nimport org.springframework.context.support.ClassPathXmlApplicationContext;\npublic class SpringMain {\npublic static void main(String[] args) {\n\/\/Get the Spring Context\nClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext(\"spring.xml\");\n\/\/Get the EmployeeDAO Bean\nEmployeeDAO employeeDAO = ctx.getBean(\"employeeDAO\", EmployeeDAO.class);\n\/\/Run some tests for JDBC CRUD operations\nEmployee emp = new Employee();\nint rand = new Random().nextInt(1000);\nemp.setId(rand);\nemp.setName(\"ABC\");\nemp.setRole(\"Java Developer\");\n\/\/Create\nemployeeDAO.save(emp);\n\/\/Read\nEmployee emp1 = employeeDAO.getById(rand);\nSystem.out.println(\"Employee Retrieved::\"+emp1);\n\/\/Update\nemp.setRole(\"CEO\");\nemployeeDAO.update(emp);\n\/\/Get All\nList&lt;Employee&gt; empList = employeeDAO.getAll();\nSystem.out.println(empList);\n\/\/Delete\nemployeeDAO.deleteById(rand);\n\/\/Close Spring Context\nctx.close();\nSystem.out.println(\"DONE\");\n}\n}\n<\/pre>\n\n\n\n<p><strong><em>OUTPUT:<\/em><\/strong><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Employee saved with id=726\nEmployee Found::{ID=726,Name=ABC,Role=Java Developer}\nEmployee Retrieved::{ID=726,Name=ABC,Role=Java Developer}\nEmployee updated with id=726\n[{ID=726,Name=ABC,Role=CEO}]\nEmployee deleted with id=726\n<\/pre>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Spring allows handling database access with the help of the Spring JDBC Template. Spring JDBC allows cleaning up the resources automatically. Also, it converts the JDBC SQL Exceptions into Run-time Exceptions. It provides flexibility to the programmers to react to the errors. It also converts the vendor-specific error messages into an easy and understandable message. [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":3994,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[42],"tags":[1084,1083],"class_list":["post-3971","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-java-tutorials","tag-jdbc-template","tag-spring-jdbc"],"_links":{"self":[{"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/posts\/3971","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/comments?post=3971"}],"version-history":[{"count":0,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/posts\/3971\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/media\/3994"}],"wp:attachment":[{"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/media?parent=3971"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/categories?post=3971"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.h2kinfosys.com\/blog\/wp-json\/wp\/v2\/tags?post=3971"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}