/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package sqlite;
import java.sql.*;
/**
 *
 * @author pokrasa
 */
public class SQLite {

    /**
     * @param args the command line arguments
     */
    void wypisz(){
        Connection c = null;
        Statement stmt = null;

        try
        {
            Class.forName("org.sqlite.JDBC");
        c = DriverManager.getConnection("jdbc:sqlite:pracownicy.db");
        c.setAutoCommit(true);
        System.out.println("Opened database successfully");

        stmt = c.createStatement();
        ResultSet rs = stmt.executeQuery( "SELECT * FROM pensje WHERE rocznik>'1990-01-01';" );
        while ( rs.next() ) {
        String  nazwisko = rs.getString("nazwisko");
                String rocznik = rs.getString("rocznik");
        int pensja  = rs.getInt("pensja");
                int id = rs.getInt("ID");
        System.out.println(id +" "+ nazwisko + "  " +pensja);
        }
        rs.close();
        stmt.close();
        c.close();
        }
        catch( Exception e)
        {
            System.err.println( e.getClass().getName() + ": " + e.getMessage() );
        System.exit(0);
        }
    }

    public static void main(String[] args) {
        Connection c = null;
        Statement stmt = null;

        try
        {
            Class.forName("org.sqlite.JDBC");
        c = DriverManager.getConnection("jdbc:sqlite:pracownicy.db");
        c.setAutoCommit(true);
        System.out.println("Opened database successfully");

        stmt = c.createStatement();
            String sql = "CREATE TABLE pensje " +
                    "(ID INT PRIMARY KEY NOT NULL, " +
                    "nazwisko VARCHAR(255) NOT NULL, " +
                    "rocznik DATE NOT NULL, " +
                    "pensja DECIMAL(8,2) NOT NULL)";

            stmt.executeUpdate(sql);

            sql = "INSERT INTO pensje (ID, nazwisko, rocznik, pensja) " +
                    "VALUES (1,'Roman','1996-02-23', 2500.50)";
            stmt.executeUpdate(sql);
            sql = "INSERT INTO pensje (ID, nazwisko, rocznik, pensja) " +
                    "VALUES (2,'Andrzej','1982-01-15', 3500.50)";
            stmt.executeUpdate(sql);
            sql = "UPDATE pensje SET pensja=pensja+(1,10*pensja) WHERE rocznik>'1990-01-01'";
            stmt.executeUpdate(sql);
        rs.wypisz();
        stmt.close();
        c.close();
        }
        catch( Exception e)
        {
            System.err.println( e.getClass().getName() + ": " + e.getMessage() );
        System.exit(0);
        }
    }

}