| 1 |
package dk.daoas.adressevedligehold.db;
|
| 2 |
|
| 3 |
import java.sql.Connection;
|
| 4 |
import java.sql.PreparedStatement;
|
| 5 |
import java.sql.ResultSet;
|
| 6 |
import java.sql.SQLException;
|
| 7 |
import java.sql.Statement;
|
| 8 |
import java.util.ArrayList;
|
| 9 |
import java.util.List;
|
| 10 |
|
| 11 |
import dk.daoas.adressevedligehold.beans.Address;
|
| 12 |
import dk.daoas.adressevedligehold.util.DeduplicateHelper;
|
| 13 |
import dk.daoas.adressevedligehold.util.TimingHelper;
|
| 14 |
|
| 15 |
|
| 16 |
|
| 17 |
public class DatabaseLayerImplementation {
|
| 18 |
|
| 19 |
static boolean DEBUG = false;
|
| 20 |
|
| 21 |
|
| 22 |
public List<Address> getAllAdresses() throws SQLException {
|
| 23 |
TimingHelper timing = new TimingHelper();
|
| 24 |
|
| 25 |
String debugFilter = DatabaseLayerImplementation.DEBUG ? " WHERE postnr = 8700 " : "";
|
| 26 |
|
| 27 |
String sql =
|
| 28 |
"SELECT id,vejnavn,husnr,husnrbogstav,kommunekode,vejkode,postnr,gadeid,upper(distributor) AS distributor,dbkbane,latitude,longitude, "
|
| 29 |
+ "rutema,korelistema,ruteti,korelisteti,ruteon,korelisteon,ruteto,korelisteto,rutefr,korelistefr,rutelo,korelistelo,ruteso,korelisteso "
|
| 30 |
+ "FROM fulddaekning.adressetabel "
|
| 31 |
+ debugFilter
|
| 32 |
;
|
| 33 |
|
| 34 |
try ( Connection conn = DBConnection.getConnection();
|
| 35 |
Statement stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
|
| 36 |
) {
|
| 37 |
stmt.setFetchSize(Integer.MIN_VALUE);
|
| 38 |
ResultSet res = stmt.executeQuery(sql);
|
| 39 |
|
| 40 |
List<Address> list = new ArrayList<Address>(2600000);//initial capacity 2.6 mio
|
| 41 |
|
| 42 |
DeduplicateHelper<String> vejnavnCache = new DeduplicateHelper<String>(81920);
|
| 43 |
DeduplicateHelper<String> husnrbogstavCache = new DeduplicateHelper<String>();
|
| 44 |
DeduplicateHelper<String> distributorCache = new DeduplicateHelper<String>();
|
| 45 |
DeduplicateHelper<String> dirigeringsCache = new DeduplicateHelper<String>(16*1024);
|
| 46 |
|
| 47 |
|
| 48 |
while (res.next()) {
|
| 49 |
|
| 50 |
Address a = new Address();
|
| 51 |
a.id = res.getInt(1);
|
| 52 |
a.vejnavn = vejnavnCache.getInstance( res.getString(2) );
|
| 53 |
a.husnr = (short) res.getInt(3);
|
| 54 |
a.husnrbogstav = husnrbogstavCache.getInstance( res.getString(4) );
|
| 55 |
a.kommunekode = (short) res.getInt(5);
|
| 56 |
a.vejkode = (short)res.getInt(6);
|
| 57 |
a.postnr = (short)res.getInt(7);
|
| 58 |
a.gadeid = res.getInt(8);
|
| 59 |
a.distributor = distributorCache.getInstance(res.getString(9));
|
| 60 |
a.dbkBane = (short) res.getInt(10);
|
| 61 |
a.latitude = (float) res.getDouble(11);
|
| 62 |
a.longitude = (float) res.getDouble(12);
|
| 63 |
|
| 64 |
a.ruteMandag = dirigeringsCache.getInstance( res.getString(13) );
|
| 65 |
a.korelisteMandag = dirigeringsCache.getInstance( res.getString(14) );
|
| 66 |
|
| 67 |
a.ruteTirsdag = dirigeringsCache.getInstance( res.getString(15) );
|
| 68 |
a.korelisteTirsdag = dirigeringsCache.getInstance( res.getString(16) );
|
| 69 |
|
| 70 |
a.ruteOnsdag = dirigeringsCache.getInstance( res.getString(17) );
|
| 71 |
a.korelisteOnsdag = dirigeringsCache.getInstance( res.getString(18) );
|
| 72 |
|
| 73 |
a.ruteTorsdag = dirigeringsCache.getInstance( res.getString(19) );
|
| 74 |
a.korelisteTorsdag = dirigeringsCache.getInstance( res.getString(20) );
|
| 75 |
|
| 76 |
a.ruteFredag = dirigeringsCache.getInstance( res.getString(21) );
|
| 77 |
a.korelisteFredag = dirigeringsCache.getInstance( res.getString(22) );
|
| 78 |
|
| 79 |
a.ruteLordag = dirigeringsCache.getInstance( res.getString(23) );
|
| 80 |
a.korelisteLordag = dirigeringsCache.getInstance( res.getString(24) );
|
| 81 |
|
| 82 |
a.ruteSondag = dirigeringsCache.getInstance( res.getString(25) );
|
| 83 |
a.korelisteSondag = dirigeringsCache.getInstance( res.getString(26) );
|
| 84 |
|
| 85 |
|
| 86 |
list.add(a);
|
| 87 |
}
|
| 88 |
res.close();
|
| 89 |
|
| 90 |
|
| 91 |
System.out.println("DB Loaded " + list.size() + " adresses in " + timing.getElapsed() + "ms");
|
| 92 |
|
| 93 |
return list;
|
| 94 |
}
|
| 95 |
}
|
| 96 |
|
| 97 |
public void updateAddresses(List<Address> addresses) {
|
| 98 |
|
| 99 |
String sql = "UPDATE fulddaekning.adressetabel " +
|
| 100 |
"SET " +
|
| 101 |
"RuteMa=?, KorelisteMa=?, " +
|
| 102 |
"RuteTi=?, KorelisteTi=?, " +
|
| 103 |
"RuteOn=?, KorelisteOn=?, " +
|
| 104 |
"RuteTo=?, KorelisteTo=?, " +
|
| 105 |
"RuteFr=?, KorelisteFr=?, " +
|
| 106 |
"RuteLo=?, KorelisteLo=?, " +
|
| 107 |
"RuteSo=?, KorelisteSo=? " +
|
| 108 |
"WHERE id=?";
|
| 109 |
|
| 110 |
|
| 111 |
|
| 112 |
try (Connection con = DBConnection.getConnection()) {
|
| 113 |
con.setAutoCommit(false);
|
| 114 |
|
| 115 |
try (PreparedStatement stmt = con.prepareStatement(sql)) {
|
| 116 |
for (Address addr : addresses) {
|
| 117 |
stmt.setString(1, addr.ruteMandag);
|
| 118 |
stmt.setString(2, addr.korelisteMandag);
|
| 119 |
stmt.setString(3, addr.ruteTirsdag);
|
| 120 |
stmt.setString(4, addr.korelisteTirsdag);
|
| 121 |
stmt.setString(5, addr.ruteOnsdag);
|
| 122 |
stmt.setString(6, addr.korelisteOnsdag);
|
| 123 |
stmt.setString(7, addr.ruteTorsdag);
|
| 124 |
stmt.setString(8, addr.korelisteTorsdag);
|
| 125 |
stmt.setString(9, addr.ruteFredag);
|
| 126 |
stmt.setString(10, addr.korelisteFredag);
|
| 127 |
stmt.setString(11, addr.ruteLordag);
|
| 128 |
stmt.setString(12, addr.korelisteLordag);
|
| 129 |
stmt.setString(13, addr.ruteSondag);
|
| 130 |
stmt.setString(14, addr.korelisteSondag);
|
| 131 |
|
| 132 |
stmt.setInt(15, addr.id);
|
| 133 |
|
| 134 |
|
| 135 |
stmt.execute();
|
| 136 |
if (stmt.getUpdateCount() != 1) {
|
| 137 |
System.out.println("Error executing update");
|
| 138 |
}
|
| 139 |
|
| 140 |
}
|
| 141 |
}
|
| 142 |
|
| 143 |
con.commit();
|
| 144 |
} catch (SQLException e) {
|
| 145 |
System.out.println("Error updating addresses" + e.getMessage() );
|
| 146 |
}
|
| 147 |
}
|
| 148 |
|
| 149 |
private static String nullify(String str) {
|
| 150 |
if (str == null)
|
| 151 |
return null;
|
| 152 |
|
| 153 |
if (str.equals("")) {
|
| 154 |
return null;
|
| 155 |
} else {
|
| 156 |
return str;
|
| 157 |
}
|
| 158 |
}
|
| 159 |
|
| 160 |
|
| 161 |
/*
|
| 162 |
private static int safeInt(String str) {
|
| 163 |
try {
|
| 164 |
return Integer.parseInt( str );
|
| 165 |
} catch (NumberFormatException e) {
|
| 166 |
return 0;
|
| 167 |
}
|
| 168 |
}
|
| 169 |
|
| 170 |
|
| 171 |
|
| 172 |
private static String coalesce(String s1, String s2) {
|
| 173 |
if (s1 != null)
|
| 174 |
return s1;
|
| 175 |
|
| 176 |
return s2;
|
| 177 |
}
|
| 178 |
|
| 179 |
*/
|
| 180 |
}
|
Parent Directory
| 
Revision Log