How To Connect To Mysql Database Inwards Coffee Alongside Example

In this tutorial, You volition acquire how to connect to MySQL database from Java plan together with running SELECT together with INSERT queries to yell upwardly together with update information amongst measuring yesteryear measuring guide. In guild to connect together with access MySQL database from Java, you lot tin forcefulness out work JDBC (Java Database Connectivity) API, which is bundled inward JDK itself. JDBC allow you lot to connect to whatever database e.g. Oracle, SQL Server or MySQL, provided you lot receive got the vendor's implementation of JDBC driver interface, which is required to connect database. You tin forcefulness out connect to MySQL from Java yesteryear using MySQL's Type 4 JDBC driver which is bundled inward mysql-connector-java-5.1.23-bin.jar. It's type 4, pure Java driver, which way you lot don't involve whatever native library or JDBC ODBC bridge, all you lot involve is to pose this JAR inward your classpath. This JAR contains "com.mysql.jdbc.Driver" which is the fundamental for making database connexion from Java plan to MySQL DB. If this JAR is non nowadays inward the classpath, hence spell running this plan you lot volition acquire java.lang.ClassNotFoundException: com.mysql.jdbc.Driver, hence ever brand certain you lot receive got this JAR inward the classpath. By the way if you lot are looking for about proficient mass to master copy JDBC programming inward Java, I propose you lot to accept a await at Practical Database Programming amongst Java By Ying Bai. This is 1 of the relatively latest mass on JDBC together with encompass ii of the most pop database SQL Server 2008 together with Oracle. This mass also teaches you lot how to run inward Netbeans Integrated environment, similar to our representative together with provides all necessary tools together with noesis to handgrip database programming inward Java. An ideal mass for graduate together with undergraduate students, equally good equally database programmers together with software engineers.

Connecting to MySQL database using JDBC

In guild to connect to MySQL database, you lot involve 4 things :

1. JDBC URL (jdbc:mysql://localhost:3306/test)
2. Username ("root")
3. Password ("root")
4. Database amongst tabular array to demonstrate query (Book database amongst few books inward essay database)

The JDBC URL for MySQL database starts amongst "jdbc:mysql" that's the protocol connexion is using to connect, followed yesteryear host together with port where your MySQL database is running. In our case, I am running MySQL server inward localhost, which yesteryear default listens to port 3306, unless you lot alter it during installation. Next role is "test" which is an empty database which comes amongst MySQL. I receive got created a Book tabular array into this database for our representative purpose. If you lot want, you lot tin forcefulness out also do the same tabular array yesteryear using next SQL :

CREATE TABLE `books` (   `id` int(11) NOT NULL,   `name` varchar(50) NOT NULL,   `author` varchar(50) NOT NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1

together with you lot tin forcefulness out work next SQL to populate tabular array amongst about proficient books :

INSERT INTO test.books (id, `name`, author)      VALUES (1, 'Effective Java', 'Joshua Bloch'); INSERT INTO test.books (id, `name`, author)      VALUES (2, 'Java Concurrency inward Practice', 'Brian Goetz');

Java Program to connect MySQL from Java

Now, let's write our Java plan to connect to this MySQL database running on localhost. Its of import to closed database connection, controversy together with result-set object in 1 trial you lot are done amongst them. It's also of import to closed them inward finally block amongst their endeavour grab block because their closed method tin forcefulness out also throw Exception together with inward that instance your plan may start leaking those resources, encounter my post service right way to closed resources inward Java for to a greater extent than details. Alternatively, you lot tin forcefulness out work try-with-resource controversy introduced inward Java seven to opened upwardly together with closed SQL resources. In fact that's the criterion way from JDK 1.7 onward.

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement;  /**  * Simple Java plan to connect to MySQL database running on localhost together with  * running SELECT together with INSERT query to yell upwardly together with add together data.  * @author Javin Paul  */ public class JavaToMySQL {      // JDBC URL, username together with password of MySQL server     private static in conclusion String url = "jdbc:mysql://localhost:3306/test";     private static in conclusion String user = "root";     private static in conclusion String password = "root";      // JDBC variables for opening together with managing connection     private static Connection con;     private static Statement stmt;     private static ResultSet rs;      public static void main(String args[]) {         String query = "select count(*) from books";          try {             // opening database connexion to MySQL server             con = DriverManager.getConnection(url, user, password);              // getting Statement object to execute query             stmt = con.createStatement();              // executing SELECT query             rs = stmt.executeQuery(query);              while (rs.next()) {                 int count = rs.getInt(1);                 System.out.println("Total reveal of books inward the tabular array : " + count);             }          } catch (SQLException sqlEx) {             sqlEx.printStackTrace();         } finally {             //close connexion ,stmt together with resultset here             try { con.close(); } catch(SQLException se) { /*can't do anything */ }             try { stmt.close(); } catch(SQLException se) { /*can't do anything */ }             try { rs.close(); } catch(SQLException se) { /*can't do anything */ }         }     }  }

java.sql.SQLException: No suitable driver constitute for jdbc:mysql://localhost:3306/test/book

When I outset run this program, I got the error "No suitable driver constitute for jdbc:mysql", because MySQL driver JAR, mysql-connector-java-5.1.23-bin.jar was non nowadays inward classpath.

java.sql.SQLException: No suitable driver constitute for jdbc:mysql://localhost:3306/test/book     at java.sql.DriverManager.getConnection(DriverManager.java:689)     at java.sql.DriverManager.getConnection(DriverManager.java:247)     at JavaToMySQL.main(JavaToMySQL.java:29) Exception in thread "main" java.lang.NullPointerException     at JavaToMySQL.main(JavaToMySQL.java:46) Java Result: 1

When I in 1 trial again ran the Java plan later on including MySQL JDBC driver inward Classpath, I was greeted amongst next error, because I had included tabular array nurture also inward the JDBC URL String equally "jdbc:mysql://localhost:3306/test/book", let's endeavour to run later on removing tabular array from JDBC String

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown database 'test/book'     at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)     at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)     at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)     at java.lang.reflect.Constructor.newInstance(Constructor.java:408)     at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)     at com.mysql.jdbc.Util.getInstance(Util.java:386)     at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1053)

Now it ran successful together with printed next output :

Total reveal of books inward the tabular array : 2

Which is correct, because our books tabular array exclusively has ii books, Effective Java together with Java Concurrency inward Practice.

By the way, if you lot receive got mysql driver during compile fourth dimension but missed it during run-time, you lot volition get
java.lang.ClassNotFoundException: com.mysql.jdbc.Driver. You tin forcefulness out follow the solution give hither to bargain amongst this error.

How to yell upwardly information using SELECT Query to MySQL using JDBC

In guild to acquire information from database, you lot tin forcefulness out run SELECT query. In our outset example, nosotros receive got used SELECT query, but nosotros exclusively acquire the count of records, this fourth dimension nosotros volition yell upwardly the tape itself. most of the plan volition rest same, except the SQL query together with the role which yell upwardly information from ResultSet object.

String query = "select id, name, writer from books";  rs = stmt.executeQuery(query);  while (rs.next()) {    int id = rs.getInt(1);    String name = rs.getString(2);    String writer = rs.getString(3);    System.out.printf("id : %d, name: %s, writer : %s %n", id, name, author);  }

This volition impress next output :

id : 1, name: Effective Java, writer : Joshua Bloch  id : 2, name: Java Concurrency in Practice, writer : Brian Goetz 

Couple of things to pay attending here. See the rs.getInt(1) method call, good that is to yell upwardly an integer column, which is "id" inward our case. In JDBC, column index laid out amongst 1, hence rs.getInt(1) volition read outset column equally Integer. It volition throw InvalidColumnIndexException if nosotros render invalid column index, equally many Java developer mistakenly render "0" for outset column i.e. rs.getInt(0). Accessing columns amongst index is risky, its amend to work column nurture instead of indexes i.e. rs.getInt("id") will render value of id column. It is also 1 of the JDBC best practices you lot volition acquire inward my post service 10 JDBC best practise for Java developers . Similarly getString() is used to read String or VARCHAR columns. The loop volition run until rs.next() render false, when reveal of rows comes to an end. This query returns 2 rows together with that's why the loop runs twice, printing details of ii books loaded from MySQL database.

How to insert information using INSERT Query to MySQL using JDBC

Inserting information is quite similar to retrieving data, merely work INSERT query insetead of SELECT query. Also this time, nosotros volition work executeUpdate() instead of executeQuery() method. This method is used to run INSERT, UPDATE or DELETE queries together with also SQL DDL statements e.g. CREATE, ALER or DROP tabular array that returns nothing, hence nosotros don't involve ResultSet object. So you lot must delete all references of ResultSet object from our program, take executeQuery() together with modify SQL Query String equally follows :

String query = "INSERT INTO test.books (id, name, author) \n" +                " VALUES (3, 'Head First Java', 'Kathy Sieara');";  // executing SELECT query stmt.executeUpdate(query);

Once you lot run the program, you lot tin forcefulness out acquire dorsum together with depository fiscal establishment tally the database. This fourth dimension you lot volition encounter three records inward your mass table, equally shown below :

That's all close how to connect to MySQL from Java program. Once you lot are able to brand a successful connexion you lot tin forcefulness out run SELECT, INSERT, DELETE or UPDATE query merely similar you lot do using MySQL ascendency trouble customer or MySQL GUI. Like connecting to whatever other database, nosotros receive got used Connection object to brand connexion together with ResultSet object to acquire the trial of our SQL Query. Just brand certain that your MySQL Server is started together with running earlier you lot connect together with mysql-connector-java-5.1.17-bin.jar is inward CLASSPATH to avoid nasty ClassNotFoundException.

Once you lot are comfortable amongst connecting, retrieving information together with inserting data, side yesteryear side measuring is to acquire how to work PreparedStatement inward Java to forbid SQL injection. In a production system, you lot should ever work PreparedStatement together with bind variables.

Further Learning
JSP, Servlets together with JDBC for Beginners: Build a Database App
Complete JDBC Programming Part 1 together with 2
learn here)

What is departure betwixt connected together with disconnected RowSet inward Java? (answer)

How to work JDBC connexion puddle inward Spring framework? (solution)

5 Simple things to improve functioning of Java database applications (tips)

Difference betwixt java.util.Date together with java.sql.Date inward Java? (answer)

How to do INSERT together with UPDATE using JDBC Batch statement? (solution)

10 JDBC Questions from Java Interviews to reply (questions)

Resources :

• If you lot don't receive got MySQL database, you lot tin forcefulness out download from here 
• If you lot don't receive got MySQL JDBC driver, you lot tin forcefulness out also download the mysql-connector-java-5.1.17-bin.jar file from here. 
• You tin forcefulness out acquire recommended JDBC book, Practical Database Programming amongst Java By Ying Bai here

Share this post