JAVA-MANI.BLOGSPOT.COM

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();
}
}
}
}

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();
}
}
}

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();
}
}
}
}

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();
}
}
}

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();
}
}
}