package dk.daoas.adressevedligehold.db;
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;
import dk.daoas.adressevedligehold.beans.Address;
import dk.daoas.adressevedligehold.beans.Address.AddressState;
import dk.daoas.adressevedligehold.util.DeduplicateHelper;
import dk.daoas.adressevedligehold.util.TimingHelper;
/*
* TODO: Batched skrivning af updates
* TODO: Skrivning af nye adresser
*
*/
public class DatabaseLayerImplementation {
//static boolean DEBUG = false;
public List
getAllAdresses() throws SQLException {
TimingHelper timing = new TimingHelper();
//String debugFilter = DatabaseLayerImplementation.DEBUG ? " WHERE postnr >= 6000 " : "";
String sql =
"SELECT id,vejnavn,husnr,husnrbogstav,kommunekode,vejkode,postnr,gadeid,upper(distributor) AS distributor,dbkbane,latitude,longitude, "
+ "rutema,korelistema,ruteti,korelisteti,ruteon,korelisteon,ruteto,korelisteto,rutefr,korelistefr,rutelo,korelistelo,ruteso,korelisteso "
+ "FROM fulddaekning.adressetabel "
//+ debugFilter
;
try ( Connection conn = DBConnection.getConnection();
Statement stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
) {
stmt.setFetchSize(Integer.MIN_VALUE);
ResultSet res = stmt.executeQuery(sql);
List list = new ArrayList(2600000);//initial capacity 2.6 mio
DeduplicateHelper vejnavnCache = new DeduplicateHelper(81920);
DeduplicateHelper husnrbogstavCache = new DeduplicateHelper();
DeduplicateHelper distributorCache = new DeduplicateHelper();
DeduplicateHelper dirigeringsCache = new DeduplicateHelper(16*1024);
while (res.next()) {
Address a = new Address();
a.id = res.getInt(1);
a.vejnavn = vejnavnCache.getInstance( res.getString(2) );
a.husnr = (short) res.getInt(3);
a.husnrbogstav = husnrbogstavCache.getInstance( res.getString(4) );
a.kommunekode = (short) res.getInt(5);
a.vejkode = (short)res.getInt(6);
a.postnr = (short)res.getInt(7);
a.gadeid = res.getInt(8);
a.distributor = distributorCache.getInstance(res.getString(9));
a.dbkBane = (short) res.getInt(10);
a.latitude = (float) res.getDouble(11);
a.longitude = (float) res.getDouble(12);
a.ruteMandag = dirigeringsCache.getInstance( res.getString(13) );
a.korelisteMandag = dirigeringsCache.getInstance( res.getString(14) );
a.ruteTirsdag = dirigeringsCache.getInstance( res.getString(15) );
a.korelisteTirsdag = dirigeringsCache.getInstance( res.getString(16) );
a.ruteOnsdag = dirigeringsCache.getInstance( res.getString(17) );
a.korelisteOnsdag = dirigeringsCache.getInstance( res.getString(18) );
a.ruteTorsdag = dirigeringsCache.getInstance( res.getString(19) );
a.korelisteTorsdag = dirigeringsCache.getInstance( res.getString(20) );
a.ruteFredag = dirigeringsCache.getInstance( res.getString(21) );
a.korelisteFredag = dirigeringsCache.getInstance( res.getString(22) );
a.ruteLordag = dirigeringsCache.getInstance( res.getString(23) );
a.korelisteLordag = dirigeringsCache.getInstance( res.getString(24) );
a.ruteSondag = dirigeringsCache.getInstance( res.getString(25) );
a.korelisteSondag = dirigeringsCache.getInstance( res.getString(26) );
list.add(a);
}
res.close();
System.out.println("DB Loaded " + list.size() + " adresses in " + timing.getElapsed() + "ms");
return list;
}
}
public void saveNewAddresses(List addresses) {
System.out.println("DB::saveNewAddresses size:" + addresses.size() );
String sql = "INSERT IGNORE INTO fulddaekning.adressetabel " +
"(vejnavn,husnr,husnrbogstav,kommunekode,vejkode,postnr,gadeid,dbkbane,indlast,aendret,david,googlechecked," +
"RuteMa,KorelisteMa,RuteTi,KorelisteTi,RuteOn,KorelisteOn,RuteTo,KorelisteTo,RuteFr,KorelisteFr,RuteLo,KorelisteLo,RuteSo,KorelisteSo) " +
"VALUES " +
"(?,?,?,?,?,?,?,?, now(), now(), ?, 0, " +
"?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
try (Connection con = DBConnection.getConnection()) {
//con.setAutoCommit(false);
try (PreparedStatement stmt = con.prepareStatement(sql)) {
for (Address addr : addresses) {
String david = String.format("%04d%03d%04d", addr.postnr, addr.kommunekode, addr.vejkode);
stmt.setString(1, addr.vejnavn);
stmt.setShort(2, addr.husnr);
stmt.setString(3, addr.husnrbogstav);
stmt.setShort(4, addr.kommunekode);
stmt.setShort(5, addr.vejkode);
stmt.setShort(6, addr.postnr);
stmt.setInt(7, addr.gadeid);
stmt.setShort(8, addr.dbkBane);
stmt.setString(9, david);
stmt.setString(10, addr.ruteMandag);
stmt.setString(11, addr.korelisteMandag);
stmt.setString(12, addr.ruteTirsdag);
stmt.setString(13, addr.korelisteTirsdag);
stmt.setString(14, addr.ruteOnsdag);
stmt.setString(15, addr.korelisteOnsdag);
stmt.setString(16, addr.ruteTorsdag);
stmt.setString(17, addr.korelisteTorsdag);
stmt.setString(18, addr.ruteFredag);
stmt.setString(19, addr.korelisteFredag);
stmt.setString(20, addr.ruteLordag);
stmt.setString(21, addr.korelisteLordag);
stmt.setString(22, addr.ruteSondag);
stmt.setString(23, addr.korelisteSondag);
stmt.execute();
if (stmt.getUpdateCount() != 1) {
System.out.println("Error executing insert: " + addr);
addr.state = AddressState.CREATE_FAIL;
}
}
}
//con.commit();
} catch (SQLException e) {
System.out.println("Error inserting addresses" + e.getMessage() );
}
}
public void updateAddresses(List addresses) {
System.out.println("DB::UpdateAddresses size:" + addresses.size() );
String sql = "UPDATE fulddaekning.adressetabel " +
"SET " +
"RuteMa=?, KorelisteMa=?, " +
"RuteTi=?, KorelisteTi=?, " +
"RuteOn=?, KorelisteOn=?, " +
"RuteTo=?, KorelisteTo=?, " +
"RuteFr=?, KorelisteFr=?, " +
"RuteLo=?, KorelisteLo=?, " +
"RuteSo=?, KorelisteSo=?, " +
"Distributor=?, " +
"DBKBane=?, " +
"Aendret=now() " +
"WHERE id=? ";
try (Connection con = DBConnection.getConnection()) {
//con.setAutoCommit(false);
try (PreparedStatement stmt = con.prepareStatement(sql)) {
for (Address addr : addresses) {
stmt.setString(1, addr.ruteMandag);
stmt.setString(2, addr.korelisteMandag);
stmt.setString(3, addr.ruteTirsdag);
stmt.setString(4, addr.korelisteTirsdag);
stmt.setString(5, addr.ruteOnsdag);
stmt.setString(6, addr.korelisteOnsdag);
stmt.setString(7, addr.ruteTorsdag);
stmt.setString(8, addr.korelisteTorsdag);
stmt.setString(9, addr.ruteFredag);
stmt.setString(10, addr.korelisteFredag);
stmt.setString(11, addr.ruteLordag);
stmt.setString(12, addr.korelisteLordag);
stmt.setString(13, addr.ruteSondag);
stmt.setString(14, addr.korelisteSondag);
stmt.setString(15, addr.distributor);
stmt.setInt(16, addr.dbkBane);
stmt.setInt(17, addr.id);
stmt.execute();
if (stmt.getUpdateCount() != 1) {
System.out.println("Error executing update");
}
}
}
//con.commit();
} catch (SQLException e) {
System.out.println("Error updating addresses" + e.getMessage() );
}
}
private static String nullify(String str) {
if (str == null)
return null;
if (str.equals("")) {
return null;
} else {
return str;
}
}
/*
private static int safeInt(String str) {
try {
return Integer.parseInt( str );
} catch (NumberFormatException e) {
return 0;
}
}
private static String coalesce(String s1, String s2) {
if (s1 != null)
return s1;
return s2;
}
*/
}