Connect Log Service with JDBC

Example

A bike-sharing log contains the user's age, gender, battery usage, vehicle ID, operation latency, latitude, lock type, longitude, operation type, operation result, and unlocking type. Data is stored in the Logstore:ebike Logstore of the project:trip_demo project. The project resides in the China (Hangzhou) region.

Time :10-12 14:26:44
__source__: 11.164.232.105 
__topic__: v1 
age: 55 
battery: 118497.673842 
bikeid: 36 
gender: male 
latency: 17 
latitude: 30.2931185245 
lock_type: smart_lock 
longitude: 120.052840484 
op: unlock 
op_result: ok 
open_lock: bluetooth 
userid: 292

JDBC statistics

1. Create a Maven project and add the following JDBC dependency to the pom.xml file.

   <dependency>
    <groupId>MySQL</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.6</version>
   </dependency>

2. Use the following code to create a Java application to query logs by using JDBC.

   /**
   * Created by mayunlei on 2017/6/19.
   */
   import com.mysql.jdbc.*;
   import java.sql.*;
   import java.sql.Connection;
   import java.sql.Statement;
   /**
   * Created by mayunlei on 2017/6/15.
   */
   public class jdbc {
    public static void main(String args[]){
         // Set the required parameters, such as the project name and Logstore name.
        final String endpoint = "cn-hangzhou-intranet.sls.aliyuncs.com";// Set the private endpoint of Log Service or a VPC.
        final String port = "10005"; // Set the MySQL port to 10005. JDBC uses this port to access log by default.
        final String project = "trip-demo";
        final String logstore = "ebike";
        final String accessKeyId = "";
        final String accessKey = "";
        Connection conn = null;
        Statement stmt = null;
        try {
            // Step 1: Load the JDBC driver.
            Class.forName("com.mysql.jdbc.Driver");
            // Step 2: Create a connection string.
            conn = DriverManager.getConnection("jdbc:mysql://"+endpoint+":"+port+"/"+project,accessKeyId,accessKey);
            // Step 3: Create a statement.
            stmt = conn.createStatement();
            // Step 4: Create a search statement. Query the number of logs that are generated on October 11, 2017, and meet the condition op = "unlock".
            String sql = "select count(1) as pv,avg(latency) as avg_latency from "+logstore+"  " +
                    "where     __date__  >=  '2017-10-11 00:00:00'   " +
                    "     and  __date__  <   '2017-10-12 00:00:00'" +
                    " and     op ='unlock'";
            // Step 5: Run the statement.
            ResultSet rs = stmt.executeQuery(sql);
            // Step 6: Extract the query results.
            while(rs.next()){
                //Retrieve by column name
                System.out.print("pv:");
                // Obtain pv from the results.
                System.out.print(rs.getLong("pv"));
                System.out.print(" ; avg_latency:");
                // Obtain avg_latency from the results.
                System.out.println(rs.getDouble("avg_latency"));
                System.out.println();
            }
            rs.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt ! = null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn ! = null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
   }