Java DB (Derby) 的使用(2)

ROWID is the logical or physical address of a designated row in database. Different database providers have different supports for it. So before using it in SQL, it is always a good practice to check the support capacity of the database used.The result is that Derby does not support ROWID, so it can not benefit the RowId class.We have also done some tests of using the Derby. Found out that Derby is not bad and is competent enough to do a normal data exploring as others. Its advantages are free, small, embedded, and it is supported by many big companies like IBM, Oracle, Sun, and a lot of liberated developers. It has a not bad perspective.

package com.han;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JavaDBUse1 {
	private static Connection con;
	private static Statement s;  

	static void createTable(Statement s) throws SQLException{
		s.execute("create table table1(" +
				"id char(6) not null primary key," +
				"name varchar(40)," +
				"score int)");
		s.execute("insert into table1 values('016954', 'San ZHANG', 86)");
		s.execute("insert into table1 values('016934', 'Wu WANG', 45)");
		s.execute("insert into table1 values('016908', 'Si LI', 97)");
	}

	public static void main(String[] args){
//		Derby.setPort("1526");
//		Derby.setServer("129.175.119.162");
		/*NetworkServerControl serverControl = new NetworkServerControl(InetAddress.getByName("myhost"),1621);
        serverControl.shutdown();*/
		
		Derby.loadDriver(); 
		try {		
			con=Derby.createDatabaseAndGetConnection("dbName1", "", "");
		//	con=Derby.getConnection("dbName3", "user2", "");
			con.setAutoCommit(false);
			s=con.createStatement();
			if(Derby.isTableExists("table1", con)){
				ResultSet rs=s.executeQuery("select * from table1 order by score");
				System.out.println();
				while(rs.next()){	
					StringBuilder sb=new StringBuilder(rs.getString("id"));
					sb.append("\t");
					sb.append(rs.getString("name"));
					sb.append("\t");
					sb.append(rs.getInt("score"));
					System.out.println(sb.toString());
				}
				System.out.println();
				rs.close();
				s.close();
				con.commit();
				con.close();
			}else{
				createTable(s);
				s.close();
				con.commit();
				con.close();
				System.out.println("Table is created");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 	
		Derby.shutdownDatabase("use1");			
		Derby.shutdownAll();
	}	
}

package com.han;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.RowIdLifetime;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * ROWID is the logical or physical address of a designated row in database.
 * Different database providers have different supports for it.
 * So before using it in SQL, it is always a good practice to check the support capacity
 * of the database used.
 * <p>
 * The result is that Derby does not support ROWID, so it can not benefit the RowId class.
 * <p>
 * We have also done some tests of using the Derby. Found out that Derby is not bad and
 * is competent enough to do a normal data exploring as others. Its advantages are free, 
 * small, embedded, and it is supported by many big companies like IBM, Oracle, Sun, and
 * a lot of liberated developers. It has a not bad perspective.
 *  
 * @author GAOWEN
 *
 */
public class TestDerbyRowId {
	static Connection con=null;
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		Derby.loadDriver();
		try {
	
			con=Derby.getConnection("dbName1","","");
			con.setAutoCommit(false);// for that later con.commit(); is valid
			DatabaseMetaData meta=con.getMetaData();
			RowIdLifetime rowIdLifetime=meta.getRowIdLifetime();
			System.out.println(rowIdLifetime);// To see if the Derby DBMS supports ROWID or not
			Derby.listAllTables(con);
			Derby.listAllSchemas(con);
			Derby.listAllSchemasAndTables(con);
			Statement s=con.createStatement();
			
			ResultSet rs=s.executeQuery("select * from tableRenamed");
			//ResultSetMetaData is useful to get information about the types and properties
			//of the columns in a ResultSet object
			//By the way, DatabaseMetaData is at a parallel level with ResultSetMetaData
			ResultSetMetaData rsmd=rs.getMetaData();// ResultSetMetaData has not the close() method
			int numberOfColumns=rsmd.getColumnCount();
			System.out.println(rsmd.isSearchable(1));
			System.out.println(numberOfColumns);
			System.out.println(rsmd.getTableName(3));// get the designated table name
			
			for(int i=1;i<=numberOfColumns;i++){
				System.out.println(rsmd.getColumnName(i)+" : "
									+rsmd.getColumnTypeName(i)+"("
									+rsmd.getPrecision(i)+")");
			}
			while(rs.next()){
				for(int i=1;i<=numberOfColumns;i++){
					System.out.print(rs.getObject(i)+"\t");
				}
				System.out.println();
			}
			//s.execute("alter table table1 add primary key(id)");
			
			/*s.executeUpdate("insert into table1 values('016925', 'Ming XIAO', 50)");
			s.addBatch("insert into table1 values('016945', 'Wang XIAO', 74)");
			s.addBatch("insert into table1 values('016955', 'Zhang XIAO', 92)");
			s.executeBatch();*/
			
			//s.execute("drop table table1");
			//s.execute("rename table table1 to tableRenamed");
		//	s.execute("rename column tableRenamed.id to identifiant");
			//rs.close();
			s.close();// It is generally good practice to release resources as soon as you are finished with them to avoid tying up database resources. 
			con.commit();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	
}

The Embedded mode is limited by that we can't run simultaneously two programs (two JVM instances) using a same database (databaseName is the same).

But we can instead use the NetworkServer mode to avoid this case, it is to say the "Client/Server" mode. In this mode, you have to first start the NetworkServer by this command :

 java org.apache.derby.drda.NetworkServerControl start [-h hostIP -p portNumber]
 

Or use the API :

 NetworkServerControl serverControl = new NetworkServerControl(InetAddress.getByName("myhost"),1621);
 

 serverControl.shutdown();
 

schema is above the table/view. In MySQL schema is equivalent to database. So in MySQL, create database==create schema, but create database is not applicable to Java Derby.

In Derby, schema is also equivalent to a user name.

package com.han;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Properties;

/**
 * The Embedded mode is limited by that we can't run simultaneously 
 * two programs (two JVM instances) using a same database (databaseName is the same).
 * <p>
 * But we can instead use the NetworkServer mode to avoid this case, 
 * it is to say the "Client/Server" mode.
 * In this mode, you have to first start the NetworkServer by this command :
 * <pre>
 * java org.apache.derby.drda.NetworkServerControl start [-h hostIP -p portNumber]
 * </pre>
 * Or use the API : 
 * <pre>
 * NetworkServerControl serverControl = new NetworkServerControl(InetAddress.getByName("myhost"),1621);
 * </pre>
 * <pre>
 * serverControl.shutdown();
 * </pre>
 * schema is above the table/view. In MySQL schema is equivalent to database. 
 * So in MySQL, create database==create schema, 
 * but create database is not applicable to Java Derby. 
 * <p>
 * In Derby, schema is also equivalent to a user name.
 * @author HAN
 *
 */
public class Derby {
	private static Connection con=null;
	private static String port=null;
	private static String ip=null;

	/**
	 * The port will be set to default: 1527
	 */
	public static void setPortToDefault(){
		port="1527";		
	}

	public static void setPort(String port){
		Derby.port=port;
	}

	public static void setServer(String ip){
		Derby.ip=ip;
	}

	/**
	 * This express loading driver is not necessary for Java 6 and later, JDBC 4.0 and later.
	 * Because it can be added automatically by <code>DriverManager</code> when connecting to a database.
	 */
	public static void loadDriver(){
		//load the driver
		if(port==null){
			if(ip!=null){
				System.out.println("You seem to have set an ip address, if so, you have also to assign a port, or else an embedded database will be automatically used");
			}
			try {
				Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
				System.out.println("The embedded driver is successfully loaded");
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}else{
			try{
				Class.forName("org.apache.derby.jdbc.ClientDriver");
				System.out.println("The client driver is successfully loaded");
			}catch(ClassNotFoundException e){
				e.printStackTrace();
			}
		}
	}

	/**
	 * create and connect a database
	 * @param databaseName
	 * @param user
	 * @param password
	 * @return a connection to the URL 
	 * @throws SQLException 
	 */
	public static Connection createDatabaseAndGetConnection(String databaseName, String user, String password) throws SQLException{
		//create and connect the database
		Properties props=new Properties();
		props.put("user",user);	
		props.put("password",password);
		if(port==null){
			if(ip!=null){
				System.out.println("You seem to have set an ip address, if so, you have also to assign a port before loading the driver, or else an embedded database is automatically used");
			}
			con=DriverManager.getConnection("jdbc:derby:"+databaseName+";create=true", props);
			System.out.println("Connection is successfully established, it uses an Embedded database");
		}else if(ip==null){
			con=DriverManager.getConnection("jdbc:derby://localhost:"+port+"/"+databaseName+";create=true", props);
			System.out.println("Connection is sucessfully established, it uses an network database but stored in the local host via the port: "+port);
		}else{
			con=DriverManager.getConnection("jdbc:derby://"+ip+":"+port+"/"+databaseName+";create=true", props);
			System.out.println("Connection is sucessfully established, it uses an network database whose host ip is: "+ip+" and via the port: "+port);
		}
		return con;
	}

	/**
	 * Shut down a specified database. But it doesn't matter that later we could also connect to another database.
	 * @param databaseName
	 */
	public static void shutdownDatabase(String databaseName){
		if(port==null){	
			try {
				DriverManager.getConnection("jdbc:derby:"+databaseName+";shutdown=true");				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				System.out.println("Database: "+databaseName+" shut down normally");
			}
		}else if(ip==null){
			try {
				DriverManager.getConnection("jdbc:derby://localhost:"+port+"/"+databaseName+";shutdown=true");				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				System.out.println("Database: "+databaseName+" shut down normally");
			}
		}else{
			try {
				DriverManager.getConnection("jdbc:derby://"+ip+":"+port+"/"+databaseName+";shutdown=true");	
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				System.out.println("Database: "+databaseName+" shut down normally");
			}
		}
	}

	/**
	 * shut down all opened databases and close the Derby driver.
	 * The effect is that after the execution of this method, we will not permitted to use Derby again in the rest of our program.
	 * Or else, an exception of "can't find a suitable driver for [a database URL]" will be thrown.
	 */
	public static void shutdownAll(){
		try {
			DriverManager.getConnection("jdbc:derby:;shutdown=true");	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("All databases shut down normally and Derby completely closed");
		}
	}

	/**
	 * Just connect to a database desired by providing the appropriate parameters.
	 * @param databaseName
	 * @param user
	 * @param password
	 * @return
	 * @throws SQLException 
	 */
	public static Connection getConnection(String databaseName, String user, String password) throws SQLException{
		if(port==null){
			if(ip!=null){
				System.out.println("You seem to have set an ip address, if so, you have also to assign a port before loading the driver, or else an embedded database is automatically used");
			}
			con=DriverManager.getConnection("jdbc:derby:"+databaseName,user,password);
			System.out.println("Connection is sucessfully established, it uses an Embedded database");
		}else if(ip==null){
			con=DriverManager.getConnection("jdbc:derby://localhost:"+port+"/"+databaseName,user,password);
			System.out.println("Connection is sucessfully established, it uses an network database but stored in the local host via the port: "+port);
		}else{
			con=DriverManager.getConnection("jdbc:derby://"+ip+":"+port+"/"+databaseName,user,password);
			System.out.println("Connection is sucessfully established, it uses an network database whose host ip is: "+ip+" and via the port: "+port);
		}
		return con;
	}
	
	public static HashSet<String> listAllTables(Connection con) throws SQLException{
		DatabaseMetaData meta = con.getMetaData();
		ResultSet res = meta.getTables(null, null, null, new String[]{"TABLE"});
		HashSet<String> set=new HashSet<String>();
		while (res.next()) {
			set.add(res.getString("TABLE_NAME"));
			//use TABLE_SCHEM to view all users or schemas
			//set.add(res.getString("TABLE_SCHEM"));
		}
		System.out.println("All the tables associated to current connection are :");
		System.out.println(set);
		return set;
	}
	
	public static boolean isTableExists(String table, Connection con) throws SQLException{
		if(listAllTables(con).contains(table.toUpperCase())){
			return true;
		}else{
			return false;
		}
	}
	
	public static HashSet<String> listAllSchemas(Connection con) throws SQLException{
		DatabaseMetaData meta = con.getMetaData();
		ResultSet res = meta.getSchemas(null, null);
		HashSet<String> set=new HashSet<String>();
		while (res.next()) {
			set.add(res.getString("TABLE_SCHEM"));
		}
		System.out.println("All the schemas associated to current connection are :");
		System.out.println(set);
		return set;
	}
	
	public static HashMap<String, String> listAllSchemasAndTables(Connection con) throws SQLException{
		DatabaseMetaData meta = con.getMetaData();
		ResultSet res = meta.getTables(null, null, null, new String[]{"TABLE"});
		HashMap<String, String> map=new HashMap<String, String>();
		while (res.next()) {
			map.put(res.getString("TABLE_SCHEM"),res.getString("TABLE_NAME"));
		}
		System.out.println("All the tables and their corresponding schemas associated to current connection are :");
		System.out.println(map);
		return map;
	}
}