Java DB (Derby) 的使用(2)
来源:互联网 发布:lofter 域名 查ip 编辑:程序博客网 时间:2024/06/03 05:40
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 blocke.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 stubDerby.loadDriver();try {con=Derby.getConnection("dbName1","","");con.setAutoCommit(false);// for that later con.commit(); is validDatabaseMetaData meta=con.getMetaData();RowIdLifetime rowIdLifetime=meta.getRowIdLifetime();System.out.println(rowIdLifetime);// To see if the Derby DBMS supports ROWID or notDerby.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 ResultSetMetaDataResultSetMetaData rsmd=rs.getMetaData();// ResultSetMetaData has not the close() methodint numberOfColumns=rsmd.getColumnCount();System.out.println(rsmd.isSearchable(1));System.out.println(numberOfColumns);System.out.println(rsmd.getTableName(3));// get the designated table namefor(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 blocke.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 driverif(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 blocke.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 databaseProperties 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 blockSystem.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 blockSystem.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 blockSystem.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 blockSystem.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;}}
- Java DB (Derby) 的使用(2)
- Java DB (Derby) 的使用(1)
- Java DB (Derby) 的使用(3)
- Java 1.6 DB derby 设置使用心得
- Java 6 中自带的 Java DB(derby) 数据库
- Java 6 中自带的 Java DB(derby) 数据库
- Derby(Java DB)简介 ZZ
- 创建并连接Java DB(derby)数据库
- Java使用Derby数据库
- (原创)真正的Derby新手教程,Derby安装,创建数据库,在Java程序中使用Derby
- Derby的下载安装和使用,(和JAVA中使用Derby)
- derby+squirrel的使用
- derby的使用
- Java 内嵌的Derby数据库基本使用
- 使用Derby数据库的注意事项
- Derby的安装与使用
- Derby的安装与使用
- Derby的安装与使用
- 毕设的问题与解决(四)
- ASP.NET 4.0中安装在IIS6中最常遇到的四个的问题
- Deploying TinyOS on Ubuntu 10.10
- Greedy——HDOJ 1050
- TinyOS安装问题
- Java DB (Derby) 的使用(2)
- 网络蜘蛛-搜狐博客批量下载(之二)
- extjs DomQuery
- 动态SQL查询计划的几个问题
- 查看数据库的连接数
- 如何在MFC的SDI程序中实现多视图
- Android_TextView属性XML详解
- Asp.net中使用母版页
- @import调用css与link调用css的区别