JDBC java and mysql

first you must include the sql package:

import java.sql.*;

In order to word with JDBC driver for mysql, you first need to load the driver:

String driver = “com.mysql.jdbc.Driver”;
Class.forName(driver).newInstance();

Next there are minimum of item you must declare:

Connection conn = null;
Statement st = null;

Now, lets connect:

String url = “jdbc:mysql://” + server_name + “:3306/”;

try
        {
            conn = DriverManager.getConnection(url + db_name, username, password);
        } catch (SQLException ex)
        {
            Logger.getLogger(MySQLtest.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println("fail to open mysql connection");
            return;
        }

where password, username, server_name and db_name are all strings you get from user input/file and so on.

next you need to create a statement:

try
        {

            st = conn.createStatement();
        } catch (SQLException ex)
        {
            Logger.getLogger(MySQLtest.class.getName()).log(Level.SEVERE, null, ex);
            return;
        }

to view results or record sets, you can call the Execute XXX method, where XXX stands for the specific flavour you need (create, update, count or select).

more info on the statement class here.

 

 

MySQL common commands

Some mySQL commands you want to keep under you hand.

Connecting to a mySQL server:
mysql -h host_address -u user -p

Viewing all the databases:
show databases;

create a database:

create database my_db;

Select a database to use:
use mydatabase;

show db tables (db must be selected):
show tables;

describe the table content:
describe new_table;

Selecting all the users in the system:

select host, user, password from mysql.user;

Creating a user:
CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';

where you can omit theIDENTIFIED BY if password is not required (but it usually is).

Deleting a user:
DROP USER user;

Assigning a password:

UPDATE mysql.user SET Password=PASSWORD('newpass')
  WHERE User='bob' AND Host='%.loc.gov';
FLUSH PRIVILEGES;

or

GRANT USAGE ON *.* TO 'bob'@'%.loc.gov' IDENTIFIED BY 'newpass';

grant privileges:
GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'
where you can replace ALL PRIVILEGES with any privilege that you like.

Revoking privileges:

revoke all privileges  on databasename.* from user;
again, you can replace ALL PRIVILEGES with any privilege that you like.

Show user privileges:

show grants for 'user'@'host';

Clean table – this will remove all the records in the table. unlike the Delete command, this will also reset any counters on the table.

TRUNCATE TABLE tablename;

Using mysql and .NET

Creating a connection String:

String connectionString = String.Format("Data Source={0};Database={1};
User ID={2};Password={3}", host, DBCatalog, username, password);

creating a connection:

mainConnection = new MySqlConnection(connectionString);

opening a connection:

mainConnection.Open();

Execute non query (one that does not return any records):

String theQuery ="select....";
DbCommand cmd = Connection.CreateCommand();
cmd.CommandText = theQuery;
cmd.ExecuteNonQuery();

Returning a simple reader:

String theQuery ="select....";
DbCommand cmd = Connection.CreateCommand();
cmd.CommandText = theQuery;   
cmd.ExecuteReader();

Returning a full-pledge record set:

DbDataAdapter dap=new MySqlDataAdapter(theQuery, mainConnection);
DataSet ds = new DataSet();
dap.Fill(ds);