Apache Derby is a java database that can be also used as an embedded database. It’s really usefull if you don’t want to store data in xml format or raw text
This example shows how to use the apache derby the embedded way and has some CRUD functions.
package derbycrud;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
/**
*
* @author gkatzioura
*/
public class EmbeddedDatabase {
private String driver = "org.apache.derby.jdbc.EmbeddedDriver";
private String protocol = "jdbc:derby";
private String dbName = "embeddedDB";
private Connection connection;
private Statement statement;
private static final String table_person = "PERSON";
private static final String createtablePerson = "CREATE TABLE PERSON(NAME VARCHAR(100),NICKNAME VARCHAR(100))";
private static final String droptablePerson = "DROP TABLE PERSON";
public EmbeddedDatabase(){
try {
loadDriver();
try {
connection = DriverManager.getConnection(protocol+":"+dbName+";create=true");
createTables();
} catch (SQLException ex) {
Logger.getLogger(EmbeddedDatabase.class.getName()).log(Level.SEVERE, null, ex);
}
} catch (ClassNotFoundException ex) {
Logger.getLogger(EmbeddedDatabase.class.getName()).log(Level.SEVERE, null, ex);
}
}
/**
Loads the apache derby driver.
*/
private void loadDriver() throws ClassNotFoundException {
Class.forName(driver);
}
/**
Checks if a table is already creating if not the table is created.
*/
private void createTables() {
try {
if(!tableExists(table_person)) {
statement = connection.createStatement();
statement.execute(createtablePerson);
statement.close();
}
} catch (SQLException ex) {
Logger.getLogger(EmbeddedDatabase.class.getName()).log(Level.SEVERE, null, ex);
}
}
/**
Function to drop Tables.
*/
public void dropTables() {
try {
if(tableExists(table_person)) {
statement = connection.createStatement();
statement.execute("DROP TABLE PERSON");
statement.close();
}
} catch (SQLException ex) {
Logger.getLogger(EmbeddedDatabase.class.getName()).log(Level.SEVERE, null, ex);
}
}
/**
Function to see if table exists.
*/
private boolean tableExists(String tableName) throws SQLException {
boolean toret = false;
DatabaseMetaData databaseMetaData = connection.getMetaData();
ResultSet resultSet = databaseMetaData.getTables(null, null, table_person, null);
if(resultSet.next()) {
toret = true;
}
resultSet.close();
return toret;
}
/**
Making an insert.
*/
public int insertPerson(String name,String nickname) {
int toret = 0;
try {
statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
ResultSet resultSet = statement.executeQuery("SELECT*FROM PERSON "
+ "WHERE NAME='"+name+"' "
+ "OR NICKNAME='"+nickname+"'");
if(countRows(resultSet)==0) {
toret = statement.executeUpdate("INSERT INTO PERSON VALUES('"+name+"','"+nickname+"')");
}
resultSet.close();
statement.close();
} catch (SQLException ex) {
Logger.getLogger(EmbeddedDatabase.class.getName()).log(Level.SEVERE, null, ex);
}
return toret;
}
/**
Selecting Persons.
*/
public String[][] getPersons() {
try {
statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
ResultSet resultSet = statement.executeQuery("SELECT*FROM PERSON");
int i = countRows(resultSet);
List<String[]> toret = new ArrayList<String[]>();
while(resultSet.next()) {
toret.add(new String[]{resultSet.getString("NAME"),resultSet.getString("NICKNAME")});
}
return toret.toArray(new String[toret.size()][]);
} catch (SQLException ex) {
Logger.getLogger(EmbeddedDatabase.class.getName()).log(Level.SEVERE, null, ex);
}
return null;
}
/**
Deleting person.
*/
public void deletePerson(String name) {
try {
statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
ResultSet resultSet = statement.executeQuery("SELECT*FROM PERSON "
+ "WHERE NAME='"+name+"'");
while(resultSet.next()) {
resultSet.deleteRow();
}
resultSet.close();
statement.close();
} catch (SQLException ex) {
Logger.getLogger(EmbeddedDatabase.class.getName()).log(Level.SEVERE, null, ex);
}
}
/**
Count number of rows.
*/
public int countRows(ResultSet resultSet) {
int size = 0;
try {
while(resultSet.next()) {
size++;
}
} catch (SQLException ex) {
Logger.getLogger(EmbeddedDatabase.class.getName()).log(Level.SEVERE, null, ex);
}
return 0;
}
}
You need to create an updateble statement, since you don’t want an exception like “not allowed because the ResultSet is not an updatable ResultSet.”, when calling a delete function.
statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
maven dependency
<dependency>
<groupId>org.apache.derby</groupId>
<artifactId>derby</artifactId>
<version>10.9.1.0</version>
</dependency>
Emmanouil Gkatziouras 