JDBC

/*
 * 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 lab_jdbc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author student
 */
public class Lab_JDBC {

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        Connection conn = null;
        Properties connectionProps = new Properties();
        connectionProps.put("user", "inf127216");
        connectionProps.put("password", "******");
        try {
            conn = DriverManager.getConnection("jdbc:oracle:thin:@//admlab2.cs.put.poznan.pl:1521/dblab02_students.cs.put.poznan.pl", connectionProps);
            System.out.println("Połączono z bazą danych");
            Statement stmt = null;
            ResultSet rs = null;
            try {
                stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
                /*rs = stmt.executeQuery("select id_prac, rpad(nazwisko, 15), placa_pod "
                        + "from pracownicy");
                rs.afterLast();
                while (rs.previous()) {
                    System.out.println(rs.getInt(1) + " " + rs.getString(2) + " "
                            + rs.getFloat(3));
                }
                int changes;
                changes = stmt.executeUpdate("INSERT INTO pracownicy(id_prac,nazwisko) "
                        + "VALUES(5,'Marecki')");
                System.out.println("Wstawiono " + changes + " krotek.");
                changes = stmt.executeUpdate("UPDATE pracownicy SET "
                        + "placa_pod=placa_pod*1.5");
                System.out.println("Zmodyfikowano " + changes + " krotek.");
                changes = stmt.executeUpdate("DELETE FROM pracownicy WHERE id_prac=5");
                System.out.println("Usunieto " + changes + " krotek.");
                conn.setAutoCommit(false);
                stmt = conn.createStatement();
                stmt.executeUpdate("INSERT INTO pracownicy(id_prac,nazwisko)"
                        + "VALUES(200,'Nowacki')");
                //conn.rollback();
                PreparedStatement pstmt = conn.prepareStatement("SELECT nazwisko FROM pracownicy WHERE id_prac=?");
                pstmt.setInt(1, 120);
                rs = pstmt.executeQuery();
                //rs = stmt.executeQuery("select id_prac, rpad(nazwisko, 15), placa_pod " + "from pracownicy");
                //rs.afterLast();
                while (rs.next()) {
                    System.out.println(rs.getInt(1) + " " + rs.getString(2) + " "
                            + rs.getFloat(3));
                }
                rs.close();
                pstmt.setInt(1, 150);
                rs = pstmt.executeQuery();
                while (rs.next()) {
                    System.out.println(rs.getInt(1) + " " + rs.getString(2) + " "
                            + rs.getFloat(3));
                }
                rs.close();
                pstmt.close();

                PreparedStatement pstmt;
                pstmt = conn.prepareStatement("UPDATE pracownicy SET "
                        + "placa_pod=placa_pod * ? WHERE id_prac = ? ");

                pstmt.setFloat(1, new Float(1));
                pstmt.setInt(2, 130);
                pstmt.addBatch();
                pstmt.setFloat(1, new Float(0));
                pstmt.setInt(2, 0);
                pstmt.addBatch();

                pstmt.executeBatch(); */

                CallableStatement stmt1 = conn.prepareCall("{call WstawZespol(?,?,?)}");
                stmt1.setInt(1, 60);
                stmt1.setString(2, "NOWY ZESPÓŁ");
                stmt1.setString(3, "PIOTROWO 3A");
                stmt1.execute();
                stmt1.close();

                rs = stmt.executeQuery("select id_zesp, nazwa, adres " + "from zespoly");
                //rs.afterLast();
                while (rs.next()) {
                    System.out.println(rs.getInt(1) + " " + rs.getString(2) + " "
                            + rs.getString(3));
                }

            } catch (SQLException ex) {
                System.out.println("Bład wykonania polecenia" + ex.toString());
            } finally {
                if (rs != null) {
                    try {
                        rs.close();
                    } catch (SQLException e) {
                        /* kod obsługi */ }
                }
                if (stmt != null) {
                    try {
                        stmt.close();
                    } catch (SQLException e) {
                        /* kod obsługi */ }
                }
            }
            conn.close();
            System.out.println("Rozłączono z bazą danych");
        } catch (SQLException ex) {
            Logger.getLogger(Lab_JDBC.class.getName()).log(Level.SEVERE,
                    "nie udało się połączyć z bazą danych", ex);
            System.exit(-1);
        }

    }
}