IT adventure / KJ in IT: 2013-03-03

Friday, March 8, 2013

Simple connecting to MySQL with PHP

How to connect to MySQL in PHP?
It's simple.

$username = "root";
$password = "password";
$host = "localhost"; 

$dbhandle = mysql_connect($host, $username, $password) 
     or die("Unable to connect to MySQL");
echo "Connected to MySQL";

$selected = mysql_select_db("dbName", $dbhandle) 
    or die("There is no database");

$result = mysql_query("SELECT model FROM cars");

while($row = mysql_fetch_array($result)) 
{
    echo "MODEL: ".$row['model'];
}

mysql_close($dbhandle);

That is all. Simple?

Newer and better way to connect:

$db = new PDO( 'mysql:host=localhost; dbname=MyDbName; charset=utf8', 'username', 'password' );

$statement = $db -> query('SELECT model FROM cars');
foreach($statement as $row)
{
    echo $row['model'];
}

jQuery load(). Dynamic load content without refreshing page

How to upload file content from other file using jQuery load() function without refreshing page?
You can do it using only AJAX or shortcut from jQuery.

Downlod jQuery file from here and put in index.html file within<head> </head> or paste path to jQuery file in internet.

When you click "Get cars" link it calls function load().

1. File index.html - our main file



Get cars


    Place to load data from other file...

2. File cars.html - from this file content will be loaded.


    
        Opel
        Ford
        BMW
        Mercedes

Java / Connect to MySQL

How connect to MySQL database using JDBC MySQL connector?
Download MySQL connector

Add connector to your Java project. It is simple - just click right mouse button on project and choose "Build Path" --> "Configure Build Path" --> "Java Build Path" and add connector *.jar library.

1. Here is simple, short example how to register and connect do MySQL via JDBC connector:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBexample 
{   
 public static void main(String[] args) throws SQLException 
 {
  Connection conn = null;
  
  try 
  {   
   Class.forName("com.mysql.jdbc.Driver"); 
   
   try 
   {
    conn = DriverManager.getConnection("jdbc:mysql://localhost/", "root", "password");
   } 
   catch (SQLException e) 
   {
    System.out.println("Check DB link / user / password");
    e.printStackTrace();
   }
  }
  catch(ClassNotFoundException e) 
  {
   System.out.println("There is no connector");
   System.out.println(e.getMessage());
   System.exit(-1);
  }
  
  System.out.println("Connector registered");
  
  conn.close();
 }
}

That's all!

Thursday, March 7, 2013

Connect to MySQL, PostgreSQL using JDBC

Now I will show you how to connect to MySQL and PostgreSQL database using JDBC connector.
We need connector.
Download MySQL connector.
Download PostgreSQL connector.

In Eclipse:
Right click on your Java project -> Buil Path -> Configure Build Path -> Java Build Path and add your jar

1. Short example (for MySQL):

Connection con = null;
Statement stmt;
ResultSet rs;

Class.forName( "com.mysql.jdbc.Driver" ).newInstance();
con = DriverManager.getConnection( "jdbc:mysql://hostname:port/dbname","username", "password" );
stmt = con.createStatement();

con.close();
stmt.close();
rs.close();

2. Full example:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBexample
{
 String dbLogin, dbPswd, dbUrl;
 
 Connection con = null;
 Statement stmt;
 ResultSet rs;
 
 /* There are two database systems:
  * - mysql
  * - pg */
 public DBexample( String databaseSystem )
 {
  if( databaseSystem.equals( "mysql" ) )
  {
   this.dbLogin = "root";
   this.dbPswd = "password";
   this.dbUrl = "jdbc:mysql://localhost:3306/dbname";
   
   try
   {
    Class.forName( "com.mysql.jdbc.Driver" ).newInstance();
    con = DriverManager.getConnection( this.dbUrl, this.dbLogin, this.dbPswd );
    stmt = con.createStatement();
   }
   catch (Exception e) 
   {
    System.out.println( "There is no connector available." );
    return;
   }  
   
   System.out.println( "Connector registered" );
   
  }
  else if( databaseSystem.equals( "pg" ) )
  {
   this.dbLogin = "root";
   this.dbPswd = "password";
   this.dbUrl = "jdbc:postgresql://localhost:5432/dbname";
   
   try
   {
    Class.forName( "org.postgresql.Driver" );
    con = DriverManager.getConnection( this.dbUrl, this.dbLogin, this.dbPswd );
    stmt = con.createStatement();
   }
   catch (Exception e)
   {
    System.out.println( "There is no connector available." );
    return;
   }
   
   System.out.println( "Connector registered" );
  }  
 }
 
 /* Execute sql: update, insert, delete... */
 public void executeUpdate(String aQuery) throws SQLException
 {
  stmt.executeUpdate(aQuery);
 }
 
 /* Execute sql: select... and get result */
 public ResultSet executeSelect( String aQuery )
 {
  try 
  {
   rs = stmt.executeQuery(aQuery);
  } 
  catch (SQLException e) 
  {  
   e.printStackTrace();
  }    
  return rs;
 }
 
 public void closeConnection() throws SQLException
 {
  con.close();
  stmt.close();
  rs.close();
 }
 
 /* Test */
 public static void main(String[] argv) throws SQLException 
 {
  ResultSet rs;
  
  /* Connect to MySQL and get all cars */
  DBexample dbMySQL = new DBexample("mysql");
  rs = dbMySQL.executeSelect("SELECT * FROM cars");  
  
  try 
  {
   while( rs.next() )
   {
    String carModel = rs.getString( "model" );
    System.out.println( carModel );   
   }
  } 
  catch (SQLException e) 
  {  
   e.printStackTrace();
  }
  /* close connection */
  dbMySQL.closeConnection();
  
  /* *********************************************************************** */
  
  /* Connect to PostgreSQL and get all users */
  DBexample dbPg = new DBexample("pg");
  rs = dbPg.executeSelect("SELECT * FROM users");
  
  try 
  {
   while( rs.next() )
   {
    String userName = rs.getString( "userName" );
    System.out.println( userName );   
   }
  } 
  catch (SQLException e) 
  {  
   e.printStackTrace();
  }
  /* close connection */
  dbPg.closeConnection();
 }
}

blogorama.com