255 lines
8.8 KiB
Markdown
255 lines
8.8 KiB
Markdown
Coding 3 (Spring REST)
|
|
- Before diving into this some terminology and concepts
|
|
- JDBC (Java Database Connectivity) : It is an API that is inbuilt in java for connecting to database
|
|
- JPA (Java Persistent API) : It is ORM (Object Relational Mapping) . Useful to directly work with java objects instead of SQL statements. It maps java objects properties to respective fields of table of relatioal database.
|
|
- JDBCTemplate : Internally use JDBC and eliminate problem with JDBC and make code more clear and concise. Enables us to directly write SQL queries.This is related to Spring boot
|
|
- MySQL Connector/J (JDBC driver) : It is a JDBC Type 4 driver, which means that it is pure Java implementation of the MySQL protocol and does not rely on the MySQL client . This is required to use mysql in spring boot
|
|
There is MySQL Connector/Java too but it is provided by third party and isnt official one
|
|
- references : https://spring.io/guides/gs/relational-data-access
|
|
https://spring.io/guides/gs/accessing-data-mysql
|
|
https://www.geeksforgeeks.org/how-to-execute-multiple-sql-commands-on-a-database-simultaneously-in-jdbc/ (normal jdbc not jdbctemplate)
|
|
https://mkyong.com/spring/spring-jdbctemplate-batchupdate-example/ (for batchupdate)
|
|
- Java concepts
|
|
- Anonymous inner class : A class with no name for which single object is created. Useful to create a new object without needing to create a subclass with overloading/overriding methods. To explain this lets simplify it to its normal form step by step
|
|
Eg:
|
|
Anonymous inner class
|
|
```java
|
|
new BatchPreparedStatementSetter() {
|
|
|
|
public void setValues(PreparedStatement ps, int i) throws SQLException {
|
|
ps.setString(1, books.get(i).getName());
|
|
ps.setBigDecimal(2, books.get(i).getPrice());
|
|
}
|
|
|
|
public int getBatchSize() {
|
|
return books.size();
|
|
}
|
|
|
|
});
|
|
|
|
```
|
|
|
|
inner class
|
|
```java
|
|
BatchPreparedStatementSetter setter = new BatchPreparedStatementSetter() {
|
|
@Override
|
|
public void setValues(PreparedStatement ps, int i) throws SQLException {
|
|
// Implementation of setting values for the prepared statement
|
|
}
|
|
|
|
@Override
|
|
public int getBatchSize() {
|
|
// Implementation of batch size
|
|
}
|
|
};
|
|
|
|
```
|
|
|
|
a proper class
|
|
```java
|
|
|
|
// here BatchPreparedStatmentSetter in interface not class thats why implements is used not extends
|
|
public class BookBatchPreparedStatementSetter implements BatchPreparedStatementSetter {
|
|
|
|
private final List<Book> books;
|
|
|
|
public BookBatchPreparedStatementSetter(List<Book> books) {
|
|
this.books = books;
|
|
}
|
|
|
|
@Override
|
|
public void setValues(PreparedStatement ps, int i) throws SQLException {
|
|
ps.setString(1, books.get(i).getName());
|
|
ps.setBigDecimal(2, books.get(i).getPrice());
|
|
}
|
|
|
|
@Override
|
|
public int getBatchSize() {
|
|
return books.size();
|
|
}
|
|
}
|
|
|
|
```
|
|
- In the following code few things like
|
|
- Dependencies that are to be installed are `spring-web`, `jdbc-api` and `mysql-connector-j`.
|
|
- A separate class is used for this which will act as a bean called `DataLoader`.
|
|
- Object of `JdbcTemplate` class can be created with DI through Autowired annotation.
|
|
- execute method is used to perform sql query. By default only single query is allowed. to allow multiple queries `spring.datasource.url` must contain query parameter as `allowMultiQueries=true`.
|
|
- There are other methods like `update`, `query`, `queryForList`, `batchUpdate` which accept 2nd/3rd parameter after sql query(1st para) and they support placeholder ie `?` for sql queries.
|
|
- update method is for operations like insert,update,etc. query is for select, queryForList will return the records without need for manually mapping them. batchupdate is used to insert/update multiple records at once. same can be done with update too as it accepts array too but multiple placeholders needed to be added too and code would not be readable.
|
|
in `application.properties`
|
|
```yml
|
|
spring.application.name=demo
|
|
spring.datasource.url=jdbc:mysql://localhost:3306/test?allowMultiQueries=true
|
|
spring.datasource.username=root
|
|
spring.datasource.password=<password>
|
|
```
|
|
|
|
Add following under dependencies section
|
|
in `pom.xml`
|
|
```xml
|
|
<dependency>
|
|
<groupId>org.springframework.boot</groupId>
|
|
<artifactId>spring-boot-starter-jdbc</artifactId>
|
|
</dependency>
|
|
<dependency>
|
|
<groupId>org.springframework.boot</groupId>
|
|
<artifactId>spring-boot-starter-web</artifactId>
|
|
</dependency>
|
|
|
|
<dependency>
|
|
<groupId>org.springframework.boot</groupId>
|
|
<artifactId>spring-boot-starter-test</artifactId>
|
|
<scope>test</scope>
|
|
</dependency>
|
|
<!-- https://mvnrepository.com/artifact/com.mysql/mysql-connector-j -->
|
|
<dependency>
|
|
<groupId>com.mysql</groupId>
|
|
<artifactId>mysql-connector-j</artifactId>
|
|
<version>9.0.0</version>
|
|
</dependency>
|
|
```
|
|
|
|
in `DataLoader.java`
|
|
```java
|
|
package com.example.demo;
|
|
|
|
import java.sql.PreparedStatement;
|
|
import java.sql.ResultSet;
|
|
import java.sql.SQLException;
|
|
import java.util.ArrayList;
|
|
import java.util.HashMap;
|
|
import java.util.List;
|
|
import java.util.Map;
|
|
import java.util.Set;
|
|
|
|
import org.springframework.beans.factory.annotation.Autowired;
|
|
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
|
|
import org.springframework.jdbc.core.JdbcTemplate;
|
|
import org.springframework.jdbc.core.RowMapper;
|
|
import org.springframework.stereotype.Component;
|
|
|
|
class Name{
|
|
|
|
public int id;
|
|
public String name;
|
|
|
|
Name(int id,String name){
|
|
this.id=id;
|
|
this.name=name;
|
|
}
|
|
}
|
|
|
|
@Component
|
|
public class DataLoader {
|
|
|
|
@Autowired
|
|
JdbcTemplate jdbcTemplate;
|
|
|
|
public void execute() throws Exception{
|
|
// jdbcTemplate.execute ("drop table if exists customers");
|
|
// jdbcTemplate.execute("create table customers(" + "id serial, name varchar(255))");
|
|
|
|
jdbcTemplate.execute("drop table if exists customers; create table customers(id serial, name varchar(255));");
|
|
|
|
jdbcTemplate.execute( String.format("INSERT INTO customers values (%d, '%s')", 1, "hey2"));
|
|
jdbcTemplate.update("INSERT INTO customers values (?, ?)", 3, "hey3");
|
|
|
|
Object[] args = new Object[] {2, "y"};
|
|
jdbcTemplate.update("INSERT INTO customers values (?, ?)",args);
|
|
|
|
List<Name> names= new ArrayList<>();
|
|
names.add(new Name(4,"hey4" ));
|
|
names.add(new Name(5,"hey5" ));
|
|
names.add(new Name(6,"hey6" ));
|
|
names.add(new Name(7,"hey7" ));
|
|
|
|
jdbcTemplate.batchUpdate("INSERT INTO customers values (?, ?)", new BatchPreparedStatementSetter() {
|
|
public void setValues(PreparedStatement ps, int i) throws SQLException{
|
|
ps.setInt(1, names.get(i).id);
|
|
ps.setString(2, names.get(i).name);
|
|
}
|
|
|
|
public int getBatchSize(){
|
|
return names.size();
|
|
}
|
|
});
|
|
|
|
Object[] args2= new Object[]{"2"};
|
|
List<String> lis= jdbcTemplate.query("select name from customers where id=?", new RowMapper<String>() {
|
|
@Override
|
|
public String mapRow(ResultSet rs, int rowNum) throws SQLException{
|
|
return rs.getString("name");
|
|
}
|
|
},args2);
|
|
|
|
List<Map<String,Object>> lis2= jdbcTemplate.query("select * from customers", new RowMapper<Map<String,Object>>(){
|
|
@Override
|
|
public Map<String,Object> mapRow(ResultSet rs, int rowNum) throws SQLException{
|
|
|
|
Map<String,Object> row = new HashMap<>();
|
|
int coln=rs.getMetaData().getColumnCount();
|
|
|
|
for(int i=1;i<= coln;i++){
|
|
String colname = rs.getMetaData().getColumnName(i);
|
|
Object colvalue = rs.getObject(i);
|
|
row.put(colname,colvalue);
|
|
}
|
|
|
|
return row;
|
|
}
|
|
|
|
} );
|
|
|
|
List<Map<String,Object>> lis3 = jdbcTemplate.queryForList("select * from customers");
|
|
|
|
System.out.println("lis3: "+lis3.toString());
|
|
|
|
|
|
|
|
|
|
for(int i=0; i< lis.size();i++){
|
|
System.out.println(i+": "+lis.get(i));
|
|
}
|
|
|
|
System.out.println("lis2: "+lis2.toString());
|
|
|
|
for( Map<String,Object> map : lis2 ){
|
|
Set<String> keys = map.keySet();
|
|
keys.forEach(key -> {
|
|
System.out.println(key+": "+map.get(key));
|
|
|
|
});
|
|
System.out.println();
|
|
}
|
|
|
|
|
|
}
|
|
|
|
}
|
|
|
|
```
|
|
|
|
in `DemoApplication.java`
|
|
```java
|
|
package com.example.demo;
|
|
|
|
import org.springframework.boot.SpringApplication;
|
|
import org.springframework.boot.autoconfigure.SpringBootApplication;
|
|
import org.springframework.context.ConfigurableApplicationContext;
|
|
|
|
@SpringBootApplication
|
|
public class DemoApplication {
|
|
|
|
public static void main(String[] args) throws Exception {
|
|
ConfigurableApplicationContext context =SpringApplication.run(DemoApplication.class, args);
|
|
|
|
DataLoader dl= context.getBean(DataLoader.class);
|
|
dl.execute();
|
|
}
|
|
|
|
}
|
|
|
|
```
|
|
|
|
|