com.mysql.cj.jdbc.MysqlDataSource Java Examples

/**
 * 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, PropertyKey.USER.getKeyName());
    removeFromRef(asRef, PropertyKey.PASSWORD.getKeyName());
    removeFromRef(asRef, "serverName");
    removeFromRef(asRef, "databaseName");

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

@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("?queryInterceptors=").append(TracingQueryInterceptor.class.getName());
  if (exceptionsTraced) {
    url.append("&exceptionInterceptors=").append(TracingExceptionInterceptor.class.getName());
  }
  url.append("&zipkinServiceName=").append("myservice");
  url.append("&serverTimezone=").append("UTC");

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

@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");
  url.append("&serverTimezone=").append("UTC");

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

private void addTenantConnectionProvider(String tenantId) {
    DataSourceProvider dataSourceProvider = database().dataSourceProvider();

    Properties properties = properties();

    MysqlDataSource tenantDataSource = new MysqlDataSource();
    tenantDataSource.setDatabaseName(tenantId);
    tenantDataSource.setUser(dataSourceProvider.username());
    tenantDataSource.setPassword(dataSourceProvider.password());

    properties.put(
            Environment.DATASOURCE,
            dataSourceProxyType().dataSource(tenantDataSource)
    );

    addTenantConnectionProvider(tenantId, tenantDataSource, properties);
}
 

public MySQLDatabase(String host, int port, String name, String user, String password) {
    super(null);
    this.name = name;

    String params = "useUnicode=true"
            + "&useJDBCCompliantTimezoneShift=true"
            + "&useLegacyDatetimeCode=false"
            + "&serverTimezone=UTC";

    MysqlDataSource mysqlDS = new MysqlDataSource();
    mysqlDS.setURL("jdbc:mysql://" + host + ":" + port + "/" + name + "?" + params);
    mysqlDS.setDatabaseName(name);
    mysqlDS.setUser(user);
    mysqlDS.setPassword(password);
    this.dataSource = mysqlDS;
}
 

public synchronized void tryConnecting() {

		MysqlDataSource dataSource = new MysqlDataSource();
		dataSource.setServerName(this.host);
		dataSource.setPort(port);
		dataSource.setUser(this.dbUsername);
		dataSource.setPassword(this.dbPassword);
		dataSource.setDatabaseName(this.dbName);

		try {
			dataSource.setCharacterEncoding("utf-8");
			connection = dataSource.getConnection();

			setConnected(true);
			onConnected();
			System.out.println("connected to mysql server: " + host + ":" + port);

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
 

@SneakyThrows
public static Connection getMySQLConnection(int port) {
    String username = "root";
    String password = "123456";

    Properties properties = new Properties();
    properties.put("user", username);
    properties.put("password", password);
    properties.put("useBatchMultiSend", "false");
    properties.put("usePipelineAuth", "false");

    String url = "jdbc:mysql://0.0.0.0:" +
            port +
            "/db1?useServerPrepStmts=false&useCursorFetch=false&serverTimezone=UTC&allowMultiQueries=false&useBatchMultiSend=false&characterEncoding=utf8";

    MysqlDataSource mysqlDataSource = new MysqlDataSource();
    mysqlDataSource.setUrl(url);
    mysqlDataSource.setUser(username);
    mysqlDataSource.setPassword(password);

    return mysqlDataSource.getConnection();
}
 

@SneakyThrows
public static Connection getMySQLConnection() {
    String username = "root";
    String password = "123456";

    Properties properties = new Properties();
    properties.put("user", username);
    properties.put("password", password);
    properties.put("useBatchMultiSend", "false");
    properties.put("usePipelineAuth", "false");

    String url = "jdbc:mysql://0.0.0.0:8066/db1?useServerPrepStmts=false&useCursorFetch=false&serverTimezone=UTC&allowMultiQueries=false&useBatchMultiSend=false&characterEncoding=utf8";

    MysqlDataSource mysqlDataSource = new MysqlDataSource();
    mysqlDataSource.setUrl(url);
    mysqlDataSource.setUser(username);
    mysqlDataSource.setPassword(password);

    return mysqlDataSource.getConnection();
}
 

private DataSource createSingle()  {
	final MysqlDataSource dataSource = new MysqlDataSource();

	dataSource.setURL(createURL());
	dataSource.setUser(null == user ? "extract" : user);
	dataSource.setPassword(password);

	dataSource.initializeProperties(createProperties());

	return dataSource;
}
 

/**
 * Ctor.
 * @param hostname Server hostname or IPv4 Address
 * @param port Server port
 * @param dbname Database name
 */
public SourceMysql(
    final String hostname,
    final int port,
    final String dbname
) {
    this.origin = new Unchecked<>(
        new Solid<>(
            () -> {
                final MysqlDataSource mds = new MysqlDataSource();
                mds.setUrl(
                    new FormattedText(
                        new Joined(
                            "",
                            "jdbc:mysql://%s:%d/%s?useSSL=false",
                            "&useTimezone=true&serverTimezone=UTC"
                        ),
                        hostname,
                        port,
                        dbname
                    ).asString()
                );
                return mds;
            }
        )
    );
}
 

protected DataSource getMysqlDataSource() {
MysqlDataSource mysqlDataSource = new MysqlDataSource();
       mysqlDataSource.setURL(DB_URL);
mysqlDataSource.setUser(DB_USER_NAME);
       mysqlDataSource.setPassword(DB_PASSWORD);
return mysqlDataSource;
   }
 

protected DataSource getMysqlDataSource() {
MysqlDataSource mysqlDataSource = new MysqlDataSource();
       mysqlDataSource.setURL(DB_URL);
mysqlDataSource.setUser(DB_USER_NAME);
       mysqlDataSource.setPassword(DB_PASSWORD);
return mysqlDataSource;
   }
 

/**
 * Sets up the {@link InitialContext} for a given configuration.
 * <p>
 * Also adds the database {@link DataSource} to the initial context.
 *
 * @param configurations the configuration used to set the initial context.
 * @throws NamingException when setting the initial context fails.
 */
private static void setupInitialContext(Properties configurations) throws NamingException {
    System.setProperty(Context.INITIAL_CONTEXT_FACTORY, "org.apache.naming.java.javaURLContextFactory");
    System.setProperty(Context.URL_PKG_PREFIXES, "org.apache.naming");

    InitialContext ic = new InitialContext();

    ic.createSubcontext("java:");
    ic.createSubcontext("java:comp");
    ic.createSubcontext("java:comp/env");
    ic.createSubcontext("java:comp/env/codedefenders");

    // Alessio: Maybe there a better way to do it...
    for (String propName : configurations.stringPropertyNames()) {
        logger.info("Setting java:comp/env/codedefenders/" + propName + " = " + configurations.get(propName));
        ic.bind("java:comp/env/codedefenders/" + propName, configurations.get(propName));
    }

    ic.createSubcontext("java:comp/env/jdbc");

    MysqlDataSource dataSource = new MysqlDataSource();
    dataSource.setURL(configurations.getProperty("db.url"));
    dataSource.setUser(configurations.getProperty("db.username"));
    dataSource.setPassword(configurations.getProperty("db.password"));

    ic.bind("java:comp/env/jdbc/codedefenders", dataSource);

    // Maybe there's a way to provide the beans definition directly here...
    Weld weld = new Weld();
    WeldContainer container = weld.initialize();
    // Manually load the dependencies and set the backend in the context, this is only because I cannot inject BeanManager
    BackendExecutorService backend = container.instance().select(BackendExecutorService.class).get();
    ic.bind("java:comp/env/codedefenders/backend", backend);
    // Weld will be automatically closed at system.exit
}
 

@Override
public void afterInit() {
    MysqlDataSource defaultDataSource = (MysqlDataSource) database().dataSourceProvider().dataSource();
    addTenantConnectionProvider(TenantContext.DEFAULT_TENANT_IDENTIFIER, defaultDataSource, properties());

    createCatalog("europe");
    createCatalog("asia");
}
 

@Override
protected DataSource createDataSource() {
    final MySQLContainer container = mysqlContainer();
    final MysqlDataSource dataSource = new MysqlDataSource();
    dataSource.setUrl(container.getJdbcUrl());
    dataSource.setUser(container.getUsername());
    dataSource.setPassword(container.getPassword());
    dataSource.setDatabaseName(container.getDatabaseName());
    return dataSource;
}
 

/**
 * Tests fix for Bug#72632 - NullPointerException for invalid JDBC URL.
 */
public void testBug72632() throws Exception {
    final MysqlDataSource dataSource = new MysqlDataSource();
    dataSource.setUrl("jdbc:mysql:nonsupported:");
    assertThrows(SQLException.class, "Connector/J cannot handle a connection string 'jdbc:mysql:nonsupported:'.", new Callable<Void>() {
        public Void call() throws Exception {
            dataSource.getConnection();
            return null;
        }
    });
}
 

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

/**
 * 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.<PropertyDefinitions.ZeroDatetimeBehavior> getProperty(PropertyKey.zeroDateTimeBehavior)
            .setValue(PropertyDefinitions.ZeroDatetimeBehavior.CONVERT_TO_NULL);

    toSerialize.<String> getProperty(PropertyKey.ha_loadBalanceStrategy).setValue("test_lb_strategy");

    boolean testBooleanFlag = !toSerialize.getBooleanProperty(PropertyKey.allowLoadLocalInfile).getValue();
    toSerialize.<Boolean> getProperty(PropertyKey.allowLoadLocalInfile).setValue(testBooleanFlag);

    RuntimeProperty<Integer> bscs = toSerialize.<Integer> getProperty(PropertyKey.blobSendChunkSize);
    int testIntFlag = bscs.getValue() + 1;
    ((AbstractRuntimeProperty<?>) bscs).setValueInternal(String.valueOf(testIntFlag), null);

    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(PropertyDefinitions.ZeroDatetimeBehavior.CONVERT_TO_NULL, thawedDs.getEnumProperty(PropertyKey.zeroDateTimeBehavior).getValue());
    assertEquals("test_lb_strategy", thawedDs.getStringProperty(PropertyKey.ha_loadBalanceStrategy).getValue());
    assertEquals(testBooleanFlag, thawedDs.getBooleanProperty(PropertyKey.allowLoadLocalInfile).getValue().booleanValue());
    assertEquals(testIntFlag, thawedDs.getMemorySizeProperty(PropertyKey.blobSendChunkSize).getValue().intValue());
}
 

public static void main(String[] args) throws Exception {
    ClassPool pool = ClassPool.getDefault();
    pool.insertClassPath(args[0]);

    System.out.println("---");
    CtClass clazz = pool.get(MysqlDataSource.class.getName());
    System.out.println("Add properties setters/getters to " + clazz.getName());
    addPropertiesGettersSetters(clazz, PropertyDefinitions.PROPERTY_KEY_TO_PROPERTY_DEFINITION.values());
    clazz.writeFile(args[0]);

}
 

@SneakyThrows
public static void main(String[] args) {
    String username = "root";
    String password = "123456";

    Properties properties = new Properties();
    properties.put("user", username);
    properties.put("password", password);
    properties.put("useBatchMultiSend", "false");
    properties.put("usePipelineAuth", "false");

    String url = "jdbc:mysql://0.0.0.0:3306/db1?useServerPrepStmts=false&useCursorFetch=true&serverTimezone=UTC&allowMultiQueries=false&useBatchMultiSend=false&characterEncoding=utf8";

    MysqlDataSource mysqlDataSource = new MysqlDataSource();
    mysqlDataSource.setUrl(url);
    mysqlDataSource.setUser(username);
    mysqlDataSource.setPassword(password);

    MySQLVariableObjectMapWrapper mapWrapper = new MySQLVariableObjectMapWrapper();
    try (Connection connection = DriverManager.getConnection(url, properties)) {
        try (Statement statement = connection.createStatement()) {
            for (MySQLVariablesEnum value : MySQLVariablesEnum.values()) {
                String[] columnNames = value.getColumnNames();

                try {
                    for (String columnName : columnNames) {
                        ResultSet resultSet = statement.executeQuery("select " + columnName);
                        while (resultSet.next()) {
                            ResultSetMetaData metaData = resultSet.getMetaData();
                            JDBCType jdbcType = JDBCType.valueOf(metaData.getColumnType(1));
                            Object object = resultSet.getObject(1);
                            Class<?> aClass = null;
                            if (object != null) {
                                aClass = object.getClass();
                            }
                            MySQLVariableObject mySQLVariableObject = new MySQLVariableObject(value, columnName, jdbcType, aClass, object);
                            mapWrapper.map.put(mySQLVariableObject.getColumnName(), mySQLVariableObject);
                            System.out.println(mySQLVariableObject);
                        }
                    }

                } catch (Exception e) {
                    e.printStackTrace();
                    System.err.println("error:" + columnNames);
                }
            }


        }
    }
    String s = JsonUtil.toJson(mapWrapper);
    MySQLVariableObjectMapWrapper from = JsonUtil.from(s, MySQLVariableObjectMapWrapper.class);
    System.out.println(s);
}
 

@SneakyThrows
public static void main(String[] args) {
    String username = "root";
    String password = "123456";

    Properties properties = new Properties();
    properties.put("user", username);
    properties.put("password", password);
    properties.put("useBatchMultiSend", "false");
    properties.put("usePipelineAuth", "false");

    String url = "jdbc:mysql://0.0.0.0:3306/db1?useServerPrepStmts=false&useCursorFetch=true&serverTimezone=UTC&allowMultiQueries=false&useBatchMultiSend=false&characterEncoding=utf8";

    MysqlDataSource mysqlDataSource = new MysqlDataSource();
    mysqlDataSource.setUrl(url);
    mysqlDataSource.setUser(username);
    mysqlDataSource.setPassword(password);

    try (Connection connection = DriverManager.getConnection(url, properties)) {
        try (Statement statement = connection.createStatement()) {
            ResultSet resultSet = statement.executeQuery("SELECT * FROM information_schema.COLUMNS  ");
                ResultSetMetaData metaData = resultSet.getMetaData();
                int columnCount = metaData.getColumnCount();
            resultSet.next();
            for (int i = 1; i <=columnCount ; i++) {
                String columnName = metaData.getColumnName(i);
                JDBCType jdbcType = JDBCType.valueOf(metaData.getColumnType(i));
                Object object = null;
                try {
                    object  = resultSet.getObject(i);
                }catch (Exception e){

                }
                Class<?> aClass = null;
                if (object != null) {
                    aClass = object.getClass();
                }
                System.out.println(""+ Optional.ofNullable(aClass).map(j->j.getSimpleName()).orElse(jdbcType.getName())+" "+columnName+" ;");
            }
        }
    }
}
 

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

@Test
public void tx2() {
	
	/*
	 
	 Fish fish = aa.find(Fish.class, id);
			assertThat(fish.getId(), equalTo(id));
			assertThat(fish.getName(), not(equalTo(name)));
			assertThat(fish.getNum(), not(equalTo(num)));
	 */
	
	
	Integer id = 1;
	String name = "乌龟";
	Integer num = 300;
	int effect = aa.tx(()->{
		
			Fish fish = aa.update(new Fish(id, name, num));
			assertThat(fish.getId(), equalTo(id));
			assertThat(fish.getName(), equalTo(name));
			assertThat(fish.getNum(), equalTo(num));
			assertThat(aa.findDatabaseName(), equalTo("aa"));
			assertThat(aa.findDatabasePort().get("Value"), equalTo("3306"));
			
			fish = bb.update(new Fish(id, name, num));
			assertThat(fish.getId(), equalTo(id));
			assertThat(fish.getName(), equalTo(name));
			assertThat(fish.getNum(), equalTo(num));
			assertThat(bb.findDatabaseName(), equalTo("bb"));
			assertThat(bb.findDatabasePort().get("Value"), equalTo("3306"));
			
			aa.save(new Fish(name, num));
			bb.save(new Fish(name, num));
			cc.save(new Fish(name, num));
			
			fish = cc.update(new Fish(id, name, num));
			assertThat(fish.getId(), equalTo(id));
			assertThat(fish.getName(), equalTo(name));
			assertThat(fish.getNum(), equalTo(num));
			assertThat(cc.findDatabaseName(), equalTo("cc"));
			assertThat(cc.findDatabasePort().get("Value"), equalTo("3307"));
			
			int i = aa.delete("Fish", "id", 1);
			assertThat(i, is(1));
			i = bb.delete("Fish", "id", 1);
			assertThat(i, is(1));
			i = cc.delete("Fish", "id", 1);
			assertThat(i, is(1));
			
			// 断言目前的连接数
			assertThat(getDclist().size(), is(3));
			
			// 断言数据源信息
			DruidDataSource dds = getDataSource(DruidDataSource.class);
			assertThat(dds, notNullValue());
			assertThat(dds.getUsername(), equalTo("aa"));
			assertThat(dds.getPassword(), equalTo("aa123456"));
			
			ComboPooledDataSource cpds = getDataSource(ComboPooledDataSource.class);
			assertThat(cpds, notNullValue());
			assertThat(cpds.getDataSourceName(), equalTo("bb"));
			assertThat(cpds.getUser(), equalTo("bb"));
			assertThat(cpds.getPassword(), equalTo("bb123456"));
			
			
			MysqlDataSource mds = getDataSource(MysqlDataSource.class);
			assertThat(mds, notNullValue());
			assertThat(mds.getUser(), equalTo("cc"));
			assertThat(mds.getDatabaseName(), equalTo("cc"));
			assertThat(mds.getPassword(), equalTo("cc123456"));

			throw new RepositoryException("模拟异常");
	});
	
	assertThat(effect, is(-1));
}
 

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

/**
 * To open connection by providing all of the properties.
 *
 * @param host String
 * @param port String
 * @param user String
 * @param pass String
 * @return Connection
 * @throws SQLException e
 */
private Connection _openConnection(String host, String port, String user, String pass) throws SQLException {

    MysqlDataSource dataSource = new MysqlDataSource();
    dataSource.setUseSSL(false);
    dataSource.setURL(_URL + host + Constants.SYMBOL_COLON + port + Constants.SYMBOL_BACK_SLASH);
    dataSource.setUser(user);
    dataSource.setPassword(pass);
    return dataSource.getConnection();
}