Programmer's Blog

Programmer's reference

Category Archives: MySQL

[JDBC] Prepared Statement with HashMap Example

//for demonstration, try catch phrases are removed

public void updateCoffeeSales(HashMap<String, Integer> salesForWeek) {

    PreparedStatement updateSales = null;
    String updateString = "update " + dbName + ".COFFEES " +
                          "set SALES = ? where COF_NAME = ?";

    con.setAutoCommit(false);    //con is java.sql.jdbc.Connection
    updateSales = con.prepareStatement(updateString);

    //Map fields <COF_NAME, SALES>
    for (Map.Entry<String, Integer> e : salesForWeek.entrySet()) {
            updateSales.setInt(1, e.getValue().intValue());  //SALES
            updateSales.setString(2, e.getKey());   //COF_NAME
            updateSales.executeUpdate();
            con.commit();
     }

     if (updateSales != null) {
          updateSales.close();
     }
     con.setAutoCommit(true);
}
Advertisements

[JDBC] Simple Query Statement

Complete Example for simple statement, note that it is using com.mysql.jdbc Connections from mysql-connector-java (Connector/J)

Configuring class path in windows if you install using a MSI, use the following path:

C:\Progra~2\MySQL\MySQL~1  , where Progra~2 refers to Program Files (x86)

package com.testing.testing;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import com.mysql.jdbc.Statement;
import com.mysql.jdbc.Connection;

public class Testing {
    
        public void getData() throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException {

            Class.forName("com.mysql.jdbc.Driver");
            
            com.mysql.jdbc.Connection conn = null;
            conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "user", "passwd");
                
            System.out.println("Connected to database");

            //Begin Statement---------------------------------
            Statement stmt = (Statement) conn.createStatement();
            String query = " Select * from mytable";
            ResultSet rs = stmt.executeQuery(query);
            while(rs.next()) {
                
                int id = rs.getInt("ID");
                String name = rs.getString("NAME");
                System.out.println("Record: " + id + " " + name);
            }            
           //End Statement---------------------------------
           conn.close();
}

        public static void main(String[] args) throws InstantiationException, IllegalAccessException, ClassNotFoundException {
            
            Testing mytest = new Testing();
            try {        
                mytest.getData();
            } catch (Exception e) 
            {
                System.out.println("SQL ERROR!");
                e.printStackTrace();
            }
        }
}

[JDBC] Connection to MySQL

//mysql-connector-java library is needed
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import com.mysql.jdbc.Statement;
import com.mysql.jdbc.Connection;
public class myclass{

    public Connection getConnection() throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException {

            Class.forName("com.mysql.jdbc.Driver").newInstance();
            com.mysql.jdbc.Connection conn = null;
         
            conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/dbname", "user", "passwd");
                
            System.out.println("Connected to database");
            return conn;
        }
    ...
    ... conn.close();
}


/* For setting parametered login, use Properties object

            Properties connectionProps = new Properties();
            connectionProps.put("user", varUser);
            connectionProps.put("password", varPasswd);
            ...
            conn = (Connection) DriverManager.getConnection(varURL, connectionProps);

*/

[Mysql] check mysql users

SQL> SELECT * FROM mysql.user;

[Mysql] user administration

SQL> CREATE USER 'user'@'localhost' IDENTIFIED BY 'password'
SQL> GRANT ALL PRIVILEGES ON * . * TO 'user'@'localhost' WITH GRANT OPTION;
SQL> FLUSH PRIVILEGES;

Then login by 
$>mysql -u user -p password

SQL> REVOKE ALL PRIVILEGES ON * . * TO 'user'@'localhost';
SQL> DROP USER 'user'@'localhost';


[Mysql] Shell script for running SQL

login="mysql -uroot john"

//run sql statement
login -e "select * from mytable"

//run sql statement with variables
sql="insert into mytable (col1,col2) values ('$var1','$var2');

$login -e "$sql"

[Mysql] Check number of records for all Tables

SELECT table_name, table_rows 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = '<database name>';

[OracleDB] Drop and add constraints

To ensure integrity drop option is placed before add option

ALTER TABLE <TABLENAME> DROP CONSTRAINT pk_primkey;

ALTER TABLE <TABLENAME> ADD CONSTRAINT pk_primkey primary key (id);

[Mysql] install DB with directory configuration

The following code is the install script for mysql with specification of directories.

p.s. mysql version 5.6

$> bin/mysql_install_db --user=mysql \
         --basedir=/opt/mysql/mysql \
         --datadir=/opt/mysql/mysql/data

[Linux] Mysql startup shell script

Execute below script as mysql user to start mysql.

//————————

./bin/mysqld_safe 
--defaults-file=/usr/local/mysql/my.cnf 
--basedir=/usr/local/mysql 
--datadir=/var/log/.....<require log path>....../mysql 
--user mysql