You can call the SQL stored procedures using the CallableStatement interface. A Callable statement can have input parameters, output parameters, or both.You 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 procedures and returns a CallableStatement object.Suppose you have a procedure name myProcedure in the database you can prepare a callable statement as://Preparing a CallableStatement CallableStatement cstmt = con.prepareCall("{call myProcedure(?, ?, ?)}");Then you can set values to the place holders using the setter methods of the CallableStatement interface and execute ... Read More

If you are accessing one type of database, such as Oracle, Sybase, or IBM, the preferred driver type is 4.If your Java application is accessing multiple types of databases at the same time, type 3 is the preferred driver.Type 2 drivers are useful in situations, where a type 3 or type 4 driver is not available yet for your database.The type 1 driver is not considered a deployment-level driver, and is typically used for development and testing purposes only.

Once you have created the statement object you can execute it using one of the execute methods of the Statement interface namely, execute(), executeUpdate() and, executeQuery().The execute() method: This method is used to execute SQL DDL statements, it returns a boolean value specifying weather the ResultSet object can be retrieved.Exampleimport java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; public class Example {    public static void main(String args[]) throws SQLException {       //Registering the Driver       DriverManager.registerDriver(new com.mysql.jdbc.Driver());       //Getting the connection       String mysqlUrl = "jdbc:mysql://localhost/sampleDB";       Connection con = ... Read More

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