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.
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
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
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
No comments:
Post a Comment