package org.kodejava.example.sql;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class SqlLimitExample {
public static void main(String[] args) {
Connection connection = null;
try {
connection = getConnection();
// Create PreparedStatement to get all data from database.
String query = "select count(*) from products";
PreparedStatement ps = connection.prepareStatement(query);
ResultSet result = ps.executeQuery();
int total = 0;
while (result.next()) {
total = result.getInt(1);
}
System.out.println("Total number of data in database: " + total + "n");
// Create PreparedStatement to the first 5 records only.
query = "select * from products limit 5";
ps = connection.prepareStatement(query);
result = ps.executeQuery();
System.out.println("Result fetched with specified limit 5");
System.out.println("====================================");
while (result.next()) {
System.out.println("id:" + result.getInt("id") +
", code:" + result.getString("code") +
", name:" + result.getString("name") +
", price:" + result.getString("price") +
", qty:" + result.getString("qty"));
}
// Create PreparedStatement to get data from the 4th
// record (remember the first record is 0) and limited
// to 3 records only.
query = "select * from products limit 3, 3";
ps = connection.prepareStatement(query);
result = ps.executeQuery();
System.out.println("nResult fetched with specified limit 3, 3");
System.out.println("====================================");
while (result.next()) {
System.out.println("id:" + result.getInt("id") +
", code:" + result.getString("code") +
", name:" + result.getString("name") +
", price:" + result.getString("price") +
", qty:" + result.getString("qty"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
closeConnection(connection);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* Get a connection to database.
*
* @return a connection to database.
* @throws Exception when an exception occurs.
*/
private static Connection getConnection() throws Exception {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost/kodejavadb";
return DriverManager.getConnection(url, "root", "");
}
/**
* Close a connection to database.
*
* @param connection a connection to be closed.
* @throws SQLException when an exception occurs.
*/
private static void closeConnection(Connection connection)
throws SQLException {
if (connection != null && !connection.isClosed()) {
connection.close();
}
}
}
An example result of our program is:
Total number of data in database: 10 Result fetched with specified limit 5 ==================================== id:1, code:P0001, name:Product 01, price:19.99, qty:10 id:2, code:P0002, name:Product 02, price:29.99, qty:20 id:3, code:P0003, name:Product 03, price:39.99, qty:30 id:4, code:P0004, name:Product 04, price:49.99, qty:40 id:5, code:P0005, name:Product 05, price:59.99, qty:50 Result fetched with specified limit 3, 3 ==================================== id:4, code:P0004, name:Product 04, price:49.99, qty:40 id:5, code:P0005, name:Product 05, price:59.99, qty:50 id:6, code:P0006, name:Product 06, price:69.99, qty:60
Latest posts by Wayan (see all)
- Install IntelliJ IDEA Community Edition and Write Hello Kotlin Program - May 13, 2018
- How do I write Hello World in Kotlin? - May 13, 2018
- How do I set the default Java (JDK) version on Mac OS X? - November 14, 2017