Category: java Mysql

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 – Overview ”; Previous Next JDBC stands for Java Database Connectivity, which is a standard Java API for database-independent connectivity between the Java programming language and a wide range of databases. The JDBC library includes APIs for each of the tasks mentioned below that are commonly associated with database usage. Making a connection to a database. Creating SQL or MySQL statements. Executing SQL or MySQL queries in the database. Viewing & Modifying the resulting records. Fundamentally, JDBC is a specification that provides a complete set of interfaces that allows for portable access to an underlying database. Java can be used to write different types of executables, such as − Java Applications Java Applets Java Servlets Java ServerPages (JSPs) Enterprise JavaBeans (EJBs). All of these different executables are able to use a JDBC driver to access a database, and take advantage of the stored data. JDBC provides the same capabilities as ODBC, allowing Java programs to contain database-independent code. Pre-Requisite Before moving further, you need to have a good understanding of the following two subjects − Core JAVA Programming SQL or MySQL Database JDBC Architecture The JDBC API supports both two-tier and three-tier processing models for database access but in general, JDBC Architecture consists of two layers − JDBC API − This provides the application-to-JDBC Manager connection. JDBC Driver API − This supports the JDBC Manager-to-Driver Connection. The JDBC API uses a driver manager and database-specific drivers to provide transparent connectivity to heterogeneous databases. The JDBC driver manager ensures that the correct driver is used to access each data source. The driver manager is capable of supporting multiple concurrent drivers connected to multiple heterogeneous databases. Following is the architectural diagram, which shows the location of the driver manager with respect to the JDBC drivers and the Java application − Common JDBC Components The JDBC API provides the following interfaces and classes − DriverManager − This class manages a list of database drivers. Matches connection requests from the java application with the proper database driver using communication sub protocol. The first driver that recognizes a certain subprotocol under JDBC will be used to establish a database Connection. Driver − This interface handles the communications with the database server. You will interact directly with Driver objects very rarely. Instead, you use DriverManager objects, which manages objects of this type. It also abstracts the details associated with working with Driver objects. Connection − This interface with all methods for contacting a database. The connection object represents communication context, i.e., all communication with database is through connection object only. Statement − You use objects created from this interface to submit the SQL statements to the database. Some derived interfaces accept parameters in addition to executing stored procedures. ResultSet − These objects hold data retrieved from a database after you execute an SQL query using Statement objects. It acts as an iterator to allow you to move through its data. SQLException − This class handles any errors that occur in a database application. The JDBC 4.0 Packages The java.sql and javax.sql are the primary packages for JDBC 4.0. This is the latest JDBC version at the time of writing this tutorial. It offers the main classes for interacting with your data sources. The new features in these packages include changes in the following areas − Automatic database driver loading. Exception handling improvements. Enhanced BLOB/CLOB functionality. Connection and statement interface enhancements. National character set support. SQL ROWID access. SQL 2003 XML data type support. Annotations. 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 – 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 ”;