@Override
public String getDocCode(String doName, String account) throws RemoteException {
// TODO Auto-generated method stub
try {
if (doName.equals("出库单") || doName.equals("入库单")) {
sql = "select code from " + doName + " where left(code,5)='" + account + "'";
Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
PreparedStatement preparedStatement = connection.prepareStatement(sql);
ResultSet resultSet = preparedStatement.executeQuery();
if (resultSet.last()) {
int codenum = Integer.parseInt(resultSet.getString(1));
codenum = codenum + 1;
String code = Integer.toString(codenum);
connection.close();
return code;
} else {
connection.close();
return account + "00001";
}
} else {
sql = "select code from b" + doName + " where left(code,5)='" + account + "'";
Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
PreparedStatement preparedStatement = connection.prepareStatement(sql);
ResultSet resultSet = preparedStatement.executeQuery();
if (resultSet.last()) {
int codenum = Integer.parseInt(resultSet.getString(1));
codenum = codenum + 1;
String code = Integer.toString(codenum);
connection.close();
return code;
} else {
sql = "select code from " + doName + " where left(code,5)='" + account + "'";
connection = DriverManager.getConnection(URL, USER, PASSWORD);
preparedStatement = connection.prepareStatement(sql);
resultSet = preparedStatement.executeQuery();
if (resultSet.last()) {
int codenum = Integer.parseInt(resultSet.getString(1));
codenum = codenum + 1;
String code = Integer.toString(codenum);
connection.close();
return code;
} else {
connection.close();
return account + "00001";
}
}
}
} catch (NumberFormatException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
@Test
public void testDropTenantTableOnlyDeletesTenantData() throws Exception {
Connection conn = DriverManager.getConnection(getUrl());
try {
conn.setAutoCommit(true);
conn.createStatement()
.executeUpdate(
"upsert into "
+ PARENT_TABLE_NAME
+ " (tenant_id, id, user) values ('AC/DC', 1, 'Bon Scott')");
conn.createStatement()
.executeUpdate(
"upsert into "
+ PARENT_TABLE_NAME
+ " (tenant_id, id, user) values ('"
+ TENANT_ID
+ "', 1, 'Billy Gibbons')");
conn.close();
conn = DriverManager.getConnection(PHOENIX_JDBC_TENANT_SPECIFIC_URL);
conn.setAutoCommit(true);
conn.createStatement().execute("drop table " + TENANT_TABLE_NAME);
conn.close();
conn = DriverManager.getConnection(getUrl());
ResultSet rs =
conn.createStatement().executeQuery("select count(*) from " + PARENT_TABLE_NAME);
rs.next();
assertEquals(1, rs.getInt(1));
} finally {
conn.close();
}
}
private void paticionDeDatos() throws SQLException {
try {
System.out.println(QInsertaDatabase);
System.out.println(QOption1);
System.out.println(QOption2);
BufferedReader leerEntrada = new BufferedReader(new InputStreamReader(System.in));
String salida = leerEntrada.readLine();
switch (salida) {
case "1":
conexion = DriverManager.getConnection(DBaseServer, "ilsaserver", "platano");
DBSelected = DBaseServer;
break;
case "2":
DBSelected = DBaseLocal;
conexion = DriverManager.getConnection(DBaseLocal, "ilsaserver", "platano");
break;
default:
System.err.println("Error Procesando Seleccion, pruebe de nuevo");
paticionDeDatos();
break;
}
} catch (IOException e) {
System.err.println("Error Procesando Seleccion, pruebe de nuevo");
paticionDeDatos();
}
}
public DBStorage(String url, String dbName, String username, String password)
throws SQLException {
this.username = username;
this.password = password;
this.DBName = dbName;
this.DB_URL = url;
this.DBName = dbName;
try {
Class.forName("org.gjt.mm.mysql.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
Connection tmpConnection = DriverManager.getConnection(DB_URL, username, password);
Statement stmt = tmpConnection.createStatement();
stmt.executeUpdate("CREATE DATABASE IF NOT EXISTS " + dbName);
stmt.close();
tmpConnection.close();
this.connection = DriverManager.getConnection(DB_URL + dbName, username, password);
System.out.println("Database created successfully...");
}
/**
* Returns a connection object to the DB.
*
* @throws ClassNotFoundException
* @throws SQLException
*/
public Connection getConnection() throws ClassNotFoundException, SQLException {
Connection connection;
Class.forName(conf.get(DBConfiguration.DRIVER_CLASS_PROPERTY));
String username = conf.get(DBConfiguration.USERNAME_PROPERTY);
String password = getPassword((JobConf) conf);
String connectString = conf.get(DBConfiguration.URL_PROPERTY);
String connectionParamsStr = conf.get(DBConfiguration.CONNECTION_PARAMS_PROPERTY);
Properties connectionParams = propertiesFromString(connectionParamsStr);
if (connectionParams != null && connectionParams.size() > 0) {
Properties props = new Properties();
if (username != null) {
props.put("user", username);
}
if (password != null) {
props.put("password", password);
}
props.putAll(connectionParams);
connection = DriverManager.getConnection(connectString, props);
} else {
if (username == null) {
connection = DriverManager.getConnection(connectString);
} else {
connection = DriverManager.getConnection(connectString, username, password);
}
}
return connection;
}
@Test
public void testUpsertValuesWithDate() throws Exception {
long ts = nextTimestamp();
Properties props = new Properties();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts));
Connection conn = DriverManager.getConnection(getUrl(), props);
conn.createStatement()
.execute("create table UpsertDateTest (k VARCHAR not null primary key,date DATE)");
conn.close();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 5));
conn = DriverManager.getConnection(getUrl(), props);
conn.createStatement()
.execute("upsert into UpsertDateTest values ('a',to_date('2013-06-08 00:00:00'))");
conn.commit();
conn.close();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
conn = DriverManager.getConnection(getUrl(), props);
ResultSet rs =
conn.createStatement().executeQuery("select k,to_char(date) from UpsertDateTest");
assertTrue(rs.next());
assertEquals("a", rs.getString(1));
assertEquals("2013-06-08 00:00:00", rs.getString(2));
}
public MPJdbcClient(String uri, MPJdbcClientOptions clientOptions, MPJdbcFormatPlugin plugin) {
try {
Class.forName(clientOptions.getDriver()).newInstance();
this.clientOptions = clientOptions;
String user = this.clientOptions.getUser();
String passwd = this.clientOptions.getPassword();
this.plugin = plugin;
this.uri = uri;
if (user == null || user.length() == 0 || passwd.length() == 0) {
logger.info("username, password assumed to be in the uri");
this.conn = DriverManager.getConnection(uri);
} else {
this.conn = DriverManager.getConnection(uri, user, passwd);
}
this.metadata = this.conn.getMetaData();
this.plugName = plugin.getName();
} catch (InstantiationException e) {
// TODO Auto-generated catch block
new DrillRuntimeException(e);
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
new DrillRuntimeException(e);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
new DrillRuntimeException(e);
} catch (SQLException e) {
new DrillRuntimeException(e);
}
}
private static Connection createConnection() {
try {
if (Settings.globals.getBoolean("Database.UseMySQL", false)) {
Class.forName("com.mysql.jdbc.Driver");
Connection ret =
DriverManager.getConnection(
Settings.globals.getString("Database.MySQLConn", ""),
Settings.globals.getString("Database.MySQLUsername", ""),
Settings.globals.getString("Database.MySQLPassword", ""));
ret.setAutoCommit(false);
return ret;
} else {
Class.forName("org.sqlite.JDBC");
Connection ret =
DriverManager.getConnection(
"jdbc:sqlite:plugins"
+ File.separator
+ "MonsterHunt"
+ File.separator
+ "MonsterHunt.sqlite");
ret.setAutoCommit(false);
return ret;
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
return null;
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}
@BeforeTest
public void createConnection() throws Exception {
JdbcStringBasedStoreConfigurationBuilder storeBuilder =
TestCacheManagerFactory.getDefaultCacheConfiguration(false)
.persistence()
.addStore(JdbcStringBasedStoreConfigurationBuilder.class);
UnitTestDatabaseManager.buildTableManipulation(storeBuilder.table(), false);
factoryConfiguration =
UnitTestDatabaseManager.configureUniqueConnectionFactory(storeBuilder).create();
tableManipulation = new TableManipulation(storeBuilder.table().create());
if (factoryConfiguration instanceof SimpleConnectionFactoryConfiguration) {
SimpleConnectionFactoryConfiguration simpleConfiguration =
(SimpleConnectionFactoryConfiguration) factoryConfiguration;
connection =
DriverManager.getConnection(
simpleConfiguration.connectionUrl(),
simpleConfiguration.username(),
simpleConfiguration.password());
} else if (factoryConfiguration instanceof PooledConnectionFactoryConfiguration) {
PooledConnectionFactoryConfiguration pooledConfiguration =
(PooledConnectionFactoryConfiguration) factoryConfiguration;
connection =
DriverManager.getConnection(
pooledConfiguration.connectionUrl(),
pooledConfiguration.username(),
pooledConfiguration.password());
}
tableManipulation.setCacheName("aName");
}
private Connection getConnection() {
Connection conn = null;
Properties connProp = new Properties();
connProp.put("user", this.username);
connProp.put("password", this.password);
if (this.dbms.equals("mysql")) {
try {
conn =
DriverManager.getConnection(
"jdbc:" + this.dbms + "://" + this.hostname + ":" + this.port + "/", connProp);
} catch (SQLException e) {
System.out.println("Invalid connection");
}
} else if (this.dbms.equals("derby")) {
try {
conn =
DriverManager.getConnection(
"jdbc:" + this.dbms + ":" + this.database + ";create=true", connProp);
} catch (SQLException e) {
System.out.println("Invalid connection");
}
}
return conn;
}
private void reconnect() throws SQLException {
if (driverFailed) {
return;
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
log.log(Level.WARNING, e.getMessage(), e);
}
}
logInsert = null;
logInsertAttribute = null;
connection =
username != null && !username.trim().isEmpty()
? DriverManager.getConnection(
url, username.trim(), password != null ? password.trim() : null)
: DriverManager.getConnection(url);
logInsert = connection.prepareStatement(logInsertQuery, Statement.RETURN_GENERATED_KEYS);
if (logInsertAttributeQuery != null && !logInsertAttributeQuery.trim().equals("")) {
logInsertAttribute = connection.prepareStatement(logInsertAttributeQuery);
}
// Disable autocommit
connection.setAutoCommit(false);
}
private void testIndexNotRebuilt() throws SQLException {
if (config.memory) {
return;
}
deleteDb("databaseEventListener");
String url = getURL("databaseEventListener", true);
String user = getUser(), password = getPassword();
Properties p = new Properties();
p.setProperty("user", user);
p.setProperty("password", password);
Connection conn = DriverManager.getConnection(url, p);
Statement stat = conn.createStatement();
// the old.id index head is at position 0
stat.execute("create table old(id identity) as select 1");
// the test.id index head is at position 1
stat.execute("create table test(id identity) as select 1");
conn.close();
conn = DriverManager.getConnection(url, p);
stat = conn.createStatement();
// free up space at position 0
stat.execute("drop table old");
// truncate, relocating to position 0
stat.execute("truncate table test");
stat.execute("insert into test select 1");
conn.close();
calledCreateIndex = false;
p.put("DATABASE_EVENT_LISTENER", getClass().getName());
conn = org.h2.Driver.load().connect(url, p);
conn.close();
assertTrue(!calledCreateIndex);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
try {
PrintWriter pw = response.getWriter();
pw.write(" <html> <body>");
Class.forName("com.mysql.jdbc.Driver");
Connection con =
DriverManager.getConnection("jdbc:mysql://localhost:3307/project", "root", "root");
Connection con2 =
DriverManager.getConnection("jdbc:mysql://localhost:3307/project", "root", "root");
Statement stm = con.createStatement();
Statement stm2 = con2.createStatement();
ResultSet rs;
ResultSet rs2;
rs = stm.executeQuery("select * from rc");
while (rs.next()) {
if (rs.getString(3) != null) {
pw.write("<br><br>Username:"******"</body> </html>");
} catch (Exception e) {
}
}
@Test
public void shouldDumpFromRemoteMySql() throws SQLException, IOException, InterruptedException {
MySqlConnectionInfo connInfo = MySqlConnectionInfo.builder().build();
String tempDbName =
new StringBuilder()
.append("tmp_")
.append(UUID.randomUUID().toString().replaceAll("-", ""))
.toString();
String mysqlUrl = connInfo.getJdbcUrl();
String user = connInfo.getUser();
String pass = connInfo.getPass();
try (Connection connection = DriverManager.getConnection(mysqlUrl, user, pass)) {
try (Statement stmt = connection.createStatement()) {
stmt.executeUpdate("CREATE DATABASE " + tempDbName);
}
try (Statement stmt = connection.createStatement()) {
stmt.executeUpdate("USE " + tempDbName + "; " + SQL_FOR_TEST);
}
schemaDumper.dumpFromRemoteDb(tempDbName, connInfo);
} catch (Exception e) {
throw e;
} finally {
try (Connection connectionToTeardown = DriverManager.getConnection(mysqlUrl, user, pass)) {
try (Statement stmt = connectionToTeardown.createStatement()) {
stmt.executeUpdate("DROP DATABASE " + tempDbName);
}
} catch (CommunicationsException e) {
assumeTrue("MySQL maybe not launched", false);
}
}
assertTrue(true);
}
@Test
public void testAllowDropParentTableWithCascadeAndSingleTenantTable() throws Exception {
long ts = nextTimestamp();
Properties props = new Properties();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts));
Connection conn = DriverManager.getConnection(getUrl(), props);
Connection connTenant = null;
try {
// Drop Parent Table
conn.createStatement().executeUpdate("DROP TABLE " + PARENT_TABLE_NAME + " CASCADE");
conn.close();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
connTenant = DriverManager.getConnection(PHOENIX_JDBC_TENANT_SPECIFIC_URL, props);
validateTenantViewIsDropped(conn);
} finally {
if (conn != null) {
conn.close();
}
if (connTenant != null) {
connTenant.close();
}
}
}
@Test
public void testUpsertDateValues() throws Exception {
long ts = nextTimestamp();
Date now = new Date(System.currentTimeMillis());
ensureTableCreated(getUrl(), TestUtil.PTSDB_NAME, null, ts - 2);
Properties props = new Properties();
props.setProperty(
PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1)); // Execute at timestamp 1
Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
String dateString = "1999-01-01 02:00:00";
PreparedStatement upsertStmt =
conn.prepareStatement(
"upsert into ptsdb(inst,host,date) values('aaa','bbb',to_date('" + dateString + "'))");
int rowsInserted = upsertStmt.executeUpdate();
assertEquals(1, rowsInserted);
upsertStmt =
conn.prepareStatement(
"upsert into ptsdb(inst,host,date) values('ccc','ddd',current_date())");
rowsInserted = upsertStmt.executeUpdate();
assertEquals(1, rowsInserted);
conn.commit();
props.setProperty(
PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 1
conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
String select = "SELECT date,current_date() FROM ptsdb";
ResultSet rs = conn.createStatement().executeQuery(select);
Date then = new Date(System.currentTimeMillis());
assertTrue(rs.next());
Date date = DateUtil.parseDate(dateString);
assertEquals(date, rs.getDate(1));
assertTrue(rs.next());
assertTrue(rs.getDate(1).after(now) && rs.getDate(1).before(then));
assertFalse(rs.next());
}
/**
* * Test SSL client connection to SSL server
*
* @throws Exception
*/
@Test
public void testSSLConnectionWithProperty() throws Exception {
setSslConfOverlay(confOverlay);
// Test in binary mode
setBinaryConfOverlay(confOverlay);
// Start HS2 with SSL
miniHS2.start(confOverlay);
System.setProperty(JAVA_TRUST_STORE_PROP, dataFileDir + File.separator + TRUST_STORE_NAME);
System.setProperty(JAVA_TRUST_STORE_PASS_PROP, KEY_STORE_PASSWORD);
// make SSL connection
hs2Conn =
DriverManager.getConnection(
miniHS2.getJdbcURL() + ";ssl=true", System.getProperty("user.name"), "bar");
hs2Conn.close();
miniHS2.stop();
// Test in http mode
setHttpConfOverlay(confOverlay);
miniHS2.start(confOverlay);
// make SSL connection
hs2Conn =
DriverManager.getConnection(
miniHS2.getJdbcURL("default", SSL_CONN_PARAMS), System.getProperty("user.name"), "bar");
hs2Conn.close();
}
@Test
public void testCreateMultiTenantTable() throws Exception {
long ts = nextTimestamp();
Properties props = new Properties();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts));
Connection conn = DriverManager.getConnection(getUrl(), props);
String ddl =
"CREATE TABLE m_multi_tenant_test( TenantId UNSIGNED_INT NOT NULL ,\n"
+ " Id UNSIGNED_INT NOT NULL ,\n"
+ " val VARCHAR ,\n"
+ " CONSTRAINT pk PRIMARY KEY(TenantId, Id) \n"
+ " ) MULTI_TENANT=true";
conn.createStatement().execute(ddl);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
conn = DriverManager.getConnection(getUrl(), props);
try {
conn.createStatement().execute(ddl);
fail();
} catch (TableAlreadyExistsException e) {
// expected
}
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 20));
conn = DriverManager.getConnection(getUrl(), props);
conn.createStatement().execute("DROP TABLE m_multi_tenant_test");
}
public static void CloseDbConnection(Connection conn) {
try { // shut down the database
conn.close();
System.out.println("Closed connection");
/* In embedded mode, an application should shut down Derby.
Shutdown throws the XJ015 exception to confirm success. */
boolean gotSQLExc = false;
try {
DriverManager.getConnection("jdbc:derby:;shutdown=true");
DriverManager.getConnection("exit");
} catch (SQLException se) {
if (se.getSQLState().equals("XJ015")) {
gotSQLExc = true;
}
}
if (!gotSQLExc) {
System.out.println("Database did not shut down normally");
} else {
System.out.println("Database shut down normally");
}
// force garbage collection to unload the EmbeddedDriver
// so Derby can be restarted
System.gc();
} catch (Throwable e) {
System.out.println(e);
;
System.exit(1);
}
}
@Test
public void testUpsertValuesWithExpression() throws Exception {
long ts = nextTimestamp();
ensureTableCreated(getUrl(), "IntKeyTest", null, ts - 2);
Properties props = new Properties();
props.setProperty(
PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1)); // Execute at timestamp 1
Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
String upsert = "UPSERT INTO IntKeyTest VALUES(-1)";
PreparedStatement upsertStmt = conn.prepareStatement(upsert);
int rowsInserted = upsertStmt.executeUpdate();
assertEquals(1, rowsInserted);
upsert = "UPSERT INTO IntKeyTest VALUES(1+2)";
upsertStmt = conn.prepareStatement(upsert);
rowsInserted = upsertStmt.executeUpdate();
assertEquals(1, rowsInserted);
conn.commit();
conn.close();
props.setProperty(
PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 1
conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
String select = "SELECT i FROM IntKeyTest";
ResultSet rs = conn.createStatement().executeQuery(select);
assertTrue(rs.next());
assertEquals(-1, rs.getInt(1));
assertTrue(rs.next());
assertEquals(3, rs.getInt(1));
assertFalse(rs.next());
}
public Connection openConnect() {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
// load the oracle driver...needs to be in classes folder in jre folder
} catch (ClassNotFoundException e) {
System.out.println(" Can't find class oracle.jdbc.driver.OracleDriver");
System.exit(1);
}
try {
conn =
DriverManager.getConnection(
"jdbc:oracle:thin:@studentoracle.students.ittralee.ie:1521/orcl",
"t00171168",
"p9udna7n");
return conn;
} catch (Exception w) {
try {
conn =
DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521/orcl.168.5.67", "hr", "hr");
return conn;
} catch (SQLException ex) {
JOptionPane.showMessageDialog(null, "Unable to connect to database");
}
}
return conn;
}
/** Test on kinds of database names. */
public void testDBName() throws SQLException {
// Do we get a non-internal error when we try to create
// over an existing directory? (T#674)
String url = "jdbc:splice:wombat/seg0;create=true";
try {
DriverManager.getConnection(url);
fail("Error XBM0J is expected");
} catch (SQLException e) {
assertEquals("XJ041", e.getSQLState());
}
// -- check to ensure an empty database name is taken
// -- as the name, over any connection attribute.
// -- this should fail.
url = "jdbc:splice: ;databaseName=wombat";
try {
DriverManager.getConnection(url);
fail("Error XJ004 is expected");
} catch (SQLException e) {
assertEquals("XJ004", e.getSQLState());
}
// and this should succeed (no database name in URL)
url = "jdbc:splice:;databaseName=wombat";
Connection con = DriverManager.getConnection(url);
con.close();
}
private static void testConnectWithHash() throws SQLException {
Connection conn = DriverManager.getConnection("jdbc:h2:mem:test", "sa", "sa");
String pwd = StringUtils.convertBytesToHex(SHA256.getKeyPasswordHash("SA", "sa".toCharArray()));
Connection conn2 =
DriverManager.getConnection("jdbc:h2:mem:test;PASSWORD_HASH=TRUE", "sa", pwd);
conn.close();
conn2.close();
}
private void establishConnection() throws SQLException, ClassNotFoundException {
Class.forName(driverName);
if (username == null) {
dbConn = DriverManager.getConnection(dbURL);
} else {
dbConn = DriverManager.getConnection(dbURL, username, password);
}
}
private void initDatabase() throws Exception {
Properties connectionProps = new Properties();
connectionProps.put("user", database_user);
connectionProps.put("password", database_password);
if (database_type.equals("mysql")) {
connectionProps.put("rewriteBatchedStatements", "true");
}
String connectionStr = ""; // construct DB connection URL
Connection conn = null;
if (database_type.equals("impala")) {
connectionStr =
"jdbc:" + "hive2://" + database_hostname + ":" + database_port + "/;auth=noSasl";
conn = DriverManager.getConnection(connectionStr, connectionProps);
tryStatement(conn, "INVALIDATE METADATA"); // refreshes impala's metadata for tables/db's
} else {
connectionStr =
"jdbc:"
+ database_type
+ "://"
+ database_hostname
+ ":"
+ database_port
+ "/"
+ database_name;
conn = DriverManager.getConnection(connectionStr, connectionProps);
}
boolean dbExists = false;
ResultSet rs = null;
try {
// Iterate our set of catalogs (i.e., databases)
rs =
(database_type.equals("hive2") || database_type.equals("impala"))
? conn.getMetaData().getSchemas()
: conn.getMetaData().getCatalogs();
while (rs.next()) {
if (rs.getString(1).equals(database_name)) {
dbExists = true;
break;
}
}
} finally {
try {
if (rs != null) {
rs.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
if (!dbExists) {
tryStatement(conn, "CREATE DATABASE " + database_name);
conn.close();
}
}
/**
* Specifically, this opens a mem-only DB, populates it, starts a HyperSQL Server to server it,
* and opens network JDBC Connection "netConn" to it,
*
* <p>Invoked before each test*() invocation by JUnit.
*/
protected void setUp() {
try {
Connection setupConn = DriverManager.getConnection("jdbc:hsqldb:mem:test", "SA", "");
setupConn.setAutoCommit(false);
Statement st = setupConn.createStatement();
st.executeUpdate("SET PASSWORD 'sapwd'");
populate(st);
st.close();
setupConn.commit();
setupConn.close();
} catch (SQLException se) {
throw new RuntimeException("Failed to set up in-memory database", se);
}
try {
server = new Server();
HsqlProperties properties = new HsqlProperties();
if (System.getProperty("VERBOSE") == null) {
server.setLogWriter(null);
server.setErrWriter(null);
} else {
properties.setProperty("server.silent", "false");
properties.setProperty("server.trace", "true");
}
properties.setProperty("server.database.0", "mem:test");
properties.setProperty("server.dbname.0", "");
properties.setProperty("server.port", AbstractTestOdbc.portString);
server.setProperties(properties);
server.start();
try {
Thread.sleep(1000);
} catch (InterruptedException ie) {
}
} catch (Exception e) {
throw new RuntimeException("Failed to set up in-memory database", e);
}
if (server.getState() != ServerConstants.SERVER_STATE_ONLINE) {
throw new RuntimeException("Server failed to start up");
}
try {
netConn = DriverManager.getConnection("jdbc:odbc:" + dsnName, "SA", "sapwd");
// netConn.setAutoCommit(false);
} catch (SQLException se) {
if (se.getMessage().indexOf("No suitable driver") > -1) {
throw new RuntimeException(
"You must install the native library for Sun's jdbc:odbc " + "JDBC driver");
}
if (se.getMessage().indexOf("Data source name not found") > -1) {
throw new RuntimeException(
"You must configure ODBC DSN '"
+ dsnName
+ "' (you may change the name and/or port by setting Java "
+ "system properties 'test.hsqlodbc.port' or "
+ "'test.hsqlodbc.dsnname'");
}
throw new RuntimeException("Failed to set up JDBC/ODBC network connection", se);
}
}
private void init() throws Exception {
Class.forName("oracle.jdbc.OracleDriver");
Class.forName("com.mysql.jdbc.Driver");
connSrc =
DriverManager.getConnection(
"jdbc:oracle:thin:@192.168.100.231:1522:mpptest", "gmcc", "skywin");
connDes =
DriverManager.getConnection("jdbc:mysql://192.168.1.100:3306/repay", "root", "123456");
}
/**
* Called when ran from command line.
*
* @param args ignored
*/
public static void main(String... args) throws Exception {
DeleteDbFiles.execute("~", "test", true);
Class.forName("org.h2.Driver");
Connection conn = DriverManager.getConnection("jdbc:h2:~/test");
Statement stat = conn.createStatement();
stat.execute(
"create table test_data(id int, user varchar, data varchar, primary key(id, user))");
stat.execute("create index on test_data(id, user)");
stat.execute("create view test as select id, data from test_data where user = user()");
stat.execute(
"create trigger t_test instead of "
+ "insert, update, delete on test for each row "
+ "call \""
+ RowAccessRights.class.getName()
+ "\"");
stat.execute("create user a password 'a'");
stat.execute("create user b password 'b'");
stat.execute("grant all on test to a");
stat.execute("grant all on test to b");
ResultSet rs;
Connection connA = DriverManager.getConnection("jdbc:h2:~/test", "a", "a");
Statement statA = connA.createStatement();
statA.execute("insert into test values(1, 'Hello'), (2, 'World')");
statA.execute("update test set data = 'Hello!' where id = 1");
statA.execute("delete from test where id = 2");
Connection connB = DriverManager.getConnection("jdbc:h2:~/test", "b", "b");
Statement statB = connB.createStatement();
statB.execute("insert into test values(1, 'Hallo'), (2, 'Welt')");
statB.execute("update test set data = 'Hallo!' where id = 1");
statB.execute("delete from test where id = 2");
rs = statA.executeQuery("select * from test");
while (rs.next()) {
System.out.println("a: " + rs.getInt(1) + "/" + rs.getString(2));
}
rs = statB.executeQuery("select * from test");
while (rs.next()) {
System.out.println("b: " + rs.getInt(1) + "/" + rs.getString(2));
}
connA.close();
connB.close();
rs = stat.executeQuery("select * from test_data");
while (rs.next()) {
System.out.println(rs.getInt(1) + "/" + rs.getString(2) + "/" + rs.getString(3));
}
conn.close();
}
@Override
public void tableChanged(TableModelEvent e) {
/*
* isChangeTable - возмодность обновление данных
* Необходим, чтобы избежать некорректного запроса к таблице,
* во время её обновления методом refreshCostOil()
*/
if (isChangeTable) {
if (e.getColumn() > 0) {
String id = jDataTable.getModel().getValueAt(e.getFirstRow(), 0).toString();
String b80 = jDataTable.getModel().getValueAt(e.getFirstRow(), 2).toString();
String b92 = jDataTable.getModel().getValueAt(e.getFirstRow(), 3).toString();
String b95 = jDataTable.getModel().getValueAt(e.getFirstRow(), 4).toString();
String bdis = jDataTable.getModel().getValueAt(e.getFirstRow(), 5).toString();
try {
SimpleDateFormat formatter = new SimpleDateFormat("dd.MM.yyyy");
Date date =
formatter.parse(jDataTable.getModel().getValueAt(e.getFirstRow(), 1).toString());
String changedate = Long.toString(date.getTime());
DriverManager.getConnection("jdbc:sqlite:" + Oil.PATH)
.createStatement()
.executeUpdate("UPDATE change SET b80 = '" + b80 + "' WHERE id LIKE '" + id + "';");
DriverManager.getConnection("jdbc:sqlite:" + Oil.PATH)
.createStatement()
.executeUpdate("UPDATE change SET b92 = '" + b92 + "' WHERE id LIKE '" + id + "';");
DriverManager.getConnection("jdbc:sqlite:" + Oil.PATH)
.createStatement()
.executeUpdate("UPDATE change SET b95 = '" + b95 + "' WHERE id LIKE '" + id + "';");
DriverManager.getConnection("jdbc:sqlite:" + Oil.PATH)
.createStatement()
.executeUpdate(
"UPDATE change SET bdis = '" + bdis + "' WHERE id LIKE '" + id + "';");
DriverManager.getConnection("jdbc:sqlite:" + Oil.PATH)
.createStatement()
.executeUpdate(
"UPDATE change SET changedate = '"
+ changedate
+ "' WHERE id LIKE '"
+ id
+ "';");
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
} catch (ParseException e1) {
JOptionPane.showMessageDialog(null, "Ошибка в дате, обновление отменено!");
}
}
}
}
@Test
public void testAlteringMultiTenancyForTableWithViewsNotAllowed() throws Exception {
Properties props = new Properties();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(nextTimestamp()));
String multiTenantTable = "BASE_MULTI_TENANT_SWITCH";
String globalTable = "GLOBAL_TABLE_SWITCH";
// create the two base tables
try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
String ddl =
"CREATE TABLE "
+ multiTenantTable
+ " (TENANT_ID VARCHAR NOT NULL, PK1 VARCHAR NOT NULL, V1 VARCHAR, V2 VARCHAR, V3 VARCHAR CONSTRAINT NAME_PK PRIMARY KEY(TENANT_ID, PK1)) MULTI_TENANT = true ";
conn.createStatement().execute(ddl);
ddl =
"CREATE TABLE "
+ globalTable
+ " (TENANT_ID VARCHAR NOT NULL, PK1 VARCHAR NOT NULL, V1 VARCHAR, V2 VARCHAR, V3 VARCHAR CONSTRAINT NAME_PK PRIMARY KEY(TENANT_ID, PK1)) ";
conn.createStatement().execute(ddl);
}
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(nextTimestamp()));
props.setProperty(PhoenixRuntime.TENANT_ID_ATTRIB, "tenant1");
// create view on multi-tenant table
try (Connection tenantConn = DriverManager.getConnection(getUrl(), props)) {
String viewName = "tenantview";
String viewDDL = "CREATE VIEW " + viewName + " AS SELECT * FROM " + multiTenantTable;
tenantConn.createStatement().execute(viewDDL);
}
props = new Properties();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(nextTimestamp()));
// create view on global table
try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
String viewName = "globalView";
conn.createStatement()
.execute("CREATE VIEW " + viewName + " AS SELECT * FROM " + globalTable);
}
props = new Properties();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(nextTimestamp()));
try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
try {
conn.createStatement()
.execute("ALTER TABLE " + globalTable + " SET MULTI_TENANT = " + true);
fail();
} catch (SQLException e) {
assertEquals(SQLExceptionCode.CANNOT_MUTATE_TABLE.getErrorCode(), e.getErrorCode());
}
try {
conn.createStatement()
.execute("ALTER TABLE " + multiTenantTable + " SET MULTI_TENANT = " + false);
fail();
} catch (SQLException e) {
assertEquals(SQLExceptionCode.CANNOT_MUTATE_TABLE.getErrorCode(), e.getErrorCode());
}
}
}
