Java with mysql code

Java connect to MySQL database with JDBC

This article explains how to write Java code to connect to a MySQL database server, step by step. If you just want to see the code example, click on Code example: a simple program connects to MySQL. If you have never written Java code to connect MySQL before, it’s worth reading this tutorial from the beginning.

1. Download JDBC driver for MySQL

First, in order to have Java program working with MySQL, we need a JDBC driver for MySQL. Browse this URL:

to download the latest version of the JDBC driver for MySQL called Connector/J. MySQL Connector/J comes into 2 major versions: 5.1 and 8.0. The latest version 8.0 supports JDBC 4.2 and JDK 8 or higher.

Click the Download button next to Platform Independent (Architecture Independent), ZIP Archive to download a zip archive. Extract the ZIP file to a desired location on your computer.

The distribution includes a binary JAR file, source code, documentation and license files. But only one file we need is the JAR file mysql-connector-java-VERSION.jar . Copy this file into your project and make it available in your program’s classpath. You can use newer version of JDBC driver for MySQL.

If your Java project based on Maven, you just need to declare the following dependency in the pom.xml file:

 mysql mysql-connector-java 8.0.19 

2. No need to load MySQL driver class explicitly

The Connector/J version 8.0 library comes with a JDBC driver class: com.mysql.cj.jdbc.Driver . Before Java 6, we have to load the driver explicitly by this statement:

Class.forName(«com.mysql.cj.jdbc.Driver»);

However that statement is no longer needed, thanks to new update in JDBC 4.0 comes from Java 6. As long as you put the MySQL JDBC driver JAR file file into your program’s classpath, the driver manager can find and load the driver.

3. Understand the getConnection() method of DriverManager class

It’s quite easy to make a connection to a database server in general, as well as to a MySQL server in particular. Just using the method getConnection() of the class DriverManager which is available in the package java.sql .

There are three different signatures of the method getConnection() which we can use:

• • static Connection getConnection(String url)
  • static Connection getConnection(String url, Properties info)
  • static Connection getConnection(String url, String user, String password)

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

  • • host: host name or IP address of the MySQL server.
    • port: port number of the server, default is 3306.
    • database: name of the database on the server.
    • propertyName1=propertyValue1 : a key=value pair for an additional property which will be sent to the server. For example, to send a username and password, write: ?user=root&password=secret

    4. Java code example connect to MySQL database

    The following example program makes three connections to three MySQL database in three different ways:

    import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.Properties; public class MySQLConnectExample < public static void main(String[] args) < // creates three different Connection objects Connection conn1 = null; Connection conn2 = null; Connection conn3 = null; try < // connect way #1 String url1 = "jdbc:mysql://localhost:3306/test1"; String user = "root"; String password = "secret"; conn1 = DriverManager.getConnection(url1, user, password); if (conn1 != null) < System.out.println("Connected to the database test1"); >// connect way #2 String url2 = "jdbc:mysql://localhost:3306/test2?user=root&password=secret"; conn2 = DriverManager.getConnection(url2); if (conn2 != null) < System.out.println("Connected to the database test2"); >// connect way #3 String url3 = "jdbc:mysql://localhost:3306/test3"; Properties info = new Properties(); info.put("user", "root"); info.put("password", "secret"); conn3 = DriverManager.getConnection(url3, info); if (conn3 != null) < System.out.println("Connected to the database test3"); >> catch (SQLException ex) < System.out.println("An error occurred. Maybe user/password is invalid"); ex.printStackTrace(); >> >

    NOTES: You should close the database connection in the finally clause like this:

    finally < if (conn != null) < try < conn.close(); >catch (SQLException ex) < ex.printStackTrace(); >> >

    And since Java 1.7, you can use the try-with-resource syntax that closes the connection automatically, for example:

    try (Connection conn = DriverManager.getConnection(url, user, password)) < if (conn != null) < System.out.println("Connected to the database"); >> catch (SQLException ex) < System.out.println("An error occurred. Maybe user/password is invalid"); ex.printStackTrace(); >

    Type the following command to compile the example program:

    javac MySQLConnectExample.java

    Suppose the Connect/J library is placed in the same directory as the MySQLConnectExample.java file. Type the following command to run:

    java -cp mysql-connector-java-5.1.21-bin.jar;. MySQLConnectExample

    And here is the result when running the example program:

    

    That means the program has successfully connected to the MySQL database server.

    For visual howto, watch this video:

    JDBC API References:

    Related JDBC Tutorials:

    About the Author:

    

    Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He started programming with Java in the time of Java 1.4 and has been falling in love with Java since then. Make friend with him on Facebook and watch his Java videos you YouTube.

    Источник

    MySQL Database Connection in Java with Example

    In this post, we will discuss how to establish the JDBC connection in Java with MySQL database. After establishing the connection we will develop a sample JDBC application to check whether the connection is done properly or not with the MySQL database. We will develop a simple JDBC program to fetch the record from one table of the MySQL database and display it on the console.

    Before establishing a MySQL database connection in Java, make sure you have the following software setup ready on your computer system,

    1) MySQL Database
    2) JDK1.8 or later version
    3) Any text editor to write the code or IDE

    Establish MySQL Database Connection

    Now let us begin the establishing MySQL JDBC connection.

    Step1) Collect JDBC driver of MySQL database.

    The JDBC driver of MySQL database doesn’t come along with MySQL database installation, We need to collect it separately. Download the JDBC driver/connector of the MySQL database.

    Open the zip file, and extract it. The mysql-connector-java-.jar is the main jar file that will be used to develop the JDBC applications through the MySQL database.

    Step2) Add jar file to the CLASSPATH.

    Note:- In the CLASSPATH don’t add the location/folder where the jar file is available. We have to add the location of the jar file, not the folder where the jar file is available.

    Don’t try to write the location name manually because we can do a spelling mistake, so it is recommended to copy the jar file location. In order to copy the jar files correctly in the Windows operating system, Go to the folder where the jar file is locating, now select jar file, right-click and select properties, in the security section you will find “Object name”. We can copy the actual path of the jar file from here.

    The folder where the Jar file is located:- C:\Users\user\Downloads\
    The location which will be added to Classpath:- C:\Users\user\Downloads\mysql-connector-java-8.0.21.jar

    How to add the jar file to the classpath?
    For this, we have to go to “Advance System Settings”. There are many ways to go to “Advance System Settings”,
    1) This PC -> Properties -> Advance System Settings
    2) Control panel -> System and Security -> System -> Advance system settings

    

    

    Now go to Advance system settings -> Environment Variables -> System Variables.

    

    

    If the CLASSPATH variable already exists then “Edit it”, otherwise create “New”.

    Variable Name: CLASSPATH Value: ;.

    Variable Name: CLASSPATH Value: ;;.

    It is recommended to place dot (.) after the variable values, and ; is a separator that separates two variable values. Learn more:- Different Ways to Set Java Classpath Environment Variables in Windows

    In case of “Edit”, if new window came then each line must have separate variable value, and using “Move UP” button place it on the first position.

    Step3) Develop the application.

    We have established the connection successfully. Now, we have to develop a sample JDBC application to check the establishing the connection is done properly or not.

    MySQL JDBC Driver Details

    We need some basic details of MySQL JDBC driver to develop the JDBC program.

    JDBC Driver Class Name:: com.mysql.cj.jdbc.Driver

    • For local MySQL:: jdbc:mysql:///
    • For remote MySQL:: jdbc:mysql://:/

    Username:
    Password:

    Note:- If you developing JDBC application for first time in local computer/laptop then use URL for local MySQL.

    How to find the database name in MySQL?
    Type below query to get all databases available in the MySQL server.

    If you are new to MySQL then create a new database,

    CREATE SCHEMA `knowprogram` ;

    Here “knowprogram” will be the name of the database. Hence the URL for local MySQL will be => jdbc:mysql:///knowprogram

    MySQL Database JDBC Connection Code in Java Example

    Now, let us develop a small Java program to check JDBC connection with MySQL Database is established properly or not?

    import java.sql.*; public class ConnectionTest < public static void main(String[] args ) throws Exception < // register Oracle thin driver with DriverManager service // it is optional for JDBC4.x version Class.forName("com.mysql.cj.jdbc.Driver"); // declare variables // place your own values final String url = "jdbc:mysql:///knowprogram"; final String user = "root"; final String password = "[email protected]"; // establish the connection Connection con = DriverManager.getConnection(url, user, password); // display status message if(con == null) < System.out.println("JDBC connection is not established"); return; >else System.out.println("Congratulations,"+ " JDBC connection is established successfully.\n"); // close JDBC connection con.close(); > //main > //class

    Compile the Java Program,
    > javac ConnectionTest.java

    Execute the program,
    > java ConnectionTest

    Congratulations, JDBC connection is established successfully.

    Standard Steps to Develop JDBC Application

    There are some standard steps to develop JDBC program. Every JDBC program will have these common points.

    1. Register JDBC driver with DriverManager service

    This step is only required for the JDBC3.0 The JDBC4.x version supports the auto-loading of the driver class, so registering JDBC driver with DriverManager is optional. Here auto-loading means when we call the method of the class then due to logic of its static block it loads the driver class.

    // register MySQL connector with // DriverManager service // optional for JDBC4.x version drivers Class.forName("com.mysql.cj.jdbc.Driver");

    2. Establish the connection with database software

    // establishing the connection with database software Connection con = DriverManager.getConnection(url, username, password);

    Here place your own URL, username, and password which are assigned to the MySQL database.

    3. Create JDBC Statement object

    // create JDBC Statement object Statement st = con.createStatement();

    4. Gather SQL query result back to Java application from database software.

    // close JDBC objects rs.close(); st.close(); con.close();

    JDBC Program Example

    Previously, we have developed a simple Java program to check that the JDBC connection is established properly with the MySQL database or not. Now, let us develop a simple Java to fetch records from a table and display it on the console.

    But for this, we need a table. You can use an existing table. But here we are developing for the first time so we will create a new table in the MySQL database.

    CREATE TABLE `knowprogram`.`student`( `sno` INT NOT NULL, `sname` VARCHAR(45) NULL, `sadd` VARCHAR(45) NULL, `avg` FLOAT NULL, PRIMARY KEY (`sno`) );

    Table created. Now, insert some record into the table,

    INSERT INTO `knowprogram`.`student` (`sno`, `sname`, `sadd`, `avg`) VALUES ('10', Sophia', 'Manchester', '70'); INSERT INTO `knowprogram`.`student` (`sno`, `sname`, `sadd`, `avg`) VALUES ('11', 'William', 'Washington', '80'); INSERT INTO `knowprogram`.`student` (`sno`, `sname`, `sadd`, `avg`) VALUES ('12', 'Alex', 'Boise', '85'); INSERT INTO `knowprogram`.`student` (`sno`, `sname`, `sadd`, `avg`) VALUES ('13', 'Amelia', 'London', '90');

    Java Program

    We will develop a simple JDBC program which will fetch the record of “student” table and display it to the console.

    import java.sql.*; public class SelectTest < public static void main(String[] args ) throws Exception < // variables final String url = "jdbc:mysql:///knowprogram"; final String user = "root"; final String password = "[email protected]"; // establish the connection Connection con = DriverManager.getConnection(url, user, password); // create JDBC statement object Statement st = con.createStatement(); // prepare SQL query String query = "SELECT SNO, SNAME, SADD, AVG FROM STUDENT"; // send and execute SQL query in Database software ResultSet rs = st.executeQuery(query); // process the ResultSet object boolean flag = false; while(rs.next()) < flag = true; System.out.println( rs.getInt(1) + " " + rs.getString(2) + " " + rs.getString(3) + " " + rs.getFloat(4) ); >if(flag == true) < System.out.println("\nRecords retrieved and displayed"); >else < System.out.println("Record not found"); >// close JDBC objects rs.close(); st.close(); con.close(); > //main > //class

    Compile and Execute the JDBC program,

    > javac SelectTest.java
    > java SelectTest

    10 Sophia Manchester 72.0
    11 William Washington 80.0
    12 Alex boise 95.0
    13 Amelia London 85.0

    Records retrieved and displayed

    Note:- While developing this application we didn’t follow any coding standards. We should follow some coding standards while developing JDBC applications. Learn more:- Coding Standards and Guidelines for JDBC Application, Best Way to Close JDBC Connection Object

    If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

    Источник