Auto-create MySQL Database in Java

Many Java / JDBC / JPA examples show only how to create DB table(s) if not existing. Like putting the table defintion or DDL in a .sql file, where the program can pick it up and execute on run-time.

But not with the database itself.

How can database be created on the fly?

ANSWER

With JDBC this can be done as a parameter to the URL connection string.

In the example shown below –

datasource.url=jdbc:mysql://localhost:3306/SCHEMA_NAME?createDatabaseIfNotExist=true

The parameter (comes after ?) – createDatabaseIfNotExist – must be set at value of true.

As I recall, this works only after MySQL version 5.1.

Note: SCHEMA is MySQL speak for database. It is common to call that even with other products. Other databases or code implementations may also term it as CATALOG.

Database tables map One To Many without foreign key in @JoinColumn

Possible to not have foreign key relationship in child table to its parent in the database, while in JPA it will still be able to map out fine? How will this be done?

Can I still use @JoinColumn to define the field for reference back to the parent table?

This is a one-to-many relationship. One parent record, many child records.

I only care about writes to the database tables right now.

ANSWER

Yes, no FKs defined in the database is fine. JPA will still be able to map out there relationships as defined in your entity classes.

In fact, enabling – spring.jpa.generate-ddl – in your application properties file will create the necessary Foreign Key and/or Unique Key constraints in the database. This is vendor dependent however.

For a One To Many relationship, it is straightforward to do. Example below definition below:

    @OneToMany(cascade = CascadeType.ALL)
    @JoinColumn(name = "recommendation_id")
    private List<CommentEntity> commentList;

This will map out the entity object values to the Comment table, assuming that is the name of the target table. Each one will be persisted to the child table along with the ID of the parent Recomendation under the recommendation_id column.

Insert blob data with wpdb

I have a shortcode in my WordPress page with which I access the WordPress database with wpdb.

It works perfectly with wpdb-> get_results () and wpdb-> query (), but I am not able to execute an insert with a blob data.

With wpdb:: insert I can’t as it only accepts a string, int and float. With wpdb-> query () it doesn’t work for me either (I even tried with fbsql_read_blob ())

I can insert using the PDO class, but I don’t know if it can be done with any WordPress function. I understand that it is safer and will work better.

Thanks in advance.

Go to Source
Author: alvaroqm

How Do I See the List of Users Allowed on a MS SQL Server Database

I want to know the users that are allowed access to a MS SQL Server database. I only want a specific database. Is there a user list hidden somewhere?

What are the ways to go about doing this? Is there an app or through a SQL query?

ANSWER

Use Microsoft SQL Server Management Studio. That is the fastest way to do it. Find the database from the explorer menu on the left-hand side. Expand the folder icon. Go to the Security folder, then click on Users next. That’s it!

If you’re using a different SQL client and you can’t see Security > Users from the GUI. Then your other option is through a SQL query.

This is the query below. More details at this page here: https://www.joseyamut.xyz/2020/08/06/get-user-list-in-a-microsoft-sql-server-database/.

USE <database_name>;
SELECT name AS username,
       type_desc AS type,
       authentication_type_desc AS auth_type
FROM sys.database_principals
ORDER BY type;

Happy user hunting! =)