Loading
 
What's Next: View All Tutorial →

Spring JDBC & MySQL Connection using JDBCTemplate and Datasource mapping

A simple program to connect mysql using the jdbcTemplate with datasource connection   Maven Dependency   <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi=&...

Published Date: 04/09/2018  Last Modified Date: 04/09/2018    New Post



A simple program to connect mysql using the jdbcTemplate with datasource connection
 

Maven Dependency

 
<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>com.demo</groupId>
	<artifactId>spring-jdbc</artifactId>
	<version>1.0</version>
	<name>spring-jdbc</name>
	<properties>
		<maven.compiler.source>1.8</maven.compiler.source>
		<maven.compiler.target>1.8</maven.compiler.target>
		<spring.version>5.0.8.RELEASE</spring.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>org.apache.logging.log4j</groupId>
			<artifactId>log4j-core</artifactId>
			<version>2.8.2</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>${spring.version}</version>
		</dependency>

		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-tx</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.46</version>
		</dependency>
	</dependencies>
</project>

spring-jdbc and spring-tx dependency is needed

DaoConfig.java

 
package com.demo.spring;

import javax.sql.DataSource;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

@Configuration
@ComponentScan(basePackages = "com.demo.spring")
public class DaoConfig {

	@Bean
	public DriverManagerDataSource ds() {
		DriverManagerDataSource ds = new DriverManagerDataSource();
		ds.setDriverClassName("com.mysql.jdbc.Driver");
		ds.setUrl("jdbc:mysql://localhost:3306/springdb?useSSL=false");
		ds.setUsername("root");
		ds.setPassword("admin");
		return ds;
	}

	@Bean
	public JdbcTemplate jt(DataSource ds) {

		JdbcTemplate jt = new JdbcTemplate();
		jt.setDataSource(ds);
		return jt;
	}
}

JdbcTemplate - jdbcTemplate is used to query a database

DriverManagerDataSource - to create a connection

Emp.java

package com.demo.spring.entity;

public class Emp {

	private int empId;
	private String name;
	private String city;
	private double salary;

	public Emp() {

	}

	public Emp(int empId, String name, String city, double salary) {

		this.empId = empId;
		this.name = name;
		this.city = city;
		this.salary = salary;
	}

	public int getEmpId() {
		return empId;
	}

	public void setEmpId(int empId) {
		this.empId = empId;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getCity() {
		return city;
	}

	public void setCity(String city) {
		this.city = city;
	}

	public double getSalary() {
		return salary;
	}

	public void setSalary(double salary) {
		this.salary = salary;
	}

	@Override
	public String toString() {
		return "Emp [empId=" + empId + ", name=" + name + ", city=" + city + ", salary=" + salary + "]";
	}

}
 

App.java

package com.demo.spring;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;

import com.demo.spring.entity.Emp;

public class App {

	public static void main(String args[]) {
		ApplicationContext ctx = new AnnotationConfigApplicationContext(DaoConfig.class);

		JdbcTemplate jt = ctx.getBean(JdbcTemplate.class);

		int count = jt.update(new PreparedStatementCreator() {

			@Override
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {

				String query = "insert into emp (empno,name,address,salary) values (?,?,?,?)";
				PreparedStatement pst = con.prepareStatement(query);
				pst.setInt(1, 108);
				pst.setString(2, "John");
				pst.setString(3, "New York");
				pst.setDouble(4, 50000);
				return pst;
			}
		});

		System.out.println("Rows Inserted" + count);

		// To get all list as each item in Map
		List empList = jt.queryForList("SELECT * FROM emp");
		System.out.println(empList);
		empList.stream().forEach(System.out::println);

		// To bind to Bean class
		List<Emp> empBeanList = jt.query("select * from emp", new BeanPropertyRowMapper(Emp.class));
		System.out.println(empBeanList);

		empBeanList.stream().forEach(e -> System.out.println(e.getName()));

		List<Emp> empRowList = jt.query("select * from emp", new RowMapper<Emp>() {
			@Override
			public Emp mapRow(ResultSet rs, int rowNum) throws SQLException {
				return new Emp(rs.getInt("empno"), rs.getString("name"), rs.getString("address"),
						rs.getDouble("salary"));
			}
		});

		empRowList.stream().forEach(System.out::println);

	}
}

BeanPropertyRowMapper 
- it maps the specific bean passed as an argument
RowMapper
 - is used to get raw data and we can map to any object

Output:

Rows Inserted1
12:03:29.790 [main] DEBUG org.springframework.jdbc.core.JdbcTemplate - Executing SQL query [SELECT * FROM emp]
12:03:29.791 [main] DEBUG org.springframework.jdbc.datasource.DataSourceUtils - Fetching JDBC Connection from DataSource
12:03:29.791 [main] DEBUG org.springframework.jdbc.datasource.DriverManagerDataSource - Creating new JDBC DriverManager Connection to [jdbc:mysql://localhost:3306/springdb?useSSL=false]
12:03:29.811 [main] DEBUG org.springframework.jdbc.datasource.DataSourceUtils - Returning JDBC Connection to DataSource
[{empno=100, name=Amitabh, address=Mumbai, salary=20000.0}, {empno=101, name=Shekhar, address=Hyderabad, salary=30000.0},{empno=108, name=John, address=New York, salary=50000.0}]
{empno=100, name=Amitabh, address=Mumbai, salary=20000.0}
{empno=108, name=John, address=New York, salary=50000.0}
12:03:29.859 [main] DEBUG org.springframework.jdbc.core.JdbcTemplate - Executing SQL query [select * from emp]
12:03:29.859 [main] DEBUG org.springframework.jdbc.datasource.DataSourceUtils - Fetching JDBC Connection from DataSource
12:03:29.859 [main] DEBUG org.springframework.jdbc.datasource.DriverManagerDataSource - Creating new JDBC DriverManager Connection to [jdbc:mysql://localhost:3306/springdb?useSSL=false]
12:03:29.866 [main] DEBUG org.springframework.jdbc.core.BeanPropertyRowMapper - No property found for column 'empno' mapped to field 'empno'
12:03:29.866 [main] DEBUG org.springframework.jdbc.core.BeanPropertyRowMapper - Mapping column 'name' to property 'name' of type 'java.lang.String'
12:03:29.871 [main] DEBUG org.springframework.jdbc.core.BeanPropertyRowMapper - No property found for column 'address' mapped to field 'address'
12:03:29.872 [main] DEBUG org.springframework.jdbc.core.BeanPropertyRowMapper - Mapping column 'salary' to property 'salary' of type 'double'
12:03:29.879 [main] DEBUG org.springframework.jdbc.datasource.DataSourceUtils - Returning JDBC Connection to DataSource
[Emp [empId=0, name=Amitabh, city=null, salary=20000.0],  Emp [empId=0, name=John, city=null, salary=50000.0], Emp [empId=0, name=John, city=null, salary=50000.0]]
Amitabh
John
12:03:29.881 [main] DEBUG org.springframework.jdbc.core.JdbcTemplate - Executing SQL query [select * from emp]
12:03:29.881 [main] DEBUG org.springframework.jdbc.datasource.DataSourceUtils - Fetching JDBC Connection from DataSource
12:03:29.881 [main] DEBUG org.springframework.jdbc.datasource.DriverManagerDataSource - Creating new JDBC DriverManager Connection to [jdbc:mysql://localhost:3306/springdb?useSSL=false]
12:03:29.889 [main] DEBUG org.springframework.jdbc.datasource.DataSourceUtils - Returning JDBC Connection to DataSource
Emp [empId=100, name=Amitabh, city=Mumbai, salary=20000.0]
Emp [empId=108, name=John, city=New York, salary=50000.0]



 
Awaiting for Administrator approval





Tags: Spring Core

← Back to list


Related Post




×