Java DB (Derby) 的使用(3)

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 freedom developers. It has a not bad perspective.
 *  
 * @author GAOWEN
 *
 */
public class TestDerbyRowId {
	static Connection con; // in fact, the object is null by default
	static Statement s=null;
	static ResultSet rs=null;
	static ResultSetMetaData rsmd;
	
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		Derby.loadDriver();
		try {
			//System.out.println(con);
			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);
			s=con.createStatement();
			
			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
			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)+")");
			}
			for(int i=1;i<=numberOfColumns;i++){
				String columnName=rsmd.getColumnName(i);
				if(columnName.length()>rsmd.getPrecision(i)){
					columnName=rsmd.getColumnName(i).substring(0, rsmd.getPrecision(i));
				}
				System.out.printf("%-"+rsmd.getPrecision(i)+"s\t|",columnName);
			}
			System.out.println();
			while(rs.next()){
				for(int i=1;i<=numberOfColumns;i++){
					System.out.printf("%-"+rsmd.getPrecision(i)+"s\t|",rs.getObject(i));
				}
				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
			System.out.println(e.getErrorCode());			
			System.out.println(e.getSQLState());
			System.out.println(e.getMessage());
			e.printStackTrace();
		}
		Derby.shutdownDatabase("dbName1");			
		Derby.shutdownAll();
		try {
			Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
			con=Derby.getConnection("dbName1","","");
			s=con.createStatement();
			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();
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		Derby.shutdownDatabase("dbName1");			
		Derby.shutdownAll();
	}
}

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("dbName1");			
		Derby.shutdownAll();
		
	}	
}

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.
	 * Because the Derby engine is not closed.
	 * @param databaseName
	 */
	public static void shutdownDatabase(String databaseName){
		boolean gotSQLExc = false;
		if(port==null){	
			try {
				DriverManager.getConnection("jdbc:derby:"+databaseName+";shutdown=true");				
			} catch (SQLException se) {
				if ( se.getSQLState().equals("08006") ) {
					gotSQLExc = true;
				}
			}
			if (!gotSQLExc) {
				System.out.println("Database did not shut down normally");
			}  else  {
				System.out.println("Database: "+databaseName+" shut down normally");
			}
		}else if(ip==null){
			try {
				DriverManager.getConnection("jdbc:derby://localhost:"+port+"/"+databaseName+";shutdown=true");				
			} catch (SQLException se) {
				// TODO Auto-generated catch block
				if ( se.getSQLState().equals("08006") ) {
					gotSQLExc = true;
				}
			}
			if (!gotSQLExc) {
				System.out.println("Database did not shut down normally");
			}  else  {
				System.out.println("Database: "+databaseName+" shut down normally");
			}
		}else{
			try {
				DriverManager.getConnection("jdbc:derby://"+ip+":"+port+"/"+databaseName+";shutdown=true");	
			} catch (SQLException se) {
				if ( se.getSQLState().equals("08006") ) {
					gotSQLExc = true;
				}
			}
			if (!gotSQLExc) {
				System.out.println("Database did not shut down normally");
			}  else  {
				System.out.println("Database: "+databaseName+" shut down normally");
			}
		}
	}

	/**
	 * shut down all opened databases and close the Derby engine.
	 * 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.
	 * However, you can still use another approach to resolve this problem: newInstance()
	 * For example,
	 * <pre>
	 * Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
	 * </pre>
	 */
	public static void shutdownAll(){
		boolean gotSQLExc = false;
		try {
			DriverManager.getConnection("jdbc:derby:;shutdown=true");	
		} catch (SQLException se) {
			// TODO Auto-generated catch block
			if ( se.getSQLState().equals("XJ015") ) {
				gotSQLExc = true;
			}
		}
		if (!gotSQLExc) {
			System.out.println("Database did not shut down normally");
		}  else  {
			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;
	}
}