Month: July 2021

JDBC with MS Access Database in Java 8

by
  • Since JDK 8, Java does not support ODBC driver for JDBC connectivity.
  • MS Access does not provide driver for JDK 8, therefore third party driver which is “UCanAccess” can be used to connect with MS Access database.
  • Download UCanAccess Driver.
  • Following is the way to connect with MSAccess database.
  • Please create the following table in Access : Student (Sno number, Sname Text(50), Scity(50))
import java.sql.*;

public class Main {

	public static void main(String[] args) throws Exception
	{		
		Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
		Connection conn = 
			DriverManager.getConnection("jdbc:ucanaccess://C:/raviroza/ravi.mdb");			
		String Q = "Insert into Student values (101,'raviroza.com','world wide web')";
		if(conn.createStatement().executeUpdate(Q)>0)
		{
			System.out.println("Recored added");
		}
	}
}

What is JDBC Prepared Statement?

by
  • Sometimes it is more convenient to use a PreparedStatement object for sending SQL statements to the database. This special type of statement is derived from the more general class, Statement, that you already know.
  • If you want to execute a Statement object many times, it usually reduces execution time to use a PreparedStatement object instead.

Android PHP MySql Service example

by

Here is the example of PHP service for Android application, PHP service allows an android app to access remote data from the database server

Example is divided in two parts

  • Android : contains the files to be created for Android Application
    1. Java file for Android Main Activity file
    2. Java file to add a record to MySql database table
    3. Java POJO file for Student details
  • PHP: contains the files to be created for PHP service
    1. PHP file to create database and table
    2. PHP file to insert record
    3. PHP file to select data in JSON format

Android Files

AndyPhpMainActivity.java

package com.example.andyphpservice;

import android.os.Bundle;
import android.app.Activity;
import android.view.Menu;
import android.view.View;
import android.widget.EditText;
import android.widget.TextView;

public class AndyPhpMainActivity extends Activity
{
	final String URLSelect = "http://10.0.2.2:81/Andy/select.php";
	final String URLInsert = "http://10.0.2.2:81/Andy/insert.php";
	
	EditText edSno,edName,edCity;
	TextView tvData;
		
    @Override
    protected void onCreate(Bundle savedInstanceState)
    {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_andy_php_main);
                       
        edSno = (EditText) findViewById(R.id.txtSno);
        edName = (EditText) findViewById(R.id.txtSname);
        edCity = (EditText) findViewById(R.id.txtCity);
        
        tvData = (TextView) findViewById(R.id.txtData);
        
        new DBSelect(getApplicationContext(),URLSelect, tvData).execute();
    }      
    public void add(View v)
    {
    	// add record to student table
    	Student stu = 
    				new Student(edSno.getText().toString(),
    					edName.getText().toString(),
    					edCity.getText().toString());
    	DBInsert ins = new DBInsert(getApplicationContext(), URLInsert, stu);
    	ins.execute();    	
    	new DBSelect(getApplicationContext(),URLSelect, tvData).execute();
    }
    public void refresh(View v)
    {
    	// refresh data in text view
    	new DBSelect(getApplicationContext(),URLSelect, tvData).execute();    	
    }
}

Student.java

package com.example.andyphpservice;

public class Student
{
	private String sno,sname,scity;
	
	public Student(String sno, String sname, String scity) {
		super();
		this.sno = sno;
		this.sname = sname;
		this.scity = scity;
	}

	public String getSno() {
		return sno;
	}

	public void setSno(String sno) {
		this.sno = sno;
	}

	public String getSname() {
		return sname;
	}

	public void setSname(String sname) {
		this.sname = sname;
	}

	public String getScity() {
		return scity;
	}

	public void setScity(String scity) {
		this.scity = scity;
	}
}

DBInsert.java

package com.example.andyphpservice;

import java.util.ArrayList;
import java.util.List;

import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
import org.apache.http.NameValuePair;
import org.apache.http.client.HttpClient;
import org.apache.http.client.entity.UrlEncodedFormEntity;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.impl.client.DefaultHttpClient;
import org.apache.http.message.BasicNameValuePair;

import android.content.Context;
import android.os.AsyncTask;
import android.util.Log;
import android.widget.Toast;

public class DBInsert extends AsyncTask<Void, Void, Void>
{
	String url;
	Student stu;
	
	Context cx;
	public DBInsert(Context cx, String url, Student stu)
	{
		this.url = url;
		this.stu = stu;
		this.cx = cx;
	}
	@Override
	protected Void doInBackground(Void... arg0)
	{
		List<NameValuePair> nameValuePairs = new ArrayList<NameValuePair>();
        nameValuePairs.add(new BasicNameValuePair("sno", stu.getSno()));
        nameValuePairs.add(new BasicNameValuePair("sname", stu.getSname()));
        nameValuePairs.add(new BasicNameValuePair("scity",stu.getScity())); 
        
        try
        {
            HttpClient httpClient = new DefaultHttpClient();
            HttpPost httpPost = new HttpPost(url);
            httpPost.setEntity(new UrlEncodedFormEntity(nameValuePairs));
            HttpResponse response = httpClient.execute(httpPost); 
            //HttpEntity entity = response.getEntity();
            Log.e("DBInsert", "insert is in progress");            
        }
        catch (Exception e)
        {
        	Log.e("DBInsert", e.toString());           
        }
		return null;
	}
	@Override
	protected void onPostExecute(Void result) {
		// TODO Auto-generated method stub
		super.onPostExecute(result);
		Toast.makeText(cx, "data inserted ! ", Toast.LENGTH_LONG).show();
		Log.e("DBInsert", "insert success");
	}
}

DBSelect.java

package com.example.andyphpservice;

import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.List;

import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
import org.apache.http.NameValuePair;
import org.apache.http.client.HttpClient;
import org.apache.http.client.entity.UrlEncodedFormEntity;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.impl.client.DefaultHttpClient;
import org.apache.http.message.BasicNameValuePair;
import org.json.JSONArray;
import org.json.JSONObject;

import android.content.Context;
import android.os.AsyncTask;
import android.util.Log;
import android.widget.TextView;
import android.widget.Toast;

public class DBSelect extends AsyncTask<Void, Void, Void>
{
	String url;
	Student stu;
	
	InputStream is = null;
	String line = null;
	String result = null;
	List<String> list=null;
	
	TextView tv;
	
	String st = "";
	Context cx;
	
	public DBSelect(Context cx, String url, TextView tvData)
	{
		this.url = url;
		this.tv = tvData;
		this.cx = cx;
		//this.stu = stu;
	}
	@Override
	protected Void doInBackground(Void... arg0)
	{
		try
		{				
			HttpClient httpClient = new DefaultHttpClient();				
			HttpPost httpPost = new HttpPost(url);
			HttpResponse response = httpClient.execute(httpPost);
			HttpEntity entity = response.getEntity();
			is = entity.getContent();
		
			BufferedReader reader = new BufferedReader 
					(new InputStreamReader(is, "iso-8859-1"), 8);
			StringBuilder sb = new StringBuilder();
			while ((line = reader.readLine()) != null)
			{
				sb.append(line + "\n");
			}
			is.close();
			result = sb.toString();
				
			JSONArray ja = new JSONArray(result);
			JSONObject jo = null;
			list = new ArrayList<String>();
			for (int i = 0; i < ja.length(); i++)
			{
				jo = ja.getJSONObject(i);		
				st = st + jo.getInt("sno") + " | " +
						jo.getString("sname") + " | " +
						jo.getString("scity") + "\n";
//				list.add(jo.getInt("sno")+" | "+ 
//						jo.getString("sname")+" | "+
//						jo.getString("scity")
//						);
			}
		}
		catch (Exception e)
		{	Log.e("Db Select", e.toString());	}
		return null;
	}
	@Override
	protected void onPostExecute(Void result) {
		// TODO Auto-generated method stub
		super.onPostExecute(result);
		//tv.setText(list.toString());
		Toast.makeText(cx, "Data Refreshed", Toast.LENGTH_LONG).show();
		tv.setText(st);
		Log.e("DBSelect", "select success");
	}
}

activity_andy_php_main.xml

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:id="@+id/LinearLayout1"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    tools:context=".AndyPhpMainActivity" >
       
    <EditText
        android:id="@+id/txtSno"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:ems="10"
        android:hint="student no." />
		<requestFocus />
		
    <EditText
        android:id="@+id/txtSname"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:ems="10"
        android:hint="student name" />
    
    <EditText
        android:id="@+id/txtCity"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="student city"
        android:ems="10" >
        
    </EditText>

    <Button
        android:id="@+id/btnAdd"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="Add" 
        android:onClick="add"
        />
    
    <Button
        android:id="@+id/btnRefresh"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="Refresh"
        android:onClick="refresh"
         />

    <ScrollView
        android:id="@+id/scrollView1"
        android:layout_width="match_parent"
        android:layout_height="wrap_content" >

        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="match_parent"
            android:orientation="vertical"
             >
            
            <TextView
            android:id="@+id/txtData"
            android:layout_width="match_parent"
            android:layout_height="match_parent"
            android:text="Student Data"
            android:textAppearance="?android:attr/textAppearanceMedium" />
            
        </LinearLayout>


    </ScrollView>

</LinearLayout>

AndroidManifest.xml

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="com.example.andyphpservice"
    android:versionCode="1"
    android:versionName="1.0" >

    <uses-permission android:name="android.permission.INTERNET"/>
    
    <uses-sdk
        android:minSdkVersion="8"
        android:targetSdkVersion="17" />

    <application
        android:allowBackup="true"
        android:icon="@drawable/ic_launcher"
        android:label="@string/app_name"
        android:theme="@style/AppTheme" >
        <activity
            android:name="com.example.andyphpservice.AndyPhpMainActivity"
            android:label="@string/app_name" >
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
    </application>

</manifest>

PHP Files

DBCreate.php

<?php
$con=mysqli_connect("localhost","root","root");

$sql="CREATE DATABASE DbAndy";

if (mysqli_query($con,$sql))
{
   echo "Database DbAndy created successfully !";
}
?>

TableCreate.php

<?php
$con=mysqli_connect("localhost","root","root","dbAndy");

$sql="CREATE TABLE tblStudent (sno int(3),sname CHAR(30),scity CHAR(30))";

if (mysqli_query($con,$sql))
{
   echo "Table (tblStudent) bas been created successfully !";
}
?>

Index.html

<html>
<head>
	<title>Student Entry | www.raviroza.com</title>
</head>

<body>
<form action="Insert.php" method="POST"> 

	<h1>	Student Data Entry	</h1>
	<p>	Number : <input type="text" name="sno">	</p>
	<p>	Name :	 <input type="text" name="sname">			</p>
	<p>	City :	 <input type="text" name="scity">			</p>
	<p>	<input type="submit" value = "Add" >								</p>
	
</form>
</body>
</html>

Index.php

<?php
$con=mysqli_connect("127.0.0.1","root","root","dbAndy");

if (mysqli_connect_errno($con))
{
   echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$sno = $_POST['sno'];
$sname = $_POST['sname'];
$scity = $_POST['scity'];

$result = mysqli_query($con,"Insert Into tblStudent(sno,sname,scity) values ('$sno','$sname' , '$scity')");

echo "PHP | Student Record Inserted. Done  !!!";

mysqli_close($con);

?>

Select.php

<?php 

$con=mysqli_connect("127.0.0.1","root","root","dbAndy");

if (mysqli_connect_errno($con))
{
   echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$sql = "SELECT * FROM tblStudent";

$r = mysqli_query($con,$sql);

$result = array();

while($row = mysqli_fetch_array($r))
{
	$flag[] = $row;
//	echo ($row[0] . "<br>" . $row[1] . "<br>" . $row[2]);
//	echo ($row[1]);
//	echo ($row[2]);
}
print(json_encode($flag));
mysqli_close($con);

?>

Output

JDBC Statement Example

by

Statement interface object is used to execute queries.

  • It executes query immediately without compiling
  • It has 3 methods to execute query
    • execute(“sql query”)
    • executeQuery(“sql query”)
    • executeUpdate(“sql query”)
  • Each of them throws SQLException

JDBC Oracle Connectivity Example (Gujarati)

by
  • JDBC connectivity with oracle database. In this tutorial I have tried to connect java program to oracle database.
  • Oracle driver (jar files) is required to connect with oracle database.
  • One need to add driver file to eclipse project as an external libraries
  • Driver has to loaded with Class.forName() method. – Finally, one can declare valid Connection object which will be able to connect with oracle database.