[[oktatas:programozas:java:java adatbázis:mariadb|< Mariadb]] ====== MariaDB minta ====== * **Szerző:** Sallai András * Copyright (c) 2020, Sallai András * Szerkesztve: 2020, 2021 * Licenc: [[https://creativecommons.org/licenses/by-sa/4.0/|CC BY-SA 4.0]] * Web: https://szit.hu ===== Adatbázis =====



create database feherbt;

use feherbt;

create table employee (
  id int not null primary key auto_increment,
  name varchar(30),
  city varchar(30),
  salary double
);

grant all privileges 
on feherbt.* 
to 'feherbt'@'localhost'
identified by 'titok';

===== Employee.java =====


public class Employee {
    int id;
    String name;
    String city;
    double salary;
}

===== Mariadb.java =====


import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.SQLException;

public class Mariadb {    

    private static Connection tryConnectDb() throws SQLException {
        String url = "jdbc:mariadb://localhost:3306/feherbt";
        Connection conn = DriverManager.getConnection(url,
                "feherbt", "titok");
        return conn;
    }
    
    public static Connection connectDb() {        
        Connection conn = null;
        try  {
            conn = tryConnectDb();
        } catch (SQLException e) {
            System.err.println("Hiba! A kapcsolódás sikertelen!");
            System.err.println(e.getMessage());
        }
        return conn;
    }    
}

===== Database.java =====


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

public class Database {
    public List tryGetEmployees() throws SQLException {
        List employees = new ArrayList<>();        
        Connection conn = Mariadb.connectDb();
        String sql = "select id, name, city, salary from employee";
        Statement stmt = conn.createStatement();        
        ResultSet rs = stmt.executeQuery(sql);
        while(rs.next()) {
            Employee employee = new Employee();
            employee.id = rs.getInt("id");
            employee.name = rs.getString("name");
            employee.city = rs.getString("city");
            employee.salary = rs.getDouble("salary");
            employees.add(employee);
        }
        conn.close();        
        return employees;
    }
    
    public List getEmployees() {
        List employees = null;
        try {
            employees = tryGetEmployees();
        } catch (SQLException e) {
            System.err.println("Hiba! A lekérdezés sikertelen!");
            System.err.println(e.getMessage());
        }
        return employees;
    }
    
    public void tryInsertEmployee(
            String name, String city, double salary) 
            throws SQLException {        
        Connection conn = Mariadb.connectDb();
        String sql = "insert into employee " +
                "(name, city, salary) values " +
                "(?, ?, ?)";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, name);
        pstmt.setString(2, city);
        pstmt.setDouble(3, salary);
        pstmt.execute();
        conn.close();        
    }
    
    public void insertEmployee(
            String name, String city, double salary) {
        try {
            tryInsertEmployee(name, city, salary);
        } catch (SQLException ex) {
            System.err.println("Hiba! A beszúrás sikertelen!");
            System.err.println(ex.getMessage());
        }
    }
}

===== Main.java =====


import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;

public class Main {    
    public static void main(String[] args) {
        List employees = new ArrayList<>();
        Database database = new Database();
        
        database.insertEmployee("Cseviczki Zsolt", "Szolnok", 3450000);
        employees = database.getEmployees();
        for(Employee employee: employees) {
            System.out.println(employee.name);
        }
    }
}