How To Connect To Mysql Database Inward Coffee Amongst Example

In this tutorial, You volition larn how to connect to MySQL database from Java programme as well as running SELECT as well as INSERT queries to recall as well as update information amongst mensuration yesteryear mensuration guide. In club to connect as well as access MySQL database from Java, yous tin displace exercise JDBC (Java Database Connectivity) API, which is bundled inward JDK itself. JDBC allow yous to connect to whatsoever database e.g. Oracle, SQL Server or MySQL, provided yous receive got the vendor's implementation of JDBC driver interface, which is required to connect database. You tin displace 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 yous don't quest whatsoever native library or JDBC ODBC bridge, all yous quest is to pose this JAR inward your classpath. This JAR contains "com.mysql.jdbc.Driver" which is the substitution for making database connexion from Java programme to MySQL DB. If this JAR is non nowadays inward the classpath, as well as thence spell running this programme yous volition acquire java.lang.ClassNotFoundException: com.mysql.jdbc.Driver, thence ever brand certain yous receive got this JAR inward the classpath. By the way if yous are looking for about proficient mass to original JDBC programming inward Java, I advise yous to accept a await at Practical Database Programming amongst Java By Ying Bai. This is i of the relatively latest mass on JDBC as well as comprehend ii of the most pop database SQL Server 2008 as well as Oracle. This mass also teaches yous how to run inward Netbeans Integrated environment, similar to our instance as well as provides all necessary tools as well as noesis to direct keep database programming inward Java. An ideal mass for graduate as well as undergraduate students, every bit good every bit database programmers as well as software engineers.

Connecting to MySQL database using JDBC

In club to connect to MySQL database, yous quest iv 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 seek out database)

The JDBC URL for MySQL database starts amongst "jdbc:mysql" that's the protocol connexion is using to connect, followed yesteryear host as well as 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 yous alter it during installation. Next exercise is "test" which is an empty database which comes amongst MySQL. I receive got created a Book tabular array into this database for our instance purpose. If yous want, yous tin displace 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

as well as yous tin displace exercise 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 programme to connect to this MySQL database running on localhost. Its of import to unopen database connection, disputation as well as result-set object i time yous are done amongst them. It's also of import to unopen them inward lastly block amongst their effort grab block because their unopen method tin displace also throw Exception as well as inward that instance your programme may start leaking those resources, meet my postal service right way to unopen resources inward Java for to a greater extent than details. Alternatively, yous tin displace exercise try-with-resource disputation introduced inward Java seven to opened upward as well as unopen 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 programme to connect to MySQL database running on localhost as well as  * running SELECT as well as INSERT query to recall as well as add together data.  * @author Javin Paul  */ public class JavaToMySQL {      // JDBC URL, username as well as password of MySQL server     private static terminal String url = "jdbc:mysql://localhost:3306/test";     private static terminal String user = "root";     private static terminal String password = "root";      // JDBC variables for opening as well as 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 expose of books inward the tabular array : " + count);             }          } catch (SQLException sqlEx) {             sqlEx.printStackTrace();         } finally {             //close connexion ,stmt as well as 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 institute for jdbc:mysql://localhost:3306/test/book

When I get-go run this program, I got the error "No suitable driver institute 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 institute 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 over again ran the Java programme afterwards including MySQL JDBC driver inward Classpath, I was greeted amongst next error, because I had included tabular array cite also inward the JDBC URL String every bit "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 as well as printed next output :

Total expose of books inward the tabular array : 2

Which is correct, because our books tabular array entirely has ii books, Effective Java as well as Java Concurrency inward Practice.

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

How to recall information using SELECT Query to MySQL using JDBC

In club to acquire information from database, yous tin displace run SELECT query. In our get-go example, nosotros receive got used SELECT query, but nosotros entirely acquire the count of records, this fourth dimension nosotros volition recall the tape itself. most of the programme volition stay same, except the SQL query as well as the exercise which recall 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 recall an integer column, which is "id" inward our case. In JDBC, column index laid about amongst 1, thence rs.getInt(1) volition read get-go column every bit Integer. It volition throw InvalidColumnIndexException if nosotros render invalid column index, every bit many Java developer mistakenly render "0" for get-go column i.e. rs.getInt(0). Accessing columns amongst index is risky, its ameliorate to exercise column cite instead of indexes i.e. rs.getInt("id") will render value of id column. It is also i of the JDBC best practices yous volition larn inward my postal service 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 expose of rows comes to an end. This query returns 2 rows as well as 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 exercise INSERT query insetead of SELECT query. Also this time, nosotros volition exercise executeUpdate() instead of executeQuery() method. This method is used to run INSERT, UPDATE or DELETE queries as well as also SQL DDL statements e.g. CREATE, ALER or DROP tabular array that returns nothing, thence nosotros don't quest ResultSet object. So yous must delete all references of ResultSet object from our program, take away executeQuery() as well as modify SQL Query String every bit 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 yous run the program, yous tin displace become dorsum as well as cheque the database. This fourth dimension yous volition meet iii records inward your mass table, every bit shown below :

That's all nearly how to connect to MySQL from Java program. Once yous are able to brand a successful connexion yous tin displace run SELECT, INSERT, DELETE or UPDATE query but similar yous do using MySQL ascendancy business customer or MySQL GUI. Like connecting to whatsoever other database, nosotros receive got used Connection object to brand connexion as well as ResultSet object to acquire the outcome of our SQL Query. Just brand certain that your MySQL Server is started as well as running earlier yous connect as well as mysql-connector-java-5.1.17-bin.jar is inward CLASSPATH to avoid nasty ClassNotFoundException.

Once yous are comfortable amongst connecting, retrieving information as well as inserting data, adjacent mensuration is to larn how to exercise PreparedStatement inward Java to foreclose SQL injection. In a production system, yous should ever exercise PreparedStatement as well as bind variables.

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

What is divergence betwixt connected as well as disconnected RowSet inward Java? (answer)

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

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

Difference betwixt java.util.Date as well as java.sql.Date inward Java? (answer)

How to do INSERT as well as UPDATE using JDBC Batch statement? (solution)

10 JDBC Questions from Java Interviews to reply (questions)

Resources :

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

Sumber https://javarevisited.blogspot.com/

Share this post