com.mysql.jdbc.jdbc2.optional.MysqlDataSource Java Examples

@BeforeMethod
public void setupTest()
{
    queryRunner.getCoordinator().getSessionPropertyDefaults()
            .setConfigurationManager("db-test", ImmutableMap.<String, String>builder()
                    .put("session-property-manager.db.url", mysqlContainer.getJdbcUrl())
                    .put("session-property-manager.db.username", mysqlContainer.getUsername())
                    .put("session-property-manager.db.password", mysqlContainer.getPassword())
                    .build());

    MysqlDataSource dataSource = new MysqlDataSource();
    dataSource.setURL(mysqlContainer.getJdbcUrl());
    dataSource.setUser(mysqlContainer.getUsername());
    dataSource.setPassword(mysqlContainer.getPassword());
    dao = Jdbi.create(dataSource)
            .installPlugin(new SqlObjectPlugin())
            .onDemand(SessionPropertiesDao.class);
}
 

@Before public void init() throws SQLException {
  StringBuilder url = new StringBuilder("jdbc:mysql://");
  url.append(envOr("MYSQL_HOST", "127.0.0.1"));
  url.append(":").append(envOr("MYSQL_TCP_PORT", 3306));
  String db = envOr("MYSQL_DB", null);
  if (db != null) url.append("/").append(db);
  url.append("?statementInterceptors=").append(TracingStatementInterceptor.class.getName());
  url.append("&zipkinServiceName=").append("myservice");

  MysqlDataSource dataSource = new MysqlDataSource();
  dataSource.setUrl(url.toString());

  dataSource.setUser(System.getenv("MYSQL_USER"));
  assumeTrue("Minimally, the environment variable MYSQL_USER must be set",
    dataSource.getUser() != null);
  dataSource.setPassword(envOr("MYSQL_PASS", ""));
  connection = dataSource.getConnection();
  spans.clear();
}
 

protected void connect() {

        try {
            MysqlDataSource dataSource = new MysqlDataSource();
            dataSource.setURL(url);
            dataSource.setPassword(password);
            dataSource.setUser(userName);
            connection = dataSource.getConnection();
            connection.setAutoCommit(true);
            closed = false;
            totalConnectionOpen++;
        } catch (SQLException sqlException) {
            this.closed = true;
            connection = null;

            handle("Unable to connect", sqlException);

        }


    }
 

/**
 * Connects to the DB and tracks if successful so upstream stuff can try to reconnect.
 */
protected void connect() {

    try {
        MysqlDataSource dataSource = new MysqlDataSource();
        dataSource.setURL(url);
        dataSource.setPassword(password);
        dataSource.setUser(userName);
        connection = dataSource.getConnection();
        connection.setAutoCommit(true);
        closed = false;
        totalConnectionOpen++;
    } catch (SQLException sqlException) {
        this.closed = true;
        connection = null;

        handle("Unable to connect", sqlException);

    }


}
 

@Override
public SQLDataSourceLocator getDataSourceLocator() {
    return new SQLDataSourceLocator() {
        public DataSource lookup(SQLDataSetDef def) throws Exception {
            String url = connectionSettings.getProperty("url");
            String user = connectionSettings.getProperty("user");
            String password = connectionSettings.getProperty("password");

            MysqlDataSource ds = new MysqlDataSource();
            ds.setURL(url);
            if (!StringUtils.isBlank(user)) {
                ds.setUser(user);
            }
            if (!StringUtils.isBlank(password)) {
                ds.setPassword(password);
            }
            return ds;
        }
    };
}
 

public Storage(Logger logger, String host, int port, String database, boolean usessl,
               String user, String pass, String prefix) {
    this.logger = logger;

    this.prefix = prefix;

    this.dataSource = new MysqlDataSource();
    this.dataSource.setUser(user);
    this.dataSource.setPassword(pass);

    this.dataSource.setServerName(host);
    this.dataSource.setPort(port);
    this.dataSource.setDatabaseName(database);
    this.dataSource.setUseSSL(usessl);

    this.dataSource.setCachePrepStmts(true);
    this.dataSource.setUseServerPreparedStmts(true);
}
 

public Storage(Logger logger, String host, int port, String database, boolean usessl,
               String user, String pass, String prefix) {
    this.logger = logger;

    this.prefix = prefix;

    this.dataSource = new MysqlDataSource();
    this.dataSource.setUser(user);
    this.dataSource.setPassword(pass);

    this.dataSource.setServerName(host);
    this.dataSource.setPort(port);
    this.dataSource.setDatabaseName(database);
    this.dataSource.setUseSSL(usessl);

    this.dataSource.setCachePrepStmts(true);
    this.dataSource.setUseServerPreparedStmts(true);
}
 

@BeforeClass
public static void beforeClass() throws Exception{
    initializeTestEnv();
    
    final int sleepCount = 30;
    
    LOGGER.debug("sleep " + sleepCount + "sec for generating enough test data");
    for(int i=1; i<=sleepCount; i++){
        LOGGER.debug(i + "/" + sleepCount);
        Thread.sleep(1000);
    }
    
    // create dataSource
    dataSource = new MysqlDataSource();
    dataSource.setUrl(DB_URL);
    dataSource.setUser(DB_USER);
    dataSource.setPassword(DB_PW);
    
    
}
 

public DataIntegrityTestTool(Properties prop) throws Exception{
    
    this.setName("data-integrity-test-thread");
    
    srcDs = new MysqlDataSource();
    srcDs.setURL(prop.getProperty(DMLQueryTool.PROP_MSR_SRC_URL));
    srcDs.setUser(prop.getProperty(DMLQueryTool.PROP_MSR_SRC_USERNAME));
    srcDs.setPassword(prop.getProperty(DMLQueryTool.PROP_MSR_SRC_PASSWROD));
    
    destCount = Integer.parseInt(prop.getProperty(DMLQueryTool.PROP_MSR_DEST_COUNT));
    destDs = new MysqlDataSource[destCount];
    for(int i=0; i<destCount; i++){
        destDs[i] = new MysqlDataSource();
        destDs[i].setURL(prop.getProperty(String.format(DMLQueryTool.PROP_MSR_DEST_URL, i)));
        destDs[i].setUser(prop.getProperty(String.format(DMLQueryTool.PROP_MSR_DEST_USERNAME, i)));
        destDs[i].setPassword(prop.getProperty(String.format(DMLQueryTool.PROP_MSR_DEST_PASSWORD, i)));
    }
}
 

/**
 * Tests fix for BUG#16791 - NullPointerException in MysqlDataSourceFactory
 * due to Reference containing RefAddrs with null content.
 * 
 * @throws Exception
 *             if the test fails
 */
public void testBug16791() throws Exception {
    MysqlDataSource myDs = new MysqlDataSource();
    myDs.setUrl(dbUrl);
    Reference asRef = myDs.getReference();
    System.out.println(asRef);

    removeFromRef(asRef, "port");
    removeFromRef(asRef, NonRegisteringDriver.USER_PROPERTY_KEY);
    removeFromRef(asRef, NonRegisteringDriver.PASSWORD_PROPERTY_KEY);
    removeFromRef(asRef, "serverName");
    removeFromRef(asRef, "databaseName");

    //MysqlDataSource newDs = (MysqlDataSource)
    new MysqlDataSourceFactory().getObjectInstance(asRef, null, null, null);
}
 

/**
 * Tests fix for BUG#19169 - ConnectionProperties (and thus some
 * subclasses) are not serializable, even though some J2EE containers
 * expect them to be.
 * 
 * @throws Exception
 *             if the test fails.
 */
public void testBug19169() throws Exception {
    MysqlDataSource toSerialize = new MysqlDataSource();
    toSerialize.setZeroDateTimeBehavior("convertToNull");

    boolean testBooleanFlag = !toSerialize.getAllowLoadLocalInfile();
    toSerialize.setAllowLoadLocalInfile(testBooleanFlag);

    int testIntFlag = toSerialize.getBlobSendChunkSize() + 1;
    toSerialize.setBlobSendChunkSize(String.valueOf(testIntFlag));

    ByteArrayOutputStream bOut = new ByteArrayOutputStream();
    ObjectOutputStream objOut = new ObjectOutputStream(bOut);
    objOut.writeObject(toSerialize);
    objOut.flush();

    ObjectInputStream objIn = new ObjectInputStream(new ByteArrayInputStream(bOut.toByteArray()));

    MysqlDataSource thawedDs = (MysqlDataSource) objIn.readObject();

    assertEquals("convertToNull", thawedDs.getZeroDateTimeBehavior());
    assertEquals(testBooleanFlag, thawedDs.getAllowLoadLocalInfile());
    assertEquals(testIntFlag, thawedDs.getBlobSendChunkSize());
}
 

/**
 * Tests fix for BUG#19169 - ConnectionProperties (and thus some
 * subclasses) are not serializable, even though some J2EE containers
 * expect them to be.
 * 
 * @throws Exception
 *             if the test fails.
 */
public void testBug19169() throws Exception {
    MysqlDataSource toSerialize = new MysqlDataSource();
    toSerialize.setZeroDateTimeBehavior("convertToNull");

    boolean testBooleanFlag = !toSerialize.getAllowLoadLocalInfile();
    toSerialize.setAllowLoadLocalInfile(testBooleanFlag);

    int testIntFlag = toSerialize.getBlobSendChunkSize() + 1;
    toSerialize.setBlobSendChunkSize(String.valueOf(testIntFlag));

    ByteArrayOutputStream bOut = new ByteArrayOutputStream();
    ObjectOutputStream objOut = new ObjectOutputStream(bOut);
    objOut.writeObject(toSerialize);
    objOut.flush();

    ObjectInputStream objIn = new ObjectInputStream(new ByteArrayInputStream(bOut.toByteArray()));

    MysqlDataSource thawedDs = (MysqlDataSource) objIn.readObject();

    assertEquals("convertToNull", thawedDs.getZeroDateTimeBehavior());
    assertEquals(testBooleanFlag, thawedDs.getAllowLoadLocalInfile());
    assertEquals(testIntFlag, thawedDs.getBlobSendChunkSize());
}
 

@Inject
public SessionPropertiesDaoProvider(DbSessionPropertyManagerConfig config)
{
    requireNonNull(config, "config is null");
    String url = requireNonNull(config.getConfigDbUrl(), "db url is null");

    MysqlDataSource dataSource = new MysqlDataSource();
    dataSource.setURL(url);

    Optional<String> username = Optional.ofNullable(config.getUsername());
    username.ifPresent(dataSource::setUser);

    Optional<String> password = Optional.ofNullable(config.getPassword());
    password.ifPresent(dataSource::setPassword);

    this.dao = Jdbi.create(dataSource)
            .installPlugin(new SqlObjectPlugin())
            .onDemand(SessionPropertiesDao.class);
}
 

public void readTable(String user, String password, String server){
     MysqlDataSource dataSource = new MysqlDataSource();
     dataSource.setUser(user);
     dataSource.setPassword(password);
     dataSource.setServerName(server);
     try{
          Connection conn = dataSource.getConnection();
          Statement stmt = conn.createStatement();
          ResultSet rs = stmt.executeQuery("SELECT * FROM data_science.books");
          while (rs.next()){
               int id = rs.getInt("id");
               String book = rs.getString("book_name");
               String author = rs.getString("author_name");
               Date dateCreated = rs.getDate("date_created");
               System.out.format("%s, %s, %s, %s\n", id, book, author, dateCreated);
          }
          rs.close();
          stmt.close();
          conn.close();
     }catch (Exception e){
          //Your exception handling mechanism goes here.
     }
}
 

/**
 * Tests fix for BUG#16791 - NullPointerException in MysqlDataSourceFactory
 * due to Reference containing RefAddrs with null content.
 * 
 * @throws Exception
 *             if the test fails
 */
public void testBug16791() throws Exception {
    MysqlDataSource myDs = new MysqlDataSource();
    myDs.setUrl(dbUrl);
    Reference asRef = myDs.getReference();
    System.out.println(asRef);

    removeFromRef(asRef, "port");
    removeFromRef(asRef, NonRegisteringDriver.USER_PROPERTY_KEY);
    removeFromRef(asRef, NonRegisteringDriver.PASSWORD_PROPERTY_KEY);
    removeFromRef(asRef, "serverName");
    removeFromRef(asRef, "databaseName");

    //MysqlDataSource newDs = (MysqlDataSource)
    new MysqlDataSourceFactory().getObjectInstance(asRef, null, null, null);
}
 

/**
 * Tests fix for Bug#72632 - NullPointerException for invalid JDBC URL.
 */
public void testBug72632() throws Exception {
    final MysqlDataSource dataSource = new MysqlDataSource();
    dataSource.setUrl("bad-connection-string");
    assertThrows(SQLException.class, "Failed to get a connection using the URL 'bad-connection-string'.", new Callable<Void>() {
        public Void call() throws Exception {
            dataSource.getConnection();
            return null;
        }
    });
}
 

/**
 * @param args
 */
public static void main(final String[] args) {
	final MysqlDataSource ds = new MysqlDataSource();
	ds.setUser("root");
	ds.setDatabaseName("nosco_test_jpetstore");
	final Query<Item> items = Item.ALL.use(ds);
	System.err.println("about to start...");
	int count = 0;
	for (final Item item : items) {
		item.getAttr1();
		item.getAttr3();
		++count;
	}
	System.err.println("...done! "+ count);
}
 

protected void setUp() throws Exception {
	super.setUp();
	MysqlDataSource mysqlDS = new MysqlDataSource();
	mysqlDS.setUser("root");
	mysqlDS.setDatabaseName("nosco_test_jpetstore");
	ds = mysqlDS;
	Context vmContext = Context.getVMContext();
	vmContext.setDataSource(ds).setAutoUndo(false);
	//vmContext.overrideSchema(ds, "public", "nosco_test_jpetstore").setAutoUndo(false);
	ccds = new ConnectionCountingDataSource(ds);
	//vmContext.overrideSchema(ccds, "public", "nosco_test_jpetstore").setAutoUndo(false);
}
 

public synchronized static DataSource getDS() {
	if (ds==null) {
		MysqlDataSource tmpDS = new MysqlDataSource();
		tmpDS.setURL("jdbc:mysql://localhost/sakila");
		tmpDS.setUser("root");
		tmpDS.setPassword("");
		ds = tmpDS;
	}
	return ds;
}
 

@Inject
public MysqlDaoProvider(DbResourceGroupConfig config)
{
    requireNonNull(config, "DbResourceGroupConfig is null");
    MysqlDataSource dataSource = new MysqlDataSource();
    dataSource.setURL(requireNonNull(config.getConfigDbUrl(), "resource-groups.config-db-url is null"));
    this.dao = Jdbi.create(dataSource)
            .installPlugin(new SqlObjectPlugin())
            .onDemand(ResourceGroupsDao.class);
}
 

public static void main(final String[] args) throws IOException, InterruptedException {
    final MysqlDataSource dataSource = new MysqlDataSource();
    dataSource.setDatabaseName(DATABASE_NAME);
    final JdbcTemplate testdb = new JdbcTemplate(dataSource);
    final MongoClient client = new MongoClient("localhost");

    final Dependency mysqlDatabaseDependency = new MySqlDatabaseDependency(DATABASE_NAME, testdb);
    final Dependency dbDependency = new MongoDBDatabaseDependency(DATABASE_NAME, client);
    final Dependency onDiskFileDependency = new OnDiskFileDependency("file");

    final DependencyManager dependencyManager = new DependencyManager();
    dependencyManager.addDependency(mysqlDatabaseDependency);
    dependencyManager.addDependency(dbDependency);
    dependencyManager.addDependency(onDiskFileDependency);

    final Application application = new Application();
    Executors.newScheduledThreadPool(1).scheduleAtFixedRate(new Runnable() {
        @Override
        public void run() {
            application.setResultSet(dependencyManager.evaluate());
        }
    }, 0L, 3L, TimeUnit.SECONDS);


    while (true) {
        final CheckResultSet resultSet = application.getResultSet();
        if (application.getResultSet() == null) {
            continue;
        }

        Thread.sleep(1000L);

        final StringWriter stringWriter = new StringWriter();
        WRITER.writeValue(stringWriter, resultSet.summarize(true));
        System.out.println(stringWriter);
    }
}
 

public static MysqlDataSource createMysqlDataSource(
    String host,
    int port,
    String user,
    String password,
    boolean mTlsEnabled,
    TlsConfiguration tlsConfig) {
  MysqlDataSource dataSource = new MysqlConnectionPoolDataSource();

  dataSource.setUser(user);
  dataSource.setPassword(password);
  dataSource.setServerName(host);
  dataSource.setPort(port);
  dataSource.setJdbcCompliantTruncation(false);
  dataSource.setAutoReconnectForConnectionPools(true);

  if (mTlsEnabled && tlsConfig != null) {
    dataSource.setUseSSL(true);
    if (tlsConfig.getKeyStoreFilePath() != null && tlsConfig.getKeyStorePassword() != null) {
      dataSource.setClientCertificateKeyStoreUrl("file:" + tlsConfig.getKeyStoreFilePath());
      dataSource.setClientCertificateKeyStorePassword(tlsConfig.getKeyStorePassword());
    }
    if (tlsConfig.getKeyStoreType() != null) {
      dataSource.setClientCertificateKeyStoreType(tlsConfig.getKeyStoreType());
    }
    if (tlsConfig.getTrustStoreFilePath() != null && tlsConfig.getTrustStorePassword() != null) {
      dataSource.setTrustCertificateKeyStoreUrl("file:" + tlsConfig.getTrustStoreFilePath());
      dataSource.setTrustCertificateKeyStorePassword(tlsConfig.getTrustStorePassword());
    }
    if (tlsConfig.getTrustStoreType() != null) {
      dataSource.setTrustCertificateKeyStoreType(tlsConfig.getTrustStoreType());
    }
  }

  return dataSource;
}
 

public MysqlServerInfo(String host, int port, String user, String password, List<String> dbNameList) throws SQLException{
    ds = new MysqlDataSource();
    ((MysqlDataSource)ds).setServerName(host);
    ((MysqlDataSource)ds).setPort(port);
    ((MysqlDataSource)ds).setUser(user);
    ((MysqlDataSource)ds).setPassword(password);
    
    this.dbNameList = dbNameList;
    
    initialize();
}
 

/**
 * Tests fix for Bug#72632 - NullPointerException for invalid JDBC URL.
 */
public void testBug72632() throws Exception {
    final MysqlDataSource dataSource = new MysqlDataSource();
    dataSource.setUrl("bad-connection-string");
    assertThrows(SQLException.class, "Failed to get a connection using the URL 'bad-connection-string'.", new Callable<Void>() {
        public Void call() throws Exception {
            dataSource.getConnection();
            return null;
        }
    });
}
 

public static Connection getConnection(String url, String username, String password) throws SQLException{
    MysqlDataSource ds = new MysqlDataSource();
    ds.setUrl(url);
    ds.setUser(username);
    ds.setPassword(password);
    return ds.getConnection();
}
 

@BeforeClass
public static void beforeClass() throws Exception{
    initializeTestEnv();
    
    final int sleepCount = 30;
    
    LOGGER.debug("sleep " + sleepCount + "sec for generating enough test data");
    for(int i=1; i<=sleepCount; i++){
        LOGGER.debug(i + "/" + sleepCount);
        Thread.sleep(1000);
    }
    
    dataSource = new MysqlDataSource();
    dataSource.setURL(DB_URL);
    dataSource.setUser(DB_USER);
    dataSource.setPassword(DB_PW);
    
    for(int i=0; i<SHARD_COUNT; i++){
        
        Connection connDest = DriverManager.getConnection(DB_DEST_URL_LIST_WITHOUT_SCHEMA[i], DB_USER, DB_PW);
        try{
            Statement stmt = connDest.createStatement();
            stmt.execute("DROP DATABASE IF EXISTS " + DB_DEST_SCHEMA_LIST[i]);
            stmt.execute("CREATE DATABASE " + DB_DEST_SCHEMA_LIST[i]);
            stmt.execute(String.format(TestUtil.getResourceAsString("sql/create_table_adt_test_1.sql"), DB_DEST_SCHEMA_LIST[i]));
            stmt.execute(String.format(TestUtil.getResourceAsString("sql/create_table_adt_test_2.sql"), DB_DEST_SCHEMA_LIST[i]));
            stmt.execute(String.format(TestUtil.getResourceAsString("sql/create_table_adt_test_3.sql"), DB_DEST_SCHEMA_LIST[i]));
        }finally{
            connDest.close();
        }
        
    }
        
}
 

public void testBug42267() throws Exception {
    MysqlDataSource ds = new MysqlDataSource();
    ds.setUrl(dbUrl);
    Connection c = ds.getConnection();
    String query = "select 1,2,345";
    PreparedStatement ps = c.prepareStatement(query);
    String psString = ps.toString();
    assertTrue("String representation of wrapped ps should contain query string", psString.endsWith(": " + query));
    ps.close();
    ps.toString();
    c.close();
}
 

public static DataModel loadFromDB() throws Exception {
	
	// Database-based DataModel - MySQLJDBCDataModel
	/*
	 * A JDBCDataModel backed by a PostgreSQL database and accessed via
	 * JDBC. It may work with other JDBC databases. By default, this class
	 * assumes that there is a DataSource available under the JNDI name
	 * "jdbc/taste", which gives access to a database with a
	 * "taste_preferences" table with the following schema: CREATE TABLE
	 * taste_preferences ( user_id BIGINT NOT NULL, item_id BIGINT NOT NULL,
	 * preference REAL NOT NULL, PRIMARY KEY (user_id, item_id) ) CREATE
	 * INDEX taste_preferences_user_id_index ON taste_preferences (user_id);
	 * CREATE INDEX taste_preferences_item_id_index ON taste_preferences
	 * (item_id);
	 */
	MysqlDataSource dbsource = new MysqlDataSource();
	dbsource.setUser("user");
	dbsource.setPassword("pass");
	dbsource.setServerName("localhost");
	dbsource.setDatabaseName("my_db");

	DataModel dataModelDB = new MySQLJDBCDataModel(dbsource,
			"taste_preferences", "user_id", "item_id", "preference",
			"timestamp");
	
	return dataModelDB;
}
 

public void testBug42267() throws Exception {
    MysqlDataSource ds = new MysqlDataSource();
    ds.setUrl(dbUrl);
    Connection c = ds.getConnection();
    String query = "select 1,2,345";
    PreparedStatement ps = c.prepareStatement(query);
    String psString = ps.toString();
    assertTrue("String representation of wrapped ps should contain query string", psString.endsWith(": " + query));
    ps.close();
    ps.toString();
    c.close();
}
 

@Override
public Class<? extends DataSource> dataSourceClassName() {
    return MysqlDataSource.class;
}