CaseAlterTable.java

package org.easyframe.tutorial.lessonc;

import java.sql.SQLException;

import jef.database.DbClient;
import jef.database.DbClientBuilder;
import jef.database.dialect.ColumnType;
import jef.database.meta.TupleMetadata;
import jef.database.query.Func;
import jef.database.support.RDBMS;

import org.easyframe.tutorial.lesson2.entity.Student;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;

public class CaseAlterTable {
	private static DbClient db;

	@BeforeClass
	public static void setup() throws SQLException{
		db = new DbClientBuilder().build();
	}
	
	/**
	 * refreshTable的效果是检查并修改数据库中的表，使其和传入的实体模型保持一致。
	 * 本例中，数据库中没有此表，因此变为建表操作。
	 * @throws SQLException
	 */
	@Test
	public void testCreateTableSimple() throws SQLException{
		db.dropTable(Student.class);
		db.refreshTable(Student.class);
	}
	
	/**
	 * 先用SQL语句直接建立一张类似的表。
	 * 然后通过refresh方法，修改已存在的表。
	 * @throws SQLException
	 */
	@Test
	public void testAlterTableSimple() throws SQLException{
		//准备一张结构不同的表
		db.dropTable(Student.class);
		String sql;
		if(db.getProfile(null).getName()==RDBMS.derby){
			sql="create table STUDENT(\n"+
				    "ID int generated by default as identity  not null,\n"+
				    "GENDER varchar(6),\n"+
				    "NAME varchar(255),\n"+
				    "DATE_OF_BIRTH timestamp,\n"+
				    "constraint PK_STUDENT primary key(ID)\n"+
				")";	
		}else {
			sql="create table STUDENT(\n"+
				    "ID int unsigned not null AUTO_INCREMENT,\n"+
				    "GENDER varchar(6),\n"+
				    "NAME varchar(255),\n"+
				    "DATE_OF_BIRTH timestamp,\n"+
				    "constraint PK_STUDENT primary key(ID)\n"+
				")";
		}
		
		db.executeSql(sql);

		//开始刷新表
		System.out.println("=== Begin refresh table ===");
		db.refreshTable(Student.class);
	}
	
	/**
	 * 传入一个事件监听器，从而可以监测刷新操作的步骤
	 * @see jef.database.support.MetadataEventListener
	 */
	@Test
	public void testAlterTableProgress() throws SQLException{
		//准备一张结构不同的表
		db.dropTable(Student.class);
		String sql;
		if(db.getProfile(null).getName()==RDBMS.derby){
			sql="create table STUDENT(\n"+
				    "ID int generated by default as identity  not null,\n"+
				    "GENDER varchar(6),\n"+
				    "NAME varchar(128),\n"+
				    "REV_NAME varchar(255),\n"+
				    "DATE_OF_BIRTH timestamp,\n"+
				    "constraint PK_STUDENT primary key(ID)\n"+
				")";
		}else {
			sql="create table STUDENT(\n"+
				    "ID int UNSIGNED not null AUTO_INCREMENT,\n"+
				    "GENDER varchar(6),\n"+
				    "NAME varchar(128),\n"+
				    "REV_NAME varchar(255),\n"+
				    "DATE_OF_BIRTH timestamp,\n"+
				    "constraint PK_STUDENT primary key(ID)\n"+
				")";	
		}
		db.executeSql(sql);
		
		System.out.println("=== Begin refresh table ===");
		db.refreshTable(Student.class,new ProgressSample());
	}
	
	/**
	 * 使用代码构造一个动态的表模型，并用这个表模型来维护数据库
	 * @throws SQLException
	 */
	@Test
	public void testAlterTupleTable() throws SQLException{
		TupleMetadata model=new TupleMetadata("MY_TABLE");
		model.addColumn("ID", new ColumnType.AutoIncrement(8));
		model.addColumn("NAME", new ColumnType.Varchar(64));
		model.addColumn("DATA", new ColumnType.Varchar(128));
		model.addColumn("DOB", new ColumnType.TimeStamp().notNull().defaultIs(Func.current_timestamp));
		model.addColumn("MODIFIED", new ColumnType.TimeStamp().notNull());
		model.addColumn("CONTANT", new ColumnType.Clob());
		
		//第一次刷新，创建 MY_TABLE
		db.refreshTable(model, null);
		
		//修改模型字段
		model.removeColumn("DATA");
		model.updateColumn("NAME", new ColumnType.Varchar(128).notNull());
		model.updateColumn("MODIFIED", new ColumnType.TimeStamp());
		model.addColumn("DATA1", new ColumnType.Varchar(64));
		model.addColumn("AGE", new ColumnType.Int(12));
		//第二次刷新，修改MY_TABLE的字段
		db.refreshTable(model, null);
		System.out.println("=== begin drop ===");
		db.dropTable(model);
	}
	
	
	@AfterClass
	public static void close() {
		if (db != null) {
			db.close();
		}
	}
}