The following program demonstrates how to connect to a database using DriverManager, then create a Statement from the connection, finally get the ResultSet or updateCount by calling executeQuery, executeUpdate and execute.
Notice calling Connection, Statement, ResultSet related methods throws SQLException. The try-with-resources block makes sure the Connection, Statement and ResultSet in the program are always closed.
- Closing a Connection also closes the Statement and ResultSet.
- Closing a statement also closes the ResultSet.
There are 3 ways to execute a sql statement.
- When you run sql SELECT, the preferred way is to call stmt.executeQuery, which returns a ResultSet.
- When you run sql DELETE, INSERT, UPDATE, the preferred way is to call stmt.executeUpdate, which returns an int indicating how many rows are effected.
- stmt.execute can run SELECT, DELETE, INSERT and UPDATE, it returns a boolean indicating the whether or not a ResultSet can be accessed. You then call stmt.getResultSet or stmt.getUpdateCount to further process the result.
You call while(rs.next()){...} to integrate through the ResultSet. Note the column index is started from 1, the column can be referred either by name or column index.
package derby;
import java.sql.*;
public class DerbyDb {
/*
* Download and unzip latest Derby http://db.apache.org/derby/derby_downloads.html
* Set classpath to point to derby.jar
* javac -cp "/Users/homenetwork/Downloads/db-derby-10.13.1.1-lib/lib/derby.jar:." DerbyDb.java
* java -cp "/Users/homenetwork/Downloads/db-derby-10.13.1.1-lib/lib/derby.jar:." DerbyDb
*/
static final String URL = "jdbc:derby:social;create=true;";
public static void main(String...args) throws SQLException {
try(Connection conn = DriverManager.getConnection(URL);
Statement stmt = conn.createStatement();){
//stmt.executeUpdate("Drop table people");
int r = stmt.executeUpdate("create table people ("
+ "name VARCHAR(255),"
+ "age INTEGER,"
+ "emailAddress VARCHAR(255),"
+ "gender varchar(255))");
System.out.println(r);
r = stmt.executeUpdate("insert into people values ('Ted', 16, 'ted@email.com', 'MALE')");
System.out.println(r);
r = stmt.executeUpdate("insert into people values ('Tedy', 16, 'tedy@email.com', 'MALE')");
System.out.println(r);
ResultSet rs = stmt.executeQuery("select * from people");
while(rs.next()) {
System.out.println("name = " + rs.getString(1));
int age = rs.getInt(2);
System.out.println(age);
String email = (String)rs.getObject("emailAddress");
System.out.println(email);
String gender = rs.getString("gender");
System.out.println(gender);
}
System.out.println();
sql(stmt, "insert into people values ('Min', 36, 'min@email.com', 'MALE')");
sql(stmt, "select * from people");
stmt.executeUpdate("Drop table people");
}
}
static void sql(Statement stmt, String sql) throws SQLException {
boolean isResultSet = stmt.execute(sql);
if(isResultSet) {
ResultSet rs = stmt.getResultSet();
while(rs.next()) {
System.out.println("name = " + rs.getString(1));
int age = rs.getInt(2);
System.out.println(age);
String email = (String)rs.getObject("emailAddress");
System.out.println(email);
String gender = rs.getString("gender");
System.out.println(gender);
}
rs.close(); //closing Statement also closes ResultSet
} else {
int r = stmt.getUpdateCount();
System.out.println(r);
}
}
}
Besides rs.getInt, rs.getString and rs.getObject, there are other methods: rs.getBoolean, getDate, getDouble, getLong, getTime and getTimeStamp. Please check the java documents for their corresponding usage.
Improved version
Finally,
- OCPJP test DriverManager, which is not a preferred way in workplace. DataSource is preferred way. Most of time, DAO layer is managed with frameworks like ibatis, mybais, Hibernate, JPA etc.
- OCPJP test Statement, which is not a preferred way in workplace. PreparedStatement is preferred way.
