Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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.Collections;
- import java.util.Date;
- import java.util.LinkedList;
- import java.util.List;
- public class CrowdSource_Database {
- private Connection connection;
- public CrowdSource_Database() throws SQLException {
- try {
- Class.forName("org.hsqldb.jdbcDriver");
- } catch (ClassNotFoundException e) {
- e.printStackTrace();
- }
- connection = DriverManager.getConnection("jdbc:hsqldb:file:"
- + "/Users/Aamer/Documents/Java/CS_DB","SA","");
- Statement delayStmt = connection.createStatement();
- try {delayStmt.execute("SET WRITE_DELAY FALSE");} //Always update data on disk
- finally {delayStmt.close();}
- Statement sqlStmt = connection.createStatement();
- try {
- sqlStmt.execute("CREATE TABLE messages(contact VARCHAR(255) NOT NULL,"+
- "message VARCHAR(4096) NOT NULL,timeposted BIGINT NOT NULL ,up INT NOT NULL DEFAULT 0, down INT NOT NULL DEFAULT 0, score LONG NOT NULL DEFAULT 0 )");
- } catch (SQLException e) {
- System.out.println("Warning: Database table \"messages\" already exists.");
- } finally {
- sqlStmt.close();
- connection.commit();
- }
- }
- public void close() throws SQLException {
- connection.close();
- }
- public void incrementUp(String contact_ ) throws SQLException {
- String stmt = "UPDATE messages SET up = up+1 WHERE contact= contact_";
- Statement updatevalue = connection.createStatement();
- try{
- updatevalue.execute(stmt);
- } finally {
- updatevalue.close();
- connection.commit();
- }
- }
- public void reset(){
- try {
- Statement stmt = connection.createStatement();
- String sql = "TRUNCATE messages";
- sql = "DELETE FROM messages";
- stmt.executeUpdate(sql);
- } catch (SQLException e) {
- }
- }
- public void incrementDown(String contact_ ) throws SQLException {
- String stmt = "UPDATE messages SET down = down+1 WHERE contact= contact_";
- Statement updatevalue = connection.createStatement();
- try{
- updatevalue.execute(stmt);
- } finally {
- updatevalue.close();
- connection.commit();
- }
- }
- public void addMessage(ScamQuery m) throws SQLException {
- String stmt = "INSERT INTO MESSAGES(contact,message,timeposted) VALUES (?,?,?)";
- PreparedStatement insertMessage = connection.prepareStatement(stmt);
- try {
- insertMessage.setString(1, m.getContact()); //set value of first "?" to "Alastair"
- insertMessage.setString(2, m.getMessage());
- insertMessage.setLong(3, System.currentTimeMillis());
- insertMessage.executeUpdate();
- } finally { //Notice use of finally clause here to finish statement
- insertMessage.close();
- connection.commit();
- }
- }
- public List<ScamQuery> getQueries() throws SQLException {
- String stmt = "SELECT nick,message,timeposted,up,down,score FROM messages "+
- "ORDER BY timeposted";
- List<ScamQuery> store = new LinkedList<ScamQuery>();
- PreparedStatement recentMessages = connection.prepareStatement(stmt);
- try {
- ResultSet rs = recentMessages.executeQuery();
- try {
- while (rs.next())
- store.add(new ScamQuery(rs.getString(1), rs.getString(2), new Date(rs.getLong(3)), rs.getInt(4), rs.getInt(5), rs.getLong(6) ));
- } finally {
- rs.close();
- }
- }
- finally {
- recentMessages.close();
- }
- Collections.reverse(store);
- return store;
- }
- public long confidence (int up, int down){
- int n = up + down;
- if(n==0){
- return 0;
- }
- double prop = up/n;
- double z = 1.96; //95% confidence interval
- double confd = (prop + z*z/(2*n) - z * Math.sqrt((prop*(1-prop)+z*z/(4*n))/n))/(1+z*z/n);
- long conf = Math.round(confd);
- return conf;
- }
- public long calculateScore(String _contact) throws SQLException{
- String _up = "SELECT up FROM messages WHERE contact=_contact";
- String _down = "SELECT down FROM messages WHERE contact=_contact";
- int up;
- int down;
- PreparedStatement upstmt = connection.prepareStatement(_up);
- PreparedStatement downstmt = connection.prepareStatement(_down);
- try{
- ResultSet ups = upstmt.executeQuery();
- ResultSet downs = downstmt.executeQuery();
- try{
- up = ups.getInt(1);
- down = downs.getInt(1);
- }
- finally{
- ups.close();
- downs.close();
- }
- }
- finally{
- upstmt.close();
- downstmt.close();
- }
- long score = confidence(up, down);
- return score;
- }
- public void updateScore(String _contact) throws SQLException{
- @SuppressWarnings("unused")
- long newScore = calculateScore(_contact);
- String stmt = "UPDATE messages SET score = newScore WHERE contact= contact_";
- Statement updatevalue = connection.createStatement();
- try{
- updatevalue.execute(stmt);
- } finally {
- updatevalue.close();
- connection.commit();
- }
- }
- }
Add Comment
Please, Sign In to add comment