Java Database Derby Instance 2 – JAVA編程語言程序開發技術文章

package com.han.ch20.q2Andq3; 
 
import java.sql.Connection; 
import java.sql.Date; 
import java.sql.PreparedStatement; 
import java.sql.ResultSet; 
import java.sql.ResultSetMetaData; 
import java.sql.SQLException; 
 
import com.han.Derby; 
 
public class UpdateStu { 
    private static Connection conn; 
    private static PreparedStatement ps; 
    private static ResultSet rs; 
    private static ResultSetMetaData rsmd; // ResultSetMetaData has not the close() method 
    public static void main(String[] rgs){ 
        Derby.loadDriver();// As is said in the class Derby, this action is not necessary with a JRE 1.6 and later. 
        try { 
            // create database "ch20" 
            conn=Derby.createDatabaseAndGetConnection("ch20", "", ""); 
 
            // drop the table "tb_stu" if it exists 
            if(Derby.isTableExists("tb_stu", conn)){ 
                ps=conn.prepareStatement("drop table tb_stu"); 
                ps.execute(); 
            } 
 
            // create a table "tb_stu" in this database 
            ps=conn.prepareStatement("create table tb_stu(id char(5), name varchar(20), sex varchar(10), birthday date)"); 
            ps.execute(); 
 
            // add elements into the table 
            ps=conn.prepareStatement("insert into tb_stu values(?,?,?,?)"); 
            ps.setString(1, "01d05"); 
            ps.setString(2, "Jing LI"); 
            ps.setString(3, "female"); 
            ps.setDate(4, Date.valueOf("1999-10-20")); 
            ps.executeUpdate(); 
            ps.setString(1, "01d06"); 
            ps.setString(2, "Hao LI"); 
            ps.setString(3, "male"); 
            ps.setDate(4, Date.valueOf("1988-10-20")); 
            ps.executeUpdate(); 
            ps.setString(1, "01d15"); 
            ps.setString(2, "Yuanyuan XIONG"); 
            ps.setString(3, "female"); 
            ps.setDate(4, Date.valueOf("2001-10-20")); 
            ps.executeUpdate(); 
            ps.setString(1, "01d16"); 
            ps.setString(2, "Mei LIU"); 
            ps.setString(3, "female"); 
            ps.setDate(4, Date.valueOf("1979-10-20")); 
            ps.executeUpdate(); 
 
            // consult the information of all the students 
            ps=conn.prepareStatement("select * from tb_stu"); 
            rs=ps.executeQuery(); 
 
            // display the query results on the console 
            rsmd=rs.getMetaData(); 
            int numberOfColumns=rsmd.getColumnCount(); 
            String[] columnNames=new String[numberOfColumns]; 
            String[] columnTypeNames=new String[numberOfColumns]; 
            int[] precisions=new int[numberOfColumns]; 
            for(int i=0;i<numberOfColumns;i++){ 
                columnNames[i]=rsmd.getColumnName(i+1); 
                columnTypeNames[i]=rsmd.getColumnTypeName(i+1); 
                precisions[i]=rsmd.getPrecision(i+1); 
                System.out.println(columnNames[i]+" : " 
                        +columnTypeNames[i]+"(" 
                        +precisions[i]+")"); 
            } 
            for(int i=0;i<numberOfColumns;i++){ 
                String columnName=columnNames[i]; 
                if(columnName.length()>precisions[i]){ 
                    columnName=columnName.substring(0, precisions[i]); 
                } 
                System.out.printf("%-"+precisions[i]+"s|",columnName); 
            } 
            System.out.println(); 
            while(rs.next()){ 
                for(int i=0;i<numberOfColumns;i++){ 
                    System.out.printf("%-"+precisions[i]+"s|",rs.getObject(i+1)); 
                } 
                System.out.println(); 
            } 
 
            // delete the students born before "2000-09-01" 
            ps=conn.prepareStatement("delete from tb_stu where birthday<?"); 
            ps.setDate(1, Date.valueOf("2000-09-01")); 
            ps.executeUpdate(); 
 
            // consult the information of all the students after the deletion 
            System.out.println("The information of the rest students after the deletion operation:"); 
            ps=conn.prepareStatement("select * from tb_stu"); 
            rs=ps.executeQuery(); 
 
            // display the query results on the console 
            for(int i=0;i<numberOfColumns;i++){ 
                String columnName=columnNames[i]; 
                if(columnName.length()>precisions[i]){ 
                    columnName=columnName.substring(0, precisions[i]); 
                } 
                System.out.printf("%-"+precisions[i]+"s|",columnName); 
            } 
            System.out.println(); 
            while(rs.next()){ 
                for(int i=0;i<numberOfColumns;i++){ 
                    System.out.printf("%-"+precisions[i]+"s|",rs.getObject(i+1)); 
                } 
                System.out.println(); 
            } 
 
            // close the opened resources 
            rs.close(); 
            ps.close(); 
            conn.close(); 
        } catch (SQLException e) { 
            // TODO Auto-generated catch block 
            e.printStackTrace(); 
        }finally{ 
            // perform a clean database closing 
            Derby.shutdownDatabase("ch20"); 
            Derby.shutdownAll(); 
        }    
    } 

[java]
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; 
    } 

摘自 Gaowen_HAN的專欄

發佈留言