Open database connection in java

Establishing a Connection

First, you need to establish a connection with the data source you want to use. A data source can be a DBMS, a legacy file system, or some other source of data with a corresponding JDBC driver. Typically, a JDBC application connects to a target data source using one of two classes:

• DriverManager : This fully implemented class connects an application to a data source, which is specified by a database URL. When this class first attempts to establish a connection, it automatically loads any JDBC 4.0 drivers found within the class path. Note that your application must manually load any JDBC drivers prior to version 4.0.
• DataSource : This interface is preferred over DriverManager because it allows details about the underlying data source to be transparent to your application. A DataSource object’s properties are set so that it represents a particular data source. See Connecting with DataSource Objects for more information. For more information about developing applications with the DataSource class, see the latest The Java EE Tutorial.

Note: The samples in this tutorial use the DriverManager class instead of the DataSource class because it is easier to use and the samples do not require the features of the DataSource class.

This page covers the following topics:

Using the DriverManager Class

Connecting to your DBMS with the DriverManager class involves calling the method DriverManager.getConnection . The following method, JDBCTutorialUtilities.getConnection , establishes a database connection:

public Connection getConnection() throws SQLException < Connection conn = null; Properties connectionProps = new Properties(); connectionProps.put("user", this.userName); connectionProps.put("password", this.password); if (this.dbms.equals("mysql")) < conn = DriverManager.getConnection( "jdbc:" + this.dbms + "://" + this.serverName + ":" + this.portNumber + "/", connectionProps); >else if (this.dbms.equals("derby")) < conn = DriverManager.getConnection( "jdbc:" + this.dbms + ":" + this.dbName + ";create=true", connectionProps); >System.out.println("Connected to database"); return conn; >

The method DriverManager.getConnection establishes a database connection. This method requires a database URL, which varies depending on your DBMS. The following are some examples of database URLs:

1. MySQL: jdbc:mysql://localhost:3306/ , where localhost is the name of the server hosting your database, and 3306 is the port number
2. Java DB: jdbc:derby:testdb;create=true , where testdb is the name of the database to connect to, and create=true instructs the DBMS to create the database. Note: This URL establishes a database connection with the Java DB Embedded Driver. Java DB also includes a Network Client Driver, which uses a different URL.

This method specifies the user name and password required to access the DBMS with a Properties object.

• Typically, in the database URL, you also specify the name of an existing database to which you want to connect. For example, the URL jdbc:mysql://localhost:3306/mysql represents the database URL for the MySQL database named mysql . The samples in this tutorial use a URL that does not specify a specific database because the samples create a new database.
• In previous versions of JDBC, to obtain a connection, you first had to initialize your JDBC driver by calling the method Class.forName . This methods required an object of type java.sql.Driver . Each JDBC driver contains one or more classes that implements the interface java.sql.Driver . The drivers for Java DB are org.apache.derby.jdbc.EmbeddedDriver and org.apache.derby.jdbc.ClientDriver , and the one for MySQL Connector/J is com.mysql.cj.jdbc.Driver . See the documentation of your DBMS driver to obtain the name of the class that implements the interface java.sql.Driver . Any JDBC 4.0 drivers that are found in your class path are automatically loaded. (However, you must manually load any drivers prior to JDBC 4.0 with the method Class.forName .)

The method returns a Connection object, which represents a connection with the DBMS or a specific database. Query the database through this object.

Specifying Database Connection URLs

A database connection URL is a string that your DBMS JDBC driver uses to connect to a database. It can contain information such as where to search for the database, the name of the database to connect to, and configuration properties. The exact syntax of a database connection URL is specified by your DBMS.

Java DB Database Connection URLs

The following is the database connection URL syntax for Java DB:

jdbc:derby:[subsubprotocol:][databaseName][;attribute=value]*

• subsubprotocol specifies where Java DB should search for the database, either in a directory, in memory, in a class path, or in a JAR file. It is typically omitted.
• databaseName is the name of the database to connect to.
• attribute=value represents an optional, semicolon-separated list of attributes. These attributes enable you to instruct Java DB to perform various tasks, including the following:
  • Create the database specified in the connection URL.
  • Encrypt the database specified in the connection URL.
  • Specify directories to store logging and trace information.
  • Specify a user name and password to connect to the database.

  See Java DB Developer’s Guide and Java DB Reference Manual from Java DB Technical Documentation for more information.

  MySQL Connector/J Database URL

  The following is the database connection URL syntax for MySQL Connector/J:

  jdbc:mysql://[host][,failoverhost. ] [:port]/[database] [?propertyName1][=propertyValue1] [&propertyName2][=propertyValue2].

  • host:port is the host name and port number of the computer hosting your database. If not specified, the default values of host and port are 127.0.0.1 and 3306, respectively.
  • database is the name of the database to connect to. If not specified, a connection is made with no default database.
  • failover is the name of a standby database (MySQL Connector/J supports failover).
  • propertyName=propertyValue represents an optional, ampersand-separated list of properties. These attributes enable you to instruct MySQL Connector/J to perform various tasks.

  Источник

  Open database connection in java

  2) Create the connection object

  The getConnection() method of DriverManager class is used to establish connection with the database.

  Syntax of getConnection() method

  Example to establish connection with the Oracle database

  3) Create the Statement object

  The createStatement() method of Connection interface is used to create statement. The object of statement is responsible to execute queries with the database.

  Syntax of createStatement() method

  Example to create the statement object

  4) Execute the query

  The executeQuery() method of Statement interface is used to execute queries to the database. This method returns the object of ResultSet that can be used to get all the records of a table.

  Syntax of executeQuery() method

  Example to execute query

  5) Close the connection object

  By closing connection object statement and ResultSet will be closed automatically. The close() method of Connection interface is used to close the connection.

  Syntax of close() method

  Example to close connection

  Note: Since Java 7, JDBC has ability to use try-with-resources statement to automatically close resources of type Connection, ResultSet, and Statement.

  It avoids explicit connection closing step.

  

  For Videos Join Our Youtube Channel: Join Now

  Feedback

  Help Others, Please Share

  

  Learn Latest Tutorials

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  Preparation

  

  

  

  

  Trending Technologies

  

  

  

  

  

  

  

  

  

  

  

  

  B.Tech / MCA

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  Javatpoint Services

  JavaTpoint offers too many high quality services. Mail us on h[email protected], to get more information about given services.

  • Website Designing
  • Website Development
  • Java Development
  • PHP Development
  • WordPress
  • Graphic Designing
  • Logo
  • Digital Marketing
  • On Page and Off Page SEO
  • PPC
  • Content Development
  • Corporate Training
  • Classroom and Online Training
  • Data Entry

  Training For College Campus

  JavaTpoint offers college campus training on Core Java, Advance Java, .Net, Android, Hadoop, PHP, Web Technology and Python. Please mail your requirement at [email protected].
  Duration: 1 week to 2 week

  Like/Subscribe us for latest updates or newsletter

  Источник

  Establishing JDBC Connection in Java

  Before establishing a connection between the front end i.e your Java Program and the back end i.e the database we should learn what precisely a JDBC is and why it came into existence. Now let us discuss what exactly JDBC stands for and will ease out with the help of real-life illustration to get it working.

  What is JDBC?

  JDBC is an acronym for Java Database Connectivity. It’s an advancement for ODBC ( Open Database Connectivity ). JDBC is a standard API specification developed in order to move data from frontend to the backend. This API consists of classes and interfaces written in Java. It basically acts as an interface (not the one we use in Java) or channel between your Java program and databases i.e it establishes a link between the two so that a programmer could send data from Java code and store it in the database for future use.

  Illustration: Working of JDBC co-relating with real-time

  Why JDBC Come into Existence?

  As previously told JDBC is an advancement for ODBC, ODBC being platform-dependent had a lot of drawbacks. ODBC API was written in C, C++, Python, and Core Java and as we know above languages (except Java and some part of Python )are platform-dependent. Therefore to remove dependence, JDBC was developed by a database vendor which consisted of classes and interfaces written in Java.

  Steps For Connectivity Between Java Program and Database

  1. Import the Packages
  2. Load the drivers using the forName() method
  3. Register the drivers using DriverManager
  4. Establish a connection using the Connection class object
  5. Create a statement
  6. Execute the query
  7. Close the connections

  Let us discuss these steps in brief before implementing by writing suitable code to illustrate connectivity steps for JDBC/

  Step 1: Import the Packages

  Step 2: Loading the drivers

  In order to begin with, you first need to load the driver or register it before using it in the program. Registration is to be done once in your program. You can register a driver in one of two ways mentioned below as follows:

  2-A Class.forName()

  Here we load the driver’s class file into memory at the runtime. No need of using new or create objects. The following example uses Class.forName() to load the Oracle driver as shown below as follows:

  Class.forName(“oracle.jdbc.driver.OracleDriver”);

  2-B DriverManager.registerDriver()

  DriverManager is a Java inbuilt class with a static member register. Here we call the constructor of the driver class at compile time. The following example uses DriverManager.registerDriver()to register the Oracle driver as shown below:

  DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver())

  Step 3: Establish a connection using the Connection class object

  After loading the driver, establish connections as shown below as follows:

  Connection con = DriverManager.getConnection(url,user,password)

  • user: Username from which your SQL command prompt can be accessed.
  • password: password from which the SQL command prompt can be accessed.
  • con: It is a reference to the Connection interface.
  • Url: Uniform Resource Locator which is created as shown below:

  String url = “ jdbc:oracle:thin:@localhost:1521:xe”

  Where oracle is the database used, thin is the driver used, @localhost is the IP Address where a database is stored, 1521 is the port number and xe is the service provider. All 3 parameters above are of String type and are to be declared by the programmer before calling the function. Use of this can be referred to form the final code.

  Step 4: Create a statement

  Once a connection is established you can interact with the database. The JDBCStatement, CallableStatement, and PreparedStatement interfaces define the methods that enable you to send SQL commands and receive data from your database.
  Use of JDBC Statement is as follows:

  Statement st = con.createStatement();

  Note: Here, con is a reference to Connection interface used in previous step .

  Step 5: Execute the query

  Now comes the most important part i.e executing the query. The query here is an SQL Query. Now we know we can have multiple types of queries. Some of them are as follows:

  The executeQuery() method of the Statement interface is used to execute queries of retrieving values from the database. This method returns the object of ResultSet that can be used to get all the records of a table.
  The executeUpdate(sql query) method of the Statement interface is used to execute queries of updating/inserting.

  Pseudo Code:

  int m = st.executeUpdate(sql); if (m==1) System.out.println("inserted successfully : "+sql); else System.out.println("insertion failed");

  Here sql is SQL query of the type String

  Источник