Java tutorial
/* * Microsoft JDBC Driver for SQL Server * * Copyright(c) Microsoft Corporation All rights reserved. * * This program is made available under the terms of the MIT License. See the LICENSE file in the project root for more information. */ package com.microsoft.sqlserver.testframework; import static org.junit.jupiter.api.Assertions.fail; import java.sql.JDBCType; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import java.util.StringJoiner; import java.util.logging.Level; import java.util.logging.Logger; import org.apache.commons.codec.binary.Hex; import com.microsoft.sqlserver.testframework.sqlType.SqlType; import com.microsoft.sqlserver.testframework.sqlType.VariableLengthType; import com.microsoft.sqlserver.testframework.util.RandomUtil; /** * This class holds data for Table. */ public class DBTable extends AbstractSQLGenerator { public static final Logger log = Logger.getLogger("DBTable"); String tableName; String escapedTableName; List<DBColumn> columns; int totalColumns; static int totalRows = 3; // default row count set to 3 DBSchema schema; /** * Initializes {@link DBTable} with tableName, schema, and {@link DBColumns} * * @param autoGenerateSchema * <code>true</code> : generates schema with all available dataTypes in SqlType class */ public DBTable(boolean autoGenerateSchema) { this(autoGenerateSchema, false, false); } /** * Initializes {@link DBTable} with tableName, schema, and {@link DBColumns} * * @param autoGenerateSchema * <code>true</code>: generates schema with all available dataTypes in SqlType class * @param unicode * <code>true</code>: sets unicode column names if autoGenerateSchema is also set to <code>true</code> */ public DBTable(boolean autoGenerateSchema, boolean unicode) { this(autoGenerateSchema, unicode, false); } /** * Initializes {@link DBTable} with tableName, schema, and {@link DBColumns} * * @param autoGenerateSchema * <code>true</code>: generates schema with all available dataTypes in SqlType class * @param unicode * <code>true</code>: sets unicode column names if autoGenerateSchema is also set to <code>true</code> * @param alternateShcema * <code>true</code>: creates table with alternate schema */ public DBTable(boolean autoGenerateSchema, boolean unicode, boolean alternateSchema) { this.tableName = RandomUtil.getIdentifier("table"); this.escapedTableName = escapeIdentifier(tableName); this.schema = new DBSchema(autoGenerateSchema, alternateSchema); if (autoGenerateSchema) { if (unicode) addColumns(unicode); else addColumns(); } else { this.columns = new ArrayList<DBColumn>(); } this.totalColumns = columns.size(); } /** * Similar to {@link DBTable#DBTable(boolean)}, but uses existing list of columns Used internally to clone schema * * @param DBTable */ private DBTable(DBTable sourceTable) { this.tableName = RandomUtil.getIdentifier("table"); this.escapedTableName = escapeIdentifier(tableName); this.columns = sourceTable.columns; this.totalColumns = columns.size(); this.schema = sourceTable.schema; } /** * adds a columns for each SQL type in DBSchema */ private void addColumns() { totalColumns = schema.getNumberOfSqlTypes(); columns = new ArrayList<DBColumn>(totalColumns); for (int i = 0; i < totalColumns; i++) { SqlType sqlType = schema.getSqlType(i); DBColumn column = new DBColumn(RandomUtil.getIdentifier(sqlType.getName()), sqlType); columns.add(column); } } /** * adds a columns for each SQL type in DBSchema */ private void addColumns(boolean unicode) { totalColumns = schema.getNumberOfSqlTypes(); columns = new ArrayList<DBColumn>(totalColumns); for (int i = 0; i < totalColumns; i++) { SqlType sqlType = schema.getSqlType(i); DBColumn column; if (unicode) column = new DBColumn( RandomUtil.getIdentifier(sqlType.getName()) + "?", sqlType); else column = new DBColumn(RandomUtil.getIdentifier(sqlType.getName()), sqlType); columns.add(column); } } /** * gets table name of the {@link DBTable} object * * @return {@link String} table name */ public String getTableName() { return tableName; } public List<DBColumn> getColumns() { return this.columns; } /** * gets escaped table name of the {@link DBTable} object * * @return {@link String} escaped table name */ public String getEscapedTableName() { return escapedTableName; } /** * * @return total rows in the table */ public static int getTotalRows() { return totalRows; } /** * * @param totalRows * set the number of rows in table, default value is 3 */ public void setTotalRows(int totalRows) { this.totalRows = totalRows; } /** * create table * * @param dbstatement */ boolean createTable(DBStatement dbstatement) { try { dropTable(dbstatement); String sql = createTableSql(); return dbstatement.execute(sql); } catch (SQLException ex) { fail(ex.getMessage()); } return false; } String createTableSql() { StringJoiner sb = new StringJoiner(SPACE_CHAR); sb.add(CREATE_TABLE); sb.add(escapedTableName); sb.add(OPEN_BRACKET); for (int i = 0; i < totalColumns; i++) { DBColumn column = getColumn(i); sb.add(escapeIdentifier(column.getColumnName())); sb.add(column.getSqlType().getName()); // add precision and scale if (VariableLengthType.Precision == column.getSqlType().getVariableLengthType()) { sb.add(OPEN_BRACKET); sb.add("" + column.getSqlType().getPrecision()); sb.add(CLOSE_BRACKET); } else if (VariableLengthType.Scale == column.getSqlType().getVariableLengthType()) { sb.add(OPEN_BRACKET); sb.add("" + column.getSqlType().getPrecision()); sb.add(COMMA); sb.add("" + column.getSqlType().getScale()); sb.add(CLOSE_BRACKET); } sb.add(COMMA); } sb.add(CLOSE_BRACKET); return sb.toString(); } /** * populate table with values * * @param dbstatement * @return */ boolean populateTable(DBStatement dbstatement) { try { populateValues(); String sql = populateTableSql(); return dbstatement.execute(sql); } catch (SQLException ex) { fail(ex.getMessage()); } return false; } private void populateValues() { // generate values for all columns for (int i = 0; i < totalColumns; i++) { DBColumn column = getColumn(i); column.populateValues(totalRows); } } public SqlType getSqlType(int columnIndex) { return getColumn(columnIndex).getSqlType(); } public String getColumnName(int columnIndex) { return getColumn(columnIndex).getColumnName(); } public int totalColumns() { return totalColumns; } /** * * @return new DBTable object with same schema */ public DBTable cloneSchema() { DBTable clonedTable = new DBTable(this); return clonedTable; } /** * * @return query to create table */ String populateTableSql() { StringJoiner sb = new StringJoiner(SPACE_CHAR); sb.add("INSERT"); sb.add("INTO"); sb.add(escapedTableName); sb.add("VALUES"); for (int i = 0; i < totalRows; i++) { if (i != 0) { sb.add(COMMA); } sb.add(OPEN_BRACKET); for (int colNum = 0; colNum < totalColumns; colNum++) { // TODO: consider how to enclose data in case of preparedStatemets if (passDataAsString(colNum)) { sb.add("'" + String.valueOf(getColumn(colNum).getRowValue(i)) + "'"); } else if (passDataAsHex(colNum)) { sb.add("0X" + Hex.encodeHexString((byte[]) (getColumn(colNum).getRowValue(i)))); } else { sb.add(String.valueOf(getColumn(colNum).getRowValue(i))); } if (colNum < totalColumns - 1) { sb.add(COMMA); } } sb.add(CLOSE_BRACKET); } return (sb.toString()); } /** * Drop table from Database * * @param dbstatement * @return true if table dropped */ boolean dropTable(DBStatement dbstatement) { boolean result = false; try { String sql = dropTableSql(); result = dbstatement.execute(sql); if (log.isLoggable(Level.FINE)) { log.fine("Table Deleted " + tableName); } else { log.fine("Table did not exist : " + tableName); } } catch (SQLException ex) { fail(ex.getMessage()); } return result; } /** * This will give you query for Drop Table. */ String dropTableSql() { StringJoiner sb = new StringJoiner(SPACE_CHAR); sb.add("IF OBJECT_ID"); sb.add(OPEN_BRACKET); sb.add(wrapName(tableName)); sb.add(","); sb.add(wrapName("U")); sb.add(CLOSE_BRACKET); sb.add("IS NOT NULL"); sb.add("DROP TABLE"); sb.add(escapedTableName); // for drop table no need to wrap. return sb.toString(); } /** * new column to add to DBTable based on the SqlType * * @param sqlType */ public void addColumn(SqlType sqlType) { schema.addSqlTpe(sqlType); DBColumn column = new DBColumn(RandomUtil.getIdentifier(sqlType.getName()), sqlType); columns.add(column); ++totalColumns; } /** * * @param index * @return DBColumn */ DBColumn getColumn(int index) { return columns.get(index); } /** * * @param colIndex * @param rowIndex * @return */ public Object getRowData(int colIndex, int rowIndex) { return columns.get(colIndex).getRowValue(rowIndex); } /** * * @param colNum * @return <code>true</code> if value can be passed as String for the column */ boolean passDataAsString(int colNum) { return (JDBCType.CHAR == getColumn(colNum).getJdbctype() || JDBCType.VARCHAR == getColumn(colNum).getJdbctype() || JDBCType.NCHAR == getColumn(colNum).getJdbctype() || JDBCType.NVARCHAR == getColumn(colNum).getJdbctype() || JDBCType.TIMESTAMP == getColumn(colNum).getJdbctype() || JDBCType.DATE == getColumn(colNum).getJdbctype() || JDBCType.TIME == getColumn(colNum).getJdbctype() || JDBCType.LONGVARCHAR == getColumn(colNum).getJdbctype() || JDBCType.LONGNVARCHAR == getColumn(colNum).getJdbctype()); } /** * * @param colNum * @return <code>true</code> if value can be passed as Hex for the column */ boolean passDataAsHex(int colNum) { return (JDBCType.BINARY == getColumn(colNum).getJdbctype() || JDBCType.VARBINARY == getColumn(colNum).getJdbctype() || JDBCType.LONGVARBINARY == getColumn(colNum).getJdbctype()); } }