JAVA-MANI.BLOGSPOT.COM

Get all table names from database

2008-12-09T09:26:00.000+05:30

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

public class GettingTableListExample {
public static void main(String[] args) throws Exception {
Connection connection = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:xe";
String username = "kodejava";
String password = "welcome";
connection = DriverManager.getConnection(url, username, password);

// Gets the metadata of the database
DatabaseMetaData dbmd = connection.getMetaData();
String[] types = {"TABLE"};

ResultSet rs = dbmd.getTables(null, null, "%", types);
while (rs.next()) {
String tableCatalog = rs.getString(1);
String tableSchema = rs.getString(2);
String tableName = rs.getString(3);

System.out.printf("%s - %s - %s%n", tableCatalog, tableSchema, tableName);
}
} catch (SQLException e) {
if (connection != null && !connection.isClosed()) {
connection.close();
}
}
}
}

Create a connection to MS Access database

2008-12-07T09:22:00.000+05:30

Here is an example about how to create a database connection to MS Access database. To allow the database access to be authenticated the security user account can be add from Tools->Security->User and Group Accounts.

On the example below we can either connect through the DSN created previously on the Windows system or we can create it in our program as the long URL below.

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

public class MSAccessConnect {
//
// If you want to use you ODBC DSN
//
//private static final String URL = "jdbc:odbc:TestDB";

private static final String USERNAME = "admin";
private static final String PASSWORD = "welcome";
private static final String DRIVER = "sun.jdbc.odbc.JdbcOdbcDriver";

private static final String URL =
"jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=D:\\Database\\testdb.mdb;}";

public static void main(String[] args) throws Exception {
Connection connection = null;
try {
Class.forName(DRIVER);
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);

//
// Do something with the connection here!
//
} catch (SQLException e) {
e.printStackTrace();
} finally {
connection.close();
}
}
}

make updates in Updatable ResultSet

2008-12-05T09:14:00.001+05:30

Using an updatable resultset enable our program to update record in the database from the ResultSet object. The operation on the ResultSet object can be update, insert or delete. With this mechanism we can update database without executing an sql command.

In the example below we have a product table with the id, product_code, product_name, quantity and price. In the first step after we load the resultset we update the product title of the first record. Then we move to the next record and delete it. At last we insert a new record to database.

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

public class UpdatableResultSetDemo {

public static void main(String[] args) {
Connection connection = null;

try {
//
// Routine to get a connection object to database.
//
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://localhost/testdb",
"root", "");

//
// Create an updatable resultset. It means that instead of using a
// separate sql comment to update the data we can update it directly
// in the resultset object.
//
// What makes it updatable is because when creating the statement we
// ask the connection object to create statement with CONCUR_UPDATABLE.
// The updatable doesn't need to be TYPE_SCROLL_SENSITIVE, but adding
// this parameter to the statement enable us to go back and forth to
// update the data.
//
Statement statement = connection.createStatement(
ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

String query = "SELECT id, product_code, product_name, quantity, price FROM products";
ResultSet uprs = statement.executeQuery(query);

System.out.println("id\tcode\tname\tquantity\tquantity\tprice");
while (uprs.next()) {
System.out.println(uprs.getString("id") + "\t"
+ uprs.getString("product_code") + "\t"
+ uprs.getString("product_name") + "\t"
+ uprs.getInt("quantity") + "\t"
+ uprs.getDouble("price"));
}

//
// Move to the first row and update the resultset data. After we
// update the rowset value we call the updateRow() method to update
// the data in the database.
//
uprs.first();
uprs.updateString("product_name", "UML Distilled 3rd Edition");
uprs.updateRow();

//
// Move to the next resultset row and delete the row in the resultset
// and apply it to the database.
//
uprs.next();
uprs.deleteRow();

//
// Insert a new row in the resultset object with the moveToInsertRow()
// method. Supply the information to be inserted and finally call the
// insertRow() method to insert record to the database.
//
uprs.moveToInsertRow();
uprs.updateString("product_code", "P0000010");
uprs.updateString("product_name", "Data Structures, Algorithms");
uprs.updateInt("quantity", 10);
uprs.updateDouble("price", 50.99);
uprs.insertRow();

uprs.beforeFirst();
System.out.println("id\tcode\tname\tquantity\tquantity\tprice");
while (uprs.next()) {
System.out.println(uprs.getString("id") + "\t"
+ uprs.getString("product_code") + "\t"
+ uprs.getString("product_name") + "\t"
+ uprs.getInt("quantity") + "\t"
+ uprs.getDouble("price"));
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}

How do I know if a table column can have a null value or not?

2008-12-03T09:20:00.001+05:30

In this example we'll show how to use ResultSetMetaData.isNullable() method to know if a column can be null or not. This method return an integer which values defined in the constants of ResultSetMetaData.columnNullable, ResultSetMetaData.columnNoNulls and ResultSetMetaData.columnNullableUnknown.

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

public class IsNullableExample {
private static final String DRIVER = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql://localhost/testdb";
private static final String USERNAME = "root";
private static final String PASSWORD = "";

public static void main(String[] args) throws Exception {
Connection connection = null;
try {
Class.forName(DRIVER);
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);

Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("SELECT id, username FROM users");

//
// The ResultSetMetaData is where all metadata related information
// for a result set is stored.
//
ResultSetMetaData metadata = resultSet.getMetaData();
int nullability = metadata.isNullable(1);

//
// Check the nullability status of a column (ID)
//
if (nullability == ResultSetMetaData.columnNullable) {
System.out.println("Columns ID can have a null value");
} else if (nullability == ResultSetMetaData.columnNoNulls) {
System.out.println("Columns ID does not allowed to have a null value");
} else if (nullability == ResultSetMetaData.columnNullableUnknown) {
System.out.println("Nullability unknown");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
connection.close();
}
}
}

Get list of stored procedure names

2008-12-01T09:01:00.000+05:30

import java.sql.*;

public class StoredProcedureListExample {
private static String url = "jdbc:oracle:thin:@localhost:1521:xe";
private static String username = "kodejava";
private static String password = "welcome";

public static void main(String[] args) throws Exception{
Connection conn = getConnection();

try {
DatabaseMetaData metadata = conn.getMetaData();
ResultSet result = metadata.getProcedures(null, "KODEJAVA", "%");
while (result.next()) {
System.out.println(result.getString("PROCEDURE_CAT")
+ " - " + result.getString("PROCEDURE_SCHEM")
+ " - " + result.getString("PROCEDURE_NAME"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
closeConnection(conn);
}
}

private static Connection getConnection() throws Exception{
Connection conn = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(url, username, password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
throw new Exception(e);
} catch (SQLException e) {
e.printStackTrace();
throw new Exception(e);
}
return conn;
}

private static void closeConnection(Connection conn) throws SQLException {
if (conn != null && !conn.isClosed()) {
conn.close();
}
}
}

How do I execute stored procedure?

2008-11-29T09:04:00.000+05:30

Stored procedure are user-generated functions or procedures that, once created in the database, can be called by the client applications, such as Java application. In this example we'll demonstrate how to use the JDBC java.sql.CallableStatement to call a stored procedure.

The store procedure in this example is just for inserting a record into table. Just like the PreparedStatement interface, in the CallableStatement we can pass the parameter to the procedure by calling the appropriate setXXX(index, value) method.

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

public class CallableStatementDemo {
private static String url = "jdbc:oracle:thin:@localhost:1521:xe";
private static String username = "kodejava";
private static String password = "welcome";

public static void main(String[] args) throws Exception {
Connection conn = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(url, username, password);

//
// Create a CallableStatement to execute the CREATE_USERS procedure
//
CallableStatement stmt = conn.prepareCall("{call CREATE_USERS (?, ?, ?, ?, ?, ?)}");

//
// Defines all the required parameter values.
//
stmt.setString(1, "kodejava");
stmt.setString(2, "welcome");
stmt.setString(3, "Kode");
stmt.setString(4, "Java");
stmt.setString(5, "Denpasar - Bali");
stmt.setString(6, "webmaster[at]kodejava[.]org");
stmt.execute();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (conn != null && !conn.isClosed()) {
conn.close();
}
}
}
}

Below is the stored procedure that was executed in the code above.

CREATE OR REPLACE PROCEDURE CREATE_USERS (username IN VARCHAR2, password IN VARCHAR2, firstName IN VARCHAR2, lastName IN VARCHAR2, address IN VARCHAR2, email IN VARCHAR2) AS
BEGIN
INSERT INTO users (username, password, first_name, last_name, address, email) VALUES (username, password, firstName, lastName, address, email);
END CREATE_USERS;

Get JDBC driver information

2008-11-27T08:55:00.002+05:30

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

public class DriverInfo {
private static final String DRIVER = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql://localhost/kodejava";
private static final String USERNAME = "root";
private static final String PASSWORD = "";

public static void main(String[] args) throws Exception {
Connection connection = null;
try {
Class.forName(DRIVER);
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);

DatabaseMetaData metadata = connection.getMetaData();
String driverName = metadata.getDriverName();
String driverVersion = metadata.getDriverVersion();
int majorVersion = metadata.getDriverMajorVersion();
int minorVersion = metadata.getDriverMinorVersion();

System.out.println("driverName = " + driverName);
System.out.println("driverVersion = " + driverVersion);
System.out.println("majorVersion = " + majorVersion);
System.out.println("minorVersion = " + minorVersion);
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (connection != null) {
connection.close();
}
}
}
}

The result of our program:

driverName = MySQL-AB JDBC Driver
driverVersion = mysql-connector-java-5.0.3 ( $Date: 2006-07-26 17:26:47 +0200 (Wed, 26 Jul 2006) $, $Revision: 5553 $ )
majorVersion = 5
minorVersion = 0

Get database product information

2008-11-25T08:53:00.000+05:30

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

public class DriverInfo {
private static final String DRIVER = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql://localhost/kodejava";
private static final String USERNAME = "root";
private static final String PASSWORD = "";

public static void main(String[] args) throws Exception {
Connection connection = null;
try {
Class.forName(DRIVER);
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);

DatabaseMetaData metadata = connection.getMetaData();

int majorVersion = metadata.getDatabaseMajorVersion();
System.out.println("majorVersion = " + majorVersion);

int minorVersion = metadata.getDatabaseMinorVersion();
System.out.println("minorVersion = " + minorVersion);

String productName = metadata.getDatabaseProductName();
System.out.println("productName = " + productName);

String productVersion = metadata.getDatabaseProductVersion();
System.out.println("productVersion = " + productVersion);
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (connection != null) {
connection.close();
}
}
}
}

And here are the result:

majorVersion = 5
minorVersion = 0
productName = MySQL
productVersion = 5.0.51a

How do I know if database support transaction

2008-11-23T08:51:00.000+05:30

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

public class SupportTransaction {
private static final String DRIVER = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql://localhost/kodejava";
private static final String USERNAME = "root";
private static final String PASSWORD = "";

public static void main(String[] args) throws Exception {
Connection connection = null;
try {
Class.forName(DRIVER);
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
DatabaseMetaData metadata = connection.getMetaData();

boolean isSupportTransaction = metadata.supportsTransactions();
System.out.println("Support Transaction = " + isSupportTransaction);
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (connection != null) {
connection.close();
}
}
}
}

Get data types supported by database

2008-11-21T08:49:00.000+05:30

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

public class DatabaseTypeInfo {
private static final String DRIVER = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql://localhost/kodejava";
private static final String USERNAME = "root";
private static final String PASSWORD = "";

public static void main(String[] args) throws Exception {
Connection connection = null;
ResultSet resultSet = null;
try {
Class.forName(DRIVER);
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);

DatabaseMetaData metadata = connection.getMetaData();
resultSet = metadata.getTypeInfo();
while (resultSet.next()) {
String typeName = resultSet.getString("TYPE_NAME");
System.out.println("Type Name = " + typeName);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (resultSet != null) {
resultSet.close();
}
if (connection != null) {
connection.close();
}
}
}
}

Here are the data type support MySQL database:

Type Name = BIT
Type Name = BOOL
Type Name = TINYINT
Type Name = BIGINT
Type Name = LONG VARBINARY
Type Name = MEDIUMBLOB
Type Name = LONGBLOB
Type Name = BLOB
Type Name = TINYBLOB
Type Name = VARBINARY
Type Name = BINARY
Type Name = LONG VARCHAR
Type Name = MEDIUMTEXT
Type Name = LONGTEXT
Type Name = TEXT
Type Name = TINYTEXT
Type Name = CHAR
Type Name = NUMERIC
Type Name = DECIMAL
Type Name = INTEGER
Type Name = INT
Type Name = MEDIUMINT
Type Name = SMALLINT
Type Name = FLOAT
Type Name = DOUBLE
Type Name = DOUBLE PRECISION
Type Name = REAL
Type Name = VARCHAR
Type Name = ENUM
Type Name = SET
Type Name = DATE
Type Name = TIME
Type Name = DATETIME
Type Name = TIMESTAMP

How do I know if database support batch update?

2008-11-19T08:47:00.000+05:30

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

public class SupportBatch {
private static final String DRIVER = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql://localhost/kodejava";
private static final String USERNAME = "root";
private static final String PASSWORD = "";

public static void main(String[] args) throws Exception {
Connection connection = null;
try {
Class.forName(DRIVER);
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
DatabaseMetaData metadata = connection.getMetaData();

//
// Check to see if the database support batch updates
//
boolean isBatchingSupported = metadata.supportsBatchUpdates();
System.out.println("Batching Supported = " + isBatchingSupported);
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (connection != null) {
connection.close();
}
}
}
}

How do I know if database support scrollable result sets?

2008-11-17T08:46:00.000+05:30

import java.sql.*;

public class SupportScrollableResultSet {
private static final String DRIVER = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql://localhost/kodejava";
private static final String USERNAME = "root";
private static final String PASSWORD = "";

public static void main(String[] args) throws Exception {
Connection connection = null;
try {
Class.forName(DRIVER);
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
DatabaseMetaData metadata = connection.getMetaData();

boolean supportForwardOnly =
metadata.supportsResultSetType(ResultSet.TYPE_FORWARD_ONLY);
System.out.println("supportForwardOnly = " + supportForwardOnly);

boolean supportScrollInsensitive =
metadata.supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE);
System.out.println("supportScrollInsensitive = " + supportScrollInsensitive);

boolean supportScrollSensitive =
metadata.supportsResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE);
System.out.println("supportScrollSensitive = " + supportScrollSensitive);
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (connection != null) {
connection.close();
}
}
}
}

How do I know if database support updatable result sets?

2008-11-15T08:44:00.000+05:30

import java.sql.*;

public class SupportUpdatetableResultSet {
private static final String DRIVER = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql://localhost/kodejava";
private static final String USERNAME = "root";
private static final String PASSWORD = "";

public static void main(String[] args) throws Exception {
Connection connection = null;
try {
Class.forName(DRIVER);
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
DatabaseMetaData metadata = connection.getMetaData();

boolean updatable = metadata.supportsResultSetConcurrency(
ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);

System.out.println("Updatable ResultSet supported = " + updatable);
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (connection != null) {
connection.close();
}
}
}
}

Get database maximum table name length

2008-11-13T08:40:00.001+05:30

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

public class MaximumTableNameLength {
private static final String DRIVER = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql://localhost/kodejava";
private static final String USERNAME = "root";
private static final String PASSWORD = "";

public static void main(String[] args) throws Exception {
Connection connection = null;
try {
Class.forName(DRIVER);
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
DatabaseMetaData metadata = connection.getMetaData();

//
// Get the information of maximum length of database table name
//
int maxLength = metadata.getMaxTableNameLength();
System.out.println("Max Table Name Length = " + maxLength);
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (connection != null) {
connection.close();
}
}
}
}

Get the max concurrent connection to a database

2008-11-11T08:38:00.000+05:30

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

public class MaxConcurrentConnection {
private static final String DRIVER = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql://localhost/kodejava";
private static final String USERNAME = "root";
private static final String PASSWORD = "";

public static void main(String[] args) throws Exception {
Connection connection = null;
try {
Class.forName(DRIVER);
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
DatabaseMetaData metadata = connection.getMetaData();

//
// Get the information of maximum concurrent connection to the
// database. The maximum number of active connections possible at
// one time; a result of zero means that there is no limit or the
// limit is not known
//
int maxConnection = metadata.getMaxConnections();
System.out.println("Maximum Connection = " + maxConnection);
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (connection != null) {
connection.close();
}
}
}
}

Get string functions supported by database

2008-11-09T08:35:00.000+05:30

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

public class StringFunction {
private static final String DRIVER = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql://localhost/kodejava";
private static final String USERNAME = "root";
private static final String PASSWORD = "";

public static void main(String[] args) throws Exception {
Connection connection = null;
try {
Class.forName(DRIVER);
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
DatabaseMetaData metadata = connection.getMetaData();

//
// Get string functions supported by database
//
String[] functions = metadata.getStringFunctions().split(",\\s*");

for (int i = 0; i < functions.length; i++) {
String function = functions[i];
System.out.println("Function = " + function);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (connection != null) {
connection.close();
}
}
}
}

Here is the result of string functions supported by MySQL database:

Function = ASCII
Function = BIN
Function = BIT_LENGTH
Function = CHAR
Function = CHARACTER_LENGTH
Function = CHAR_LENGTH
Function = CONCAT
Function = CONCAT_WS
Function = CONV
Function = ELT
Function = EXPORT_SET
Function = FIELD
Function = FIND_IN_SET
Function = HEX
Function = INSERT
Function = INSTR
Function = LCASE
Function = LEFT
Function = LENGTH
Function = LOAD_FILE
Function = LOCATE
Function = LOCATE
Function = LOWER
Function = LPAD
Function = LTRIM
Function = MAKE_SET
Function = MATCH
Function = MID
Function = OCT
Function = OCTET_LENGTH
Function = ORD
Function = POSITION
Function = QUOTE
Function = REPEAT
Function = REPLACE
Function = REVERSE
Function = RIGHT
Function = RPAD
Function = RTRIM
Function = SOUNDEX
Function = SPACE
Function = STRCMP
Function = SUBSTRING
Function = SUBSTRING
Function = SUBSTRING
Function = SUBSTRING
Function = SUBSTRING_INDEX
Function = TRIM
Function = UCASE
Function = UPPER

Get numeric functions supported by database

2008-11-07T08:31:00.000+05:30

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

public class NumericFunction {
private static final String DRIVER = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql://localhost/kodejava";
private static final String USERNAME = "root";
private static final String PASSWORD = "";

public static void main(String[] args) throws Exception {
Connection connection = null;
try {
Class.forName(DRIVER);
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
DatabaseMetaData metadata = connection.getMetaData();

//
// Get numeric functions supported by database
//
String[] functions = metadata.getNumericFunctions().split(",\\s*");

for (int i = 0; i < functions.length; i++) {
String function = functions[i];
System.out.println("Function = " + function);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (connection != null) {
connection.close();
}
}
}
}

Here is the numeric functions supported by MySQL database.

Function = ABS
Function = ACOS
Function = ASIN
Function = ATAN
Function = ATAN2
Function = BIT_COUNT
Function = CEILING
Function = COS
Function = COT
Function = DEGREES
Function = EXP
Function = FLOOR
Function = LOG
Function = LOG10
Function = MAX
Function = MIN
Function = MOD
Function = PI
Function = POW
Function = POWER
Function = RADIANS
Function = RAND
Function = ROUND
Function = SIN
Function = SQRT
Function = TAN
Function = TRUNCATE

Get date time functions supported by database

2008-11-05T08:29:00.000+05:30

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

public class DateTimeFunction {
private static final String DRIVER = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql://localhost/kodejava";
private static final String USERNAME = "root";
private static final String PASSWORD = "";

public static void main(String[] args) throws Exception {
Connection connection = null;
try {
Class.forName(DRIVER);
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
DatabaseMetaData metadata = connection.getMetaData();

//
// Get date and time functions supported by database
//
String[] functions = metadata.getTimeDateFunctions().split(",\\s*");

for (int i = 0; i < functions.length; i++) {
String function = functions[i];
System.out.println("Function = " + function);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (connection != null) {
connection.close();
}
}
}
}

Date and time functions supported by MySQL database.

Function = DAYOFWEEK
Function = WEEKDAY
Function = DAYOFMONTH
Function = DAYOFYEAR
Function = MONTH
Function = DAYNAME
Function = MONTHNAME
Function = QUARTER
Function = WEEK
Function = YEAR
Function = HOUR
Function = MINUTE
Function = SECOND
Function = PERIOD_ADD
Function = PERIOD_DIFF
Function = TO_DAYS
Function = FROM_DAYS
Function = DATE_FORMAT
Function = TIME_FORMAT
Function = CURDATE
Function = CURRENT_DATE
Function = CURTIME
Function = CURRENT_TIME
Function = NOW
Function = SYSDATE
Function = CURRENT_TIMESTAMP
Function = UNIX_TIMESTAMP
Function = FROM_UNIXTIME
Function = SEC_TO_TIME
Function = TIME_TO_SEC

Get system functions supported by database

2008-11-03T08:24:00.002+05:30

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

public class SystemFunction {
private static final String DRIVER = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql://localhost/kodejava";
private static final String USERNAME = "root";
private static final String PASSWORD = "";

public static void main(String[] args) throws Exception {
Connection connection = null;
try {
Class.forName(DRIVER);
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
DatabaseMetaData metadata = connection.getMetaData();

//
// Get system functions supported by database
//
String[] functions = metadata.getSystemFunctions().split(",\\s*");

for (int i = 0; i < functions.length; i++) {
String function = functions[i];
System.out.println("Function = " + function);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (connection != null) {
connection.close();
}
}
}
}

Here are MySQL database supported system functions.

Function = DATABASE
Function = USER
Function = SYSTEM_USER
Function = SESSION_USER
Function = PASSWORD
Function = ENCRYPT
Function = LAST_INSERT_ID
Function = VERSION

Math operation for BigInteger

2008-11-01T08:22:00.001+05:30

import java.math.BigInteger;

public class BigIntegerOperation {
public static void main(String[] args) {
BigInteger numberA = new BigInteger("98765432123456789");
BigInteger numberB = BigInteger.TEN;

numberA = numberA.add(numberB);
System.out.println("numberA = " + numberA);

numberA = numberA.multiply(numberB);
System.out.println("numberA = " + numberA);

numberA = numberA.subtract(numberB);
System.out.println("numberA = " + numberA);

numberA = numberA.divide(numberB);
System.out.println("numberA = " + numberA);

numberA = numberA.mod(numberB);
System.out.println("numberA = " + numberA);

numberA = numberA.pow(2);
System.out.println("numberA = " + numberA);

numberA = numberA.negate();
System.out.println("numberA = " + numberA);
}
}

Our class result are:

numberA = 98765432123456799
numberA = 987654321234567990
numberA = 987654321234567980
numberA = 98765432123456798
numberA = 8
numberA = 64
numberA = -64

Convert BigInteger into another radix number

2008-10-31T08:06:00.001+05:30

In this example you'll see how we can convert a BigInteger number into another radix such as binary, octal and hexadecimal.

import java.math.BigInteger;

public class BigIntegerConversion {
public static void main(String[] args) {
BigInteger number = new BigInteger("2008");

System.out.println("Number = " + number);
System.out.println("Binary = " + number.toString(2));
System.out.println("Octal = " + number.toString(8));
System.out.println("Hexadecimal = " + number.toString(16));

number = new BigInteger("FF", 16);
System.out.println("Number = " + number);
System.out.println("Number = " + number.toString(16));
}
}

The result of our examples:

Number = 2008
Binary = 11111011000
Octal = 3730
Hexadecimal = 7d8
Number = 255
Number = ff

Math operation for BigDecimal

2008-10-29T08:10:00.000+05:30

import java.math.BigDecimal;

public class BigDecimalOperation {
public static void main(String[] args) {
BigDecimal decimalA = new BigDecimal("98765432123456789");
BigDecimal decimalB = new BigDecimal("10");

decimalA = decimalA.add(decimalB);
System.out.println("decimalA = " + decimalA);

decimalA = decimalA.multiply(decimalB);
System.out.println("decimalA = " + decimalA);

decimalA = decimalA.subtract(decimalB);
System.out.println("decimalA = " + decimalA);

decimalA = decimalA.divide(decimalB);
System.out.println("decimalA = " + decimalA);

decimalA = decimalA.pow(2);
System.out.println("decimalA = " + decimalA);

decimalA = decimalA.negate();
System.out.println("decimalA = " + decimalA);
}
}

Our class result are:

decimalA = 98765432123456799
decimalA = 987654321234567990
decimalA = 987654321234567980
decimalA = 98765432123456798
decimalA = 9754610582533151990855052972412804
decimalA = -9754610582533151990855052972412804

Convert a bean to XML persistence

2008-10-27T08:01:00.001+05:30

import java.beans.XMLEncoder;
import java.io.BufferedOutputStream;
import java.io.FileOutputStream;
import java.io.FileNotFoundException;

public class BeanToXML {
private Long id;
private String itemName;
private String itemColour;
private Integer itemQuantities;

public static void main(String[] args) {
BeanToXML bean = new BeanToXML();
bean.setId(new Long(1));
bean.setItemName("T-Shirt");
bean.setItemColour("Dark Red");
bean.setItemQuantities(new Integer(100));

try {
XMLEncoder encoder = new XMLEncoder(new BufferedOutputStream(
new FileOutputStream("Bean.xml")));

//
// Write an XML representation of the specified object to the output.
//
encoder.writeObject(bean);
encoder.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
}
}

public Long getId() {
return id;
}

public void setId(Long id) {
this.id = id;
}

public String getItemName() {
return itemName;
}

public void setItemName(String itemName) {
this.itemName = itemName;
}

public String getItemColour() {
return itemColour;
}

public void setItemColour(String itemColour) {
this.itemColour = itemColour;
}

public Integer getItemQuantities() {
return itemQuantities;
}

public void setItemQuantities(Integer itemQuantities) {
this.itemQuantities = itemQuantities;
}
}

The XML persistence will be like:

1

Dark Red

T-Shirt

100

Convert an XML persistence to bean

2008-10-25T08:00:00.000+05:30

import java.beans.XMLDecoder;
import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.FileNotFoundException;

public class XmlToBean {
public static void main(String[] args) {
try {
XMLDecoder decoder = new XMLDecoder(new BufferedInputStream(
new FileInputStream("Bean.xml")));

//
// Reads the next object from the underlying input stream.
//
BeanToXML bean = (BeanToXML) decoder.readObject();
decoder.close();

System.out.println("ID = " + bean.getId());
System.out.println("Item Name = " + bean.getItemName());
System.out.println("Item Colour = " + bean.getItemColour());
System.out.println("Item Quantities = " + bean.getItemQuantities());
} catch (FileNotFoundException e) {
e.printStackTrace();
}
}
}

The result are:

ID = 1
Item Name = T-Shirt
Item Colour = Dark Red
Item Quantities = 100

Prevent bean's property being serialized to XML

2008-10-23T07:57:00.000+05:30

import java.beans.*;
import java.io.BufferedOutputStream;
import java.io.FileOutputStream;
import java.io.FileNotFoundException;

public class BeanToXmlTransient {
private Long id;
private String itemName;
private String itemColour;
private Integer itemQuantities;

static {
try {
//
// In this block will change the attribute type of the itemQuantities
// to transient so it will be not serialized to xml when we use
// XMLEncode to convert the bean to xml persistence.
//
BeanInfo bi = Introspector.getBeanInfo(BeanToXmlTransient.class);
PropertyDescriptor[] pds = bi.getPropertyDescriptors();
for (int i = 0; i < pds.length; i++) {
PropertyDescriptor propertyDescriptor = pds[i];
if (propertyDescriptor.getName().equals("itemQuantities")) {
propertyDescriptor.setValue("transient", Boolean.TRUE);
}
}
} catch (IntrospectionException e) {
e.printStackTrace();
}
}

public static void main(String[] args) {
BeanToXmlTransient bean = new BeanToXmlTransient();
bean.setId(new Long(1));
bean.setItemName("T-Shirt");
bean.setItemColour("Dark Red");
bean.setItemQuantities(new Integer(100));

try {
XMLEncoder encoder = new XMLEncoder(new BufferedOutputStream(
new FileOutputStream("BeanTransient.xml")));

//
// Write an XML representation of the specified object to the output.
//
encoder.writeObject(bean);
encoder.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
}
}

public Long getId() {
return id;
}

public void setId(Long id) {
this.id = id;
}

public String getItemName() {
return itemName;
}

public void setItemName(String itemName) {
this.itemName = itemName;
}

public String getItemColour() {
return itemColour;
}

public void setItemColour(String itemColour) {
this.itemColour = itemColour;
}

public Integer getItemQuantities() {
return itemQuantities;
}

public void setItemQuantities(Integer itemQuantities) {
this.itemQuantities = itemQuantities;
}
}