Category: springjdbc

Spring JDBC – Environment Setup ”; Previous Next This chapter will guide you on how to prepare a development environment to start your work with Spring Framework. It will also teach you how to set up JDK, Maven and Eclipse on your machine before you set up Spring Framework − Step 1 – Setup Java Development Kit (JDK) You can download the latest version of SDK from Oracle”s Java site − Java SE Downloads. You will find instructions for installing JDK in downloaded files, follow the given instructions to install and configure the setup. Finally set PATH and JAVA_HOME environment variables to refer to the directory that contains java and javac, typically java_install_dir/bin and java_install_dir respectively. If you are running Windows and have installed the JDK in C:jdk-11.0.11, you would have to put the following line in your C:autoexec.bat file. set PATH=C:jdk-11.0.11;%PATH% set JAVA_HOME=C:jdk-11.0.11 Alternatively, on Windows NT/2000/XP, you will have to right-click on My Computer, select Properties → Advanced → Environment Variables. Then, you will have to update the PATH value and click the OK button. On Unix (Solaris, Linux, etc.), if the SDK is installed in /usr/local/jdk-11.0.11 and you use the C shell, you will have to put the following into your .cshrc file. setenv PATH /usr/local/jdk-11.0.11/bin:$PATH setenv JAVA_HOME /usr/local/jdk-11.0.11 Alternatively, if you use an Integrated Development Environment (IDE) like Borland JBuilder, Eclipse, IntelliJ IDEA, or Sun ONE Studio, you will have to compile and run a simple program to confirm that the IDE knows where you have installed Java. Otherwise, you will have to carry out a proper setup as given in the document of the IDE. Step 2 – Setup Eclipse IDE All the examples in this tutorial have been written using Eclipse IDE. So we would suggest you should have the latest version of Eclipse installed on your machine. To install Eclipse IDE, download the latest Eclipse binaries from www.eclipse.org/downloads. Once you download the installation, unpack the binary distribution into a convenient location. For example, in C:eclipse on Windows, or /usr/local/eclipse on Linux/Unix and finally set PATH variable appropriately. Eclipse can be started by executing the following commands on Windows machine, or you can simply double-click on eclipse.exe %C:eclipseeclipse.exe Eclipse can be started by executing the following commands on Unix (Solaris, Linux, etc.) machine − $/usr/local/eclipse/eclipse After a successful startup, if everything is fine then it should display the following result − Step 3 – Download Maven Archive Download Maven 3.8.4 from https://maven.apache.org/download.cgi. OS Archive name Windows apache-maven-3.8.4-bin.zip Linux apache-maven-3.8.4-bin.tar.gz Mac apache-maven-3.8.4-bin.tar.gz Step 4 – Extract the Maven Archive Extract the archive, to the directory you wish to install Maven 3.8.4. The subdirectory apache-maven-3.8.4 will be created from the archive. OS Location (can be different based on your installation) Windows C:Program FilesApache Software Foundationapache-maven-3.8.4 Linux /usr/local/apache-maven Mac /usr/local/apache-maven Step 5 – Set Maven Environment Variables Add M2_HOME, M2, MAVEN_OPTS to environment variables. OS Output Windows Set the environment variables using system properties. M2_HOME=C:Program FilesApache Software Foundationapache-maven-3.8.4 M2=%M2_HOME%bin MAVEN_OPTS=-Xms256m -Xmx512m Linux Open command terminal and set environment variables. export M2_HOME=/usr/local/apache-maven/apache-maven-3.8.4 export M2=$M2_HOME/bin export MAVEN_OPTS=-Xms256m -Xmx512m Mac Open command terminal and set environment variables. export M2_HOME=/usr/local/apache-maven/apache-maven-3.8.4 export M2=$M2_HOME/bin export MAVEN_OPTS=-Xms256m -Xmx512m Step 6 – Add Maven bin Directory Location to System Path Now append M2 variable to System Path. OS Output Windows Append the string ;%M2% to the end of the system variable, Path. Linux export PATH=$M2:$PATH Mac export PATH=$M2:$PATH Step 7 – Verify Maven Installation Now open console and execute the following mvn command. OS Task Command Windows Open Command Console c:> mvn –version Linux Open Command Terminal $ mvn –version Mac Open Terminal machine:~ joseph$ mvn –version Finally, verify the output of the above commands, which should be as follows − OS Output Windows Apache Maven 3.8.4 (9b656c72d54e5bacbed989b64718c159fe39b537) Maven home: C:Program FilesApache Software Foundationapache-maven-3.8.4 Java version: 11.0.11, vendor: Oracle Corporation, runtime: C:Program FilesJavajdk11.0.11 Default locale: en_IN, platform encoding: Cp1252 OS name: “windows 10”, version: “10.0”, arch: “amd64”, family: “windows” Linux Apache Maven 3.8.4 (9b656c72d54e5bacbed989b64718c159fe39b537) Java version: 11.0.11 Java home: /usr/local/java-current/jre Mac Apache Maven 3.8.4 (9b656c72d54e5bacbed989b64718c159fe39b537) Java version: 11.0.11 Java home: /Library/Java/Home/jre Print Page Previous Next Advertisements ”;

Spring JDBC – Calling Stored Function ”; Previous Next Following example will demonstrate how to call a stored function using Spring JDBC. We”ll read one of the available records in Student Table by calling a stored function. We”ll pass an id and receive a student name. Syntax SimpleJdbcCall jdbcCall = new SimpleJdbcCall(dataSource).withFunctionName(“get_student_name”); SqlParameterSource in = new MapSqlParameterSource().addValue(“in_id”, id); String name = jdbcCall.executeFunction(String.class, in); Student student = new Student(); student.setId(id); student.setName(name); Where, in − SqlParameterSource object to pass a parameter to a stored function. jdbcCall − SimpleJdbcCall object to represent a stored function. jdbcTemplateObject − StudentJDBCTemplate object to called stored function from database. student − Student object. The SimpleJdbcCall class can be used to call a stored function with IN parameter and a return value. You can use this approach while working with either of the RDBMS such as Apache Derby, DB2, MySQL, Microsoft SQL Server, Oracle, and Sybase. To understand the approach, consider the following MySQL stored procedure, which takes student Id and returns the corresponding student”s name. So let us create this stored function in your TEST database using MySQL command prompt − DELIMITER $$ DROP FUNCTION IF EXISTS `TEST`.`get_student_name` $$ CREATE FUNCTION `get_student_name` (in_id INTEGER) RETURNS varchar(200) BEGIN DECLARE out_name VARCHAR(200); SELECT name INTO out_name FROM Student where id = in_id; RETURN out_name; DELIMITER ; To understand the above-mentioned concepts related to Spring JDBC, let us write an example which will call a stored function. To write our example, let us have a working Eclipse IDE in place and use the following steps to create a Spring application. Step Description 1 Update the project Student created under chapter Spring JDBC – First Application. 2 Update the bean configuration and run the application as explained below. Following is the content of the Data Access Object interface file StudentDAO.java. package com.tutorialspoint; 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); /** * 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); } Following is the content of the Student.java file. package com.tutorialspoint; 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; } } Following is the content of the StudentMapper.java file. package com.tutorialspoint; 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; } } Following is the implementation class file StudentJDBCTemplate.java for the defined DAO interface StudentDAO. package com.tutorialspoint; import java.util.List; import javax.sql.DataSource; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; import org.springframework.jdbc.core.namedparam.SqlParameterSource; import org.springframework.jdbc.core.simple.SimpleJdbcCall; 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 Student getStudent(Integer id) { SimpleJdbcCall jdbcCall = new SimpleJdbcCall(dataSource).withFunctionName(“get_student_name”); SqlParameterSource in = new MapSqlParameterSource().addValue(“in_id”, id); String name = jdbcCall.executeFunction(String.class, in); Student student = new Student(); student.setId(id); student.setName(name); return student; } } The code you write for the execution of the call involves creating an SqlParameterSource containing the IN parameter. It”s important to match the name provided for the input value with that of the parameter name declared in the stored function. The executeFunction method takes the IN parameters and returns a String as specified in the stored function. Following is the content of the MainApp.java file package com.tutorialspoint; import java.util.List; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import com.tutorialspoint.StudentJDBCTemplate; public class MainApp { public static void main(String[] args) { ApplicationContext context = new ClassPathXmlApplicationContext(“Beans.xml”); StudentJDBCTemplate studentJDBCTemplate = (StudentJDBCTemplate)context.getBean(“studentJDBCTemplate”); Student student = studentJDBCTemplate.getStudent(1); System.out.print(“ID : ” + student.getId() ); System.out.print(“, Name : ” + student.getName() ); } } Following is the configuration file 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-3.0.xsd “> <!– Initialization for data source –> <bean id = “dataSource” class = “org.springframework.jdbc.datasource.DriverManagerDataSource”> <property name = “driverClassName” value = “com.mysql.cj.jdbc.Driver”/> <property name = “url” value = “jdbc:mysql://localhost:3306/TEST”/> <property name = “username” value = “root”/> <property name = “password” value = “admin”/> </bean> <!– Definition for studentJDBCTemplate bean –> <bean id = “studentJDBCTemplate” class = “com.tutorialspoint.StudentJDBCTemplate”> <property name = “dataSource” ref = “dataSource” /> </bean> </beans> Once you are done creating the source and bean configuration files, let us run the application. If everything is fine with your application, it will print the following message. ID : 1, Name : Zara Print Page Previous Next Advertisements ”;

Spring JDBC – Handling BLOB ”; Previous Next Following example will demonstrate how to update a BLOB using an Update Query with the help of Spring JDBC. We”ll update the available records in Student Table. Student Table CREATE TABLE Student( ID INT NOT NULL AUTO_INCREMENT, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, IMAGE BLOB, PRIMARY KEY (ID) ); Syntax MapSqlParameterSource in = new MapSqlParameterSource(); in.addValue(“id”, id); in.addValue(“image”, new SqlLobValue(new ByteArrayInputStream(imageData), imageData.length, new DefaultLobHandler()), Types.BLOB); String SQL = “update Student set image = :image where id = :id”; NamedParameterJdbcTemplate jdbcTemplateObject = new NamedParameterJdbcTemplate(dataSource); jdbcTemplateObject.update(SQL, in); Where, in − SqlParameterSource object to pass a parameter to update a query. SqlLobValue − Object to represent an SQL BLOB/CLOB value parameter. jdbcTemplateObject − NamedParameterJdbcTemplate object to update student object in database. To understand the above-mentioned concepts related to Spring JDBC, let us write an example which will update a query. To write our example, let us have a working Eclipse IDE in place and use the following steps to create a Spring application. Step Description 1 Update the project Student created under chapter Spring JDBC – First Application. 2 Update the bean configuration and run the application as explained below. Following is the content of the Data Access Object interface file StudentDAO.java. package com.tutorialspoint; 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); /** * This is the method to be used to update * a record into the Student table. */ public void updateImage(Integer id, byte[] imageData); } Following is the content of the Student.java file. package com.tutorialspoint; public class Student { private Integer age; private String name; private Integer id; private byte[] image; 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; } public byte[] getImage() { return image; } public void setImage(byte[] image) { this.image = image; } } Following is the content of the Student.java file. package com.tutorialspoint; 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”)); student.setImage(rs.getBytes(“image”)); return student; } } Following is the implementation class file StudentJDBCTemplate.java for the defined DAO interface StudentDAO. package com.tutorialspoint; import java.util.List; import javax.sql.DataSource; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate; import org.springframework.jdbc.core.namedparam.SqlParameterSource; import org.springframework.jdbc.core.simple.SimpleJdbcCall; import org.springframework.jdbc.core.support.SqlLobValue; import org.springframework.jdbc.support.lob.DefaultLobHandler; import java.io.ByteArrayInputStream; import java.sql.Types; public class StudentJDBCTemplate implements StudentDAO { private DataSource dataSource; private JdbcTemplate jdbcTemplateObject; public void setDataSource(DataSource dataSource) { this.dataSource = dataSource; } public void updateImage(Integer id, byte[] imageData) { MapSqlParameterSource in = new MapSqlParameterSource(); in.addValue(“id”, id); in.addValue(“image”, new SqlLobValue(new ByteArrayInputStream(imageData), imageData.length, new DefaultLobHandler()), Types.BLOB); String SQL = “update Student set image = :image where id = :id”; NamedParameterJdbcTemplate jdbcTemplateObject = new NamedParameterJdbcTemplate(dataSource); jdbcTemplateObject.update(SQL, in); System.out.println(“Updated Record with ID = ” + id ); } } Following is the content of the MainApp.java file. package com.tutorialspoint; import java.util.List; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import com.tutorialspoint.StudentJDBCTemplate; public class MainApp { public static void main(String[] args) { ApplicationContext context = new ClassPathXmlApplicationContext(“Beans.xml”); StudentJDBCTemplate studentJDBCTemplate = (StudentJDBCTemplate)context.getBean(“studentJDBCTemplate”); byte[] imageData = {0,1,0,8,20,40,95}; studentJDBCTemplate.updateImage(1, imageData); } } Following is the configuration file 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-3.0.xsd “> <!– Initialization for data source –> <bean id = “dataSource” class = “org.springframework.jdbc.datasource.DriverManagerDataSource”> <property name = “driverClassName” value = “com.mysql.cj.jdbc.Driver”/> <property name = “url” value = “jdbc:mysql://localhost:3306/TEST”/> <property name = “username” value = “root”/> <property name = “password” value = “admin”/> </bean> <!– Definition for studentJDBCTemplate bean –> <bean id = “studentJDBCTemplate” class = “com.tutorialspoint.StudentJDBCTemplate”> <property name = “dataSource” ref = “dataSource” /> </bean> </beans> Once you are done creating the source and bean configuration files, let us run the application. If everything is fine with your application, it will print the following message. Updated Record with ID = 1 You can check the byte[] stored by querying the database. Print Page Previous Next Advertisements ”;

Spring JDBC – Update Query ”; Previous Next Following example will demonstrate how to update a query using Spring JDBC. We”ll update the available records in Student Table. Syntax String updateQuery = “update Student set age = ? where id = ?”; jdbcTemplateObject.update(updateQuery, age, id); Where, updateQuery − Update query to update student with place holders. jdbcTemplateObject − StudentJDBCTemplate object to update student object in the database. To understand the above-mentioned concepts related to Spring JDBC, let us write an example which will update a query. To write our example, let us have a working Eclipse IDE in place and use the following steps to create a Spring application. Step Description 1 Update the project Student created under chapter Spring JDBC – First Application. 2 Update the bean configuration and run the application as explained below. Following is the content of the Data Access Object interface file StudentDAO.java. package com.tutorialspoint; 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); /** * This is the method to be used to update * a record into the Student table. */ public void update(Integer id, 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); } Following is the content of the Student.java file. package com.tutorialspoint; 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; } } Following is the content of the StudentMapper.java file. package com.tutorialspoint; 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; } } Following is the implementation class file StudentJDBCTemplate.java for the defined DAO interface StudentDAO. package com.tutorialspoint; import java.util.List; import javax.sql.DataSource; import org.springframework.jdbc.core.JdbcTemplate; 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 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; } public Student getStudent(Integer id) { String SQL = “select * from Student where id = ?”; Student student = jdbcTemplateObject.queryForObject( SQL, new Object[]{id}, new StudentMapper() ); return student; } } Following is the content of the MainApp.java file. package com.tutorialspoint; import java.util.List; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import com.tutorialspoint.StudentJDBCTemplate; public class MainApp { public static void main(String[] args) { ApplicationContext context = new ClassPathXmlApplicationContext(“Beans.xml”); StudentJDBCTemplate studentJDBCTemplate = (StudentJDBCTemplate)context.getBean(“studentJDBCTemplate”); System.out.println(“—-Updating Record with ID = 2 —–” ); studentJDBCTemplate.update(2, 20); System.out.println(“—-Listing Record with ID = 2 —–” ); Student student = studentJDBCTemplate.getStudent(2); System.out.print(“ID : ” + student.getId() ); System.out.print(“, Name : ” + student.getName() ); System.out.println(“, Age : ” + student.getAge()); } } Following is the configuration file 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-3.0.xsd “> <!– Initialization for data source –> <bean id = “dataSource” class = “org.springframework.jdbc.datasource.DriverManagerDataSource”> <property name = “driverClassName” value = “com.mysql.cj.jdbc.Driver”/> <property name = “url” value = “jdbc:mysql://localhost:3306/TEST”/> <property name = “username” value = “root”/> <property name = “password” value = “admin”/> </bean> <!– Definition for studentJDBCTemplate bean –> <bean id = “studentJDBCTemplate” class = “com.tutorialspoint.StudentJDBCTemplate”> <property name = “dataSource” ref = “dataSource” /> </bean> </beans> Once you are done creating the source and bean configuration files, let us run the application. If everything is fine with your application, it will print the following message. —-Updating Record with ID = 2 —– Updated Record with ID = 2 —-Listing Record with ID = 2 —– ID : 2, Name : Nuha, Age : 20 Print Page Previous Next Advertisements ”;