Connecting to mysql

Hi, guys.
Can you advice me nice tutorial about how to connect my project to mysql. I looked some but it didn’t help. Please, give me nice tutorial.

Thanks in advance!

There is no one definitive answer to your question, since the details of the answer depends on what kind of tech stack you plan to use. For example if you choose to create Vaadin application with Spring Boot and use that as helper for your back end, it will offer rather simple way to create repositories with Spring Data, there is simple example here

https://github.com/mstahv/spring-boot-spatial-example

There is also full stack test application at Vaadin.com/start see https://vaadin.com/start/framework/full-stack-spring , which is also based on Spring Boot and Spring Data. There is example production settings which use MySQL

If you want to use Java EE type stack, there exists similar test app also https://vaadin.com/start/framework/full-stack-javaee

Tatu Lund:
There is no one definitive answer to your question, since the details of the answer depends on what kind of tech stack you plan to use. For example if you choose to create Vaadin application with Spring Boot and use that as helper for your back end, it will offer rather simple way to create repositories with Spring Data, there is simple example here

https://github.com/mstahv/spring-boot-spatial-example

There is also full stack test application at Vaadin.com/start see https://vaadin.com/start/framework/full-stack-spring , which is also based on Spring Boot and Spring Data. There is example production settings which use MySQL

If you want to use Java EE type stack, there exists similar test app also https://vaadin.com/start/framework/full-stack-javaee

I want to connect to mysql as we do it in JavaFX. I saw something like it but don’t know how to do it.
Please help if know how to solve it.
Thanks in advance!

How are you connecting to MySQL in JavaFX? I think the exact same way should work.

Olli Tietäväinen:
How are you connecting to MySQL in JavaFX? I think the exact same way should work.

I did it but in FX i added jdbc to a lib folder (where we put jdk) but i can’t add jdbc in vaadin. That’s my problem…
Can you guys help me with it, please?

What are you using for dependency management? Maven, possibly? You should use your dependency manager and let it get the JDBC driver for you. Maybe this will help: https://crunchify.com/java-mysql-jdbc-hello-world-tutorial-create-connection-insert-data-and-retrieve-data-from-mysql/

Olli Tietäväinen:
What are you using for dependency management? Maven, possibly? You should use your dependency manager and let it get the JDBC driver for you. Maybe this will help: https://crunchify.com/java-mysql-jdbc-hello-world-tutorial-create-connection-insert-data-and-retrieve-data-from-mysql/

I just created vaadin project.
Not maven or other.

How did you create your project? With the Eclipse plugin? Which Vaadin version are you using?

Olli Tietäväinen:
How did you create your project? With the Eclipse plugin? Which Vaadin version are you using?

i use netbeans 8.2. and created vaadin web application project

Okay, that’s probably your problem - you might be using some ancient Netbeans plugin that’s not supported any more. Try using https://vaadin.com/start to create a project instead - just choose the version you want from the menu. Follow the instructions; that should give you a nice Maven project.

Olli Tietäväinen:
Okay, that’s probably your problem - you might be using some ancient Netbeans plugin that’s not supported any more. Try using https://vaadin.com/start to create a project instead - just choose the version you want from the menu. Follow the instructions; that should give you a nice Maven project.

So i can’t connect to mysql without maven project?

You can, but I’d recommend using a Maven project anyway.

Hi, all again. I have just connected to mysql with only vaadin project and want to share with you.

(I’m using Netbeans IDE, MySQL Workbench and Tomcat server).

So, 1st create your DB (i called it “demo”).

2nd you need to download mysql connector java (JAR file) (this is the link (Select Platform Independent and download zip file): https://dev.mysql.com/downloads/connector/j/).

3rd copy this jar file to lib folder of apache tomcat server.

4th in Netbeans SERVERs panel right click to databases->new connect. Choose MySQL(Connector/J driver) for driver. And to driver files add that mysql connector jar file, then click → next. Write your DB name in Database (in my case it was “demo”) and at the end write your password of this db. Click next->finish.!

Code:

  1. DatabaseHandler.java

     package com.mycompany.vaadin1;
    
     import java.sql.*;
    
     import java.util.logging.Level;
    
     import java.util.logging.Logger;
    
     public class DatabaseHandler {
    
     private static final String HOST = "jdbc:mysql://localhost:3306/demo";
     private static final String USERNAME = "root";
     private static final String PASSWORD = "password";
    
     private Connection connection;
    
     public DatabaseHandler(){
     	try {
     		connection = DriverManager.getConnection(HOST, USERNAME, PASSWORD);
     	} catch (SQLException e) {
     		e.printStackTrace();
     	}
     }
    
     	public Connection getConnection() {
     		return connection;
     	}
    
     	public void setConnection(Connection connection) {
     		this.connection = connection;
     	}
     }
    
  2. MyUI.java

/* I have database with name “demo”. Table with name “customers”, also “id”, “first_name” columns; */

package com.mycompany.vaadin1;

import javax.servlet.annotation.WebServlet;


import com.vaadin.annotations.Theme;

import com.vaadin.annotations.VaadinServletConfiguration;

import com.vaadin.server.VaadinRequest;

import com.vaadin.server.VaadinServlet;

import com.vaadin.ui.Button;

import com.vaadin.ui.TextField;

import com.vaadin.ui.UI;

import com.vaadin.ui.VerticalLayout;

import com.vaadin.server.UserError;

import com.vaadin.ui.HorizontalSplitPanel;

import com.vaadin.ui.TabSheet;

import com.vaadin.ui.Window;

import com.vaadin.ui.themes.ValoTheme;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.logging.Level;

import java.util.logging.Logger;



@Theme("mytheme")


public class MyUI extends UI {

@Override

protected void init(VaadinRequest vaadinRequest) {
           
		   DatabaseHandler worker = new DatabaseHandler();
           
		   String query = "select first_name from demo.customers where id=1";
           
		   TextField txt1  = new TextField();
          
		  try {
               
			   Statement statement = worker.getConnection().createStatement();
               
			   ResultSet resultSet = statement.executeQuery(query);
                
                while(resultSet.next()){
                    txt1.setValue(resultSet.getString("first_name")); 
                    //System.out.println(resultSet.getString("first_name"));
                }
                
            } catch (SQLException e) {
                e.printStackTrace();
            }
			VerticalLayout vP = new VerticalLayout(txt1);
			setContent(vP);
	}
	@WebServlet(urlPatterns = "/*", name = "MyUIServlet", asyncSupported = true)
	@VaadinServletConfiguration(ui = MyUI.class, productionMode = false)
	public static class MyUIServlet extends VaadinServlet {
	}
}

Good job!

Olli Tietäväinen:
Good job!

Hope everything is clear :slight_smile:

Olli Tietäväinen:
You can, but I’d recommend using a Maven project anyway.

i have a similar problem, i also don’t know how to connect my vaadin app to mysql

Application class

package br.com.fjsistemas;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.web.servlet.support.SpringBootServletInitializer;

@SpringBootApplication
public class Application extends SpringBootServletInitializer {

    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }

}

product class

package br.com.fjsistemas.backend;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@AllArgsConstructor
@NoArgsConstructor
@Data
@Entity
public class Produto {
	

	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private Long id;
	private String nome;
	private String marca;
	private String modelo;
	private Double valor;

}

interface ProductRepository

package br.com.fjsistemas.repository;

import org.springframework.data.jpa.repository.JpaRepository;

import br.com.fjsistemas.backend.Produto;


public interface ProdutoRepository extends JpaRepository<Produto, Integer> {

	
}

class ProductService

package br.com.fjsistemas.service;

import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import br.com.fjsistemas.backend.Produto;
import br.com.fjsistemas.repository.ProdutoRepository;

@Service
public class ProdutoService {
	
	private static final Logger LOGGER = Logger.getLogger(ProdutoService.class
			.getName());


	@Autowired
	private ProdutoRepository produtoRepository;

	public List<Produto> buscar() {
		List<Produto> produtos = produtoRepository.findAll();
		return produtos;

	}
	
	public Long contar() {
		return produtoRepository.count();
	}
	
	public void salvar(Produto produto) {
		if (produto == null) {
			LOGGER.log(Level.SEVERE, "O Produto é nulo");
			return;
		}
		produtoRepository.save(produto);
	}
}

class Product Registration

package br.com.fjsistemas.view;

import java.util.List;
import java.util.Locale;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import com.vaadin.flow.component.Text;
import com.vaadin.flow.component.button.Button;
import com.vaadin.flow.component.button.ButtonVariant;
import com.vaadin.flow.component.dialog.Dialog;
import com.vaadin.flow.component.grid.Grid;
import com.vaadin.flow.component.grid.GridVariant;
import com.vaadin.flow.component.html.Div;
import com.vaadin.flow.component.icon.Icon;
import com.vaadin.flow.component.icon.VaadinIcon;
import com.vaadin.flow.component.notification.Notification;
import com.vaadin.flow.component.notification.Notification.Position;
import com.vaadin.flow.component.orderedlayout.HorizontalLayout;
import com.vaadin.flow.component.textfield.TextField;
import com.vaadin.flow.data.binder.Binder;
import com.vaadin.flow.data.converter.StringToDoubleConverter;
import com.vaadin.flow.data.renderer.NumberRenderer;
import com.vaadin.flow.router.PageTitle;
import com.vaadin.flow.router.Route;
import com.vaadin.flow.router.RouteAlias;
import com.vaadin.flow.spring.annotation.UIScope;

import br.com.fjsistemas.backend.Produto;
import br.com.fjsistemas.main.MainView;
import br.com.fjsistemas.service.ProdutoService;

@Route(value = "cadastroproduto", layout = MainView.class)
@PageTitle("Cadastro de Produtos")
@RouteAlias(value = "Cadastro Produtos", layout = MainView.class)
@Component
@UIScope
public class CadastroProduto extends Div {

	private static final long serialVersionUID = 1L;

	private List<Produto> lista;
	private Grid<Produto> grid;

	private final Button remover;
	private final Button adicionar;
	private final Button edit;

	private Produto produtoAtual;

	private final Binder<Produto> janelaBinder = new Binder<>(Produto.class);
	private final Binder<Produto> gridBinder = new Binder<>(Produto.class);

	private final TextField gridTxtNome = new TextField();
	private final TextField gridTxtMarca = new TextField();
	private final TextField gridTxtModelo = new TextField();
	private final TextField gridTxtValor = new TextField();

	private ProdutoService produtoService;

	@Autowired
	public CadastroProduto(ProdutoService produtoService) {
		this.produtoService = produtoService;
		addGrid();

		Dialog janela = getEditDialog();

		adicionar = new Button(new Icon(VaadinIcon.PLUS_CIRCLE_O));
		adicionar.addThemeVariants(ButtonVariant.LUMO_PRIMARY);
		adicionar.addClickListener(event -> janela.open());

		remover = new Button(new Icon(VaadinIcon.MINUS_CIRCLE_O));
		remover.addThemeVariants(ButtonVariant.LUMO_PRIMARY);
		remover.addClickListener(event -> {
			if (produtoAtual != null) {
				lista.remove(produtoAtual);
				grid.setItems(lista);
				produtoAtual = null;
				enableEditingButtons(false);
			}
		});

		edit = new Button(new Icon(VaadinIcon.EDIT));
		edit.addThemeVariants(ButtonVariant.LUMO_PRIMARY);
		edit.addClickListener(e -> {
			grid.getEditor().editItem(produtoAtual);
			gridTxtNome.focus();
			gridTxtMarca.focus();
			gridTxtModelo.focus();
			gridTxtValor.focus();

		});

		enableEditingButtons(false);
		HorizontalLayout layoutInterno2 = new HorizontalLayout();
		layoutInterno2.add(adicionar, remover, edit);
		add(layoutInterno2);
	}

	private Dialog getEditDialog() {
		Dialog janela = new Dialog();

		janela.setCloseOnEsc(false);
		janela.setCloseOnOutsideClick(false);
		janela.setWidth("700px");
		janela.setHeight("360px");

		HorizontalLayout layoutJanelaInterno1 = new HorizontalLayout();

		Text titulo = new Text("Novo Produto");

		HorizontalLayout layoutJanelaInterno2 = new HorizontalLayout();

		TextField nome = new TextField("Produto:");
		nome.setMinWidth("50%");

		TextField marca = new TextField("Marca:");
		marca.setMinWidth("50%");

		HorizontalLayout layoutJanelaInterno3 = new HorizontalLayout();

		TextField modelo = new TextField("Modelo:");
		modelo.setWidth("50%");

		TextField valor = new TextField("Valor Unitário:");
		valor.setWidth("50%");

		layoutJanelaInterno1.add(titulo);
		layoutJanelaInterno1.setMargin(isVisible());
		layoutJanelaInterno2.add(nome, marca);
		layoutJanelaInterno2.setMargin(isVisible());
		layoutJanelaInterno3.add(modelo, valor);
		layoutJanelaInterno3.setMargin(isVisible());

		janelaBinder.forField(nome).withConverter(String::toUpperCase, String::valueOf, " ").bind(Produto::getNome,
				Produto::setNome);

		janelaBinder.forField(marca).withConverter(String::toUpperCase, String::valueOf, " ").bind(Produto::getMarca,
				Produto::setMarca);

		janelaBinder.forField(modelo).withConverter(String::toUpperCase, String::valueOf, " ").bind(Produto::getModelo,
				Produto::setModelo);

		janelaBinder.forField(valor).withConverter(new StringToDoubleConverter(0.0, "Valor Inválido"))
				.withNullRepresentation(0.0).bind(Produto::getValor, Produto::setValor);

		HorizontalLayout layoutBotoes = new HorizontalLayout();

		Button salvar = new Button("Salvar");
		salvar.addThemeVariants(ButtonVariant.LUMO_PRIMARY);
		salvar.getStyle().set("margin-top", "25px");
		salvar.addClickListener(event -> {
			Produto produtoNovo = new Produto();

			lista.add(produtoNovo);

			grid.setItems(lista);

			janelaBinder.writeBeanIfValid(produtoNovo);
			janelaBinder.readBean(new Produto(null, " ", " ", " ", null));
			janelaBinder.getFields().forEach(f -> f.clear());

		});

		Button fechar = new Button("Fechar");
		fechar.addThemeVariants(ButtonVariant.LUMO_PRIMARY);
		fechar.getStyle().set("margin-top", "25px");
		fechar.addClickListener(event -> {
			janela.close();
		});

		janela.add(layoutJanelaInterno1);
		janela.add(layoutJanelaInterno2);
		janela.add(layoutJanelaInterno3);
		janela.add(layoutBotoes);

		layoutBotoes.add(salvar, fechar);
		return janela;
	}

	private void enableEditingButtons(boolean enable) {
		remover.setEnabled(enable);
		edit.setEnabled(enable);
	}

	private void addGrid() {

		lista = produtoService.buscar();
		grid = new Grid<>();

		grid.setItems(lista);
		grid.setHeight("835px");

		grid.addColumn(Produto::getId).setHeader("ID:").setAutoWidth(true);

		grid.addColumn(Produto::getNome).setHeader("Produto:").setAutoWidth(true).setKey("nome");

		grid.addColumn(Produto::getMarca).setHeader("Marca:").setAutoWidth(true).setKey("marca");

		grid.addColumn(Produto::getModelo).setHeader("Modelo:").setAutoWidth(true).setKey("modelo");

		grid.addColumn(new NumberRenderer<>(Produto::getValor, "R$ %(,.2f", Locale.getDefault(), "R$ 0.00"))
				.setHeader("Valor Unitário:").setAutoWidth(true).setKey("valor");

		grid.addThemeVariants(GridVariant.LUMO_COMPACT, GridVariant.LUMO_COLUMN_BORDERS);

		grid.asSingleSelect().addValueChangeListener(e -> {
			produtoAtual = e.getValue();
			enableEditingButtons(e.getValue() != null);
		});

		grid.getEditor().setBinder(gridBinder);

		gridTxtNome.getElement().addEventListener("keydown", event -> grid.getEditor().cancel())
				.setFilter("event.key === 'Enter'");

		gridTxtMarca.getElement().addEventListener("keydown", event -> grid.getEditor().cancel())
				.setFilter("event.key === 'Enter'");

		gridTxtModelo.getElement().addEventListener("keydown", event -> grid.getEditor().cancel())
				.setFilter("event.key === 'Enter'");

		gridTxtValor.getElement().addEventListener("keydown", event -> grid.getEditor().cancel())
				.setFilter("event.key === 'Enter'");

		gridBinder.forField(gridTxtNome).withConverter(String::toUpperCase, String::valueOf, " ").bind("nome");
		grid.getColumnByKey("nome").setEditorComponent(gridTxtNome);

		gridBinder.forField(gridTxtMarca).withConverter(String::toUpperCase, String::valueOf, " ").bind("marca");
		grid.getColumnByKey("marca").setEditorComponent(gridTxtMarca);

		gridBinder.forField(gridTxtModelo).withConverter(String::toUpperCase, String::valueOf, " ").bind("modelo");
		grid.getColumnByKey("modelo").setEditorComponent(gridTxtModelo);

		gridBinder.forField(gridTxtValor).withConverter(new StringToDoubleConverter("Número Inválido")).bind("valor");
		grid.getColumnByKey("valor").setEditorComponent(gridTxtValor);

		grid.getEditor().addCloseListener(event -> {
			if (gridBinder.getBean() != null) {
				Notification.show("Texto Alterado!", 1000, Position.TOP_END);

			}
		});
		add(grid);
	}
}

the way the codes are at the moment, I can open MySQL to type products in MySQL and so they are loaded into the system.

But what I would like to do is the other way around, I would like to fill out the product within the system and when clicking save, it is saved in the database.

This last behavior, I couldn’t do