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