How To Connect To Mysql Database Inwards Coffee Amongst Example

In this tutorial, You volition larn how to connect to MySQL database from Java computer program together with running SELECT together with INSERT queries to cry back together with update information alongside measuring past times measuring guide. In lodge to connect together with access MySQL database from Java, you lot tin utilisation JDBC (Java Database Connectivity) API, which is bundled inwards JDK itself. JDBC allow you lot to connect to whatsoever database e.g. Oracle, SQL Server or MySQL, provided you lot guide maintain the vendor's implementation of JDBC driver interface, which is required to connect database. You tin connect to MySQL from Java past times using MySQL's Type 4 JDBC driver which is bundled inwards mysql-connector-java-5.1.23-bin.jar. It's type 4, pure Java driver, which agency you lot don't require whatsoever native library or JDBC ODBC bridge, all you lot require is to seat this JAR inwards your classpath. This JAR contains "com.mysql.jdbc.Driver" which is the primal for making database connectedness from Java computer program to MySQL DB. If this JAR is non nowadays inwards the classpath, hence piece running this computer program you lot volition acquire java.lang.ClassNotFoundException: com.mysql.jdbc.Driver, hence ever brand certain you lot guide maintain this JAR inwards the classpath. By the way if you lot are looking for but about practiced mass to master copy JDBC programming inwards Java, I propose you lot to guide maintain a expect at Practical Database Programming alongside Java By Ying Bai. This is i of the relatively latest mass on JDBC together with comprehend ii of the most pop database SQL Server 2008 together with Oracle. This mass also teaches you lot how to operate inwards Netbeans Integrated environment, similar to our instance together with provides all necessary tools together with noesis to grip database programming inwards 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 lodge to connect to MySQL database, you lot require 4 things :

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

The JDBC URL for MySQL database starts alongside "jdbc:mysql" that's the protocol connectedness is using to connect, followed past times host together with port where your MySQL database is running. In our case, I am running MySQL server inwards localhost, which past times default listens to port 3306, unless you lot alter it during installation. Next role is "test" which is an empty database which comes alongside MySQL. I guide maintain created a Book tabular array into this database for our instance purpose. If you lot want, you lot tin also do the same tabular array past times 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 utilisation next SQL to populate tabular array alongside but about practiced 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 inwards Practice', 'Brian Goetz');

Java Program to connect MySQL from Java

Now, let's write our Java computer program to connect to this MySQL database running on localhost. Its of import to closed database connection, contestation together with result-set object in i trial you lot are done alongside them. It's also of import to closed them inwards finally block alongside their effort grab block because their closed method tin also throw Exception together with inwards that instance your computer program may start leaking those resources, encounter my post right way to closed resources inwards Java for to a greater extent than details. Alternatively, you lot tin utilisation try-with-resource contestation introduced inwards Java seven to opened upwards together with closed SQL resources. In fact that's the measure 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 computer program to connect to MySQL database running on localhost together with  * running SELECT together with INSERT query to cry back 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 connectedness 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 release of books inwards the tabular array : " + count);             }          } catch (SQLException sqlEx) {             sqlEx.printStackTrace();         } finally {             //close connectedness ,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 flora for jdbc:mysql://localhost:3306/test/book

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

java.sql.SQLException: No suitable driver flora 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 i trial again ran the Java computer program afterwards including MySQL JDBC driver inwards Classpath, I was greeted alongside next error, because I had included tabular array advert also inwards the JDBC URL String equally "jdbc:mysql://localhost:3306/test/book", let's effort to run afterwards 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 release of books inwards the tabular array : 2

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

By the way, if you lot guide maintain 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 follow the solution give hither to bargain alongside this error.

How to cry back information using SELECT Query to MySQL using JDBC

In lodge to acquire information from database, you lot tin run SELECT query. In our start example, nosotros guide maintain used SELECT query, but nosotros entirely acquire the count of records, this fourth dimension nosotros volition cry back the tape itself. most of the computer program volition stay same, except the SQL query together with the role which cry back 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 cry back an integer column, which is "id" inwards our case. In JDBC, column index laid about alongside 1, hence rs.getInt(1) volition read start column equally Integer. It volition throw InvalidColumnIndexException if nosotros render invalid column index, equally many Java developer mistakenly render "0" for start column i.e. rs.getInt(0). Accessing columns alongside index is risky, its ameliorate to utilisation column advert instead of indexes i.e. rs.getInt("id") will render value of id column. It is also i of the JDBC best practices you lot volition larn inwards my post 10 JDBC best exercise for Java developers . Similarly getString() is used to read String or VARCHAR columns. The loop volition run until rs.next() render false, when release 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, but utilisation INSERT query insetead of SELECT query. Also this time, nosotros volition utilisation 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 require 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 acquire dorsum together with banking enterprise tally the database. This fourth dimension you lot volition encounter three records inwards your mass table, equally shown below :

That's all most how to connect to MySQL from Java program. Once you lot are able to brand a successful connectedness you lot tin run SELECT, INSERT, DELETE or UPDATE query but similar you lot do using MySQL ascendance describe of piece of occupation customer or MySQL GUI. Like connecting to whatsoever other database, nosotros guide maintain used Connection object to brand connectedness together with ResultSet object to acquire the outcome 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 inwards CLASSPATH to avoid nasty ClassNotFoundException.

Once you lot are comfortable alongside connecting, retrieving information together with inserting data, side past times side measuring is to larn how to utilisation PreparedStatement inwards Java to forbid SQL injection. In a production system, you lot should ever utilisation 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 divergence betwixt connected together with disconnected RowSet inwards Java? (answer)

How to utilisation JDBC connectedness puddle inwards Spring framework? (solution)

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

Difference betwixt java.util.Date together with java.sql.Date inwards 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 guide maintain MySQL database, you lot tin download from here 
• If you lot don't guide maintain MySQL JDBC driver, you lot tin also download the mysql-connector-java-5.1.17-bin.jar file from here. 
• You tin acquire recommended JDBC book, Practical Database Programming alongside Java By Ying Bai here

Bagikan Artikel ini