daoas/naermestepshop/Database.java

package dk.daoas.naermestepshop;

import geocode.GeoPoint;

import java.io.IOException;
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.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.logging.Logger;


public class Database {
        private Logger logger = Logger.getLogger(Database.class.getName());
        
        private Connection conn;
        private PreparedStatement saveStmt;
        private int batchCount = 0;
        
        public Database(Properties props) throws SQLException,IOException {
                conn = getConnection(props);
                
                String sql = "INSERT INTO fulddaekning.shopafstande (id,postnr, daoshop,daoafstand,swipshop,swipafstand,glsshop,glsafstand) "+
                                "VALUES (?,?,?,?,?,?,?,? )";

                saveStmt = conn.prepareStatement(sql);          

        }
        
        public List<Adresse> getAdresser() throws Exception {
                String sql = "SELECT id,postnr,latitude,longitude " +
                                         "FROM fulddaekning.adressetabel " +
                                         "WHERE latitude IS NOT NULL " +
                                         "AND longitude IS NOT NULL "
                                         //+ "LIMIT 250000"
                                         ;
                List<Adresse> result = new ArrayList<Adresse>();
                

                try ( Statement stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY) ){
                        stmt.setFetchSize(Integer.MIN_VALUE);
                        
                        try  (ResultSet res = stmt.executeQuery(sql))  {
                                while (res.next()) {
                                        double latitude = res.getDouble("latitude");
                                        double longitude = res.getDouble("longitude");
                                        
                                        Adresse addr = new Adresse(latitude,longitude);
                                        addr.id = res.getInt("id");
                                        addr.postnr = res.getShort("postnr");
                                        
                                        result.add(addr);
                                }
                        }
                }
                logger.info("getAdresser() : " + result.size());
                
                return result;
        }
        
        public List<GeoPoint> getShops(String kaede) throws Exception {
                

                String sql = "SELECT kaede,ktonr,navn,adresse,postnr,latitude,longitude " + 
                                "FROM fulddaekning.shoppositioner " +
                                "WHERE kaede = ? ";
                
                List<GeoPoint> result = new ArrayList<GeoPoint>();
                
                try (PreparedStatement stmt = conn.prepareStatement(sql) ) {
                        
                
                        stmt.setString(1, kaede);
                        
                        
                        try (ResultSet res = stmt.executeQuery()) {
                                //logger.info("Starting exec query done");
                
                                while (res.next()) {
                                        double latitude = res.getDouble("latitude");
                                        double longitude = res.getDouble("longitude");
                                        
                                        Shop shop = new Shop(latitude,longitude);
                                        shop.kaede = kaede;
                                        shop.ktonr = res.getInt("ktonr");
                                        shop.adresse = res.getString("adresse");
                                        shop.postnr = res.getShort("postnr");
                                        
                                        result.add(shop);
                                }
                        }
                }
                logger.info("getShops(" + kaede + ") : " + result.size());

                
                return result;
        }
        

        public void resetResultTable() throws SQLException {
                logger.info("Truncating result table");
                String sql = "TRUNCATE TABLE fulddaekning.shopafstande";
                conn.createStatement().executeUpdate(sql);              
        }       
        
        public synchronized void gemResultat(Adresse orgAdresse, ShopResult dao, ShopResult swip, ShopResult gls) throws SQLException {

                saveStmt.setInt(1, orgAdresse.id);
                saveStmt.setShort(2, orgAdresse.postnr);
                saveStmt.setInt(3, dao.shop.ktonr);
                saveStmt.setDouble(4, dao.distance);
                saveStmt.setInt(5, swip.shop.ktonr);
                saveStmt.setDouble(6, swip.distance);
                saveStmt.setInt(7, gls.shop.ktonr);
                saveStmt.setDouble(8, gls.distance);

                saveStmt.addBatch();
                batchCount++;
                if (batchCount >= 5000) {
                        saveStmt.executeBatch();
                        batchCount = 0;
                }
        }
        
        public void saveBatch() throws SQLException{
                saveStmt.executeBatch();
                batchCount = 0;
        }
        

        public Connection getConnection(Properties props) throws SQLException, IOException {
                
                String db_host = props.getProperty("DB_HOST");
                String db_user = props.getProperty("DB_USER");
                String db_pass = props.getProperty("DB_PASS");


            Connection conn = null;
            Properties connectionProps = new Properties();
            connectionProps.put("user", db_user);
            connectionProps.put("password", db_pass);

            //For debug output, tilføj denne til JDBC url'en: &profileSQL=true     
            conn = DriverManager.getConnection(
                           "jdbc:mysql://" +
                           db_host +
                           ":3306/?rewriteBatchedStatements=true",
                           connectionProps);
            logger.info("Connected to database");
            return conn;
        }       
}
1	package dk.daoas.naermestepshop;
2
3	import geocode.GeoPoint;
4
5	import java.io.IOException;
6	import java.sql.Connection;
7	import java.sql.DriverManager;
8	import java.sql.PreparedStatement;
9	import java.sql.ResultSet;
10	import java.sql.SQLException;
11	import java.sql.Statement;
12	import java.util.ArrayList;
13	import java.util.List;
14	import java.util.Properties;
15	import java.util.logging.Logger;
16
17
18	public class Database {
19	private Logger logger = Logger.getLogger(Database.class.getName());
20
21	private Connection conn;
22	private PreparedStatement saveStmt;
23	private int batchCount = 0;
24
25	public Database(Properties props) throws SQLException,IOException {
26	conn = getConnection(props);
27
28	String sql = "INSERT INTO fulddaekning.shopafstande (id,postnr, daoshop,daoafstand,swipshop,swipafstand,glsshop,glsafstand) "+
29	"VALUES (?,?,?,?,?,?,?,? )";
30
31	saveStmt = conn.prepareStatement(sql);
32
33	}
34
35	public List<Adresse> getAdresser() throws Exception {
36	String sql = "SELECT id,postnr,latitude,longitude " +
37	"FROM fulddaekning.adressetabel " +
38	"WHERE latitude IS NOT NULL " +
39	"AND longitude IS NOT NULL "
40	//+ "LIMIT 250000"
41	;
42	List<Adresse> result = new ArrayList<Adresse>();
43
44
45	try ( Statement stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY) ){
46	stmt.setFetchSize(Integer.MIN_VALUE);
47
48	try (ResultSet res = stmt.executeQuery(sql)) {
49	while (res.next()) {
50	double latitude = res.getDouble("latitude");
51	double longitude = res.getDouble("longitude");
52
53	Adresse addr = new Adresse(latitude,longitude);
54	addr.id = res.getInt("id");
55	addr.postnr = res.getShort("postnr");
56
57	result.add(addr);
58	}
59	}
60	}
61	logger.info("getAdresser() : " + result.size());
62
63	return result;
64	}
65
66	public List<GeoPoint> getShops(String kaede) throws Exception {
67
68
69	String sql = "SELECT kaede,ktonr,navn,adresse,postnr,latitude,longitude " +
70	"FROM fulddaekning.shoppositioner " +
71	"WHERE kaede = ? ";
72
73	List<GeoPoint> result = new ArrayList<GeoPoint>();
74
75	try (PreparedStatement stmt = conn.prepareStatement(sql) ) {
76
77
78	stmt.setString(1, kaede);
79
80
81	try (ResultSet res = stmt.executeQuery()) {
82	//logger.info("Starting exec query done");
83
84	while (res.next()) {
85	double latitude = res.getDouble("latitude");
86	double longitude = res.getDouble("longitude");
87
88	Shop shop = new Shop(latitude,longitude);
89	shop.kaede = kaede;
90	shop.ktonr = res.getInt("ktonr");
91	shop.adresse = res.getString("adresse");
92	shop.postnr = res.getShort("postnr");
93
94	result.add(shop);
95	}
96	}
97	}
98	logger.info("getShops(" + kaede + ") : " + result.size());
99
100
101	return result;
102	}
103
104
105	public void resetResultTable() throws SQLException {
106	logger.info("Truncating result table");
107	String sql = "TRUNCATE TABLE fulddaekning.shopafstande";
108	conn.createStatement().executeUpdate(sql);
109	}
110
111	public synchronized void gemResultat(Adresse orgAdresse, ShopResult dao, ShopResult swip, ShopResult gls) throws SQLException {
112
113	saveStmt.setInt(1, orgAdresse.id);
114	saveStmt.setShort(2, orgAdresse.postnr);
115	saveStmt.setInt(3, dao.shop.ktonr);
116	saveStmt.setDouble(4, dao.distance);
117	saveStmt.setInt(5, swip.shop.ktonr);
118	saveStmt.setDouble(6, swip.distance);
119	saveStmt.setInt(7, gls.shop.ktonr);
120	saveStmt.setDouble(8, gls.distance);
121
122	saveStmt.addBatch();
123	batchCount++;
124	if (batchCount >= 5000) {
125	saveStmt.executeBatch();
126	batchCount = 0;
127	}
128	}
129
130	public void saveBatch() throws SQLException{
131	saveStmt.executeBatch();
132	batchCount = 0;
133	}
134
135
136
137	public Connection getConnection(Properties props) throws SQLException, IOException {
138
139	String db_host = props.getProperty("DB_HOST");
140	String db_user = props.getProperty("DB_USER");
141	String db_pass = props.getProperty("DB_PASS");
142
143
144
145
146	Connection conn = null;
147	Properties connectionProps = new Properties();
148	connectionProps.put("user", db_user);
149	connectionProps.put("password", db_pass);
150
151	//For debug output, tilføj denne til JDBC url'en: &profileSQL=true
152	conn = DriverManager.getConnection(
153	"jdbc:mysql://" +
154	db_host +
155	":3306/?rewriteBatchedStatements=true",
156	connectionProps);
157	logger.info("Connected to database");
158	return conn;
159	}
160	}