Category: java Mysql

Java & MySQL – Batching with PrepareStatement Object ”; Previous Next Here is a typical sequence of steps to use Batch Processing with PrepareStatement Object − Create SQL statements with placeholders. Create PrepareStatement object using either prepareStatement() methods. Set auto-commit to false using setAutoCommit(). Add as many as SQL statements you like into batch using addBatch() method on created statement object. Execute all the SQL statements using executeBatch() method on created statement object. Finally, commit all the changes using commit() method. 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.PreparedStatement; 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 INSERT_QUERY = “INSERT INTO Employees(first,last,age) VALUES(?, ?, ?)”; public static void printResultSet(ResultSet rs) throws SQLException{ // Ensure we start with first row rs.beforeFirst(); while(rs.next()){ // Display values 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”)); } System.out.println(); } public static void main(String[] args) { // Open a connection try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS); PreparedStatement stmt = conn.prepareStatement(INSERT_QUERY, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE) ) { conn.setAutoCommit(false); ResultSet rs = stmt.executeQuery(“Select * from Employees”); printResultSet(rs); // Set the variables stmt.setString( 1, “Pappu” ); stmt.setString( 2, “Singh” ); stmt.setInt( 3, 33 ); // Add it to the batch stmt.addBatch(); // Set the variables stmt.setString( 1, “Pawan” ); stmt.setString( 2, “Singh” ); stmt.setInt( 3, 31 ); // Add it to the batch stmt.addBatch(); // Create an int[] to hold returned values int[] count = stmt.executeBatch(); //Explicitly commit statements to apply changes conn.commit(); rs = stmt.executeQuery(“Select * from Employees”); printResultSet(rs); stmt.close(); rs.close(); } 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: 1, Age: 23, First: Zara, Last: Ali ID: 2, Age: 30, First: Mahnaz, Last: Fatma ID: 3, Age: 35, First: Zaid, Last: Khan ID: 4, Age: 33, First: Sumit, Last: Mittal ID: 5, Age: 40, First: John, Last: Paul ID: 7, Age: 35, First: Sita, Last: Singh ID: 8, Age: 20, First: Rita, Last: Tez ID: 9, Age: 20, First: Sita, Last: Singh ID: 10, Age: 30, First: Zia, Last: Ali ID: 11, Age: 35, First: Raj, Last: Kumar ID: 1, Age: 23, First: Zara, Last: Ali ID: 2, Age: 30, First: Mahnaz, Last: Fatma ID: 3, Age: 35, First: Zaid, Last: Khan ID: 4, Age: 33, First: Sumit, Last: Mittal ID: 5, Age: 40, First: John, Last: Paul ID: 7, Age: 35, First: Sita, Last: Singh ID: 8, Age: 20, First: Rita, Last: Tez ID: 9, Age: 20, First: Sita, Last: Singh ID: 10, Age: 30, First: Zia, Last: Ali ID: 11, Age: 35, First: Raj, Last: Kumar ID: 12, Age: 33, First: Pappu, Last: Singh ID: 13, Age: 31, First: Pawan, Last: Singh C:> Print Page Previous Next Advertisements ”;

Discuss Java & MySQL ”; Previous Next Java based application can connect to MySQL using JDBC API. JDBC works with Java on a variety of platforms, such as Windows, Mac OS, and the various versions of UNIX. Print Page Previous Next Advertisements ”;

Java & MySQL – Drop Table Example ”; Previous Next This chapter provides an example on how to delete a table using JDBC application. Before executing the following example, make sure you have the following in place − To execute the following example you can replace the username and password with your actual user name and password. Your MySQL database you are using, is up and running. NOTE − This is a serious operation and you have to make a firm decision before proceeding to delete a table, because everything you have in your table would be lost. 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 a database server. Execute a query − Requires using an object of type Statement for building and submitting an SQL statement to drop a table in a seleted 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/TUTORIALSPOINT”; 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 = “DROP TABLE REGISTRATION”; stmt.executeUpdate(sql); System.out.println(“Table deleted in given database…”); } 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 Table deleted in given database… C:> Print Page Previous Next Advertisements ”;

Java & MySQL – ResultSet ”; Previous Next The SQL statements that read data from a database query, return the data in a result set. The SELECT statement is the standard way to select rows from a database and view them in a result set. The java.sql.ResultSet interface represents the result set of a database query. A ResultSet object maintains a cursor that points to the current row in the result set. The term “result set” refers to the row and column data contained in a ResultSet object. The methods of the ResultSet interface can be broken down into three categories − Navigational methods − Used to move the cursor around. Get methods − Used to view the data in the columns of the current row being pointed by the cursor. Update methods − Used to update the data in the columns of the current row. The updates can then be updated in the underlying database as well. The cursor is movable based on the properties of the ResultSet. These properties are designated when the corresponding Statement that generates the ResultSet is created. JDBC provides the following connection methods to create statements with desired ResultSet − createStatement(int RSType, int RSConcurrency); prepareStatement(String SQL, int RSType, int RSConcurrency); prepareCall(String sql, int RSType, int RSConcurrency); The first argument indicates the type of a ResultSet object and the second argument is one of two ResultSet constants for specifying whether a result set is read-only or updatable. Type of ResultSet The possible RSType are given below. If you do not specify any ResultSet type, you will automatically get one that is TYPE_FORWARD_ONLY. Type Description ResultSet.TYPE_FORWARD_ONLY The cursor can only move forward in the result set. ResultSet.TYPE_SCROLL_INSENSITIVE The cursor can scroll forward and backward, and the result set is not sensitive to changes made by others to the database that occur after the result set was created. ResultSet.TYPE_SCROLL_SENSITIVE. The cursor can scroll forward and backward, and the result set is sensitive to changes made by others to the database that occur after the result set was created. Concurrency of ResultSet The possible RSConcurrency are given below. If you do not specify any Concurrency type, you will automatically get one that is CONCUR_READ_ONLY. Concurrency Description ResultSet.CONCUR_READ_ONLY Creates a read-only result set. This is the default ResultSet.CONCUR_UPDATABLE Creates an updateable result set. All our examples written so far can be written as follows, which initializes a Statement object to create a forward-only, read only ResultSet object − try( Statement stmt = conn.createStatement( ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);) } catch(Exception ex) { …. } finally { …. } Print Page Previous Next Advertisements ”;

Java & MySQL – Select Records Example ”; Previous Next This chapter provides an example on how to select/ fetch records from a table using JDBC application. Before executing the following example, make sure you have the following in place − To execute the following example you can replace the username and password with your actual user name and password. Your MySQL database you are using 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 a database server. Execute a query − Requires using an object of type Statement for building and submitting an SQL statement to select (i.e. fetch ) records from a table. Extract Data − Once SQL query is executed, you can fetch records from the table. 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.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 Registration”; 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); ) { while(rs.next()){ //Display values 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 ”;

Java & MySQL – Transactions ”; Previous Next If your JDBC Connection is in auto-commit mode, which it is by default, then every SQL statement is committed to the database upon its completion. That may be fine for simple applications, but there are three reasons why you may want to turn off the auto-commit and manage your own transactions − To increase performance. To maintain the integrity of business processes. To use distributed transactions. Transactions enable you to control if, and when, changes are applied to the database. It treats a single SQL statement or a group of SQL statements as one logical unit, and if any statement fails, the whole transaction fails. To enable manual- transaction support instead of the auto-commit mode that the JDBC driver uses by default, use the Connection object”s setAutoCommit() method. If you pass a boolean false to setAutoCommit( ), you turn off auto-commit. You can pass a boolean true to turn it back on again. For example, if you have a Connection object named conn, code the following to turn off auto-commit − conn.setAutoCommit(false); Commit & Rollback Once you are done with your changes and you want to commit the changes then call commit() method on connection object as follows − conn.commit( ); Otherwise, to roll back updates to the database made using the Connection named conn, use the following code − conn.rollback( ); Using Savepoints The new JDBC 3.0 Savepoint interface gives you the additional transactional control. When you set a savepoint you define a logical rollback point within a transaction. If an error occurs past a savepoint, you can use the rollback method to undo either all the changes or only the changes made after the savepoint. The Connection object has two new methods that help you manage savepoints − setSavepoint(String savepointName) − Defines a new savepoint. It also returns a Savepoint object. releaseSavepoint(Savepoint savepointName) − Deletes a savepoint. Notice that it requires a Savepoint object as a parameter. This object is usually a savepoint generated by the setSavepoint() method. There is one rollback (String savepointName) method, which rolls back work to the specified savepoint. Print Page Previous Next Advertisements ”;

Java & MySQL – LIKE Clause Example ”; Previous Next This chapter provides an example on how to select records from a table using JDBC application. This would add additional conditions using LIKE clause while selecting records from the table. Before executing the following example, make sure you have the following in place − To execute the following example you can replace the username and password with your actual user name and password. Your MySQL database you are using, 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 a database server. Execute a query − Requires using an object of type Statement for building and submitting an SQL statement to fetch records from a table which meet given condition. This Query makes use of LIKE clause to select records to select all the students whose first name starts with “za”. 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.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 Registration”; public static void main(String[] args) { // Open a connection try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS); Statement stmt = conn.createStatement();) { System.out.println(“Fetching records without condition…”); ResultSet rs = stmt.executeQuery(QUERY); while(rs.next()){ //Display values 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”)); } // Select all records having ID equal or greater than 101 System.out.println(“Fetching records with condition…”); String sql = “SELECT id, first, last, age FROM Registration” + ” WHERE first LIKE ”%za%””; rs = stmt.executeQuery(sql); while(rs.next()){ //Display values 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”)); } rs.close(); } 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 Fetching records without condition… ID: 100, Age: 30, First: Zara, Last: Ali ID: 102, Age: 30, First: Zaid, Last: Khan ID: 103, Age: 28, First: Sumit, Last: Mittal Fetching records with condition… ID: 100, Age: 30, First: Zara, Last: Ali ID: 102, Age: 30, First: Zaid, Last: Khan C:> Print Page Previous Next Advertisements ”;

Java & MySQL Tutorial Quick Guide Resources Job Search Discussion Java based application can connect to MySQL using JDBC API. JDBC works with Java on a variety of platforms, such as Windows, Mac OS, and the various versions of UNIX. Audience This tutorial is designed for Java programmers who would like to understand the JDBC framework to connect to MySQL in detail along with its architecture and actual usage. Prerequisites Before proceeding with this tutorial, you should have a good understanding of Java programming language. As you are going to deal with MySQL database, you should have prior exposure to SQL and Database concepts. Print Page Previous Next Advertisements ”;

Java & MySQL – Navigating a ResultSet ”; Previous Next There are several methods in the ResultSet interface that involve moving the cursor, including − S.N. Methods & Description 1 public void beforeFirst() throws SQLException Moves the cursor just before the first row. 2 public void afterLast() throws SQLException Moves the cursor just after the last row. 3 public boolean first() throws SQLException Moves the cursor to the first row. 4 public void last() throws SQLException Moves the cursor to the last row. 5 public boolean absolute(int row) throws SQLException Moves the cursor to the specified row. 6 public boolean relative(int row) throws SQLException Moves the cursor the given number of rows forward or backward, from where it is currently pointing. 7 public boolean previous() throws SQLException Moves the cursor to the previous row. This method returns false if the previous row is off the result set. 8 public boolean next() throws SQLException Moves the cursor to the next row. This method returns false if there are no more rows in the result set. 9 public int getRow() throws SQLException Returns the row number that the cursor is pointing to. 10 public void moveToInsertRow() throws SQLException Moves the cursor to a special row in the result set that can be used to insert a new row into the database. The current cursor location is remembered. 11 public void moveToCurrentRow() throws SQLException Moves the cursor back to the current row if the cursor is currently at the insert row; otherwise, this method does nothing Following is the example which makes use of few navigation 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 – 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 ”;