הלינקייה: מגזין חודשי למפתחים

רוצה לשמוע על כל האירועים, המדריכים, הקורסים והמאמרים שנכתבו החודש ?
הלינקייה הינו מגזין חופשי בעברית שמשאיר אותך בעניינים.
בלי ספאם. בלי שטויות. פעם בחודש אצלך בתיבה.

DB Lesson Examples

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
 
 
public class PeopleDBManager {
	public Connection getConnection() throws ClassNotFoundException, SQLException  {
		Class.forName("org.sqlite.JDBC");
	    Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db");
 
	    return conn;
	}
 
	public void setPeople(Connection conn, List<Person> people) throws SQLException {
		String sql = "DELETE FROM people";
		PreparedStatement s = conn.prepareStatement(sql);
		s.execute();
		s.close();
 
		for (Person p : people) {
			storePerson(conn, p);
		}
	}
 
	public List<Person> getAllPeople(Connection conn) throws SQLException {
		String sql = "SELECT id, name, address, age FROM people";
 
		PreparedStatement s = conn.prepareStatement(sql);
		List<Person> results = new LinkedList<Person>();
 
		ResultSet rs = s.executeQuery();
		while ( rs.next() ) {
			results.add(new Person(rs.getInt("id"), rs.getString("name"), rs.getString("address"), rs.getInt("age")));
		}
 
		s.execute();
		s.close();
 
		return results;
	}
 
	public void storePerson(Connection conn, Person p) throws SQLException {
		if ( isInDb(conn, p) ) {
			updateRecord(conn, p);
		} else {
			addPerson(conn, p);
		}
	}
 
	public boolean isInDb(Connection conn, Person p) throws SQLException {
		String sql = "SELECT id FROM people WHERE id = ?";
		boolean result = false;
 
		PreparedStatement s = conn.prepareStatement(sql);
 
		s.setInt(1, p.getId());
 
		ResultSet rs = s.executeQuery();
 
		while (rs.next() ) {
			result = true;
			break;
		}
 
		rs.close();
		s.close();
 
		return result;
	}
 
	public void updateRecord(Connection conn, Person p) throws SQLException {
		String sql = "UPDATE people SET name=?, address=? WHERE id=?";
 
		PreparedStatement s = conn.prepareStatement(sql);
 
		s.setString(1, p.getName());
		s.setString(2, p.getAddress());
		s.setInt(3, p.getId());
 
		s.execute();
		s.close();
	}
 
	public void addPerson(Connection conn, Person p) throws SQLException {
		String sql = "INSERT INTO people VALUES (?, ?, ?, ?)";
 
		PreparedStatement s = conn.prepareStatement(sql);
 
		s.setInt(1, p.getId());
		s.setString(2, p.getName());
		s.setString(3, p.getAddress());
		s.setInt(4, p.getAge());
		s.execute();
		s.close();		
	}
 
	public void removePerson(Connection conn, Person p) throws SQLException {
		String sql = "DELETE FROM people WHERE id = ?";
 
		PreparedStatement s = conn.prepareStatement(sql);
 
		s.setInt(1, p.getId());
		s.execute();
		s.close();
	}
}
public final class Person {
	private String name;
	private String address;
	private int age;
	private int id;
 
	public String getName() {
		return this.name;
	}
 
	public String getAddress() {
		return this.address;
	}
 
	public int getId() {
		return this.id;
	}
 
	public int getAge() {
		return this.age;
	}
 
	public Person(int id, String name, String address, int age) {
		this.name = name;
		this.address = address;
		this.id = id;
		this.age = age;
	}
 
	public String toString() {
		return "[" + getId() + "] " + getName() + " Lives at: " + getAddress() + " aged: " + this.age;
	}
 
 
}
 
import java.sql.Connection;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
 
 
public class Population {
	public void reducePopulation(List<Person> population) {
		if ( population.size() == 0 ) {
			return;
		}
 
 
		Person oldest = population.get(0);
 
		for (Person p : population ) {
			if ( p.getAge() > oldest.getAge() ) {
				oldest = p;
			}
		}
 
		population.remove(oldest);
	}
 
	public List<Person> initPopulation() {
		List<Person> results = new LinkedList<Person>();
 
		for ( int i=0; i < 10; ++i ) {
			int age = (int) (Math.random() * 80);
			String name = "p" + i;
			String address = "City_" + i;
			Person p = new Person(i, name, address, age);
			results.add(p);
		}
 
		return results;
	}
 
	public void printList(List<Person> all) {
		for ( Person p : all ) {
			System.out.println(p);
		}
	}
 
	public static void main(String [] args) throws ClassNotFoundException, SQLException {
		PeopleDBManager mgr = new PeopleDBManager();
		Connection conn = mgr.getConnection();
		Population population = new Population();
 
		List<Person> data = mgr.getAllPeople(conn);
 
		if ( data.size() == 0 ) {
			System.out.println("Initializing list");
			// no one left - initialize new list
			data = population.initPopulation();
			mgr.setPeople(conn, data);
		} else {
			// some one is there - reduce them
			population.reducePopulation(data);
			mgr.setPeople(conn, data);
			population.printList(data);
		}
 
	}
}
 
course: