Category: java Mysql

Java & MySQL – Exceptions Handling ”; Previous Next Exception handling allows you to handle exceptional conditions such as program-defined errors in a controlled fashion. When an exception condition occurs, an exception is thrown. The term thrown means that current program execution stops, and the control is redirected to the nearest applicable catch clause. If no applicable catch clause exists, then the program”s execution ends. JDBC Exception handling is very similar to the Java Exception handling but for JDBC, the most common exception you”ll deal with is java.sql.SQLException. SQLException Methods An SQLException can occur both in the driver and the database. When such an exception occurs, an object of type SQLException will be passed to the catch clause. The passed SQLException object has the following methods available for retrieving additional information about the exception − Method Description getErrorCode( ) Gets the error number associated with the exception. getMessage( ) Gets the JDBC driver”s error message for an error, handled by the driver or gets the Oracle error number and message for a database error. getSQLState( ) Gets the XOPEN SQLstate string. For a JDBC driver error, no useful information is returned from this method. For a database error, the five-digit XOPEN SQLstate code is returned. This method can return null. getNextException( ) Gets the next Exception object in the exception chain. printStackTrace( ) Prints the current exception, or throwable, and it”s backtrace to a standard error stream. printStackTrace(PrintStream s) Prints this throwable and its backtrace to the print stream you specify. printStackTrace(PrintWriter w) Prints this throwable and it”s backtrace to the print writer you specify. By utilizing the information available from the Exception object, you can catch an exception and continue your program appropriately. Here is the general form of a try block − try { // Your risky code goes between these curly braces!!! } catch(Exception ex) { // Your exception handling code goes between these // curly braces } finally { // Your must-always-be-executed code goes between these // curly braces. Like closing database connection. } Example Study the following example code to understand the usage of try….catch…finally blocks. This code has been written based on the environment and database setup done in the previous chapter. import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class TestApplication { static final String DB_URL = “jdbc:mysql://localhost/TUTORIALSPOINT”; static final String USER = “guest”; static final String PASS = “guest123”; static final String QUERY = “{call getEmpName (?, ?)}”; public static void main(String[] args) { // Open a connection try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS); CallableStatement stmt = conn.prepareCall(QUERY); ) { // Bind values into the parameters. stmt.setInt(1, 1); // This would set ID // Because second parameter is OUT so register it stmt.registerOutParameter(2, java.sql.Types.VARCHAR); //Use execute method to run stored procedure. System.out.println(“Executing stored procedure…” ); stmt.execute(); //Retrieve employee name with getXXX method String empName = stmt.getString(2); System.out.println(“Emp Name with ID: 1 is ” + empName); } catch (SQLException e) { e.printStackTrace(); } } } Now let us compile the above example as follows − C:>javac TestApplication.java C:> When you run TestApplication, it produces the following result if there is no problem, otherwise the corresponding error would be caught and error message would be displayed − C:>java TestApplication Executing stored procedure… Emp Name with ID: 1 is Zara C:> Print Page Previous Next Advertisements ”;

Java & MySQL – Viewing a ResultSet ”; Previous Next The ResultSet interface contains dozens of methods for getting the data of the current row. There is a get method for each of the possible data types, and each get method has two versions − One that takes in a column name. One that takes in a column index. For example, if the column you are interested in viewing contains an int, you need to use one of the getInt() methods of ResultSet − S.N. Methods & Description 1 public int getInt(String columnName) throws SQLException Returns the int in the current row in the column named columnName. 2 public int getInt(int columnIndex) throws SQLException Returns the int in the current row in the specified column index. The column index starts at 1, meaning the first column of a row is 1, the second column of a row is 2, and so on. Similarly, there are get methods in the ResultSet interface for each of the eight Java primitive types, as well as common types such as java.lang.String, java.lang.Object, and java.net.URL. There are also methods for getting SQL data types java.sql.Date, java.sql.Time, java.sql.TimeStamp, java.sql.Clob, and java.sql.Blob. Check the documentation for more information about using these SQL data types. Following is the example which makes use of few viewing methods described. This sample code has been written based on the environment and database setup done in the previous chapters. Copy and paste the following example in TestApplication.java, compile and run as follows − import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class TestApplication { static final String DB_URL = “jdbc:mysql://localhost/TUTORIALSPOINT”; static final String USER = “guest”; static final String PASS = “guest123”; static final String QUERY = “SELECT id, first, last, age FROM Employees”; public static void main(String[] args) { // Open a connection try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS); Statement stmt = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet rs = stmt.executeQuery(QUERY); ) { // Move cursor to the last row. System.out.println(“Moving cursor to the last…”); rs.last(); // Extract data from result set System.out.println(“Displaying record…”); //Retrieve by column name int id = rs.getInt(“id”); int age = rs.getInt(“age”); String first = rs.getString(“first”); String last = rs.getString(“last”); // Display values System.out.print(“ID: ” + id); System.out.print(“, Age: ” + age); System.out.print(“, First: ” + first); System.out.println(“, Last: ” + last); // Move cursor to the first row. System.out.println(“Moving cursor to the first row…”); rs.first(); // Extract data from result set System.out.println(“Displaying record…”); // Retrieve by column name id = rs.getInt(“id”); age = rs.getInt(“age”); first = rs.getString(“first”); last = rs.getString(“last”); // Display values System.out.print(“ID: ” + id); System.out.print(“, Age: ” + age); System.out.print(“, First: ” + first); System.out.println(“, Last: ” + last); // Move cursor to the first row. System.out.println(“Moving cursor to the next row…”); rs.next(); // Extract data from result set System.out.println(“Displaying record…”); id = rs.getInt(“id”); age = rs.getInt(“age”); first = rs.getString(“first”); last = rs.getString(“last”); // Display values System.out.print(“ID: ” + id); System.out.print(“, Age: ” + age); System.out.print(“, First: ” + first); System.out.println(“, Last: ” + last); } catch (SQLException e) { e.printStackTrace(); } } } Now let us compile the above example as follows − C:>javac TestApplication.java C:> When you run TestApplication, it produces the following result − C:>java TestApplication Moving cursor to the last… Displaying record… ID: 103, Age: 30, First: Sumit, Last: Mittal Moving cursor to the first row… Displaying record… ID: 100, Age: 18, First: Zara, Last: Ali Moving cursor to the next row… Displaying record… ID: 101, Age: 25, First: Mehnaz, Last: Fatma C:> Print Page Previous Next Advertisements ”;

Java & MySQL – Connections ”; Previous Next After you”ve installed the appropriate driver, it is time to establish a database connection using JDBC. The programming involved to establish a JDBC connection is fairly simple. Here are these simple three steps − Import JDBC Packages − Add import statements to your Java program to import required classes in your Java code. Database URL Formulation − This is to create a properly formatted address that points to the database to which you wish to connect. Create Connection Object − Finally, code a call to the DriverManager object”s getConnection( ) method to establish actual database connection. Import JDBC Packages The Import statements tell the Java compiler where to find the classes you reference in your code and are placed at the very beginning of your source code. To use the standard JDBC package, which allows you to select, insert, update, and delete data in SQL tables, add the following imports to your source code − import java.sql.* ; // for standard JDBC programs import java.math.* ; // for BigDecimal and BigInteger support Register JDBC Driver You must have the required JDBC driver in the classpath. In current case, you set CLASSPATH variable to C:Program FilesMySQLmysql-connector-java-5.1.8mysql-connector-java-5.1.8-bin.jar. Your driver version may vary based on your installation. Database URL Formulation After you”ve loaded the driver, you can establish a connection using the DriverManager.getConnection() method. For easy reference, let me list the three overloaded DriverManager.getConnection() methods − getConnection(String url) getConnection(String url, Properties prop) getConnection(String url, String user, String password) Here each form requires a database URL. A database URL is an address that points to your database. Formulating a database URL is where most of the problems associated with establishing a connection occurs. Following table lists down the MySQL JDBC driver name and database URL. RDBMS JDBC driver name URL format MySQL com.mysql.jdbc.Driver jdbc:mysql://hostname/ databaseName All the highlighted part in URL format is static and you need to change only the remaining part as per your database setup. Create Connection Object We have listed down three forms of DriverManager.getConnection() method to create a connection object. Using a Database URL with a username and password The most commonly used form of getConnection() requires you to pass a database URL, a username, and a password − As you are using MySQL driver, you”ll specify a host:port:databaseName value for the database portion of the URL. If you have a host at TCP/IP address 192.0.0.1 with a host name of localhost, and your MySQL listener is configured to listen on port 3306 as default, and your database name is TUTORIALSPOINT, then complete database URL would be − jdbc:mysql://localhost/TUTORIALSPOINT Now you have to call getConnection() method with appropriate username and password to get a Connection object as follows − String URL = “jdbc:mysql://localhost/TUTORIALSPOINT”; String USER = “guest”; String PASS = “password” Connection conn = DriverManager.getConnection(URL, USER, PASS); Using a Database URL and a Properties Object A third form of the DriverManager.getConnection( ) method requires a database URL and a Properties object − DriverManager.getConnection(String url, Properties info); A Properties object holds a set of keyword-value pairs. It is used to pass driver properties to the driver during a call to the getConnection() method. To make the same connection made by the previous examples, use the following code − import java.util.*; String URL = “jdbc:mysql://localhost/TUTORIALSPOINT”; Properties info = new Properties( ); info.put( “user”, “guest” ); info.put( “password”, “guest123″ ); Connection conn = DriverManager.getConnection(URL, info); For a better understanding, we suggest you to study our Java & MySQL − Sample Code tutorial. Now let us compile the above example as follows − C:>javac FirstExample.java C:> When you run FirstExample, it produces the following result − C:>java FirstExample ID: 100, Age: 18, First: Zara, Last: Ali ID: 101, Age: 25, First: Mahnaz, Last: Fatma ID: 102, Age: 30, First: Zaid, Last: Khan ID: 103, Age: 28, First: Sumit, Last: Mittal C:> Print Page Previous Next Advertisements ”;

Java & MySQL – Create Database Example ”; Previous Next This tutorial provides an example on how to create a Database using JDBC application. Before executing the following example, make sure you have the following in place − You should have admin privilege to create a database in the given schema. To execute the following example, you need to replace the username and password with your actual user name and password. Your MySQL is up and running. Required Steps The following steps are required to create a new Database using JDBC application − Import the packages − Requires that you include the packages containing the JDBC classes needed for database programming. Most often, using import java.sql.* will suffice. Open a connection − Requires using the DriverManager.getConnection() method to create a Connection object, which represents a physical connection with the database server. To create a new database, you need not give any database name while preparing database URL as mentioned in the below example. Execute a query − Requires using an object of type Statement for building and submitting an SQL statement to the database. Clean up the environment − try with resources automatically closes the resources. Sample Code Copy and paste the following example in TestApplication.java, compile and run as follows − import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; public class TestApplication { static final String DB_URL = “jdbc:mysql://localhost/”; static final String USER = “guest”; static final String PASS = “guest123”; public static void main(String[] args) { // Open a connection try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS); Statement stmt = conn.createStatement(); ) { String sql = “CREATE DATABASE STUDENTS”; stmt.executeUpdate(sql); System.out.println(“Database created successfully…”); } catch (SQLException e) { e.printStackTrace(); } } } Now let us compile the above example as follows − C:>javac TestApplication.java C:> When you run TestApplication, it produces the following result − C:>java TestApplication Database created successfully… C:> Print Page Previous Next Advertisements ”;

Java & MySQL – Sample Code ”; Previous Next This chapter provides an example of how to create a simple java based application to access MySQL database. This will show you how to open a database connection, execute a SQL query, and display the results. All the steps mentioned in this template example, would be explained in subsequent chapters of this tutorial. Creating JDBC Application There are following six steps involved in building a JDBC application − Import the packages: Requires that you include the packages containing the JDBC classes needed for database programming. Most often, using import java.sql.* will suffice. Open a connection: Requires using the DriverManager.getConnection() method to create a Connection object, which represents a physical connection with the database. Execute a query: Requires using an object of type Statement for building and submitting an SQL statement to the database. Extract data from result set − Requires that you use the appropriate ResultSet.getXXX() method to retrieve the data from the result set. Clean up the environment − Requires explicitly closing all database resources versus relying on the JVM”s garbage collection. Sample Code This sample example can serve as a template when you need to create your own JDBC application in the future. This sample code has been written based on the environment and database setup done in the previous chapter. Copy and paste the following example in TestApplication.java, compile and run as follows − import java.sql.*; public class TestApplication { static final String DB_URL = “jdbc:mysql://localhost/TUTORIALSPOINT”; static final String USER = “guest”; static final String PASS = “guest123”; static final String QUERY = “SELECT id, first, last, age FROM Employees”; public static void main(String[] args) { // Open a connection try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(QUERY);) { // Extract data from result set while (rs.next()) { // Retrieve by column name System.out.print(“ID: ” + rs.getInt(“id”)); System.out.print(“, Age: ” + rs.getInt(“age”)); System.out.print(“, First: ” + rs.getString(“first”)); System.out.println(“, Last: ” + rs.getString(“last”)); } } catch (SQLException e) { e.printStackTrace(); } } } Now let us compile the above example as follows − C:>javac TestApplication.java C:> When you run TestApplication, it produces the following result − C:>java TestApplication ID: 100, Age: 18, First: Zara, Last: Ali ID: 101, Age: 25, First: Mahnaz, Last: Fatma ID: 102, Age: 30, First: Zaid, Last: Khan ID: 103, Age: 28, First: Sumit, Last: Mittal C:> Print Page Previous Next Advertisements ”;