The CallableStatement interface provides methods to execute the stored procedures. Since the JDBC API provides a stored procedure SQL escape syntax, you can call stored procedures of all RDBMS in single standard way.Creating a CallableStatementYou can create an object of the CallableStatement (interface) using the prepareCall() method of the Connection interface. This method accepts a string variable representing a query to call the stored procedure and returns a CallableStatement object.A Callable statement can have input parameters, output parameters or both. To pass input parameters to the procedure call you can use place holder and set values to these using the ... Read More

The PreparedStatement interface extends the Statement interface it represents a precompiled SQL statement which can be executed multiple times. This accepts parameterized SQL quires and you can pass 0 or more parameters to this query.Initially this statement uses place holders “?” instead of parameters, later on you can pass arguments to these dynamically using the setXXX() methods of the PreparedStatement interface.Creating a PreparedStatementYou can create an object of the PreparedStatement (interface) using the prepareStatement() method of the Connection interface. This method accepts a query (parameterized) and returns a PreparedStatement object.When you invoke this method the Connection object sends the given ... Read More

The Statement interface represents the static SQL statement, it is used to create and execute general purpose SQL statements using Java programs.Creating a statementYou can create an object of this interface using the createStatement() method of the connection interface. Create a statement by invoking this method as shown below.Statement stmt = null; try {    stmt = conn.createStatement( );    . . . } catch (SQLException e) {    . . . } finally {    . . . }Executing the Statement objectOnce you have created the statement object you can execute it using one of the execute methods namely, ... Read More

You can use aggregate function count along with if() for this. To understand the concept, let us create a table. The query to create a table is as followsmysql> create table CountOccurrencesDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> TechnicalSubject varchar(100)    -> ); Query OK, 0 rows affected (0.68 sec)Now you can insert some records in the table using insert command. The query is as followsmysql> insert into CountOccurrencesDemo(TechnicalSubject) values('Java'); Query OK, 1 row affected (0.14 sec) mysql> insert into CountOccurrencesDemo(TechnicalSubject) values('MongoDB'); Query OK, 1 row affected (0.13 sec) mysql> insert into ... Read More

You need to use REGEXP for this. The syntax is as followsSELECT *FROM yourTableName WHERE yourColumnName REGEXP '[a-zA-Z]';To understand the concept, let us create a table. The query to create a table is as followsmysql> create table SelectNonNumericValue    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> UserId varchar(100)    -> ); Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command. The query is as followsmysql> insert into SelectNonNumericValue(UserId) values('123John'); Query OK, 1 row affected (0.12 sec) mysql> insert into SelectNonNumericValue(UserId) values('58475Carol98457Taylor24'); Query OK, 1 row affected (0.52 sec) ... Read More

You need to use CREATE command to create a new user with password in MySQL 8. Let us check the versionmysql> select version(); +-----------+ | version() | +-----------+ | 8.0.12    | +-----------+ 1 row in set (0.14 sec)The syntax is as follows to create a new user with passwordCREATE USER 'yourUserName'@'localhost' IDENTIFIED BY 'yourPassword';The following is the syntax to grant all privileges to the created userGRANT ALL ON *.* TO 'yourUserName'@'localhost';Now flush the privileges using flush commandflush privileges; Let us create a new user with the help of the above syntax. The query is as followsmysql> use MySQL; Database ... Read More

To understand the MySQL select statement DISTINCT for multiple columns, let us see an example and create a table. The query to create a table is as followsmysql> create table selectDistinctDemo    -> (    -> InstructorId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> StudentId int,    -> TechnicalSubject varchar(100)    -> ); Query OK, 0 rows affected (0.50 sec)Insert some records in the table using insert command. The query is as followsmysql> insert into selectDistinctDemo(StudentId, TechnicalSubject) values(121, 'Java'); Query OK, 1 row affected (0.15 sec) mysql> insert into selectDistinctDemo(StudentId, TechnicalSubject) values(121, 'MongoDB'); Query OK, 1 row affected (0.16 ... Read More

For this, you need to use an INSERT SELECT statement. The syntax is as followsINSERT INTO yourDatabaseName1.yourTableName1(yourColumnName1, yourColumnName2, ....N) SELECT yourColumnName1, yourColumnName2, ....N FROM yourdatabaseName2.yourTableName2;Here, I am using the following two databasessampletestLet us create the first table in the “test” databasemysql> use test; Database changed mysql> create table send    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> Name varchar(20) -> ); Query OK, 0 rows affected (1.19 sec)Insert some records in the first table using insert command. The query is as followsmysql> insert into send(Name) values('John'); Query OK, 1 row affected ... Read More

Let us first create a table. The query to create a table is as followsmysql> create table FirstAndLastDataDemo    -> (    -> EmployeeId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> EmployeeName varchar(20),    -> EmployeeAge int    -> ); Query OK, 0 rows affected (0.59 sec)Insert some records in the table using insert command. The query is as followsmysql> insert into FirstAndLastDataDemo(EmployeeName, EmployeeAge) values('John', 23); Query OK, 1 row affected (0.15 sec) mysql> insert into FirstAndLastDataDemo(EmployeeName, EmployeeAge) values('Bob', 13); Query OK, 1 row affected (0.11 sec) mysql> insert into FirstAndLastDataDemo(EmployeeName, EmployeeAge) values('Larry', 24); Query OK, 1 row affected ... Read More

You can use INFORMATION_SCHEMA.COLUMNS table to display MySQL table name with columns. The syntax is as follows −SELECT DISTINCT TABLE_NAME, Column_Name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'yourDatabaseName';Here, we have a database with the name ‘sample’ with tables. The query is as follows to display table name along with column name −mysql> SELECT DISTINCT TABLE_NAME, Column_Name -> FROM INFORMATION_SCHEMA.COLUMNS -> WHERE TABLE_SCHEMA = 'sample';Output+--------------------------------+-------------------------------+ | TABLE_NAME                     | COLUMN_NAME                   | +--------------------------------+-------------------------------+ | aggregatefunctiondemo          | UserId         ... Read More