hsqlsimpleapp1.java

hsql数据库的JDBC连接程序

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

import java.util.Properties;

public class HsqlSimpleApp1 {
    public String framework = "embedded";
    public String driver = "org.hsqldb.jdbcDriver";
    public String protocol = "jdbc:hsqldb:";

 /*   public static void main(String[] args) {
	String args1="58634410";
	new HsqlSimpleApp1().go(args1);
		
    }
*/	
    void go(String id) {
		
		  String userid=id;
		   System.out.println("------*****选出ID*****----------");
		    System.out.println(userid);
		    System.out.println("------*****选出ID*****----------");
		  System.out.println("HsqlSimpleApp1 starting in " + framework + " mode.");	
          try {
            
            Class.forName(driver).newInstance();
            System.out.println("Loaded the appropriate driver.");
			
            String user          = "sa";
	        String password      = "";
	        String getColumnName = "false";

	        Properties props = new Properties();

			props.put("user", user);
			props.put("password", password);
	        props.put("jdbc.strict_md", "false");
			props.put("jdbc.get_column_name", getColumnName);

		
			Connection conn = DriverManager.getConnection(protocol + "mydb1", props);
            System.out.println("Connected to and created database HsqlDB");
			
            /*
	      				We could also turn autocommit off by putting
	      				;autocommit=false on the URL.
	    				*/
            conn.setAutoCommit(false);
			
            /*
	      			 Creating a statement lets us issue commands against
	      			 the connection.
	    				*/
            Statement s = conn.createStatement();
			
            /*
	      				We create a table and adding a few rows.
	    				*/
            s.execute("CREATE TABLE Stuff(Id varchar(10), FirstName varchar(30), LastName varchar(30), Department varchar(255))");
            System.out.println("Created table Stuff");
            s.execute("INSERT INTO Stuff VALUES('033776618','Yinnon', 'Haviv', 'CS')");
            System.out.println("Inserted ('033776618','Yinnon', 'Haviv', 'CS')");
            s.execute("INSERT INTO Stuff VALUES('032290835','Enav', 'Weinrebe', 'CS')");
            System.out.println("Inserted ('032290835','Enav', 'Weinrebe', 'CS')");
            s.execute("INSERT INTO Stuff VALUES('030776163','Luba', 'Kogan', 'MATH')");
            System.out.println("Inserted ('030776163','Luba', 'Kogan', 'MATH')");
            s.execute("INSERT INTO Stuff VALUES('056084080','Amir', 'Sapir', 'CS')");
            System.out.println("Inserted ('056084080','Amir', 'Sapir', 'CS')");
		    s.execute("INSERT INTO Stuff VALUES('58634410','Lqm', 'Jelver', 'MATH')");
            System.out.println("Inserted ('58634410','Lqm', 'Jelver', 'MATH')");
            
            /* 
            		here is an example of updating rows in the table
            */
            
            s.execute("UPDATE Stuff set LastName='Sapir' where ID='030776163'");
            System.out.println("Changing Luba Kogan -> Luba Sapir");

            /*
            		here we query the database
            		1. getting Names of all the stuff according the LastName,First
            		2. getting ID of all the members of the CS department.
            */

						 	
            System.out.println("------*****选出ID*****----------");
            ResultSet rsAllStuff = s.executeQuery("SELECT *  FROM Stuff  WHERE Id='"+userid+"'  ");    /// '和"之间不能为空哦！！
            while (rsAllStuff.next())
            {
            		System.out.println(rsAllStuff.getString("LastName") + " " + rsAllStuff.getString("FirstName"));			
            }
            System.out.println("------*****选出ID*****----------");
            rsAllStuff.close(); // don't forget close the recordset.
            System.out.println("rsAllStuff RecordSet closed");
            

			
            /*
	    			  We release statement resource.
	    				*/
            s.close();
            System.out.println("Closed statement");
			
            /*
	      				We end the transaction and the connection.
	    				*/
            conn.commit();
            conn.close();
            System.out.println("Committed transaction and closed connection");
			
            boolean gotSQLExc = false;
            if (framework.equals("embedded")) {
                try {
                    DriverManager.getConnection("jdbc:Hsql:;shutdown=true");
                } catch (SQLException se) {
                    gotSQLExc = true;
                }
                if (!gotSQLExc)
                    System.out.println("Database did not shut down normally");
                else
                    System.out.println("Database shut down normally");
	    }
	}
	catch (Throwable e) {
	    System.out.println("exception thrown:");
			
            if (e instanceof SQLException) 
                printSQLError((SQLException)e);
            else
                e.printStackTrace();
        }
		
        System.out.println("HsqlSimpleApp finished");
    }
	
    static void printSQLError(SQLException e) {
        while (e != null) {
            System.out.println(e.toString());
            e = e.getNextException();
        }
    }
		
}