JavaBeat Certifications Certifications Kits Articles Interview Questions OCAJP 7 OCPJP 5 OCPJP 6 OCEJWCD 6 SCBCD 5.0 SCEA SCJA

Access MySQL Database: open connection, create table, insert and retrieve

Topic :
Feedback Request New QnA Print Email

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

public class SimpleProgramToAccessOracleDatabase {
public static Connection getConnection() throws Exception {
// load the Oracle JDBC Driver
Class.forName("oracle.jdbc.driver.OracleDriver");
// define database connection parameters
return DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:database", "userName",
"password");
}

public static void main(String[] args) throws SQLException {
Connection conn = null; // connection object
Statement stmt = null; // statement object
ResultSet rs = null; // result set object
try {
conn = getConnection(); // without Connection, can not do much
// create a statement: This object will be used for executing
// a static SQL statement and returning the results it produces.
stmt = conn.createStatement();
// start a transaction
conn.setAutoCommit(false);

// create a table called cats_tricks
stmt.executeUpdate("CREATE TABLE cats_tricks " + "(name VARCHAR2(30), trick VARCHAR2(30))");
// insert two new records to the cats_tricks table
stmt.executeUpdate("INSERT INTO cats_tricks VALUES('mono', 'r')");
stmt.executeUpdate("INSERT INTO cats_tricks VALUES('mono', 'j')");

// commit the transaction
conn.commit();

// set auto commit to true (from now on every single
// statement will be treated as a single transaction
conn.setAutoCommit(true);

// get all of the the records from the cats_tricks table
rs = stmt.executeQuery("SELECT name, trick FROM cats_tricks");

// iterate the result set and get one row at a time
while (rs.next()) {
String name = rs.getString(1); // 1st column in query
String trick = rs.getString(2); // 2nd column in query
System.out.println("name=" + name);
System.out.println("trick=" + trick);
System.out.println("==========");
}
} catch (ClassNotFoundException ce) {
// if the driver class not found, then we will be here
System.out.println(ce.getMessage());
} catch (SQLException e) {
// something went wrong, we are handling the exception here
if (conn != null) {
conn.rollback();
conn.setAutoCommit(true);
}

System.out.println("--- SQLException caught ---");
// iterate and get all of the errors as much as possible.
while (e != null) {
System.out.println("Message : " + e.getMessage());
System.out.println("SQLState : " + e.getSQLState());
System.out.println("ErrorCode : " + e.getErrorCode());
System.out.println("---");
e = e.getNextException();
}
} finally { // close db resources
try {
rs.close();
stmt.close();
conn.close();
} catch (Exception e) {
}

}
}
}

Topic :
Feedback Request New QnA Print Email

All api java java 6.0 java 7.0 jdbc jsp servlet ejb jndi jms ejb 3.0 j2ee jee 5.0 jee 6.0 jsf struts spring Hibernate ajax JBoss Seam netbeans eclipse ant xml maven dojo junit javafx j2me log4j ESB JBoss Apache Quartz scjp mysql oracle gwt openjpa jmx yui google-guice android JBoss scwcd 5.0 scjp 1.5 scjp 1.6 scja scbcd 5.0

javabeat | advertise | about us | contact | useful resources
Copyright (2004 - 2013), JavaBeat