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"));
      }
    }
  }
}
 

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 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();
    }
}
 

private void testOptimizationTenantSpecific(String dataTableName, String indexTableName, Integer saltBuckets) throws Exception {
    createBaseTable(dataTableName, saltBuckets, "('e','i','o')", true);
    Connection conn1 = DriverManager.getConnection(getUrl() + ';' + PhoenixRuntime.TENANT_ID_ATTRIB + "=tid1");
    try{
        conn1.createStatement().execute("UPSERT INTO " + dataTableName + " values(1,2,4,'z')");
        conn1.createStatement().execute("UPSERT INTO " + dataTableName + " values(1,2,3,'a')");
        conn1.createStatement().execute("UPSERT INTO " + dataTableName + " values(2,4,2,'a')");
        conn1.createStatement().execute("UPSERT INTO " + dataTableName + " values(3,1,1,'c')");
        conn1.commit();
        createIndex(indexTableName, dataTableName, "v1");
        
        String query = "SELECT /*+ INDEX(" + dataTableName + " " + indexTableName + ")*/ k1,k2,k3,v1 FROM " + dataTableName +" where v1='a'";
        ResultSet rs = conn1.createStatement().executeQuery("EXPLAIN "+ query);
        
        String actual = QueryUtil.getExplainPlan(rs);
        String expected = "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + dataTableName + " \\['tid1'\\]\n" +
                        "    SKIP-SCAN-JOIN TABLE 0\n" +
                        "        CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + indexTableName + " \\['tid1','a'\\]\n" +
                        "            SERVER FILTER BY FIRST KEY ONLY\n" +
                        "    DYNAMIC SERVER FILTER BY \\(\"" + dataTableName + ".K1\", \"" + dataTableName + ".K2\"\\) IN \\(\\(\\$\\d+.\\$\\d+, \\$\\d+.\\$\\d+\\)\\)";
        assertTrue("Expected:\n" + expected + "\ndid not match\n" + actual, Pattern.matches(expected, actual));
        
        rs = conn1.createStatement().executeQuery(query);
        assertTrue(rs.next());
        assertEquals(1, rs.getInt("k1"));
        assertEquals(2, rs.getInt("k2"));
        assertEquals(3, rs.getInt("k3"));
        assertEquals("a", rs.getString("v1"));
        assertTrue(rs.next());
        assertEquals(2, rs.getInt("k1"));
        assertEquals(4, rs.getInt("k2"));
        assertEquals(2, rs.getInt("k3"));
        assertEquals("a", rs.getString("v1"));
        assertFalse(rs.next());
    } finally {
        conn1.close();
    }
}
 

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;
}
 

@Test
public void testDistinctCountLimitBug5217() throws Exception {
    Connection conn = null;
    try {
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        conn = DriverManager.getConnection(getUrl(), props);
        String tableName = generateUniqueName();
        String sql = "create table " + tableName + "( "+
                " pk1 integer not null , " +
                " pk2 integer not null, " +
                " v integer, " +
                " CONSTRAINT TEST_PK PRIMARY KEY (pk1,pk2))";
        conn.createStatement().execute(sql);
        conn.createStatement().execute("UPSERT INTO "+tableName+"(pk1,pk2,v) VALUES (1,1,1)");
        conn.createStatement().execute("UPSERT INTO "+tableName+"(pk1,pk2,v) VALUES (2,2,2)");
        conn.commit();

        sql = "select count(distinct pk1) from " + tableName + " limit 1";
        ResultSet rs = conn.prepareStatement(sql).executeQuery();
        assertResultSet(rs, new Object[][]{{Long.valueOf(2L)}});
    } finally {
        if(conn!=null) {
            conn.close();
        }
    }
}
 

/**
 * Creates a new connection. <br>
 * The connection is wrapped by {@link LuteceConnection}
 *
 * @return The new created connection
 * @throws SQLException
 *             The SQL exception
 */
private Connection newConnection( ) throws SQLException
{
    Connection conn;

    if ( _strUser == null )
    {
        conn = DriverManager.getConnection( _strUrl );
    }
    else
    {
        conn = DriverManager.getConnection( _strUrl, _strUser, _strPassword );
    }

    // wrap connection so this connection pool is used when conn.close() is called
    conn = LuteceConnectionFactory.newInstance( this, conn );

    _logger.info( "New connection created. Connections count is : " + ( getConnectionCount( ) + 1 ) );

    return conn;
}
 

@Test
public void testBytesRowsForHashJoin() throws Exception {
    String tableA = generateUniqueName();
    String tableB = generateUniqueName();
    String sql =
            "SELECT ta.c1.a, ta.c2.b FROM " + tableA + " ta JOIN " + tableB
                    + " tb ON ta.k = tb.k";
    try (Connection conn = DriverManager.getConnection(getUrl())) {
        initData(conn, tableA);
        initData(conn, tableB);
        assertEstimatesAreNull(sql, Lists.newArrayList(), conn);
    }
}
 

@Test
public void testConnectionPropertiesSync() throws Exception {
  ConnectionSpec.getDatabaseLock().lock();
  try {
    AvaticaConnection conn = (AvaticaConnection) DriverManager.getConnection(url);
    conn.setAutoCommit(false);
    conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);

    // sync connection properties
    conn.createStatement();
    Connection remoteConn = getConnection(
            AvaticaServersForTest.PropertyRemoteJdbcMetaFactory.getInstance(PROPERTIES), conn.id);

    assertFalse(remoteConn.getAutoCommit());
    assertEquals(remoteConn.getTransactionIsolation(),
            Connection.TRANSACTION_REPEATABLE_READ);

    // after 1s, remote connection expired and reopen
    Thread.sleep(1000);

    conn.createStatement();
    Connection remoteConn1 = getConnection(
            AvaticaServersForTest.PropertyRemoteJdbcMetaFactory.getInstance(PROPERTIES), conn.id);

    assertFalse(remoteConn1.getAutoCommit());
    assertEquals(remoteConn1.getTransactionIsolation(),
            Connection.TRANSACTION_REPEATABLE_READ);
  } finally {
    ConnectionSpec.getDatabaseLock().unlock();
  }
}
 

@Test
public void invalidCharacters() throws Exception {
	Connection conn = DriverManager.getConnection(getUrl());
	String ddl = "CREATE TABLE test_table ( some_column BINARY(12) NOT NULL CONSTRAINT PK PRIMARY KEY (some_column))";

	conn.createStatement().execute(ddl);

	try {
		conn.createStatement().executeQuery("SELECT * FROM test_table WHERE some_column = DECODE('zzxxuuyyzzxxuuyy', 'hex')");
        fail();
	} catch (SQLException e) {
		assertEquals(SQLExceptionCode.ILLEGAL_DATA.getErrorCode(), e.getErrorCode());
	}
}
 

@Test
public void testInListParameterMetaData1() throws Exception {
    String query = "SELECT a_string, b_string FROM atable WHERE a_string IN (?, ?)";
    Connection conn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TestUtil.TEST_PROPERTIES));
    PreparedStatement statement = conn.prepareStatement(query);
    ParameterMetaData pmd = statement.getParameterMetaData();
    assertEquals(2, pmd.getParameterCount());
    assertEquals(String.class.getName(), pmd.getParameterClassName(1));
    assertEquals(String.class.getName(), pmd.getParameterClassName(2));
}
 

@Test
public void testOrderOfOperationsAdditionDivision() throws Exception {
    Connection conn = DriverManager.getConnection(getUrl());
    initIntegerTable(conn);
    ResultSet rs;
    
    // 6 + 4 / 3
    // 6 + 1
    // 7
    rs = conn.createStatement().executeQuery("SELECT six + four / three FROM ARITHMETIC_TEST");
    assertTrue(rs.next());
    assertEquals(7, rs.getLong(1));
    assertFalse(rs.next());
    
    // 4 / 3 + 6
    // 1 + 6     
    // 7
    rs = conn.createStatement().executeQuery("SELECT four / three + six FROM ARITHMETIC_TEST");
    assertTrue(rs.next());
    assertEquals(7, rs.getLong(1));
    assertFalse(rs.next());
}
 

@Test
public void testUpsertOnSCNSetMutTableWithoutIdx() throws Exception {

    helpTestUpsertWithSCNIT(false, false, true, false, false);
    prep.executeUpdate();
    props = new Properties();
    Connection conn = DriverManager.getConnection(getUrl(),props);
    ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM "+tableName);
    assertTrue(rs.next());
    assertEquals("abc", rs.getString(1));
    assertEquals("This is the first comment!", rs.getString(2));
    assertFalse(rs.next());
}
 

/**
 * Test grouped aggregation query with a mix of aggregated data types
 * @throws Exception
 */
@Test
public void testSumGroupedAggregation() throws Exception {
    long ts = nextTimestamp();
    String tenantId = getOrganizationId();
    String query = "SELECT feature,sum(unique_users),sum(cpu_utilization),sum(transactions),sum(db_utilization),sum(response_time),count(1) c FROM PRODUCT_METRICS WHERE organization_id=? AND feature < ? GROUP BY feature";
    String url = getUrl() + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + (ts + 5); // Run query at timestamp 5
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(url, props);
    try {
        initTableValues(tenantId, getSplits(tenantId), ts);
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, tenantId);
        statement.setString(2, F3);
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        assertEquals(F1, rs.getString("feature"));
        assertEquals(120, rs.getInt("sum(unique_users)"));
        assertEquals(BigDecimal.valueOf(8), rs.getBigDecimal(3));
        assertEquals(1200L, rs.getLong(4));
        assertEquals(BigDecimal.valueOf(2.6), rs.getBigDecimal(5));
        assertEquals(0, rs.getLong(6));
        assertEquals(true, rs.wasNull());
        assertEquals(4, rs.getLong("c"));
        assertTrue(rs.next());
        assertEquals(F2, rs.getString("feature"));
        assertEquals(40, rs.getInt(2));
        assertEquals(BigDecimal.valueOf(3), rs.getBigDecimal(3));
        assertEquals(400L, rs.getLong(4));
        assertEquals(BigDecimal.valueOf(0.8), rs.getBigDecimal(5));
        assertEquals(0, rs.getLong(6));
        assertEquals(true, rs.wasNull());
        assertEquals(1, rs.getLong("c"));
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 

@Test
    public void sqlServerTest() throws SQLException, IOException {
        final String url = "jdbc:sqlserver://localhost:1433;DatabaseName=test";
        final String username = "sa";
        final String password = "hydb001*";
        Connection conn = DriverManager.getConnection(url, username, password);
        ScriptRunner runner = new ScriptRunner(conn);
        Resources.setCharset(Charset.forName("UTF-8")); //设置字符集,不然中文乱码插入错误
        runner.setLogWriter(null);//设置是否输出日志

        final File file  = new File("D:\\CODE\\metadata\\metadata-core\\src\\main\\resources\\coreSqlServer.sql");
        InputStreamReader isr = new InputStreamReader(new FileInputStream(file), "UTF-8");
        runner.runScript(isr);
//        runner.runScript(Resources.getResourceAsReader("sql/CC21-01.sql"));
        runner.closeConnection();
        conn.close();
    }
 

public Connection getConnection(){
    try {
        return
        DriverManager.getConnection("jdbc:mysql://localhost:3306/atividade_06?", "root","12345");
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
}
 

@Test
public void testRoundingUpDoubleInWhere() throws Exception {
	Connection conn = DriverManager.getConnection(getUrl());
	ResultSet rs = conn.createStatement().executeQuery(
       "SELECT * FROM " + tableName + " WHERE ROUND(\"DEC\", 2) = 1.26");
	assertTrue(rs.next());
}
 

public static void deleteUserCache(String seed)
{
	PreparedStatement preparedStatement=null;
	Connection connection=null;
	Statement smStatement=null;
	ResultSet rsResultSet = null;
	try {
		Class.forName(driverString);
		connection=DriverManager.getConnection(conString,"sa","123456");
		preparedStatement=connection.prepareStatement("delete UserCache where id='"+seed+"'");
		preparedStatement.executeUpdate();
	} catch (Exception e) {
		e.printStackTrace();
	}
	finally
	{
		try {
			if(rsResultSet==null)
			{
				rsResultSet.close();
			}
			if(smStatement==null)
			{
				smStatement.close();
			}
			if(connection==null)
			{
				connection.close();
			}
			
		} catch (Exception e2) {

		}
	}
}
 

/** {@inheritDoc} */
public void contextDestroyed(ServletContextEvent sce) {
    stopMessaging();
    logStop("Messaging");

    stopHibernate();
    logStop("Hibernate");

    if (sce == null) {
        // this has been called from the testsuite, next steps would
        // break subsequent tests
        return;
    }

    // This manually deregisters JDBC driver,
    // which prevents Tomcat from complaining about memory leaks
    Enumeration<Driver> drivers = DriverManager.getDrivers();
    while (drivers.hasMoreElements()) {
        Driver driver = drivers.nextElement();
        try {
            DriverManager.deregisterDriver(driver);
            log.info("deregistering jdbc driver: " + driver);
        }
        catch (SQLException e) {
            log.warn("Error deregistering driver " + driver);
        }
    }


    // shutdown the logger to avoid ThreadDeath exception during
    // webapp reload.
    LogManager.shutdown();
}
 

public static final void checkStackOverflow(int arg, ResultSet[] rs)
    throws SQLException {
  if (arg == 0) {
    Connection conn = DriverManager.getConnection("jdbc:default:connection");
    rs[0] = conn.createStatement().executeQuery(
        "<local> select count(*) from simple_table");
  }
  else {
    checkStackOverflow(arg - 1, rs);
  }
}
 

public static void execulteSql(String sql, String toDn) throws SQLException, IOException {
    PhysicalDBNode dbNode = MycatServer.getInstance().getConfig().getDataNodes().get(toDn);
    PhysicalDBPool dbPool = dbNode.getDbPool();
    PhysicalDatasource datasource = dbPool.getSources()[dbPool.getActivedIndex()];
    DBHostConfig config = datasource.getConfig();
    Connection con = null;
    try {
        con = DriverManager
                .getConnection("jdbc:mysql://" + config.getUrl() + "/" + dbNode.getDatabase(), config.getUser(), config.getPassword());

        JdbcUtils.execute(con, sql, new ArrayList<>());

    } finally {
        JdbcUtils.close(con);
    }

}
 

public void testGenericStartup() throws Exception {
              startTenantFlow();
	Class.forName("org.h2.Driver");
	Connection conn = DriverManager.getConnection("jdbc:h2:mem:ds-test-db;DB_CLOSE_DELAY=-1");
	Statement stmt = conn.createStatement();
	stmt.executeUpdate("RUNSCRIPT FROM './src/test/resources/sql/CreateH2TestDB.sql'");
	stmt.close();
	conn.close();
}
 

protected void establishConnection() throws SQLException, ClassNotFoundException {
	Class.forName(drivername);
	if (username == null) {
		connection = DriverManager.getConnection(dbURL);
	} else {
		connection = DriverManager.getConnection(dbURL, username, password);
	}
}
 

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);
        }
    }
}
 

/**
 * 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();
}
 

@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();
    }
}
 

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;
}
 

@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());
    }
}
 

@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();
    }
}