Nov 10, 2018

MySQL JDBC example


1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
//STEP 1. Import required packages

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

public class JDBCService {

    // JDBC driver name and database URL
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://192.168.1.5:3306/i88";

    //  Database credentials
    static final String USER = "user";
    static final String PASS = "pass";

    public static void main(String[] args) {
        try {
            Connection conn = null;
            Statement stmt = null;
            try {
                //STEP 2: Register JDBC driver
                Class.forName("com.mysql.jdbc.Driver");

                //STEP 3: Open a connection
                System.out.println("Connecting to database...");
                conn = DriverManager.getConnection(DB_URL, USER, PASS);

                //STEP 4: Execute a query to create statment with
                // required arguments for RS example.
                System.out.println("Creating statement...");
                stmt = conn.createStatement(
                        ResultSet.TYPE_SCROLL_INSENSITIVE,
                        ResultSet.CONCUR_READ_ONLY);
                String sql;
                sql = "SELECT id,id1 FROM sanity_copy_contact";
                // Move cursor to the last row.
                try (ResultSet rs = stmt.executeQuery(sql)) {
                    // Move cursor to the last row.
                    System.out.println("Moving cursor to the last...");
                    rs.last();

                    //STEP 5: Extract data from result set
                    System.out.println("Displaying record...");
                    //Retrieve by column name
                    int id = rs.getInt("id");
                    int id1 = rs.getInt("id1");

                    //Display values
                    System.out.print("ID: " + id);
                    System.out.print(", id1: " + id1);

                    // Move cursor to the first row.
                    System.out.println("Moving cursor to the first row...");
                    rs.first();

                    //STEP 6: Extract data from result set
                    System.out.println("Displaying record...");
                    //Retrieve by column name
                    id = rs.getInt("id");
                    id1 = rs.getInt("id1");

                    //Display values
                    System.out.print("ID: " + id);
                    System.out.print(", id1: " + id1);
                    // Move cursor to the first row.

                    System.out.println("Moving cursor to the next row...");
                    rs.next();

                    //STEP 7: Extract data from result set
                    System.out.println("Displaying record...");
                    id = rs.getInt("id");
                    id1 = rs.getInt("id1");

                    //Display values
                    System.out.print("ID: " + id);
                    System.out.print(", id1: " + id1);

                    ResultSetMetaData rsmd = rs.getMetaData();
                    String name = rsmd.getColumnName(1);
                    int count = rs.getInt(name);
                    System.out.print("count: " + count);
                }
                stmt.close();
                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
            System.out.println("Goodbye!");

            conn = DriverManager.getConnection(DB_URL, USER, PASS);

            //STEP 4: Execute a query to create statment with
            // required arguments for RS example.
            System.out.println("Creating statement...");
            stmt = conn.createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);

            String sql = "SELECT count(*) as count FROM sanity_copy_contact";
            ResultSet rs = stmt.executeQuery(sql);
            rs.first();

            //get from column name
            int count = rs.getInt("count");
            System.out.print("count: " + count);

            //get from column number
            //the column number starts at 1
            count = rs.getInt(1);
            System.out.print("count: " + count);

            ResultSetMetaData rsmd = rs.getMetaData();
            String name = rsmd.getColumnName(1);
            count = rs.getInt(name);
            System.out.print("count: " + count);

        } catch (SQLException ex) {
            Logger.getLogger(JDBCService.class.getName()).log(Level.SEVERE, null, ex);
        }//end try
    }
}//end main


Example 2:


1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
private Connection connect = null;
    private Statement statement = null;
    private PreparedStatement preparedStatement = null;
    private ResultSet resultSet = null;

    public void JDBCexample(String i88ca) {

        try {
            // this will load the MySQL driver, each DB has its own driver
            Class.forName("com.mysql.jdbc.Driver");
            // setup the connection with the DB.
            connect = DriverManager.getConnection("jdbc:mysql://192.168.1.5/mydb?user=user&password=pass");

            // statements allow to issue SQL queries to the database
            statement = connect.createStatement();
            // resultSet gets the result of the SQL query
            resultSet = statement
                    .executeQuery("select * from mytable");

            // preparedStatements can use variables and are more efficient
            preparedStatement = connect
                    .prepareStatement("insert into mytable(`url`) values (?)");
            preparedStatement.setString(1, i88ca);
            preparedStatement.executeUpdate();
            preparedStatement = connect
                    .prepareStatement("select * from mytable");
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                System.out.println(resultSet.getString("url"));
            }

        } catch (ClassNotFoundException | SQLException ex) {
            Logger.getLogger(MyClass.class.getName()).log(Level.SEVERE, null, ex);
        }
    }