At first, add these dependencies in pom.xml.
<!-- JPA Data -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<!-- Use MySQL Connector-J -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
Then add these settings in application.properties.
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
#Change "test" at the end to your database name.
spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=root
#Change "yourpassword" to your password of mysql's root user.
spring.datasource.password=yourpassword
#Supposing you are using MySQL5
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect
If you don't have a database yet, start your mysql and run this command to create a database.
Create database test;
use test;
Create a table too.
Drop table if exists cat;
Create table cat(
id int not null auto_increment,
name varchar(100) not null,
rescued date not null,
vaccinated tinyint(1) not null,
primary key (id)
);
Then insert records.
insert into cat(name, rescued, vaccinated)values('nyan','2018-10-01','1');
insert into cat(name, rescued, vaccinated)values('joe','2018-1-5','1');
insert into cat(name, rescued, vaccinated)values('leo','2017-3-21','0');
After running these sqls, save these as schema.sql (table) and data.sql (sqls to insert) in resources folder.
Create a model folder and a Cat.java:
Create a model folder and a Cat.java:
Write as follows inside Cat.java
package com.blogspot.noteoneverything.chatboard.model;
import java.util.Date;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
@Entity
public class Cat{
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private long id;
private String name;
private Date rescued;
private Boolean vaccinated;
public long getId(){
return id;
}
public void setId(long id){
this.id = id;
}
public String getName(){
return name;
}
public void setName(String name){
this.name = name;
}
public Date getRescued(){
return rescued;
}
public void setRescued(Date rescued){
this.rescued = rescued;
}
public Boolean getVaccinated(){
return vaccinated;
}
public void setVaccinated(Boolean vaccinated){
this.vaccinated = vaccinated;
}
}
We will create a repository file. Create "dao" folder and CatRepository.java:
Then write as follows inside CatRepository.java:
package com.blogspot.noteoneverything.chatboard.dao;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;
import com.blogspot.noteoneverything.chatboard.model.Cat;
@Repository
public interface CatRepository extends CrudRepository<Cat,Long>{
Cat findByName(String name);
}
(We are extending CrudRepository now but JpaRepository is more popular.)
We will create service files. Create a "service" folder, then CatService.java and CatServiceImpl.java.
We will create service files. Create a "service" folder, then CatService.java and CatServiceImpl.java.
Write as follows in CatService.java:
package com.blogspot.noteoneverything.chatboard.service;
import java.util.Date;
import java.util.List;
public interface CatService {
void addCat(String name, Date rescued, Boolean vaccinated);
void deleteCat(String name, Long id);
List atriskcats(Date rescued);
long getGeneratedKey(String name, Date rescued, Boolean vaccinated);
}
Write as follows in CatServiceImpl.java:
package com.blogspot.noteoneverything.chatboard.service;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.Format;
import java.text.SimpleDateFormat;
import java.text.DateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Service;
@Service
public class CatServiceImpl implements CatService {
@Autowired
private DataSource dataSource;
private JdbcTemplate jdbcTemplate;
private long generatedKey;
private String rescuedstring;
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
public void addCat(String name, Date rescued, Boolean vaccinated){
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
jdbcTemplate.update("INSERT INTO cat(name,rescued,vaccinated)VALUES(?,?,?)",name,rescued,vaccinated );
}
public void deleteCat(String name, Long id){
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
jdbcTemplate.update("DELETE FROM cat WHERE name='"+name+"' AND id="+id);
}
public List atriskcats(Date rescued){
DateFormat df = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
String stringRescued = df.format(rescued);
String sql = "SELECT * FROM cat WHERE rescued < '"+ stringRescued +"' AND vaccinated = '0'";
List catList = new ArrayList();
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
jdbcTemplate.query(sql, new ResultSetExtractor() {
public List extractData(ResultSet rs) throws SQLException {
while (rs.next()) {
String name = rs.getString("name");
catList.add(name);
}
return catList;
}
}
);
System.out.println("catlist");
return catList;
}
public long getGeneratedKey(String name, Date rescued, Boolean vaccinated) {
String sql ="INSERT INTO cat(name,rescued,vaccinated) VALUES(?,?,?)";
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
KeyHolder holder = new GeneratedKeyHolder();
java.sql.Date rescuedsql = new java.sql.Date(rescued.getTime());
System.out.println(rescuedsql);
jdbcTemplate.update(new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
PreparedStatement statement = connection.prepareStatement(sql.toString(),
Statement.RETURN_GENERATED_KEYS);
statement.setString(1, name);
statement.setDate(2, rescuedsql );
statement.setBoolean(3, vaccinated);
return statement;
}
}, holder);
generatedKey = holder.getKey().longValue();
System.out.println("generated key is " + generatedKey);
return generatedKey;
}
}
We will create a controller now. Create "controller" folder and CatController.java inside.
Write as follows inside CatController.java.
package com.blogspot.noteoneverything.chatboard.controller;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.text.SimpleDateFormat;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.format.annotation.DateTimeFormat;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.InitBinder;
import org.springframework.web.bind.WebDataBinder;
import org.springframework.beans.propertyeditors.CustomDateEditor;
import com.blogspot.noteoneverything.chatboard.dao.CatRepository;
import com.blogspot.noteoneverything.chatboard.model.Cat;
import com.blogspot.noteoneverything.chatboard.service.CatService;
@Controller
public class CatController {
@Autowired
private CatRepository catrepository;
@Autowired
private CatService catservice;
private ArrayList catModelList;
private List<String> catrisklist = null;
@GetMapping(value = "/")
public String cathome(
@RequestParam(value = "search", required = false)
@DateTimeFormat(pattern = "yyyy-MM-dd") Date q,
Model model) {
if (q != null) {
catModelList = new ArrayList();
System.out.println("q is = " + q);
catrisklist = catservice.atriskcats(q);
for (String name : catrisklist) {
System.out.println("Cats in repository are : " + catrepository.findAll());
Cat catgy = catrepository.findByName(name);
System.out.println(catgy.toString() + "catgy name : " + catgy.getName());
catModelList.add(catgy);
System.out.println("This cat's name is : " + catgy.getName());
}
}
model.addAttribute("search", catModelList);
model.addAttribute("cats", catrepository.findAll());
return "index";
}
@PostMapping(value = "/")
public String addcat(@RequestParam("name") String name,
@RequestParam("rescued") @DateTimeFormat(pattern = "yyyy-MM-dd") Date rescued,
@RequestParam("vaccinated") Boolean vaccinated, Model model) {
catservice.addACat(name, rescued, vaccinated);
System.out.println("name = " + name + ",rescued = " + rescued + ", vaccinated = " + vaccinated);
return "redirect:/";
}
@PostMapping(value = "/delete")
public String deleteCat(@RequestParam("name") String name,
@RequestParam("id") Long id) {
catservice.deleteADOG(name, id);
System.out.println("Cat named = " + name + "was removed from our database. Hopefully he or she was adopted.");
return "redirect:/";
}
@PostMapping(value = "/genkey")
public String genkey(@RequestParam("name") String name,
@RequestParam("rescued") @DateTimeFormat(pattern = "yyyy-MM-dd") Date rescued,
@RequestParam("vaccinated") Boolean vaccinated, Model model) {
catservice.getGeneratedKey(name, rescued, vaccinated);
System.out.println("name = " + name + ",rescued = " + rescued + ", vaccinated = " + vaccinated);
return "redirect:/";
}
}
The catrepository.findAll() used in this class can be used like also:
//all cats here
Iterable cats = catrepository.findAll();
Iterable cats = catrepository.findAll();
At last, add index.html in resources folder.
Write as follows inside:
<!DOCTYPE html>
<html lang="en">
<head>
<!-- META SECTION -->
<title>Cat JdbcTemplate, Thymeleaf, JPA Example</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<meta name="viewport" content="width=device-width, initial-scale=1" />
<!-- END META SECTION -->
<!-- BEGIN STYLE -->
<style>
table, th, td {
border: 1px solid black;
padding: 1px;
}
</style>
<!-- END STYLE -->
</head>
<body>
<div>
<h2>Current Cats In Rescue</h2>
<table>
<thead>
<tr>
<th>ID</th>
<th>Name</th>
<th>Rescue Date</th>
<th>Vaccinated</th>
</tr>
</thead>
<tbody>
<tr th:each="cats : ${cats}">
<td th:text="${cats.id}">Text ...</td>
<td th:text="${cats.name}">Text ...</td>
<td th:text="${cats.rescued}">Text ...</td>
<td th:text="${cats.vaccinated}">Text...</td>
</tr>
</tbody>
</table>
</div>
<!-- begin form for finding at risk cats -->
<h2>Find Cats That Need Vaccines</h2>
<form action="#" th:action="@{/}" th:object="${search}">
<label for="search_input">Search:</label> <input name="search"
id="search" >
</input>
<div th:if="${not #lists.isEmpty(search)}">
<h3>Search Results Of At Risk Cats</h3>
<table>
<thead>
<tr>
<th>ID</th>
<th>Name</th>
<th>Vaccinated</th>
<th>Rescued</th>
</tr>
</thead>
<tbody>
<tr th:each="search : ${search}">
<td th:text="${search.id}">Text ...</td>
<td th:text="${search.name}">Text ...</td>
<td th:text="${search.vaccinated}">Text ...</td>
<td th:text="${search.rescued}">Text...</td>
</tr>
</tbody>
</table>
</div>
</form>
<div>
<h2>Add A Cat</h2>
<form action="#" th:action="@{/}" method="post">
<label>Name<input type="text" name="name" id="name"></input></label>
<label>Vaccinated<input type="text" name="vaccinated" id="vaccinated"></input></label>
<label>Rescued<input type="text" name="rescued" id="rescued"></input></label>
<input type="submit" value="Submit"></input>
</form>
</div>
<div>
<h2>Delete A Cat</h2>
<form action="/delete" th:action="@{/delete}" method="post">
<label>Name<input type="text" name="name" id="name"></input></label>
<label>ID<input type="text" name="id" id="id"></input></label>
<input type="submit" value="Submit"></input>
</form>
</div>
<div>
<h2>Return Generated Key When Adding A Cat</h2>
<form action="/genkey" th:action="@{/genkey}" method="post">
<label>Name<input type="text" name="name" id="name"></input></label>
<label>Vaccinated<input type="text" name="vaccinated" id="vaccinated"></input></label>
<label>Rescued<input type="text" name="rescued" id="rescued"></input></label>
<input type="submit" value="Submit"></input>
</form>
</div>
</body>
</html>
Result:
