Java and My Sql datatypes

The following table represent the default Java mapping for various common MySQL data types:

MySQL Type	Java Type
CHAR	String
VARCHAR	String
LONGVARCHAR	String
NUMERIC	java.math.BigDecimal
DECIMAL	java.math.BigDecimal
BIT	boolean
TINYINT	byte
SMALLINT	short
INTEGER	int
BIGINT	long
REAL	float
FLOAT	double
DOUBLE	double
BINARY	byte []
VARBINARY	byte []
LONGVARBINARY	byte []
DATE	java.sql.Date
TIME	java.sql.Time
TIMESTAMP	java.sql.Tiimestamp

1. CHAR, VARCHAR and LONGVARCHAR
   MySQL data types CHAR, VARCHAR, LONGVARCHAR are closely related. CHAR represents a small, fixed-length character string, VARCHAR represents a small, variable-length character string, and LONGVARCHAR represents a large, variable-length character string. There is no need for Java programmer to distinguish these three MySQL data types. These can be expressed identically in Java. These data types could be mapped in Java to either String or char[]. But String seemed more appropriate type for normal use. Java String class provide a method to convert a String into char[] and a constructor for converting a char[] into a String.
    
   The method ResultSet.getString allocates and returns a new String. It is suitable for retrieving data from CHAR, VARCHAR and LONGVARCHAR fields. This is suitable for retrieving normal data, but LONGVARCHAR MySQL type can be used to store multi-megabyte strings. So that Java programmers needs a way to retrieve the LONGVARCHAR value in chunks. To handle this situation, ResultSet interface have two methods for allowing programmers to retrieve aLONGVARCHAR value as a Java input stream from which they can subsequently read data in whatever size chunks they prefer. These methods are getAsciiStream andgetCharacterStream, which deliver the data stored in a LONGVARCHAR column as a stream of ASCII or Unicode characters.
     
2. NUMERIC and DECIMALThe NUMERIC and DECIMAL MySQL data types are very similar. They both represent fixed point numbers where absolute precision is required. The most convenient Java mapping for theseMySQL data type is java.math.BigDecimal. This Java type provides math operations to allowBigDecimal types to be added, subtracted, multiplied, and divided with other BigDecimaltypes, with integer types, and with floating point types.
   
   We also allow access to these MySQL types as simple Strings and char []. Thus, the Java programmers can use the getString() to retrieve the NUMERICAL and DECIMAL results.
    
3. BINARY, VARBINARY and LONGVARBINARY 
   These MySQL data types are closely related. BINARY represents a small, fixed-length binary value, VARBINARY represents a small, variable-length binary value and LONGVARBINARYrepresents a large, variable-length binary value. For Java programers there is no need to distinguish among these data types and they can all be expressed identically as byte arrays in Java. It is possible to read and write SQL statements correctly without knowing the exactBINARY data type. The ResultSet.getBytes method is used for retrieving the DECIMAL andNUMERICAL values. Same as LONGVARCHAR type, LONGVARBINARY type can also be used to return multi-megabyte data values then the method getBinaryStream is recommended.
    
4. BITThe MySQL type BIT represents a single bit value that can be 'zero' or 'one'. And this MySQLtype can be mapped directly to the Java boolean type.
     
5. TINYINT, SMALLINT, INTEGER and BIGINT
   The MySQL TINYINT type represents an 8-bit integer value between 0 and 255 that may be signed or unsigned. SMALLINT type represents a 16-bit signed integer value between -32768 and 32767. INTEGER type represents a 32-bit signed integer value between -2147483648 and 2147483647. BIGINT type represents an 64-bit signed integer value between -9223372036854775808 and 9223372036854775807. These MySQL TINYINT, SMALLINT, INTEGER, and BIGINT types can be mapped to Java's byte, short, int and long data types respectively.
    
6. REAL, FLOAT and DOUBLE
   The MySQL REAL represents a "single precision" floating point number that supports seven digits of mantissa and the FLOAT and DOUBLE type represents a "double precision" floating point number that supports 15 digits of mantissa. The recommended Java mapping for REALtype to Java float and FLOAT, DOUBLE type to Java double.
    
7. DATE, TIME and TIMESTAMP
   These three MySQL types are related to time. The DATE type represents a date consisting of day, month, and year, the TIME type represents a time consisting of hours, minutes, and seconds and the TIMESTAMP type represents DATE plus TIME plus a nanosecond field. The standard Java class java.util.Date that provides date and time information but does not match any of these three MySQL date/time types exactly, because it has DATE and TIME information but no nanoseconds.
   That's why we define three subclasses of java.util.Date. These are:
    
   • java.sql.Date for SQL DATE information.
   • java.sql.Time for SQL TIME information.
   • java.sql.Timestamp for SQL TIMESTAMP information. 

Java with MySql

1. Java Database Connectivity (JDBC)

The interface for accessing relational databases from Java is JDBC. Via JDBC you maintain the databases connection, issue database queries and updates and receive the results.

JDBC is an interface independently of any database. For each database you require the database specific implementation of the JDBC driver.

2. Introduction to MySQL

To learn to install and use MySQL please see MySQL - Tutorial.

The following will assume that you have successfully installed MySQL and know how to access MySQL via the command line.

3. MySQL JDBC driver

To connect to MySQL from Java you have to use the JDBC driver from MySQL. The MySQL JDBC driver is called "MySQL Connector/J". You should be able to find the latest MySQL JDBC driver on this pagehttp://dev.mysql.com/downloads/connector/j .

The download contains a jar file which we require later.

4. Create example database

Start the MySQL command line client and paste the following SQL script into it to create a sample database which we can use as an example.

create database feedback;
use feedback;
CREATE USER sqluser IDENTIFIED BY 'sqluserpw'; 
grant usage on *.* to sqluser@localhost identified by 'sqluserpw'; 
grant all privileges on feedback.* to sqluser@localhost;

CREATE TABLE COMMENTS (id INT NOT NULL AUTO_INCREMENT, 
    MYUSER VARCHAR(30) NOT NULL,
    EMAIL VARCHAR(30), 
    WEBPAGE VARCHAR(100) NOT NULL, 
    DATUM DATE NOT NULL, 
    SUMMERY VARCHAR(40) NOT NULL,
    COMMENTS VARCHAR(400) NOT NULL,
    PRIMARY KEY (ID));

INSERT INTO COMMENTS values (default, 'lars', 'myemail@gmail.com','http://www.vogella.com', '2009-09-14 10:33:11', 'Summery','My first comment'); 

5. Java JDBC

Create a Java project and a package "de.vogella.mysql.first".

Create a folder "lib" and copy the JDBC driver into this folder. Add the JDBC driver to your classpath. SeeAdding jars to the classpath for details.

Create the following class to connect to the MySQL database and perform queries, inserts and deletes. It also prints the metadata (table name, column names) of a query result.

package de.vogella.mysql.first;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

public class MySQLAccess {
  private Connection connect = null;
  private Statement statement = null;
  private PreparedStatement preparedStatement = null;
  private ResultSet resultSet = null;

  public void readDataBase() throws Exception {
    try {
      // This will load the MySQL driver, each DB has its own driver
      Class.forName("com.mysql.jdbc.Driver");
      // Setup the connection with the DB
      connect = DriverManager
          .getConnection("jdbc:mysql://localhost/feedback?"
              + "user=sqluser&password=sqluserpw");

      // Statements allow to issue SQL queries to the database
      statement = connect.createStatement();
      // Result set get the result of the SQL query
      resultSet = statement
          .executeQuery("select * from FEEDBACK.COMMENTS");
      writeResultSet(resultSet);

      // PreparedStatements can use variables and are more efficient
      preparedStatement = connect
          .prepareStatement("insert into  FEEDBACK.COMMENTS values (default, ?, ?, ?, ? , ?, ?)");
      // "myuser, webpage, datum, summery, COMMENTS from FEEDBACK.COMMENTS");
      // Parameters start with 1
      preparedStatement.setString(1, "Test");
      preparedStatement.setString(2, "TestEmail");
      preparedStatement.setString(3, "TestWebpage");
      preparedStatement.setDate(4, new java.sql.Date(2009, 12, 11));
      preparedStatement.setString(5, "TestSummary");
      preparedStatement.setString(6, "TestComment");
      preparedStatement.executeUpdate();

      preparedStatement = connect
          .prepareStatement("SELECT myuser, webpage, datum, summery, COMMENTS from FEEDBACK.COMMENTS");
      resultSet = preparedStatement.executeQuery();
      writeResultSet(resultSet);

      // Remove again the insert comment
      preparedStatement = connect
      .prepareStatement("delete from FEEDBACK.COMMENTS where myuser= ? ; ");
      preparedStatement.setString(1, "Test");
      preparedStatement.executeUpdate();
      
      resultSet = statement
      .executeQuery("select * from FEEDBACK.COMMENTS");
      writeMetaData(resultSet);
      
    } catch (Exception e) {
      throw e;
    } finally {
      close();
    }

  }

  private void writeMetaData(ResultSet resultSet) throws SQLException {
    //   Now get some metadata from the database
    // Result set get the result of the SQL query
    
    System.out.println("The columns in the table are: ");
    
    System.out.println("Table: " + resultSet.getMetaData().getTableName(1));
    for  (int i = 1; i<= resultSet.getMetaData().getColumnCount(); i++){
      System.out.println("Column " +i  + " "+ resultSet.getMetaData().getColumnName(i));
    }
  }

  private void writeResultSet(ResultSet resultSet) throws SQLException {
    // ResultSet is initially before the first data set
    while (resultSet.next()) {
      // It is possible to get the columns via name
      // also possible to get the columns via the column number
      // which starts at 1
      // e.g. resultSet.getSTring(2);
      String user = resultSet.getString("myuser");
      String website = resultSet.getString("webpage");
      String summery = resultSet.getString("summery");
      Date date = resultSet.getDate("datum");
      String comment = resultSet.getString("comments");
      System.out.println("User: " + user);
      System.out.println("Website: " + website);
      System.out.println("Summery: " + summery);
      System.out.println("Date: " + date);
      System.out.println("Comment: " + comment);
    }
  }

  // You need to close the resultSet
  private void close() {
    try {
      if (resultSet != null) {
        resultSet.close();
      }

      if (statement != null) {
        statement.close();
      }

      if (connect != null) {
        connect.close();
      }
    } catch (Exception e) {

    }
  }

} 

Create the following main program to test your class.

package de.vogella.mysql.first.test;

import de.vogella.mysql.first.MySQLAccess;

public class Main {
  public static void main(String[] args) throws Exception {
    MySQLAccess dao = new MySQLAccess();
    dao.readDataBase();
  }


} 

JDBC – Insert A Record

Here’s an example to show you how to insert a record into table via JDBC statement. To issue a insert statement, calls theStatement.executeUpdate() method like this :

Statement statement = dbConnection.createStatement();
// execute the insert SQL stetement
statement.executeUpdate(insertTableSQL);

Full example…

package com.mkyong.jdbc;
 
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
 
public class JDBCStatementInsertExample {
 
 private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
 private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:MKYONG";
 private static final String DB_USER = "user";
 private static final String DB_PASSWORD = "password";
 private static final DateFormat dateFormat = new SimpleDateFormat(
   "yyyy/MM/dd HH:mm:ss");
 
 public static void main(String[] argv) {
 
  try {
 
   insertRecordIntoDbUserTable();
 
  } catch (SQLException e) {
 
   System.out.println(e.getMessage());
 
  }
 
 }
 
 private static void insertRecordIntoDbUserTable() throws SQLException {
 
  Connection dbConnection = null;
  Statement statement = null;
 
  String insertTableSQL = "INSERT INTO DBUSER"
    + "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) " + "VALUES"
    + "(1,'mkyong','system', " + "to_date('"
    + getCurrentTimeStamp() + "', 'yyyy/mm/dd hh24:mi:ss'))";
 
  try {
   dbConnection = getDBConnection();
   statement = dbConnection.createStatement();
 
   System.out.println(insertTableSQL);
 
   // execute insert SQL stetement
   statement.executeUpdate(insertTableSQL);
 
   System.out.println("Record is inserted into DBUSER table!");
 
  } catch (SQLException e) {
 
   System.out.println(e.getMessage());
 
  } finally {
 
   if (statement != null) {
    statement.close();
   }
 
   if (dbConnection != null) {
    dbConnection.close();
   }
 
  }
 
 }
 
 private static Connection getDBConnection() {
 
  Connection dbConnection = null;
 
  try {
 
   Class.forName(DB_DRIVER);
 
  } catch (ClassNotFoundException e) {
 
   System.out.println(e.getMessage());
 
  }
 
  try {
 
   dbConnection = DriverManager.getConnection(
                               DB_CONNECTION, DB_USER,DB_PASSWORD);
   return dbConnection;
 
  } catch (SQLException e) {
 
   System.out.println(e.getMessage());
 
  }
 
  return dbConnection;
 
 }
 
 private static String getCurrentTimeStamp() {
 
  java.util.Date today = new java.util.Date();
  return dateFormat.format(today.getTime());
 
 }
 
}

Result

A record is inserted into a table named “DBUSER”.

INSERT INTO DBUSER(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) 
VALUES(1,'mkyong','system', to_date('2011/04/04 13:59:03', 'yyyy/mm/dd hh24:mi:ss'))
Record IS inserted INTO DBUSER TABLE!

JDBC - Create A Table

JDBC Statement Example – Create A Table

Here’s an example to show you how to create a table in database via JDBC statement. To issue a create statement, calls the Statement.execute() method like this :

Statement statement = dbConnection.createStatement();
// execute create SQL stetement
statement.execute(createTableSQL);

Full example…

package com.mkyong.jdbc;
 
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
 
public class JDBCStatementCreateExample {
 
 private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
 private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:MKYONG";
 private static final String DB_USER = "user";
 private static final String DB_PASSWORD = "password";
 
 public static void main(String[] argv) {
 
  try {
 
   createDbUserTable();
 
  } catch (SQLException e) {
 
   System.out.println(e.getMessage());
 
  }
 
 }
 
 private static void createDbUserTable() throws SQLException {
 
  Connection dbConnection = null;
  Statement statement = null;
 
  String createTableSQL = "CREATE TABLE DBUSER("
    + "USER_ID NUMBER(5) NOT NULL, "
    + "USERNAME VARCHAR(20) NOT NULL, "
    + "CREATED_BY VARCHAR(20) NOT NULL, "
    + "CREATED_DATE DATE NOT NULL, " + "PRIMARY KEY (USER_ID) "
    + ")";
 
  try {
   dbConnection = getDBConnection();
   statement = dbConnection.createStatement();
 
   System.out.println(createTableSQL);
                        // execute the SQL stetement
   statement.execute(createTableSQL);
 
   System.out.println("Table \"dbuser\" is created!");
 
  } catch (SQLException e) {
 
   System.out.println(e.getMessage());
 
  } finally {
 
   if (statement != null) {
    statement.close();
   }
 
   if (dbConnection != null) {
    dbConnection.close();
   }
 
  }
 
 }
 
 private static Connection getDBConnection() {
 
  Connection dbConnection = null;
 
  try {
 
   Class.forName(DB_DRIVER);
 
  } catch (ClassNotFoundException e) {
 
   System.out.println(e.getMessage());
 
  }
 
  try {
 
   dbConnection = DriverManager.getConnection(
     DB_CONNECTION, DB_USER,DB_PASSWORD);
   return dbConnection;
 
  } catch (SQLException e) {
 
   System.out.println(e.getMessage());
 
  }
 
  return dbConnection;
 
 }
 
}

Result

Here’s the result.

CREATE TABLE DBUSER(
  USER_ID NUMBER(5) NOT NULL, 
  USERNAME VARCHAR(20) NOT NULL, 
  CREATED_BY VARCHAR(20) NOT NULL, 
  CREATED_DATE DATE NOT NULL, 
  PRIMARY KEY (USER_ID) 
)
TABLE "user" IS created!

Connect To Oracle DB Via JDBC Driver

Here’s an example to show you how to connect to Oracle database via JDBC driver.

1. Download MySQL JDBC Driver

Get Oracle JDBC driver here – ojdbcxxx.jar

2. Java JDBC Connection Example

Code snippets to use JDBC to connect a Oracle database.

Class.forName("org.postgresql.Driver");
Connection connection = null;
connection = DriverManager.getConnection(
 "jdbc:oracle:thin:@localhost:1521:mkyong","username","password");
connection.close();

See a complete example below :
File : OracleJDBC.java

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
 
public class OracleJDBC {
 
 public static void main(String[] argv) {
 
  System.out.println("-------- Oracle JDBC Connection Testing ------");
 
  try {
 
   Class.forName("oracle.jdbc.driver.OracleDriver");
 
  } catch (ClassNotFoundException e) {
 
   System.out.println("Where is your Oracle JDBC Driver?");
   e.printStackTrace();
   return;
 
  }
 
  System.out.println("Oracle JDBC Driver Registered!");
 
  Connection connection = null;
 
  try {
 
   connection = DriverManager.getConnection(
     "jdbc:oracle:thin:@localhost:1521:mkyong", "username",
     "password");
 
  } catch (SQLException e) {
 
   System.out.println("Connection Failed! Check output console");
   e.printStackTrace();
   return;
 
  }
 
  if (connection != null) {
   System.out.println("You made it, take control your database now!");
  } else {
   System.out.println("Failed to make connection!");
  }
 }
 
}

2. Run It

Assume OracleJDBC.java is store in “C:\jdbc-test” folder, together with Oracle JDBC driver (ojdbc6.jar), then run following commands :

C:\jdbc-test>javac OracleJDBC.java
 
C:\jdbc-test>java -cp c:\jdbc-test\ojdbc6.jar;c:\jdbc-test OracleJDBC
-------- Oracle JDBC Connection Testing ------------
Oracle JDBC Driver Registered!
You made it, take control your database now!
 
C:\jdbc-test>

Done

Connect To MySQL With JDBC Driver

Here’s an example to show you how to connect to MySQL database via JDBC driver.

1. Download MySQL JDBC Driver

Get a MySQL JDBC driver from here -MySQL JDBC Driver Download Here

2. Java JDBC Connection Example

Code snippets to use JDBC to connect a MySQL database.

  Class.forName("com.mysql.jdbc.Driver");
  Connection connection = null;
  connection = DriverManager.getConnection(
          "jdbc:mysql://hostname:port/dbname","username", "password");
  connection.close();

See a complete example below :
File : JDBCExample.java

package com.mkyong.common;
 
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
 
public class JDBCExample {
 
 public static void main(String[] argv) {
 
  System.out.println("-------- MySQL JDBC Connection Testing ------------");
 
  try {
 
   Class.forName("com.mysql.jdbc.Driver");
 
  } catch (ClassNotFoundException e) {
 
   System.out.println("Where is your MySQL JDBC Driver?");
   e.printStackTrace();
   return;
 
  }
 
  System.out.println("MySQL JDBC Driver Registered!");
  Connection connection = null;
 
  try {
   connection = DriverManager
     .getConnection("jdbc:mysql://localhost:3306/mkyongcom",
       "root", "password");
 
  } catch (SQLException e) {
   System.out.println("Connection Failed! Check output console");
   e.printStackTrace();
   return;
  }
 
  if (connection != null) {
   System.out.println("You made it, take control your database now!");
  } else {
   System.out.println("Failed to make connection!");
  }
 }
}

3. Run It

Assume JDBCExample.java is store in c:\test folder, together with MySQL JDBC driver

C:\test>java -cp c:\test\mysql-connector-java-5.1.8-bin.jar;c:\test JDBCExample
-------- MySQL JDBC Connection Testing ------------
MySQL JDBC Driver Registered!
You made it, take control your database now!
 
C:\test>

Android Button Example

 

In Android, just use “android.widget.Button” class to display a normal button.

In this tutorial, we show you how to display a normal button, add a click listener, when user click on the button, open an URL in your Android’s internet browser.

P.S This project is developed in Eclipse 3.7, and tested with Android 2.3.3.

Note
For more advance function, like image, please refer to this ImageButton example and also this ImageButton selector example.

1. Add Button

Open “res/layout/main.xml” file, add a button.

File : res/layout/main.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:orientation="vertical" >
 
    <Button
        android:id="@+id/button1"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Button - Go to mkyong.com" />
 
</LinearLayout>

2. Code Code

Attach a click listener to the button.

When user click on it, open mobile browser and display URL : http://www.mkyong.com.

File : MyAndroidAppActivity.java

package com.mkyong.android;
 
import android.app.Activity;
import android.content.Intent;
import android.net.Uri;
import android.os.Bundle;
import android.widget.Button;
import android.view.View;
import android.view.View.OnClickListener;
 
public class MyAndroidAppActivity extends Activity {
 
 Button button;
 
 @Override
 public void onCreate(Bundle savedInstanceState) {
  super.onCreate(savedInstanceState);
  setContentView(R.layout.main);
 
  addListenerOnButton();
 
 }
 
 public void addListenerOnButton() {
 
  button = (Button) findViewById(R.id.button1);
 
  button.setOnClickListener(new OnClickListener() {
 
   @Override
   public void onClick(View arg0) {
 
     Intent browserIntent = 
                            new Intent(Intent.ACTION_VIEW, Uri.parse("http://www.mkyong.com"));
       startActivity(browserIntent);
 
   }
 
  });
 
 }
 
}

3. Demo

Run the application.

1. Result, a normal button.

2. Click on the button, display URL in browser.