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	torben	2582	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			}