java.sql.DriverManager Java Examples

public static Connection connect(DBInstanceInfo db, String username, String password, MyPerfContext context)
 throws java.sql.SQLException
 {
java.util.Properties info = new java.util.Properties();
info.put ("user", username);
info.put ("password",password);
if("oracle".equalsIgnoreCase(db.getDbType()))
{
  info.put("oracle.net.CONNECT_TIMEOUT", String.valueOf(context.getConnectionTimeout()));
  info.put("oracle.net.READ_TIMEOUT", String.valueOf(context.getConnectionReadTimeout()));
  info.put("oracle.jdbc.ReadTimeout", String.valueOf(context.getConnectionReadTimeout()));
}
else if("mysql".equalsIgnoreCase(db.getDbType()))		
{
  info.put("connectTimeout", String.valueOf(context.getConnectionTimeout()));
  info.put("socketTimeout", String.valueOf(context.getConnectionReadTimeout()));
}
return DriverManager.getConnection(db.getConnectionString(), info);
 }
 

@Ignore("Fix error checking")
@Test
@SuppressWarnings({"try"})
public void testErrorPropagation() throws Exception {
  //Test error propagation
  Properties props = new Properties();
  props.put("aggregationMode", "facet");
  try (Connection con = DriverManager.getConnection("jdbc:solr://" + zkHost + "?collection=" + COLLECTIONORALIAS, props)) {
    try (Statement stmt = con.createStatement()) {
      try (ResultSet rs = stmt.executeQuery("select crap from " + COLLECTIONORALIAS + " group by a_s " +
          "order by sum(a_f) desc")) {
      } catch (Exception e) {
        String errorMessage = e.getMessage();
        assertTrue(errorMessage.contains("Group by queries must include at least one aggregate function"));
      }
    }
  }
}
 

@Test
public void testNotEquals2() throws Exception {
    String query = "SELECT entity_id FROM // one more comment  \n" +
    "aTable WHERE organization_id=? and not a_integer = 1 and a_integer <= 2";
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
    Connection conn = DriverManager.getConnection(getUrl(), props);
    try {
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, tenantId);
        ResultSet rs = statement.executeQuery();
        assertTrue (rs.next());
        assertEquals(rs.getString(1), ROW2);
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 

public static Connection getReadCommittedConnection() throws SQLException {
  
  Connection conn = null;
  if (SQLTest.hasJSON) {
    Properties info = new Properties();
    info.setProperty("sync-commits", "true");
    conn = DriverManager.getConnection(protocol + dbName, info);
  }
  else {
    conn = DriverManager.getConnection(protocol + dbName);
  }
  if (SQLTest.random.nextInt(20) == 1) {
  	//test auto upgrade to read committed
  	conn.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
  } else {
    conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
  }
  conn.setAutoCommit(false); 
  return conn;
}
 

@Test
public void testOrPKWithAndPKAndNotPK() throws SQLException {
    String query = "select * from bugTable where ID = 'i1' or (ID = 'i2' and company = 'c3')";
    PhoenixConnection pconn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class);
    pconn.createStatement().execute("create table bugTable(ID varchar primary key,company varchar)");
    PhoenixPreparedStatement pstmt = newPreparedStatement(pconn, query);
    QueryPlan plan = pstmt.optimizeQuery();
    Scan scan = plan.getContext().getScan();
    Filter filter = scan.getFilter();
    Expression idExpression = new ColumnRef(plan.getTableRef(), plan.getTableRef().getTable().getColumnForColumnName("ID").getPosition()).newColumnExpression();
    Expression id = new RowKeyColumnExpression(idExpression,new RowKeyValueAccessor(plan.getTableRef().getTable().getPKColumns(),0));
    Expression company = new KeyValueColumnExpression(plan.getTableRef().getTable().getColumnForColumnName("COMPANY"));
    // FilterList has no equals implementation
    assertTrue(filter instanceof FilterList);
    FilterList filterList = (FilterList)filter;
    assertEquals(FilterList.Operator.MUST_PASS_ALL, filterList.getOperator());
    assertEquals(
        Arrays.asList(
            new SkipScanFilter(
                ImmutableList.of(Arrays.asList(
                    pointRange("i1"),
                    pointRange("i2"))),
                SchemaUtil.VAR_BINARY_SCHEMA),
            singleKVFilter(
                    or(constantComparison(CompareOp.EQUAL,id,"i1"),
                       and(constantComparison(CompareOp.EQUAL,id,"i2"),
                           constantComparison(CompareOp.EQUAL,company,"c3"))))),
        filterList.getFilters());
}
 

@ExpectWarning("ODR_OPEN_DATABASE_RESOURCE")
public void notReported(String url, String username, String password) throws Exception {
    for (int i = 0; i < 10; i++) {
        if (i > 5) {
            Connection connection = DriverManager.getConnection(url, username, password);
            PreparedStatement pstmt = connection.prepareStatement("SELECT count(1) from tab");
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getString(1));
            }
        }
    }
}
 

private Connection createConnection(Map<String, String> additionalProperties)
        throws SQLException
{
    String url = format("jdbc:presto://localhost:%s", server.getHttpsAddress().getPort());
    Properties properties = new Properties();
    properties.setProperty("user", "test");
    properties.setProperty("SSL", "true");
    properties.setProperty("SSLTrustStorePath", getResource("localhost.truststore").getPath());
    properties.setProperty("SSLTrustStorePassword", "changeit");
    properties.putAll(additionalProperties);
    return DriverManager.getConnection(url, properties);
}
 

Connection insertRowsInTable(String tableName, long numRows) throws SQLException {
    String dml = "UPSERT INTO " + tableName + " VALUES (?, ?)";
    Connection conn = DriverManager.getConnection(getUrl());
    PreparedStatement stmt = conn.prepareStatement(dml);
    for (int i = 1; i <= numRows; i++) {
        stmt.setString(1, "key" + i);
        stmt.setString(2, "value" + i);
        stmt.executeUpdate();
    }
    conn.commit();
    return conn;
}
 

@Test
public void testSingleQueryWithAggregator() throws Exception {
    Properties props = PropertiesUtil.deepCopy(TestUtil.TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    try {
        prepareTableWithValues(conn, 100);
        String query = "SELECT count(i1) FROM " + tableName +" tablesample (22) where i2>=3000 or i1<2 ";
        ResultSet rs = conn.createStatement().executeQuery(query);
        
        assertTrue(rs.next());
        assertEquals(14, rs.getInt(1));
    } finally {
        conn.close();
    }
}
 

@Override
public void open(Map<String, Object> config, SinkContext sinkContext) throws Exception {
    jdbcSinkConfig = JdbcSinkConfig.load(config);

    jdbcUrl = jdbcSinkConfig.getJdbcUrl();
    if (jdbcSinkConfig.getJdbcUrl() == null) {
        throw new IllegalArgumentException("Required jdbc Url not set.");
    }

    Properties properties = new Properties();
    String username = jdbcSinkConfig.getUserName();
    String password = jdbcSinkConfig.getPassword();
    if (username != null) {
        properties.setProperty("user", username);
    }
    if (password != null) {
        properties.setProperty("password", password);
    }


    Class.forName(JdbcUtils.getDriverClassName(jdbcSinkConfig.getJdbcUrl()));
    connection = DriverManager.getConnection(jdbcSinkConfig.getJdbcUrl(), properties);
    connection.setAutoCommit(false);
    log.info("Opened jdbc connection: {}, autoCommit: {}", jdbcUrl, connection.getAutoCommit());

    tableName = jdbcSinkConfig.getTableName();
    tableId = JdbcUtils.getTableId(connection, tableName);
    // Init PreparedStatement include insert, delete, update
    initStatement();

    int timeoutMs = jdbcSinkConfig.getTimeoutMs();
    batchSize = jdbcSinkConfig.getBatchSize();
    incomingList = Lists.newArrayList();
    swapList = Lists.newArrayList();
    isFlushing = new AtomicBoolean(false);

    flushExecutor = Executors.newScheduledThreadPool(1);
    flushExecutor.scheduleAtFixedRate(this::flush, timeoutMs, timeoutMs, TimeUnit.MILLISECONDS);
}
 

private Connection createDatabase() throws IOException, SQLException {
	File dir = new File("target/db");
	FileUtils.deleteDirectory(dir);
	dir.mkdir();
	String script = IOUtils.toString(
			DatabaseTest.class.getResourceAsStream("/create.sql"), "UTF-8");
	String[] commands = script.split(";");
	String url = "jdbc:h2:file:target/db/test";
	Connection con = DriverManager.getConnection(url, "sa", "");
	for (String command : commands) {
		execute(con, command);
	}
	return con;
}
 

@Test
public void previousUntilLastTest5() {
  String[] retArray = new String[] {
      "17,25",
      "22,25",
      "27,26",
      "32,29",
      "37,40",
      "42,null",
      "47,null",
  };

  try (Connection connection = DriverManager.
      getConnection("jdbc:iotdb://127.0.0.1:6667/", "root", "root");
      Statement statement = connection.createStatement()) {
    boolean hasResultSet = statement.execute(
        "select last_value(temperature) from "
            + "root.ln.wf01.wt01 "
            + "GROUP BY ([17, 48), 5ms) FILL(float[previousUntilLast])");

    assertTrue(hasResultSet);
    int cnt;
    try (ResultSet resultSet = statement.getResultSet()) {
      cnt = 0;
      while (resultSet.next()) {
        String ans = resultSet.getString(TIMESTAMP_STR) + "," + resultSet
            .getString(last_value("root.ln.wf01.wt01.temperature"));
        assertEquals(retArray[cnt], ans);
        cnt++;
      }
      assertEquals(retArray.length, cnt);
    }

  } catch (Exception e) {
    e.printStackTrace();
    fail(e.getMessage());
  }

}
 

public static void paramTest(String type, short param1, short[] param2,
    short[] param3, int[] out, ResultSet[] rs1) throws SQLException {
  Connection conn = DriverManager.getConnection("jdbc:default:connection");
  try {
    PreparedStatement pstmt = conn.prepareStatement(
      "values(cast(? as " + type + "))");
    pstmt.setObject(1, param1);
    rs1[0] = pstmt.executeQuery();
  } finally {
    conn.close();
  }
  param2[0] = param1;
  out[0] = 5;
}
 

public static Connection getConnection() {
	try{
		DriverManager.registerDriver((Driver) Class.forName("com.mysql.jdbc.Driver").newInstance());
		
		StringBuilder url = new StringBuilder();
		
		url.
		append("jdbc:mysql://").		//db type
		append("localhost:"). 			//host name
		append("3306/").				//port
		append("db_example?").			//db name
		append("user=tully&").			//login
		append("password=tully");		//password
		
		System.out.append("URL: " + url + "\n");
		
		Connection connection = DriverManager.getConnection(url.toString());
		return connection;
	} catch (SQLException | InstantiationException | IllegalAccessException | ClassNotFoundException e) {
		e.printStackTrace();
	}
       return null;
}
 

/**
 * Utility method to see if a driver is registered
 */
private boolean isDriverRegistered(Driver d) {
    boolean foundDriver = false;
    java.util.Enumeration e = DriverManager.getDrivers();
    while (e.hasMoreElements()) {
        if (d == (Driver) e.nextElement()) {
            foundDriver = true;
            break;
        }
    }
    return foundDriver;
}
 

@Test
public void testInvalidURL() throws Exception {
  Class.forName(PhoenixDriver.class.getName());
  try {
  DriverManager.getConnection("any text whatever you want to put here");
  fail("Should have failed due to invalid driver");
  } catch(Exception e) {
  }
}
 

@Test
public void testSettingPropertiesWhenTableHasDefaultColFamilySpecified() throws Exception {
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    String ddl = "CREATE TABLE  " + dataTableFullName + " (\n"
            +"ID1 VARCHAR(15) NOT NULL,\n"
            +"ID2 VARCHAR(15) NOT NULL,\n"
            +"CREATED_DATE DATE,\n"
            +"CREATION_TIME BIGINT,\n"
            +"CF.LAST_USED DATE,\n"
            +"CONSTRAINT PK PRIMARY KEY (ID1, ID2)) " + generateDDLOptions("IMMUTABLE_ROWS=true, DEFAULT_COLUMN_FAMILY = 'XYZ'"
            + (!columnEncoded ? ",IMMUTABLE_STORAGE_SCHEME=" + PTable.ImmutableStorageScheme.ONE_CELL_PER_COLUMN : ""));
    Connection conn = DriverManager.getConnection(getUrl(), props);
    conn.createStatement().execute(ddl);
    assertImmutableRows(conn, dataTableFullName, true);
    ddl = "ALTER TABLE  " + dataTableFullName
            + " SET COMPACTION_ENABLED = FALSE, CF.BLOCKSIZE=50000, IMMUTABLE_ROWS = TRUE, TTL=1000";
    conn.createStatement().execute(ddl);
    assertImmutableRows(conn, dataTableFullName, true);
    try (Admin admin = conn.unwrap(PhoenixConnection.class).getQueryServices().getAdmin()) {
        TableDescriptor tableDesc = admin.getDescriptor(TableName.valueOf(dataTableFullName));
        ColumnFamilyDescriptor[] columnFamilies = tableDesc.getColumnFamilies();
        assertEquals(2, columnFamilies.length);
        assertEquals("CF", columnFamilies[0].getNameAsString());
        assertEquals(ColumnFamilyDescriptorBuilder.DEFAULT_REPLICATION_SCOPE, columnFamilies[0].getScope());
        assertEquals(1000, columnFamilies[0].getTimeToLive());
        assertEquals(50000, columnFamilies[0].getBlocksize());
        assertEquals("XYZ", columnFamilies[1].getNameAsString());
        assertEquals(ColumnFamilyDescriptorBuilder.DEFAULT_REPLICATION_SCOPE, columnFamilies[1].getScope());
        assertEquals(1000, columnFamilies[1].getTimeToLive());
        assertEquals(ColumnFamilyDescriptorBuilder.DEFAULT_BLOCKSIZE, columnFamilies[1].getBlocksize());
        assertEquals(Boolean.toString(false), tableDesc.getValue(TableDescriptorBuilder.COMPACTION_ENABLED));
    }
}
 

@Override
public Connection getConnection() throws ProcessException {
    try {
        Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
        return DriverManager.getConnection("jdbc:derby:" + databaseLocation + ";create=true");
    } catch (final Exception e) {
        throw new ProcessException("getConnection failed: " + e);
    }
}
 

public static ColorData setPalette(String rgba, double palette_index) {
        try ( Connection conn = DriverManager.getConnection(protocol + dbHome() + login);) {

            return setPalette(conn, rgba, palette_index);
        } catch (Exception e) {
            failed(e);
//            // logger.debug(e.toString());
            return null;
        }
    }
 

@Override
public KeyValueStorage createKeyValue(String name) {

    try {
        if (kv_db == null) {
            Class.forName("org.sqlite.JDBC");
            kv_db = DriverManager.getConnection("jdbc:sqlite:keyvalue.db");
        }
        return new ClcKeyValueStorage(kv_db, name, this.context);
    } catch (SQLException | ClassNotFoundException e) {
        logger.error("Error in creating keyvalue storage", e);
    }
    return null;
}
 

private Connection loadConnection() {
	try {
		Connection connection = DriverManager.getConnection(url, username, password);
		if (ispool == false) {
			return connection; // 不启用连接池将不再代理
		}
		return new FlyConnectionProxy(connection).setFlyDataSource(this).getProxy(Connection.class);// 返回代理对象
	} catch (SQLException e) {
		throw new FlySQLException("创建连接失败：", e);
	}
}
 

@Test
public void selectUnknownTimeSeries() throws ClassNotFoundException {
  Class.forName(Config.JDBC_DRIVER_NAME);

  try (Connection connection = DriverManager
      .getConnection(Config.IOTDB_URL_PREFIX + "127.0.0.1:6667/", "root", "root");
      Statement statement = connection.createStatement()) {
    statement.execute("select s0, s10 from root.vehicle.*");
    try (ResultSet resultSet = statement.getResultSet()) {
      int cnt = 0;
      while (resultSet.next()) {
        cnt++;
      }
      assertEquals(23400, cnt);
    }
  } catch (SQLException e) {
    e.printStackTrace();
    fail();
  }
}
 

/**
 * Test for Driver.connect() behavior clarifications:
 * - connect() throws SQLException if URL is null.
 */
public void testDriverConnectNullArgument() throws Exception {
    assertThrows(SQLException.class, "The url cannot be null", new Callable<Void>() {
        public Void call() throws Exception {
            Driver mysqlDriver = new Driver();
            mysqlDriver.connect(null, null);
            return null;
        }
    });

    assertThrows(SQLException.class, "The url cannot be null", new Callable<Void>() {
        public Void call() throws Exception {
            DriverManager.getConnection(null);
            return null;
        }
    });
}
 

@Test
public void testUpsert() throws Exception {
    String testString1 = "mwalsh1";
    String testString2 = "mwalsh2";
    
    Connection conn = DriverManager.getConnection(getUrl());
    String ddl = "CREATE TABLE IF NOT EXISTS MD5_UPSERT_TEST (k1 binary(16) NOT NULL,k2 binary(16) NOT NULL  CONSTRAINT pk PRIMARY KEY (k1, k2))";
    conn.createStatement().execute(ddl);
    String dml = String.format("UPSERT INTO MD5_UPSERT_TEST VALUES(md5('%s'),md5('%s'))", testString1, testString2);
    conn.createStatement().execute(dml);
    conn.commit();
    
    ResultSet rs = conn.createStatement().executeQuery("SELECT k1,k2 FROM MD5_UPSERT_TEST");
    assertTrue(rs.next());
    byte[] pk1 = MessageDigest.getInstance("MD5").digest(testString1.getBytes());
    byte[] pk2 = MessageDigest.getInstance("MD5").digest(testString2.getBytes());
    assertArrayEquals(pk1, rs.getBytes(1));
    assertArrayEquals(pk2, rs.getBytes(2));
    assertFalse(rs.next());
    PreparedStatement stmt = conn.prepareStatement("SELECT k1,k2 FROM MD5_UPSERT_TEST WHERE k1=md5(?)");
    stmt.setString(1, testString1);
    rs = stmt.executeQuery();
    assertTrue(rs.next());
    byte[] second1 = rs.getBytes(1);
    byte[] second2 = rs.getBytes(2);
    assertArrayEquals(pk1, second1);
    assertArrayEquals(pk2, second2);
    assertFalse(rs.next());
}
 

@Test
public void testCursorsWithColsOfTypesDecimal() throws Exception {
    String cursorName = generateUniqueName();
    String tenantId = getOrganizationId();
    String aTable = initATableValues(null, tenantId,
        getDefaultSplits(tenantId), null, null, getUrl(), null);
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);

    String query = "SELECT x_decimal FROM "+ aTable+" WHERE ?=organization_id AND entity_id IN (?,?,?)";
    query = query.replaceFirst("\\?", "'"+tenantId+"'");
    query = query.replaceFirst("\\?", "'"+ROW7+"'");
    query = query.replaceFirst("\\?", "'"+ROW8+"'");
    query = query.replaceFirst("\\?", "'"+ROW9+"'");

    Connection conn = DriverManager.getConnection(getUrl(), props);
    try {
        String cursor = "DECLARE " + cursorName + " CURSOR FOR "+query;
        conn.prepareStatement(cursor).execute();
        cursor = "OPEN " + cursorName;
        conn.prepareStatement(cursor).execute();
        cursor = "FETCH NEXT FROM " + cursorName;

        ResultSet rs = conn.prepareStatement(cursor).executeQuery();
        int count = 0;
        while(rs.next()) {
            assertTrue(BigDecimal.valueOf(0.1).equals(rs.getBigDecimal(1)) || BigDecimal.valueOf(3.9).equals(rs.getBigDecimal(1)) || BigDecimal.valueOf(3.3).equals(rs.getBigDecimal(1)));
            count++;
            if(count == 3) break;
            rs = conn.prepareStatement(cursor).executeQuery();
        }
        assertTrue(count == 3);
    } finally {
        String sql = "CLOSE " + cursorName;
        conn.prepareStatement(sql).execute();
        conn.close();
    }
}
 

@org.junit.Test
public void testBadConnect() throws Exception
{
    try
    {
        DriverManager.getConnection(TestUtil.getBadURL()+"/?connectionTimeout=1000", TestUtil.getUser(), TestUtil.getPassword());
        assertFalse("Should not get here",true);
    }
    catch(SQLException ex)
    {
        assertEquals("Error opening connection",ex.getMessage());
    }
}
 

public static ArrayList<ProductList> TableGenerator(){
        ArrayList<ProductList> list = new ArrayList<>();
        try {
            Connection con = DriverManager.getConnection("jdbc:sqlite:DBs/kidsDB.db");
            Statement ps = con.createStatement();
            ResultSet rs = ps.executeQuery("SELECT mbrand, mmodel, mprice,mquantity, mdescription, mphoto FROM kids");
            
            ProductList pl;
            
            while(rs.next()){
                pl = new ProductList(rs.getString("mbrand"),rs.getString("mmodel"),
                        rs.getInt("mprice"),rs.getInt("mquantity"),rs.getString("mdescription"),
                        rs.getString("mphoto"));
                
                list.add(pl);

            }
            
            con.close();
        } catch (SQLException ex) {
            Logger.getLogger(MobileDB.class.getName()).log(Level.SEVERE, null, ex);
        }
        return list;
}
 

@Test
public void testDateInList() throws Exception {
    String query = "SELECT entity_id FROM ATABLE WHERE a_date IN (?,?) AND a_integer < 4";
    String url = PHOENIX_JDBC_URL + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + (ts + 5); // Run query at timestamp 5
    Properties props = new Properties(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(url, props);
    try {
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setDate(1, new Date(0));
        statement.setDate(2, date);
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        assertEquals(ROW1, rs.getString(1));
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 

/**
 * A test case for DERBY-3304. An explicit rollback inside the procedure
 * should close all the resultsets created before the call to the
 * procedure and any resultsets created inside the procedure including
 * the dynamic resultsets.
 * 
 * @param p1
 * @param data
 * @throws SQLException
 */
public static void rollbackInsideProc(int p1, ResultSet[] data) 
throws SQLException {
    Connection conn = DriverManager.getConnection(
    		"jdbc:default:connection");
    PreparedStatement ps = conn.prepareStatement(
    		"select * from dellater1 where i = ?");
    ps.setInt(1, p1);
    data[0] = ps.executeQuery();
    conn.rollback();
    conn.close();
}
 

private void getTableTester(String normalizedName, String sqlStatementName) throws SQLException {
    Connection conn = DriverManager.getConnection(getUrl());
    try {
        conn.createStatement().execute("CREATE TABLE " + sqlStatementName + " (k VARCHAR PRIMARY KEY)");
        PTable aTable = PhoenixRuntime.getTable(conn, normalizedName);
        assertNotNull(aTable);
    } finally {
        if (null != conn) {
            conn.createStatement().execute("DROP TABLE IF EXISTS " + sqlStatementName);
        }
    }
}