Found 4219 Articles for MySQLi

To insert a record in the table using PreparedStatement in Java, you need to use below syntax to insert records. The syntax is as follows −String anyVariableName= "INSERT INTO yourTableName(yourColumnName1, yourColumnName2, yourColumnName3, .........N)" + "VALUES (?, ?, ?, ..............N)";Now use the PreparedStatement object to set the value for all columns. The syntax is as follows −PreparedstatementObject = con.prepareStatement(query); PreparedstatementObject .setXXX(1, yourValue); PreparedstatementObject .setXXX(2, yourValue); PreparedstatementObject .setXXX(3, yourValue); . . . NThe above prepared statement will solve your problem. Now first create a table in MySQL. The query to create a table is as follows −mysql> create table CourseDemo -> ( ... Read More

To count the unique values on a column, you need to use keyword DISTINCT. To understand how it is done, let us create a table. The query to create a table is as follows −mysql> create table UniqueCountByIPAddress    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> UserHits int,    -> UserIPAddress varchar(50),    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.69 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into UniqueCountByIPAddress(UserHits, UserIPAddress) values(10, '127.0.0.1'); Query OK, 1 row affected (0.20 sec) mysql> insert into ... Read More

You can use GROUP BY clause and COUNT() function for this. The syntax is as follows −SELECT yourColumnName1, yourColumnName2, ..N, COUNT(*) as anyAliasName FROM yourTableName GROUP BY yourColumnName1, yourColumnName2;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table GroupAndCountByDate    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> TripDate date,    -> ShopId int,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.79 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> ... Read More

You will get this type of exception whenever your JDBC URL is not accepted by any of the loaded JDBC drivers by the method acceptsURL. You need to mention the MySQL JDBC driver which is as follows −The MySQL JDBC url is as follows −jdbc:mysql://localhost:3306/test?useSSL=falseThe prototype of acceptsURL is as follows −boolean acceptsURL(String url) throws SQLExceptionThe acceptsURL returns boolean that means if the JDBC driver understands the database URL it returns true otherwise false. It takes one parameter of type String which is a database URL.The entire database URL connection is as follows. The syntax −con = DriverManager. getConnection("jdbc:mysql://localhost:3306/yourDatabaseName?useSSL=false", "yourUserName", ... Read More

You can sort the table_name property from INFORMATION_SCHEMA.TABLES with ORDER BY clause. Sort in ascending order or descending order with the help of ASC or DESC respectively. The syntax is as follows −SELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema='yourDatabaseName' ORDER BY table_name DESC;Use the database with the name sample and have some tables. First, we will show all tables after that we will apply to sort on the table name. The query to display all tables is as follows −mysql> show tables;The following is the output −+--------------------------+ | Tables_in_sample         | +--------------------------+ | ... Read More

You need to use LOCATE() along with SUBSTR(). The below syntax will find the word after delimiter. Here, delimiter is colon(:), you can use another i.e. it is up to you. The syntax is as follows −SELECT SUBSTR(yourColumnName, LOCATE(':', yourColumnName)+1, (CHAR_LENGTH(yourColumnName) - LOCATE(':', REVERSE(yourColumnName)) - LOCATE(':', yourColumnName))) AS anyAliasName FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table SearchTextBetweenDelimitersDemo -> ( -> ... Read More

No, MySQL won’t improve search performance whenever you have leading wildcards because MySQL will be unable to use the index. If you change to ‘anyLetter%’ then it will be able to use indexThe below syntax is better to use with trailing wildcards. The syntax is as follows −SELECT *FROM yourTableName WHERE yoorColumnName LIKE ‘anyLetter%’;The query to create a table is as follows −mysql> create table TrailingWildCardDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name Varchar(20), -> PRIMARY KEY(Id) -> ); Query OK, 0 ... Read More

In PHP, the empty string equals to a NULL value, but in MySQL, the case is the different i.e. empty string is not equal to NULL value. To understand the above syntax, let us create a column with NOT NULL constraint while you can insert an empty string.Let us create a table. The query to create a table is as follows −mysql> create table EmptyStringNotNullDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Name varchar(10) not null,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.83 sec)Now you can insert some records ... Read More

To create column names to be used in a query, you need to use a user-defined variable with the set command. The syntax is as follows −SET @anyVariableName := (    SELECT CONCAT    (       "SELECT",    GROUP_CONCAT(CONCAT(" 1 as ", COLUMN_NAME) SEPARATOR ', '), " FROM DUAL")    FROM INFORMATION_SCHEMA_COLUMNS    WHERE TABLE_NAME= ‘yourTableName’ );Now prepare the statement using the PREPARE command. The syntax is as follows −PREPARE anyVariableName from @anyVariableName;Execute statement using EXECUTE command. The syntax is as follows −EXECUTE anyVariableName;Deallocate the prepared statement using DEALLOCATE command. The syntax is as follows −DEALLOCATE PREPARE anyVariableName; ... Read More

To get the column name on the result set, you need to use getMetaData() method. The prototype of getMetadata() is as follows −ResultSetMetaData getMetaData throws SQLException;Create a MySQL table with 5 column names. The query to create a table is as follows −mysql> create table javagetallcolumnnames    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Name varchar(20),    -> Age int,    -> Salary float,    -> Address varchar(100),    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (1.34 sec)The following is the Java code that gets the column name on ResultSet. The code ... Read More