Saturday, March 28, 2020

Jdbc - How To Connect Mysql Database From Coffee Programme Amongst Example

When you lot start learning JDBC inwards Java, the showtime programme you lot desire to execute is connected to database from Java together with acquire roughly effect dorsum past times executing roughly SELECT queries. In this Java program, nosotros volition larn How to connect to MySQL database from Java program together with execute a query against it. I pick out MySQL database because it's costless together with tardily to install together with setup. If you lot are using Netbeans IDE thus you lot tin flame connect MySQL Server straight from Netbeans, Which agency inwards ane window you lot could live on writing Java code together with other you lot tin flame write SQL queries. Another wages of using MySQL database is that it provides type iv JDBC driver bundled inwards mysql-connector-java-5.1.17-bin.jar which is tardily to use. By the way, if you lot are using Oracle database thus you lot tin flame depository fiscal establishment jibe Java programme to connect Oracle database, to connect together with run SQL queries against Oracle DB.


This Java tutorial every bit good explains roughly of the mutual fault which comes spell working inwards JDBC code e.g. during connexion or reading results. In social club to connect to MySQL database you lot demand iii things database URL, username, together with password together with nosotros are using default user origin here, which is created during MySQL installation.

By the way, you lot tin flame every bit good use PreparedStatement for connexion because it’s ane of the JDBC best practice to purpose PreparedStatement for amend performance together with avoiding SQL Injection.




Java Program to Connect MySQL Database to Execute Query

Here is consummate Java programme to connect MySQL database running on localhost together with executing queries against that. This illustration connects to exam database of MySQL server, running on the local host at port 3306. At set down level, nosotros demand a JDBC connexion object to communicate amongst MySQL database, a Statement object to execute the query together with a ResultSet object to acquire effect from database. By the way you lot tin flame every bit good purpose Rowset object together with difference betwixt RowSet together with ResultSet is ane of the often asked JDBC Interview question.

One of the affair which I don’t similar most JDBC is lots of boiler plate code e.g. closing connection, contestation together with effect laid together with other resources inwards finally block. Once you lot motility ahead together with start using framework similar Spring, you lot tin flame purpose JdbcTemplate to avoid these boilerplate coding. It’s every bit good skillful to setup your tabular array together with information earlier writing Java program. We volition live on using next tabular array for query:


mysql> select * from stock; +---------+-------------------------+--------------------+ | RIC     | COMPANY                 | LISTED_ON_EXCHANGE | +---------+-------------------------+--------------------+ | 6758.T  | Sony                    | T                  | | GOOG.O  | Google Inc              | O                  | | GS.N    | Goldman Sachs Group Inc | northward                  | | INDIGO  | INDIGO Airlines         | NULL               | | INFY.BO | InfoSys                 | BO                 | | VOD.L   | Vodafone Group PLC      | L                  | +---------+-------------------------+--------------------+ 6 rows in set (0.00 sec)


And this is our Java programme to connect MySQL database together with you lot demand to add together mysql-connector-java-5.1.17-bin.jar into classpath, which contains JDBC type iv driver required to connect MySQL database. If you lot don’t include this JAR inwards your classpath, you lot volition acquire next fault java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
 the showtime programme you lot desire to execute is connected to database from Java together with acquire roughly res JDBC - How to connect MySQL database from Java programme amongst Example
package test;  import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.logging.Level; import java.util.logging.Logger;  /**  *  * Java programme to connect to MySQL Server database running on localhost,  * using JDBC type 4 driver.  *  * @author http://java67.blogspot.com  */ public class MySQLTest{      public static void main(String args[]) {         String dbURL = "jdbc:mysql://localhost:3306/test";         String username ="root";         String password = "root";                 Connection dbCon = null;         Statement stmt = null;         ResultSet rs = null;                 String query ="select count(*) from stock";                 try {             //getting database connexion to MySQL server             dbCon = DriverManager.getConnection(dbURL, username, password);                         //getting PreparedStatment to execute query             stmt = dbCon.prepareStatement(query);                         //Resultset returned past times query             rs = stmt.executeQuery(query);                         while(rs.next()){              int count = rs.getInt(1);              System.out.println("count of stock : " + count);             }                     } catch (SQLException ex) {             Logger.getLogger(CollectionTest.class.getName()).log(Level.SEVERE, null, ex);         } finally{            //close connexion ,stmt together with resultset here         }             }       }  Output: count of stock : 6

ResultSet is used to recollect query effect If you lot don't telephone holler upwards rs.next() together with straight telephone holler upwards rs.getInt(columnName) together with getIndex(), you volition acquire next error, thus ever telephone holler upwards rs.next() earlier calling whatever getXXX() method.

java.sql.SQLException: Before start of effect set
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
        at com.mysql.jdbc.ResultSetImpl.checkRowPos(ResultSetImpl.java:841)
        at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2674)

That’s all on How to connect to MySQL database from Java program. We convey seen measuring past times measuring details to connect MySQL 5.5. database together with executed SELECT query against it. We convey every bit good touched base of operations on roughly of the mutual SQLException which comes inwards JDBC code during connexion or reading effect via ResultSet.

Further Learning
JSP, Servlets together with JDBC for Beginners: Build a Database App
Complete JDBC Programming Part 1 together with 2
5 Tips to improve performance of JDBC Java application
  • Difference betwixt Time, TimeStamp together with Date inwards Java
  • Difference betwixt java.util.Date together with java.sql.Date inwards Java
  • How to convert java.util.Date to java.sql.Date inwards Java
  • How to ready java.sql.SQLException: Invalid column index
  • No comments:

    Post a Comment