derby数据库嵌入程序模式

导入相应jar包，我是直接在derby官网下载压缩包后解压后把 lib文件夹下的jar包全导入到项目中了，然后就可以在项目中直接使用

 

 

 

package db;
/*

Licensed to the Apache Software Foundation (ASF) under one or more
contributor license agreements. See the NOTICE file distributed with
this work for additional information regarding copyright ownership.
The ASF licenses this file to You under the Apache License, Version 2.0
(the "License"); you may not use this file except in compliance with
the License. You may obtain a copy of the License at

http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.

*/

import javax.sql.*;
import java.sql.*;
import org.apache.derby.jdbc.EmbeddedDataSource;

/**
* A container for the singleton data source, so we don't have to
* create a separate one for each class that wants to do JDBC
*/
public class DatabaseManager {

private static EmbeddedDataSource ds;

public static String REQUESTS_TABLE = "APP.REQUESTS";
public static String EVENTS_TABLE = "APP.EVENTS";

// We want to keep the same connection for a given thread
// as long as we're in the same transaction
private static ThreadLocal<Connection> tranConnection = new ThreadLocal();

private static void initDataSource(String dbname, String user,
String password) {
ds = new EmbeddedDataSource();
ds.setDatabaseName(dbname);
ds.setUser(user);
ds.setPassword(password);
ds.setCreateDatabase("create");
}

public static void logSql() throws Exception {
executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
"'derby.language.logStatementText', 'true')");
}

public static synchronized void beginTransaction() throws Exception {
if ( tranConnection.get() != null ) {
throw new Exception("This thread is already in a transaction");
}
Connection conn = getConnection();
conn.setAutoCommit(false);
tranConnection.set(conn);
}

public static void commitTransaction() throws Exception {
if ( tranConnection.get() == null ) {
throw new Exception("Can't commit: this thread isn't currently in a " +
"transaction");
}
tranConnection.get().commit();
tranConnection.set(null);
}

public static void rollbackTransaction() throws Exception {
if ( tranConnection.get() == null ) {
throw new Exception("Can't rollback: this thread isn't currently in a " +
"transaction");
}
tranConnection.get().rollback();
tranConnection.set(null);
}

/** get a connection */
public static Connection getConnection() throws Exception {
if ( tranConnection.get() != null ) {
return tranConnection.get();
} else {
return ds.getConnection();
}
}

public static void releaseConnection(Connection conn) throws Exception {
// We don't close the connection while we're in a transaction,
// as it needs to be used by others in the same transaction context
if ( tranConnection.get() == null ) {
conn.close();
}
}

public static void initDatabase(String dbname, String user, String password,
boolean dropTables)
throws Exception {
initDataSource(dbname, user, password);

if ( dropTables ) {
dropTables();
}

// Assumption: if the requests table doesn't exist, none of the
// tables exists. Avoids multiple queries to the database
if ( ! tableExists("REQUESTS") ) {
createTables();
}
}

private static boolean tableExists(String tablename) throws Exception {
Connection conn = getConnection();
ResultSet rs;
boolean exists;

try {
DatabaseMetaData md = conn.getMetaData();

rs = md.getTables(null, "APP", tablename, null);
exists = rs.next();
} finally {
releaseConnection(conn);
}

return exists;
}

private static void createTables() throws Exception {
System.out.println("Creating tables");

executeUpdate(
"CREATE TABLE " + REQUESTS_TABLE + "(" +
"sequence_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, " +
"request_type INTEGER, " +
"event_id VARCHAR(300), " +
"date VARCHAR(20), " +
"title VARCHAR(300), " +
"edit_url VARCHAR(300))");

executeUpdate(
"CREATE TABLE " + EVENTS_TABLE + "(" +
"event_id VARCHAR(300) PRIMARY KEY, " +
"date VARCHAR(20), " +
"title VARCHAR(300), " +
"edit_url VARCHAR(300), " +
"version_id VARCHAR(300))");
}

/**
* Drop the tables. Used mostly for unit testing, to get back
* to a clean state
*/
public static void dropTables() throws Exception {
try {
executeUpdate("DROP TABLE " + REQUESTS_TABLE);
} catch ( SQLException sqle ) {
if (! tableDoesntExist(sqle.getSQLState())) {
throw sqle;
}
}

try {
executeUpdate("DROP TABLE " + EVENTS_TABLE);
} catch ( SQLException sqle ) {
if (! tableDoesntExist(sqle.getSQLState())) {
throw sqle;
}
}
}

private static boolean tableDoesntExist(String sqlState) {
return sqlState.equals("42X05") ||
sqlState.equals("42Y55");
}

/**
* Clean out the tables
*/
public static void clearTables() throws Exception {
Connection conn = getConnection();

try {
executeUpdate("DELETE FROM " + REQUESTS_TABLE);
executeUpdate("DELETE FROM " + EVENTS_TABLE);
} finally {
releaseConnection(conn);
}

}

/**
* Helper wrapper around boilerplate JDBC code. Execute a statement
* that doesn't return results using a PreparedStatment, and returns
* the number of rows affected
*/
public static int executeUpdate(String statement)
throws Exception {
Connection conn = getConnection();
try {
PreparedStatement ps = conn.prepareStatement(statement);
return ps.executeUpdate();
} finally {
releaseConnection(conn);
}
}

/**
* Helper wrapper around boilerplat JDBC code. Execute a statement
* that returns results using a PreparedStatement that takes no
* parameters (you're on your own if you're binding parameters).
*
* @return the results from the query
*/
public static ResultSet executeQueryNoParams(Connection conn,
String statement) throws Exception {
PreparedStatement ps = conn.prepareStatement(statement);
return ps.executeQuery();
}

public static void main(String[] args) throws Exception{

initDataSource("test1","app","app");
logSql();
Connection conn = getConnection() ;
PreparedStatement ps = conn.prepareStatement("select * from T ") ;
ResultSet st = ps.executeQuery() ;
while(st.next()){
System.out.println(st.getString(1));
}
conn.close() ;
}
}