java - JDBC - 双值未显示在 MySQL 数据库中

Question

我有一个程序，它从网页上抓取价格，然后找到该价格与上次抓取的价格的差异，然后将该值发送到我的 MySQL 数据库。

查看代码之前需要注意的事项:

实际价格( double )确实被发送并正确输入到我的数据库中，尽管我的priceChange变量没有。我尝试将其更改为 BigDecimal，但没有发生任何变化。

PriceGrabber.java(现在很草率，我知道。我最终会精简代码。一旦我的核心功能开始工作。)

    import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.TimerTask;
import java.util.Date;
import org.jsoup.*;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import java.util.*;
import java.lang.*;
import java.net.URL;
import java.io.*;



public class PriceGrabber extends TimerTask{


    @Override
    public void run() {


        try {

            if(Coinbase.getLastPrice() ==  Variables.getCoinbase()){
                System.out.println();
            }else {
                System.out.println("Price Change Detected in Coinbase!");
                System.out.println("Price Change: " + (Variables.getCoinbase() - Coinbase.getLastPrice()));
                Coinbase.setLastPrice(Variables.getCoinbase());
                Variables.addPrice(Variables.getCoinbase(), "Coinbase", (Variables.getCoinbase() - Coinbase.getLastPrice()));

            }
            if(BTCE.getLastPrice() ==  Variables.getBTCE()){
                System.out.println();
            }else {
                System.out.println("Price Change Detected in BTC-E!");
                System.out.println("Price Change: " + (Variables.getBTCE() - BTCE.getLastPrice()));
                BTCE.setLastPrice(Variables.getBTCE());
                Variables.addPrice(Variables.getBTCE(), "BTC-e", (Variables.getBTCE() - BTCE.getLastPrice()));
            }

            if(BitStamp.getLastPrice() ==  Variables.getBitStamp()){
                System.out.println();
            }else {
                System.out.println("Price Change Detected in BitStamp Market!");
                System.out.println("Price Change: " + (Variables.getBitStamp() - BitStamp.getLastPrice()));
                BitStamp.setLastPrice(Variables.getBitStamp());

                Variables.addPrice(Variables.getBitStamp(), "Bitstamp", (Variables.getBitStamp() - BitStamp.getLastPrice()));
            }


            if(Bitfinext.getLastPrice() ==  Variables.getBitfinext()){
                System.out.println();
            }else {
                System.out.println("Price Change Detected in Bitfinext!");
                System.out.println("Price Change: " + (Variables.getBitfinext() - Bitfinext.getLastPrice()));
                Bitfinext.setLastPrice(Variables.getBitfinext());
                Variables.addPrice(Variables.getBitfinext(), "Bitfinext", (Variables.getBitfinext() - Bitfinext.getLastPrice()));
            }



            //Variables.printPrices();


        } catch (IOException e) {

        }

    }


}

变量.java

       import java.io.*;
import java.math.BigDecimal;
import java.sql.*;
import java.util.TimerTask;
import java.util.Date;
import org.jsoup.*;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import java.util.*;
import java.lang.*;
import java.net.URL;
import java.io.*;
import java.net.MalformedURLException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Enumeration;


    public class Variables {

        public static final String DB_URL = "jdbc:mysql://URL TO DB";

        //  Database credentials
        public static final String USER = "username";
        public static final String PASS = "password";

    //Web Elements
    public static URL url = null;
    public static Document page = null;
    public static Element priceElement = null;
    public static Document doc = null;
    public static String price;
    public static String priceString;
    public static String timeStamp;









    //Different Market URL's
    public static String coinbaseURL = "https://bitcoinwisdom.com/markets/coinbase/btcusd";
    public static String btceURL = "https://bitcoinwisdom.com/markets/btce/btcusd";
    public static String bitstampURL = "https://bitcoinwisdom.com/markets/bitstamp/btcusd";
    public static String bitfinextURL = "https://bitcoinwisdom.com/markets/bitfinex/btcusd";



    //Sets the URL
    public static void setURL(String siteURL) throws MalformedURLException, IOException{

        url = new URL(siteURL);

    }


    public static Double getCoinbase() throws IOException{

        try{
            setURL(coinbaseURL);

            page = Jsoup.parse(url, 5000);
            if(page.select("div.green").first() == null){
                priceElement = page.select("div.red").first();
            }else{
                priceElement = page.select("div.green").first();
            }

            priceString = priceElement.toString();
            doc = Jsoup.parse(priceString);
            price = doc.body().text();



        }catch(IOException e) {
            System.out.println("something went wrong");
            System.out.println(e.getMessage());
        }
        return Double.parseDouble(price);
    }

    public static Double getBTCE() throws IOException{

        try{
            setURL(btceURL);

            page = Jsoup.parse(url, 5000);
            if(page.select("div.green").first() == null){
                priceElement = page.select("div.red").first();
            }else{
                priceElement = page.select("div.green").first();
            }

            priceString = priceElement.toString();
            doc = Jsoup.parse(priceString);
            price = doc.body().text();



        }catch(IOException e){
            System.out.println("oops! Something went wrong");
            System.out.println(e.getMessage());
        }
        return Double.parseDouble(price);
    }

    public static Double getBitStamp() throws IOException {

        try {
            setURL(bitstampURL);

            page = Jsoup.parse(url, 5000);
            if (page.select("div.green").first() == null) {
                priceElement = page.select("div.red").first();
            } else {
                priceElement = page.select("div.green").first();
            }

            priceString = priceElement.toString();
            doc = Jsoup.parse(priceString);
            price = doc.body().text();


        } catch (IOException e) {
            System.out.println("oops! Something went wrong");
            System.out.println(e.getMessage());
        }
        return Double.parseDouble(price);
    }

    public static Double getBitfinext() throws IOException {

            try {
            setURL(bitfinextURL);

            page = Jsoup.parse(url, 5000);
            if (page.select("div.green").first() == null) {
                priceElement = page.select("div.red").first();
            } else {
                priceElement = page.select("div.green").first();
            }

            priceString = priceElement.toString();
            doc = Jsoup.parse(priceString);
            price = doc.body().text();


        } catch (IOException e) {
            System.out.println("oops! Something went wrong");
            System.out.println(e.getMessage());
        }
        return Double.parseDouble(price);
    }








        //***********************************************************************************\\
        //TODO:******************************************************************************\\
        //TODO:   SEARCH FOR QUEUE SYSTEM TO AVOID HAVING TO RECONNECT FOR EACH ADDITION     \\
        //TODO:******************************************************************************\\
        //***********************************************************************************\\
    public static void addPrice(Double price, String market, Double priceChange){

        java.sql.Connection conn = null;
        Statement stmt = null;
        try{
            //STEP 2: Register JDBC driver
            Class.forName("com.mysql.jdbc.Driver");

            //STEP 3: Open a connection
            conn = DriverManager.getConnection(DB_URL, USER, PASS);

            java.sql.Timestamp  sqlDate = new java.sql.Timestamp(new java.util.Date().getTime());


            String query = " insert into prices (market, price, pricechange, time_of_change)"
                    + " values (?, ?, ?, ?)";


            PreparedStatement preparedStmt = conn.prepareStatement(query);
            preparedStmt.setString (1, market);
            preparedStmt.setDouble (2, price);
            preparedStmt.setDouble(3, priceChange);
            preparedStmt.setTimestamp(4, sqlDate);


            // execute the preparedstatement
            preparedStmt.execute();

            conn.close();


        }catch(SQLException se){
            //Handle errors for JDBC
            se.printStackTrace();
        }catch(Exception e){
            //Handle errors for Class.forName
            e.printStackTrace();
        }finally{
            //finally block used to close resources
            try{
                if(stmt!=null)
                    stmt.close();
            }catch(SQLException se2){
            }// nothing we can do
            try{
                if(conn!=null)
                    conn.close();
            }catch(SQLException se){
                se.printStackTrace();
            }//end finally try
        }//end try
    }//end main




}

我将向您展示 Coinbase.java 的源代码，它与其他类(BTCE.java、Bitfinex.java 等)完全相同，只是为了节省空间

Coinbase.java(与其他市场类别相同)

    /**
 * Created by Sullivan4653 on 12/8/2014.
 */
public class Coinbase {

    public static double lastPrice = 0.0;

    public static void setLastPrice(double price){
        lastPrice = price;

    }

    public static double getLastPrice(){
        return lastPrice;
    }
}

**最后是我的 MySQL 信息:**

除了priceChange 之外，我的MySQL 数据库/表和列都可以工作。值类型设置为 Double。默认值=空。这与我的价格栏相同，它似乎有效，因为我正在获取价格值。 (又名不是像我的 PriceChange 列那样每次都是 0)

我的问题是，为什么这个 double 现在显示在我的 MySQL 数据库中？我对帖子的长度表示歉意，但我想确保我不会遗漏任何可能有助于别人理解问题的细节。我已经为此苦苦挣扎了几天，但找不到错误!

谢谢!

Answer 1

如果您已经想制作这样的东西，请停止使用静态类，深入研究 Java :)

此外，如果您想向其他人提供代码，请使用一些好的命名约定，而不是让所有内容看起来都像默认变量(IE coinbaseURL，根据您的修饰符，它应该类似于 COINBASE_URL)。

在您的代码中，您使用新值声明旧值，因此您将覆盖它(您不能再使用它，因为它已更改为新值)。这就是我的意思:

Coinbase.setLastPrice(Variables.getCoinbase());
Variables.addPrice(Variables.getCoinbase(), "Coinbase", (Variables.getCoinbase() - Coinbase.getLastPrice()));

我已经简化并改进了您的代码，这应该可行。

import java.io.IOException;
import java.util.concurrent.Executors;
import java.util.concurrent.ScheduledExecutorService;
import java.util.concurrent.TimeUnit;

/**
 * @author JKetelaar
 */

public class PriceGrabber implements Runnable {

    private Coinbase coinbase;
    private Variables variables;

    public PriceGrabber(){
        this.coinbase = new Coinbase(0);
        this.variables = new Variables();

        this.variables.connect();
    }

    public static void main(String[] args){
        ScheduledExecutorService scheduleTaskExecutor = Executors.newScheduledThreadPool(5);

        scheduleTaskExecutor.scheduleAtFixedRate(new PriceGrabber(), 0, 10, TimeUnit.SECONDS);
    }

    @Override
    public void run() {
        try {
            if (coinbase.getPrice() == variables.getCoinbase()) {
                System.out.println("No changes found for Coinbase...");
            } else {
                System.out.println("Price Change detected in Coinbase!");

                Double cbase = variables.getCoinbase();
                Double cprice = coinbase.getPrice();

                System.out.println("Old price: " + cprice + "\nNew price: " + cbase + "\nPrice Change: " + (cbase - cprice));
                coinbase.setPrice(cbase);
                variables.addPrice(cbase, "Coinbase", (cprice - cprice));

            }
        } catch (IOException ignored) {

        }

    }


}

-

public class Coinbase {

    private double price = 0.0;

    public Coinbase(double price) {
        this.price = price;
    }

    public void setPrice(double price) {
        this.price = price;
    }

    public double getPrice() {
        return price;
    }
}

-

import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;

import java.io.IOException;
import java.net.URL;
import java.sql.*;

public class Variables {

    public static final String DB_URL = "jdbc:mysql://160.153.49.168:3306/btcprogram";

    //  Database credentials
    public static final String USER = "sully";
    public static final String PASS = "asweq123e";

    //Web Elements
    public static URL url = null;
    public static Document page = null;
    public static Element priceElement = null;
    public static Document doc = null;
    public static String price;
    public static String priceString;


    public static String coinbaseURL = "https://bitcoinwisdom.com/markets/coinbase/btcusd";
    private Connection connection;


    //Sets the URL
    public void setURL(String siteURL) throws IOException {
        url = new URL(siteURL);
    }


    public Double getCoinbase() throws IOException {

        try {
            setURL(coinbaseURL);

            page = Jsoup.parse(url, 5000);
            if (page.select("div.green").first() == null) {
                priceElement = page.select("div.red").first();
            } else {
                priceElement = page.select("div.green").first();
            }

            priceString = priceElement.toString();
            doc = Jsoup.parse(priceString);
            price = doc.body().text();


        } catch (IOException e) {
            System.out.println("something went wrong");
            System.out.println(e.getMessage());
        }
        return Double.parseDouble(price);
    }

    public void addPrice(Double price, String market, Double priceChange) {
        /**
         * In your table set the time of change to a default value, so the table will do the time itself.
         * Makes it easier for you and doesn't get complicated if you want to get others to insert prices.
         */
        this.query("INSERT INTO prices (market, price, pricechange) VALUES (?, ?, ?)", new Object[]{
                price, market, priceChange
        });
    }

    public ResultSet query(String q, Object[] args) {
        if (connection == null) {
            System.out.println("No connection to the database.");
            return null;
        }
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(q);
            for (int i = 1; i <= args.length; i++) {
                if (args[i] instanceof Double){
                    preparedStatement.setDouble(i, Double.parseDouble(String.valueOf(args[i])));
                }else{
                    preparedStatement.setString(i, String.valueOf(args[i - 1]));
                }
            }
            if (q.toLowerCase().startsWith("select")) {
                return preparedStatement.executeQuery();
            } else {
                preparedStatement.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public void connect() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager
                    .getConnection("jdbc:mysql://160.153.49.168:3306/btcprogram?"
                            + "user=sully&password=asweq123e");
            if (!connection.isClosed()) {
                System.out.println("Successfully connected to the database...\nReady for SQL queries!");
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

}