LinkedList Insert / Insert Given Position / Print List

public class AddPositiontoNode {
 Node head;
 static class Node {
  Node next;
  int data;
  Node(int d) {
   this.data = d;
  }
 }
 public static AddPositiontoNode insertNode(AddPositiontoNode list, int d) {
  Node newNode = new Node(d);
  newNode.next = null;
  if (list.head == null) {
   list.head = newNode;
  } else {
   Node currNode = list.head;
   while (currNode.next != null) {
    currNode = currNode.next;
   }
   if (currNode.next == null) {
    currNode.next = newNode;
   }
  }
  return list;
 }
 public static AddPositiontoNode insertGivenPosition(AddPositiontoNode node, int position, int data) {
  Node newNode = new Node(data);
  int index = 1;
  Node currNode = node.head;
  if (position == 1) {
   newNode.next = node.head.next;
   node.head = newNode;
  } else {
   while (index + 1 != position) {
    index++;
    currNode = currNode.next;
   }
   if (index + 1 == position) {
    newNode.next = currNode.next;
    currNode.next = newNode;
   } else {
    newNode.next = currNode.next;
    currNode.next = newNode;
   }
  }
  return node;
 }
 public static void printList(Node head) {
  Node currNode = head;
  while (currNode != null) {
   System.out.println(currNode.data);
   currNode = currNode.next;
  }
 }
 public static void main(String[] args) {
  AddPositiontoNode node = new AddPositiontoNode();
  node = insertNode(node, 1);
  node = insertNode(node, 2);
  node = insertNode(node, 4);
  node = insertNode(node, 5);

  printList(node.head);
  System.out.println("---------------------");
  node = insertGivenPosition(node, 3, 3);
  printList(node.head);
 }
}

Output :

1
2
4
5
---------------------
1
2
3
4
5

JAVA JDBC Examples

Oncelikle JDBC i kullanmak icin https://www.oracle.com/technetwork/apps-tech/jdbc-112010-090769.html linkinden ojdbc6.jar i indiriyoruz.

Ve eclipse de yeni bir java projesi olusturup icinde libs adli bir folder olusturuyoruz. Indirdigimiz jar i oraya yapistiriyoruz. Ama bu sekilde yapistirmamiz o jar i kullanmamiza yetmez o yuzden jar a sag tiklayip Build Path -> Add to Build Path secenegine tikliyoruz. Bu sekilde referenced library e jar imiz eklenmis oldu. Artik kullanabiliriz.

JDBC i kullanmak icin temel olarak yapmamiz gerekenler

Connection olustur => Statement olustur => Execute Et => Resultset ile cek.

Statement

Statement icin temelde 2 tip var.

ResultSet executeQuery(String sql) => gordugumuz gibi result set dondurur. select islemleri icin bunu kullaniriz.

int executeUpdate(String sql) => bunu ise insert, update, delete gibi islemlerde kullaniriz ve sonuc olarak islemlerden etkilenen row countu dondurur.


* Ayrica yazdigimiz tum connection, statement ve resultset in kapatilmasi gerekir. bunu tek tek kapatmak yerine try icinde hepsini tamamlarsak otomatik olarak kullanmadiginda kapatilir.

ResultSet Types

3 tip vardir.

Type_Forward_Only : ornegin bir islemde ilk basta 1. rowdayiz ve bunu kullanarak 5. row a gecebiliriz. Ama 5. rowdan 3 e gecemeyiz.Sadece ileri dogru gidebiliriz.

Type_Scroll_Insensitive : bununla hem forward hem backward ilerleyebiliriz. Ama Insensitive oldg icin result olustuktan sonraki degisikliklere duyarsizdir.

Type_Scroll_Sensitive :  bununla hem forward hem backward ilerleyebiliriz. Sensitive oldg icin result olustuktan sonraki degisikliklere duyarlidir.

* hic birini yazmazsak defaultu Type_Forward_Only dir.

ResultSet Concur Types

2 tip vardir.

Concur_Read_Only 

Concur_Updatable 

* defaultu Concur_Read_Only dir.

Asagida hem update hem insert yaptigimiz bir ornek yer almaktadir.

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class UpdatableRecords {

 public static void main(String[] args) {
  try (Connection conn = DBUtil.getConnection(DBType.ORADB);
    Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
    ResultSet rs = stmt.executeQuery("select department_id, department_name, manager_id, location_id from departments");
    ) {
   rs.absolute(6);
   rs.updateString("department_name", "Information Technology");
   rs.updateRow();
   
   System.out.println("Record updated successfuly");
   
   rs.moveToInsertRow();
   rs.updateInt("department_id", 999);
   rs.updateString("department_name", "Training");
   rs.updateInt("manager_id", 200);
   rs.updateInt("location_id", 2000);
   rs.insertRow();
   System.out.println("Record inserted succesfully");
   
  } catch(SQLException e){
   DBUtil.showErrorMsg(e);
  }

 }

}

Degisiklikleri sqlplus tan goruntuleyebiliriz.

Simdi de PreparedStatement kullanimina bakalim. Statement in bir alt sinifidir ve sorguya parametre girilecegi zaman kullanilir.

Asagida ornek bir yer almaktadir.

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class PreparedStatementEx01 {

 public static void main(String[] args) {
  
  Connection conn = null;
  PreparedStatement pstmt = null;
  ResultSet rs = null;
  try {
   String sql = "select * from Employees where SALARY < ? and Department_Id=?";
   
   conn = DBUtil.getConnection(DBType.ORADB);
   pstmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
   
   preparedStatement(pstmt, 10000, 50);
   System.out.println("---------------------------");
   
   preparedStatement(pstmt, 3000, 50);
   
   
  } catch(SQLException e){
   DBUtil.showErrorMsg(e);
  }
 }

 private static void preparedStatement(PreparedStatement pstmt, double salary, int department_id) throws SQLException {
  ResultSet rs;
  pstmt.setDouble(1, salary);
  pstmt.setInt(2, department_id);
  rs = pstmt.executeQuery();
  String format = "%-4s%-20s%-25s%-10f\n";
  
  while(rs.next()){
   System.out.format(format, rs.getString("Employee_ID"), rs.getString("First_Name"),
     rs.getString("Last_Name"), rs.getFloat("Salary"));
  }
  rs.last();
  System.out.println("Total num of rows: "+ rs.getRow());
 }
}

INSERT RECORD

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;

public class InsertRecord {

 public static void main(String[] args) throws SQLException {
  Connection conn = DBUtil.getConnection(DBType.ORADB);
  PreparedStatement pstmt = null;
  
  String empname;
  int salary, empid;
  Date hiredate;
  
  Scanner scan = new Scanner(System.in);
  
  System.out.print("Enter emp id: ");
  empid = Integer.parseInt(scan.nextLine());
  
  System.out.print("Enter emp name: ");
  empname = scan.nextLine();
  
  System.out.println("Enter salary: ");
  salary = Integer.parseInt(scan.nextLine());
  
  System.out.println("Enter hire date: ");
  hiredate = java.sql.Date.valueOf(scan.nextLine());
  
  String sql = "insert into newemp values(?,?,?,?)";
  
  pstmt = conn.prepareStatement(sql);
  
  pstmt.setInt(1, empid);
  pstmt.setString(2, empname);
  pstmt.setInt(3, salary);
  pstmt.setDate(4, hiredate);
  
  int result = pstmt.executeUpdate();
  
  if(result == 1){
   System.out.println("Record inserted");
  } else{
   System.err.println("Error occured when insert.");
  }
  
  scan.close();
  pstmt.close();
  conn.close();
 }

}

UPDATE RECORD

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;

public class UpdateRecord01 {
 public static void main(String[] args) throws SQLException{
  Connection conn = DBUtil.getConnection(DBType.ORADB);
  String sql = "update newemp set empsalary = ? where empid=20";
  
  Scanner scan = new Scanner(System.in);
  System.out.println("Enter new salary : ");
  int salary = scan.nextInt();
  PreparedStatement pstmt  = conn.prepareStatement(sql);
  pstmt.setInt(1, salary);
    
  int result  = pstmt.executeUpdate();
  if(result == 1){
   System.out.println("Succesfully updated");
  } else{
   System.err.println("Error occured");
  }
  scan.close();
  pstmt.close();
  conn.close();
 }
}

DELETE RECORD

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;

public class DeleteRecord01 {

 public static void main(String[] args) throws SQLException {
  Connection conn = DBUtil.getConnection(DBType.ORADB);
  String sql = "Delete from newemp where empid=?";
  
  Scanner scan = new Scanner(System.in);
  System.out.println("Enter id of emp to delete:");
  int empid = scan.nextInt();
  
  PreparedStatement pstmt = conn.prepareStatement(sql);
  pstmt.setInt(1, empid);
  
  int result = pstmt.executeUpdate();
  if(result == 1){
   System.out.println("Succesfully deleted");
  } else{
   System.err.println("Error occured.");
  }
  scan.close();
  pstmt.close();
  conn.close();
 }

}

CALLABLE STATEMENT

Callable Statement ile Stored Procedure e parametre gonderebiliriz.
Bunun icin oncelikle bir stored procedure olusturalim.
SqlPlus i acalim. Ve daha once elimizdeki newemp tablosuna kayit eklememizi saglayacak bir procedure yazalim.

create or replace Procedure AddEmployee
(
eid in NEWEMP.EMPID%TYPE,
ename in NEWEMP.EMPNAME%TYPE,
sal in NEWEMP.EMPSALARY%TYPE,
hdate in NEWEMP.HIREDATE%TYPE
)
is
begin
    insert into NEWEMP values (eid, ename, sal, hdate);
commit;
end;
/

Procedure yukaridaki gibi olmalidir.

Simdi eclipse e gecip kodumuzu yazalim.

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.SQLException;
import java.util.Scanner;

public class CallableStatementEx01 {

 public static void main(String[] args) {
  try (Connection conn = DBUtil.getConnection(DBType.ORADB);
    CallableStatement cstmt = conn.prepareCall("{call AddEmployee (?,?,?,?)}"); 
    Scanner scan = new Scanner(System.in); ) {
   
   
   System.out.println("Enter empid: ");
   int empid = Integer.parseInt(scan.nextLine());
   System.out.println("Enter empname: ");
   String empname = scan.nextLine();
   System.out.println("Enter empsalary: ");
   int salary = Integer.parseInt(scan.nextLine());
   System.out.println("Enter hiredate: ");
   Date hdate = java.sql.Date.valueOf(scan.nextLine());
   
   cstmt.setInt(1, empid);
   cstmt.setString(2, empname);
   cstmt.setInt(3, salary);
   cstmt.setDate(4, hdate);
   
   cstmt.execute();
   System.out.println("Added succesfully");
   
  } catch(SQLException ex){
   DBUtil.showErrorMsg(ex);
  }
 }

}

INSERTING MULTIPLE RECORDS

Yukarida yazdigimiz kod ile bir kez insert islemi yapabiliyoruz. Eger 10 kez insert yapmak istesek her bir insert isleminde database e baglanip islem yapmamiz gerekecek ve bu da network trafigini arttiracak. Bu yuzden 10 kez insert yapip bir kez database e baglanmamiz iyi bir yontemdir.

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.SQLException;
import java.util.Scanner;

public class InsertMultipleRecords01 {

 public static void main(String[] args) {
  try (Connection conn = DBUtil.getConnection(DBType.ORADB);
    CallableStatement cstmt = conn.prepareCall("{call AddEmployee(?,?,?,?)}");
    Scanner scan = new Scanner(System.in); ) {
   String option;
   do {
   System.out.println("Enter eid: ");
   int eid = Integer.parseInt(scan.nextLine());
   System.out.println("Enter ename: ");
   String ename = scan.nextLine();
   System.out.println("Enter salary: ");
   int sal = Integer.parseInt(scan.nextLine());
   System.out.println("Enter hire date: ");
   Date hdate = java.sql.Date.valueOf(scan.nextLine());
   
   cstmt.setInt(1, eid);
   cstmt.setString(2, ename);
   cstmt.setInt(3, sal);
   cstmt.setDate(4, hdate);
   
   cstmt.addBatch();
   System.out.println("Do you want to add one more record ? yes/no:");
   option = scan.nextLine();
   } while (option.equals("yes"));
   
   int[] updatedRecords = cstmt.executeBatch();
   System.out.println("Total num of updated records: " + updatedRecords.length);
  } catch(SQLException e){
   DBUtil.showErrorMsg(e);
  }

 }

}

Kayitlarin eklenip eklenmedigini kontrol etmek icin SqlPlus ta su query i calistirabiliriz.

select * from newemp where empid in (33, 44, 55);

Bu sekilde eklemis oldugumuz kayitlari gorebiliriz.

STORED PROCEDURE

Stored procedure lar ayni zamanda bir deger de dondurebilir. Bu durumu gorebilecegimiz bir ornek yapalim.
Kullanicidan alacagimiz department id deki calisan sayisni dondurmek isteyelim. Oncelikle bunun icin gerekli stored procedure i yazalim.

create or replace procedure shownum (
dept_id in Employees.Department_Id%Type,
empCount out number
)
as
begin
select count(*) into empCount from Employees
where Department_Id = dept_id;
end;
/

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Scanner;

public class OutSpEx01 {

 public static void main(String[] args) {
  try (Connection conn = DBUtil.getConnection(DBType.ORADB);
    CallableStatement cstmt = conn.prepareCall("{call shownum (?,?)}");
    Scanner scan = new Scanner(System.in);  ) {
   
   System.err.println("Enter department id: ");
   int deptid = scan.nextInt();
   cstmt.setInt(1, deptid);
   cstmt.registerOutParameter(2, Types.INTEGER);
   cstmt.execute();
   
   int totalCount = cstmt.getInt(2);
   System.out.println("Total number of employees work in department "+ deptid + " is "+ totalCount);
   
  } catch(SQLException e){
   DBUtil.showErrorMsg(e);
  }
 }

}

RETURN MULTIPLE ROWS FROM STORED PROCEDURE

Stored procedure dan multiple row dondurmek istedigimizde data type olarak SYS_REFCURSOR kullanmaliyiz.

Verilen department id de calisanlarin bilgilerini dondurecek bir sp yazalim.

Create or replace procedure GETEMPLOYEES
(
p_deptid in employees.department_id%type,
p_persons out SYS_REFCURSOR
)
as
begin
open p_persons for
select employee_id, first_name || last_name as ename,
email, salary from employees
where department_id = p_deptid
order by employee_id;
end;
/

Simdi ise gerekli kodumuzu yazalim.

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
import oracle.jdbc.internal.OracleTypes;

public class ReturnMultipleRowsSp01 {
 public static void main(String[] args){
  
  try (Connection conn = DBUtil.getConnection(DBType.ORADB);
    CallableStatement cstmt = conn.prepareCall("{call getemployees(?,?)}");
    Scanner scan = new Scanner(System.in); ) {
   
   System.out.println("Enter deparment id :");
   int deptid = scan.nextInt();
   
   cstmt.setInt(1, deptid);
   cstmt.registerOutParameter(2, OracleTypes.CURSOR);
   cstmt.execute();
   ResultSet rs = ((oracle.jdbc.internal.OracleCallableStatement)cstmt).getCursor(2);
   String format = "%-4s%-20s%-25s%-10f\n";
   while(rs.next()){
    System.out.format(format, rs.getInt("employee_id"), rs.getString("ename"), rs.getString("email"), rs.getDouble("salary"));
   }   
  } catch(SQLException e){
   DBUtil.showErrorMsg(e);
  }
 }
}

ASP.NET VERITABANI BAGLANTISI - ASP.NET DATABASE CONNECTION - INSERT DATA TO DATABASE

Visual Studio -> New -> Web Site -> ASP.NET Empty Web Site seciyoruz. Projemiz acildiktan sonra File -> New -> File diyerek acilan ekrandan C# altindan Web Form u seciyoruz. Ve Add e tikliyoruz.
Herseyden once database connection in yapildigindan emin olmaliyiz.Bunun icin ekranda yer alan "Server Explorer " penceresine gidiyoruz.Eger boyle bir ekran yoksa View -> Server Explorer i secerek ekranda gorunmesini saglayabiliriz.Daha sonra o ekranda bulunan "Data Connections " a sag tiklayip "Add Connection " diyoruz ve acilan ekranda "Server Name " i kendi bilgisayarimizi kullanacaksak kendi PC mizi secili hale getiriyoruz.Daha sonra alt kisimdan kullanacagimiz database ismini seciyoruz.Ve "OK" u secip islemi tamamliyoruz.

Ben olusturdugum database de student tablosuna veri ekleyecegim.(Mssql i kullaniyorum . Kullanacagim database ismi deneme , kullanacagim tablo ise Student tablosu). Student tablosunda 3 tane sutun bulunmakta.Bunlar name , surname ve age. O yuzden bu bilgileri kullanicidan alarak student tablosuna eklemem lazim.Bilgileri almak amaciyla bir web form olusturuyorum.Burada 3 tane textbox olacak bu bilgileri almak icin .

                                           

Formumu bu sekilde hazirladim. Simdi ise Submit e cift tiklayarak click event i yazmak icin kod kismina geciyoruz. Kod kismi ise su sekilde :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;

public partial class anasayfa : System.Web.UI.Page
{

    SqlConnection conn;

    SqlCommand xp;

  
    protected void Page_Load(object sender, EventArgs e)
    {
        conn = new SqlConnection("data source=.;initial catalog=deneme;integrated security=true");
 
/* data source un . olmasi kendi bilgisayarimizda calistigimiz anlamina gelir.  */

        xp= new SqlCommand("INSERT INTO Student (Name,Surname,Age) VALUES (@Name,@Surname,@Age)", conn);


   
    }

    public int AddStudent(string name, string surname, int age)

    {

        try

        {


            conn.Open();

            xp.Parameters.AddWithValue("@Name", SqlDbType.NVarChar).Value = name;

            xp.Parameters.AddWithValue("@Surname", SqlDbType.NVarChar).Value = surname;

            xp.Parameters.AddWithValue("@Age", SqlDbType.Int).Value = age;

          
            return xp.ExecuteNonQuery(); //ExecuteNonQuery int tipinde bir deger dondurur.Bu yuzden AddStudent methodumuz int tipinde tanimlanmistir.



        }

        finally { conn.Close(); }

    }


    protected void Button1_Click(object sender, EventArgs e)
    {


        
        int result = AddStudent(TextBox1.Text, TextBox2.Text, int.Parse(TextBox3.Text.ToString()));

        if (result == 0) {

            Label4.Text = "Data could not inserted to database !";
         }
        else {

            Label4.Text = "Data inserted to database !";
         }
    }


}

Kodumuzu yazip calistirdigimizda karsimiza soyle bir ekran gelmektedir.

Bu sekilde Submit e bastigimizda karsimiza eklenip eklenmedigine dair bir bildirim gelicektir.

MSSQL i de kontrol ederek datamizin eklendigini gorebiliriz...